Removing Duplicate Rows in SQL Server for Tables with no primary key

When you have duplicates in a table that has no primary key defined, and you are using an older version of SQL Server, such as SQL Server 2000, you do not have an easy way to identify a single row. Therefore, you cannot simply delete this row by specifying a WHERE clause in a DELETE statement.

You can, however, use the SET ROWCOUNT 1 command, which will restrict the subsequent DELETE statement to removing only one row. For example:

DECLARE @table TABLE (dataVARCHAR(20))
INSERT INTO @table VALUES ('not duplicate row')INSERT INTO @table VALUES ('duplicate row')INSERT INTO @table VALUES ('duplicate row')

SET ROWCOUNT 1DELETE FROM @table WHERE data = 'duplicate row'SET ROWCOUNT 0

In the above example, only one row is deleted. Consequently, there will be one remaining row with the content “duplicate row”. If you have more than one duplicate of a particular row, you would simply adjust the ROWCOUNT accordingly. Note that after the delete, you should reset the ROWCOUNT to 0 so that subsequent queries are not affected.

Use of SET NOCOUNT in stored procedures

Whenever we write any procedure and execute it a message appears in message window that shows number of rows affected with the statement written in the procedure.
But this message creates an extra overhead on the network. By using SET NOCOUNT we can remove this extra overhead from the network, that can actually improve the performance of our database and our application.
When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
Example:
USE STUDENTDB
GO
SELECT * FROM [STUDENTDB].[STUDENT];
Messege:
(664 row(s) affected)

USE STUDENTDB
GO
SET NOCOUNT ON
SELECT * FROM [STUDENTDB].[STUDENT];
SET NOCOUNT OFF
Messege:
Command(s) completed successfully.
SET NOCOUNT NO statement can be useful in store procedures. SET NOCOUNT ON statement into store procedures can reduce network traffic, because client will not receive the message indicating the number of rows affected by T-SQL statement. Setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
Reference: http://msdn.microsoft.com/en-us/library/ms189837.aspx

Tech Boys Group Dance

iframe width 459 height 344 src //www.youtube.com/embed/YPwqnfRUfxM frameborder 0 allowfullscreen> /iframe>

How to find missing sequence number in a column of sql table?

Answer:-

UPDATE a

SET a.seq = b.seq+1FROM table1 AS a(NOLOCK)INNER

JOIN table1

AS b(NOLOCK)LEFT

OUTER JOIN table1 AS c(NOLOCK)ON b.seq + 1 = c.seqON a.seq-1 = a.seq+1WHERE b.seq IS NULL