Tech-Discussion

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

            RATIO_TO_REPORT If you have a table in which amounts are present and you want to calculate the percentage distribution of those amounts as below: + ----------+--------+----------------+ | STORE_ID | PROFIT | PERCENT_PROFIT | | ----------+--------+----------------| |         1 | 500 . 00 |     42          | |         2 | 250 . 00 |     21          | |         3 | 450 . 00 |     38          | |         4 |   NULL |     NULL        | + ----------+--------+----------------+ MS-SQL Server   Using the SUM function and OVER clause, you can achieve the above output as below: In  Snowflake,  we can use a similar approach as above.  However, Snowflake has a different function  RATIO_TO_REPORT : ...

           Select data from a table If you want to select all columns from a table then we use the SELECT command.  MS-SQL Server   Using the  SELECT command we can fetch the data from the table below: In  Snowflake,  we can use a similar approach as above.  However, Snowflake has a different approach i.e you can mention the below command:  Alternatively, you can use the below command as well. For more details, you can refer to the following  Literals-Table . Yogesh Shinde LinkedInProfile <<< Back   Next >>>