GROUPING SETS in SQL Server
Posted on January 10, 2011
(Last modified on January 2, 2022)
| 4 minutes
| 759 words
| Adrian Hills
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
Posted on August 10, 2010
(Last modified on January 2, 2022)
| 1 minutes
| 140 words
| Adrian Hills
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
Posted on July 1, 2010
(Last modified on January 2, 2022)
| 2 minutes
| 343 words
| Adrian Hills
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 SET NOEXEC ON GO SELECT TOP * FROM sys.
[Read More]SQL Server XML datatype with CDATA
Posted on June 14, 2010
(Last modified on January 2, 2022)
| 1 minutes
| 75 words
| Adrian Hills
So today I learnt something new - it turns out the XML datatype in SQL Server does not preserve CDATA sections.
e.g.
DECLARE @XML XML SET @XML = '<Test><NodeA><![CDATA[Testing cdata section <woop!>]]></NodeA></Test>' SELECT @XML Results ------------------------------------------------------------------ <Test><NodeA>Testing cdata section <woop!></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.
[Read More]Optimising wildcard prefixed LIKE conditions
Posted on May 26, 2010
(Last modified on January 2, 2022)
| 3 minutes
| 562 words
| Adrian Hills
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):
SELECT name FROM SalesLT.Product WHERE name LIKE '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
Posted on April 6, 2010
(Last modified on January 2, 2022)
| 2 minutes
| 240 words
| Adrian Hills
What’s the result of this, in SQL Server?
SELECT CAST(12345 AS VARCHAR) As you expect, it’s “12345”. How about this?
SELECT CAST(1234567890123456789012345678901 AS VARCHAR) You may be expecting it to return “1234567890123456789012345678901” but it will in fact throw an error:
Msg 8115, Level 16, State 5, Line 1 Arithmetic overflow error converting numeric to data type varchar. It’s all because a length was not given for the VARCHAR to convert the value to.
[Read More]Queue table processing in SQL Server
Posted on March 1, 2010
(Last modified on January 2, 2022)
| 2 minutes
| 342 words
| Adrian Hills
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
Posted on March 1, 2010
(Last modified on January 2, 2022)
| 2 minutes
| 276 words
| Adrian Hills
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
Posted on February 21, 2010
(Last modified on January 2, 2022)
| 3 minutes
| 435 words
| Adrian Hills
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
Posted on February 11, 2010
(Last modified on January 2, 2022)
| 3 minutes
| 581 words
| Adrian Hills
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 CREATE TABLE [dbo].[Customer] ( Id INTEGER IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(50) NOT NULL ); GO -- Populate Customer table with 100,000 sample records DECLARE @Counter INTEGER SET @Counter = 1 WHILE (@Counter <= 100000) BEGIN INSERT Customer (Name) VALUES ('Test Customer #' + CAST(@Counter AS VARCHAR(10))) SET @Counter = @Counter + 1 END Option 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]