GROUPBY,ROLLUP AND CUBE In SQL Server

     Here I am discussing about ROLLUP and CUBE in SQL Server. Both functions are used for aggregation. So we can check this with an example.

DECLARE @TransLog TABLE

(

ID INT PRIMARY KEY IDENTITY(1, 1),

ItemName VARCHAR(50) NOT NULL,

CompanyName VARCHAR(50) NOT NULL,

Amount MONEY NOT NULL

)

INSERT INTO @TransLog

SELECT 'Mobile','Walmart',5000

UNION ALL

SELECT 'Mobile','Amazon',6000

UNION ALL

SELECT 'Mobile','Amazon',4000

UNION ALL

SELECT 'Mobile','FlipKart',6000

UNION ALL

SELECT 'TV','Walmart',3000

UNION ALL

SELECT 'TV','Amazon',2000

UNION ALL

SELECT 'TV','FlipKart',8000

UNION ALL

SELECT 'TV','FlipKart',6000

SELECT * FROM @TransLog

     The above table shows Different product and amount received for each product. If we want to calculate total amount received for each product we can use GROUPBY in SQL Server.

SELECT

ItemName, SUM(Amount) AS TotalAmount

FROM @TransLog

GROUP BY ItemName

Next we want item wise total and grand total of whole product , here we are applying ROLLUP

SELECT

COALESCE (ItemName, 'Grand Total') AS ItemName,

SUM(Amount) AS TotalAmount

FROM @TransLog

GROUP BY ROLLUP (ItemName)

     We got item wise total and grand total using ROLLUP .Then Next I want all company wise total and Grand total using CUBE

SELECT

COALESCE (ItemName,'All TransType') AS ItemName,

COALESCE (CompanyName, 'All Company') AS Company,

SUM(Amount) AS Total

FROM @TransLog

GROUP BY CUBE (CompanyName, ItemName)

Split comma separated value using string_split In SQL

           In this short article I will share with an example, how to split and convert a comma separated / delimited string to a different column.We have to create function to split string using delimiter. here i am sharing simple way to split a string in SQL Server by casting data to XML.

DECLARE @TEMPTABLE AS TABLE

(

  Remarks VARCHAR(50)

)

INSERT INTO @TEMPTABLE

SELECT 'Amal,TravelCost,100'

UNION ALL

SELECT 'Arun,TravelCost,200'

UNION ALL

SELECT 'Manu,TravelCost,300'

UNION ALL

SELECT 'Vinu,TravelCost,400'

SELECT * FROM @TEMPTABLE

SELECT

CAST(('<X>'+REPLACE(Remarks,',' ,'</X><X>') +'</X>') AS XML).value('(/X)[1]', 'varchar(128)') AS Customer,

CAST(('<X>'+REPLACE(Remarks,',' ,'</X><X>') +'</X>') AS XML).value('(/X)[2]', 'varchar(128)') AS TransType,

CAST(('<X>'+REPLACE(Remarks,',' ,'</X><X>') +'</X>') AS XML).value('(/X)[3]', 'varchar(128)') AS Amount FROM @TEMPTABLE

       In the above example first we cast the data to XML and split string to separate column by finding the position of XML closing tag.

      You can also split string using STRING_SPLIT function in SQL server .It is a table-valued built in function that splits a string into rows of sub strings, based on a specified separator character.

SELECT VALUE

FROM @TEMPTABLE

CROSS APPLY STRING_SPLIT(Remarks, ',')

      
     If input string contains two or more consecutive occurrences of the delimiter character. Empty sub strings are treated the same as are plain sub strings.

SELECT * FROM  STRING_SPLIT('a;;b', ';')

Dead Lock In SQL Server

     

     A deadlock is a situation where in two transactions wait for each other to give up their respective locks. Transaction A attempts to update table 1 and subsequently read data from table 2, then transaction B attempts to update table 2 and subsequently read data from table 1. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa; neither transaction can complete until the other transaction releases locks. So in this situation SQL server throws exception and kill the process and roll back it for other process

how to trace DeadLock in SQL Server

  Below administrative query will help you to find the DeadLock, If you click on xml data you will get a dead lock report

SELECT

xed.value('@timestamp', 'datetime2(3)') as CreationDate,

xed.query('.') AS XEvent

FROM

(

SELECT CAST([target_data] AS XML) AS TargetData

FROM sys.dm_xe_session_targets AS st

INNER JOIN sys.dm_xe_sessions AS s

ON s.address = st.event_session_address

WHERE s.name = N'system_health'

AND st.target_name = N'ring_buffer'

) AS Data

CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)

ORDER BY CreationDate DESC

Using STUFF and STRING_AGG In SQL Server

        The STUFF function is insert a string into another string .It removes the specified length of characters and insert new string to matching position 

   SELECT STUFF('Hi Rahul', 1, 2, 'Mr') AS Name

       From the above query it will replace Hi to Mr. You can use the STUFF with XML Path for concatenate the column data easily.

DECLARE @TempTable TABLE(Id INT,Name VARCHAR(20))

INSERT INTO @TempTable

SELECT 1, 'Raju'

UNION ALL

SELECT 2, 'Manu'

UNION ALL

SELECT 3, 'Vinu'

SELECT * FROM @TempTable

SELECT

   STUFF(

                (SELECT 
                ',' + Name 
           FROM @TempTable FOR XML PATH ('')), 1, 1, ''

               ) AS NAMES

FROM @TempTable WHERE ID = 1

       

      To handle this exact scenario, MS introduced new functionality called STRING_AGG in SQL 2017. STRING_AGG Concatenates the values of string expressions and places separator values between them.

DECLARE @TempTable TABLE(ID INT,Name VARCHAR(20))

INSERT INTO @TempTable

SELECT 1, 'Raju'

UNION ALL

SELECT 2, 'Manu'

UNION ALL

SELECT 3, 'Vinu'

SELECT * FROM @TempTable

SELECT

     STRING_AGG(Name, ',') AS NAMES

FROM @TempTable

      I believe this would be help full method for what you are trying to accomplish with the XML/STUFF method.