How to remove Duplicate records from table

This is the frequently asked question in an interview. How to remove or select distinct all data with the unique value. You all are knows that whenever we write distinct keyword with column it will returns unique combination of all columns.

In following query if you use distinct then it will return all rows then how to eliminate particular column duplication.You can not use distinct at this moment. I have a solution for this.

Just copy and paste following code in your SQL Server 2005 query window and then run it.

 
DECLARE @EMPDATA TABLE(EMPNO BIGINT PRIMARY KEY,EMAIL VARCHAR(50))

INSERT INTO @EMPDATA VALUES(1,'parag@gmail.com')

INSERT INTO @EMPDATA VALUES(2,'deven@gmail.com')

INSERT INTO @EMPDATA VALUES(3,'parag@gmail.com')

INSERT INTO @EMPDATA VALUES(4,'jig@gmail.com')

INSERT INTO @EMPDATA VALUES(5,'dharmik@gmail.com')

INSERT INTO @EMPDATA VALUES(6,'jig@gmail.com')

INSERT INTO @EMPDATA VALUES(7,'sandy@gmail.com')

INSERT INTO @EMPDATA VALUES(8,'dharmik@gmail.com')

;WITH CTE

AS

(

   SELECT ROW_NUMBER()OVER(PARTITION BY EMAIL ORDER BY  EMPNO)RANK,*

     FROM @EMPDATA

)

SELECT EMPNO,EMAIL FROM CTE

WHERE RANK=1

ORDER BY EMPNO

In above query it will just select the unique value if you want to remove then write DELETE statement where rank = 1.

Difference between DENSE_RANK() and ROW_NUMBER()

The difference between DENSE_RANK() and Row_number() is that Row_Number will generate a sequence of record one by one while DENSE_RANK() will also generate a sequence, but it is depend upon the field if field value is repeated then Rank of Dense will also repeat.




Following is the example that demonstrate a difference between both of them.

create table department(
    dept_name     char(20)     not null,
    emp_cnt       int          not null,
    budget        float,
    date_month    datetime);
  GO

  insert into department values('Research', 5, 50000, '01.01.2002');
  insert into department values('Research', 10, 70000, '01.02.2002');
  insert into department values('Research', 5, 65000, '01.07.2002');
  insert into department values('Accounting', 5, 10000, '01.07.2002');
  insert into department values('Accounting', 10, 40000, '01.02.2002');
  insert into department values('Accounting', 6, 30000, '01.01.2002');
  insert into department values('Accounting', 6, 40000, '01.02.2003');
  insert into department values('Marketing', 6, 10000, '01.01.2003');
  insert into department values('Marketing', 10, 40000, '01.02.2003');
  insert into department values('Marketing', 3, 30000, '01.07.2003');
  insert into department values('Marketing', 5, 40000, '01.01.2003');
  GO

  SELECT DENSE_RANK() OVER(ORDER BY budget DESC) AS rank_budget, 
ROW_NUMBER() OVER(ORDER BY budget DESC) AS row_number,dept_name, emp_cnt, budget
  FROM department
  WHERE budget <= 50000;
  GO


rank_budget          row_number           dept_name          emp_cnt     budget
---------------- -------------------- -------------------- ---------- ------------
     1                    1               Research                 5     50000
     2                    2               Accounting              10     40000
     2                    3               Accounting               6     40000
     2                    4               Marketing               10     40000
     2                    5               Marketing                5     40000
     3                    6               Marketing                3     30000        3                    7               Accounting               6     30000
     4                    8               Accounting               5     10000
     4                    9               Marketing                6     10000


SQL SERVER – 2005 – Find Nth Highest Record from Table – Using CTE

This question is quite a popular question and it is frequently asked question in interview that retrieve Nth highest record from table you can use top keyword and distinct and temp table. But, it will degrade your performance so in practice, you can easily use New features of SQL Server 2005 and that is CTE. Let us see....

USE AdventureWorks
GO


WITH SALCTE

AS

(

      SELECT e1.*,

      Row_number() OVER(ORDER BY e1.Rate DESC) AS Rank
      FROM HumanResources.EmployeePayHistory AS e1

)
SELECT * FROM SALCTE

WHERE Rank = 4

It will give you 4th highest record. Suppose if you want 5th, 6th or nth highest record then write 5th, 6th or nth instead of Rank = 4. 

