75 Important queries in SQL Server every developer should know

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','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.

    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','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.

    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

 
We are nothing without our users ! You can help us offer even more high quality content. Please share our page !
Best quality Asp .Net Ajax Control Toolkit tutorials.

Give your valuable comments.

Name
Email
Comment
8 + 5 =
 
About Us | Terms of Use | Privacy Policy | Disclaimer | Contact Us Copyright © 2012-2018 CodingFusion
75 Important SQL Server queries you should know