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.
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.
CREATE TABLE TableName
(
Id INT PRIMARY KEY,
Name Nvarchar(500),
Age INT
)
3. Create table with primary key and Identity Column:
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.
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.
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.
UPDATE TableName SET NAME='Max Payne' WHERE Id=1
Update all records:
In this Sql query we will update all records within a table.
UPDATE TableName SET AGE=31
7. Delete query:
Delete single record:
In this Sql query we will delete single record from a table.
DELETE FROM TableName WHERE Id=1
Delete multiple records:
In this Sql query we will delete all records from a table.
DELETE FROM TableName
8. Select:
Select all columns from a tables:
In this Sql query we will select all columns from a table.
SELECT * FROM TableName
Select specific columns:
In this Sql query we will select specific columns from a table.
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.
CREATE VIEW view_name AS SELECT Id,Name,Age
FROM TableName
Usage:
select * From view_name
10. Create Stored procedure:
In this query we will learn about How to create stored procedure in Sql Server.
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.
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName';
Get column names with data type:
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.
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.
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.
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.
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.
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.
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.
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.
SELECT Name FROM dbo.sysdatabases
OR
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.
select @@language AS DefaultLanguage
21. Check server name:
In this query we will learn about How to Check SQL Server instance name.
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
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE}
Usage
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.
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.
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.
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.
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.
;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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
SELECT * FROM TableName ORDER BY NEWID()
Select N percent of random Rows:
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:
UPDATE TableName SET Column1=Column2, Column2=Column1
Usage:
UPDATE TableName SET Name=Email,Email=Name
46. Rename Database:
In this query we will learn about How to rename database in SQL Server.
EXEC sp_renamedb 'oldName', 'newName'
OR
ALTER DATABASE oldName MODIFY NAME = newName
47. Rename Table:
In this query we will learn about How to rename Table in SQL Server.
EXEC sp_rename 'OldTableName', 'NewTableName'
48. Rename Table Column:
In this query we will learn about How to rename Table Column in SQL Server.
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.
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.
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.
INSERT INTO TableName (NAME,Email,Age) VALUES ('Test User','[email protected]',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.
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.
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.
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.
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
ALTER TABLE TableName DISABLE TRIGGER TriggerName
Disable all triggers
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
ALTER TABLE TableName ENABBLE TRIGGER TriggerName
Enable all triggers
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
ALTER DATABASE [ForTesting] SET OFFLINE
Make Database online
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
DECLARE @TempTable TABLE
(
Id int primary key,
Name varchar(50),
Email varchar(50)
)
INSERT INTO @TempTable VALUES (1,'Max','[email protected]'),
(2,'Tony','[email protected]'),
(3,'Jack','[email protected]')
SELECT * FROM @TempTable
74. Delete duplicate rows :
In this query we will learn about How to delete duplicate rows in SQL Server.
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.
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.
75 Important Queries In SQL Server