Rob Farley : sql, t-sql tuesday

Filegroups and Non-Clustered Indexes

Rob Farley — Tue, 12 Mar 2013 00:20:11 GMT

Let’s start with some basics and then jump in a bit deeper, for this post to go with the 40th T-SQL Tuesday, hosted this month by Jen McCown.

SQL Server holds data, and that data is stored physically in files.

Of course, in the database world we think of the data as living in tables*, so naturally there must be some sort of mapping between the data in a table and the data in a file. This is where filegroups come in.

When SQL objects are created within a database, there is an option to be able to tell the system where to actually store it. But this isn’t a file, it’s a filegroup. (If it were a file and the disk volume that the file was on filled up, we’d have big problems. Instead, we put it in a filegroup, and can add extra files (on different volumes if so desired) to that filegroup.) Objects are stored within filegroups. Filegroups are groups of files, although many database systems do end up with only a single file per filegroup.

Filegroups end up providing a large amount of flexibility for the storage of data. Rarely accessed data can be put in filegroups that have files on cheaper (but probably slower) disk, while data that is heavily written can be put on disk that’s more appropriate for that, and so on. I’m sure you get the picture, and this is nothing overly thought-worthy.

You may even have already considered the idea around partitioning data across filegroups, moving data of a certain age (but potentially even from the same table) onto a different filegroup, so that queries that use different parts of tables can benefit from having some data on faster disk. Lots of potential. :)

Where it becomes interesting though, is when you start to consider the concept of non-clustered indexes.

You see, while a clustered index (or heap) provides the structure to store the data across all the columns of a table (although I’m simplifying it a little in regard to LOB data), we use copies of some of that data (in non-clustered indexes) to optimise access to the data.

So where are these copies stored? Many database people don’t even think about this kind of thing, in the same way that they often don’t think about including indexes in their database design. I’m going to figure you’re not in that boat though, because I probably know you, and you’re probably not one of those people.

Most people like to have a default filegroup that is NOT the primary filegroup. It means that when they create new objects, those new objects get put in the default filegroup, not the primary one. But it’s not actually that simple.

Let’s start by creating a database.

CREATE DATABASE fg_testing;
GO
USE fg_testing;
GO
SELECT *
FROM sys.filegroups;
--Only one filegroup at the moment

Notice the column data_space_id. This is the column which identifies each filegroup. We’ll use it later.

Let’s create a new filegroup and set it to be the default.

ALTER DATABASE fg_testing ADD FILEGROUP FG2;
GO
SELECT *
FROM sys.filegroups;
GO

Cool – data_space_id 2 is created.

ALTER DATABASE fg_testing MODIFY FILEGROUP FG2 DEFAULT;

I think this is a shame – but I guess it’s fair enough. We have to have a file in the filegroup before we can make it the default. That’s easy enough though, and probably what we’d be wanting to do before too long anyway.

ALTER DATABASE fg_testing ADD FILE ( NAME = N'file2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\fg_testing_file2.ndf') TO FILEGROUP FG2;
GO
ALTER DATABASE fg_testing MODIFY FILEGROUP FG2 DEFAULT;

(I’m doing this on my laptop, which only has one physical drive – on a server it’d go somewhere more sensible of course)

Now let’s add another filegroup. This is going to be for some data, but I don’t want it to be my default.

ALTER DATABASE fg_testing ADD FILEGROUP FG3;
GO
ALTER DATABASE fg_testing ADD FILE ( NAME = N'file3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\fg_testing_file3.ndf') TO FILEGROUP FG3;

Now, I want to create an object on FG3, which I’m going to pretend is my slower disk.

Once created, I’m looking in sys.objects to confirm which filegroup the table is in, but there’s no information there. Remember that a table is only metadata, and the things that matter are the indexes/heaps that are on it. Checking in sys.indexes shows me that indeed, it’s in data_space_id 3.

CREATE TABLE dbo.OrderDates (OrderDate date PRIMARY KEY, NumOrders int NOT NULL DEFAULT 0) ON FG3;
GO
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID('dbo.OrderDates');
GO
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.OrderDates');
GO

For completeness’ sake, I’m going to put some data in there, using a query that I blogged about yesterday.

INSERT dbo.OrderDates (OrderDate, NumOrders)
SELECT TOP (DATEDIFF(DAY,'20010101','20130312'))
DATEADD(day,ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1,'20010101') AS OrderDate,
ABS(CHECKSUM(NEWID())) % 100 as NumOrders
FROM master.dbo.spt_values t1 CROSS JOIN master.dbo.spt_values t2
ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT 1))
GO

But the whole point of this was to see what happens with the non-clustered index, which I want to be on recent data only (ie, filtered), and I want it to be in the DEFAULT filegroup.

As I want it in the default group, I won’t specify a filegroup for the index.

CREATE INDEX ixRecentData ON dbo.OrderDates (OrderDate)
INCLUDE (NumOrders)
WHERE OrderDate >= '20130101';
GO
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.OrderDates');

But look! It’s put the non-clustered index in the same filegroup as the clustered index. This isn’t what I wanted. In fact, it’s almost never what I’d want, because even if the disk performance is identical, it can be nice to have transactions which update both the clustered and non-clustered indexes using different disks.

Instead, we have to specify it explicitly to tell it to use the filegroup we want.

DROP INDEX ixRecentData ON dbo.OrderDates
GO
CREATE INDEX ixRecentData ON dbo.OrderDates (OrderDate)
INCLUDE (NumOrders)
WHERE OrderDate >= '20130101'
ON FG2;
GO
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.OrderDates');

It’s a shame to have to be so explicit with these things, but when you create non-clustered indexes, you really should think about which filegroup they’re going onto, because they may well not go onto the default one as you might expect.

@rob_farley

Behind the scenes of PowerShell and SQL

Rob Farley — Tue, 12 Feb 2013 00:11:46 GMT

Every year, PowerShell increases its stranglehold on the Windows Server system and the applications that run upon it – with good reason too. Its consistent mechanisms for interaction between its scripting interface and the underlying systems make it easy for people to feel comfortable, and there is a discoverability that has been lacking in many other scripting environments.

Of course, SQL Server hasn’t been overlooked at all, and it’s coming up to five years since the SnapIns were made available (even longer since people started to dabble with SQL using PowerShell).

But what’s going on behind the scenes? Does PowerShell present a threat to those amongst us who will always prefer T-SQL? Does PowerShell give us new options that are not available any other way? Well, let’s have a bit of a look, especially since this month’s T-SQL Tuesday (hosted by Wayne Sheffield who tweets as @DBAWayne) is on the topic of PowerShell.

So we know PowerShell is useful. However we spin it up, we can quickly jump into writing commands, whether it be interacting with WMI, hooking into some .Net assembly we’ve loaded up, or simply browsing the file system. I’ve developed a tendency to use it to start whichever SQL instances I’m needing for the day – by default I have all of them turned off, since I don’t know which one I’ll be wanting most.

If we’re going to be interacting with SQL, then it’s easiest to either load up the SQLPS environment directly (there’s a PowerShell shortcut within Management Studio), or else (as I do), start a PowerShell window with the Snapin loaded. I prefer this later option, as the SQLPS environment is a slightly cut-back version of PowerShell. But either way – the stuff I’ll continue on with is essentially the same whichever environment you use.

If you’ve talked about SQL with me long enough, you’ll have come across the fact that I often use SQL Profiler when I’m curious about where to find information. My former colleague Jim McLeod (@Jim_McLeod) blogged about this a few months ago, with an example that I remember looking through with him four or five years ago. It’s a great technique that works on all kinds of things, even across different versions of SQL Server. It also adds as a terrific reminder that Management Studio is not a special application, it simply knows how to ask for the pieces of information that it shows.

But PowerShell (or SMO, for that matter), that’s in the .Net world. Surely that would be able to bypass the clunky T-SQL stuff that Management Studio does... I mean, Management Studio has to be flexible enough to work across remote servers, talking through firewalls that only allow T-SQL interfaces. Surely PowerShell has access to a deeper magic.

Well, no. PowerShell still lets you talk to remote servers, and ends up using the same methods.

Let’s prove it.

Spin up Profiler, and start a trace against your favourite instance. I like to watch for SQL:BatchCompleted, SP:StmtCompleted and RPC:Completed events when doing this kind of thing. I’m using an instance that isn’t doing anything else, but you could apply a ColumnFilter to filter the events to things with an ApplicationName starting with SQLPS if you prefer.

With that running, I jump into PowerShell and do something like:

PS SQLSERVER:\sql\localhost\sql2008r2> dir Databases | ft name

This lists the names of the databases on my SQL2008R2 instances. You don’t need to see the results, you can imagine them for yourself.

If PowerShell were using some secret interface, it’s unlikely we’d see something in Profiler. But it’s not, and we see a bunch of stuff.

We see a bunch of Remote Procedure Calls, each with a Stored Procedure Statement Completed event showing the same information. And look – we see queries against master.sys.databases, asking for the name of each of the databases, passing in the name as a parameter. Brilliant! Notice just a bit earlier though, there’s a SQL:BatchCompleted call. This means that a query has been passed in directly. It’s this:

SELECT
CAST(
serverproperty(N'Servername')
AS sysname) AS [Server_Name],
dtb.name AS [Name]
FROM
master.sys.databases AS dtb
WHERE
(CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit)=0)
ORDER BY
[Name] ASC

So it grabs the list of database names first, and then makes extra calls to be able to fetch the list of names again, one by one.

The reason why it’s grabbing the list of names one by one isn’t because it’s stupid and is asking to be ridiculed. It’s because we've asked to see that property, and I guess the PowerShell people figured that no matter what property you ask for, it’ll go and fetch it to show you.

When I asked for the CompatibilityLevel property instead, I got some different rows thrown in. Interestingly though, it still asked for the name each time.

Also interestingly, when I asked for the CompatibilityLevel a subsequent time, the calls for “SELECT dtb.compatibility_level…” weren’t in there. They’d been cached by the PowerShell environment – important to note if you ever come across PowerShell giving you old values.

So what about asking something more interesting? Let’s try asking about the IndexSpaceUsage in AdventureWorks.

PS SQLSERVER:\sql\localhost\sql2008r2> gi Databases\AdventureWorks | ft IndexSpaceUsage

The result tells me it’s 62576. Yeah, but today I’m not interested in that, just what happened in the trace.

Four entries. An SP:StmtCompleted with matching RPC:Completed, and two SQL:BatchCompleted.

The SP:StmtCompleted and RPC:Completed were this statement, passing in the parameter value ‘AdventureWorks’. Clearly incredibly informative.

SELECT
CAST(0 AS float) AS [IndexSpaceUsage],
dtb.name AS [DatabaseName]
FROM
master.sys.databases AS dtb
WHERE
(dtb.name=@_msparam_0)