SQL Challenge for Beginners - Find Factorial

The Challenge for Beginners

This challenge is not a real time problem directly, but it measures about logical thinking. The problem is all about finding the factorial of numbers. Though it is known to most of us what a factorial is, but to recall the concept here is an example:

Factorial of 3 is 1*2*3 = 6 i.e. the factorial of a non-negative integer n, denoted by n!, is the product of all positive integers less than or equal to n.

Sample Input Data

Nums
-----------
0 
1 
3 
5 
10

Expected Output

Nums        Factorial
----------- -----------
0                    1
1                    1
3                    6
5                  120
10             3628800

Script

Here is the script to generate the sample data

DECLARE @Fact TABLE(Nums INT)
INSERT INTO @Fact 
SELECT 0 UNION ALL  
SELECT 1 UNION ALL  
SELECT 3 UNION ALL  
SELECT 5 UNION ALL  
SELECT 10

SELECT * FROM @Fact

Solution :

DECLARE @Fact TABLE(Nums INT)
INSERT INTO @Fact 
SELECT 0 UNION ALL 
SELECT 1 UNION ALL 
SELECT 3 UNION ALL 
SELECT 5 UNION ALL 
SELECT 10

DECLARE @no INT,@f BIGINT,@nums INT

DECLARE C1 CURSOR FOR SELECT nums FROM @fact
OPEN C1
FETCH NEXT FROM C1 INTO @no
WHILE @@FETCH_STATUS=0
BEGIN
        SELECT @f=1,@nums=@no
        WHILE @no>0
        BEGIN
             SET @f=@f*@no
             SET @no=@no-1 
        END
        DECLARE @t TABLE(nums INT,Fact BIGINT)
        INSERT INTO @T SELECT @Nums,@F
        FETCH NEXT FROM C1 INTO @no
END
CLOSE C1
DEALLOCATE C1 

SELECT * FROM @T ORDER BY 1

Just Paste Above solution in your SQL Server You will get a

Output that really you need.

Basic of Join

INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.


RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.


FULL OUTER JOIN
This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Additional Notes related to JOIN:

The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.

SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
GO

The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.

/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The above example can also be created using Right Outer Join.


NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.

Get Date Time in Any Format – UDF – User Defined Functions

Following User Defined Function will give you date in Any format Whichever you want. For the output of this function see the end of User Defined Function.

CREATE FUNCTION [dbo].[UfDateFormat]
(
@Date datetime,
@fORMAT VARCHAR(80)
)
RETURNS NVARCHAR(80)
AS
BEGIN
DECLARE @Dateformat INT
DECLARE @ReturnedDate VARCHAR(80)
DECLARE @TwelveHourClock INT
DECLARE @Before INT
DECLARE @pos INT
DECLARE @Escape INT

