Monday, December 24, 2018

What are the Differences between the Temporary Table and Table Variable

Key Differences between the Temporary Table and Table Variable
The following are the Main differences between the Temporary Table and Table Variable.

Temporary Table
Table Variable
The Temporary Tables can be used in Stored Procedures, Triggers and Batches but not in User defined Functions.
The Table Variables can be used in User defined Functions, Stored Procedures, and Batches.
The Local Temporary Tables are Temporary Tables that are available only to the one Session in which we created them. Global Temporary Tables are Temporary Tables that are available to all sessions and all the users. 
The scope of the Table Variable in the stored procedure, user defined function or batch where it is declared like any local variable we create with a DECLARE statement. 
The Local Temporary Tables are automatically destroyed at the end of the Procedure or Session in which we created them. 
Global Temporary Tables are dropped automatically when the last session using the Temporary Table has been completed.
We can also drop Temporary Tables explicitly using Drop command similar to normal table.

The Table variables are automatically cleaned up at the end of the User defined Function, Stored Procedure, or Batch in which they are defined.
The Temporary Table name can be of maximum 116 characters.
The Table Variable name can be of maximum 128 characters.
The constraints like PRIMARY KEY, UNIQUE, CHECK, NULL etc can be implemented at the time of creating Temporary Tables using CREATE TABLE statement or can be added after the table has been created. FOREIGN KEY not allowed.
The constraints like PRIMARY KEY, UNIQUE, CHECK, DEFAULT, NULL can be added, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed. 
The Temporary Tables supports adding Indexes explicitly even after creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint. 
The Table Variables doesn’t allow the explicit addition of Indexes after it is declared, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined at the time of declaring Table Variable.
The Temporary Tables can also be directly created and data can be inserted using Select Into statement without creating a Temporary Table explicitly.
The Table Variables can’t be created using Select Into statement because being a variable it must be declared before use. 
The SET IDENTITY_INSERT statement is supported in Temporary Table.
The SET IDENTITY_INSERT statement is not supported in Table Variables.
We can’t return a Temporary Table from a user-defined function.
We can return a Table Variable from a user-defined function.
The Temporary Table can be truncated like normal table.
The Table Variables can’t be truncated like normal table or Temporary Tables.
The data in the Temporary Table will be rolled back when a transaction is rolled back similar to normal table. 
The data in the Table Variable will not be rolled back when a transaction is rolled back. 
The Temporary Tables used in Stored Procedures cause more recompilations of the stored procedures than when Table Variables are used. 
Table Variables used in stored procedures cause fewer recompilations of the stored procedures than when temporary tables are used.
The Temporary Table will generally use more resources than Table Variable.
The Table Variable will generally use less resources than a temporary table.
The Temporary Tables can be access in Nested Stored Procedures.
The Tables Variables can’t be access in Nested Stored Procedures.
The ALTER command can be used with Temporary Tables.
The ALTER command does not support with Table Variables.
The Temporary Tables should be used for large result sets.
The Table Variables should be used for small result sets and the everyday type of data manipulation since they are faster and more flexible than Temporary Tables.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Monday, December 17, 2018

How to use STRING_SPLIT() Function or XML Method to Split a String or Text into Multiple Rows based on a Delimiter in SQL Server

How to Split a String or Text into Multiple Rows based on a Delimiter using STRING_SPLIT () Function and XML Method in SQL Server
In SQL Server, we often need to split the String or Text, based on delimiters. We can do this using STRING_SPLIT () Function or XML Method as discussed below.

1) Using STRING_SPLIT () Function :
In SQL Server 2016, Microsoft introduced the STRING_SPLIT() inbuilt function to split a string using a specific delimiter. This is the easiest method to split delimited string in SQL Server. To use STRING_SPLIT, the database should be at least in Compatibility level of 130(i.e, SQL Server 2016.)
Syntax:
STRING_SPLIT(StrInput, Delimitor)
Example:
Now will see how we can split a String with different delimiters(like comma, space, pipe..etc).
DECLARE @strInput1 VARCHAR(2000);
DECLARE @strInput2 VARCHAR(2000);
DECLARE @strInput3 VARCHAR(2000);


