OUTPUT Clause

Returns information from, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. Output clause works like a trigger, but if we create a trigger it will write all the data into audit table related to that event(Insert or Update or Delete). By using OUTPUT clause we can write required information into audit table.

Check the below code to know how it works :


CREATE TABLE TEST(ID INT IDENTITY(1,1),NAME VARCHAR(10),GENDER CHAR(1))
CREATE TABLE TEST_AUDIT(ID INT,NAME VARCHAR(10),GENDER CHAR(1),LST_UPDT_DTM DATETIME DEFAULT GETDATE())

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT

GO

INSERT INTO TEST
OUTPUT INSERTED.ID, INSERTED.NAME, INSERTED.GENDER
INTO TEST_AUDIT(ID,NAME,GENDER)
SELECT 'SATYA', 'M'

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT

GO

UPDATE TEST SET NAME = 'KRISHNA'
OUTPUT INSERTED.ID, INSERTED.NAME, INSERTED.GENDER
INTO TEST_AUDIT(ID,NAME,GENDER)
WHERE NAME = 'SATYA'

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT

GO

DELETE FROM TEST
OUTPUT DELETED.ID, DELETED.NAME, DELETED.GENDER
INTO TEST_AUDIT(ID,NAME,GENDER)
WHERE NAME = 'KRISHNA'

GO

SELECT * FROM TEST
SELECT * FROM TEST_AUDIT

Transaction Control Language (TCL) :

A Transaction Control Language (TCL)  is a used to control transactional processing in a database.

COMMIT :
To apply the transaction by saving the database changes. We cannot roll back a transaction after a commit transaction statement is issued because the data modifications have been made a permanent part of the database. COMMIT statement releases all row and table locks, and makes the changes to visible for other users.
Syntax :
COMMIT [TRAN | TRANSACTION] [TRANSACTION_NAME | TRANSACTION_VARIABLE_NAME]

ROLLBACK :
To undo all changes of a transaction. Rollback command is used for restore database to original since last commit. Rollback transaction erases all data modifications made from the start of the transaction or to a savepoint. The ROLLBACK statement in SQL cancels the proposed changes in a pending database transaction. The transaction can be rolled back completely by specifying the transaction name in the ROLLBACK statement.
Syntax :
ROLLBACK [TRAN | TRANSACTION ] [SAVEPOINT_NAME | SAVEPOINT_VARRIABLE ]


SAVEPOINT : 
To divide the transaction into smaller sections. It defines breakpoints for a transaction to allow partial rollbacks. Savepoints are useful in situations where errors are unlikely to occur. The use of a savepoint to roll back part of a transaction in the case of an infrequent error can be more efficient than having each transaction test to see if an update is valid before making the update. Updates and rollbacks are expensive operations, so savepoints are effective only if the probability of encountering the error is low and the cost of checking the validity of an update beforehand is relatively high.
Syntax :
SAVE [TRAN | TRANSACTION ] [SQVEPOINT_NAME | SAVEPOINT_VARIABLE_NAME]

What is De-normalization ? Why should we go for De-normalization ?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is a technique to move from higher to lower normal forms of database modeling in order to improve the data retrieval performance.

In a normalized database, more joins are required to gather all the information from multiple tables, as data is stored in multiple tables rather than in one large table. Queries that have a lot of complex joins will require more CPU usage and will adversely affect performance. So in order to improve the query performance we go for  De-normalization process.

What is the differences between TRUNCATE and DELETE ?.

TRUNCATE :

Truncate is a DDL command.
Truncate removes all rows from a table.
Truncate is executed by using a  table and page lock but not each row, So it can not activate a trigger and we cannot rollbacked the transaction.
Truncate resets the seed value if the Identity column exists.
Truncate drops all object statistics.
Truncate is faster because it deletes pages instead of rows.

Syntax : TRUNCATE TABLE TABLE_NAME

DELETE :

Delete is a DML command.
Delete removes specified rows from a table by using where condition and it removes all rows if no where condition.
Delete is executed by using a row lock, So it can activate a trigger and we can rollbacked the transaction.
Delete retain the identity value same.
Delete keeps all object statistics.
Delete is slower than truncate command.

Syntax : DELETE FROM TABLE_NAME  [WHERE COLUMN_ID = 1]

Script to find out the Missing Indexes in Database :

