Tech-Discussion

Error Handling - DML When you run bulk DML and a row's data doesn't match the table structure, the statement fails. MS-SQL Server We can see it here; this statement will fail with " Cannot insert the value NULL into column does not allow nulls" or " Conversion failed when converting". As a result, zero records will be inserted. In Snowflake, we can use ERROR_LOGGING as follows; as a result, two records with errors will be skipped, and a single record will be inserted: Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series              

Row-Timestamp Last Updated To support auditing and tracking requirements, the transactional table should include a column that records the date and time of record creation and subsequent updates. MS-SQL Server We can achieve it by creating  an additional column as follows: In Snowflake, we can use a similar approach as above; however, we can make use of a Row-Timestamp as follows: You can access the same column as below: For more details, refer to the Row-Timestamp section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series                

Recursive View - Hierarchy Sometimes data is stored in a hierarchical structure within a table. To display the complete lineage or parent-child relationship, you can use a recursive query. MS-SQL Server We can achieve it by creating a CTE as follows: In Snowflake,   we can use a similar approach as above; however, we can make use of a  Recursive View  as follows: For more details, refer to the Recursive_View section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series              

Period Data Type In the warehouse, you usually save a record with start_date and end_date. If you want to filter a record based on these dates, then you have to use both date columns. MS-SQL Server We can use a WHERE clause as below: In Snowflake,   we can use a similar approach as above; however,  we can use the PERIOD DATA TYPE  as follows: For more details, refer to the Period-DataType  or  Period-Function   sections. You can see multiple use cases for the PERIOD data type. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series              

Clear the cache When you are working on query performance, you have to clear the cache. MS-SQL Server We can achieve it as follows: In Snowflake,   we can use ALTER_SESSION or ALTER WAREHOUSE  as follows: Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series          

Show Columns of a Table Sometimes, we need to check the columns of a particular table. MS-SQL Server We can achieve it as follows: In Snowflake , we can  use SHOW_COLUMNS  as follows: For more details, refer to the SHOW_COLUMNS section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series        

Extract a Year from the DATE Sometimes, we need to extract the year component from a given date. MS-SQL Server We can achieve it as follows: In Snowflake , we use EXTRACT as follows: For more details, refer to the EXTRACT section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series      

Parse and Save XML Sometimes, we need to parse an XML file and save it in a table. MS-SQL Server We can achieve it as follows: In Snowflake , we use PARSE_XML  as follows: For more details, refer to the PARSE_XML section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series      

Convert Data Type - Decimal to Integer Sometimes, for various reasons, we need to convert decimal values to integer values. MS-SQL Server We can achieve it as follows: In Snowflake , we use EXTRACT as follows: Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series      

Filter Columns in Result Grid When the result set contains numerous columns, it becomes challenging to identify the columns we need. MS-SQL Server The result grid does not allow us to select only the required columns. As a workaround, we need to explicitly limit the columns in the SELECT clause. In Snowflake , we use the filter provided in the Result Grid  as follows: Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series      

Create or Alter Table - Add Column You design a table to meet your current requirements, and after a few days, you might need to add an additional column to the existing table. MS-SQL Server We can achieve it by altering the table structure as follows: In Snowflake ,   we can use a similar approach as above; however, we can make use of ALTER  as follows: For more details, refer to the ALTER section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series    

Table Structure - Describe  Sometimes, we need to look at how a table is arranged to understand it better. MS-SQL Server We can achieve it as follows: In Snowflake , we use DESC  as follows: For more details, refer to the DESC section. Yogesh Shinde LinkedInProfile <<< Back Next >>> Snowflake Vs MS-SQL - Series  

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