SET @strInput1 = 'Sunday,01,Tuesday,30,Thursday,04,Saturday'
SET @strInput2 = 'Sunday 01 Tuesday,30,Thursday 04 Saturday'
SET @strInput3 = 'Sunday|01|Tuesday,30,Thursday|04|Saturday'

Select Value As sValues FROM STRING_SPLIT(@strInput1, ',')
Result:
Select Value As sValues FROM STRING_SPLIT(@strInput2, ' ')
Result:
Select Value As sValues FROM STRING_SPLIT(@strInput3, '|')

Result:
Note:
Please note that the STRING_SPLIT() Function will work only for the SQL Server 2016 and its higher versions.
For the lower versions like SQL Server-2008, 2012, 2014.. we can use below XML Method.

2) Using the XML Method :
Declare @str Varchar(4000),
@sXML AS XML


Set @str ='Sunday|01|Tuesday,30,Thursday|04|Saturday'
Set @sXML='<root><i>'+REPLACE(@str,'|','</i><i>')+'</i></root>'

Select LTRIM(RTRIM([i].value('.','varchar(4000)'))) As StrValues
From @sXML.nodes('/root/i') As [Items]([i])


                                                    

3) Using a User defined Function with XML Method :
We can create user defined function using the XML Method to Split a String based on the specified delimiter as follows..
CREATE FUNCTION [udf_SplitString]
  (
    @SrcString VARCHAR(4000),
    @Delim VARCHAR(25)=','
   )
 RETURNS @ResultTable TABLE
  (
    [strID] INT IDENTITY(1,1),
    [sValues] VARCHAR(4000)
   )
AS

BEGIN
    DECLARE @w_xml xml; 
    SET @w_xml=N'<root><i>' + Replace(@SrcString,@Delim,'</i><i>')+'</i></root>';

  INSERT INTO @ResultTable ([sValues])
SELECT
LTRIM(RTRIM([i].value('.', 'VARCHAR(4000)'))) As Value
FROM
@w_xml.nodes('//root/i') AS [Items]([i])
Where LTRIM(RTRIM([i].value('.', 'VARCHAR(4000)')))<>''
RETURN
END
 GO

Now we will check how this function splits the strings based on the specified delimiter.
Select * from udf_SplitString('Sunday,01,Tuesday,30,Thursday,04,Saturday',',');
Result:

Select * from
udf_SplitString('Sunday 01 Tuesday,30,Thursday 04 Saturday', ' ');
Result:

Select * from
udf_SplitString('Sunday|01 Tuesday|30,Thursday |04;Saturday', '|');
Result:

Select * from udf_SplitString('Sunday|01 Tuesday||30,Thursday |||04;Saturday', '|');
Result:

Notes:
We can check the compatibility of our database(s) using the below Query.
Select Name As dbName, Compatibility_level , Version_Name =
CASE Compatibility_level
           WHEN 65 THEN 'SQL Server 6.5'
           WHEN 70 THEN 'SQL Server 7.0'
           WHEN 80 THEN 'SQL Server 2000'
           WHEN 90 THEN 'SQL Server 2005'
           WHEN 100 THEN 'SQL Server 2008/R2'
           WHEN 110 THEN 'SQL Server 2012'
           WHEN 120 THEN 'SQL Server 2014'
           WHEN 130 THEN 'SQL Server 2016'
           WHEN 140 THEN 'SQL Server 2017'
     ELSE 'New/Unknown - '+CONVERT(varchar(10),Compatibility_level) 

END
From Sys.Databases


Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Sunday, December 16, 2018

How to Convert a delimited List of Values from a Row into Multiple Rows based on Delimiter in SQL Server

User Defined Function to Convert a delimited List of Values from a Row into Multiple Rows based on Delimiter in SQL Server
Suppose we have the delimited(eg. comma, space, pipe) List of values as follows...
Select '111,222,333,444,555' as csvDelim_Num
Select '111,Two,333,Fourth,555' as csvDelim_Text
Select 'The Number 3 Is Prime' as spaceDelim_Text
Select '1,First|2-nd|||3_Third|||||1+1+1+1' as pipeDelim_Text

