Sql Server Read Nvarchar(Max) Cursor and Delete Lock
Introduction
One of the principal functions of a Business Intelligence team is to enable business users with an agreement of data created and stored by business systems. Understanding the data should give business users an insight into how the business organization is performing. A typical understanding of data within an insurance industry could relate to measuring the number of claims received vs successfully processed claims. Such data could be stored in source system as per the layout in Table one:
Table 1: Sample policy claims data
| RecKey | PolID | PolNumber | PolType | Effective Engagement | DocID | DocName | Submitted |
| 1 | ii | Pol002 | Hospital Cover | 01-Oct-07 | 1 | Doc A | 0 |
| 2 | two | Pol002 | Infirmary Cover | 01-Oct-07 | four | Medico B | 0 |
| 3 | 2 | Pol002 | Hospital Cover | 01-Oct-07 | 5 | Doc C | 1 |
| 4 | 2 | Pol002 | Hospital Cover | 01-Oct-07 | 7 | Dr. D | 1 |
| five | 2 | Pol002 | Infirmary Cover | 01-Oct-07 | ten | Doc Eastward | 1 |
Although each data entry in Table ane has a unique RecKey identifier, it all still relates to a single policy claim (policy Pol002). Thus, a correct representation of this information ought to exist in a single row that contains a single instance of policy Pol002 as shown in Tabular array 2:
Table 2 Transposed layout
| PolNumber | PolType | Constructive Date | Doc A | Dr. B | Dr. C | Dr. D | Doc East |
| Pol002 | Infirmary Comprehend | 01-Oct-07 | 0 | 0 | one | 1 | one |
The objective of this article is to demonstrate dissimilar SQL Server T-SQL options that could be utilised in order to transpose repeating rows of data into a unmarried row with repeating columns as depicted in Table 2. Some of the T-SQL options that will be demonstrated will utilize very few lines of code to successfully transpose Table 1 into Table two merely may non necessary be optimal in terms query execution. Therefore, the execution plan and I/O statistics of each T-SQL option will be evaluated and analysed using ApexSQL Plan.
Selection #1: Pivot
Using a T-SQL Pin role is i of the simplest method for transposing rows into columns. Script i shows how a Pivot function tin can exist utilised.
| SELECT * FROM ( SELECT [ PolNumber ] , [ PolType ] , [ Effective Date ] , [ DocName ] , [ Submitted ] FROM [ dbo ] . [ InsuranceClaims ] ) AS SourceTable PIVOT ( AVG ( [ Submitted ] ) FOR [ DocName ] IN ( [ Md A ] , [ Medico B ] , [ Doc C ] , [ Dr. D ] , [ Medico E ] ) ) AS PivotTable ; |
The results of executing Script 1 are shown in Effigy i, as information technology can be seen, the output is exactly similar to that of Tabular array ii.
Figure ane
Furthermore, as we add more policy numbers in our dataset (i.e. Pol003), we are able to automatically think them without making any changes to Script 1.
Figure two
Although, we don't take to modify the script to bear witness additional policies, nosotros unfortunately have to update it if we need to return more columns. This is because the Pin part works with but a predefined list of possible fields. Thus, in order to return [Doc F] column, we would firstly demand to update the FOR clause in Script 1 to include [Doc F] and only and then would the output reflect [Doc F] as shown in Figure 3.
Figure 3
However, imagine if business organisation later decides to add 100 more than documents that are required to process a claim? It would mean that you demand to update your Pivot script and manually add those 100 fields. Thus, although transposing rows using Pivot operator may seem simple, it may later be difficult to maintain.
Performance Breakdown
The bodily estimated plan depicted in Effigy 4, indicates that only a single scan was made against the base table with a bulk of the cost (at 77.6%) used for sorting information.
Effigy 4
In terms of operational tree, the highest increased in I/O was recorded during the Sort performance at 0.01 milliseconds.
Figure v
Option #2: CURSOR
Although the general consensus in the professional community is to stay abroad from SQL Server Cursors, at that place are all the same instances whereby the use of cursors is recommended. I suppose if they were totally useless, Microsoft would have deprecated their usage long ago, right? Anyway, Cursors present us with another option to transpose rows into columns. Script 2 displays a T-SQL lawmaking that tin can be used to transpose rows into columns using the Cursor role.
| 1 ii iii 4 v 6 7 8 9 10 eleven 12 13 fourteen 15 sixteen 17 xviii 19 twenty 21 22 23 24 25 26 27 28 29 30 31 | DECLARE @ PolNumber NVARCHAR ( 255 ) , @ PolNumber5 NVARCHAR ( 255 ) , @ PolType VARCHAR ( 255 ) , @ DocName NVARCHAR ( 255 ) , @ Submitted INT , @ Eff DATE , @ message_T NVARCHAR ( MAX ) ; Prepare @ message_T = '' ; SET @ PolNumber5 = '' ; DECLARE policyDocs_csr CURSOR FOR SELECT [ PolNumber ] , [ PolType ] , [ Effective Appointment ] , [ DocName ] , [ Submitted ] FROM [ dbo ] . [ InsuranceClaims ] ORDER Past [ PolNumber ] ; Open up policyDocs_csr ; FETCH NEXT FROM policyDocs_csr INTO @ PolNumber , @ PolType , @ Eff , @ DocName , @ Submitted ; WHILE @ @ FETCH_STATUS = 0 BEGIN IF @ PolNumber5 <> @ PolNumber SET @ message_T = @ message_T + CHAR ( 13 ) + @ PolNumber + ' | ' + @ PolType + ' | ' + CONVERT ( VARCHAR , @ eff ) + ' | ' + @ DocName + ' ( ' + CONVERT ( VARCHAR , isnull ( @ submitted , '' ) ) + ' ) | ' ; ELSE IF @ PolNumber5 = @ PolNumber SET @ message_T = @ message_T + @ DocName + ' ( ' + Catechumen ( VARCHAR , isnull ( @ submitted , '' ) ) + ' ) | ' ; Fix @ PolNumber5 = @ PolNumber ; FETCH Next FROM policyDocs_csr INTO @ PolNumber , @ PolType , @ Eff , @ DocName , @ Submitted ; End ; IF @ @ FETCH_STATUS <> 0 Print @ message_T ; Shut policyDocs_csr ; DEALLOCATE policyDocs_csr ; |
Execution of Script two lead to the result set displayed in Figure six yet, the Cursor option uses far more lines of code than its T-SQL Pivot counterpart.
Figure 6
Similar to the Pivot function, the T-SQL Cursor has the dynamic capability to return more rows as additional policies (i.due east. Pol003) are added into the dataset, equally shown in Figure 7:
Figure 7
However, unlike the Pivot function, the T-SQL Cursor is able to expand to include newly added fields (i.e. [Doc F]) without having to make changes to the original script.
Figure ix
Performance Breakdown
The major limitation of transposing rows into columns using T-SQL Cursor is a limitation that is linked to cursors in general – they rely on temporary objects, swallow retention resources and processes row ane at a time which could all result into significant performance costs. Thus, unlike in the Pin office wherein the majority of the cost was spent sorting the dataset, the majority of price in the Cursor choice is split between the Sort operation (at 46.2%) as well equally the temporary TempDB object (at 40.v%).
Effigy 10
Similar to the operational tree of the Pivot function, the operator with the higher percentages in the execution plan of the Cursor part are probable to consume more I/O resources than other operators. In this case, both the Sort and temporary TempDB objects recorded the virtually I/O usage cost at 0.01 milliseconds each.
Figure 11
Option #3: XML
The XML pick to transposing rows into columns is basically an optimal version of the Pivot in that information technology addresses the dynamic column limitation. The XML version of the script addresses this limitation by using a combination of XML Path, dynamic T-SQL and some built-in T-SQL functions such as STUFF and QUOTENAME. The version of the script that uses XML function to transpose rows into columns is shown in Script 3.
| DECLARE @ cols NVARCHAR ( MAX ) , @ query NVARCHAR ( MAX ) ; Ready @ cols = STUFF ( ( SELECT Distinct ',' + QUOTENAME ( c . [ DocName ] ) FROM [ dbo ] . [ InsuranceClaims ] c FOR XML PATH ( '' ) , Blazon ) . value ( '.' , 'nvarchar(max)' ) , 1 , ane , '' ) ; SET @ query = 'SELECT [PolNumber], ' + @ cols + 'from (SELECT [PolNumber], [PolType], [submitted] As [amount], [DocName] AS [category] FROM [dbo].[InsuranceClaims] )x pivot (max(amount) for category in (' + @ cols + ')) p' ; EXECUTE ( @ query ) ; |
The output of Script iii execution is shown in Figure 12.
Effigy 12
Similar to T-SQL Pivot and Cursor options, newly added policies (i.e. Pol003) are retrievable in the XML option without having to update the original script. Furthermore, the XML selection is also able to cater for dynamic field names (i.east. [Doctor F]) as shown in Effigy 13.
Figure 13
Performance Breakdown
The execution plan of Script 3 is almost similar to that of the Pivot function script in that bulk of the cost is taken upwardly by the Sort operator with the Table browse beingness the second most plush operation.
Figure fourteen
In terms of I/O price, the Sort operation used the longest time at 0.01 milliseconds.
Figure fifteen
Pick #4: Dynamic SQL
Another culling to the optimal XML option is to transpose rows into columns using purely dynamic SQL – without XML functions. This option utilises the aforementioned congenital-in T-SQL functions that are used in the XML choice version of the script as shown in Script 4.
| DECLARE @ columns NVARCHAR ( MAX ) , @ sql NVARCHAR ( MAX ) ; SET @ columns = Northward '' ; SELECT @ columns += North ', p.' + QUOTENAME ( [ Proper noun ] ) FROM ( SELECT [ DocName ] Equally [ Proper noun ] FROM [ dbo ] . [ InsuranceClaims ] AS p GROUP By [ DocName ] ) Every bit 10 ; SET @ sql = N ' SELECT [PolNumber], ' + STUFF ( @ columns , 1 , 2 , '' ) + ' FROM ( SELECT [PolNumber], [Submitted] AS [Quantity], [DocName] as [Proper name] FROM [dbo].[InsuranceClaims]) AS j Pin (SUM(Quantity) FOR [Name] in (' + STUFF ( REPLACE ( @ columns , ', p.[' , ',[' ) , ane , 1 , '' ) + ')) AS p;' ; EXEC sp_executesql |
Script 4: Transpose information using Dynamic SQL office
Again, similar all the other options, the script using Dynamic SQL returns data in a correctly transposed layout. Like to T-SQL Cursor and XML options, Dynamic SQL is able to cater for newly added rows and columns without whatever prior updates to the script.
Figure xvi
Performance Breakup
Except for using XML functions, the Dynamic SQL choice is very like to the XML option. It is not surprising then that its execution program and operations tree will look almost similar to that of the XML option.
Figure 17
Figure 18
Decision
In this commodity, nosotros've had a look at available T-SQL options for transposing rows into columns. The Pivot option was shown to be the simplest option however its inability to cater for dynamic columns made information technology the least optimal option. The T-SQL Cursor option addressed some of the limitations of the Pivot option though at a pregnant cost of resources and SQL Server performance. Finally, the XML and the Dynamic SQL options proved to be the best optimal options in terms of transposing rows into columns with favorable performance results and constructive treatment dynamic rows and columns.
Downloads
- Sample Insurance Claims information
References
- Worktables
- Statistics
- Using Pin and UNPIVOT
- Writer
- Recent Posts
Sifiso is a Johannesburg based certified professional within a wide range of Microsoft Technology Competencies such SQL Server and Visual Studio Application Lifecycle Management.
He is the member of the Johannesburg SQL User Group and also concur a Chief's Degree in MCom It Direction from the University of Johannesburg.
He currently works for Sambe Consulting as a Main Consultant.
View all posts by Sifiso W. Ndlovu
pierceparawascrack.blogspot.com
Source: https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/
0 Response to "Sql Server Read Nvarchar(Max) Cursor and Delete Lock"
Post a Comment