Finally cleared MCITP - SQL Server 2008 Developer

Finally cleared MCITP - SQL Server 2008 Developer

Convert 100000 to 100,000

Check this out;

--with .00
Select CONVERT(varchar(20), CAST(12342 AS money),1)

--without .00
SELECT REVERSE(SUBSTRING(REVERSE(CONVERT(varchar(20), CAST(1234266666 AS money),1)), 4, 20))

Delete duplicate records in a table

A simple CTE to delete duplicate records;

;with wcte as (
Select Row_Number() OVER (partition By Column1 Order By Column1) as ROW ,Column1,Column2
from YourTable )
Delete from wcte where Row >= 2

Check Blocking in Database

Here is the script;


Declare @vTable table(SPID int,[Status] nvarchar(256),Login nvarchar(256),HostName nvarchar(256),
BlkBy nvarchar(256),DBName nvarchar(256),Command nvarchar(max),CPUTime int
,DiskIO int,LastBatch nvarchar(256),ProgramName nvarchar(256),SPID1 int,REQUESTID int)

Insert into @vTable
        Exec sp_who2

Select SPID,Status,HostName,BlkBy as BlockBy, DBName, Command, CPUTime,DiskIO, ProgramName,objectid,text
from (Select a.sql_handle,b.* from sys.dm_exec_requests a
Inner Join @vTable b on b.SPID = a.session_ID) Main
cross apply sys.dm_exec_sql_text (Main.sql_handle)
where BlkBy <> ' .'