Skip to content

Instantly share code, notes, and snippets.

@diluculo

diluculo/SQL Server commands and queries.md

Forked from ashish2199/SQL Server commands and queries.md
Created September 13, 2023 06:11
Show Gist options
  • Save diluculo/27e19f9e55c90c89b34fb0a7ca7bd5a1 to your computer and use it in GitHub Desktop.
Save diluculo/27e19f9e55c90c89b34fb0a7ca7bd5a1 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @ashish2199 ashish2199 renamed this gist Nov 26, 2017. 1 changed file with 208 additions and 143 deletions.
    351 changes: 208 additions & 143 deletions SQL Server commands and queries → SQL Server commands and queries.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,10 @@
    To create a Table
    create table risk_clos_rank(
    id_num int IDENTITY(1,1) NOT NULL,
    username nvarchar(100),
    datetime_of_decision DATETIME
    )
    ***To create a Table***
    ```sql
    create table risk_clos_rank(
    id_num int IDENTITY(1,1) NOT NULL,
    username nvarchar(100),
    datetime_of_decision DATETIME
    );

    CREATE TABLE TheNameOfYourTable (
    ID INT NOT NULL IDENTITY(1,1),
    @@ -14,83 +15,106 @@ To create a Table
    CategoryID int NOT NULL REFERENCES Categories(ID),
    PRIMARY KEY (ID)
    );

    To create a copy of table( doesnt create constraints like primary key, not null , indexes ect)
    ```
    ***To create a copy of table( doesnt create constraints like primary key, not null , indexes ect)***
    ```sql
    SELECT * INTO NewTable FROM OldTable
    Eg. SELECT * INTO clos_ext_bkup FROM clos_ext;

    To create a copy of table with its data (create and insert)
    ```
    ***To create a copy of table with its data (create and insert)***
    ```sql
    SELECT expressions INTO new_table FROM tables [WHERE conditions];
    SELECT employee_id AS contact_id, last_name, first_name INTO contacts FROM employees WHERE employee_id < 1000;

    The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list.
    ```

    The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list.

    Inserting Data from another table ( only insert)
    ***Inserting Data from another table ( only insert)***
    ```sql
    INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool'
    INSERT INTO contacts (contact_id, last_name, first_name) SELECT employee_id, last_name, first_name FROM employees WHERE employee_id <= 100;

    Inserting Multiple values
    ```
    ***Inserting Multiple values***
    ```sql
    INSERT INTO table1 (First, Last)
    VALUES
    ('Fred', 'Smith'),
    ('John', 'Smith'),
    ('Michael', 'Smith'),
    ('Robert', 'Smith');

    To add a column
    ```
    ***To add a column***
    ```sql
    ALTER TABLE table_name ADD column_1 column-definition,column_2 column-definition,column_n column_definition;
    alter table risk_user_approval_tree add lineusr nvarchar(100);
    ALTER TABLE table ADD columnname BIT CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES

    To add a auto increment
    ```
    ***To add a auto increment***
    ```
    ALTER TABLE 'tableName' ADD 'NewColumn' INT IDENTITY(1,1);

    To add a column with computed value
    ```
    ***To add a column with computed value***
    ```sql
    ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5);

    To delete/drop a column
    ```
    ***To delete/drop a column***
    ```sql
    ALTER TABLE table_name DROP COLUMN column_name;

    To drop a table
    ```
    ***To drop a table***
    ```sql
    DROP TABLE tablename;

    To modify a column
    ```
    ***To modify a column***
    ```sql
    ALTER TABLE table_name ALTER COLUMN column_name column_type;

    To update a row
    ```
    ***To update a row***
    ```sql
    UPDATE clos_customer_master SET Prev = 'Reactivation' WHERE Prev = 'Reactivate';

    To update a row from select clause
    ```

    ***To update a row from select clause***
    ```sql
    UPDATE table SET Col1 = i.Col1, Col2 = i.Col2 FROM ( SELECT ID, Col1, Col2 FROM other_table) i WHERE i.ID = table.ID;
    The subquery results are substituted into the outer query. As we need table object in outer query, we need to make an alias of inner query.
    ```
    The subquery results are substituted into the outer query. As we need table object in outer query, we need to make an alias of inner query.

    To add a primary key
    ***To add a primary key***
    ```sql
    ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

    To find the name of constraints
    ```
    ***To find the name of constraints***
    ```sql
    SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'tablename'

    To find name of Primary key constraint
    ```
    ***To find name of Primary key constraint***
    ```sql
    SELECT name FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'CLOS_ext';

    Drop primary key
    ```
    ***Drop primary key***
    ```sql
    ALTER TABLE table_name DROP CONSTRAINT constraint_name;

    To rename a column (alter command doesnt work here)
    ```
    ***To rename a column (alter command doesnt work here)***
    ```sql
    sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';
    sp_rename 'cl_ff_docm.WINAME', 'WI_NAME', 'COLUMN';

    To rename a table
    ```
    ***To rename a table***
    ```sql
    sp_rename 'old_table_name', 'new_table_name';

    To top 10% of records
    ```
    ***To top 10% of records***
    ```sql
    SELECT TOP(10) * FROM CLOS_EXT

    To find when a table was altered
    SELECT [name] ,create_date, modify_date FROM sys.tables;

    To find which table contains a given column

    ```
    ***To find when a table was altered***
    ```sql
    SELECT [name] , create_date, modify_date FROM sys.tables;
    ```
    ***To find which table contains a given column***
    ```sql
    SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

    SELECT OBJECT_SCHEMA_NAME (c.object_id) SchemaName,
    @@ -105,17 +129,21 @@ To find which table contains a given column
    WHERE o.type = 'U'
    -- and o.Name = 'YourTableName'
    ORDER BY o.Name, c.Name

    To find which table has which constraint and on which column.
    ```
    ***To find which table has which constraint and on which column.***
    ```sql
    Select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE;

    Selcting based on case
    SELECT CASE WHEN <test> THEN <returnvalue>
    ```
    ***Selcting based on case***
    ```sql
    SELECT CASE
    WHEN <test> THEN <returnvalue>
    WHEN <othertest> THEN <returnthis>
    ELSE <returndefaultcase>
    ELSE <returndefaultcase>
    END AS <newcolumnname>
    FROM <table>


    Eg.
    SELECT ProductNumber, Name, "Price Range" =
    CASE
    WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
    @@ -126,15 +154,16 @@ Selcting based on case
    END
    FROM Production.Product
    ORDER BY ProductNumber ;

    Adding row numbers to the result //here we are creating
    SELECT
    ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
    name, recovery_model_desc
    ```
    ***Adding row numbers to the result*** //here we are creating
    ```sql
    SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name,
    recovery_model_desc
    FROM sys.databases
    WHERE database_id < 5;

    While loop
    ```
    ***While Loop***
    ```sql
    DECLARE @MaxCount INTEGER
    DECLARE @Count INTEGER
    DECLARE @Txt VARCHAR(MAX)
    @@ -144,7 +173,8 @@ While loop
    WHILE @Count<=@MaxCount
    BEGIN
    IF @Txt!=''
    SET @Txt=@Txt+',' + (SELECT Txt FROM ConcatenationDemo WHERE RowID=@Count)
    SET @Txt=@Txt+',' + (SELECT Txt FROM ConcatenationDemo
    WHERE RowID=@Count)
    ELSE
    SET @Txt=(SELECT Txt FROM ConcatenationDemo WHERE RowID=@Count)
    SET @Count += 1
    @@ -162,8 +192,9 @@ While loop
    ELSE
    CONTINUE
    END

    Try / Catch Statements
    ```
    ***Try / Catch Statements***
    ```sql
    BEGIN TRY
    -- try / catch requires SQLServer 2005
    -- run your code here
    @@ -174,125 +205,136 @@ Try / Catch Statements
    PRINT error_message()
    -- handle error condition
    END CATCH

    To get date in DD/MM/YYYY format
    ```
    ***To get date in DD/MM/YYYY format***
    ```sql
    SELECT CONVERT(varchar, GETDATE(), 103);

    To get all foreign keys refrencing a given table
    ```
    ***To get all foreign keys refrencing a given table***
    ```sql
    EXEC sp_fkeys 'TableName'

    To get datatype, size of columns of a table
    ```
    ***To get datatype, size of columns of a table***
    ```sql
    EXEC sp_columns CLOS_EXT;

    To get empty string after concatenation of a string with NULL
    When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.
    ```
    ***To get empty string after concatenation of a string with NULL***

    To compile without executing
    SET NOEXEC ON;
    When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them.
    When ***SET CONCAT_NULL_YIELDS_NULL*** is ON, concatenating a null value with a string yields a NULL result.

    For example, SELECT 'abc' + NULL yields NULL.

    When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string).

    For example, SELECT 'abc' + NULL yields abc.

    Updating data from another table
    ***To compile without executing***
    ```sql
    SET NOEXEC ON;
    ```
    When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them.

    ***Updating data from another table***
    ```sql
    UPDATE table SET Col1 = i.Col1, Col2 = i.Col2 FROM ( SELECT ID, Col1, Col2 FROM other_table) i WHERE i.ID = table.ID

    Check if column exists in table
    ```
    ***Check if column exists in table***
    ```sql
    IF EXISTS(SELECT 1 FROM sys.columns
    WHERE Name = N'columnName'
    AND Object_ID = Object_ID(N'schemaName.tableName'))
    BEGIN
    -- Column Exists
    END

    Converting Multi row data into a comma separated string
    ```
    ***Converting Multi row data into a comma separated string***
    ```sql
    DECLARE @Names VARCHAR(8000)
    SELECT @Names = COALESCE(@Names + ', ', '') +
    ISNULL(Name, 'N/A')
    FROM People
    ```
    ***Nvarchar***
    allows storing of unicode data



    Nvarchar
    allows storing of unicode data

    To remove duplicate rows
    ***To remove duplicate rows***
    ```sql
    select distinct * into t2 from t1;
    delete from t1;
    insert into t1 select * from t2;
    drop table t2;

    Check if the table exists
    IF (EXISTS (SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'TheSchema'
    AND TABLE_NAME = 'TheTable'))
    ```
    ***Check if the table exists***
    ```sql
    IF (EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'TheSchema'
    AND TABLE_NAME = 'TheTable')
    )
    BEGIN
    --Do Stuff
    END

    Find tables with given column name
    ```
    ***Find tables with given column name***
    ```sql
    select * from INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME like '%clientid%'
    order by TABLE_NAME

    Find all user tables
    ```
    ***Find all user tables***
    ```sql
    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

    WITH (NOLOCK)
    is the equivalent of using READ UNCOMMITED as a transaction isolation level. While it can prevent reads being deadlocked by other.

    Fing the last identity inserted into a table
    @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

    SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.

    IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.

    @@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.
    that is, if there was a second IDENTITY inserted based on a trigger after your insert, it would not be reflected in SCOPE_IDENTITY, only the insert you performed.
    IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

    Identity doesn’t guarantee uniqueness. If you want that, make a PK or add a unique index.

    Allows explicit values to be inserted into the identity column of a table.
    ```
    ***Allows explicit values to be inserted into the identity column of a table.***
    ```sql
    SET IDENTITY_INSERT dbo.Tool ON

    The DBCC CHECKIDENT management command is used to reset identity counter. Example:
    ```
    The ***DBCC CHECKIDENT*** management command is used to reset identity counter. Example:
    ```sql
    DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
    GO
    ```


    DECLARE and SET Varibales
    ***DECLARE and SET Varibales***
    ```sql
    DECLARE @Mojo int
    SET @Mojo = 1
    SELECT @Mojo = Column FROM Table WHERE id=1;

    Add a Foreign Key
    ```
    ***Add a Foreign Key***
    ```sql
    ALTER TABLE Products WITH CHECK ADD CONSTRAINT [FK_Prod_Man] FOREIGN KEY(ManufacturerID) REFERENCES Manufacturers (ID);

    Add a NULL Constraint
    ```
    ***Add a NULL Constraint***
    ```sql
    ALTER TABLE TableName ALTER COLUMN ColumnName int NOT NULL;

    Set Default Value for Column
    ```
    ***Set Default Value for Column***
    ```sql
    ALTER TABLE TableName ADD CONSTRAINT DF_TableName_ColumnName DEFAULT 0 FOR ColumnName;

    Create an Index
    ```
    ***Create an Index***
    ```sql
    CREATE INDEX IX_Index_Name ON Table(Columns)

    Check Constraint
    ```
    ***Check Constraint***
    ```sql
    ALTER TABLE TableName ADD CONSTRAINT CK_CheckName CHECK (ColumnValue > 1)

    Single Line Comments
    ```
    ***Single Line Comments***
    ```sql
    SET @mojo = 1 --THIS IS A COMMENT

    Multi-Line Comments
    ```
    ***Multi-Line Comments***
    ```sql
    /* This is a comment
    that can span
    multiple lines
    */

    User Defined Function
    ```
    ***User Defined Function***
    ```
    CREATE FUNCTION dbo.DoStuff(@ID int)
    RETURNS int
    AS
    @@ -308,9 +350,9 @@ User Defined Function
    END
    GO
    SELECT dbo.DoStuff(0);

    Pivot - To convert rows into columns

    ```
    ***Pivot - To convert rows into columns***
    ```sql
    SELECT Wi_name, Often, Sometimes, Never, NA
    FROM
    (
    @@ -321,4 +363,27 @@ Pivot - To convert rows into columns
    (
    MAX(T) for Past_due IN ([Often], [Sometimes], [Never],[NA])
    )
    P2 ORDER BY WI_NAME;
    P2 ORDER BY WI_NAME;
    ```

    ***WITH (NOLOCK)***

    is the equivalent of using READ UNCOMMITED as a transaction isolation level. While it can prevent reads being deadlocked by other.

    ### ***Finding the last identity inserted into a table***

    - ***@@IDENTITY*** returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

    - ***SCOPE_IDENTITY()*** returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.

    - ***IDENT_CURRENT('tableName')*** returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.

    @@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session.

    However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope. That is, if there was a second IDENTITY inserted based on a trigger after your insert, it would not be reflected in SCOPE_IDENTITY, only the insert you performed.


    IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

    Identity doesn’t guarantee uniqueness. If you want that, make a PK or add a unique index.

  2. @ashish2199 ashish2199 revised this gist Nov 26, 2017. 1 changed file with 5 additions and 6 deletions.
    11 changes: 5 additions & 6 deletions SQL Server commands and queries
    View file
    Original file line number Diff line number Diff line change
    @@ -1,10 +1,9 @@
    To create a Table

    create table risk_clos_rank(
    id_num int IDENTITY(1,1) NOT NULL,
    username nvarchar(100),
    datetime_of_decision DATETIME
    )
    create table risk_clos_rank(
    id_num int IDENTITY(1,1) NOT NULL,
    username nvarchar(100),
    datetime_of_decision DATETIME
    )

    CREATE TABLE TheNameOfYourTable (
    ID INT NOT NULL IDENTITY(1,1),
  3. @ashish2199 ashish2199 renamed this gist Nov 26, 2017. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions SQL Server commands and queries.md → SQL Server commands and queries
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,5 @@
    To create a Table

    create table risk_clos_rank(
    id_num int IDENTITY(1,1) NOT NULL,
    username nvarchar(100),
  4. @ashish2199 ashish2199 renamed this gist Nov 26, 2017. 1 changed file with 0 additions and 0 deletions.
    0 SQL Server commands and queries → SQL Server commands and queries.md
    View file
    File renamed without changes.
  5. @ashish2199 ashish2199 revised this gist Nov 26, 2017. 1 changed file with 1 addition and 21 deletions.
    22 changes: 1 addition & 21 deletions SQL Server commands and queries
    View file
    Original file line number Diff line number Diff line change
    @@ -321,24 +321,4 @@ Pivot - To convert rows into columns
    (
    MAX(T) for Past_due IN ([Often], [Sometimes], [Never],[NA])
    )
    P2 ORDER BY WI_NAME;

    ---

    Tasks
    - [ ] a task list item
    - [ ] list syntax required
    - [ ] normal **formatting**
    - [ ] incomplete
    - [x] completed

    ------

    Tasks
    - [ ] a task list item
    - [ ] list syntax required
    - [ ] normal **formatting**
    - [ ] incomplete
    - [x] completed

    ---
    P2 ORDER BY WI_NAME;
  6. @ashish2199 ashish2199 revised this gist Nov 26, 2017. 1 changed file with 21 additions and 1 deletion.
    22 changes: 21 additions & 1 deletion SQL Server commands and queries
    View file
    Original file line number Diff line number Diff line change
    @@ -321,4 +321,24 @@ Pivot - To convert rows into columns
    (
    MAX(T) for Past_due IN ([Often], [Sometimes], [Never],[NA])
    )
    P2 ORDER BY WI_NAME;
    P2 ORDER BY WI_NAME;

    ---

    Tasks
    - [ ] a task list item
    - [ ] list syntax required
    - [ ] normal **formatting**
    - [ ] incomplete
    - [x] completed

    ------

    Tasks
    - [ ] a task list item
    - [ ] list syntax required
    - [ ] normal **formatting**
    - [ ] incomplete
    - [x] completed

    ---
  7. @ashish2199 ashish2199 revised this gist Nov 26, 2017. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions SQL Server commands and queries
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,9 @@
    To create a Table
    create table risk_clos_rank(
    id_num int IDENTITY(1,1) NOT NULL,
    username nvarchar(100),
    datetime_of_decision DATETIME
    )
    create table risk_clos_rank(
    id_num int IDENTITY(1,1) NOT NULL,
    username nvarchar(100),
    datetime_of_decision DATETIME
    )

    CREATE TABLE TheNameOfYourTable (
    ID INT NOT NULL IDENTITY(1,1),
  8. @ashish2199 ashish2199 created this gist Nov 26, 2017.
    324 changes: 324 additions & 0 deletions SQL Server commands and queries
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,324 @@
    To create a Table
    create table risk_clos_rank(
    id_num int IDENTITY(1,1) NOT NULL,
    username nvarchar(100),
    datetime_of_decision DATETIME
    )

    CREATE TABLE TheNameOfYourTable (
    ID INT NOT NULL IDENTITY(1,1),
    DateAdded DATETIME DEFAULT(getdate()) NOT NULL,
    Description VARCHAR(100) NULL,
    IsGood BIT DEFAULT(0) NOT NULL,
    TotalPrice MONEY NOT NULL,
    CategoryID int NOT NULL REFERENCES Categories(ID),
    PRIMARY KEY (ID)
    );

    To create a copy of table( doesnt create constraints like primary key, not null , indexes ect)
    SELECT * INTO NewTable FROM OldTable
    Eg. SELECT * INTO clos_ext_bkup FROM clos_ext;

    To create a copy of table with its data (create and insert)
    SELECT expressions INTO new_table FROM tables [WHERE conditions];
    SELECT employee_id AS contact_id, last_name, first_name INTO contacts FROM employees WHERE employee_id < 1000;

    The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list.

    Inserting Data from another table ( only insert)
    INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool'
    INSERT INTO contacts (contact_id, last_name, first_name) SELECT employee_id, last_name, first_name FROM employees WHERE employee_id <= 100;

    Inserting Multiple values
    INSERT INTO table1 (First, Last)
    VALUES
    ('Fred', 'Smith'),
    ('John', 'Smith'),
    ('Michael', 'Smith'),
    ('Robert', 'Smith');

    To add a column
    ALTER TABLE table_name ADD column_1 column-definition,column_2 column-definition,column_n column_definition;
    alter table risk_user_approval_tree add lineusr nvarchar(100);
    ALTER TABLE table ADD columnname BIT CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES

    To add a auto increment
    ALTER TABLE 'tableName' ADD 'NewColumn' INT IDENTITY(1,1);

    To add a column with computed value
    ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5);

    To delete/drop a column
    ALTER TABLE table_name DROP COLUMN column_name;

    To drop a table
    DROP TABLE tablename;

    To modify a column
    ALTER TABLE table_name ALTER COLUMN column_name column_type;

    To update a row
    UPDATE clos_customer_master SET Prev = 'Reactivation' WHERE Prev = 'Reactivate';

    To update a row from select clause
    UPDATE table SET Col1 = i.Col1, Col2 = i.Col2 FROM ( SELECT ID, Col1, Col2 FROM other_table) i WHERE i.ID = table.ID;
    The subquery results are substituted into the outer query. As we need table object in outer query, we need to make an alias of inner query.

    To add a primary key
    ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

    To find the name of constraints
    SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'tablename'

    To find name of Primary key constraint
    SELECT name FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'CLOS_ext';

    Drop primary key
    ALTER TABLE table_name DROP CONSTRAINT constraint_name;

    To rename a column (alter command doesnt work here)
    sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';
    sp_rename 'cl_ff_docm.WINAME', 'WI_NAME', 'COLUMN';

    To rename a table
    sp_rename 'old_table_name', 'new_table_name';

    To top 10% of records
    SELECT TOP(10) * FROM CLOS_EXT

    To find when a table was altered
    SELECT [name] ,create_date, modify_date FROM sys.tables;

    To find which table contains a given column

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

    SELECT OBJECT_SCHEMA_NAME (c.object_id) SchemaName,
    o.Name AS Table_Name,
    c.Name AS Field_Name,
    t.Name AS Data_Type,
    t.max_length AS Length_Size,
    t.precision AS Precision
    FROM sys.columns c
    INNER JOIN sys.objects o ON o.object_id = c.object_id
    LEFT JOIN sys.types t on t.user_type_id = c.user_type_id
    WHERE o.type = 'U'
    -- and o.Name = 'YourTableName'
    ORDER BY o.Name, c.Name

    To find which table has which constraint and on which column.
    Select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE;

    Selcting based on case
    SELECT CASE WHEN <test> THEN <returnvalue>
    WHEN <othertest> THEN <returnthis>
    ELSE <returndefaultcase>
    END AS <newcolumnname>
    FROM <table>

    SELECT ProductNumber, Name, "Price Range" =
    CASE
    WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
    WHEN ListPrice < 50 THEN 'Under $50'
    WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
    WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
    ELSE 'Over $1000'
    END
    FROM Production.Product
    ORDER BY ProductNumber ;

    Adding row numbers to the result //here we are creating
    SELECT
    ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
    name, recovery_model_desc
    FROM sys.databases
    WHERE database_id < 5;

    While loop
    DECLARE @MaxCount INTEGER
    DECLARE @Count INTEGER
    DECLARE @Txt VARCHAR(MAX)
    SET @Count = 1
    SET @Txt = ''
    SET @MaxCount = (SELECT MAX(RowID) FROM ConcatenationDemo)
    WHILE @Count<=@MaxCount
    BEGIN
    IF @Txt!=''
    SET @Txt=@Txt+',' + (SELECT Txt FROM ConcatenationDemo WHERE RowID=@Count)
    ELSE
    SET @Txt=(SELECT Txt FROM ConcatenationDemo WHERE RowID=@Count)
    SET @Count += 1
    END
    SELECT @Txt AS Txt

    DECLARE @i int
    SET @i = 0
    WHILE (@i < 10)
    BEGIN
    SET @i = @i + 1
    PRINT @i
    IF (@i >= 10)
    BREAK
    ELSE
    CONTINUE
    END

    Try / Catch Statements
    BEGIN TRY
    -- try / catch requires SQLServer 2005
    -- run your code here
    END TRY
    BEGIN CATCH
    PRINT 'Error Number: ' + str(error_number())
    PRINT 'Line Number: ' + str(error_line())
    PRINT error_message()
    -- handle error condition
    END CATCH

    To get date in DD/MM/YYYY format
    SELECT CONVERT(varchar, GETDATE(), 103);

    To get all foreign keys refrencing a given table
    EXEC sp_fkeys 'TableName'

    To get datatype, size of columns of a table
    EXEC sp_columns CLOS_EXT;

    To get empty string after concatenation of a string with NULL
    When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

    To compile without executing
    SET NOEXEC ON;
    When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them.




    Updating data from another table
    UPDATE table SET Col1 = i.Col1, Col2 = i.Col2 FROM ( SELECT ID, Col1, Col2 FROM other_table) i WHERE i.ID = table.ID

    Check if column exists in table
    IF EXISTS(SELECT 1 FROM sys.columns
    WHERE Name = N'columnName'
    AND Object_ID = Object_ID(N'schemaName.tableName'))
    BEGIN
    -- Column Exists
    END

    Converting Multi row data into a comma separated string
    DECLARE @Names VARCHAR(8000)
    SELECT @Names = COALESCE(@Names + ', ', '') +
    ISNULL(Name, 'N/A')
    FROM People



    Nvarchar
    allows storing of unicode data

    To remove duplicate rows
    select distinct * into t2 from t1;
    delete from t1;
    insert into t1 select * from t2;
    drop table t2;

    Check if the table exists
    IF (EXISTS (SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'TheSchema'
    AND TABLE_NAME = 'TheTable'))
    BEGIN
    --Do Stuff
    END

    Find tables with given column name
    select * from INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME like '%clientid%'
    order by TABLE_NAME

    Find all user tables
    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

    WITH (NOLOCK)
    is the equivalent of using READ UNCOMMITED as a transaction isolation level. While it can prevent reads being deadlocked by other.

    Fing the last identity inserted into a table
    @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.

    SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.

    IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.

    @@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.
    that is, if there was a second IDENTITY inserted based on a trigger after your insert, it would not be reflected in SCOPE_IDENTITY, only the insert you performed.
    IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

    Identity doesn’t guarantee uniqueness. If you want that, make a PK or add a unique index.

    Allows explicit values to be inserted into the identity column of a table.
    SET IDENTITY_INSERT dbo.Tool ON

    The DBCC CHECKIDENT management command is used to reset identity counter. Example:
    DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
    GO


    DECLARE and SET Varibales
    DECLARE @Mojo int
    SET @Mojo = 1
    SELECT @Mojo = Column FROM Table WHERE id=1;

    Add a Foreign Key
    ALTER TABLE Products WITH CHECK ADD CONSTRAINT [FK_Prod_Man] FOREIGN KEY(ManufacturerID) REFERENCES Manufacturers (ID);

    Add a NULL Constraint
    ALTER TABLE TableName ALTER COLUMN ColumnName int NOT NULL;

    Set Default Value for Column
    ALTER TABLE TableName ADD CONSTRAINT DF_TableName_ColumnName DEFAULT 0 FOR ColumnName;

    Create an Index
    CREATE INDEX IX_Index_Name ON Table(Columns)

    Check Constraint
    ALTER TABLE TableName ADD CONSTRAINT CK_CheckName CHECK (ColumnValue > 1)

    Single Line Comments
    SET @mojo = 1 --THIS IS A COMMENT

    Multi-Line Comments
    /* This is a comment
    that can span
    multiple lines
    */

    User Defined Function
    CREATE FUNCTION dbo.DoStuff(@ID int)
    RETURNS int
    AS
    BEGIN
    DECLARE @result int
    IF @ID = 0
    BEGIN
    RETURN 0
    END
    SELECT @result = COUNT(*)
    FROM table WHERE ID = @ID
    RETURN @result
    END
    GO
    SELECT dbo.DoStuff(0);

    Pivot - To convert rows into columns

    SELECT Wi_name, Often, Sometimes, Never, NA
    FROM
    (
    SELECT Wi_name, Past_due, 'Selected' T, '' F
    FROM NG_CA_MISCELLANEOUS_DETAILS
    ) P1
    PIVOT
    (
    MAX(T) for Past_due IN ([Often], [Sometimes], [Never],[NA])
    )
    P2 ORDER BY WI_NAME;