Find Duplicate Records using CTE (Common Table Expression) in SQL Server 2005

There are many ways to find duplicate records in a table.Here i use CTE (Common Table Expression) for finding duplicate records.This is the simplest and easily understandable way.



CREATE TABLE [dbo].[Employees]
( [EmpID] int,
[EmpName] nvarchar(100)
)
GO
INSERT INTO [Employees]([EmpID], [EmpName])
select 1,'Aamir'
UNION ALL
select 2,'Faisal'
UNION ALL
select 3 ,'Yasir'
UNION ALL
select 4 ,'Aamir'
UNION ALL
select 5,'Jibran'
UNION ALL
select 6,'Arif'
UNION ALL
select 7,'Jibran'
UNION ALL
select 8,'Khalid'
select * from [Employees];
WITH DuplicateEmployees(EmpID,EmpName,Ranking)
AS
(
SELECT EmpID,EmpName,Ranking = DENSE_RANK() OVER(PARTITION BY EmpName ORDER BY NewId() ASC)
FROM [Employees]
)
select * from DuplicateEmployees
WHERE Ranking > 1

Conversion From Decimal to Binary in SQL Server 2005.

Below is the function that we use to convert decimal to binary number.


Create FUNCTION dbo.udf_GetNumbers (@base INT, @lenght INT)
RETURNS @baseN_numbers TABLE
(
decNum INT PRIMARY KEY NOT NULL,
baseN_Num VARCHAR(50) NOT NULL
)
AS
BEGIN
WITH tblBase AS
(
SELECT CAST(0 AS VARCHAR(50)) AS baseNum
UNION ALL
SELECT CAST((baseNum + 1) AS VARCHAR(50))
FROM tblBase WHERE baseNum < @base-1
),
numbers AS
(
SELECT CAST(baseNum AS VARCHAR(50)) AS num FROM tblBase
UNION ALL
SELECT CAST((t2.baseNum + num) AS VARCHAR(50)) FROM numbers CROSS JOIN tblBase t2
WHERE LEN(NUM) < @lenght
)
INSERT INTO @baseN_numbers
SELECT ROW_NUMBER() OVER (ORDER BY NUM) -1 AS rowID, NUM
FROM numbers WHERE LEN(NUM) > @lenght - 1
OPTION (MAXRECURSION 0);
RETURN
END



select * FROM dbo.udf_GetNumbers(2, 8)

will display the 0 to 255 decimal and binary numbers.

Return Query Text Along With sp_who2 Using Dynamic Management Views in SQL Server 2005

Sp_Who2 is used to check which SPID blocked by other SPID with some other information like DBName,Staus,Login,Command,ProgramName etc but this Sp is not give us the exact query that is run against SPID.

Below is the stored procedure that we use to get all SP_who2 results along with query text.


Create procedure who_blk_with_command
as
SELECT D.text SQLStatement, A.Session_ID SPID,
ISNULL(B.status,A.status) Status, A.login_name [Login],
A.[host_name] HostName, C.BlkBy, DB_NAME(B.Database_ID) DBName,
B.command, ISNULL(B.cpu_time, A.cpu_time) CPUTime,
ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO,
A.last_request_start_time LastBatch, A.program_name FROM
sys.dm_exec_sessions A
LEFT JOIN sys.dm_exec_requests B ON A.session_id = B.session_id
LEFT JOIN
( SELECT A.request_session_id SPID,
B.blocking_session_id BlkBy
FROM sys.dm_tran_locks as A
INNER JOIN sys.dm_os_waiting_tasks as B
ON A.lock_owner_address = B.resource_address ) C
ON A.Session_ID = C.SPID
OUTER APPLY sys.dm_exec_sql_text(sql_handle) D

Fast Paging in SQL Server 2005

Paging is the mostly used in database when we need to display records page wise there are different techniques here i introduce fastest way.

Problem : We need to display records page wise with sorting on lastname and firstname columns problem is that our selection table is very large and mostley people used query like

Declare @PageIndex INT, @PageSize INT

WITH PagingRecord AS (
SELECT UserId,ROW_NUMBER() OVER (ORDER BY LastName,FirstName)AS Row, LastName,FirstName
FROM Users)

SELECT *
FROM PagingRecord
WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize

problem in this query if there are large amount of records according to above query first we load all records and order by lastname and firstname the query take so much time to execute.

My point is that why we load all records every time when we move to next page.

Solution : We need to load records according to page index and page size for example Pageindex = 2 ,Pagesize = 50 we need to load only 100 records instead of all records and out of those 100 records display 51 to 100 records.
For this use below query


Create PROCEDURE dbo.GetRecordsByPaging
@PageIndex INT,
@PageSize INT
AS
BEGIN
declare @N int
set @N = @PageIndex * @PageSize;
WITH PagingRecord AS (
SELECT top (@N) UserID,ROW_NUMBER() OVER (ORDER BY LastName,FirstName)AS Row, LastName,FirstName
FROM Users
)
SELECT *
FROM PagingRecord
WHERE Row between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
END



Exec GetRecordsByPaging 2,50
return 2nd page records from 51 to 100.