Output clause will return information from or expressions based on each row affected by INSERT, UPDATE, DELETE or MERGE Statement. Output clause has access to inserted and deleted tables. This a very good feature in SQL Server started from 2005.Here I am explaining with a simple example : Inserting a row into Employee Details and displaying the record details with database user who performed this operation using output clause.
--Declaring a Temp table for Displaying who has inserted the data
CREATE TABLE #tmp
INSERT INTO dbo.employeedetails
From the name itself we can guess that SYNONYMS are alias which is pointing to the Database objects.The use of synonym is to replace Multi part name SQL Statement into Single part name SQL Statement.
Synonyms can be created on only following objects.
- Assembly Aggregate (CLR) Aggregate Functions
- Assembly (CLR) Stored Procedure
- Assembly (CLR) Table-valued Function
- Assembly (CLR) Scalar Function
- SQL Table-valued Function
- Extended Stored Procedure
- SQL Scalar Function
- SQL Inline-table-valued Function
- SQL Stored Procedure
- Table (User-defined)
Let us look the below code for more understanding.
This is a common question in Interview “find the second highest salary of Employee details table”. I hope most of my friends also have faced this question. Here I am explaining about two different methods-
• Using Row_number()
• Without using any SQL Function.
This is my Employeedetails table.
Method 1: Using Row_number()
Here I am showing third highest salary.
Method 2 – With no SQL Server Function
Another method: Suggested by Fazal – one of the regular blog reader of SQLINFO.
WHERE salary NOT IN (SELECT TOP 1 salary
ORDER BY salary DESC)