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 .