Some SQL programming differences

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Some other pages relevant to these blogs include:

Courses in Snowflake

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

How does Snowflake compare to Microsoft's T-SQL for SQL Server? Part five of a five-part series of blogs
All dialects of SQL are similar, you'd think, but the different ethos and editing environment of Snowflake versus SQL Server make the switch surprisingly difficult, as this blog explains. How does Snowflake compare to Microsoft's T-SQL for SQL Server? Differences in SQL You write Snowflake in a very different environment Relationships and primary keys don't exist in Snowflake Some SQL programming differences (this blog)

How does Snowflake compare to Microsoft's T-SQL for SQL Server?

Part five of a five-part series of blogs

All dialects of SQL are similar, you'd think, but the different ethos and editing environment of Snowflake versus SQL Server make the switch surprisingly difficult, as this blog explains.

How does Snowflake compare to Microsoft's T-SQL for SQL Server?
Differences in SQL
You write Snowflake in a very different environment
Relationships and primary keys don't exist in Snowflake
Some SQL programming differences (this blog)

Posted by Andy Brown on 28 January 2026

In this blog

Snowflake stored procedure languages
Snowflake table functions

The more advanced the SQL you write, the greater the differences you'll come across between T-SQL and Snowflake SQL. This final part of our blog gives a couple of examples of where the two languages diverge.

Snowflake stored procedure languages

There are two big differences when writing SQL stored procedures in Snowflake. One is that a stored procedure must return a defined table of data - something like this procedure to list all of the books written by an author with a given last name:

CREATE OR REPLACE PROCEDURE GetBooksByAuthorLastName(p_last_name VARCHAR)

RETURNS TABLE (

AuthorId NUMBER,

FirstName VARCHAR,

LastName VARCHAR,

BookId NUMBER,

Title VARCHAR

)

LANGUAGE SQL

BEGIN

RETURN TABLE (

SELECT

a.AuthorId,

a.FirstName,

a.LastName,

b.BookId,

b.Title

FROM Author a

JOIN Book b

ON b.AuthorId = a.AuthorId

WHERE a.LastName = p_last_name

ORDER BY a.FirstName, b.Title

);

END;

$$;

The other big difference is that you can use 3 different languages to write stored procedures in Snowflake:

Language	Notes
SQL	The example stored procedure above is written in Snowflake SQL.
JavaScript	You can construct and execute dynamic SQL to return a set of results without having to define it, and include loops, branching and error handling.
Python	Python uses Snowpark within Snowflake, giving you access to all of the analytics and data-handling ability of Python programs.

Snowflake table functions

Snowflake functions returning tables of data are much more restricted than their T-SQL equivalents, but this is probably a good thing. In particular table-valued functions in Snowflake can't include any additional programming.

The reason I say that this is probably a good thing is that it makes the functions run much more quickly - in effect they can be treated just like views (although I'm sure that's not quite what's going on behind the scenes!).

Here's an example of a Snowflake table function to return all of the books written by an author with a given surname:

CREATE OR REPLACE FUNCTION BooksByAuthorLastName(p_last_name VARCHAR)

RETURNS TABLE (

AuthorId NUMBER,

FirstName VARCHAR,

LastName VARCHAR,

BookId NUMBER,

Title VARCHAR

)

LANGUAGE SQL

SELECT

a.AuthorId,

a.FirstName,

a.LastName,

b.BookId,

b.Title

FROM Author a

JOIN Book b

ON b.AuthorId = a.AuthorId

WHERE a.LastName = p_last_name

ORDER BY a.FirstName, b.Title

$$;

The results of the function can then be treated as a normal table:

SELECT CooperBooks.*

FROM TABLE(BooksByAuthorLastName('Cooper')) as CooperBooks

What all of the above shows is that - if you're transitioning from T-SQ - there's not much to worry you about progamming in Snowflake SQL!

Parts of this blog
How does Snowflake compare to Microsoft's T-SQL for SQL Server? Differences in SQL You write Snowflake in a very different environment Relationships and primary keys don't exist in Snowflake Some SQL programming differences (this blog)

Some other pages relevant to these blogs include:

Courses in Snowflake

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This blog has 0 threads

Add a new post

ALL BLOGS

SNOWFLAKE BLOGS

SNOWFLAKE BLOGS

Snowflake stored procedure languages

Snowflake table functions

Head office

London

Manchester