Pivot Example

SELECT * FROM Sales

SELECT EmpId, [2005], [2006], [2007]

FROM (SELECT EmpId, Yr, Sales FROM Sales) AS s

PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007])) AS p

Output

EmpId	Yr	Sales
1	2005	12000
1	2006	18000
1	2007	25000
2	2005	15000
2	2006	6000
3	2006	20000
3	2007	24000

EmpId	2005	2006	2007
1	12000	18000	25000
2	15000	6000	NULL
3	NULL	20000	24000

Coalesce

select StatusDes

from tblstatusmaster

DECLARE @DepartmentName VARCHAR(1000)

SELECT @DepartmentName = COALESCE(@DepartmentName,'') + StatusDes + ';' 
FROM  tblstatusmaster

SELECT @DepartmentName AS DepartmentNames

StatusDes

Scheduled

Registered

Scan Completed

Report Completed

In Progress

Report Despatched

DepartmentNames

Scheduled;Registered;Scan Completed;Report Completed;In Progress;Report Despatched;

Begin Tran and End Tran Example

BEGIN TRAN T1  

 ( code) 

  IF 

                            @@ERROR<> 0  

       BEGIN 

         RAISERROR ('ERROR OCCURS SELECTING.', 16, 1)  

         ROLLBACK  TRAN T1  

         RETURN 

       END 

  ELSE   

       BEGIN 

        COMMIT 

        TRAN T1  

       END   

 

Use the sp_executesql stored procedure instead of the EXECUTE statement.

·         Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:

DECLARE @Query VARCHAR(100)
DECLARE @Age INT
SET @Age = 25
SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' +CONVERT(VARCHAR(3),@Age)
EXEC (@Query)

If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,

DECLARE @Query NVARCHAR(100)
SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age'
EXECUTE sp_executesql @Query, N'@Age int', @Age = 25

the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.

Check Linked Server whether its active

i.Through Ping Command

Declare @status int

EXEC @status=master.dbo.xp_cmdshell 'ping 172.19.1.4 -n 1',no_output

Select @status

GO

 

ii.DECLARE @srvr NVARCHAR(128), @statusval INT;
SET @srvr = '172.19.1.1\SQLEXPRESS';
BEGIN  TRY
  EXEC @statusval = sp_testlinkedserver @srvr;END
TRY
BEGIN CATCH
SET @statusval = SIGN(@@ERROR);END
CATCH;
IF @statusval <> 0
RAISERROR('Unable to connect to linked server. Try later!', 16, 2 );

All Linked Server Info

Select * From sysservers
  

wait statistics -Dynamic Management Views

Select * from sys.dm_os_wait_stats 
Select * from sys.dm_os_waiting_tasks

Check Column exists in table, if not, add

Check For a Column Exists In A Table If Not Add

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName' AND COLUMN_NAME ='ColumnName')

BEGIN

ALTER TABLE TableName ADD ColumnName varchar(50) NULL

END

Delete A Table as well as Temp Table in SQL

2 Ways are there to Delete A Temp Table

1st Way

IF OBJECT_ID('tempdb..#mytemptable') > 0 
    DROP TABLE #mytemptable

2nd Way

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table]')
                   AND type in (N'U'))


DROP TABLE [dbo].[Table]