Swim in SQL server

Archive for the ‘With Example’ Category

Working with Window Functions in SQL Server

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

How to separate Integers from String using T-SQL

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

Inserting default values in SQL-Table

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.

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

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

How to get the last execution Time of Stored Procedure

–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