SQL Server – OUTPUT CLAUSE – SIMPLE –EXAMPLE

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
(
name VARCHAR(50),
salary FLOAT,
user_updated VARCHAR(50)
)
 
GO
 
INSERT INTO dbo.employeedetails
OUTPUT inserted.name,
inserted.salary,
Suser_name()
INTO #tmp
VALUES ('Manu',
45000)
 
GO
 
SELECT *
FROM #tmp

 RESULT:

 

SQL Server – What are Synonyms in SQL Server?

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
  • Replication-filter-procedure
  • SQL Table-valued Function
  • Extended Stored Procedure
  • SQL Scalar Function
  • SQL Inline-table-valued Function
  • SQL Stored Procedure
  • Table (User-defined)
  • View

Let us look the below code for more understanding.

 

SQL SERVER – Find Nth /Second/Third Highest Salary – Common interview Question

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.


SELECT MAX(salary)

FROM   employeedetails

WHERE  salary NOT IN (SELECT TOP 1 salary

                      FROM   employeedetails

                      ORDER  BY salary DESC)