This is getting the value zero. Wow. Brilliant stuff.

The last entry – the second of the two SQL:BatchCompleted events is:

select convert(float,low/1024.) from master.dbo.spt_values where number = 1 and type = 'E'

If you run this in Management Studio, you’ll discover it gives the value 8. Ok.

The other entry is more interesting.

use [AdventureWorks]
SELECT
SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS [DataSpaceUsage],
SUM(a.used_pages) AS [IndexSpaceTotal]
FROM
sys.allocation_units AS a INNER JOIN sys.partitions AS p ON (a.type = 2 AND p.partition_id = a.container_id) OR (a.type IN (1,3) AND p.hobt_id = a.container_id)

This is more like it! We run this in Management Studio, and we see two values. DataSpaceUsage is 13682, IndexSpaceTotal is 21504. Neither are our value 62576. But we do have clues in the column names, and in that value 8 that came back too. We can easily deduce that it’s actually (IndexSpaceTotal-DataSpaceUsage)*8, and we have ourselves a nice little method for working out the IndexSpaceUsage ourselves now if we need it.

Or we can just ask PowerShell next time as well.

Incidentally – if you’re considering doing the MCM Lab exam some time, then you might find that a familiarity with PowerShell comes in really handy. I’m not saying there are PowerShell questions on the exam at all – I’m just suggesting that you may find that PowerShell becomes a really useful way of getting at some of the information that you’re looking for. If you’re stumbling around the list of DMVs trying to remember which one it is that stores some particular thing, remember that you might be able to get the data out more easily if you use PowerShell instead.

So can we discover secret things about SQL from PowerShell? Are there things we can do in PowerShell that are impossible through other mechanisms? Hooks that let us break the rules even?

Recently, Kendal van Dyke asked a question about this kind of thing on Twitter. He was wondering if you could have a default constraint on a column in a view. The reason for his wondering was that he saw a property on a view column in PowerShell that made him wonder. The answer is no though, and there’s a simple reason.

PowerShell is a programmatic interface. It involves classes and property and methods. It does things row by row, which is why much of what you see in that trace feels amazingly pedantic – asking about things which shouldn’t have to be that complicated. The implication of this though, is that PowerShell reuses the concept of a column, regardless of whether this is a column in a table, a view, or anywhere else it decides to need a column. The fact that columns in tables have some extra properties isn’t enough to make this class re-use pointless. If we try to set a Default constraint for a column in a view though, we get an error, just like if we tried to do it any other way.

The PowerShell I used was:

$db = Get-Item SQLSERVER:\sql\localhost\sql2008r2\Databases\AdventureWorks
$def = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Default –ArgumentList $db, "ViewDefault"
$def.TextHeader = "CREATE DEFAULT ViewDefault AS"
$def.TextBody = "'ABC'"
$def.Create()
$def.BindToColumn("vStateProvinceCountryRegion","StateProvinceCode","Person")

The code that ended up getting called was to the stored procedure sp_bindefault (despite it being deprecated). Naturally, trying to execute this against a view column gives an error regardless of what wrappers you have put around it – PowerShell or not.

So PowerShell is very useful, and it provides a handy way of getting to a lot of things that could be otherwise hard. But looking below the surface, it isn’t able to circumvent the basic restrictions of SQL Server, because it still ends up doing its work using T-SQL.

@rob_farley

Joins in single-table queries

Rob Farley — Tue, 11 Dec 2012 00:05:17 GMT

Tables are only metadata. They don’t store data.

I’ve written something about this before, but I want to take a viewpoint of this idea around the topic of joins, especially since it’s the topic for T-SQL Tuesday this month. Hosted this time by Sebastian Meine (@sqlity), who has a whole series on joins this month. Good for him – it’s a great topic.

In that last post I discussed the fact that we write queries against tables, but that the engine turns it into a plan against indexes. My point wasn’t simply that a table is actually just a Clustered Index (or heap, which I consider just a special type of index), but that data access always happens against indexes – never tables – and we should be thinking about the indexes (specifically the non-clustered ones) when we write our queries.

I described the scenario of looking up phone numbers, and how it never really occurs to us that there is a master list of phone numbers, because we think in terms of the useful non-clustered indexes that the phone companies provide us, but anyway – that’s not the point of this post.

So a table is metadata. It stores information about the names of columns and their data types. Nullability, default values, constraints, triggers – these are all things that define the table, but the data isn’t stored in the table. The data that a table describes is stored in a heap or clustered index, but it goes further than this.

All the useful data is going to live in non-clustered indexes. Remember this. It’s important. Stop thinking about tables, and start thinking about indexes.

So let’s think about tables as indexes. This applies even in a world created by someone else, who doesn’t have the best indexes in mind for you.

I’m sure you don’t need me to explain Covering Index bit – the fact that if you don’t have sufficient columns “included” in your index, your query plan will either have to do a Lookup, or else it’ll give up using your index and use one that does have everything it needs (even if that means scanning it). If you haven’t seen that before, drop me a line and I’ll run through it with you. Or go and read a post I did a long while ago about the maths involved in that decision.

So – what I’m going to tell you is that a Lookup is a join.

When I run SELECT CustomerID FROM Sales.SalesOrderHeader WHERE SalesPersonID = 285; against the AdventureWorks2012 get the following plan:

I’m sure you can see the join. Don’t look in the query, it’s not there. But you should be able to see the join in the plan. It’s an Inner Join, implemented by a Nested Loop. It’s pulling data in from the Index Seek, and joining that to the results of a Key Lookup.

It clearly is – the QO wouldn’t call it that if it wasn’t really one. It behaves exactly like any other Nested Loop (Inner Join) operator, pulling rows from one side and putting a request in from the other. You wouldn’t have a problem accepting it as a join if the query were slightly different, such as

SELECT sod.OrderQty
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail as sod
on sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesPersonID = 285;

Amazingly similar, of course. This one is an explicit join, the first example was just as much a join, even thought you didn’t actually ask for one.

You need to consider this when you’re thinking about your queries.

But it gets more interesting.

Consider this query:

SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = 276
AND CustomerID = 29522;

It doesn’t look like there’s a join here either, but look at the plan.

That’s not some Lookup in action – that’s a proper Merge Join. The Query Optimizer has worked out that it can get the data it needs by looking in two separate indexes and then doing a Merge Join on the data that it gets. Both indexes used are ordered by the column that’s indexed (one on SalesPersonID, one on CustomerID), and then by the CIX key SalesOrderID. Just like when you seek in the phone book to Farley, the Farleys you have are ordered by FirstName, these seek operations return the data ordered by the next field. This order is SalesOrderID, even though you didn’t explicitly put that column in the index definition. The result is two datasets that are ordered by SalesOrderID, making them very mergeable.

Another example is the simple query

SELECT CustomerID
FROM Sales.SalesOrderHeader
WHERE SalesPersonID = 276;

This one prefers a Hash Match to a standard lookup even! This isn’t just ordinary index intersection, this is something else again! Just like before, we could imagine it better with two whole tables, but we shouldn’t try to distinguish between joining two tables and joining two indexes.