To Split a different kind of delimited List of values, We will created a User defined Function using the combination of multiple String Functions as follows..
CREATE FUNCTION [dbo].[udf_List2Table]
(
@List VARCHAR(8000),
@Delim VARCHAR(5)

)
RETURNS
 @ParsedList TABLE
  (
  Item VARCHAR(4000)
  )

AS
BEGIN
  Declare @Item Varchar(4000), @Pos INT
  Set @List = LTRIM(RTRIM(@List))+ @Delim
  Set @Pos = CHARINDEX(@Delim, @List, 1)


 IF @Delim=' '
  BEGIN
   Set @Delim='='
   Set @List=REPLACE(@List,' ','=')
   Set @List = LTRIM(RTRIM(@List))+ @Delim
   Set @Pos = CHARINDEX(@Delim, @List, 1)

  END 
  
 WHILE @Pos > 0
  BEGIN
   SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos-1)))
   IF @Item <> ''
    BEGIN
     Insert Into @ParsedList (Item)
     Values (CAST(@Item As Varchar(4000)))
    END

   SET @List = RIGHT(@List, LEN(@List) - @Pos)
   SET @Pos = CHARINDEX(@Delim, @List, 1)

  END

RETURN
END
GO

Now we can Pass the delimited List of values into the above function to split them based on the specified delimiter.
SELECT CAST(Item AS INT) As sValues
FROM dbo.udf_List2Table('111,222,333,444,555',',')
GO
Result:

SELECT Item As sValues
FROM dbo.udf_List2Table('111,Two,333,Fourth,555',',')
Result:
SELECT Item AS sValues
FROM dbo.udf_List2Table('1,First|2-nd|||3_Third|||||1+1+1+1','|')
GO
Result:
SELECT Item As sValues
FROM dbo.udf_List2Table('The Number 3 Is Prime',' ')

Result:
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

How to Convert Multiple Rows from a Column into a Comma Separated Values List in SQL Server

How to Convert Multiple Rows from a Column into a CSV List in SQL Server
Suppose we have the Table with data as follows..
Declare @RegionTable As Table
(
RgnName Varchar(50)
)

Insert Into @RegionTable 
Values ( 'East'),('West'),('123.456'),('South'),('987.654'),('North')

Select * From @RegionTable
GO

Now we can convert these rows into a single List of Comma Separated Values using various Methods as follows..

1) Using COALESCE Function:
The COALESCE Function evaluates the arguments in order and always returns first non-null value from the defined argument list.
Declare @RegionTable  As  Table
(
RgnName Varchar(50)
)

Insert Into @RegionTable Values ( 'East'),('West'),('123.456'),('South'),('987.654'),('North')
Select * From @RegionTable


Declare @listStr Varchar(4000)
Select @listStr = COALESCE(@listStr+',' ,'') + RgnName
From @RegionTable
Select @listStr As 'RgnList'

GO

Output:



2) Using SubString Function:
Declare @listStr Varchar(4000)
Set @listStr = ''
Select @listStr = @listStr + RgnName + ','
From [dbo].[RegionTable]
Select Substring(@listStr , 1, LEN(@listStr)-1) As 'RgnList'

GO

3) Using SubString Function with XML Method :
Select Substring((Select ',' + r.[RgnName] From @RegionTable r
                             Order By r.[RgnName]
                             FOR XML PATH(''))
,2,150000) AS RgnList
GO
Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

How to Split the Comma Separated Values from a Column into Multiple Rows in SQL Server

How to use XML Method to Split the Comma Separated Values from a Column into Multiple Rows in SQL Server
Suppose the Source Table as follows..

DECLARE @csvData TABLE
                           (
                              EmpID INT,
                              Qalifi VARCHAR(4000)
                            )

Insert into @csvData Values(1,'B.E.,123.45, MBA, 986.54'), (2,'M.Com.,45.67'), (3,'M.Sc.,M.Tech.')
Select * From @csvData
GO

In the above Table(@csvData) , in the Column 'Qualfi' we have the Comma Separated Values. Now we can convert and separate those values in multiple rows using the below Query with XML Method.
DECLARE @csvData TABLE
(
EmpID INT,
Qalifi VARCHAR(8000)
)

