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
--------------------------------------------------------------------------------------------------------

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