Something I personally haven’t seen a lot of out there in the SQL Server world, is use of GROUPING SETS - an operator that can be applied in a GROUP BY clause. So what does it do? How would you use it? Take the AdventureWorks sample database as an example playground. Suppose you want to query the sales data to find the following: total sales for each product total sales for each product category total sales There’s a number of ways you could do this. [Read More]

Passing a TABLE variable into dynamic SQL

A question popped up on StackOverflow today, asking how to pass a TABLE variable into a dynamic SQL statement in SQL Server 2008. I’ve previously blogged about table-valued parameters, comparing the approach of passing in a TABLE of values to a stored procedure to the techniques you’d have had to use in earlier versions of SQL Server (e.g. CSV or XML), but this specific question is worth a quick follow-up. [Read More]

Validating an SQL query programmatically, with gotcha

If you want to check the validity of a TSQL statement programmatically, you can make use of the SET NOEXEC statement. As this MSDN reference states, SET NOEXEC ON will compile the query but won’t actually execute it. This is ideal (well, nearly….) if perhaps you have a dynamically generated statement that you want to check is valid before executing it. Example time SETNOEXECONGOSELECTTOP*FROMsys.tablesGOSETNOEXECOFFGOThis results in the following error, as I’ve failed to specify the number of rows to return for the TOP: [Read More]

SQL Server XML datatype with CDATA

So today I learnt something new - it turns out the XML datatype in SQL Server does not preserve CDATA sections.

SET @XML = '<Test><NodeA><![CDATA[Testing cdata section <woop!>]]></NodeA></Test>'  
<Test><NodeA>Testing cdata section &lt;woop!&gt;</NodeA></Test>  

After a quick dig around, I found this MS Connect case. I personally would like to see it accept whatever you pass in without silently altering it, as long as it’s well-formed XML of course.

Optimising wildcard prefixed LIKE conditions

Suppose you want to write a query to find all products in your database that have a name beginning with “Long-Sleeve”. You’d more than likely use something like below (examples based on AdventureWorks LT sample database): SELECTnameFROMSalesLT.ProductWHEREnameLIKE'Long-Sleeve%'This produces a good execution plan, performing an index seek on the nonclustered index that exists on the name column. If you look at the seek operation, you’ll see the query optimiser has done a good job of ensuring an index can be used by looking at the seek predicate: [Read More]

SQL CAST to VARCHAR without size

What’s the result of this, in SQL Server? SELECTCAST(12345ASVARCHAR)As you expect, it’s “12345”. How about this? SELECTCAST(1234567890123456789012345678901ASVARCHAR)You may be expecting it to return “1234567890123456789012345678901” but it will in fact throw an error: Msg8115,Level16,State5,Line1Arithmeticoverflowerrorconvertingnumerictodatatypevarchar.It’s all because a length was not given for the VARCHAR to convert the value to. By default, as documented in MSDN, if no length is specified it will default to 30 when using CAST or CONVERT. This results in the error. [Read More]

Queue table processing in SQL Server

Implementing SQL Server queue table processing logic is something I keep meaning to blog about and finally I’ve got round to it thanks to my memory being jogged by StackOverflow questions I’ve recently participated in, including this one. The scenario is you queue up records in a database table, each representing a piece of work needing to be done. You then want to have processes that periodically poll this table to pick up the next item of work from the queue and process them. [Read More]

Changing primary key index structure

Changing the structure of a primary key constraint index from nonclustered to clustered (or from clustered to nonclustered) is not necessarily as straight forward as it first seems. The process of changing it over, involves the constraint being dropped and then recreated. This could potentially cause a problem if you’re making the change on a table whilst there could be activity against it. Example TableX was originally created as below: [Read More]

Optimising date filtered SQL queries

How you structure your SQL queries is very important and choosing the wrong approach can have big effects on the performance of the query. One of the key things that should flag up the potential for needing optimisation, is if you are using a function/calculation within a WHERE clause. These can lead to inefficient execution plans, preventing sub-optimal index use. A classic example to demonstrate this is when querying the data for a specific month. [Read More]

SQL Server 2008 - Table Valued Parameters vs XML vs CSV

The scenario is, you want to create a stored procedure that returns the records relating to a finite set of keys/IDs that you pass in to it. How do you do it? Well you could use dynamic SQL, but for the purpose of this article I’m going to ignore that option. Test scenario -- Create basic customer table CREATETABLE[dbo].[Customer](IdINTEGERIDENTITY(1,1)PRIMARYKEY,NameNVARCHAR(50)NOTNULL);GO-- Populate Customer table with 100,000 sample records DECLARE@CounterINTEGERSET@Counter=1WHILE(@Counter<=100000)BEGININSERTCustomer(Name)VALUES('Test Customer #'+CAST(@CounterASVARCHAR(10)))SET@Counter=@Counter+1ENDOption 1 - CSV list of keys In SQL Server 2000, you would most likely have ended up passing in a CSV list of keys, splitting that out into a table, and then joining that on to your real data table to match the records to return. [Read More]