Insert into @csvData Values(1,'B.E.,123.45, MBA, 986.54'), (2,'M.Com.,45.67'), (3,'M.Sc.,M.Tech.')
Select * From @csvData


Select EmpID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(4000)'))) As Qalifi
From
(
Select EmpID, CAST( ( '<XMLRoot><RowData>' +

                                       REPLACE(Qalifi,',','</RowData><RowData>') + 
                                      '</RowData></XMLRoot>' ) As XML
                                 ) As X
From   @csvData
) T1

CROSS APPLY X.nodes('/XMLRoot/RowData')m(n)
GO
Notes:
In the above Query, we can use any letters instead of m, n.
Output:

Note:
The above query can written as per below as well.
Select EmpID, LTRIM(RTRIM([i].value('.','varchar(4000)'))) As Qalifi
From
        (
           Select EmpID, CAST( ( '<root><i>' +
                                                 REPLACE(Qalifi,',','</i><i>') +
                                                 '</i></root>' ) As XML
                                                ) As X
           From @csvData
         ) T1
CROSS APPLY X.nodes('/root/i') As [Items]([i])
GO

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Saturday, December 15, 2018

How to Find the Employees having same Salary By Department in SQL Server

SQL Sub Query with Count(*) Function, Over(Partition By..) Clause to Find the Employees having Same Salary By Department
Suppose we have the Employee and Department tables as follows..
Select * From [dbo].[Tbl_EmpDetails]


Select * From [dbo].[Tbl_Dept]

Now our objective here is to find and select the Employees who are drawing Same Salary.
We achieve that using the Count (*) Function with Over (Partition By..) Clause in Subquery as discussed below.
1) Employees Drawing the Same Salary :
We can find the Employees drawing the Same Salary by using the following Subquery on the Employee table as per below.
SELECT Emp_Id, EmpName, NetSal From
 (
 SELECT Emp_Id,EmpName, NetSal, Count(*) Over (Partition by NetSal) as SalaryCnt
 FROM [dbo].[Tbl_EmpDetails]
 ) S1
WHERE SalaryCnt>1
ORDER By NetSal Desc

Result :

2) Employees Drawing the Same Salary with Dept Details :
We can find the Employees drawing the Same Salary, with their Department Details using the following Subquery on the Employee and Department Tables as below.
SELECT S1.Emp_Id,S1.EmpName,S1.DeptName, S1.NetSal
FROM(Select E.*, D.DeptName,Count(*) Over (Partition by E.NetSal) as SalaryCnt
            From [dbo].[Tbl_EmpDetails] E
   Inner Join [dbo].[Tbl_Dept] D
   ON E.Dept_Id=D.DeptId
  ) S1
WHERE S1.SalaryCnt > 1
ORDER By S1.NetSal Desc

Result:

3) Employees Drawing the Same Salary By Department :

We can find the Employees drawing the Same Salary By Department using the following Subquery on the Employee and Department Tables as below.
SELECT S1.Emp_Id,S1.EmpName,S1.DeptName, S1.NetSal
From (Select E.*, D.DeptName,
                     Count(*) Over (Partition by D.DeptName,E.NetSal) as SalaryCnt
          From [dbo].[Tbl_EmpDetails] E
   Inner Join [dbo].[Tbl_Dept] D
   ON E.Dept_Id=D.DeptId
  ) S1
WHERE S1.SalaryCnt > 1
ORDER By S1.NetSal Desc

Result:

Note:
Since No two Employees in the IT Department is having the Same Salary so that that record has been excluded.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Thursday, December 13, 2018

How to generate a Range of Sequence Numbers and Random Numbers in SQL Server

How to generate a Sequence of Numbers and Random Numbers in SQL Server
In some Scenarios, we need to generate a set of Sequence of Numbers or Random Numbers in SQL Server. This can be done in numerous ways as discussed below.
Random Unique ID :
The NEWID() function will generate a  unique identifier(alpha-numeric) on each execution.
This is the logical id format that uses to assign to each record across the Servers/Databases by the SQL SERVER.
Select NEWID() As Random_UID
Result :
223F240E-C068-4F6E-ACB9-76CEDD5504F6

Random Number :
The RAND() function will generate a unique random number between 0 and 1 on each execution.
Select RAND() As Random_Number
Result:
0.0458571872134441

