Tech-Discussion

Hierarchical Data Suppose you have data in a table for lineage  MS-SQL Server   When you want to represent data hierarchically then you can use the self-join query as below. In  Snowflake  we can achieve this by using the same query as above. Also, there is a new sub-clause of the FROM clause known as -  CONNECT BY . This Joins a table to itself to process hierarchical data in the table. Also, this clause can be displayed in the form of a path.   For more details, you can refer to the following  LINK  & also refer to  LINK  for additional hierarchical queries. Yogesh Shinde LinkedInProfile <<< Back   Next >>>

    Undrop - Recover objects MS-SQL Server   When you are working on a critical database and someone drops one of the tables by mistake, it is a tough task to recover the deleted table.  There could be multiple options to recover the deleted table. If you have taken the copy of the table then you can rename it and recover it. But if it is a transactional table then there is high chance latest records may not be present and you can lose them. If you are taking back-ups on short intervals then you have to restore that table. Also, there could be other alternatives.  In each, recovering the deleted table definitely, it is time-consuming. In  Snowflake  we can achieve this by using the simple command - Undrop . Note: A table can be restored only if the object was deleted within the Data Retention Period. If a table with the same name already exists, an error is returned. For more details, you can refer to the following  LINK . Yogesh Shinde LinkedInPr...

       Single level IIF MS-SQL Server   If you want to return based on some condition, we go with case statement.  In  Snowflake  we can achieve this by using same query as above. However there is an alternative solution as below: You can use this in nesting format as well: For more details, you can refer the following  LINK . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

      Count based on condition MS-SQL Server   If you want to take a count based on condition then you have to use where clause.  Scenario 1: Scenario 2: In  Snowflake  we can achieve this by using same queries as above. However there is an alternative solution as below: Scenario 1: Scenario 2: I have not compared the performance, if anyone knows then please comment. For more details, you can refer the following  LINK . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

      The value of another column for MAX value Suppose you have a data as below and if you want to get the details of another columns like employee_id based on MAX salary. MS-SQL Server   You can achieve this by writing query in multiple ways. One of the ways is writing sub query.  There is NO direct way to achieve this. However in  Snowflake  we can achieve this by using Aggregate function MAX_BY . This query will return a record Employee_id = 900. But there is a catch in this function, if there more than one row contains the maximum value for the salary column, the function is non-deterministic and might return the employee ID for a different row in subsequent executions. For more details, you can refer the following  LINK . On similar line there is function MIN_BY . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

      IF NOT EXISTS at Column Level  During your development, sometimes you may need to add NEW column in the existing table. As a common practise, you first need to confirm that column with same name does not exist. MS-SQL Server   You can achieve this by checking the column name and if not exists then add new column. However in  Snowflake  we can achieve this by using check in ALTER command itself. For more details, you can refer the following  LINK . Similarly, we can use to drop an existing column. The same command works in  MS-SQL Server. Yogesh Shinde LinkedInProfile <<< Back   Next >>>

     MAX among columns  Suppose if you want to get the maximum value among multiple columns.   In this case, among columns Column_Date1 & Column_Date2, we want the maximum to be selected.   MS-SQL Server   You can NOT achieve this directly but using little bit tricky way. However in  Snowflake  we can achieve this by using Conditional Expression Functions like GREATEST . Note: If one of the columns contains NULL then it returns the NULL value. To overcome this, you can use variation of this  GREATEST_IGNORE_NULLS. For more details, you can refer the following  LINK . On similar line, you use LEAST for minimum value among multiple columns. Yogesh Shinde LinkedInProfile <<< Back   Next >>>

     Top n rows    MS-SQL Server   Suppose if you want to pull only limited data then you can use TOP N clause. However in  Snowflake  we can achieve this by two ways. 1. Top clause:           This is similar to MS-SQL Server.           For more details, you can refer the following LINK .  2. Limit clause :      You can mention this clause at the end of SQL Statement.           This LIMIT clause provides you more flexibility if you use  OFFSET  along with it.       For more details, you can refer the following  LINK . But if you try to use both of these clauses in single SQL statement then it throws a compilation error( Duplicate LIMIT ). Yogesh Shinde LinkedInProfile <<< Back   Next >>>

     Truncate and Insert    MS-SQL Server   Suppose if you want to Insert new set of data in a table and prior to that if you want to remove all records then you can achieve this in two steps. 1. First Truncate/Delete all records from the table. 2. Insert new records into the table. However in  Snowflake  we can achieve this in single step. Insert statement is having an optional parameter OVERWRITE . This parameter specifies that the target table should be truncated before inserting the values into the table. For more details, you can refer the following  LINK . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

    Temporary Tables    MS-SQL Server   When you create temporary table, you can NOT create with same name as that of main table. In SQL, you have to use # or ## to create temporary table. However  Snowflake  does allow to create temporary table with same name as that of main table. Consider you have a table "Main_Table" under DEV schema say with 1000 records and if you create temporary table without records then temporary table gets created. When you fire a select query as below, it returns zero records. This means it pulls the records from newly created temporary table and not from the main table. If you create a view on this newly created temp table and fire select statement on the view then you  get zero records. If you move to different session/worksheet and fire select statement on view then you get actual count of the table. Because temporary table's scope is for a particular session. And finally, when you drop this table, temporary table g...

   Trailing Comma in Select Statement   MS-SQL Server  does NOT support trailing Comma in SELECT Statement. If you try to run the query with trailing comma at the end, then you may get an error like " Incorrect syntax near the keyword 'from'. " However Snowflake  does support trailing Comma in SELECT Statement. If you try to run the above query with trailing comma at the end, it will NOT throw an error and will pull result. You can read more about this at  Trailing_Comma_Select  Yogesh Shinde LinkedInProfile <<< Back   Next >>>

  Different Versions of Stored Procedure I've spent a lot of time developing MS-SQL Server code. After I started working on Snowflake, I experienced a different approach to the same thing. Here I am trying to list various aspects of Snowflake compared to MS-SQL Server. In MS-SQL Server, with same name you can create SP which will return two different results based on the call.  It is not overloading. A. First Version B. Second Version   When you will execute this SP, you will get the output similar like this:  Code inside Stored Procedure will look like this:   If you search under Object Explorer, you will find only one instance. In Snowflake, you can create two instances of Stoerd Procedure with same name A. First Version B. Second Version      When you will execute these SPs, you will get the output similar like this:     If you search under Object Explorer, you will find two instances.   Pl...