In this article we
will learn about some of the mostly used SQL Server queries every
developer should know. These queries can be asked you as an Interview
Question or they are handy for you in your day to day tasks.
1. Create Table:
In this SQL Server
query we will learn How to create table in SQL Server.
|
1
2
3
4
5
6
|
CREATE TABLE TableName
(
Id INT,
Name Nvarchar(500),
Age INT
)
|
2. Create table with primary key:
In this query we
will Create SQL Server table with primary key.
|
1
2
3
4
5
6
|
CREATE TABLE TableName
(
Id INT PRIMARY KEY,
Name Nvarchar(500),
Age INT
)
|
3. Create table with primary key and Identity
Column:
|
1
2
3
4
5
6
|
CREATE TABLE TableName
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Name Nvarchar(500),
Age INT
)
|
4. Insert values into SQL Server table:
In this SQL Server
query we will learn about How to insert values into Sql Server table.
|
1
|
INSERT INTO TableName (Name,Age) VALUES ('Max',30);
|
5. Insert multiple rows in a single query:
In this Sql query we
will learn How to Insert Multiple Rows in a Single SQL Query. This
query is very important and generally asked in .Net Interview questions.
|
1
2
|
INSERT INTO TableName (Name,Age)
VALUES ('Max',30),('John',28),('Jack',31);
|
6. Update query:
Update single record:
In this Sql query we
will update single record from a table.
|
1
|
UPDATE TableName SET NAME='Max Payne' WHERE Id=1
|
Update all records:
In this Sql query we
will update all records within a table.
|
1
|
UPDATE TableName SET AGE=31
|
7. Delete query:
Delete single record:
In this Sql query we
will delete single record from a table.
|
1
|
DELETE FROM TableName WHERE Id=1
|
Delete multiple records:
In this Sql query we
will delete all records from a table.
|
1
|
DELETE FROM TableName
|
8. Select:
Select all columns from a tables:
In this Sql query we
will select all columns from a table.
|
1
|
SELECT * FROM TableName
|
Select specific columns:
In this Sql query we
will select specific columns from a table.
|
1
|
SELECT columnName1,ColumnName2 from TableName
|
9. Create View:
A view is a virtual
table created based on the result generated by SQL statement. Fields in a view
are directly related to one of more tables from database.
|
1
2
|
CREATE VIEW view_name AS SELECT Id,Name,Age
FROM TableName
|
Usage:
|
1
|
select * From view_name
|
10. Create Stored procedure:
In this query we will
learn about How to create stored procedure in Sql Server.
|
1
2
3
|
CREATE PROCEDURE getInfoFromTable
AS
SELECT * FROM TableName
|
11. Get all column names from table:
In this query we will
learn about How to get all column names from Sql Server table.
|
1
2
|
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName';
|
Get column names with data type:
|
1
2
|
select COLUMN_NAME,DATA_TYPE from
INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName';
|
12. Search column name from database:
In this query we will
learn about How to search any column name from Sql Server database.
This query will look out for matching column names in database.
|
1
2
3
4
|
SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%columnNameToSearch%'
|
13. Get all user created tables:
In this query we will
learn about How to get names of all user created tables in Sql Server.
|
1
|
SELECT NAME FROM sys.objects WHERE TYPE='U'
|
14. Get all view names:
In this query we will
learn about How to get names of all views in Sql Server.
|
1
|
SELECT * FROM sys.views
|
15. Get all stored procedure names:
In this query we will
learn about How to get names of all stored procedure in Sql Server.
|
1
|
SELECT * FROM sys.procedures
|
16. Counting rows for all tables at once:
In this query we will
learn about How to fetch the row count for all tables in a SQL SERVER
database. This query is very usefull when we want to know how many records
are present in tables.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
|
17. Get Comma Separated List of all columns in
table:
In this query we will
learn about How to get Comma Separated List of all columns in table.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
Select TABLE_SCHEMA, TABLE_NAME
, Stuff(
(
Select ',' + C.COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS As C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
And C.TABLE_NAME = T.TABLE_NAME
Order By C.ORDINAL_POSITION
For Xml Path('')
), 1, 1, '') As Columns
From INFORMATION_SCHEMA.TABLES As T
WHERE T.TABLE_NAME='TableName'
|
18. Generate ADO .net parameters:
In this query we will
learn about How to Generate ADO .net SqlCommand parameters in Sql Server. Fellows who have worked on asp .net webforms
and ADO .net must know the pain of generating parameters for SqlCommand object for large tables. I have written this query to save
my time and generate parameters on the fly. This is one of my favourate SQL Query.
|
1
2
|
SELECT
'cmd.Parameters.AddWithValue("@'+COLUMN_NAME+'", '+COLUMN_NAME+');'
FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME='TableName';
|
19. Retrieve List of All Database names:
In this query we will
learn about How to get list of all database names in Sql Server.
|
1
|
SELECT Name FROM dbo.sysdatabases
|
OR
|
1
|
EXEC sp_databases
|
20. Check default Language of SQL Server:
In this query we will
learn about How to Check default Language of SQL Server.
|
1
|
select @@language AS DefaultLanguage
|
21. Check server name:
In this query we will
learn about How to Check SQL Server instance name.
|
1
|
select @@SERVERNAME AS ServerName
|
22. Add columns to existing table:
In this query we will
learn about How to add new column to existing SQL Server table.
Syntax
|
1
2
|
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE}
|
Usage
|
1
2
|
ALTER TABLE TableName
ADD Email NVARCHAR(50)
|
23. Remove column from a table:
In this query we will
learn about How to remove column from SQL Server Table.
|
1
2
|
ALTER TABLE TableName
DROP COLUMN Email
|
24. Check column having NULL values only from
a table:
In this query we will
learn about How to check if any column contains only NULL values in SQL
Server Table.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
DECLARE @col varchar(255), @cmd varchar(max)
DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON
t.Object_ID = c.Object_ID
WHERE t.Name = 'TableName'
OPEN getinfo
FETCH NEXT FROM getinfo into @col
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM
TableName WHERE [' +
@col + '] IS NOT NULL) BEGIN print '''
+ @col + ''' end'
EXEC(@cmd)
FETCH NEXT FROM getinfo into @col
END
CLOSE getinfo
DEALLOCATE getinfo
|
25. Get list of tables without primary key:
In this query we will
learn about How to get list of tables without primary key in SQL Server.
|
1
2
3
4
|
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS
TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;
|
26. Get list of tables without having identity
column:
In this query we will
learn about How to get list of tables without having identity column in
SQL Server.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT
TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE
Table_NAME NOT IN
(
SELECT DISTINCT c.TABLE_NAME FROM
INFORMATION_SCHEMA.COLUMNS c
INNER
JOIN sys.identity_columns ic
on
(c.COLUMN_NAME=ic.NAME))
AND
TABLE_TYPE ='BASE TABLE'
|
27. Get month names with month numbers:
In this query we will
learn about How to get all month names with month number in SQL Server.
I have used this query to bind my dropdownlist with month name and month
number.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
;WITH months(MonthNumber) AS
(
SELECT 0
UNION ALL
SELECT MonthNumber+1
FROM months
WHERE MonthNumber < 12
)
SELECT
DATENAME(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS
[MonthName],Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE())) AS
MonthNumber
FROM months
ORDER BY
Datepart(MONTH,DATEADD(MONTH,-MonthNumber,GETDATE()))
;
|
28. Get name of current month:
In this query we will
learn about How to get name of current month in SQL Server.
|
1
|
select DATENAME(MONTH, GETDATE()) AS CurrentMonth
|
29. Get name of day:
In this query we will
learn about How to get name of day in SQL Server.
|
1
|
select DATENAME(WEEKDAY, GETDATE()) AS TodayIs
|
30. Get first date of current month:
In this query we will
learn about How to first date of current month in SQL Server.
|
1
|
SELECT
CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105)
FirstDate;
|
31. Get last date of current month:
In this query we will
learn about How to last date of current month in SQL Server.
|
1
|
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())),
DATEADD(MONTH,1,GETDATE())),105) LastDate;
|
32. Get first date of previous month:
In this query we will
learn about How to first date of previous month in SQL Server.
|
1
|
select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
FirstDayOfPreviousMonth
|
33. Get last date of previous month:
In this query we will
learn about How to last date of previous month in SQL Server.
|
1
|
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1,
-1) LastDayOfPreviousMonth
|
34. Get first date of current year:
In this query we will
learn about How to first date of current year in SQL Server.
|
1
|
SELECT dateadd(year,datediff(year,0,getdate()),0)
FirstDateOfYear
|
35. Get last date of current year:
In this query we will
learn about How to last date of current year in SQL Server.
|
1
|
SELECT
dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0)))
LasteDateOfYear
|
36. Select date without time:
In this query we will
learn about How to select date without time in SQL Server.
|
1
|
SELECT CONVERT(date, getdate()) AS DateWithoutTime
|
37. Select records within two dates:
In this query we will
learn about How to select records within two dates in SQL Server.
|
1
|
SELECT * FROM EMPLOYEE WHERE CONVERT(date,
CreatedOn) BETWEEN '1/1/2015' AND '12/31/2016'
|
Note: Here Employee is my table name and CreatedOn is a name
of date column used for filtering.
38. Calculate age from birth date:
In this query we will
learn about How to calculate age from birth date in SQL Server.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
DECLARE @date datetime, @tmpdate datetime, @years int,
@months int, @days int
SELECT @date = '05/05/1989'
SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE
WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE())
AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE
WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
SELECT @years AS Years, @months AS Months, @days AS Days
|
39. Get days between two dates:
In this query we will
learn about How to get days between two dates in SQL Server.
|
1
|
SELECT DATEDIFF(DAY, '1/1/2016', '3/1/2016')
DaysDifference
|
40. Truncate all tables using single query:
In this query we will
learn about How to truncate all database tables using single query in
SQL Server.
|
1
|
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'
|
Note: Please take proper backup before running
this query. This query will remove all data from all tables in selected
database.
41. Delete all records from all tables having
foreign keys:
In this query we will
learn about How to delete all rows from all tables in SQL Server which
have foreign keys.
|
1
2
3
|
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT
ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT
ALL'
|
42. Drop all tables:
In this query we will
learn about How to drop all tables with single query in SQL Server.
|
1
|
EXEC sys.sp_MSforeachtable @command1 = 'Drop Table ?'
|
43. Reset identity seed after deleting records
in SQL Server:
In this query we will
learn about How to Reset identity seed after deleting records in SQL
Server.
|
1
|
DBCC CHECKIDENT ('[TableName]', RESEED, 0);
|
Note:Make sure to delete all records before using
this query.
44. Select random rows:
In this query we will
learn about How to Select random rows from SQL Server table.
Select N random Rows:
|
1
|
SELECT * FROM TableName ORDER BY NEWID()
|
Select N percent of random Rows:
|
1
|
SELECT TOP 5 PERCENT * FROM TableName ORDER BY NEWID()
|
45. Swap values of 2 columns:
In this query we will
learn about How to swap values of two columns in SQL Server.
Format:
|
1
|
UPDATE TableName SET Column1=Column2, Column2=Column1
|
Usage:
|
1
|
UPDATE TableName SET Name=Email,Email=Name
|
46. Rename Database:
In this query we will
learn about How to rename database in SQL Server.
|
1
|
EXEC sp_renamedb 'oldName', 'newName'
|
OR
|
1
|
ALTER DATABASE oldName MODIFY NAME = newName
|
47. Rename Table:
In this query we will learn
about How to rename Table in SQL Server.
|
1
|
EXEC sp_rename 'OldTableName', 'NewTableName'
|
48. Rename Table Column:
In this query we will
learn about How to rename Table Column in SQL Server.
|
1
|
EXEC sp_rename 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'
|
49. Check SQL Server version:
In this query we will
learn about How to check version of SQL Server.
|
1
|
SELECT @@version
|
50. Get list of hard drives with free space:
In this query we will
learn about How to get list of system hard drives with available free
space using SQL Server.
|
1
|
EXEC master..xp_fixeddrives
|
51. Get Id of latest inserted record:
In this query we will
learn about How to Id of latest inserted record in SQL Server.
|
1
2
3
4
|
INSERT INTO TableName (NAME,Email,Age) VALUES ('Test User','test@gmail.com',30)
-- Get Id of latest inserted record
SELECT SCOPE_IDENTITY()
|
52. Delete duplicate records:
In this query we will
learn about How to delete duplicate records in SQL Server.
|
1
2
3
4
5
6
7
|
DELETE
FROM TableName
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TableName
GROUP BY DuplicateColumn)
|
53. Display Text of Stored Procedure, Trigger,
View :
In this query we will
learn about How to Display Text of Stored Procedure, Trigger, View in
SQL Server.
|
1
2
|
exec sp_helptext @objname = 'getInfoFromTable'
-- Here getInfoFromTable is
my storedprocedure name
|
54. Get List of Primary Key and Foreign Key
for a particular table :
In this query we will
learn about How to get list of Primary Key and Foreign Key for a
particular table in SQL Server.
|
1
2
3
4
5
|
SELECT DISTINCT
Constraint_Name AS [ConstraintName],
Table_Schema AS [Schema],
Table_Name AS [Table Name] FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='TableName'
|
55. Get List of Primary Key and Foreign Key of
entire database:
In this query we will
learn about How to get list of Primary Key and Foreign Key for a entire
database in SQL Server.
|
1
2
3
4
|
SELECT DISTINCT
Constraint_Name AS [ConstraintName],
Table_Schema AS [Schema],
Table_Name AS [Table Name] FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
|
56. Disable Trigger:
In this query we will
learn about How to disable trigger in SQL Server.
Disable particular trigger
|
1
|
ALTER TABLE TableName DISABLE TRIGGER TriggerName
|
Disable all triggers
|
1
|
ALTER TABLE TableName DISABLE TRIGGER ALL
|
57. Enable Trigger:
In this query we will
learn about How to enable trigger in SQL Server.
Enable particular trigger
|
1
|
ALTER TABLE TableName ENABBLE TRIGGER TriggerName
|
Enable all triggers
|
1
|
ALTER TABLE TableName ENABLE TRIGGER ALL
|
58. Make database offline or online :
In this query we will
learn about How to make database offline or online in SQL Server.
Make database offline
|
1
|
ALTER DATABASE [ForTesting] SET OFFLINE
|
Make Database online
|
1
|
ALTER DATABASE [ForTesting] SET ONLINE
|
Note: Make sure to select any other database
while using this query.
59. Backup Database :
In this query we will
learn about How to take backup of database using script in SQL Server.
|
1
|
BACKUP DATABASE DataBaseName TO
DISK='d:\NameOfBackupFile.bak'
|
60. Restore Database :
In this query we will
learn about How to restore database using script in SQL Server.
|
1
|
RESTORE DATABASE DataBaseName TO
DISK='d:\NameOfBackupFile.bak'
|
61. Search string in a single table :
In this query we will
learn about How to search string within a table in SQL Server.
|
1
|
SELECT * FROM TableName WHERE Name LIKE '%TextToSearch%'
|
62. Search string in all tables :
In this query we will
learn about How to search string in all tables in SQL Server.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
DECLARE @SearchStr nvarchar(100)='textToSearch'
CREATE TABLE #Results (ColumnName nvarchar(370),
ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName
nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) >
@TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT
NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME =
PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar',
'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(COLUMN_NAME) >
@ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT '''
+ @TableName + '.'
+ @ColumnName + ''', LEFT('
+ @ColumnName + ', 3630)
FROM ' + @TableName + 'WITH (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE '
+ @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
|
63. Replace a string in a SQL Server Table :
In this query we will
learn about How to replace a string in a SQL Server Table Column.
|
1
|
UPDATE TableName SET ColumnName = REPLACE(ColumnName, 'OldValue',
'NewValue')
|
64. Left Outer Join :
In this query we will
learn about How to use Left outer join in SQL Server.
|
1
2
3
|
SELECT * FROM Table1 as
t1
LEFT OUTER JOIN Table2 as
t2
on t1.PrimaryColumn=t2.ForeignKeyColumn
|
65. Right Outer Join :
In this query we will
learn about How to use Right outer join in SQL Server.
|
1
2
3
|
SELECT * FROM Table1 as
t1
RIGHT OUTER JOIN Table2 as
t2
on t1.PrimaryColumn=t2.ForeignKeyColumn
|
66. Full Outer Join :
In this query we will
learn about How to use Full outer join in SQL Server.
|
1
2
3
|
SELECT * FROM Table1 as
t1
FULL OUTER JOIN Table2 as
t2
on t1.PrimaryColumn=t2.ForeignKeyColumn
|
67. Inner Join :
In this query we will
learn about How to use Inner join in SQL Server.
|
1
2
3
|
SELECT * FROM Table1 as
t1
INNER JOIN Table2 as
t2
on t1.PrimaryColumn=t2.ForeignKeyColumn
|
68. Cross Join :
In this query we will
learn about How to use Cross join in SQL Server.
|
1
2
|
SELECT * FROM Table1
CROSS JOIN Table2
|
69. IF ELSE :
In this query we will
learn about How to use IF ELSE Statements in SQL Server.
|
1
2
3
4
5
6
7
8
9
|
DECLARE @ValueToCheck INT;
SET @ValueToCheck = 14;
IF @ValueToCheck=15
SELECT 'Value is 15'
As Result
ELSE IF @ValueToCheck<15
SELECT 'Value is Less than 15' As Result
ELSE IF @ValueToCheck>15
SELECT 'Value is greater than 15' As Result
|
70. CASE Expression :
In this query we will
learn about How to CASE Expression in SQL Server.
|
1
2
3
4
5
6
|
SELECT CASE Name
WHEN 'max' THEN 'Name is max'
WHEN 'jack'
THEN 'Name is max'
ELSE ' You have selected other user'
END AS Result
FROM TableName
|
71. Select Second highest salary :
In this query we will
learn about How to select second highest salary in SQL Server.
|
1
|
SELECT MAX(Salary) from Employee WHERE Salary NOT IN
(select MAX(Salary) from Employee );
|
72. Paging Example :
In this query we will
learn about How to Implement paging in SQL Server.
|
1
2
3
|
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id)
AS RowNum,* FROM Country)as Result
WHERE RowNum>=20 AND RowNum<=40
ORDER BY RowNum
|
73. Temporary Tables Example :
In this query we will
learn about How to use temporary tables in SQL Server.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
DECLARE @TempTable TABLE
(
Id int primary key,
Name varchar(50),
Email varchar(50)
)
INSERT INTO @TempTable VALUES (1,'Max','max@gmail.com'),
(2,'Tony','Tony@gmail.com'),
(3,'Jack','Jack@Indivar.com')
SELECT * FROM @TempTable
|
74. Delete duplicate rows :
In this query we will
learn about How to delete duplicate rows in SQL Server.
|
1
2
3
4
5
6
|
DELETE FROM TableName
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TableName
GROUP BY DuplicateColumn1, DuplicateColumn2)
|
75. Reset Identity Seed :
In this query we will
learn about How to Reset identity seed after deleting records in SQL
Server.
|
1
|
DBCC CHECKIDENT ('[YourTableName]', RESEED, 0);
|
Note: Here 0 is the
number-1 from where you want to start your auto increment. Suppose you wants to
start auto increment your primary key value from 50 then you need to pass 49 in
the above method.
