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

GOTO

Alters the flow of execution to a label. The statements that follow GOTO are skipped and processing continues at the label. GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can be nested.
Reference : MSDN_GOTO

Difference between Len() and DataLength() functions

Len() function gives the length of the characters in a string by excluding the trailing spaces, but datalength() function will give the length of characters in a string by including trailing spaces also because it returns the amount of storage required to represent a column.

Datalength() function works on nvarchar and nchar accurately by considering 2 bytes per character where len() function does not.

Datalength() function works on text and ntext where len() function does not works.

Check This code

create table test(val1 char(10), val2 nchar(10))


insert into test values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from test

Output is
3 3   10   20

create table test_v(val1 varchar(10), val2 nvarchar(10))


insert into test_v values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from test_v

Output is
3   3   3   6


create table test_c(val1 text, val2 ntext)


insert into test_c values('sat','sat')


select len(val1),len(val2),datalength(val1),datalength(val2) from  test_c

Above query gives the below error, because len() function not support text data types
Argument data type text is invalid for argument 1 of len function.

select datalength(val1),datalength(val2) from  test_c

Output is
3 6


Reference : MSDN_Len
                   MSDN_DataLength

ROW Functions

@@Rowcount : Returns the number of rows affected by the last statement.
Reference : MSDN_ROWCOUNT   


ROWCOUNT_BIG( ) : This function is also working same as @@rowcount, if the table having more than 2 billion rows then this one will useful.
Reference : MSDN_ROWCOUNT_BIG


SET ROWCOUNT : MSSQL Server stops the query processing after the specified number of rows are returned.
By using the below code we can eliminates the restriction on number of rows processing
SET ROWCOUNT 0

Reference : MSDN_SET_ROWCOUNT

TOP : Limits the rows returned in a query result set to a specified number of rows or percentage of rows.
Reference : MSDN_TOP


ROW_NUMBER : Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Reference : MSDN_ROWNUMBER

RANK : Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row.
Reference : MSDN_RANK

DENSE_RANK : Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
Reference : MSDN_DENSERANK

NTILE : Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Reference : MSDN_NTILE