The Query Optimizer can see (using basic maths) that it’s worth doing these particular operations using these two less-than-ideal indexes (because of course, the best indexese would be on both columns – a composite such as (SalesPersonID, CustomerID – and it would have the SalesOrderID column as part of it as the CIX key still).

You need to think like this too.

Not in terms of excusing single-column indexes like the ones in AdventureWorks2012, but in terms of having a picture about how you’d like your queries to run. If you start to think about what data you need, where it’s coming from, and how it’s going to be used, then you will almost certainly write better queries.

…and yes, this would include when you’re dealing with regular joins across multiples, not just against joins within single table queries.

When someone deletes a shared data source in SSRS

Rob Farley — Tue, 09 Oct 2012 00:55:50 GMT

SQL Server Reporting Services plays nicely. You can have things in the catalogue that get shared. You can have Reports that have Links, Datasets that can be used across different reports, and Data Sources that can be used in a variety of ways too.

So if you find that someone has deleted a shared data source, you potentially have a bit of a horror story going on. And this works for this month’s T-SQL Tuesday theme, hosted by Nick Haslam, who wants to hear about horror stories. I don’t write about LobsterPot client horror stories, so I’m writing about a situation that a fellow MVP friend asked me about recently instead.

The best thing to do is to grab a recent backup of the ReportServer database, restore it somewhere, and figure out what’s changed. But of course, this isn’t always possible.

And it’s much nicer to help someone with this kind of thing, rather than to be trying to fix it yourself when you’ve just deleted the wrong data source. Unfortunately, it lets you delete data sources, without trying to scream that the data source is shared across over 400 reports in over 100 folders, as was the case for my friend’s colleague.

So, suddenly there’s a big problem – lots of reports are failing, and the time to turn it around is small. You probably know which data source has been deleted, but getting the shared data source back isn’t the hard part (that’s just a connection string really). The nasty bit is all the re-mapping, to get those 400 reports working again.

I know from exploring this kind of stuff in the past that the ReportServer database (using its default name) has a table called dbo.Catalog to represent the catalogue, and that Reports are stored here. However, the information about what data sources these deployed reports are configured to use is stored in a different table, dbo.DataSource. You could be forgiven for thinking that shared data sources would live in this table, but they don’t – they’re catalogue items just like the reports. Let’s have a look at the structure of these two tables (although if you’re reading this because you have a disaster, feel free to skim past).

Frustratingly, there doesn’t seem to be a Books Online page for this information, sorry about that. I’m also not going to look at all the columns, just ones that I find interesting enough to mention, and that are related to the problem at hand. These fields are consistent all the way through to SQL Server 2012 – there doesn’t seem to have been any changes here for quite a while.

dbo.Catalog

The Primary Key is ItemID. It’s a uniqueidentifier. I’m not going to comment any more on that. A minor nice point about using GUIDs in unfamiliar databases is that you can more easily figure out what’s what. But foreign keys are for that too…

Path, Name and ParentID tell you where in the folder structure the item lives. Path isn’t actually required – you could’ve done recursive queries to get there. But as that would be quite painful, I’m more than happy for the Path column to be there. Path contains the Name as well, incidentally.

Type tells you what kind of item it is. Some examples are 1 for a folder and 2 a report. 4 is linked reports, 5 is a data source, 6 is a report model. I forget the others for now (but feel free to put a comment giving the full list if you know it).

Content is an image field, remembering that image doesn’t necessarily store images – these days we’d rather use varbinary(max), but even in SQL Server 2012, this field is still image. It stores the actual item definition in binary form, whether it’s actually an image, a report, whatever.

LinkSourceID is used for Linked Reports, and has a self-referencing foreign key (allowing NULL, of course) back to ItemID.

Parameter is an ntext field containing XML for the parameters of the report. Not sure why this couldn’t be a separate table, but I guess that’s just the way it goes. This field gets changed when the default parameters get changed in Report Manager.

There is nothing in dbo.Catalog that describes the actual data sources that the report uses. The default data sources would be part of the Content field, as they are defined in the RDL, but when you deploy reports, you typically choose to NOT replace the data sources. Anyway, they’re not in this table. Maybe it was already considered a bit wide to throw in another ntext field, I’m not sure. They’re in dbo.DataSource instead.

dbo.DataSource

The Primary key is DSID. Yes it’s a uniqueidentifier...

ItemID is a foreign key reference back to dbo.Catalog

Fields such as ConnectionString, Prompt, UserName and Password do what they say on the tin, storing information about how to connect to the particular source in question.

Link is a uniqueidentifier, which refers back to dbo.Catalog. This is used when a data source within a report refers back to a shared data source, rather than embedding the connection information itself. You’d think this should be enforced by foreign key, but it’s not. It does allow NULLs though.

Flags this is an int, and I’ll come back to this.

When a Data Source gets deleted out of dbo.Catalog, you might assume that it would be disallowed if there are references to it from dbo.DataSource. Well, you’d be wrong. And not because of the lack of a foreign key either.

Deleting anything from the catalogue is done by calling a stored procedure called dbo.DeleteObject. You can look at the definition in there – it feels very much like the kind of Delete stored procedures that many people write, the kind of thing that means they don’t need to worry about allowing cascading deletes with foreign keys – because the stored procedure does the lot.

Except that it doesn’t quite do that.

If it deleted everything on a cascading delete, we’d’ve lost all the data sources as configured in dbo.DataSource, and that would be bad. This is fine if the ItemID from dbo.DataSource hooks in – if the report is being deleted. But if a shared data source is being deleted, you don’t want to lose the existence of the data source from the report.

So it sets it to NULL, and it marks it as invalid.

We see this code in that stored procedure.

UPDATE [DataSource]
   SET
      [Flags] = [Flags] & 0x7FFFFFFD, -- broken link
      [Link] = NULL
FROM
   [Catalog] AS C
   INNER JOIN [DataSource] AS DS ON C.[ItemID] = DS.[Link]
WHERE
   (C.Path = @Path OR C.Path LIKE @Prefix ESCAPE '*')

Unfortunately there’s no semi-colon on the end (but I’d rather they fix the ntext and image types first), and don’t get me started about using the table name in the UPDATE clause (it should use the alias DS). But there is a nice comment about what’s going on with the Flags field.

What I’d LIKE it to do would be to set the connection information to a report-embedded copy of the connection information that’s in the shared data source, the one that’s about to be deleted. I understand that this would cause someone to lose the benefit of having the data sources configured in a central point, but I’d say that’s probably still slightly better than LOSING THE INFORMATION COMPLETELY. Sorry, rant over. I should log a Connect item – I’ll put that on my todo list.

So it sets the Link field to NULL, and marks the Flags to tell you they’re broken. So this is your clue to fixing it.

A bitwise AND with 0x7FFFFFFD is basically stripping out the ‘2’ bit from a number. So numbers like 2, 3, 6, 7, 10, 11, etc, whose binary representation ends in either 11 or 10 get turned into 0, 1, 4, 5, 8, 9, etc. We can test for it using a WHERE clause that matches the SET clause we’ve just used. I’d also recommend checking for Link being NULL and also having no ConnectionString. And join back to dbo.Catalog to get the path (including the name) of broken reports are – in case you get a surprise from a different data source being broken in the past.

SELECT c.Path, ds.Name
FROM dbo.[DataSource] AS ds
JOIN dbo.[Catalog] AS c ON c.ItemID = ds.ItemID
WHERE ds.[Flags] = ds.[Flags] & 0x7FFFFFFD
AND ds.[Link] IS NULL
AND ds.[ConnectionString] IS NULL;

When I just ran this on my own machine, having deleted a data source to check my code, I noticed a Report Model in the list as well – so if you had thought it was just going to be reports that were broken, you’d be forgetting something.

So to fix those reports, get your new data source created in the catalogue, and then find its ItemID by querying Catalog, using Path and Name to find it.

And then use this value to fix them up. To fix the Flags field, just add 2. I prefer to use bitwise OR which should do the same. Use the OUTPUT clause to get a copy of the DSIDs of the ones you’re changing, just in case you need to revert something later after testing (doing it all in a transaction won’t help, because you’ll just lock out the table, stopping you from testing anything).

UPDATE ds SET [Flags] = [Flags] | 2, [Link] = '3AE31CBA-BDB4-4FD1-94F4-580B7FAB939D' /*Insert your own GUID*/
OUTPUT deleted.Name, deleted.DSID, deleted.ItemID, deleted.Flags
FROM dbo.[DataSource] AS ds
JOIN dbo.[Catalog] AS c ON c.ItemID = ds.ItemID
WHERE ds.[Flags] = ds.[Flags] & 0x7FFFFFFD
AND ds.[Link] IS NULL
AND ds.[ConnectionString] IS NULL;

But please be careful. Your mileage may vary. And there’s no reason why 400-odd broken reports needs to be quite the nightmare that it could be. Really, it should be less than five minutes.

@rob_farley

Tricks? In T-SQL?

Rob Farley — Tue, 14 Aug 2012 02:16:19 GMT

Four years ago, I was preparing to speak at TechEd Australia. I’d been asked to give a session on “T-SQL Tips and Tricks”, but I’d pushed back and we’d gone with “T-SQL Tips and Techniques” instead. I hadn’t wanted to show Tricks, because despite being a fan of ‘magicians’ (like Tommy Cooper) I feel like the trickery should disappear with the understanding of the technique used. This month, Mike Fal asks about Trick Shots, and I’m reminded of some of the things I do with T-SQL, and that session I gave nearly four years ago.

So I gave a talk, in which I covered 15 T-SQL Tips (probably more – I definitely threw a lot of stuff in there). They included things like the Incredible Shrinking Execution Plan, using the OUTPUT clause to return identity values on multiple rows, short-circuiting GROUP BY statements with unique indexes, and plenty more. There are a lot more things that I cover these days – you can’t exactly stay still and remain current – but still I like to maintain that there shouldn’t be trickery with T-SQL.

The common thread going through many of the tips, along with every class I teach about T-SQL, is the importance of the execution plan. That’s where you can see what’s actually going on, and hopefully it can explain some of the magic that you see. Of course, there’s more to it than that, but getting your head around the relationship between queries and plans can definitely help demystify situations.

Take recursive CTEs, for example. In the piece of code below (against old AdventureWorks, to which I added a covering index to avoid lookups), we see a sub-query used within a CTE (which is all about giving a name to the sub-query so it can be referenced later), and then in the second half of the UNION ALL statement, still within the sub-query, we see the CTE name used (where I’ve made it bold). Despite the fact that we haven’t even finished using it yet. This functionality has been around for a long time, but yet many people are not used to it, and see it as a trick.

WITH OrgChart AS
(
    SELECT 1 AS EmployeeLevel, *
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT o.EmployeeLevel + 1, e.*
    FROM OrgChart AS o
    JOIN HumanResources.Employee AS e
    ON e.ManagerID = o.EmployeeID
)
SELECT *
FROM OrgChart;

There isn’t a trick here, and it comes down the principle of pulling sub-query definitions (including CTEs and non-indexed views) into the outer query. You see, the OrgChart here isn’t a database object, it’s simply a nested sub-query.

You might imagine that it looks a bit like this, where I’ve replaced the OrgChart reference with a copy of the query itself.

...but I’m not a big fan of this kind of representation, because it’s a bit strange to see that “WHERE ManagerID IS NULL” bit in there repeatedly. Are we really going to be getting that row out over and over again?

I’ve seen people try to demonstrate this something like:

SELECT o2.EmployeeLevel + 1 AS EmployeeLevel, e.*
FROM
(
    SELECT o1.EmployeeLevel + 1 AS EmployeeLevel, e.*
    FROM
    (
        SELECT 1 AS EmployeeLevel, *
        FROM HumanResources.Employee
        WHERE ManagerID IS NULL
    ) AS o1
    JOIN HumanResources.Employee AS e
    ON e.ManagerID = o1.EmployeeID
) AS o2
JOIN HumanResources.Employee AS e
ON e.ManagerID = o2.EmployeeID
;

, but this isn’t right either, because this query is putting the data onto the end of rows, whereas we really do need a UNION ALL.

The easiest way of showing what’s going on is to look at the execution plan.

Look at the first operator called – it’s an Index Spool (over on the left). It gets its data from a Concatenation between data that comes from an Index Seek, and a join between a Table Spool and another Index Seek. This sounds all well and good, but that Table Spool is empty. There’s nothing on the spool at all.

At least, until the Concatenation operator returns that first row to the Index Spool. When this happens, the Table Spool can serve another row. The Nested Loop happily takes the row, and requests any matching rows from the Index Seek at the bottom-right of the plan, and the Concatenation operator happily passes these rows back to the Index Spool, at which point the Table Spool has more rows it can serve up again.

The Index Spool controls all this. At some point, the system has to realise that there’s no more data that’s going to be served up. The Table Spool doesn’t just sit waiting for rows to appear, nor does the spooling behaviour cause the Table Spool to suddenly get kicked off again. This is all handled because the Concatenation operator keeps getting prodded (by the Index Spool) that there’s more data that’s been pushed onto it. The Table Spool doesn’t know (or even care) if the rows it’s handed over are going to end up back on the spool – after all, it doesn’t know if those employees are also managers, it just serves up the data that appears on it, when requested.

The recursive CTE is not magic. It doesn’t do any kind of trickery. It’s just a loop that feeds its data back into itself. And of course, to understand this properly, you should make sure you know to read plans from the left, revealing the Index Spool which really runs this query.

Learn to read execution plans. There are a bunch of resources out there (such as other posts of mine, stuff by Grant Fritchey, and more), but above all, just start opening them up and seeing how your queries run. You’ll find that a lot of the ‘tricks’ you think are in T-SQL aren’t really tricks at all, it’s just about understanding how your queries are being executed.

And before you ask, I won’t be at TechEd Australia this year.

MERGE gives better OUTPUT options

Rob Farley — Tue, 12 Jun 2012 00:40:06 GMT

MERGE is very cool. There are a ton of useful things about it – mostly around the fact that you can implement a ton of change against a table all at once. This is great for data warehousing, handling changes made to relational databases by applications, all kinds of things.

One of the more subtle things about MERGE is the power of the OUTPUT clause. Useful for logging.

If you’re not familiar with the OUTPUT clause, you really should be – it basically makes your DML (INSERT/DELETE/UPDATE/MERGE) statement return data back to you. This is a great way of returning identity values from INSERT commands (so much better than SCOPE_IDENTITY() or the older (and worse) @@IDENTITY, because you can get lots of rows back). You can even use it to grab default values that are set using non-deterministic functions like NEWID() – things you couldn’t normally get back without running another query (or with a trigger, I guess, but that’s not pretty).

That inserted table I referenced – that’s part of the ‘behind-the-scenes’ work that goes on with all DML changes. When you insert data, this internal table called inserted gets populated with rows, and then used to inflict the appropriate inserts on the various structures that store data (HoBTs – the Heaps or B-Trees used to store data as tables and indexes). When deleting, the deleted table gets populated. Updates get a matching row in both tables (although this doesn’t mean that an update is a delete followed by an inserted, it’s just the way it’s handled with these tables). These tables can be referenced by the OUTPUT clause, which can show you the before and after for any DML statement. Useful stuff.

MERGE is slightly different though.

With MERGE, you get a mix of entries. Your MERGE statement might be doing some INSERTs, some UPDATEs and some DELETEs. One of the most common examples of MERGE is to perform an UPSERT command, where data is updated if it already exists, or inserted if it’s new. And in a single operation too. Here, you can see the usefulness of the deleted and inserted tables, which clearly reflect the type of operation (but then again, MERGE lets you use an extra column called $action to show this).

(Don’t worry about the fact that I turned on IDENTITY_INSERT, that’s just so that I could insert the values)

One of the things I love about MERGE is that it feels almost cursor-like – the UPDATE bit feels like “WHERE CURRENT OF …”, and the INSERT bit feels like a single-row insert. And it is – but into the inserted and deleted tables. The operations to maintain the HoBTs are still done using the whole set of changes, which is very cool.

And $action – very convenient.

But as cool as $action is, that’s not the point of my post. If it were, I hope you’d all be disappointed, as you can’t really go near the MERGE statement without learning about it.

The subtle thing that I love about MERGE with OUTPUT is that you can hook into more than just inserted and deleted.

Did you notice in my earlier query that my source table had a ‘src’ field, that wasn’t used in the insert? Normally, this would be somewhat pointless to include in my source query. But with MERGE, I can put that in the OUTPUT clause.

This is useful stuff, particularly when you’re needing to audit the changes. Suppose your query involved consolidating data from a number of sources, but you didn’t need to insert that into the actual table, just into a table for audit. This is now very doable, either using the INTO clause of OUTPUT, or surrounding the whole MERGE statement in brackets (parentheses if you’re American) and using a regular INSERT statement.

This is also doable if you’re using MERGE to just do INSERTs.

In case you hadn’t realised, you can use MERGE in place of an INSERT statement. It’s just like the UPSERT-style statement we’ve just seen, except that we want nothing to match. That’s easy to do, we just use ON 1=2.

This is obviously more convoluted than a straight INSERT. And it’s slightly more effort for the database engine too. But, if you want the extra audit capabilities, the ability to hook into the other source columns is definitely useful.

Oh, and before people ask if you can also hook into the target table’s columns... Yes, of course. That’s what deleted and inserted give you.

Analytic functions – they’re not aggregates

Rob Farley — Tue, 10 Apr 2012 01:53:49 GMT

SQL 2012 brings us a bunch of new analytic functions, together with enhancements to the OVER clause. People who have known me over the years will remember that I’m a big fan of the OVER clause and the types of things that it brings us when applied to aggregate functions, as well as the ranking functions that it enables.

The OVER clause was introduced in SQL Server 2005, and remained frustratingly unchanged until SQL Server 2012.

This post is going to look at a particular aspect of the analytic functions though (not the enhancements to the OVER clause). When I give presentations about the analytic functions around Australia as part of the tour of SQL Saturdays (starting in Brisbane this Thursday), and in Chicago next month, I’ll make sure it’s sufficiently well described. But for this post – I’m going to skip that and assume you get it.

The analytic functions introduced in SQL 2012 seem to come in pairs – FIRST_VALUE and LAST_VALUE, LAG and LEAD, CUME_DIST and PERCENT_RANK, PERCENTILE_CONT and PERCENTILE_DISC. Perhaps frustratingly, they take slightly different forms as well. The ones I want to look at now are FIRST_VALUE and LAST_VALUE, and PERCENTILE_CONT and PERCENTILE_DISC.

The reason I’m pulling this ones out is that they always produce the same result within their partitions (if you’re applying them to the whole partition).

Consider the following query:

SELECT
    YEAR(OrderDate),
    FIRST_VALUE(TotalDue)
        OVER (PARTITION BY YEAR(OrderDate)
              ORDER BY OrderDate, SalesOrderID
              RANGE BETWEEN UNBOUNDED PRECEDING
                        AND UNBOUNDED FOLLOWING),
    LAST_VALUE(TotalDue)
        OVER (PARTITION BY YEAR(OrderDate)
              ORDER BY OrderDate, SalesOrderID
              RANGE BETWEEN UNBOUNDED PRECEDING
                        AND UNBOUNDED FOLLOWING),
    PERCENTILE_CONT(0.95)
        WITHIN GROUP (ORDER BY TotalDue)
        OVER (PARTITION BY YEAR(OrderDate)),
    PERCENTILE_DISC(0.95)
        WITHIN GROUP (ORDER BY TotalDue)
        OVER (PARTITION BY YEAR(OrderDate))
FROM Sales.SalesOrderHeader
;

This is designed to get the TotalDue for the first order of the year, the last order of the year, and also the 95% percentile, using both the continuous and discrete methods (‘discrete’ means it picks the closest one from the values available – ‘continuous’ means it will happily use something between, similar to what you would do for a traditional median of four values). I’m sure you can imagine the results – a different value for each field, but within each year, all the rows the same.

Notice that I’m not grouping by the year. Nor am I filtering. This query gives us a result for every row in the SalesOrderHeader table – 31465 in this case (using the original AdventureWorks that dates back to the SQL 2005 days).

The RANGE BETWEEN bit in FIRST_VALUE and LAST_VALUE is needed to make sure that we’re considering all the rows available. If we don’t specify that, it assumes we only mean “RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”, which means that LAST_VALUE ends up being the row we’re looking at.

At this point you might think about other environments such as Access or Reporting Services, and remember aggregate functions like FIRST. We really should be able to do something like:

SELECT
    YEAR(OrderDate),
    FIRST_VALUE(TotalDue)
        OVER (PARTITION BY YEAR(OrderDate)
              ORDER BY OrderDate, SalesOrderID
              RANGE BETWEEN UNBOUNDED PRECEDING
                        AND UNBOUNDED FOLLOWING)
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
;

But you can’t. You get that age-old error:

Msg 8120, Level 16, State 1, Line 5
Column 'Sales.SalesOrderHeader.OrderDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 5
Column 'Sales.SalesOrderHeader.SalesOrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Hmm.

You see, FIRST_VALUE isn’t an aggregate function. None of these analytic functions are. There are too many things involved for SQL to realise that the values produced might be identical within the group.

Furthermore, you can’t even surround it in a MAX. Then you get a different error, telling you that you can’t use windowed functions in the context of an aggregate.

And so we end up grouping by doing a DISTINCT.

SELECT DISTINCT
    YEAR(OrderDate),
        FIRST_VALUE(TotalDue)
            OVER (PARTITION BY YEAR(OrderDate)
                  ORDER BY OrderDate, SalesOrderID
                  RANGE BETWEEN UNBOUNDED PRECEDING
                            AND UNBOUNDED FOLLOWING),
        LAST_VALUE(TotalDue)
            OVER (PARTITION BY YEAR(OrderDate)
                  ORDER BY OrderDate, SalesOrderID
                  RANGE BETWEEN UNBOUNDED PRECEDING
                            AND UNBOUNDED FOLLOWING),
    PERCENTILE_CONT(0.95)
        WITHIN GROUP (ORDER BY TotalDue)
        OVER (PARTITION BY YEAR(OrderDate)),
    PERCENTILE_DISC(0.95)
        WITHIN GROUP (ORDER BY TotalDue)
        OVER (PARTITION BY YEAR(OrderDate))
FROM Sales.SalesOrderHeader
;

I’m sorry. It’s just the way it goes. Hopefully it’ll change the future, but for now, it’s what you’ll have to do.

If we look in the execution plan, we see that it’s incredibly ugly, and actually works out the results of these analytic functions for all 31465 rows, finally performing the distinct operation to convert it into the four rows we get in the results.

You might be able to achieve a better plan using things like TOP, or the kind of calculation that I used in http://sqlblog.com/blogs/rob_farley/archive/2011/08/23/t-sql-thoughts-about-the-95th-percentile.aspx (which is how PERCENTILE_CONT works), but it’s definitely convenient to use these functions, and in time, I’m sure we’ll see good improvements in the way that they are implemented.

Oh, and this post should be good for fellow SQL Server MVP Nigel Sammy’s T-SQL Tuesday this month.

Be the surgeon

Rob Farley — Tue, 13 Mar 2012 00:13:00 GMT

It’s a phrase I use often, especially when teaching, and I wish I had realised the concept years earlier. (And of course, fits with this month’s T-SQL Tuesday topic, hosted by Argenis Fernandez)

When I’m sick enough to go to the doctor, I see a GP. I used to typically see the same guy, but he’s moved on now. However, when he has been able to roughly identify the area of the problem, I get referred to a specialist, sometimes a surgeon.

Being a surgeon requires a refined set of skills. It’s why they often don’t like to be called “Doctor”, and prefer the traditional “Mister” (the history is that the doctor used to make the diagnosis, and then hand the patient over to the person who didn’t have a doctorate, but rather was an expert cutter, typically from a background in butchering). But if you ask the surgeon about the pain you have in your leg sometimes, you’ll get told to ask your GP. It’s not that your surgeon isn’t interested – they just don’t know the answer.

IT is the same now.

That wasn’t something that I really understood when I got out of university. I knew there was a lot to know about IT – I’d just done an honours degree in it. But I also knew that I’d done well in just about all my subjects, and felt like I had a handle on everything. I got into developing, and still felt that having a good level of understanding about every aspect of IT was a good thing.

This got me through for the first six or seven years of my career.

But then I started to realise that I couldn’t compete.

I’d moved into management, and was spending my days running projects, rather than writing code. The kids were getting older. I’d had a bad back injury (ask anyone with chronic pain how it affects your ability to concentrate, retain information, etc). But most of all, IT was getting larger.

I knew kids without lives who knew more than I did. And I felt like I could easily identify people who were better than me in whatever area I could think of. Except writing queries (this was before I discovered technical communities, and people like Paul White and Dave Ballantyne). And so I figured I’d specialise.

I wish I’d done it years earlier.

Now, I can tell you plenty of people who are better than me at any area you can pick. But there are also more people who might consider listing me in some of their lists too. If I’d stayed the GP, I’d be stuck in management, and finding that there were better managers than me too.

If you’re reading this, SQL could well be your thing. But it might not be either. Your thing might not even be in IT. Find out, and then see if you can be a world-beater at it.

But it gets even better, because you can find other people to complement the things that you’re not so good at.

My company, LobsterPot Solutions, has six people in it at the moment. I’ve hand-picked those six people, along with the one who quit. The great thing about it is that I’ve been able to pick people who don’t necessarily specialise in the same way as me. I don’t write their T-SQL for them – generally they’re good enough at that themselves. But I’m on-hand if needed. Consider Roger Noble, for example. He’s doing stuff in HTML5 and jQuery that I could never dream of doing to create an amazing HTML5 version of PivotViewer. Or Ashley Sewell, a guy who does project management far better than I do. I could go on. My team is brilliant, and I love them to bits. We’re all surgeons, and when we work together, I like to think we’re pretty good!

@rob_farley

APPLY – not exactly set-based

Rob Farley — Tue, 10 Jan 2012 00:06:04 GMT

In my last post, I showed a technique for dealing with working columns when writing T-SQL. The idea was around using APPLY to be able to push values from the existing set through calculations (but preferably not scalar functions, of course), producing new columns which can be used further down the query, even in the WHERE and GROUP BY clauses. Useful stuff indeed.

But there is a limitation which I didn’t cover, and I feel that this is worth writing about for this month’s T-SQL Tuesday. The theme allows people to write about previous Tuesday topics – so I’m going to revisit topics 17 and 25 as I go a little further into APPLY.

These working columns can only be applied if they are calculations on a single row of data. The resulting set might be bigger or smaller based on how many rows are affected, but the input (at least logically) should be considered on a row-by-row basis.

Consider the following query:

SELECT p.Name, r.RevName
FROM
Production.ProductSubcategory AS s
CROSS APPLY
(SELECT REVERSE(s.Name) AS RevName) AS r
CROSS APPLY
(
    SELECT TOP (1) *
    FROM Production.Product AS p
    WHERE p.ProductSubcategoryID = s.ProductSubcategoryID
    ORDER BY p.ListPrice DESC
) AS p
ORDER BY s.ProductSubcategoryID;

You see two CROSS APPLYs here – one creating a simple working column in REVERSE(s.Name), the other even involving another table to do a lookup. This second CROSS APPLY doesn’t necessarily produce any rows – if there is no matching Product, the resultset won’t contain any rows for that ProductSubcategory. I’ve shown some of its results below:

But what about the following query, which gives the same 37 rows on the AdventureWorks database:

SELECT
(
    SELECT TOP (1) p.Name
    FROM Production.Product AS p
    WHERE p.ProductSubcategoryID = s.ProductSubcategoryID
    ORDER BY p.ListPrice DESC
),
REVERSE(s.Name) AS RevName
FROM Production.ProductSubcategory AS s
ORDER BY s.ProductSubcategoryID;

This is very similar, but you’ll notice that instead of developing the result set in the FROM clause, I’ve used the calculations directly in the SELECT clause to produce the same result.

Except that it’s not the same.

Here, my TOP sub-query can only produce a single value. You’ll notice I change my query to fetch only a single column now – any more would give an error. And I’m lucky I’ve selected TOP(1), not TOP(2), or TOP (1) WITH TIES – which would both be legal in my APPLY system. You’ll see that APPLY provides additional flexibility here.

So now consider the following query:

SELECT
    p.Name,
    p.ProductSubcategoryID,
    COUNT(*) OVER (PARTITION BY p.ProductSubcategoryID) AS SubcatCnt
FROM Production.Product AS p
ORDER BY p.ProductSubcategoryID, p.Name;

You will see that both look at the list of Products, and produce a count of the number of products which are in the same Subcategory. I’m sure you’re all very familiar with the OVER clause and the use of the windowing technique provided by the PARTITION BY clause.

If we run this first query and look at some of the results, you’ll see the marvellous windowing technique, which I’ve highlighted using ZoomIt. You’ll see that the SubcatCnt column has the value 3 for the Subcategory with 3 items, and 6 for the one with 6, and so on.

Excellent stuff. But you’ll probably also be aware that you can’t use windowing functions like this in the WHERE clause – they’re applied only in the SELECT clause.

Having seen my working columns trick though, you might think otherwise.

Check this out – it’s perfectly legal!

SELECT
p.Name,
p.ProductSubcategoryID,
sc.SubcatCnt
FROM Production.Product AS p
CROSS APPLY
(
SELECT COUNT(*) OVER (PARTITION BY p.ProductSubcategoryID) AS SubcatCnt
) AS sc
ORDER BY p.ProductSubcategoryID, p.Name;

Amazing stuff. I can now use SubcatCnt in the WHERE clause. Job done!

But I’m having you on. It’s a trick. It’s rubbish.

You see, APPLY logically works on a single row at a time, as I said earlier. Look at the same block of results for this second query.

Loads of 1s. Not exactly helpful. It’s accurate though – there is a count of one row in each partition of the set that is logically passed in each time.

APPLY works out the result for each row individually. It doesn’t do it for the whole set. So if you’re wanting working columns that need to be applied to multiple rows at once, then you need to think of another solution.

APPLY is good, but not that good. Know its limitations and you’ll be better off.

A T-SQL Tip: Working calculations

Rob Farley — Tue, 13 Dec 2011 00:46:09 GMT

T-SQL Tuesday again and this month is on T-SQL Tips (thanks Allen!). In some ways it’s a tough topic, because there are things I don’t really consider tips that other people do, and vice-versa. This one’s legitimate though: Using CROSS APPLY for working columns in calculations.

Let me give you an example.

Back in 2009, I wrote a blog post on Julian (YYDDD) dates. http://msmvps.com/blogs/robfarley/archive/2009/03/25/converting-to-and-from-julian-format-in-t-sql.aspx.

Someone asked me recently about using this for time periods, for example, someone’s age when they play a football match. For example, Cesc Fàbregas holds the record as the youngest Arsenal player, aged 16 years and 177 days. Given someone’s birthdate and date of interest, this seems like it should be quite easy to work out, but can get frustratingly tricky.

The idea is quite straight forward – you just count the number of days from their most recent birthday. So let’s have a think.

Counting the number of years between the startdate and the enddate is a nice place to start. I could count the number of days, but that might give me rounding error based on leap years. But counting the years simply compares the year component, such as 1987 and 2003, and the player might not have had his birthday this year yet. In that case, we’d want to go with the year before. Anyway – once we’ve figured out how old the player is, we can figure out when their last birthday was and count the days since then. Easy.

But so easy to make a mistake somewhere.

And this is where CROSS APPLY can come into its own, by allowing us to use working columns.

Let’s start with a few players. Some young ones, and Thierry Henry, just because. I’m using the VALUES method, but you could have a Players table just as easily.

select *
from
(
    values
     ('Cesc Fàbregas', cast('4 May 1987' as date), cast('28 October 2003' as date))
    ,('Theo Walcott', cast('16 March 1989' as date), cast('19 August 2006' as date))
    ,('Jack Wilshere', cast('1 January 1992' as date), cast('13 September 2008' as date))
    ,('Thierry Henry', cast('17 August 1977' as date), cast('7 August 1999' as date))
) p (Name, DoB, Debut)

First, I want to make it clear which column is my startdate and which is my enddate. It’s too easy to be thinking “startdate” and pick up “Debut” here, because this sounds very much like the same thing. There’s the startdate of their playing career, and the startdate of the calculation. To avoid confusion, I’m going to do some simple column-renaming. This gives me more reusable code, and APPLY even means I never have to worry about whether I already have these column names referring to something else, because they’re going to get their own table alias too.

select *
from
(
    values
     ('Cesc Fàbregas', cast('4 May 1987' as date), cast('28 October 2003' as date))
    ,('Theo Walcott', cast('16 March 1989' as date), cast('19 August 2006' as date))
    ,('Jack Wilshere', cast('1 January 1992' as date), cast('13 September 2008' as date))
    ,('Thierry Henry', cast('17 August 1977' as date), cast('7 August 1999' as date))
) p (Name, DoB, Debut)
CROSS APPLY
   (select p.DoB as startdate, p.Debut as enddate) as working

Might seem like a bit of a waste to you, but it means so much to me. Really.

Now I want to count how many years there are between my startdate and enddate, and work out when the startdate is this year. I could do this in one step, sure, but I want to be able to check my working really thoroughly.

select *
from
(
    values
     ('Cesc Fàbregas', cast('4 May 1987' as date), cast('28 October 2003' as date))
    ,('Theo Walcott', cast('16 March 1989' as date), cast('19 August 2006' as date))
    ,('Jack Wilshere', cast('1 January 1992' as date), cast('13 September 2008' as date))
    ,('Thierry Henry', cast('17 August 1977' as date), cast('7 August 1999' as date))
) p (Name, DoB, Debut)
CROSS APPLY
(select p.DoB as startdate, p.Debut as enddate) as working
CROSS APPLY
(select datediff(year,working.startdate,working.enddate) as YearsDifferent) yd
CROSS APPLY
(select dateadd(year,yd.YearsDifferent,working.startdate) as StartDateThisYear) sdty

Now I can easily test to see if I need to subtract a year or not.

select *
from
(
    values
     ('Cesc Fàbregas', cast('4 May 1987' as date), cast('28 October 2003' as date))
    ,('Theo Walcott', cast('16 March 1989' as date), cast('19 August 2006' as date))
    ,('Jack Wilshere', cast('1 January 1992' as date), cast('13 September 2008' as date))
    ,('Thierry Henry', cast('17 August 1977' as date), cast('7 August 1999' as date))
) p (Name, DoB, Debut)
CROSS APPLY
(select p.DoB as startdate, p.Debut as enddate) as working
CROSS APPLY
(select datediff(year,working.startdate,working.enddate) as YearsDifferent) yd
CROSS APPLY
(select dateadd(year,yd.YearsDifferent,working.startdate) as StartDateThisYear) sdty
CROSS APPLY
(select case when sdty.StartDateThisYear > working.enddate then -1 else 0 end as YearOffset) yo
CROSS APPLY
(select dateadd(year,yd.YearsDifferent + yo.YearOffset,working.startdate) as AdjustedYear) ay

I’m sure you can see where this is going. I now have their latest birthday before the date I’m looking for, and I can easily turn this into a Julian Date format.

select p.*, jd.JulianDiff
from
(
    values
     ('Cesc Fàbregas', cast('4 May 1987' as date), cast('28 October 2003' as date))
    ,('Theo Walcott', cast('16 March 1989' as date), cast('19 August 2006' as date))
    ,('Jack Wilshere', cast('1 January 1992' as date), cast('13 September 2008' as date))
    ,('Thierry Henry', cast('17 August 1977' as date), cast('7 August 1999' as date))
) p (Name, DoB, Debut)
CROSS APPLY
   (select p.DoB as startdate, p.Debut as enddate) as working
CROSS APPLY
   (select datediff(year,working.startdate,working.enddate) as YearsDifferent) yd
CROSS APPLY
   (select dateadd(year,yd.YearsDifferent,working.startdate) as StartDateThisYear) sdty
CROSS APPLY
   (select case when sdty.StartDateThisYear > working.enddate then -1 else 0 end as YearOffset) yo
CROSS APPLY
   (select dateadd(year,yd.YearsDifferent + yo.YearOffset,working.startdate) as AdjustedYear) ay
CROSS APPLY
   (select (yd.YearsDifferent + yo.YearOffset) * 1000 as YearComponent) yc
CROSS APPLY
   (select datediff(day,ay.AdjustedYear,working.enddate) as DayComponent) dc
CROSS APPLY
   (select yc.YearComponent + dc.DayComponent as JulianDiff) as jd
;

The great thing about this is that I can be very confident of my working, being able to check each step of the calculation along the way. I can even turn it into a simple TVF, whilst keeping the calculations just as verifiable:

create function dbo.YYDDD(@startdate date, @enddate date) returns table
as return
(
select jd.JulianDiff
FROM
(values (@startdate, @enddate)) working (startdate, enddate)
CROSS APPLY
   (select datediff(year,working.startdate,working.enddate) as YearsDifferent) yd
CROSS APPLY
   (select dateadd(year,yd.YearsDifferent,working.startdate) as StartDateThisYear) sdty
CROSS APPLY
   (select case when sdty.StartDateThisYear > working.enddate then -1 else 0 end as YearOffset) yo
CROSS APPLY
   (select dateadd(year,yd.YearsDifferent + yo.YearOffset,working.startdate) as AdjustedYear) ay
CROSS APPLY
   (select (yd.YearsDifferent + yo.YearOffset) * 1000 as YearComponent) yc
CROSS APPLY
   (select datediff(day,ay.AdjustedYear,working.enddate) as DayComponent) dc
CROSS APPLY
   (select yc.YearComponent + dc.DayComponent as JulianDiff) as jd
)
;

select p.*, jd.JulianDiff
from
(
    values
     ('Cesc Fàbregas', cast('4 May 1987' as date), cast('28 October 2003' as date))
    ,('Theo Walcott', cast('16 March 1989' as date), cast('19 August 2006' as date))
    ,('Jack Wilshere', cast('1 January 1992' as date), cast('13 September 2008' as date))
    ,('Thierry Henry', cast('17 August 1977' as date), cast('7 August 1999' as date))
) p (Name, DoB, Debut)
CROSS APPLY dbo.YYDDD(DoB, Debut) jd;

I know it’s really simple, but it’s an amazingly useful tip for making sure that you do things right.

Oh, and as for the plan – SQL just rolls it all up into a single Compute Scalar as if we’d written it out the long way, happily showing us that doing it step-by-step ourselves is no worse at all.

@rob_farley

When is a SQL function not a function?

Rob Farley — Tue, 08 Nov 2011 00:01:17 GMT

Should SQL Server even have functions? (Oh yeah – this is a T-SQL Tuesday post, hosted this month by Brad Schulz)

Functions serve an important part of programming, in almost any language. A function is a piece of code that is designed to return something, as opposed to a piece of code which isn’t designed to return anything (which is known as a procedure). SQL Server is no different. You can call stored procedures, even from within other stored procedures, and you can call functions and use these in other queries. Stored procedures might query something, and therefore ‘return data’, but a function in SQL is considered to have the type of the thing returned, and can be used accordingly in queries. Consider the internal GETDATE() function.

SELECT GETDATE(), SomeDatetimeColumn
FROM dbo.SomeTable;

There’s no logical difference between the field that is being returned by the function and the field that’s being returned by the table column. Both are the datetime field – if you didn’t have inside knowledge, you wouldn’t necessarily be able to tell which was which. And so as developers, we find ourselves wanting to create functions that return all kinds of things – functions which look up values based on codes, functions which do string manipulation, and so on.

But it’s rubbish.

Ok, it’s not all rubbish, but it mostly is. And this isn’t even considering the SARGability impact. It’s far more significant than that. (When I say the SARGability aspect, I mean “because you’re unlikely to have an index on the result of some function that’s applied to a column, so try to invert the function and query the column in an unchanged manner”)

I’m going to consider the three main types of user-defined functions in SQL Server:

Scalar
Inline Table-Valued
Multi-statement Table-Valued

I could also look at user-defined CLR functions, including aggregate functions, but not today. I figure that most people don’t tend to get around to doing CLR functions, and I’m going to focus on the T-SQL-based user-defined functions.

Most people split these types of function up into two types. So do I. Except that most people pick them based on ‘scalar or table-valued’. I’d rather go with ‘inline or not’.

If it’s not inline, it’s rubbish. It really is.

Let’s start by considering the two kinds of table-valued function, and compare them.

These functions are going to return the sales for a particular salesperson in a particular year, from the AdventureWorks database.

CREATE FUNCTION dbo.FetchSales_inline(@salespersonid int, @orderyear int)
RETURNS TABLE AS
RETURN (
    SELECT e.LoginID as EmployeeLogin, o.OrderDate, o.SalesOrderID
    FROM Sales.SalesOrderHeader AS o
    LEFT JOIN HumanResources.Employee AS e
    ON e.EmployeeID = o.SalesPersonID
    WHERE o.SalesPersonID = @salespersonid
    AND o.OrderDate >= DATEADD(year,@orderyear-2000,'20000101')
    AND o.OrderDate < DATEADD(year,@orderyear-2000+1,'20000101')
)
;
GO

CREATE FUNCTION dbo.FetchSales_multi(@salespersonid int, @orderyear int)
RETURNS @results TABLE (
    EmployeeLogin nvarchar(512),
    OrderDate datetime,
    SalesOrderID int
    )
AS
BEGIN
    INSERT @results (EmployeeLogin, OrderDate, SalesOrderID)
    SELECT e.LoginID, o.OrderDate, o.SalesOrderID
    FROM Sales.SalesOrderHeader AS o
    LEFT JOIN HumanResources.Employee AS e
    ON e.EmployeeID = o.SalesPersonID
    WHERE o.SalesPersonID = @salespersonid
    AND o.OrderDate >= DATEADD(year,@orderyear-2000,'20000101')
    AND o.OrderDate < DATEADD(year,@orderyear-2000+1,'20000101')
    ;
    RETURN
END
;
GO

You’ll notice that I’m being nice and responsible with the use of the DATEADD function, so that I have SARGability on the OrderDate filter.

Regular readers will be hoping I’ll show what’s going on in the execution plans here. Here I’ve run two SELECT * queries with the “Show Actual Execution Plan” option turned on.

Notice that the ‘Query cost’ of the multi-statement version is just 2% of the ‘Batch cost’. But also notice there’s trickery going on. And it’s nothing to do with that extra index that I have on the OrderDate column.

Trickery.

Look at it – clearly, the first plan is showing us what’s going on inside the function, but the second one isn’t. The second one is blindly running the function, and then scanning the results. There’s a Sequence operator which is calling the TVF operator, and then calling a Table Scan to get the results of that function for the SELECT operator. But surely it still has to do all the work that the first one is doing...

To see what’s actually going on, let’s look at the Estimated plan.

Now, we see the same plans (almost) that we saw in the Actuals, but we have an extra one – the one that was used for the TVF. Here’s where we see the inner workings of it. You’ll probably recognise the right-hand side of the TVF’s plan as looking very similar to the first plan – but it’s now being called by a stack of other operators, including an INSERT statement to be able to populate the table variable that the multi-statement TVF requires.

And the cost of the TVF is 57% of the batch!

But it gets worse.

Let’s consider what happens if we don’t need all the columns. We’ll leave out the EmployeeLogin column.

Here, we see that the inline function call has been simplified down. It doesn’t need the Employee table. The join is redundant and has been eliminated from the plan, making it even cheaper. But the multi-statement plan runs the whole thing as before, only removing the extra column when the Table Scan is performed.

A multi-statement function is a lot more powerful than an inline one. An inline function can only be the result of a single sub-query. It’s essentially the same as a parameterised view, because views demonstrate this same behaviour of extracting the definition of the view and using it in the outer query. A multi-statement function is clearly more powerful because it can contain far more complex logic.

But a multi-statement function isn’t really a function at all. It’s a stored procedure. It’s wrapped up like a function, but behaves like a stored procedure. It would be completely unreasonable to expect that a stored procedure could be simplified down to recognise that not all the columns might be needed, but yet this is part of the pain associated with this procedural function situation.

The biggest clue that a multi-statement function is more like a stored procedure than a function is the “BEGIN” and “END” statements that surround the code. If you try to create a multi-statement function without these statements, you’ll get an error – they are very much required. When I used to present on this kind of thing, I even used to call it “The Dangers of BEGIN and END”, and yes, I’ve written about this type of thing before in a similarly-named post over at my old blog.

Now how about scalar functions...

Suppose we wanted a scalar function to return the count of these.

CREATE FUNCTION dbo.FetchSales_scalar(@salespersonid int, @orderyear int)
RETURNS int
AS
BEGIN
    RETURN (
        SELECT COUNT(*)
        FROM Sales.SalesOrderHeader AS o
        LEFT JOIN HumanResources.Employee AS e
        ON e.EmployeeID = o.SalesPersonID
        WHERE o.SalesPersonID = @salespersonid
        AND o.OrderDate >= DATEADD(year,@orderyear-2000,'20000101')
        AND o.OrderDate < DATEADD(year,@orderyear-2000+1,'20000101')
    );
END
;
GO

Notice the evil words? They’re required. Try to remove them, you just get an error.

That’s right – any scalar function is procedural, despite the fact that you wrap up a sub-query inside that RETURN statement. It’s as ugly as anything. Hopefully this will change in future versions.

Let’s have a look at how this is reflected in an execution plan. Here’s a query, its Actual plan, and its Estimated plan:

SELECT e.LoginID, y.year, dbo.FetchSales_scalar(p.SalesPersonID, y.year) AS NumSales
FROM (VALUES (2001),(2002),(2003),(2004)) AS y (year)
CROSS JOIN Sales.SalesPerson AS p
LEFT JOIN HumanResources.Employee AS e
ON e.EmployeeID = p.SalesPersonID;

We see here that the cost of the scalar function is about twice that of the outer query. Nicely, the query optimizer has worked out that it doesn’t need the Employee table, but that’s a bit of a red herring here. There’s actually something way more significant going on.

If I look at the properties of that UDF operator, it tells me that the Estimated Subtree Cost is 0.337999.

If I just run the query SELECT dbo.FetchSales_scalar(281,2003); we see that the UDF cost is still unchanged.

You see, this 0.0337999 is the cost of running the scalar function ONCE.

But when we ran that query with the CROSS JOIN in it, we returned quite a few rows. 68 in fact. Could’ve been a lot more, if we’d had more salespeople or more years.

And so we come to the biggest problem. This procedure (I don’t want to call it a function) is getting called 68 times – each one between twice as expensive as the outer query. And because it’s calling it in a separate context, there is even more overhead that I haven’t considered here.

The cheek of it, to say that the Compute Scalar operator here costs 0%! I know a number of IT projects that could’ve used that kind of costing method, but that’s another story that I’m not going to go into here.

Let’s look at a better way.

Suppose our scalar function had been implemented as an inline one. Then it could have been expanded out like a sub-query. It could’ve run something like this:

SELECT e.LoginID, y.year,
(SELECT COUNT(*)
    FROM Sales.SalesOrderHeader AS o
    LEFT JOIN HumanResources.Employee AS e
    ON e.EmployeeID = o.SalesPersonID
    WHERE o.SalesPersonID = p.SalesPersonID
    AND o.OrderDate >= DATEADD(year,y.year-2000,'20000101')
    AND o.OrderDate < DATEADD(year,y.year-2000+1,'20000101')
    ) AS NumSales
FROM (VALUES (2001),(2002),(2003),(2004)) AS y (year)
CROSS JOIN Sales.SalesPerson AS p
LEFT JOIN HumanResources.Employee AS e
ON e.EmployeeID = p.SalesPersonID;

Don’t worry too much about the Scan of the SalesOrderHeader underneath a Nested Loop. If you remember from plenty of other posts on the matter, execution plans don’t push the data through. That Scan only runs once. The Index Spool sucks the data out of it and populates a structure that is used to feed the Stream Aggregate. The Index Spool operator gets called 68 times, but the Scan only once (the Number of Executions property demonstrates this).

Here, the Query Optimizer has a full picture of what’s being asked, and can make the appropriate decision about how it accesses the data. It can simplify it down properly.

To get this kind of behaviour from a function, we need it to be inline. But without inline scalar functions, we need to make our function be table-valued. Luckily, that’s ok.

CREATE FUNCTION dbo.FetchSales_inline2(@salespersonid int, @orderyear int)
RETURNS table
AS
RETURN (SELECT COUNT(*) as NumSales
    FROM Sales.SalesOrderHeader AS o
    LEFT JOIN HumanResources.Employee AS e
    ON e.EmployeeID = o.SalesPersonID
    WHERE o.SalesPersonID = @salespersonid
    AND o.OrderDate >= DATEADD(year,@orderyear-2000,'20000101')
    AND o.OrderDate < DATEADD(year,@orderyear-2000+1,'20000101')
);
GO

But we can’t use this as a scalar. Instead, we need to use it with the APPLY operator.

SELECT e.LoginID, y.year, n.NumSales
FROM (VALUES (2001),(2002),(2003),(2004)) AS y (year)
CROSS JOIN Sales.SalesPerson AS p
LEFT JOIN HumanResources.Employee AS e
ON e.EmployeeID = p.SalesPersonID
OUTER APPLY dbo.FetchSales_inline2(p.SalesPersonID, y.year) AS n;

And now, we get the plan that we want for this query.

All we’ve done is tell the function that it’s returning a table instead of a single value, and removed the BEGIN and END statements. We’ve had to name the column being returned, but what we’ve gained is an actual inline simplifiable function. And if we wanted it to return multiple columns, it could do that too. I really consider this function to be superior to the scalar function in every way.

It does need to be handled differently in the outer query, but in many ways it’s a more elegant method there too. The function calls can be put amongst the FROM clause, where they can then be used in the WHERE or GROUP BY clauses without fear of calling the function multiple times (another horrible side effect of functions).

So please. If you see BEGIN and END in a function, remember it’s not really a function, it’s a procedure. And then fix it.

@rob_farley

Joins without JOIN

Rob Farley — Tue, 04 Oct 2011 02:43:38 GMT

I’m now doing two sessions at the SQL Saturday event in Portland. I had been scheduled to do a single session (on indexes), but got an email yesterday asking if I could do another one as well. So now I’m going to do a session earlier in the day about Joins.

Yes, JOINs. Nice co-incidence to find that this month’s T-SQL Tuesday, hosted by Stuart Ainsworth and a week early because of the PASS Summit, is on that same topic. Plus the fact that I gave a presentation on it to the Adelaide SQL User Group last week.

So let’s jump in…

There are three types of joins that we write with the JOIN keyword – INNER, OUTER and CROSS. I’m imagine if you’ve read this far through this post, you’re very much aware of that. But there are other types of joins as well, that don’t use the JOIN keyword (and I’m not counting the comma short-hand for CROSS JOIN that we all used back in the 90s).

A join describes the way that two sets are related to each other within a query (and for the purposes of this post, I’m not going to count joins that concatenate two sets, such as using the UNION keyword). If you’re using the JOIN keyword, the relationship between the tables is shown in the ON clause – although if you’re using CROSS JOIN, the relationship is that every row in one set is related to every row in the other set.

If we don’t use the JOIN keyword, then where do we see joins? Well, in correlated sub-queries. I showed this when demonstrating that the APPLY keyword applies a join between one set an another in another recent blog post.

But every correlated sub-query, whether using APPLY or not must perform a join.

Consider the following:

SELECT *
, (SELECT COUNT(*) FROM Production.Product AS p
WHERE p.ProductSubcategoryID = s.ProductSubcategoryID) AS ProdCount
FROM Production.ProductSubcategory AS s;

There’s a join here, between Production.ProductSubcategory and Production.Product. The ON clause equivalent is the WHERE clause of the correlated sub-query. But what kind of join is it?

Let’s think... There’s clearly a relationship between the two tables, so it’s not a CROSS JOIN. Also, there’s nothing that would stop a row from the ProductSubcategory table to be returned, so it’s not an INNER JOIN (which only returns rows that match). It’s an OUTER JOIN. And we can prove this by looking at the execution plan.

The plan says “Right Outer Join”. Don’t be upset by the fact that it’s a Right Join rather than a Left Join – it’s just that Product table is coming from the top row and ProductSubcategory is from the bottom row. The ‘side’ of an Outer Join is just a matter of perspective. A Right Join is no different to a Left Join, it’s simply a question of which side of the desk you’re on.

Another type of join with JOIN uses EXISTS or IN. These two act very similarly, and I’m going to use EXISTS in my examples.

Consider the following query:

SELECT *
FROM Production.ProductSubcategory AS s
WHERE EXISTS
(SELECT * FROM Production.Product AS p
WHERE p.ProductSubcategoryID = s.ProductSubcategoryID);

and it’s converse:

SELECT *
FROM Production.ProductSubcategory AS s
WHERE NOT EXISTS
(SELECT * FROM Production.Product AS p
WHERE p.ProductSubcategoryID = s.ProductSubcategoryID);

Just as before, this performs a join between the two tables. But it’s not actually an Outer Join we see going on here. After all, we see that rows can be filtered out of the ProductSubcategory table. Furthermore, we don’t have access to any information in the Product table, not even the count of rows. But it does Filter. This is neither an Inner Join or an Outer Join. The EXISTS form is a Semi Join, and the NOT EXISTS form is an Anti Semi Join. These operations simply filter one set based on whether a match is present or not. We see this operation in the two plans involved.

The fact that the Query Optimizer chose to implement one with a Hash Match and one with a Nested Loop is irrelevant. We’re looking at the Logical aspects of the plan, not the Physical.

And so we see there are plenty of times that a Join can appear without the JOIN keyword.

But let me show you something interesting about the Anti Semi Join for a moment...

Many people don’t like NOT EXISTS, and would rather write my earlier query like this:

SELECT *
FROM Production.ProductSubcategory AS s
LEFT OUTER JOIN Production.Product AS p
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE p.ProductID IS NULL;

It’s relatively easy to show that these two queries should be identical. Both of them find rows where a match doesn’t exist. Both perform a logical Anti Semi Join. And yet many people will tell you that doing an Outer Join plus a WHERE clause is better – from a performance perspective. They will generally tell you that NOT EXISTS is logically equivalent.

We’ve seen plenty of times when the Query Optimizer will realise that two queries are identical and produce the same plan, but this doesn’t seem to be the case here.

This plan shows very much what we asked for. An Outer Join plus a Filter, as opposed to the Anti Semi Join.

And this has very little reason to be any faster than an Anti Semi Join operation. In fact, the interesting thing here is that the Outer Join completes. It tracks every successful match and passes that up to the Filter. Our Anti Semi Join operation doesn’t do that.

I see no reason to use an Outer Join plus Filter over NOT EXISTS. It may have been quicker at some point if the Anti Semi Join operation was done poorly in earlier versions, but I’d really like to hear if anyone can show me the Outer Join plus Filter method being quicker.

PS: This Saturday (Oct 8th) I’ll be presenting this kind of stuff in my “Deeper JOINs” session at SQL Saturday #92 in Portland, and in my pre-conference seminar a couple of days later (Monday 10th) in Seattle at the PASS Summit. You can still register for these events, so if you find this kind of thing interesting, get yourself over to the Pacific North West and I’ll see you there!

@rob_farley

My biggest recommendation for people learning T-SQL

Rob Farley — Mon, 11 Jul 2011 23:58:28 GMT

It’s not quite a Best Practice, but it’s something that I see as very important. It makes the difference between someone who might be quite good at T-SQL, and someone who can go past the rest and become one of those people who get asked to solve other people’s T-SQL problems.

It’s easy – you read the plans.

You see, the plans explain to you what you’re actually doing, instead of just following the standard formula to get it done.

It’s the same in many parts of life. If you’re going to be a mechanic, it helps to understand how an engine works. If you’re a pilot, you should understand the principles of flight.

And so it is with T-SQL. We write a query, and the Query Optimizer pulls it apart and translates it into an execution plan. It’s this plan that runs, not our query. Unfortunately, the nuances of this translation demonstrate all of us ignorant. There may be people who understand it better than most, such as some of them people who work on the Query Optimizer at Microsoft.

Grasping a handle on the main ideas is definitely worthwhile though. If you can understand the ways that Physical Joins work, and the things that influence index choices, you will naturally write better queries. You may still have trouble working out the logic that produces the correct result, but trouble-shooting will be much easier and performance gains will surely follow.

CTEs – query-scoped views

Rob Farley — Tue, 10 May 2011 00:04:37 GMT

This T-SQL Tuesday is on the topic of CTEs, and is hosted by Bob Pusateri (@SQLBob). For a bunch more posts on the topic, follow the link to his Invitation Post, and you’ll see a pile of them, all about CTEs. If you’re reading this and it’s still May 10th 2011 (GMT), then why not put a post together yourself!

I’m sure there will be plenty of posts about how CTEs can be used to wrap ranking functions, and about recursion – useful features both – but I want to explain how I see CTEs.

CTEs are Common Table Expressions. The clue is in the name – they’re table expressions, and able to be referred to in the FROM clause, fulfilling the same role that a table might. Not a table in the sense of storing data, but in the sense that a table refers to a set of data, ready to be transformed into a result set.

There are a few things that can be used as a table expression in a FROM clause. Obviously a table, but others include views, table-valued functions (whether inline or procedural), and table-valued sub-queries. Those of you who regularly read my blog (and if you’re not reading this at sqlblog.com, where are you reading it?) will know that I like to call views ‘stored sub-queries’, and TVFs ‘stored correlated sub-queries’ or ‘parameterised views’. So perhaps there’s no surprises here that these are all table expressions too.

But this post is about CTEs. They’re half way between view and sub-queries. They exist only within a single query, and contain a table-expression. A stored sub-query if you like, but only scoped to a particular query. They’re like views, but don’t persist.

So a CTE is a view for when you don’t really want a view. It’s a query-scoped view. Very useful really – and you can even use them for UPDATEs, INSERTs and DELETEs, so long as you understand that this is subject to the same rules as views (but obviously you can’t put triggers on them).

But this query-scoping can feel like a problem. What if I want my view to persist for a bit longer? That could be quite handy, couldn’t it? What if we wanted a view to be created at the start of a stored procedure, and be scoped there? We can do this with tables, we just put a hash in front of the name: CREATE TABLE #blah (id int), and the scope is set.

Unfortunately when we try to do this with views, we get a nice error.

That’s right: “Temporary views are not allowed.” It knows what we’re trying to do, it just doesn’t let us do it. It’ll give similar errors for temporary functions too.

Luckily, you can vote for this on the Connect site. There are a couple of different suggestions on how it could be done, so I’ll let you choose the one that you prefer

http://connect.microsoft.com/SQLServer/feedback/details/640863 (Please allow creation of temporary views)

http://connect.microsoft.com/SQLServer/feedback/details/343067 (Module-level table expressions)

Maybe at some point we’ll be able to have a wider range of options over the scope of our ‘common’ table-expressions.

@rob_farley

The power of T-SQL's APPLY operator

Rob Farley — Tue, 12 Apr 2011 23:30:00 GMT

I could go on all day about APPLY – it really is an incredible part of T-SQL. It helps solves problems were frustratingly painful to solve otherwise. It empowers the query writer in a way that is matched by few other features. This makes it a worthy topic for this month’s T-SQL Tuesday, hosted by Matt Velic.

Yes, I’ve already written a post for T-SQL Tuesday this month, but I’m on a flight for a couple of hours, and I do like the APPLY operator. This post will probably be more like what other people are writing this month, looking at some of the inner workings of the APPLY operator. So if you’re reading this, go and read some of the other posts about APPLY, to have things explained in a slightly different way.

APPLY, in both its inner (CROSS APPLY) and outer (OUTER APPLY) forms, allows a correlated sub-query or table-valued function to be part of the FROM clause. This is different to a regular JOIN, which is between two sets that are independent of each other, but able to be joined according to some predicate that lives in the ON clause. Using APPLY, that restriction disappears, and the predicate can be built into the sub-query (or TVF) itself.

These two queries are exactly the same. They have identical execution plans, and produce the same result.

SELECT p.Name, s.Name, s.ProductCategoryID
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS s
ON s.ProductSubcategoryID = p.ProductSubcategoryID;

SELECT p.Name, s.Name, s.ProductCategoryID
FROM Production.Product AS p
CROSS APPLY (
            SELECT subcat.Name, subcat.ProductCategoryID
            FROM Production.ProductSubcategory AS subcat
            WHERE subcat.ProductSubcategoryID = p.ProductSubcategoryID) AS s;

You may look at this and figure that the first option is entirely adequate, and see no reason to switch. There isn’t a reason to switch for most situations, and I don’t actually want you to change the way you write regular joins. But I do want you to consider the power of having a predicate as part of a correlated table-sub-query.

One common scenario is being able to hook easily into the first row in a particular join. It’s easy – you just add TOP (1) to the sub-query, and put an ORDER BY clause in there. I do this happily in BI solutions, where I want to grab a record from a dimension table that is current at the time that an event occurred.

OUTER APPLY (
    SELECT TOP (1) d.DimKey
    FROM dbo.DimensionTable AS d
    WHERE d.BusinessKey = e.DimensionCode
    AND d.StartDate < e.EventDate
    ORDER BY d.StartDate DESC) AS d

I know, you can do this very easily using a correlated sub-query in the SELECT clause, since the sub-query returns a single value (yes, it’s scalar). But what if we needed to return multiple columns from a sub-query, or needed to return the best three records for a particular category. Here I’m finding the three best-selling products for each subcategory.

SELECT s.Name, TopProds.*
FROM Production.ProductSubcategory AS s
OUTER APPLY (
    SELECT TOP (3) p.Name, SUM(o.OrderQty) AS NumOrdered
    FROM Production.Product AS p
    JOIN Sales.SalesOrderDetail AS o
    ON o.ProductID = p.ProductID
    WHERE p.ProductSubcategoryID = s.ProductSubcategoryID
    GROUP BY p.ProductID, p.Name
    ORDER BY SUM(o.OrderQty) DESC) as TopProds
;

This could be done with a Common Table Expression (CTE) and the ROW_NUMBER() functdion, but that solution isn’t nearly as elegant as this. This is the kind of solution that was designed for APPLY. One of those times when a problem and solution are nicely matched, made for each other.

Here’s an example that achieves the same using a CTE:

WITH OrderedProds AS
(
   SELECT p.ProductSubcategoryID,
          p.Name,
          SUM(o.OrderQty) AS NumOrdered,
          ROW_NUMBER() OVER (PARTITION BY p.ProductSubcategoryID
                             ORDER BY SUM(o.OrderQty) DESC) AS ProdOrder
    FROM Production.Product AS p
    JOIN Sales.SalesOrderDetail AS o
    ON o.ProductID = p.ProductID
    GROUP BY p.ProductID, p.Name, p.ProductSubcategoryID
)
SELECT s.Name, o.Name, o.NumOrdered
FROM Production.ProductSubcategory s
LEFT JOIN OrderedProds o
ON o.ProductSubcategoryID = s.ProductSubcategoryID
WHERE o.ProdOrder <= 3
;

I’m sure you’ll appreciate that this feels a lot more contrived than using APPLY. The Query Optimizer should really break these two queries down to exactly the same, recognising that joining on the PARTITION BY column(s) is a correlation mechanism. It doesn’t do that yet, but one day, I’m sure it might.

(The CTE query runs quicker than the APPLY equivalent in this particular case on the AdventureWorks database, but it shouldn’t need to. The behaviour largely depends on a number of other factors, in the same way that multiple Seeks can sometimes be faster than a Scan, but not always.)

Let’s think about views for a moment. They’re sometimes referred to as “Virtual Tables” (yes, SQL Books Online, I’m looking at you!)

People who have heard my ~~preaching~~ presenting about writing good T-SQL will have heard me refer to views as “stored sub-queries”. I reject the notion that they’re “virtual tables”. That’s rubbish – they’re treated in by the Query Optimizer as a stored sub-query, and should be referred to as such. The natural extension of this is that an inline table-valued function is a parameterised view, or stored correlated table sub-query. Basically the same as what we see here.

So let’s create it.

CREATE FUNCTION dbo.Top3ProductsBySubcategory(@subcat int) RETURNS TABLE
AS RETURN(
SELECT TOP (3) p.Name, SUM(o.OrderQty) AS NumOrdered
    FROM Production.Product AS p
    JOIN Sales.SalesOrderDetail AS o
    ON o.ProductID = p.ProductID
    WHERE p.ProductSubcategoryID = @subcat
    GROUP BY p.ProductID, p.Name
    ORDER BY SUM(o.OrderQty) DESC
    );

Notice I’ve simply changed the correlation hook (s.ProductSubcategoryID) into the parameter. Also note that I’m NOT using BEGIN and END, which would render the function procedural and non-simplifiable. To hear me go on about that in more detail, have a look at the video at http://bit.ly/SimpleRob – a URL I have to thank Farmer Paul for, which will direct you to a presentation I did at SQLBits V, in late 2009. (I figure what Paul meant when he created “Simple Rob” is that I present simple truths that have a profound importance, and not suggesting that I’m ‘simple’ at all. He’s not suggesting that my IQ is low or anything like that. He wouldn’t do that, he’s far too nice a bloke, and my friend. I mean, I know he’s a Kiwi, and Kiwis have a friendly rivalry with Aussies – I think they’re jealous of our weather – but he wouldn’t pick on me like that. Besides, Paul and I are both English originally, he just moved to NZ instead of Australia. Maybe he just enjoys colder weather.)

So now my query can become:

SELECT s.Name, TopProds.*
FROM Production.ProductSubcategory AS s
OUTER APPLY dbo.Top3ProductsBySubcategory(s.ProductSubcategoryID) AS TopProds
;

This executes in exactly the same way, but we’ve contained our logic nicely by storing the sub-query in an inline TVF. Notice the join condition – it’s buried inside the function, abstracted away, and yet completely simplifiable by the Query Optimizer. It’s still very much a join – if you were to look at the execution plan, you’d see a Nested Loop (Left Outer Join), with a Seek Predicate being used further down. (I’ve created indexes to help, of course.) You should be able to click on the image to see it in more detail.

I really could keep going about APPLY, and talk about a bunch of other uses for it, but I’ll leave that for another time.

What I want to leave you with is the reminder that APPLY is just a more powerful way of doing a JOIN, because the set that you’re joining to doesn’t need to exist yet. You may have heard people talk about APPLY being bad, in the same breath as people will tell you that a correlated sub-query is bad. Unfortunately, there are times when this is true. But there are times when any join is bad, it largely depends on what indexes you have in place, and what kind of simplification the Query Optimizer can do in working out your plan. If you have considered issues such as SARGability and Residual Predicates, then you really shouldn’t have much of a problem.

Of course you understand joining to Tables, and to Views. If you aren’t used to joining to sub-queries, then try expanding a view yourself. Then think about what happens if you use APPLY and sneak the join condition into a predicate within the sub-query. All of a sudden you’ll find yourself able to write richer queries, and have a much stronger T-SQL arsenal.