DECLARE @dbid INT

SELECT @dbid = Db_id('database_name')

SELECT d.statement                     AS table_with_missing_index,
       s.avg_user_impact,
       CONVERT(INT, ( unique_compiles + user_seeks + user_scans ) *
                    avg_total_user_cost
                    * (
                    avg_user_impact )) AS index_advantage,
       d.equality_columns,
       d.inequality_columns,
       d.included_columns,
       s.avg_total_user_cost,
       s.unique_compiles,
       s.user_seeks,
       s.user_scans,
       s.last_user_seek
FROM   sys.dm_db_missing_index_group_stats s,
       sys.dm_db_missing_index_groups g,
       sys.dm_db_missing_index_details d
WHERE  s.group_handle = g.index_group_handle
       AND d.index_handle = g.index_handle
       AND database_id = @dbid
ORDER  BY d.statement,
          index_advantage DESC,
          s.avg_user_impact DESC 

Concatenate all the rows values into single row :

Script to create a table and load the data :
CREATE TABLE test_rowconcatenate
  (
     id   INT IDENTITY(1, 1),
     name VARCHAR(10)
  )

SELECT *
FROM   test_rowconcatenate

INSERT INTO test_rowconcatenate
VALUES     ('A'),
            ('B'),
            ('C'),
            ('D'),
            ('E'),
            ('F'),
            ('G'),
            ('H'),
            ('I'),
            ('J'),
            ('K'),
            ('L'),
            ('M'),
            ('N'),
            ('O'),
            ('P')

SELECT *
FROM   test_rowconcatenate

Ways to concatenate all the row values into single record :

1).

DECLARE @string VARCHAR(max) = ''

SELECT @string = @string + ',' + name
FROM   test_rowconcatenate

PRINT Substring(@string, 2, Len(@string))

2).
DECLARE @string VARCHAR(max)='',
        @field  VARCHAR(10)='',
        @i      INT = 1,
        @n      INT

SELECT @n = Count(0)
FROM   test_rowconcatenate

WHILE( @i <= @n )
  BEGIN
      SELECT @field = name
      FROM   (SELECT name,
                     Row_number()
                       OVER(
                         ORDER BY id) row_id
              FROM   test_rowconcatenate) a
      WHERE  row_id = @i

      SET @string = @string + ',' + @field
      SET @i = @i + 1
  END

PRINT Substring(@string, 2, Len(@string))

3).
DECLARE @string VARCHAR(max) ='',
        @field  VARCHAR(10)
DECLARE test_cur CURSOR FOR
  SELECT name
  FROM   test_rowconcatenate

OPEN test_cur

FETCH FROM test_cur INTO @field

WHILE @@Fetch_status = 0
  BEGIN
      SET @string = @string + ',' + @field

      FETCH FROM test_cur INTO @field
  END

CLOSE test_cur

DEALLOCATE test_cur

PRINT Stuff(@string, 1, 1, '')

4).
SELECT Stuff((SELECT ',' + name
              FROM   test_rowconcatenate
              FOR xml path('')), 1, 1, '')

Query to find out the 2nd highest or Nth highest record :

Script to create a table and loading data :

CREATE TABLE test_highest_record
  (
     id     INT IDENTITY(1, 1),
     name   VARCHAR(10),
     salary INT
  )

INSERT INTO test_highest_record
VALUES     ('A',
            10000),
            ('A',
             1000),
            ('B',
             70000),
            ('C',
             40000),
            ('D',
             90000),
            ('E',
             20000),
            ('F',
             300),
            ('G',
             4000),
            ('H',
             11000)

No w check the records in table,

SELECT * 

FROM   test_highest_record 

ORDER  BY 3 

ways to find out the 2nd highest number : 

1). 

SELECT TOP 1 salary 

FROM   (SELECT TOP 2 salary 

        FROM   test_highest_record 

        ORDER  BY 1 DESC) a 

ORDER  BY 1 

2).

SELECT Max(salary) 

FROM   test_highest_record 

WHERE  salary NOT IN (SELECT Max(salary) 

    

                  FROM   test_highest_record) 

3).

SELECT salary 

FROM   (SELECT salary, 

               Row_number() 

                 OVER( 

                   ORDER BY salary DESC) row_id 

        FROM   test_highest_record) a 

WHERE  row_id = 2 

