Tech-Discussion

            Adding a comment to the Column of a Table Suppose you are designing a new table, and you want to add a comment for the columns of that table, so that whoever uses this table will get some idea of the columns of this table. MS-SQL Server   We can achieve it by using a system-level stored procedure( sp_addExtendedProperty ), which uses Extended properties to allow you to store custom metadata: In  Snowflake,  we can use a similar approach by using the  COMMENT  clause as  below: For more details, refer to  COMMENT . Yogesh Shinde LinkedInProfile <<< Back   Next >>>   

         Duplicate Rows Identification – Group By ALL If you want to identify total identical rows (i.e., all column values are the same) from a table. MS-SQL Server   We can achieve the above-expected result by any one of the approaches below: In  Snowflake,  we can use all the above queries; however, we can use   GROUP BY ALL  as below. This is more beneficial when the table has so many columns; you don't need to mention all columns in the GROUP BY clause. For more details, you can refer to the following:  GROUP_BY_ALL . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

           Remove Duplicates From String Suppose you have a string that contains values separated by a delimiter, and you have to remove those duplicates. -- Actual String 'A, B, C, a, b' -- Expected String 'A, B, C' MS-SQL Server   We can achieve it by using functions like  STRING_AGG & STRING_SPLIT : In  Snowflake,  we can use a similar approach by using  LISTAGG & SPLIT_TO_TABLE,  as below For more details, refer to  LISTAGG  &  SPLIT_TO_TABLE . Yogesh Shinde LinkedInProfile <<< Back   Next >>>   

           COLLATE - Comparing Text Strings Suppose you want to compare the string using case sensitivity. When  comparing " HELLO " with " hello ", it should not  match. MS-SQL Server    We can achieve it by using different collation SQL_LATIN1_GENERAL_CP1_CS_AS : However, in  Snowflake,  we can use a similar approach by using  COLLATE,  as below: Note: CS used here for case-sensitive. For case-insensitive, you can use CI. For more details, you can refer:  COLLATE . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

           String Length and Trimmed Length Suppose you want to calculate the length of a string that contains trailing spaces. MS-SQL Server   We can achieve it by using functions like LEN & DATALENGTH : However, in  Snowflake,  we can use two functions,  LENGTH & RTRIMMED_LENGTH,  as below: For more details,  refer to  LENGTH  &  RTRIMMED_LENGTH . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

          Convert Column Into Comma-Separated If you have data in a table and you want to convert columnar data into a comma-separated string. Current Data: Expected Data: MS-SQL Server   We can achieve it by anyone approaching below: However, in  Snowflake,  we can use two ARRAY functions,  ARRAY_AGG & ARRAY_TO_STRING,  as below: For more details, you can refer to:  ARRAY_AGG  &  ARRAY_TO_STRING . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

         First Sunday Of The Month Sometimes, you have to calculate the first day of the month, like the first Sunday. You have to use that date for further calculation or filtering. Suppose you are running the query today, i.e., 28-Jun-2025, and if you want to know the date of the 1st Sunday of this month, i.e., 01-Jun-2025. MS-SQL Server   We can achieve this as follows: In  Snowflake,  we can use a similar approach .  For more details, you can refer to the following:  DATE_TIME . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

         Data Is Numeric Or NOT While applying your business logic, you need to check whether the data present in a particular column is Numeric or Text . Based on this, your logic will vary.   COL2       IS_NUMERIC   1           true   2           true   3 . 0         true   A           false   B           false MS-SQL Server   We can achieve this by using the function ISNUMERIC as below: In  Snowflake,  we can use a similar approach; we can use   TRY_TO_NUMBER  as below. For more details, you can refer to the following:  TRY_TO_NUMBER . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

        UNION By Name Sometimes, we have to combine data from multiple tables. In this case, we can use UNION. However, the catch is that the order of the columns should be the same. MS-SQL Server   We can achieve this by using UNION as below: In  Snowflake,  we can use  the same query as above; however, we have to use the same column order in the select clause. However, we can use  UNION BY Name   as below. The order of the columns in the  select clause does not matter. NOTE: Similarly, you can use  UNION ALL BY NAME . For more details, you can refer to the following:  UNION . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

         Flow Operator To Save Data Temporary Many times you have to execute multiple SQL statements and you have to save the result-set temporary which will be used in next SQL statement. MS-SQL Server   We can achieve this by using temporary tables. Also you can use CTE as below: In  Snowflake,  we can use similar approach, however it has an additional operator  Flow Operators,  as below: For more details, you can refer:  FLOW_OPERATOR  &  ARRAY_TO_STRING . Yogesh Shinde LinkedInProfile <<< Back   Next >>>          

         Multi Table Insert We insert data into a table, and sometimes, we must archive/back it up in another table. MS-SQL Server   We can achieve this by using magic or virtual tables present, like INSERTED,  as below: However, in  Snowflake, the insert statement has an additional clause,  INSERT ALL,  which allows you to insert into multiple tables:  For more details, you can refer to the following:  Multi_Table_Insert . Yogesh Shinde LinkedInProfile <<< Back   Next >>>        

         Set Multiple Variables Sometimes we get the values from table and assign to multiple variables. MS-SQL Server   We can achieve this as below: However, in  Snowflake,  this can be achieved in little bit different way as below:  Yogesh Shinde LinkedInProfile <<< Back   Next >>>       

          IS DISTINCT FROM In some cases, we need to get the records that are NOT mapping as below: And you are expecting the result as below: MS-SQL Server   We can achieve this by using INNER JOIN as below: However, in  Snowflake,  this can be achieved in the same way as above; however, you can use the  IS DISTINCT FROM   command as below:  For more details, you can refer to the following:  IS DISTINCT FROM . Yogesh Shinde LinkedInProfile <<< Back   Next >>>      

          SELECT records - TABLE To see the records from any table, you need to use the SELECT command. MS-SQL Server   If we want to fetch ALL records, then you can use the SELECT command as below: However, in  Snowflake,  this can be achieved in the same way as above; however, you can  use the  TABLE  command as below:  Yogesh Shinde LinkedInProfile <<< Back   Next >>>     

          ALTER Table ADD Column Business requirements change during development or even after the project goes live, and you want to add an additional column to the existing table. MS-SQL Server   If we want to add a new column to the existing table, it is always better to check whether it exists. For this, in SQL, you have to perform 2 steps as below: However, in  Snowflake,  this can be achieved in a single step.  Yogesh Shinde LinkedInProfile <<< Back   Next >>>