How to use Common Table Expressions In SQL Server

Common Table Expressions offer the same functionality as a view,But you can select,delete,update tables using CTE and you do not have to store definition metadata 

CREATE TABLE #TABLECTE(NAME VARCHAR(20),AGE INT); --Temp Table Creation

INSERT INTO #TABLECTE VALUES('RAFEL',20);

INSERT INTO #TABLECTE VALUES('JAMES',18);

INSERT INTO #TABLECTE VALUES('JOHN',20);

INSERT INTO #TABLECTE VALUES('SYAM',21);

INSERT INTO #TABLECTE VALUES('JACK','');

WITH CTE_Sample (EMP_ID,EMP_NAME,AGE)

AS

(

      SELECT ROW_NUMBER() OVER(ORDER BY NAME), NAME,AGE from #TABLECTE

     

)

--SELECT *  FROM CTE_Sample  --- TO SELECT CTE WITH ROWNUMBER

--WHERE AGE=20

 --UPDATE CTE_Sample  ----  UPDATE #TABLECTE USING  CTE CTE

 --SET AGE=40

 --WHERE EMP_NAME='JACK'

 --DELETE  FROM CTE_Sample --- DELETE #TABLECTE USING CTE

 --WHERE AGE=21 

 SELECT * FROM #TABLECTE ---- SELECT AFTER TABLE OPERATION

DROP TABLE #TABLECTE

Database Was in Suspect Mode, Now Detached, Can't Recover

Steps for Recovering Detached Database to Suspected State 

1. Create a DB with Same Name as the Lost DB.
2. Stop SQL Server Service.
3. Replace the .mdf & .ldf files with Old Files(Lost DB).
4. Start SQL Server Service.

Query For Recovering Suspected Database to ONLINE State 

EXEC sp_resetstatus 'yourDBname';

ALTER DATABASE 'yourDBname' SET EMERGENCY

DBCC checkdb('yourDBname')

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

How to Delete Duplicate rows from a table ?

--//Create ProductMaster table

CREATE TABLE ProductMaster

(

    ProductID INT,

    ProductName VARCHAR(25)

)

--//Insert Rows with Duplicate Values

INSERT INTO ProductMaster

(ProductID, ProductName)

VALUES

(1, 'Product-1'),

(1, 'Product-1'),

(2, 'Product-2'),

(3, 'Product-3'),

(3, 'Product-3'),

(4, 'Product-4'),

(5, 'Product-5'),

(5, 'Product-5'),

(5, 'Product-5')

--//Let's check the values inserted

SELECT * FROM ProductMaster

--//Query to Delete Duplicate Rows

;WITH CTE_Products

AS

(

    SELECT

    ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY ProductID) AS Num,              ProductID

    FROM ProductMaster

)

DELETE FROM CTE_Products WHERE Num > 1

--//Let's check Row in the ProductMaster table

SELECT * FROM ProductMaster

--//Clean the ProductMaster Table

DROP TABLE ProductMaster