SQL Talk

 the main difference between ROWS and RANGE is in the way duplicate rows are treated. ROWS treat duplicates as distinct values, where as RANGE treats them as a single entity.

 Similarities between RANK, DENSE_RANK and ROW_NUMBER functions

• Returns an increasing integer value starting at 1 based on the ordering of rows imposed by the ORDER BY clause (if there are no ties)
• ORDER BY clause is required
• PARTITION BY clause is optional
• When the data is partitioned, the integer value is reset to 1 when the partition changes

Insert Into Employees Values (4, 'Sara', 'Female', 5000)

Insert Into Employees Values (5, 'Todd', 'Male', 3000)

Notice that no two employees in the table have the same salary. So all the 3 functions RANK, DENSE_RANK and ROW_NUMBER produce the same increasing integer value when ordered by Salary column.

SELECT Name, Salary, Gender,

ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber,

RANK() OVER (ORDER BY Salary DESC) AS [Rank],

DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank

FROM Employees

You will only see the difference when there ties (duplicate values in the column used in the ORDER BY clause).

Now let's include duplicate values for Salary column. 

To do this 
First delete existing data from the Employees table
DELETE FROM Employees

Insert new rows with duplicate valuse for Salary column

Insert Into Employees Values (1, 'Mark', 'Male', 8000)

Insert Into Employees Values (2, 'John', 'Male', 8000)

Insert Into Employees Values (3, 'Pam', 'Female', 8000)

Insert Into Employees Values (4, 'Sara', 'Female', 4000)

Insert Into Employees Values (5, 'Todd', 'Male', 3500)

At this point data in the Employees table should be as shown below


Notice 3 employees have the same salary 8000. When you execute the following query you can clearly see the difference between RANK, DENSE_RANK and ROW_NUMBER functions.

SELECT Name, Salary, Gender,

ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber,

RANK() OVER (ORDER BY Salary DESC) AS [Rank],

DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank

FROM Employees

Difference between RANK, DENSE_RANK and ROW_NUMBER functions

• ROW_NUMBER : Returns an increasing unique number for each row starting at 1, even if there are duplicates.
• RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows, but the next row after the duplicate rows will have the rank it would have been assigned if there had been no duplicates. So RANK function skips rankings if there are duplicates.
• DENSE_RANK : Returns an increasing unique number for each row starting at 1. When there are duplicates, same rank is assigned to all the duplicate rows but the DENSE_RANK function will not skip any ranks. This means the next row after the duplicate rows will have the next rank in the sequence.

INSERT INTO SELECT Statement In SQL Server

INSERT INTO SELECT statement in SQL Server is used to copy the data from the source table and insert it into the target table. But, before copying the data, the target table must exist in the database.

Key Points

• The INSERT INTO SELECT statement requires the target table to exist in the database before data can be transferred from the source table to the target table.
• The data types in the source and target tables must match for the INSERT INTO SELECT statement to work.

Syntax

INSERT [TOP (expression [PERCENT])]
INTO <target_table> (<column_name>)
SELECT * FROM <source_table>
[WHERE condition]

Note:

The TOP clause allows you to specify the number of rows returned by the query to insert into the target table. If you use the PERCENT option, the statement will insert a percentage of the rows instead.

SELECT INTO Statement In SQL Server

The SELECT INTO statement in SQL Server is used to copy data from one table to a new table.

Key Points

• The new table does not need to exist in the database to transfer data from the source table.
• A new table with the structure is automatically created while executing this command.
• The new table will be created with the column names and types defined in the source table.
• Users can also create new column names using the "AS" clause.
• SELECT INTO statement cannot be used to insert data into an existing table.
• SELECT INTO statement does not copy constraints such as primary key and indexes from the source_table to the new_table.

Syntax

SELECT <select_list>
INTO <new_table>
FROM <source_table>
[WHERE condition]


The SELECT INTO statement in SQL Server, selects data from one table and inserts it into a new table.