SELECT @ReturnedDate='error! unrecognised format '+@format
SELECT @DateFormat=CASE @format
WHEN 'mmm dd yyyy hh:mm AM/PM' THEN 100
WHEN 'mm/dd/yy' THEN 1
WHEN 'mm/dd/yyyy' THEN 101
WHEN 'yy.mm.dd' THEN 2
WHEN 'dd/mm/yy' THEN 3
WHEN 'dd.mm.yy' THEN 4
WHEN 'dd-mm-yy' THEN 5
WHEN 'dd Mmm yy' THEN 6
WHEN 'Mmm dd, yy' THEN 7
WHEN 'hh:mm:ss' THEN 8
WHEN 'yyyy.mm.dd' THEN 102
WHEN 'dd/mm/yyyy' THEN 103
WHEN 'dd.mm.yyyy' THEN 104
WHEN 'dd-mm-yyyy' THEN 105
WHEN 'dd Mmm yyyy' THEN 106
WHEN 'Mmm dd, yyyy' THEN 107
WHEN 'Mmm dd yyyy hh:mm:ss:ms AM/PM' THEN 9
WHEN 'Mmm dd yyyy hh:mi:ss:mmm AM/PM' THEN 9
WHEN 'Mmm dd yy hh:mm:ss:ms AM/PM' THEN 109
WHEN 'mm-dd-yy' THEN 10
WHEN 'mm-dd-yyyy' THEN 110
WHEN 'yy/mm/dd' THEN 11
WHEN 'yyyy/mm/dd' THEN 111
WHEN 'yymmdd' THEN 12
WHEN 'yyyymmdd' THEN 112
WHEN 'dd Mmm yyyy hh:mm:ss:Ms' THEN 113
WHEN 'hh:mm:ss:Ms' THEN 14
WHEN 'yyyy-mm-dd hh:mm:ss' THEN 120
WHEN 'yyyy-mm-dd hh:mm:ss.Ms' THEN 121
WHEN 'yyyy-mm-ddThh:mm:ss.Ms' THEN 126
WHEN 'dd Mmm yyyy hh:mm:ss:ms AM/PM' THEN 130
WHEN 'dd/mm/yy hh:mm:ss:ms AM/PM' THEN 131
WHEN 'RFC822' THEN 2
WHEN 'dd Mmm yyyy hh:mm' THEN 4
ELSE 1 END
SELECT @ReturnedDate='error! unrecognised format ' +@format+CONVERT(VARCHAR(10),@DateFormat)
IF @DateFormat>=0
SELECT @ReturnedDate=CONVERT(VARCHAR(80),@Date,@DateFormat)
--check for favourite and custom formats that can be done quickly
ELSE IF @DateFormat=-2--then it is RFC822 format
SELECT @ReturnedDate=LEFT(DATENAME(dw, @Date),3) + ', ' + STUFF(CONVERT(NVARCHAR,@Date,113),21,4,' GMT')
ELSE IF @DateFormat=-4--then it is european day format with minutes
SELECT @ReturnedDate=CONVERT(CHAR(17),@Date,113)
ELSE
BEGIN
SELECT @Before=LEN(@format)
SELECT @Format=REPLACE(REPLACE(REPLACE( @Format,'AM/PM','#'),'AM','#'),'PM','#')
SELECT @TwelveHourClock=CASE WHEN @Before >LEN(@format) THEN 109 ELSE 113 END, @ReturnedDate=''
WHILE (1=1)--forever
BEGIN
SELECT @pos=PATINDEX('%[yqmidwhs:#]%',@format+' ')
IF @pos=0--no more date format strings
BEGIN
SELECT @ReturnedDate=@ReturnedDate+@format
BREAK
END
IF @pos>1--some stuff to pass through first
BEGIN
SELECT @escape=CHARINDEX ('\',@Format+'\') --is it a literal character that is escaped?
IF @escape<@pos BEGIN
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@escape-1) +SUBSTRING(@format,@escape+1,1)
SET @format=RTRIM(SUBSTRING(@Format,@Escape+2,80))
CONTINUE
END
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@pos-1)
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END
SELECT @pos=PATINDEX('%[^yqmidwhs:#]%',@format+' ')--get the end
SELECT @ReturnedDate=@ReturnedDate+--'('+substring(@Format,1,@pos-1)+')'+
CASE SUBSTRING(@Format,1,@pos-1)
--Mmmths as 1--12
WHEN 'M' THEN CONVERT(VARCHAR(2),DATEPART(MONTH,@Date))
--Mmmths as 01--12
WHEN 'Mm' THEN CONVERT(CHAR(2),@Date,101)
--Mmmths as Jan--Dec
WHEN 'Mmm' THEN CONVERT(CHAR(3),DATENAME(MONTH,@Date))
--Mmmths as January--December
WHEN 'Mmmm' THEN DATENAME(MONTH,@Date)
--Mmmths as the first letter of the Mmmth
WHEN 'Mmmmm' THEN CONVERT(CHAR(1),DATENAME(MONTH,@Date))
--Days as 1--31
WHEN 'D' THEN CONVERT(VARCHAR(2),DATEPART(DAY,@Date))
--Days as 01--31
WHEN 'Dd' THEN CONVERT(CHAR(2),@date,103)
--Days as Sun--Sat
WHEN 'Ddd' THEN CONVERT(CHAR(3),DATENAME(weekday,@Date))
--Days as Sunday--Saturday
WHEN 'Dddd' THEN DATENAME(weekday,@Date)
--Years as 00--99
WHEN 'Yy' THEN CONVERT(CHAR(2),@Date,12)
--Years as 1900--9999
WHEN 'Yyyy' THEN DATENAME(YEAR,@Date)
WHEN 'hh:mm:ss' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8)
WHEN 'hh:mm:ss:ms' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'h:mm:ss' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8)
--tthe SQL Server BOL syntax, for compatibility
WHEN 'hh:mi:ss:mmm' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'h:mm:ss:ms' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'H:m:s' THEN SUBSTRING(REPLACE(':'+SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,8),':0',':'),2,30)
WHEN 'H:m:s:ms' THEN SUBSTRING(REPLACE(':'+SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,12),':0',':'),2,30)
--Hours as 00--23
WHEN 'hh' THEN REPLACE(SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,2),' ','0')
--Hours as 0--23
WHEN 'h' THEN LTRIM(SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,2))
--Minutes as 00--59
WHEN 'Mi' THEN DATENAME(minute,@date)
WHEN 'mm' THEN DATENAME(minute,@date)
WHEN 'm' THEN CONVERT(VARCHAR(2),DATEPART(minute,@date))
--Seconds as 0--59
WHEN 'ss' THEN DATENAME(second,@date)
--Seconds as 0--59
WHEN 'S' THEN CONVERT(VARCHAR(2),DATEPART(second,@date))
--AM/PM
WHEN 'ms' THEN DATENAME(millisecond,@date)
WHEN 'mmm' THEN DATENAME(millisecond,@date)
WHEN 'dy' THEN DATENAME(dy,@date)
WHEN 'qq' THEN DATENAME(qq,@date)
WHEN 'ww' THEN DATENAME(ww,@date)
WHEN '#' THEN REVERSE(SUBSTRING(REVERSE(CONVERT(CHAR(26), @date,109)),1,2))
ELSE
SUBSTRING(@Format,1,@pos-1)
END
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END
END
RETURN @ReturnedDate
END
GO

