Common Table Expression

When we use the same set of a particular data multiple times in between the entire script, it make sense to put that in common table expression to use instead of the query.


Simple Example for CTE.


WITH [CTE_name] AS (SELECT EmpId, designation FROM work.dbo.employee)
SELECT RepId
 ,ProductId
 ,ProductName
 ,ProductType
 ,Price
 ,C.Designation 
FROM work.dbo.Product P
 JOIN CTE_Name C 
   ON C.empid = P.RepId

SQL Script to Chage the mode to Single and Multi User

To change from multi to single mode


ALTER DATABASE [Work]   --DB Name
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE


To change from single to multi mode


ALTER DATABASE [Work]   --DB Name
SET MULTI_USER
GO

To get details of MDF and LDF file of a Database

Use [work]   --DB Name
execute sp_helpfile