Swim in SQL server

–With Text:
SELECT
                 qs.sql_handle,qs.statement_start_offset,qs.statement_end_offset,
                 qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid
FROM      sys.dm_exec_query_stats AS qs
                 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
                 CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
WHERE  st.text like '%S_GET_SerialNos%'
 
 
–Tip2:
SELECT      db_name(database_id) DatabaseName, object_name(object_id) ObjectName, *
FROM          sys.dm_exec_procedure_stats
WHERE      database_id = DB_ID()
ORDER BY database_id

There are 4 Types of Rank functions in SQL Server.
Row_Number() : Row Number will return serial numbers in the result set.
Rank()                   : Rank will returns the rank of each row , if the values are same for more records it will give the same number,if the value is change, then it will give the row number in the partition.
Dense_Rank()    : Dense_Rank will returns the rank of each row,if the values are same, then it will give the same number,if the value is changed ,then it will give the next number of the previous row in the partition.
Ntile(n)                : It will partition the result set into “n” numbers and give the rank to each record.
–Declare a table with name of @CLASSROOM
DECLARE @CLASSROOM TABLE (ID INT IDENTITY, NAME VARCHAR(100) , BRANCH VARCHAR(10) , M1 INT , M2 INT , M3 INT , TOT as (M1+M2+M3) )
–Insert values into @CLASSROOM
INSERT INTO @CLASSROOM
VALUES ( ‘RAJ’ ,‘CSE’ , 60 , 70 , 40 )
              ,( ‘RAHUL’ ,‘CSE’ , 60 , 80 , 30 )
              ,( ‘RAMESH’ ,‘CSE’ , 60 , 60 , 40 )
              ,( ‘RAKESH’ ,‘IT’ , 50 , 70 , 60 )
              ,( ‘RAM’ ,‘IT’ , 50 , 70 , 60 )
              ,( ‘RAVI’ ,‘IT’ , 60 , 70 , 90 )
              ,( ‘RAJESH’ ,‘IT’ , 70 , 30 , 80 )
              ,( ‘ROHITH’ ,‘CSE’ , 50 , 60 , 90 )
              ,( ‘ROCK’ ,‘IT’ , 50 , 70 , 50 )
              ,( ‘RAAGHAV’ ,‘CSE’ , 50 , 60 , 60 )
 
–Ranking functions
SELECT NAME , TOT
              ,ROW_NUMBER() OVER(ORDER BY TOT DESC) [Row_Number]
              ,RANK() OVER(ORDER BY TOT DESC) [Rank]
              ,DENSE_RANK() OVER( ORDER BY TOT DESC) [Dense_Rank]
              ,NTILE(2) OVER(ORDER BY TOT DESC) [Ntile]
FROM @CLASSROOM
 
–Ranking functions With Partition By clause.
SELECT BRANCH,NAME , TOT
              ,ROW_NUMBER() OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Row_Number]
              ,RANK() OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Rank]
              ,DENSE_RANK() OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Dense_Rank]
              ,NTILE(2) OVER(PARTITION BY BRANCH ORDER BY TOT DESC) [Ntile]
FROM @CLASSROOM

Here we can see different methods to separate Integers and String data types from a string variable.

SET NOCOUNT ON

declare @str varchar(100) = 'A1B2C3D4'
DECLARE @I INT = 1,@var varchar(100)='' , @int Varchar(100) = ''

WHILE @I <= LEN(@str)
begin
-- 48 Is ASCII Key for '0' and 57 is for '9'
--Checking Each char is between 0 to 9 or not. If yes then concatenation to @int variable. Else concatenation with @var Variable.
IF @I = 1 SELECT @str 'Input String' , SUBSTRING(@STR,@I,1) 'First Char of the Input String', ASCII(SUBSTRING(@STR,@I,1))  AS 'ASCII Value of the First Char'

IF ASCII(SUBSTRING(@STR,@I,1)) BETWEEN 48 AND 57
--IF PATINDEX('[^0-9-]',SUBSTRING(@STR,@I,1) ) = 0
BEGIN
IF @I = 1 SELECT @str , SUBSTRING(@STR,@I,1), ASCII(SUBSTRING(@STR,@I,1))
SET @int += SUBSTRING(@STR,@I,1)+','
END
ELSE
BEGIN
SET @var += SUBSTRING(@STR,@I,1)+','
END
set @I +=1
end
SELECT @str 'Input String','Integers'= SUBSTRING(@int,1,LEN(@int)-1) , REVERSE(STUFF(REVERSE(@var),1,1,'')) AS [String]
---------------------------Method 2----------------------------
/*
Inserting each character into @TBL table.
Using with CASE Statement displaying and Checking CHAR is Integer or String.
*/
SELECT @I = LEN(@Str)
DECLARE @TBL TABLE (ID INT IDENTITY , [Char] CHAR(1))
WHILE @I >= 1
begin
INSERT INTO @TBL SELECT SUBSTRING(@STR,@I,1)
set @I -=1
end

