Posts

Snowflake Vs MS-SQL - Part 85 - UNION ALL BY NAME

Image
        UNION ALL BY NAME Sometimes, we have to combine data from multiple tables. In this case, we can use UNION or UNION ALL. However, the catch is that the column order or the number of columns in both tables must be the same. MS-SQL Server   We can achieve this  using  UNION  as shown below,  specifying the column names. We cannot use (*) in the select clause as the number of columns and their names are different: In  Snowflake ,  we can use  the same query as above; however, we can use the  (*) in the select clause  with  UNION ALL BY NAME   as below. The order of the columns in the  select clause does not matter. For more details, you can refer to the following:  UNION_ALL_BY_NAME . Yogesh Shinde LinkedInProfile <<< Back   Next >>>    
Post a Comment
Read more

Snowflake Vs MS-SQL - Part 84 - EXTRACT Day/Month/Year

Image
            EXTRACT Day/Month/Year Sometimes you have to extract Day or Month, or Year from a Date. MS-SQL Server   We can achieve it as follows: In  Snowflake,  we can use a similar approach; however, we can use  EXTRACT  as follows: Note: You can use EXTRACT to convert the timestamp to its  Unix epoch  time  representation as below: For more details, refer to the  EXTRACT  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>>
Post a Comment
Location: Pune, Maharashtra, India
Read more

Snowflake Vs MS-SQL - Part 83 - Compare NULL - IS NOT DISTINCT FROM

Image
            Compare NULL - IS NOT DISTINCT FROM Sometimes you have to compare with NULL in the WHERE clause. MS-SQL Server   We can achieve it as below: In  Snowflake,  we can use a similar approach; however, we can use  IS NOT DISTINCT FROM   as below: For more details, refer to the  IS_NOT_DISTINCT_FROM  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>>    
Post a Comment
Location: Pune, Maharashtra, India
Read more

Snowflake Vs MS-SQL - Part 82 - Generate Unique Identifier

Image
            Generate Unique Identifier Sometimes, we need to generate a unique identifier and use it consistently throughout our code. MS-SQL Server   We can achieve it by using built-in functions as follows: In  Snowflake,  we can use a similar function,  UUID_STRING (),  as below : For more details, refer to the  UUID_STRING  section. Yogesh Shinde LinkedInProfile   <<< Back   Next >>>  
Post a Comment
Location: Pune, Maharashtra, India
Read more

Snowflake Vs MS-SQL - Part 81 - First Day Of The Month - DATE_TRUNC

Image
            First Day Of The Month Suppose you calculate the first day of the month based on today's date. MS-SQL Server   We can achieve it by using built-in functions as below: In  Snowflake,  we can use a similar approach; however, we can use  DATE_TRUNC   as  below: This can also be used for Year & Week.  For more details, refer to the  DATE_TRUNC  section . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  
Post a Comment
Location: Pune, Maharashtra, India
Read more

Snowflake Vs MS-SQL - Part 80- WHERE clause for multiple columns

Image
            WHERE clause for multiple columns Suppose you want to filter the data of a table, then you need to use multiple columns in the WHERE clause to achieve the expected result. MS-SQL Server   We can achieve it by using the WHERE clause along with AND: In  Snowflake,  we can use a similar approach, and multiple columns can be clubbed as  below: For more details, refer to the WHERE  section . Yogesh Shinde LinkedInProfile <<< Back   Next >>>
Post a Comment
Location: Pune, Maharashtra, India
Read more

Snowflake Vs MS-SQL - Part 79 - Adding a comment to the Column of a Table

Image
            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 >>>   
Post a Comment
Location: Pune, Maharashtra, India
Read more

Snowflake Vs MS-SQL - Part 78 - Duplicate Rows Identification - Group By ALL

Image
         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 >>>  
Post a Comment
Location: Pune, Maharashtra, India
Read more

Snowflake Vs MS-SQL - Part 77 - Remove Duplicates From String

Image
           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 >>>   
Post a Comment
Location: Pune, Maharashtra, India
Read more

Snowflake Vs MS-SQL - Part 76 - COLLATE - Comparing Text Strings

Image
           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 >>>  
Post a Comment
Location: Pune, Maharashtra, India
Read more

Snowflake Vs MS-SQL - Part 75 - String Length and Trimmed Length

Image
           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 >>>
Post a Comment
Read more

Snowflake Vs MS-SQL - Part 74 - Convert Column Into Comma Separated

Image
          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 >>>
Post a Comment
Read more

Snowflake Vs MS-SQL - Part 73 - First Coming Sunday

Image
          First Coming Sunday Sometimes you have to calculate first for the coming Sunday. You must use that date for further calculations or filtering. MS-SQL Server   We can achieve this as follows: In  Snowflake,  we can use a similar approach  as below.  For more details, you can refer to the following:  NEXT_DAY . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  
Post a Comment
Location: Pune, Maharashtra, India
Read more

Snowflake Vs MS-SQL - Part 72 - First Sunday Of The Month

Image
         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 >>>  
Post a Comment
Location: Pune, Maharashtra, India
Read more

Snowflake Vs MS-SQL - Part 71 - Data Is Numeric Or NOT

Image
         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 >>>  
Post a Comment
Location: Pune, Maharashtra, India
Read more