Tech-Discussion

Count of non-NULL records of the column using ILIKE If you want to get the Count of non-NULL records of the column. MS-SQL Server   We can achieve it as follows: In  Snowflake ,  we can use a similar approach as above; however, we can use  ILIKE  as follows: For more details, refer to the  ILIKE  section. Yogesh Shinde LinkedInProfile     <<< Back   Next >>>

Snowflake Vs MS-SQL - Series Part 0 -  Snowflake Key Concepts  -  Part 0 Part 1 -  Different Versions of Stored Procedure  -  Part 1 Part 2 -  Trailing Comma in Select Statement  -  Part 2   Part 3 -  Temporary Tables  -  Part 3   Part 4 -  Truncate and Insert  -  Part 4 Part 5 -  Top n rows  -  Part 5   Part 6 -  MAX among columns  -  Part 6   Part 7 -  IF NOT EXISTS at Column Level  -  Part 7 Part 8 -  The value of another column for the MAX value  -  Part 8   Part 9 -  Count based on condition  -  Part 9   Part 10 -  Single-level IIF  -  Part 10 Part 11 -  Undrop - Recover objects  -  Part 11   Part 12 -  Hierarchical Data and CONNECT BY   -  Part 12 Part 13 -  Create Table  -  Part 13 Part 14 -  Clone Table  -  Part 14   P...

          UTC Time - CONVERT TIMEZONE Many times you get the time inthe local time zone, but you want to display the same time in UTC Time Zone. MS-SQL Server   We can achieve it as follows: In  Snowflake ,  we can use a similar approach; however, we can use  CONVERT_TIMEZONE  as follows: For more details, refer to the  CONVERT_TIMEZONE  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>>

Modulus/Modulo Function - Remainder While calculating, if you want to find the remainder, then we can use this. MS-SQL Server   We can achieve it as follows: In  Snowflake ,  we can use a similar approach; however, we can use  MOD  as follows: For more details, refer to the  MOD  section. Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

        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 >>>    

            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 >>>

            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 >>>    

            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 >>>  

            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 >>>  

            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 >>>

            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 >>>