If you want re-generate the same unique random number on each execution, then set the seed for it. The seed can be any integer.
Select RAND(10) As Random_Number
0.713759689954247
Select RAND(50) As Random_Number
0.714505008804575

Random Unique ID Int Type :
We can use the CHECKSUM () Function with NEWID() Function to generate a quite unique integer value from the unique identifier.
Select (CHECKSUM(NEWID())) As Random_UID_Int

Now with the Combination of above Functions, we can generate the Random Numbers between the specified ranges as follows...

Random Number between -100 to 100 (inclusive of -100 and 100) :
We can generate a Random Number between -100 and 100 (inclusive) on each execution of the below Query. We are doing module division with 101 to get 100 in random. 

Select (CHECKSUM(NEWID()))%101 As [Random_Number_btw_-100_100]

Random Number between 0 to 100 (inclusive of 0 and 100) :
We can generate a Random Number between 0 and 100 (inclusive) on each execution of the below Query. We are doing module division with 101 to get 100 in random and taking only the Absolute Value(+ve). We can use 201 to get a random number between 0 to 200.  
Select ABS(CHECKSUM(NEWID()))%101 As [Random_Number_btw_0_100]

Random Number between 0 to 100 (inclusive of 0 and 100) :
Select CAST(RAND(CHECKSUM(NEWID())) * 100 as INT) + 1  AS [Random_Number_btw_1_100]
OR
Select FLOOR(RAND(CHECKSUM(NEWID()))*(100-1+1)+1) AS [Random_Number_btw_1_100]
Result:

Till now we discussed about only generating one random number. Now will see how we can generate the Sequence of Numbers and Random Numbers Sequence using the Common
Table expressions as follows.

Sequence of Numbers from 1 to 100 with use of CTE :
We can generate the Sequence of Numbers from 1 to 100 using the below CTE Query. The no.of recursions used here is 100. In SQL Server, the Max no.of allowed recursions are 32767, so that we need to make sure that the recursions specified should be less than the Max allowed.

WITH SeqOfNumbers (Num)
AS (
-- Anchor member definition
SELECT 1
UNION ALL
-- Recursive member definition
SELECT
sn.Num + 1
FROM SeqOfNumbers
sn
WHERE sn.Num < 100
)
-- Statement that executes the CTE
SELECT Num As NumberSeq
FROM SeqOfNumbers
OPTION (MAXRECURSION 32767);

GO

Result:
In the following result we are showing only the first 15 rows out of 100.

Notes :
The no.of recursions used here is 100. In SQL Server, the Max no.of allowed recursions are 32767, so that we need to make sure that the recursions specified should be less than the Max allowed.
If we use more than 32767 recursions(eg: if we use 33000 instead of 100 in above), we will get the following error.
Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.

Caution Note:
To avoid the limitations on the Max no.of recursions, we can set the option as OPTION (MAXRECURSION 0), but it leads to infinite no.of loops if you don't specify your recursions correctly.

Sequence of Numbers from 1 to 100 with use of CTE :
We can generate the Random Numbers Sequence from 1 to 100 using the below CTE Query.
WITH RandomNumbers (SeqNum, RandomNum) AS (
-- Anchor member definition
SELECT 1 AS SeqNum,
(CAST(RAND(CHECKSUM(NEWID())) * 100 as INT) + 1) AS RandomNum
-- FLOOR(RAND(CHECKSUM(NEWID()))*(100-1+1)+1) AS RandomNumber
UNION ALL
-- Recursive member definition
SELECT rn.SeqNum + 1 ,
(CAST(RAND(CHECKSUM(NEWID())) * 100 as INT) + 1)
--FLOOR(RAND(CHECKSUM(NEWID()))*(100-1+1)+1)
FROM RandomNumbers rn
WHERE rn.SeqNum < 100
)
-- Statement that executes the CTE
SELECT rn.SeqNum, rn.RandomNum
FROM RandomNumbers rn
OPTION (MAXRECURSION 500);
GO


Result:

In the following result we are showing only the first 15 rows out of 100.

Note:
Please note that only the Random Numbers Sequence will change on each execution but not the Sequence Numbers.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog