TSQL logics Realtime

#Kill Session
=============



Sp_who2 active

select distinct program_name from sys.dm_exec_sessions

select * from sys.dm_exec_sessions
where program_name like '%SQLAgent - TSQL%'

126

dbcc inputbuffer(126)


--EXEC io.dbo.stp_ORACLE_Run_ExportDTS  
--@SubType = 'Commission',  
--@DependencyType = 'SAP',
---- create ORACLE EVENT DEPENDENCY WHEN IT GETS CREATED   @DependencySubType = 'Event'

kill 126

SPID 126: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.


To ensure whether it is done


kill 126
The sp id not exitst some thining like that




Futher the Debugging to kill the locks
======================================


sp_lock

126 7 0 0 DB                                 S GRANT




#Partition
==========


DROP TABLE T1

CREATE TABLE T1(ID INT , COL1 MONEY , COL2 VARCHAR(100))

TRUNCATE TABLE T1

TRUNCATE TABLE T1
INSERT INTO T1 VALUES (NULL,100,'')
INSERT INTO T1 VALUES (NULL,100,NULL)
INSERT INTO T1 VALUES (1,100,'A')
INSERT INTO T1 VALUES (1,100,'A')
INSERT INTO T1 VALUES (2,100,'A')
INSERT INTO T1 VALUES (2,100,'A')
INSERT INTO T1 VALUES (3,300,'B')
INSERT INTO T1 VALUES (3,300,'B')
INSERT INTO T1 VALUES (4,400,'B')
INSERT INTO T1 VALUES (4,500,'B')


SELECT *, COUNT(*) OVER  (PARTITION BY ID ) AS IDCOUNT, COUNT(*) OVER  (PARTITION BY COL1 ) AS COL1COUNT,
COUNT(*) OVER  (PARTITION BY COL2 ) AS COL2COUNT FROM T1

ID COL1 COL2 IDCOUNT COL1COUNT COL2COUNT
== ==== ==== ======= ========= =========

NULL 100.00 NULL 2 6 1
NULL 100.00 2 6 1
1 100.00 A 2 6 4
1 100.00 A 2 6 4
2 100.00 A 2 6 4
2 100.00 A 2 6 4
3 300.00 B 2 2 4
3 300.00 B 2 2 4
4 400.00 B 2 1 4
4 500.00 B 2 1 4




#Last run Query
===============


SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC


#Go
===


DROP TABLE NULLVALUES_SPARSE

GO

CREATE TABLE NULLVALUES_SPARSE
(
ID INT IDENTITY(1,1) ,
NAME VARCHAR(50) NOT NULL,
MOBILE BIGINT SPARSE NULL,
MOBILE2 BIGINT SPARSE NULL
)


GO
INSERT INTO NULLVALUES_SPARSE VALUES('SAGAR',NULL,NULL)
GO 900
INSERT INTO NULLVALUES_SPARSE VALUES('SAGAR',9600012345,9840012345)
GO 100

-- The top row will insert 900 times
-- The second row will insert 10o times

Very nice...



- To Divide the Group of Statements we use GO

- You can use GO TO in Error Handling Part








#Generate Squence
=================


SELECT @A = ' ALTER TABLE '+@DB+'.'+@SCHEMA+'.'+@REPT+' ADD SEQUENCE VARCHAR(8000)'
EXEC  (@A)


SELECT @A = ' UPDATE VVK SET VVK.SEQUENCE = VVK.SEQ FROM ( SELECT ROW_NUMBER() OVER (ORDER BY SEQUENCE) AS SEQ , *  FROM '+@DB+'.'+@SCHEMA+'.'+@TMPNAME +' )VVK '
EXEC  (@A)


UPDATE VVK SET VVK.COL1 = VVK.SEQ FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS SEQ , *  FROM TEMPTD
)VVK



#Concat
=======


DROP TABLE T2

CREATE TABLE T2(ID INT, NAME VARCHAR(10))

TRUNCATE TABLE T2

INSERT INTO T2 VALUES (1,'A')
INSERT INTO T2 VALUES (1,'A')

INSERT INTO T2 VALUES (2,'B')
INSERT INTO T2 VALUES (2,'C')

INSERT INTO T2 VALUES (3,'D')
INSERT INTO T2 VALUES (4,'D')
INSERT INTO T2 VALUES (5,'E')

INSERT INTO T2 VALUES (6,'F')
INSERT INTO T2 VALUES (6,'G')


CREATE FUNCTION [DBO].ConcatValues (@METHOD VARCHAR(20))
RETURNS VARCHAR(1000)
AS

BEGIN
DECLARE @OUTPUT VARCHAR(1000)

DECLARE @TBL TABLE(ID INT,NAME VARCHAR(20))

INSERT INTO @TBL SELECT DISTINCT ID,NAME FROM T2 WHERE ID = @METHOD

SELECT @OUTPUT = COALESCE(@OUTPUT + ',' , '') + NAME FROM @TBL
WHERE ID = @METHOD
ORDER BY ID

RETURN @OUTPUT
END


SELECT  ID, [DBO].CONCATVALUES(ID) FROM T2







