Tech-Discussion

Create Table When writing the code, you need to create a table on the fly many times.  MS-SQL Server   You can use the INTO clause to create a table during run time. If you want to create an empty table  i.e. with only structure   then you can mention a condition in the where clause that is NOT true as below. In  Snowflake  we can achieve this by using the Create or Replace clause. This syntax is called CTAS(Create Table As Select). To create an empty table i.e. with only structure in Snowflake we can use the  LIKE clause as below. Note: The same applies to the Temporary table. For more details, you can refer to the following  LINK . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

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