DataBase Backup In Single Query

BACKUP DATABASE DATABASENAME NAME TO DISK='D:/DATABASE.BAK'

DataBase Backup Query

DECLARE @PATHNAME     NVARCHAR(512), 
        @DATABASENAME NVARCHAR(512) 

SET @DATABASENAME = 'DATABASENAME' 
SET @PATHNAME = 'D:\' + @DATABASENAME + '-' 
                + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' 
                + REPLACE((CONVERT(VARCHAR(8), GETDATE(), 108)), ':', '-') 
                + '.BAK' 

BACKUP DATABASE @DATABASENAME TO DISK = @PATHNAME WITH NOFORMAT, NOINIT, NAME = 
N'', SKIP, NOREWIND, NOUNLOAD, STATS = 10 

GO 

Trigger to Automatically Manage the Modified Date Field

CREATE TRIGGER TRIGGEREMPUPDATE 
ON DBO.EMPLOYEE 
AFTER UPDATE 
AS 
  BEGIN 
      UPDATE [DBO].EMPLOYEE 
      SET    MODIFIEDDATE = GETDATE() 
      WHERE  EMPLOYEE.EMPID IN (SELECT DISTINCT INSERTED.EMPID 
                                FROM   INSERTED) 
  END 

Column Exists In SQL Server Table

IF EXISTS
(
          SELECT 1 
          FROM   SYS.COLUMNS 
          WHERE  NAME = N'EMPID' 
                       AND OBJECT_ID = OBJECT_ID(N'EMPLOYEEMAST')
) 
BEGIN 
          PRINT 'COLUMN EXISTS !!' 
END 

Using Common Table Expression (CTE) In Sql Server

WITH EMPCTE (EMP_CODE,  EMP_NAME,  REMARK) 
AS ( SELECT EMP_CODE, 
                     EMP_NAME, 
                     REMARK, 
                     BIRTHDATE, 
                     JOINDATE 
         FROM   EMPMAST
      ) 

SELECT EMP_CODE, 
            EMP_NAME, 
            REMARK 
 FROM   EMPCTE  AS CTE 

Find The Number Of Users Connected To A SQL Server DATABASE

SELECT  D_E_S.DATABASE_ID,
DB_NAME(D_E_S.DATABASE_ID) AS DataBaseName,
COUNT(D_E_S.SESSION_ID) AS Total_Connection,
D_E_S.ORIGINAL_LOGIN_NAME AS Org_Login_Name,
D_E_S.HOST_NAME AS ComputerName
FROM SYS.DM_EXEC_SESSIONS D_E_S
WHERE D_E_S.DATABASE_ID = 5
GROUP BY D_E_S.DATABASE_ID,D_E_S.ORIGINAL_LOGIN_NAME,D_E_S.HOST_NAME
ORDER BY 1,2,3

Table Variables With Using Primary Keys and Indexes

Declaring a Table Variable with a Primary Key

DECLARE @SQLFORMMAST TABLE
(       
           FORMID INT NOT NULL,        
           FORMNAME VARCHAR(100),        
           DESCRIPTION NVARCHAR(100),    
           PRIMARY KEY (FORMID)
)

Declaring a Table Variable with a Composite Primary Key

DECLARE @SQLFORMMAST TABLE

(       

           FORMID INT NOT NULL,        

           FROMNAME VARCHAR(100),        

           DESCRIPTION NVARCHAR(100)               

  PRIMARY KEY (FORMID)

)

Declaring a Table Variable with a Composite Unique Index

DECLARE @SQLFORMMAST TABLE

(       

           FORMID INT PRIMARY KEY,        

           FORMNAME VARCHAR(100),        

           DESCRIPTION NVARCHAR(100)               

  UNIQUE CLUSTERED (FORMID) 

)