#Stuff
======



DROP TABLE T1

CREATE TABLE T1(NAME VARCHAR(10))

TRUNCATE TABLE T1
INSERT INTO T1 VALUES ('A')
INSERT INTO T1 VALUES ('A')
INSERT INTO T1 VALUES ('B')
INSERT INTO T1 VALUES ('D')
INSERT INTO T1 VALUES ('D')
INSERT INTO T1 VALUES ('E')
INSERT INTO T1 VALUES ('F')

INSERT INTO T1 VALUES (NULL)
INSERT INTO T1 VALUES ('')

GO



Select * from T1

SELECT ',' + NAME FROM  T1 FOR XML PATH('')

SELECT STUFF(',123456789', 1, 1, '')


-- PLEASE BE CAUTIOUS WITH NULL OR '' VALUES
-- We are just replacing the first value comma with empty

DECLARE @A VARCHAR(100)
SELECT @A = STUFF((SELECT ',' + NAME FROM  T1 FOR XML PATH('')), 1,1,'')
PRINT  @A


GO


DECLARE @A VARCHAR(100)
SELECT @A = STUFF((SELECT DISTINCT ',' + NAME FROM  T1 FOR XML PATH('')), 1,1,'')
PRINT  @A


GO


DECLARE @A VARCHAR(100)
SELECT @A = STUFF((SELECT DISTINCT ' and ' + NAME FROM  T1 FOR XML PATH('')), 1,5,'')
PRINT  @A




#Table Variable
===============


Like local variables, it has a limited scope, which is within the batch, function, or stored procedure in which it was declared.

If you need a temporary table in your Transact-SQL code,

consider using a table variable instead of creating a temporary table.

Table variables are created and manipulated in memory instead of the tempdb database,

 making them much faster in some cases.

Table variables require less locking and logging resources.

Stored Procedures containing table variables instead of temporary tables need fewer recompilations.




declare @stdtest table (colname varchar(20), colvalue int)

insert into @stdtest (colname, colvalue) values ('a', 2)
insert into @stdtest (colname, colvalue) values ('b', 4)
insert into @stdtest (colname, colvalue) values ('c', 4)
insert into @stdtest (colname, colvalue) values ('d', 4)
insert into @stdtest (colname, colvalue) values ('e', 5)
insert into @stdtest (colname, colvalue) values ('f', 5)
insert into @stdtest (colname, colvalue) values ('g', 7)
insert into @stdtest (colname, colvalue) values ('h', 9)


declare @std decimal
declare @mean decimal
declare @lower decimal
declare @higher decimal
declare @noofstds int

select @std = STDEV(colvalue), @mean = AVG(colvalue) from @stdtest
set @noofstds = 1
select @lower = @mean - (@noofstds * @std)
select @higher = @mean + (@noofstds * @std)

select @std,@mean,@lower, @higher, * from @stdtest where colvalue between @lower and @higher




# Vs ##
=======



# Local temporary tables are visible only in the current session;
## global temporary tables are visible to all sessions.

