Find Duplicate Row Count in Table

In this blog, we’ll discuss a simple way to find duplicate record count of any column in a table.

There are numerous occasions, when we like to find if any column is having a duplicate row and if yes then we may also need to find the number of total counts the duplicate value is occurring in the table.

;WITH DUPLICATEROWS AS
(
SELECT
/* Name of the Column hard coded here just for reference that, we are searching duplicate value for this column */
‘[Col1]’ DuplicateColName
/* It’ll help in getting the count of duplicates value found in table */
,[Col1] DuplicateColValue
/* It is the main logic to partition and order by the table with the column we are searching for duplicate value */
,Row_Number() OVER (PARTITION BY [Col1] ORDER BY [Col1]) DuplicateRowNo
FROM
SchemaName.TableName¬†— Table name with schema
)
SELECT DuplicateColName,DuplicateColValue,Count(DuplicateRowNo) DuplicateCount
FROM DUPLICATEROWS WHERE DuplicateRowNo >1
Group By DuplicateColName,DuplicateColValue
Order By DuplicateColValue

Example1
/* Create a table with duplicate values*/
create table test_t1(ID int null)
go
insert into test_t1 values(null),(null),(1),(2),(2),(4),(1),(null),(null),(1),(2),(2),(4),(1)
go

;WITH DUPLICATEROWS AS
(
SELECT
‘[ID]’ DuplicateColName
,[ID] DuplicateColValue
,Row_Number() OVER (PARTITION BY [ID] ORDER BY [ID]) DuplicateRowNo
FROM
[DBO].[test_t1]
)
SELECT DuplicateColName,DuplicateColValue,Count(DuplicateRowNo) DuplicateCount
FROM DUPLICATEROWS WHERE DuplicateRowNo >1
Group By DuplicateColName,DuplicateColValue
Order By DuplicateColValue

/* Query Output */

DuplicateColName DuplicateColValue DuplicateCount
[ID] NULL 3
[ID] 1 3
[ID] 2 3
[ID] 4 1

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Advertisements

GO statement in SQL

In this blog, we’ll discuss about why not to use the ‘GO’ statement inside Stored Procedure or Functions or Views in Microsoft SQL Server.

The GO statement in MS SQL act as a releases resources and clean out your session.GO statement is used as a Batch separator in Sql Server. Batch is nothing but one or more Sql Server statements sent to the Sql Server engine as one set of statements.

So,once the GO statement is used inside stored procedure or function or view, it’ll make a batch of all the statements from the beginning of the script upto the GO statement will be compiled into one execution plan and sent to the server for process.So, it means everything in that batch is local to that batch. In other words everything after the GO statement is treated as different then the rest of the part above the GO statement.

Example1

CREATE PROCEDURE GO_Test1
AS
SELECT ‘First Statement’
GO
SELECT ‘Second Statement’
SELECT ‘Third Statement’

EXEC GO_Test1
–Output
First Statement

Now, check your procedure definition, you’ll be find that, the procedure definition is exactly upto the last statement above the GO statement.
CREATE PROCEDURE GO_Test1
AS
SELECT ‘First Statement’

Example2

CREATE PROCEDURE GO_Test1
AS
Begin
SELECT ‘First Statement’
GO
SELECT ‘Second Statement’
SELECT ‘Third Statement’
End

On executing the above query,you’ll get below SQL error message.

Msg 102, Level 15, State 1, Procedure GO_Test1, Line 4
Incorrect syntax near ‘First Statement’.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘End’.

The GO statements that you see in other’s scripts are to prevent the parser from stop execution after the previous statement errors out. it’s similar to Visual Basic “On Error Resume Next” statement. that way your script will continue execution until the end of the script file.

In your stored proc or functions or views, you don’t need the go statement.

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.