Tech-Discussion

PIVOT - Custom alias to column name When we are performing a PIVOT operation, we must provide aliases for the generated columns. MS-SQL Server We can achieve it as follows: In Snowflake , we use   PIVOT  as follows: For more details, refer to the PIVOT section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series

Row Comparison - HASH When we need to compare data across all columns in two particular tables. MS-SQL Server We can achieve it as follows: In Snowflake , we use HASH  as follows: For more details, refer to the HASH section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series

  ORDER BY ALL - SORTING When we fetch data, we sometimes need to sort it. If there are limited columns, then it is fine. Suppose there are multiple columns, then you have to mention the entire list of columns in an order by clause, which is a tedious job. MS-SQL Server We can achieve it as follows: In  Snowflake ,    we can use a similar approach as above; however, we can make use of  ORDER BY ALL  as follows: For more details, refer to the  ORDER BY ALL  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series    

  Last Value Whenever we are sorting out the data and want to find the last value in the series. MS-SQL Server We can achieve it as follows: In  Snowflake ,    we can use a similar approach as above; however, we can make use of  LAST_VALUE  as follows: For more details, refer to the  LAST_VALUE  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series     

Trigonometry Functions - Geometry We rarely need to use trigonometric functions during calculations. MS-SQL Server We can achieve it as follows: In  Snowflake ,   we use   SIN  as follows: For more details, refer to the  SIN  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series     

  Split String into Different Columns Suppose your application stores the name of an employee as a full name, and while retrieving, you want to separate it out as first name, middle name & last name. MS-SQL Server We can achieve it as follows: In Snowflake ,  we can make use of SPLIT_PART  as follows: For more details, refer to the SPLIT_PART section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series

Multi-Table Insert based on condition Usually, we insert the data into a single table; however, if you want to insert records into different tables based on a value. MS-SQL Server We can achieve it as follows: In  Snowflake ,   we can use  the same query as above; however, we can use   INSERT FIRST  as follows: For more details, refer to the  INSERT FIRST  section. Also, please refer to the earlier article regarding INSERT ALL -  LINK . Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series    

Create Table If Not Exists Whenever we are creating a new table, we must verify that the table is not already present. MS-SQL Server We can achieve it as follows: In  Snowflake ,    we can use it  as follows: For more details, refer to the  CREATE TABLE  section. Also, please refer to the earlier article on Create Table -  LINK . Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series  

Identify Data Type - SYSTEM$TYPEOF Sometimes, you have to find out the data type or nature of the data in the existing column during data analysis. MS-SQL Server We can do as follows: In  Snowflake ,  we can use  SYSTEM$TYPEOF  as follows: For more details, refer to the  SYSTEM_TYPEOF  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series   

Row Last Updated - ROW_TIMESTAMP Many times, you have to find out when the record was updated last time MS-SQL Server We need to create a column in a table, like modified_date, which will store the value of datetime: In  Snowflake ,  we can use a similar approach as above; however, we can use  ROW_TIMESTAMP  as follows: For more details, refer to the  ROW_TIMESTAMP  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series  

Date and Time - INTERVAL Often, you need to add a duration/interval to an existing date. MS-SQL Server We can achieve it as follows: In  Snowflake ,    we can use a similar approach as above; however, we can make use of the new data type  INTERVAL  as follows: For more details, refer to the  INTERVAL  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series

BOOLAND_AGG If you want to identify values that are the same as the given criteria. In the table below, all orders have an order_status of SHIPPED. ORDER_ID   ORDER_STATUS 1         SHIPPED 2         SHIPPED 3         SHIPPED 4         SHIPPED MS-SQL Server We can achieve it as follows: In Snowflake,  we can use  the same query as above; however, we can use   BOOLAND_AGG  as follows: For more details, refer to the  BOOLAND_AGG  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series  

MERGE ALL BY NAME If you are performing upsert operations, you need to provide a list of columns in the Update as well as the Insert clause. MS-SQL Server   We can achieve it as follows: In Snowflake, we can use  the same query as above; however, we can use   MERGE  as follows: For more details, refer to the  MERGE-By-Using-All-By-Name  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series

Stop or Cancel the Running Query While executing a query, we noticed it was taking unusually long to complete. In such situations, it’s important to know how to safely stop or cancel the execution to avoid performance bottlenecks and system impact. MS-SQL Server   We can achieve it as follows: In  Snowflake ,  we can use  CANCEL_QUERY  as follows: For more details, refer to the  CANCEL_QUERY  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series

ARRAY UNNEST - FLATTEN If you have an array saved in a table and want to generate each value in each row as below: MS-SQL Server   We can achieve it as follows: In  Snowflake ,  we can use  FLATTEN  as follows: For more details, refer to the  FLATTEN  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series

Replace the String - INSERT - STUFF If you want to delete a part of a string and then insert another string into the original string at a specified position. MS-SQL Server   We can achieve it as follows: In  Snowflake ,  we can use INSERT  as follows: For more details, refer to the  INSERT  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>> Snowflake Vs MS-SQL - Series