4).

SELECT Max(salary) 

FROM   test_highest_record t1 

WHERE  2 <= (SELECT Count(0) 

             FROM   test_highest_record t2 

             WHERE  t1.salary <= t2.salary) 

To find out the Nth highest Number :

1).

SELECT TOP 1 salary 

FROM   (SELECT TOP @N@ salary 

        FROM   test_highest_record 

        ORDER  BY 1 DESC) a 

ORDER  BY 1 

2).

SELECT salary 

FROM   (SELECT salary, 

               Row_number() 

                 OVER( 

                   ORDER BY salary DESC) row_id 

        FROM   test_highest_record) a 

WHERE  row_id =  @N@

3).

SELECT Max(salary) 

FROM   test_highest_record t1 

WHERE   @N@ <= (SELECT Count(0) 

             FROM   test_highest_record t2 

             WHERE  t1.salary <= t2.salary) 

Note : Replace  @N@ with number .

SSMS Shortcut Keys

Shortcut Keys :

CTRL+R  ===>  Hide the query results window

SHIFT+ALT+ENTER ===> Expand the query window

ALT+F1 ===> Sp_Help

CTRL+1 ===> Sp_who

CTRL+2 ===> Sp_lock

CTRL+U ===> Changing the Database.

CTRL+SHIFT+L ===> Chnaging the code case to Lower

CTRL+SHIFT+U ===> Chnaging the code case to Upper

CTRL+K followed by CTRL+C ===> Commenting the selected Code

CTRL+K followed by CTRL+U ===> Uncommenting the selected Code

CTRL+K followed by CTRL+K ===> To set the bookmark on the line.

CTRL+K followed by CTRL+N ===> To goto the next bookmarked line in the code.

CTRL+K followed by CTRL+P ===> To goto the Previous bookmarked line in the code.

CTRL+K followed by CTRL+L ===> To clear all bookmarks in the code. (Note : It will ask you for confirmation to clear all the bookmarks, if you click yes then only it will clear all the bookmarks)

CTRL+K followed by CTRL+W ===> To manage all the bookmarks in a query window.

CTRL+F ===> To find a spefic keyword.

CTRL+H ===> To replace a spefic keyword with mentioned keyword.

CTRL+G ===> To go to a spefic Line.

CTRL+N ===> To Open a New Query window.

CTRL+O ===> To open a File.

CTRL+TAB ===> To Switch to other query window.

F8 ===> Object Explorer

CTRL+ALT+T ===> Template Explorer

CTRL+ALT+L ===>  Solution Explorer

F4 ===>  Properties window

CTRL+ALT+G ===>  Registered Servers explorer


CTRL+T (before executing the script) ===> To dosplay the results as Text format.

CTRL+D (before executing the script) ===> To display the results in GRID format.

CTRL+SHIFT+F (before executing the script) ===> To get the results in File format.

CTRL+L ===> To display the Query Execution Plan.

CTRL+M ===> To get the query Actual execution plan with query results.

SHIFT+ALT+S ===> To include client statistics.



Reference :  MSDN_SK
               

Get the Weekend Count Between Two dates :

Check This code


declare @todate datetime, @fromdate datetime,@t int,@n int,@i int
select @todate = getdate() , @fromdate = getdate()-12, @t = datediff(dd,@fromdate,@todate), @n = 1 , @i = 0
while(@n <= @t)
Begin
IF datepart(dw,@fromdate) = 1 or datepart(dw,@fromdate) = 7
BEGIN
set @i = @i +1
END
set @fromdate = @fromdate+1
set @n = @n + 1
END
print @i

STUFF FUNCTION

This function inserts a string into another string, it deletes a specified lenght of the characters at the starting position in the expression and inserts the second string at the starting position.

syntax : STUFF ( character_expression , start , length , replaceWith_expression )

start : Integer value it specifies the position to start the insertion.

length : Integer value it specifies the number of characters to delete from start position.

Reference : MSDN_STUFF



Difference between STUFF and REPLACE Functions :

STUFF function deletes a specified length of string from start position and inserts the second string in the expression.

REPLACE function replaces all occurences of the string with the second string in the expression.

Check this code

select stuff('krishna',1,0,'MR. ')

output is
MR. krishna

select replace('krishna', 'k','satya k')

output is
satya krishna