SELECT INTO statement in SQL Server can do the following
1. Copy all rows and columns from an existing table into a new table. This is extremely useful when you want to make a backup copy of the existing table.

2. Copy all rows and columns from an existing table into a new table in an external database.

3. Copy only selected columns into a new table

4. Copy only selected rows into a new table

5. Copy columns from 2 or more table into a new table

6. Create a new table whose columns and datatypes match with an existing table. 

7. Copy all rows and columns from an existing table into a new table on a different SQL Server instance. For this, create a linked server and use the 4 part naming convention

Please note : You cannot use SELECT INTO statement to select data into an existing table. For this you will have to use INSERT INTO statement.

 What is the use of MERGE statement in SQL Server

2. Target Table - The table that require changes (Inserts, Updates and Deletes)

The merge statement joins the target table to the source table by using a common column in both the tables. Based on how the rows match up as a result of the join, we can then perform insert, update, and delete on the target table. 

Merge statement syntax
MERGE [TARGET] AS T
USING [SOURCE] AS S
   ON [JOIN_CONDITIONS]
 WHEN MATCHED THEN 
      [UPDATE STATEMENT]
 WHEN NOT MATCHED BY TARGET THEN
      [INSERT STATEMENT] 
 WHEN NOT MATCHED BY SOURCE THEN
      [DELETE STATEMENT]



Example 1 : In the example below, INSERT, UPDATE and DELETE are all performed in one statement

1. When matching rows are found, StudentTarget table is UPDATED (i.e WHEN MATCHED)

2. When the rows are present in StudentSource table but not in StudentTarget table those rows are INSERTED into StudentTarget table (i.e WHEN NOT MATCHED BY TARGET)

3. When the rows are present in StudentTarget table but not in StudentSource table those rows are DELETED from StudentTarget table (i.e WHEN NOT MATCHED BY SOURCE)

Create table StudentSource

(

     ID int primary key,

     Name nvarchar(20)

)

GO

Insert into StudentSource values (1, 'Mike')

Insert into StudentSource values (2, 'Sara')

GO

Create table StudentTarget

(

     ID int primary key,

     Name nvarchar(20)

)

GO

Insert into StudentTarget values (1, 'Mike M')

Insert into StudentTarget values (3, 'John')

GO

MERGE StudentTarget AS T

USING StudentSource AS S

ON T.ID = S.ID

WHEN MATCHED THEN

     UPDATE SET T.NAME = S.NAME

WHEN NOT MATCHED BY TARGET THEN

     INSERT (ID, NAME) VALUES(S.ID, S.NAME)

WHEN NOT MATCHED BY SOURCE THEN

     DELETE;

Please Note : Merge statement should end with a semicolon, otherwise you would get an error stating - A MERGE statement must be terminated by a semi-colon (;)

In real time we mostly perform INSERTS and UPDATES. The rows that are present in target table but not in source table are usually not deleted from the target table.

SQL Server Merge Command Key Points

• The MERGE SQL statement requires a semicolon (;) as a statement terminator. Otherwise, Error 10713 is raised when a MERGE statement is executed without the statement terminator.
• When used after MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted to the client.
• At least one of the three MATCHED clauses must be specified when using MERGE statement; the MATCHED clauses can be specified in any order. However, a variable cannot be updated more than once in the same MATCHED clause.
• Of course, it's obvious, but just to mention, the person executing the MERGE statement should have SELECT permission on the SOURCE table and INSERT, UPDATE and DELETE permissions on the TARGET table.
• MERGE statement improves the performance as all the data is read and processed only once whereas in previous versions three different statements have to be written to process three different activities (INSERT, UPDATE or DELETE) in which case the data in both the source and target tables are evaluated and processed multiple times; at least once for each statement.
• MERGE statement takes the same kind of locks minus one Intent Shared (IS) Lock that was due to the SELECT statement in the 'IF EXISTS' as we did in previous versions of SQL Server.
• For every INSERT, UPDATE, or DELETE action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee which action to fire triggers first or last. Triggers defined for the same action honor the order you specify.