sql server important queries web developer should knows

CREATE TABLE EMP
(
ID INT PRIMARY KEY IDENTITY(1,1) ,
NAME VARCHAR(50),
AGE INT,
SALARY BIGINT
)

--DROP TABLE EMP2

INSERT INTO EMP VALUES('AJAY','21','25000')

INSERT INTO EMP VALUES('DEV2','23','35000')
INSERT INTO EMP VALUES('DEV3','20','45000')
INSERT INTO EMP VALUES('DEV3','24','35000')
INSERT INTO EMP VALUES('DEV4','20','15000')
INSERT INTO EMP VALUES('DEV5','25','25000')
INSERT INTO EMP VALUES('DEV6','28','35000')
INSERT INTO EMP VALUES('DEV7','23','45000')
INSERT INTO EMP VALUES('DEV8','22','65000')
INSERT INTO EMP VALUES('DEV9','27','50000')
INSERT INTO EMP VALUES('DEV10','21','30000')

--TRUNCATE TABLE EMP

1)FIND NTH LENGTH SALARY EMPLOYEE :

 SELECT TOP 1 SALARY FROM(SELECT TOP 5 SALARY FROM EMP ORDER BY SALARY DESC) EMP ORDER BY SALARY ASC

 OR:

 EXEC FINDSAL 2
 ALTER PROC FINDSAL
 @SAL VARCHAR(15)
 AS
 BEGIN
  SET NOCOUNT ON;
        ;WITH CTESALARY AS
             (
   --YOU CAN USE DENSE_RANK() THIS FUNCTION OVER THE ROW_NUMBER()
              SELECT NAME,AGE,SALARY,ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS NTHSALARY
              FROM EMP
             )
    SELECT * FROM CTESALARY WHERE NTHSALARY=@SAL
    SET NOCOUNT OFF;
    END

2)CREATE ANOTHER TABLE FROM EXITING DATABASE TABLE :
  2.1)COPY DATA:
    SELECT * INTO EMP4 FROM EMP WHERE 1=1;
  2.1)COPY STRUCTURE
    SELECT * INTO EMP5 FROM EMP WHERE 1=2;

3)DUPLICATE ROW REMOVE FROM TABLE:

    WITH CTESALARY AS
     (
              --YOU CAN USE DENSE_RANK() THIS FUNCTION OVER THE ROW_NUMBER()
              SELECT NAME,AGE,SALARY,ROW_NUMBER() OVER(PARTITION  BY SALARY ORDER BY SALARY DESC) AS NTHSALARY
              FROM EMP
    )
    DELETE FROM CTESALARY WHERE NTHSALARY>1
    SELECT * FROM EMP

4)HOW TO CONCATENATE MULTIPLE ROWS INTO A SINGLE TEXT STRING :
    SELECT NAME+ ', ' From EMP For XML PATH('')

    OR:

    DECLARE @NAME VARCHAR(MAX);
    SET @NAME= '';

    SELECT @NAME= @NAME+ IsNull(NAME + ', ', '')
    FROM EMP
    Select @NAME AS NAME

    OR:

    DECLARE @NAME VARCHAR(MAX);
    SET @NAME= '';
    SELECT @NAME= @NAME + ISNULL(NAME,'')+','
    FROM EMP
    IF (RIGHT(@NAME, 1) = ',')
    SET @NAME = LEFT(@NAME, LEN(@NAME) - 1)
    SELECT @NAME AS NAME

    OR:

    DECLARE @NAME VARCHAR(MAX);
    SELECT @NAME = COALESCE(@NAME + ',', '') + CAST(NAME AS VARCHAR)
    FROM EMP
    SELECT @NAME AS NAME

    OR:

    SELECT STRING_AGG(CAST(NAME AS NVARCHAR(MAX)),',') NAME  FROM EMP

    OR:

    SELECT STUFF((SELECT ',' + NAME FROM EMP FOR XML PATH ('')), 1, 1, '') AS NAME

5)PRINT 1 TO 100 SQL SERVER
 
    SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY OBJECT_ID) AS NUM
    FROM MASTER.SYS.COLUMNS

    OR:

    WITH CTE AS
    (
SELECT COUNT=1
UNION ALL
SELECT COUNT=COUNT+1
FROM CTE WHERE COUNT <100

    )
    SELECT COUNT FROM CTE

   OR:

  WITH CTE AS
  (
  SELECT NUM=1
  UNION ALL
  SELECT NUM=NUM +1 FROM CTE WHERE NUM <100
  )
  INSERT INTO #SOMEDATA (VAL) SELECT NUM FROM CTE

6)RANDOM ALPHANUMERIC VALUE GENRATE

   DECLARE @VAL NVARCHAR(MAX)
   SELECT @VAL = CONVERT(VARCHAR(255), NEWID())
   SELECT @VAL

   SELECT SUBSTRING('ABCDE546FGH436364IJKLM63N346OPQR6236STUVW463XYZ',
                 (ABS(CHECKSUM(NEWID())) % 26)+1, 10)

   SELECT RIGHT(NEWID(),5)

   SELECT SUBSTRING(CONVERT(NVARCHAR(MAX), NEWID()),0,19)

7)HOW TO COUNT FILED VALUE GREATER THAN ONE IN SQL

   SELECT X.CNT,* FROM TABLENAME A
   OUTER APPLY(
SELECT CNT= COUNT(FILED1) FROM TABLENAME B WHERE B.FILED1=A.FILED1
    )X
    WHERE X.CNT>1

8)FIND A SPECIFIC COLUMN NAME IN ALL TABLES OF A DATABASE IN SQL SERVER

 SELECT C.NAME  AS 'COLUMNNAME',T.NAME AS 'TABLENAME' FROM
 SYS.COLUMNS C
 JOIN SYS.TABLES T ON C.OBJECT_ID = T.OBJECT_ID
 WHERE C.NAME LIKE '%COL1%'
 ORDER BY COLUMNNAME,TABLENAME;

OR:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'COL1'

(9)HOW TO FIND TABLES NAME WHICH STORE PROCEDURE USED IN SQL SERVER
   (i)FIND VIEW AND STORE PROCEDURE USE TABLES

SELECT DISTINCT 'SP_HELPTEXT '''+OBJECT_SCHEMA_NAME(ID)+'.'+OBJECT_NAME(ID)+'''' 
FROM SYSCOMMENTS 
WHERE TEXT LIKE '%'+REPLACE(REPLACE('TBL_NAME',']','\]'),'[','\[')+'%' ESCAPE '\' 
ORDER BY 'SP_HELPTEXT '''+OBJECT_SCHEMA_NAME(ID)+'.'+OBJECT_NAME(ID)+'''';

OR:

  (ii)ONLY STORE USE TABLES

SELECT NAME FROM SYS.PROCEDURES WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TBL_NAME%'

(10)HOW TO FIND STORE PROCEDURE NAME WHICH TABLES USED IN SQL SERVER
    (i)FIND STORE PROCEDURE WHICH TABLES AND OTHER_SP USED

SELECT DISTINCT O.NAME AS 'PROC_NAME' , OO.NAME AS 'TBL_NAME'
FROM SYSDEPENDS D, SYSOBJECTS O, SYSOBJECTS OO
WHERE O.ID=D.ID
AND O.NAME= 'USP_GETDATA'
AND OO.ID=D.DEPID
ORDER BY O.NAME,OO.NAME

(11)HOW TO RETURN RANDOM ROW DATA  IN SQL SERVER

 SELECT TOP 1 *
 FROM
 (
  SELECT *
  FROM EMP
 ) EMPTBL
 ORDER BY NEWID()