Tech-Discussion

         Derived Column Sometimes, you use two columns to derive values from them, and you need to filter the data based on this derived value. MS-SQL Server   We can achieve this by an additional level of SELECT as below: However, Snowflake allows you to use a derived column in the WHERE clause as below: Yogesh Shinde LinkedInProfile <<< Back   Next >>>   

        Data Type in Grid You regularly run a query and the result comes in the grid below. Sometimes you need to know the datatype of any column. MS-SQL Server   The grid which shows the data can't display the data type of that column. You have to go back to the table structure to see the data type of that column. However, the  Snowflake  grid shows datatype in symbolic ways as below: Yogesh Shinde LinkedInProfile <<< Back   Next >>>   

       Divide by Zero Sometimes in calculation, if zero comes at the denominator place, it throws an error like Divide by Zero.   MS-SQL Server   You can find the workaround as below : However, in  Snowflake  we can use  DIV0  as below. For more details, you can refer to the following  DIV0   and  DIV0NULL . Yogesh Shinde LinkedInProfile <<< Back   Next >>>   

       GET_DDL: View Code Sometimes we need to see the code of any object like Stored Procedure, View, or any other object.   MS-SQL Server   You can achieve this by using Object Explorer and searching for a particular object. You can achieve the same thing by using the code below: However, in  Snowflake  we can use the code as below . For more details, you can refer to the following  GET_DDL . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

      Number Generator Sometimes you need to generate numbers in sequential orders as below:   MS-SQL Server   You can achieve this by using multiple ways, one of them is using CTE . However, in  Snowflake  we can achieve this by using a  Generator. For more details, you can refer to the following  Generator . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

     Affected Rows - SQLROWCOUNT DML commands are part of our day-to-day life. After DML command execution if you want to get the number of records executed you can get it by row count. In this case, you are inserting 6 records in a table and you should get that count  i.e. six.         INSERT INTO Main_Table ( store_ID , province , profit )         VALUES                 ( 1 , 'Ontario' , 500 ),                 ( 2 , 'Saskatchewan' , 500 ),                 ( 3 , 'Ontario' , 450 ),                 ( 4 , 'Ontario' , 450 ),                 ( 5 , 'Saskatchewan' , 450 ),                 ( 6 , 'USA' , 650 )                   ;   MS-SQL Server ...

                  DATA TYPE CONVERSION Many times you need to convert data from one data type to another data type.  Suppose you have string ' 2025-01-01 ' and want to convert it into DATE Format. MS-SQL Server We can achieve this by using the  CAST() OR CONVERT() functions as below. In  Snowflake,  we can use a similar approach and also other functions are available.   For more details, you can refer to the following  CONVERSIONS . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

                 POSITION If you want to search for a single character or group of characters in another string. ------------------------------------+------------+   Available_String                   | Position_1 | ------------------------------------+------------+   nevermore1 , nevermore2 , nevermore3 .|   1         |   -----------------------------------+------------+ In this case, we want to search " nevermore " in the string " nevermore1, nevermore2, nevermore3 .". It should return the position as 1. MS-SQL Server We can achieve this by using the  CHARINDEX()  as below. To get the second occurrence, we need to use the third parameter of CHARINDEX() as CHARINDEX('nevermore', province,5). In  Snowflake,  we can use a similar approach, which is  REGEXP_INSTR () .  This gives you the flexibility to find a second occurre...

                RANDOM Your table has many records and if you want to sort them randomly. MS-SQL Server   We can achieve this by using the NEWID() as below. In  Snowflake,  we can use a similar approach by using  RANDOM() .  For more details, you can refer to the following  RANDOM . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

                CONTAINS If your table column has many records and you want to filter the data based on particular letter(s) consisting of. Eg. it should contain " tea " Actual Data: ------------+   DRINK_NAME | ------------+   coffee     |   ice tea    |   latte      |   tea        |  [ NULL ]     | ------------+ Expected Data: ------------+   DRINK_NAME | ------------+   tea        |   ice tea    | ------------+ MS-SQL Server   We can achieve this by using the LIKE operator as below. In  Snowflake,  we can use a similar approach as above.  However, Snowflake has an additional operator  CONTAINS .  For more details, you can refer to the following  CONTAINS . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

               ORDER BY NULL If your table column is NULLABLE and contains NULL values then when we sort the data, NULL values come at the top by default. Actual Data: Expected Data: MS-SQL Server   We can achieve customized sorting as below. In  Snowflake,  we can use a similar approach as above.  However, Snowflake has an additional clause in  ORDER BY.  For more details, you can refer to the following  ORDER BY . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

               STARTSWITH If your table column has many records and you want to filter the data based on starting letter(s). Actual Data: ------------+   DRINK_NAME | ------------+   coffee     |   ice tea    |   latte      |   tea        |  [ NULL ]     | ------------+ Expected Data: ------------+   DRINK_NAME | ------------+   tea        | ------------+ MS-SQL Server   We can achieve this by using the LIKE operator as below. In  Snowflake,  we can use a similar approach as above.  However, Snowflake has an additional operator  STARTSWITH .  For more details, you can refer to the following  STARTSWITH . Note: Similarly , you can use  ENDSWITH Yogesh Shinde LinkedInProfile <<< Back   Next >>>

              LATERAL JOIN We often need to use different JOINS along with joining conditions to pull data from multiple tables. MS-SQL Server   We can use any JOIN, here I have used INNER JOIN. For any join, you must mention columns in which you will join both tables. In  Snowflake,  we can use a similar approach as above.  However, Snowflake has an additional JOIN called  LATERAL JOIN.  This looks like a sub-query. You can use lateral join along with other joins like inner join & outer join. For more details, you can refer to the following  LATERAL JOIN . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

             NATURAL JOIN To pull data from multiple tables, we often need to use different JOINS along with joining conditions. MS-SQL Server   We can use any JOIN, here I have used INNER JOIN. For any join, you must mention columns in which you will join both tables. In  Snowflake,  we can use a similar approach as above.  However, Snowflake has an additional JOIN called  NATURAL JOIN.  You can use this clause when both tables have a column with the same name. In this case, Store_ID is present in both tables.  You don't need to mention the column name when you are joining both tables.  For more details, you can refer to the following  NATURAL JOIN . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

             Using Many times to pull data from multiple tables we need to use different JOINS along with joining conditions. MS-SQL Server   We can use any JOIN, here I have used INNER JOIN. For any join, you have to mention columns in which you will join both tables. In  Snowflake,  we can use a similar approach as above.  However, Snowflake is a clause  USING.  You can use this clause when both tables have a column with the same name. In this case, Store_ID is present in both tables.  For more details, you can refer to the following  USING . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

            DUPLICATE_COUNT If you want to determine how many duplicate groups are present under any particular column. In the dataset below, Ontario presents 3 times, Saskatchewan presents 2 times, and LA only once.  It shows  Ontario  &  Saskatchewan  are duplicate values, and we expect 2 as duplicate groups. + ----------+--------------+ | STORE_ID | Province     | | ----------+--------------+ |         1 | Ontario      | |         2 | Saskatchewan | |         3 | Ontario      | |         4 | Ontario      | |         5 | Saskatchewan | |         6 | LA           | + ----------+--------------+ MS-SQL Server   Using the aggregate function we can achieve as below: In  Snowflake,  we can use a similar appr...