SELECT [Char],CASE WHEN PATINDEX('%[^0-9-]%',  [Char] ) = 0 THEN 'Integer' ELSE 'Strings' END AS [TYPE]
FROM @TBL
ORDER BY    [TYPE], ID  DESC
--------------------------------Method 3-------------------
SELECT Integers , Strings  FROM
(
SELECT ID, [Char],CASE WHEN PATINDEX('%[^0-9-]%',  [Char] ) = 0 THEN 'Integers' ELSE 'Strings' END AS [TYPE]
FROM @TBL
)  AS P
PIVOT(MAX(CHAR) FOR [TYPE] IN (Integers,Strings)  ) AS PVT
ORDER BY Integers  , Strings

How to insert Identity value in a table?
/*
In the below example we have single Identity column table. Using with "Default Values" we can insert the values into table.
*/
create table #tbl(id int identity)
go

insert into #tbl default values
go 10

select * from #tbl
go

drop table #tbl
go

____________________________________________________________________________________________
CREATE TABLE #TBL1(ID INT IDENTITY , NAME VARCHAR(10) NULL , COUNTRY VARCHAR(10) DEFAULT 'INDIA')
go

INSERT INTO #TBL1 DEFAULT VALUES
GO 10

SELECT * FROM #TBL1
go

drop table #TBL1
go

/*
Here the ID is Identity column , Name will allow the nulls and Country Default value is 'India'.
*/

3 Ways to give alias names in select statement.

SELECT TABLE_NAME=name,[Table ID]= OBJECT_ID , ‘Table Type’=type FROM sys.tables

SELECT name TABLE_NAME, OBJECT_ID [Table ID], type ‘Table Type’ FROM sys.tables

SELECT name AS TABLE_NAME, OBJECT_ID AS [Table ID], type AS ‘Table Type’ FROM sys.tables

Case Statement in SQL Srever

How to use Case Statement in SQL Server? Here i explained Case statement with simple exampleS.

CREATE TABLE #Student(Stud_ID INT IDENTITY, NAME VARCHAR(100), DeptID INT, DOB DATETIME,Gender CHAR(1),JoiningDate Datetime,Marks INT)

INSERT INTO #Student

VALUES (‘ABHISHEK’ , 1 , ’01-JAN-1986′,1, ’01-JAN-2011′,50),

(‘PRASHANTH’, 1 ,’01-JUN-1985′,1,’01-JUN-2010′,70),

(‘RAMS’, 2 ,’01-MAR-1986′,1,’01-MAR-2011′,60),

(‘SRIRAM’, 3 ,’01-JAN-1985′,1 ,’01-JAN-2010′,50),

(‘RAM’, 1 ,’01-JAN-1986′,1,’01-JAN-2011′,20),

(‘ABHI’, 2 ,’01-JAN-1986′,1,’01-JAN-2011′,70),

(‘RANI’, 1 ,’01-JAN-1986′,2,’01-JAN-2010′,80),

(‘JYOTHI’,3 ,’01-JAN-1985′,2,’01-JAN-2011′,90),

(‘PRIYA’, 2 ,’01-JAN-1986′,2,’01-JAN-2011′,34),

(‘ROJA’, 1 ,’01-JAN-1985′,2,’01-JAN-2010′,50)

SELECT*FROM #Student

################# Example 1 #################

/*USING CASE STATMENT IN SELECT

HERE I WANT TO DISPLAY THE GENDER(M,F) ACCORDING TO GENDER COLUMN,IF GENDER=1 THEN M, GENDER= 2 THEN F

*/

SELECT S.Stud_ID , S.NAME , S.DeptID , S.DOB

,CASE S.Gender WHEN 1 THEN ‘M’

WHEN 2 THEN ‘F’

ELSE ‘UNKNOWN’

END AS Gender

,S.Marks,S.JoiningDate

FROM #Student S

/*

Here we are cheking condition based on only single Column(Gender) with equals operator.

So I have Written “S.Gender” After “CASE” Statment.

*/

/*

################# Example 2 #################

Here we can know how to use Conditional Operators in Case Statment.

Using Case Statment we can Give Grade of students.

*/

