Delete Duplicte Rows from Table

;WITH CTE AS (
 SELECT ROW_NUMBER() OVER(PARTITION BY Column1,Column2 ORDER BY Column3) AS RowID
   ,*
 FROM dbo.TableName (NOLOCK)
)
DELETE x
--SELECT Count(*)
FROM cte  x
WHERE RowID > 1

Reset Identity column in SQL Server

If you have deleted all records in your table and you want to start new identity value from 1 then you need to run below command
 

The following line resets the Identity value for the Employee table to 0 so that the next record added starts at 1.

DBCC CHECKIDENT('Employee', RESEED, 0)

Also, there are 40 records in your table and you want to start next record from 51 then you need to run below command

DBCC CHECKIDENT('Employee', RESEED, 50)




 

Querying XML in Sql Server

declare @xml xml = ' Viral Bhatt Petlad Anand Jinal Shah Ahmedabad Ahmedabad Rajesh Davda Wadhwan City Surendrabagar ' SELECT ISNULL(b.value('Name[1]','NVARCHAR(50)'),NULL) AS Name, ISNULL(b.value('City[1]','NVARCHAR(50)'),NULL) AS City, ISNULL(b.value('District[1]','NVARCHAR(50)'),NULL) AS District FROM @xml.nodes('/xmlroot/xmlattribute') a(b)

Sending E-Mail through SQL Server stored procedures

This summary is not available. Please click here to view the post.

Inline variable assignment in sql server 2008

Instead of:

DECLARE @myVar int
SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Reset Identity Column in SQL Server

Run Following SQL Query in your Query Window and Execute it. This will reset the identity column’s value once gain to 0, so that the new record will start from 1.

DBCC CHECKIDENT(‘Users’, RESEED, 0)

Here ‘Users’ is table name.This will reset the Identity column’s value to 0. So, next record will start from 1.

Enjoy!!!

The transaction log for database 'mydatabase' is full

Issue : The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Resolution :

BACKUP LOG WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (, 1)
GO