-- Output of User Defined Function
SELECT [dbo].[UFDateFormat] ('8/7/2008', 'mm/dd/yy')
GO
SELECT [dbo].[UFDateFormat] ('8/7/2008', 'hh:mm:ss')
GO
SELECT [dbo].[UFDateFormat] ('8/7/2008', 'mmm')
GO
SELECT [dbo].[UFDateFormat] ('8/7/2008', 'Mmm dd yyyy hh:mm:ss:ms AM/PM')
GO
SELECT [dbo].[UFDateFormat] ('8/7/2008', '#')
GO

Count Duplicate Records – Rows

How to find count of all the duplicate records in the table.
Following query demonstrates usage of GROUP BY, HAVING, ORDER BY in one query and returns the results with duplicate column and its count in descending order.

-- Count Duplicate Records from Table

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC




How to get Random Value from different database ?

There are lots of way to select random record and row from database table. Here, some example where you don't require any other application logic and just apply back-end query.

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

SQL SERVER – Stored Procedure Optimization Tips – Best Practices

If you want to optimize your store procedure then keep following tips in your mind when you are preparing and executing store procedure.

Use schema name with object name:

SELECT * FROM dbo.Table -- Preferred method
-- Instead of
SELECT * FROM Table -- Avoid this method
--And finally call the stored procedure with qualified name like:
EXEC dbo.Proc -- Preferred method
--Instead of
EXEC Proc -- Avoid this method

This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available.

Do not use the prefix “sp_” in the stored procedure name:

If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. So, it will degrade your performance.

Include SET NOCOUNT ON statement:

With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

Use IF EXISTS (SELECT 1) instead of (SELECT *):

To check the existence of a record in another table, we uses the IF EXISTS clause. So, In If Exists statement use SELECT 1 instead of SELECT * that will select all data and select 1 will get only 1 data if any record is exists. So, Using SELECT 1 you can improve your performance.

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = 'MyTable' AND type = 'U')

Try to avoid using SQL Server cursors whenever possible:

Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance.

Use TRY-Catch for error handling:

Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:

BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH

Use the sp_executesql stored procedure instead of the EXECUTE statement.

The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code.
For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.




SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL

If you want to retrieve both top and bottom records both together using a T-SQL then you can use either of the following scripts.

Script 1 :

USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID IN (
SELECT TOP 1 MIN(SalesOrderDetailID) SalesOrderDetailID
FROM Sales.SalesOrderDetail
UNION ALL
SELECT TOP 1 MAX(SalesOrderDetailID) SalesOrderDetailID
FROM Sales.SalesOrderDetail)
GO

Script 2 :

USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID)
OR
SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC)
GO