SELECT S.Stud_ID , S.NAME , S.DeptID , S.DOB

,CASE S.Gender WHEN 1 THEN ‘M’

WHEN 2 THEN ‘F’

ELSE ‘UNKNOWN’

END AS Gender

,S.Marks

,CASE WHEN S.Marks <= 34 THEN ‘FAIL’

WHEN S.Marks >= 35 AND S.Marks <= 59 THEN ‘3rd Class’

WHEN S.Marks >= 60 AND S.Marks <= 74 THEN ‘2nd Class’

WHEN S.Marks >= 75 AND S.Marks <= 100 THEN ‘1st Class’

END AS Grade

,S.JoiningDate

FROM #Student S

ORDER BY Grade , S.NAME

/*

Here am going to show how to use CASE Statment in WHERE Clause.

################ Example 3 ##########################################

*/

SELECT*

FROM #Student S

WHERE (CASE S.DeptID WHEN 1 THEN’CSE’

WHEN 2 THEN ‘CSIT’

WHEN 3 THEN ‘ECE’

WHEN 4 THEN ‘EIE’

END ) = ‘CSE’

/*

Here am going to show, How to use CASE Statment in ORDER BY Clause.

###################### Example 4 ####################

As per my requirement, The first record should be Fixed Value ,

Like In my below example,

If i write Order by Branch, then i will get the output order as CSE , CSIT , ECE .

If i choose Desc Order then output order is ECE,CSIT,CSE.

I want to display CSIT Records first and other branches in desc order.

*/

SELECT S.Stud_ID , NAME , CASE S.DeptID WHEN 1 THEN ‘CSE’

WHEN 2 THEN ‘CSIT’

WHEN 3 THEN ‘ECE’

WHEN 4 THEN ‘EIE’

END AS Branch

,S.Gender,S.JoiningDate

INTO #Tmp_Student

FROM #Student S

SELECT*

FROM #Tmp_Student

ORDER BY CASE Branch WHEN’CSIT’THEN 0 ELSE 1 END,Branch desc, NAME

/*Using with Case Statment , we can Generate CROSS TAB(Crystal Reports)/Matrix (SSRS) Type output.*/

SELECT Branch ,

COUNT(CASE WHEN YEAR(JoiningDate)=2010 THEN Stud_ID ELSE NULL END) AS [2010]

,COUNT(CASE WHEN YEAR(JoiningDate)=2011 THEN Stud_ID ELSE NULL END) AS [2011]

,COUNT(CASE WHEN YEAR(JoiningDate)=2012 THEN Stud_ID ELSE NULL END) AS [2012]

FROM #Tmp_Student

group by Branch

/*

We Can use Case statment in UPDATE Statment also.

Here is the Example.

*/

UPDATE TS SET TS.Gender =(CASE TS.Gender WHEN 1 THEN’M’

WHEN 2 THEN ‘F’

ELSE ‘UNKNOWN’

END)

FROM #Tmp_Student TS

SELECT*FROM #Tmp_Student

/* Clear Temp Memory */

DROPTABLE #Student , #Tmp_Student

Self Join in SQL Server

Self Join:

Self Join is nothing but joining one table with different names. We can use Inner Join or Outer Join to join the table.
Here is the small example which gives the difference between consecutive days of temperature.

CREATE TABLE #TBL (CDATE DATETIME , TEMP INT)

INSERT INTO #TBL
VALUES( ‘2011-11-30 16:11:59.797′ , 20),
(‘2011-12-01 16:11:59.797′ , 30),
(‘2011-12-02 16:11:59.797′ , 10),
(‘2011-12-03 16:11:59.797′ , 50),
(‘2011-12-04 16:11:59.797′ , 20),
(‘2011-12-05 16:11:59.797′ , 70),
(‘2011-12-06 16:11:59.797′ , 40),
(‘2011-12-07 16:11:59.797′ , 30),
(‘2011-12-08 16:11:59.797′ , 10),
(‘2011-12-09 16:11:59.797′ , 50)

SELECT CONVERT(VARCHAR(30) , T.CDATE,6) + ‘———‘ + CONVERT(VARCHAR(30) , TT.CDATE,6) CURRENT_DT
,T.TEMP CURRENT_DATE_TEMP , TT.TEMP TOMORROW_TEMP
, T.TEMP – TT.TEMP DIFF
FROM #TBL T
INNER JOIN #TBL TT ON TT.CDATE = DATEADD(DD,1,T.CDATE )

DROP TABLE #TBL

Follow

Get every new post delivered to your Inbox.