Prefix local temporary table names with single number sign (#table_name)
prefix global temporary table names with a double number sign (##table_name).

CREATE TABLE #MyTempTable (col_a INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)

CREATE TABLE ##MyTempTable (col_a INT PRIMARY KEY)
INSERT INTO ##MyTempTable VALUES (1)


If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users,

SQL Server has to be able to distinguish the tables created by the different users.
SQL Server does this by internally appending a numeric suffix to each local temporary table name.
The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the
CREATE TABLE statement and the system-generated numeric suffix.

To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE.





#Trim Multiple Spaces
=====================


-- If you know there won't be more than a certain number of spaces in a row, you could just nest the replace:

SELECT replace(replace(replace(replace('.Net    c#     java','  ',' '),'  ',' '),'  ',' '),'  ',' ')

-- 4 replaces should fix up to 16 consecutive spaces (16, then 8, then 4, then 2, then 1)

-- If it could be significantly longer, then you'd have to do something like an in-line function:


CREATE FUNCTION strip_spaces(@str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
    WHILE CHARINDEX('  ', @str) > 0
        SET @str = REPLACE(@str, '  ', ' ')

    RETURN @str
END


-- Then just do

SELECT dbo.strip_spaces(myText) FROM myTable





#While
======



DECLARE @I INT
SET @I = 1
WHILE (@I <= 100)
BEGIN

IF (@I <=10)
INSERT INTO BIG_TABLE (COL2) VALUES (5)

IF (@I > 10 AND @I <= 50)
INSERT INTO BIG_TABLE (COL2) VALUES (15)

IF (@I > 50 AND @I <= 100)
INSERT INTO BIG_TABLE (COL2) VALUES (25)

SET @I = @I + 1
END




#Merge
======


DROP TABLE TBLSOURCE
DROP TABLE TBLTARGET


CREATE TABLE TBLSOURCE (ID INT ,NAME VARCHAR(20),QTY INT)

CREATE TABLE TBLTARGET(ID1 INT ,NAME1 VARCHAR(20),QTY1 INT)

INSERT INTO TBLSOURCE VALUES(1,'VIVEK',10),(2,'SRINU',20),(3,'ANJI',30),(5,'HYMA',50)

INSERT INTO TBLTARGET VALUES(1,'VIVEK',10),(2,'ANJI',20),(3,'SRINU',20),(4,'ZZZZZ',40)



MERGE TBLTARGET AS T2
USING TBLSOURCE AS T1
ON T1.ID = T2.ID1

WHEN MATCHED AND (T1.NAME != T2.NAME1 OR T1.QTY!= T2.QTY1) THEN
UPDATE SET T2.NAME1 = T1.NAME, T2.QTY1 = T1.QTY

WHEN NOT MATCHED THEN
INSERT VALUES(T1.ID,T1.NAME,T1.QTY)

WHEN NOT MATCHED BY SOURCE THEN
DELETE;

SELECT * FROM TBLSOURCE
SELECT * FROM TBLTARGET








Go





USE tempdb
GO

IF OBJECT_ID('dbo.Dogs', 'U') IS NOT NULL
  DROP TABLE dbo.Dogs;
GO
CREATE TABLE dbo.Dogs
(
dogID INT NOT NULL,
Name NVARCHAR(25) NOT NULL,
BirthDate DATE NULL,
DeathDate DATE NULL,
Weight INT NULL,
HarnessSize NVARCHAR(10)
CONSTRAINT PK_Dogs PRIMARY KEY(dogID)
);

INSERT INTO dbo.Dogs(dogID, Name, BirthDate, DeathDate, Weight, HarnessSize)
VALUES
(1, 'Jewel', NULL, NULL, 42, 'RedGreen'),
(2, 'Mardy', '19970630', NULL, 62, 'Yellow'),
(3, 'Izzi', '6/30/2001', NULL, 44, 'Red'),
(4, 'Topaz', '5/1/2006', '5/21/2006', 53, 'BlueYellow');

SELECT * FROM dbo.Dogs;

-- Define the source, here a TABLE variable. Could just as easily be a
-- persistent table.
IF EXISTS (SELECT * FROM sys.table_types WHERE name = 'typDogUpdates')
  DROP TYPE dbo.typDogUpdates;
GO

CREATE TYPE dbo.typDogUpdates AS TABLE (
dogID INT NOT NULL,
Name NVARCHAR(25) NOT NULL,
BirthDate DATE NULL,
DeathDate DATE NULL,
Weight INT NULL,
HarnessSize NVARCHAR(10)
)
GO

-- Begin the batch
DECLARE @dogUpdates AS dbo.typDogUpdates

INSERT INTO @dogUpdates(dogID, Name, BirthDate, DeathDate, Weight, HarnessSize)
VALUES
(2, 'Mardy', '6/30/1997', NULL, 62, 'Yellow'),
(3, 'Izzi', '6/30/2001', NULL, 39, 'RedGreen'),
(5, 'Raja', NULL, NULL, 42, 'RedGreen');

SELECT * FROM dbo.Dogs

-- Perform a merge
MERGE dbo.Dogs AS Target
USING (SELECT dogID, Name, Birthdate, DeathDate, Weight, HarnessSize
FROM @dogUpdates) AS Source
ON (Target.dogID = Source.dogID)
WHEN MATCHED THEN
UPDATE SET Target.Name = Source.Name,
Target.BirthDate = Source.BirthDate,
Target.DeathDate = Source.DeathDate,
Target.Weight = Source.Weight,
Target.HarnessSize = Source.HarnessSize
WHEN NOT MATCHED THEN
INSERT (dogID, Name, Birthdate, DeathDate, Weight, HarnessSize)
VALUES (Source.dogID, Source.Name, Source.Birthdate, Source.DeathDate,
Source.Weight, Source.HarnessSize)
OUTPUT $action, Inserted.*, Deleted.*;

SELECT * FROM dbo.Dogs
GO












#DelDup
=======




Create table t1(id int,name varchar(20))

insert into t1 values(1,'vv'),(2,'aa'),(1,'vv'),(2,'aa')


Go




with cte (id,name,duplicatecount)
as
(
select id,name,ROW_NUMBER() over  (PARTITION by id,name order by NAME ) as duplicatecount from t1
)
delete from cte where duplicatecount > 1





GO




CREATE PROCEDURE DELDUPREC @TABLENAME VARCHAR(1024)
AS
BEGIN

DECLARE @A NVARCHAR(1024),@B NVARCHAR(1024),@C NVARCHAR(1024),@D NVARCHAR(1024),@E NVARCHAR(1024)

SELECT @D = COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME = @TABLENAME AND COLUMN_NAME = (SELECT TOP 1 COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME = 't1')

SET @A = 'WITH DELDUPREC ('

SELECT @B = COALESCE(@B+',','') + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLENAME

SET @C = ',DUPLICATECOUNT) as ( select ' + @B+ ',ROW_NUMBER() over  (PARTITION by '+@B+' order by '+@D +
' ) as duplicatecount from ' +@TABLENAME +')delete from DELDUPREC where duplicatecount > 1'

SELECT @E = @A+@B+@C
EXEC(@E)
END