Resolved: Could not complete cursor operation because the table schema changed after the cursor was declared.

I have written about how a reindex job can interrupt cursor operations:

Could not complete cursor operation because the table schema changed after the cursor was declared.

Regardless of WAIT_AT_LOW_PRIORITY, index operations can still break cursor operations

Today I got the chance to have another go at this issue. In previous posts, the cursor was declared as is without specifying any attributes.

After going through all the attributes, I have decided to test a few and see if they would make any difference. I will spare you the trouble and list the findings below:

FAST_FORWARD

Could not complete cursor operation because the table schema changed after the cursor was declared.

FORWARD_ONLY

Could not complete cursor operation because the table schema changed after the cursor was declared.

READONLY

Could not complete cursor operation because the table schema changed after the cursor was declared.

STATIC

It worked!

It was quite obvious why it worked in hindsight, according to the trusted Books Online:

STATIC

Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

There you go, problem solved.

How to break up a large data insert script so it is runnable in SQL Server Management Studio

I needed to move a reasonably large amount of data from one table to another table on a different server. It was a one off activity so I didn’t want to bother with tools such as the Import and Export Wizard nor SSIS. I simply wanted to generate a script with the insert statements and run it in SSMS.

OK, when I tried that the first time, SSMS wasn’t too happy running the 160,000 line script:

Msg 8623, Level 16, State 1, Line 1
The query processor ran out of internal resources and could not 
produce a query plan. This is a rare event and only expected for 
extremely complex queries or queries that reference a very large 
number of tables or partitions. Please simplify the query. If you 
believe you have received this message in error, contact Customer 
Support Services for more information.

The error was somehow anticipated when I saw the size of the script. I worked around the issue by keeping the insert statements in the same script but breaking up the “union all select” into separate inserts. Here is the full example for you to devour:

use tempdb

if object_id('dbo.student') is not null
 drop table dbo.student

create table dbo.student
(
 id int not null identity(1, 1)
 , name sysname not null
 , residential_address nvarchar(max) not null
)

if object_id('dbo.merge_output') is not null
 drop table dbo.merge_output

create table dbo.merge_output
(
 id int not null
 , name sysname not null
 , residential_address nvarchar(max) not null 
 , merge_action sysname not null
)
 
;with cte_source(name, residential_address)
as
(
 select 'Louie1', '1 King St'
 union all select 'Louie2', '2 King St'
 union all select 'Louie3', '3 King St'
 union all select 'Louie4', '4 King St'
 union all select 'Louie5', '5 King St'
 union all select 'Louie6', '6 King St'
 union all select 'Louie7', '7 King St'
 union all select 'Louie8', '8 King St'
 union all select 'Louie9', '9 King St'
 union all select 'Louie10', '10 King St'
 union all select 'Louie11', '11 King St'
 union all select 'Louie12', '12 King St'
 union all select 'Louie13', '13 King St'
 union all select 'Louie14', '14 King St'
 union all select 'Louie15', '15 King St'
 union all select 'Louie16', '16 King St'
 union all select 'Louie17', '17 King St'
 union all select 'Louie18', '18 King St'
 union all select 'Louie19', '19 King St'
 union all select 'Louie20', '20 King St'
)
merge Student t
using cte_source s on t.name = s.name
when not matched then
 insert (name, residential_address) 
 values (s.name, s.residential_address)
when matched then
 update 
 set residential_address = s.residential_address
output inserted.id
, inserted.name
, inserted.residential_address
, $action
into merge_output;

if object_id('dbo.insert_statement') is not null
 drop table dbo.insert_statement

create table dbo.insert_statement
(
 seq int not null identity(1, 1)
 , cmd nvarchar(max) not null 
)

insert dbo.insert_statement
select concat('union all select ''id:', id
, ', name:', name
, ', residential_address: ', residential_address
, ', event:', merge_action, '''')
from merge_output

update dbo.insert_statement
set cmd = replace(cmd, 'union all ', 'go' + char(13) + char(10) 
+ 'insert dbo.event (message)' + char(13) + char(10))
where seq % 5 = 1

print '
**** Run the command below to export data:
sqlcmd -S ' + @@servername + ' -d tempdb -M -y0 -Q "set nocount on;
select cmd from dbo.insert_statement order by seq" -o "events.sql"'

Let me walk you though the script.

use tempdb

if object_id('dbo.student') is not null
 drop table dbo.student

create table dbo.student
(
 id int not null identity(1, 1)
 , name sysname not null
 , residential_address nvarchar(max) not null
)

dbo.student is a table where we insert/update student details. As a result of the insert/update, we want to capture the actions as events and insert into an event table.

if object_id('dbo.merge_output') is not null
 drop table dbo.merge_output

create table dbo.merge_output
(
 id int not null
 , name sysname not null
 , residential_address nvarchar(max) not null 
 , merge_action sysname not null
)
 
;with cte_source(name, residential_address)
as
(
 select 'Louie1', '1 King St'
 union all select 'Louie2', '2 King St'
 union all select 'Louie3', '3 King St'
 union all select 'Louie4', '4 King St'
 union all select 'Louie5', '5 King St'
 union all select 'Louie6', '6 King St'
 union all select 'Louie7', '7 King St'
 union all select 'Louie8', '8 King St'
 union all select 'Louie9', '9 King St'
 union all select 'Louie10', '10 King St'
 union all select 'Louie11', '11 King St'
 union all select 'Louie12', '12 King St'
 union all select 'Louie13', '13 King St'
 union all select 'Louie14', '14 King St'
 union all select 'Louie15', '15 King St'
 union all select 'Louie16', '16 King St'
 union all select 'Louie17', '17 King St'
 union all select 'Louie18', '18 King St'
 union all select 'Louie19', '19 King St'
 union all select 'Louie20', '20 King St'
)
merge Student t
using cte_source s on t.name = s.name
when not matched then
 insert (name, residential_address) 
 values (s.name, s.residential_address)
when matched then
 update 
 set residential_address = s.residential_address
output inserted.id
, inserted.name
, inserted.residential_address
, $action
into merge_output;

We are using a MERGE statement to get the data into the dbo.student table. The changes are captured by the OUTPUT clause into the dbo.merge_output table.

if object_id('dbo.insert_statement') is not null
 drop table dbo.insert_statement

create table dbo.insert_statement
(
 seq int not null identity(1, 1)
 , cmd nvarchar(max) not null 
)

insert dbo.insert_statement
select concat('union all select ''id:', id
, ', name:', name
, ', residential_address: ', residential_address
, ', event:', merge_action, '''')
from merge_output

Now, we are taking data out of the dbo.merge_output table and inserting into a transient table called dbo.insert_statement. If you select everything from dbo.insert_statement now, you’ll see:

seq cmd
1 union all select 'id:1, name:Louie1, residential_address:1 King St, event:INSERT'
2 union all select 'id:2, name:Louie2, residential_address:2 King St, event:INSERT'
3 union all select 'id:3, name:Louie3, residential_address:3 King St, event:INSERT'
4 union all select 'id:4, name:Louie4, residential_address:4 King St, event:INSERT'
5 union all select 'id:5, name:Louie5, residential_address:5 King St, event:INSERT'
6 union all select 'id:6, name:Louie6, residential_address:6 King St, event:INSERT'
7 union all select 'id:7, name:Louie7, residential_address:7 King St, event:INSERT'
8 union all select 'id:8, name:Louie8, residential_address:8 King St, event:INSERT'
9 union all select 'id:9, name:Louie9, residential_address:9 King St, event:INSERT'
10 union all select 'id:10, name:Louie10, residential_address:10 King St, event:INSERT'
11 union all select 'id:11, name:Louie11, residential_address:11 King St, event:INSERT'
12 union all select 'id:12, name:Louie12, residential_address:12 King St, event:INSERT'
13 union all select 'id:13, name:Louie13, residential_address:13 King St, event:INSERT'
14 union all select 'id:14, name:Louie14, residential_address:14 King St, event:INSERT'
15 union all select 'id:15, name:Louie15, residential_address:15 King St, event:INSERT'
16 union all select 'id:16, name:Louie16, residential_address:16 King St, event:INSERT'
17 union all select 'id:17, name:Louie17, residential_address:17 King St, event:INSERT'
18 union all select 'id:18, name:Louie18, residential_address:18 King St, event:INSERT'
19 union all select 'id:19, name:Louie19, residential_address:19 King St, event:INSERT'
20 union all select 'id:20, name:Louie20, residential_address:20 King St, event:INSERT'

I have 20 rows in the example but the real data set I had to deal with as I mentioned earlier was 160,000 rows. Initially, I only added one “insert dbo.event (message)” as the first line with all the “union all select” following it. (Of course, the first union all was removed but I won’t show you how that’s done because the solution below covers this part)

update dbo.insert_statement
set cmd = replace(cmd, 'union all ', 'go' + char(13) + char(10) 
+ 'insert dbo.event (message)' + char(13) + char(10))
where seq % 5 = 1

This whole blog is about the statement above. In this UPDATE statement, we are trying to achieve three objectives:

  1. Add the “insert dbo.event (message)” header necessary for the insert statement.
  2. Remove “union all ” from the first line of the SELECT statements.
  3. Break up the script into chunks.

Once exported, the events.sql file looks like:

go
insert dbo.event (message)
select 'id:1, name:Louie1, residential_address:1 King St, event:INSERT'
union all select 'id:2, name:Louie2, residential_address:2 King St, event:INSERT'
union all select 'id:3, name:Louie3, residential_address:3 King St, event:INSERT'
union all select 'id:4, name:Louie4, residential_address:4 King St, event:INSERT'
union all select 'id:5, name:Louie5, residential_address:5 King St, event:INSERT'
go
insert dbo.event (message)
select 'id:6, name:Louie6, residential_address:6 King St, event:INSERT'
union all select 'id:7, name:Louie7, residential_address:7 King St, event:INSERT'
union all select 'id:8, name:Louie8, residential_address:8 King St, event:INSERT'
union all select 'id:9, name:Louie9, residential_address:9 King St, event:INSERT'
union all select 'id:10, name:Louie10, residential_address:10 King St, event:INSERT'
go
insert dbo.event (message)
select 'id:11, name:Louie11, residential_address:11 King St, event:INSERT'
union all select 'id:12, name:Louie12, residential_address:12 King St, event:INSERT'
union all select 'id:13, name:Louie13, residential_address:13 King St, event:INSERT'
union all select 'id:14, name:Louie14, residential_address:14 King St, event:INSERT'
union all select 'id:15, name:Louie15, residential_address:15 King St, event:INSERT'
go
insert dbo.event (message)
select 'id:16, name:Louie16, residential_address:16 King St, event:INSERT'
union all select 'id:17, name:Louie17, residential_address:17 King St, event:INSERT'
union all select 'id:18, name:Louie18, residential_address:18 King St, event:INSERT'
union all select 'id:19, name:Louie19, residential_address:19 King St, event:INSERT'
union all select 'id:20, name:Louie20, residential_address:20 King St, event:INSERT'

Bonus Tip – Exporting string data out of a table without truncation

print '
**** Run the command below to export data:
sqlcmd -S ' + @@servername + ' -d tempdb -M -y0 -Q "set nocount on;
select cmd from dbo.insert_statement order by seq" -o "events.sql"'

If there is a string column longer than 256 characters, it can be fiddly trying to export it out of SSMS. Instead of messing around with query settings, I simply issued a sqlcmd command with the -y0 switch:

-y variable_length_type_display_width
If display_width is 0, the output is truncated at 1 MB.

It works most of the time but if your string is larger than 1MB, then you need a more sophisticated solution.

My job is done for the day.

Regardless of WAIT_AT_LOW_PRIORITY, index operations can still break cursor operations

Today I had the pleasure to read about the new index rebuild option WAIT_AT_LOW_PRIORITY. It’s a great addition to the DBA arsenal and you can read all about it here: Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

I have written about how index operations can interrupt cursor operations. Now naturally I wondered whether the new index option makes any difference to this scenario. Let’s work through an example.

1. Create table and index

create table temp
(
    id int not null identity(1, 1) primary key
    , data varchar(10) not null default('test')
)
go

insert temp default values
go 50

create index idx_temp on temp (data)
go

2. Start a cursor loop

declare cur cursor
for
    select id from temp

open cur

declare @id int

fetch cur into @id

while @@fetch_status = 0
begin
    print @id
    waitfor delay '00:00:01'
    
    fetch cur into @id
end

close cur
deallocate cur

3. Rebuild index with the new option

alter index idx_temp 
on temp 
rebuild with 
(
    online = on 
    (
        wait_at_low_priority
        (
             max_duration = 1 minutes
             , abort_after_wait = self
        )
    )
)

We still get the error:

1
2
3
4
5
6
Msg 16943, Level 16, State 4, Line 18
Could not complete cursor operation because the table schema changed 
after the cursor was declared.

I guess the lesson for today is that no matter how seemingly harmless/efficient the index operation is, you still need to test it thoroughly before applying to prod.

What is query_hash from sys.dm_exec_query_stats?

According to Books Online, it is a:

Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values.

My first question after reading the above was how similar is similar? There is only one way to find out and what started out as a simple experiment turned into a game of its own. The aim of the game is to change a proc in as many steps as possible and still generate the same query_hash. My record was 10 steps and stopped at that.  Here is an example of what I did:

StepChangequery_hashProc
1Create a new proc0x47F48A4C2B564BB8CREATE proc louie as select * from sys.objects
2Add a parameter0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as select * from sys.objects
3Set nocount on0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on select * from sys.objects
4Return 10x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on select * from sys.objects return 1
5Print0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' select * from sys.objects print 'After' return 1
6Raiserror0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' select * from sys.objects print 'After' raiserror('Error', 16, 1) return 1
7Try...Catch0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' begin try select * from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
8Return all columns0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on print 'Before' begin try select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
9Set transaction isolation level read uncommitted0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
10Manipulate variable0x47F48A4C2B564BB8CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try if @name is null set @name = 'louie' select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1

I think by now you get the picture. My observation was that as long as the query was not touched, the query_hash stayed the same. Now let’s see what changes would cause a different query_hash to be generated. By the way, below is the query I used to retrieve the query_hash:

select a.sql_handle, a.query_hash, c.text
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.sql_handle) c
join sys.objects b on c.objectid = b.object_id
where b.name = 'louie'
Changequery_hashProc
With (nolock)0xC32DED2DE8B7FB38CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try if @name is null set @name = 'louie' select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects with (nolock) print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1
Add a where clause to return the same resultset0xE659977264A05561CREATE proc louie @name sysname = null as set nocount on set transaction isolation level read uncommitted print 'Before' begin try if @name is null set @name = 'louie' select name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published
from sys.objects where name = isnull(nullif(@name, 'louie'), name) print 'After' end try begin catch raiserror('Error', 16, 1); throw end catch return 1

The list is of course not exhaustive and it is pretty safe to assume that if a query is modified (with the potential to return a different resultset), its query_hash would change as well.

Could not complete cursor operation because the table schema changed after the cursor was declared.

Msg 16943, Level 16, State 4, Line 16
Could not complete cursor operation because the table schema changed after the cursor was declared.

Adding or removing non clustered indexes seems like a low risk performance tuning operation, right? Well, under normal circumstances it is true as you are not modifying the underlying data nor changing the table schema. I found out the hard way today that it could cause rather nasty errors like the one above if it is performed while there is a cursor loop running in another session.

 

I will show you in a quick demo. Let’s start by creating a table and inserting some data.

 

 

create table temp
(
    id int not null identity(1, 1) primary key
    , data varchar(10) not null default('test')
)
go

insert temp default values
go 50

We will then kick off a simple cursor looping through the table.

declare cur cursor
for
    select id from temp

open cur

declare @id int

fetch cur into @id

while @@fetch_status = 0
begin
    print @id
    waitfor delay '00:00:01'
    
    fetch cur into @id
end

close cur
deallocate cur

 

 

While the session is running, we will pretend that we have no knowledge of the running cursor code and we’ve identified a performance tuning opportunity to add a non clustered index to the table. We will do just that but in a separate session window.

 

create index idx_temp on temp (data)

 

 

Once the index is created, we then switch back to the session window with the cursor and should expect to see the error in the Messages tab.

 

1
2
3
4
5
Msg 16943, Level 16, State 4, Line 16
Could not complete cursor operation because the table schema changed after the cursor was declared.

So there you have it, I’ve just demonstrated that no matter how seemingly harmless an operation is, there is always a potential risk for unintended side effects.

CHARINDEX actually works with text data type, but…

We had a table which was used to store error logs in a text column. From time to time I needed to extract some “error” messages out and I always resorted to charindex. Most of the time it worked just fine but sometimes I would get an empty result set back. As this behaviour wasn’t any show stopper so I never paid too much attention to it until one day I decided to get to the bottom of this seemingly random behaviour.

As usual, when I am uncertain about a system function, I would go to Books Online and read the definition, repeatedly at times. After scrolling down to the remarks section, a close inspection revealed something startling:

CHARINDEX cannot be used with text, ntext, and image data types.

This is simply not true! I know because I have been using charindex with text for months if not years. I am speculating that the author actually meant:

CHARINDEX should not be used with text, ntext, and image data types.

Now, I am going to show you with a simple demo that charindex CAN be used with text. However, there is a catch.

Let’s start with the script below:

-- Create a temp table for demo purpose.
declare @charindex_test table
( 
    text_data       text 
    , varchar_data  varchar(max) 
)

/*
Pretend that we have a long string with the
word error in there. Note the position of the
characters. 
 
Pos: 7995 7996 7997 7998 7999 8000
Char:   p    e    r    r    o    r
*/
insert @charindex_test (varchar_data) 
values (replicate(cast('p' as varchar(max))
        , 8000 - len('error')) + 'error') 

/*
Pretend that we have a long string with the
word error in there. Note the position of the
characters. 
 
Pos: 7996 7997 7998 7999 8000 8001
Char:   p    e    r    r    o    r
*/
insert @charindex_test (varchar_data) 
values (replicate(cast('p' as varchar(max))
        , 8001 - len('error')) + 'error')

-- Copy the content to a text column
update @charindex_test 
set text_data = varchar_data

-- Demonstrate the effect of using charindex with text
select 
    text_data 
    , varchar_data
    , datalength(text_data)            as 'text_data_bytes' 
    , charindex('error', text_data)    as 'text_data_pos' 
    , datalength(varchar_data)         as 'varchar_data_bytes' 
    , charindex('error', varchar_data) as 'varchar_data_pos' 
from @charindex_test

select *
from @charindex_test
where text_data like '%error%'

By running the entire script, we’ll get the output like the display below:

image

From this simple demo, we can make the following observations:

  • CHARINDEX can be used with text. However, there is an 8000 character limit.
  • LIKE on the other hand does not suffer from the 8000 characters limitation.
  • If you have to rely on CHARINDEX, then cast text as varchar(max) first.
  • You cannot blindly trust the official documentation. Always test and see with your own eyes.

How to format nested REPLACE statements cleanly and efficiently

Replace is one of my favourite T-SQL string functions. One of its main attractions is the ability to remove noise words from a large chunk of text. Most of the time though, you are likely to have quite a few word patterns that you want to filter out, and this will make the code hard to read, see the example below:

select replace(replace(replace(message, 'Microsoft (R) SQL Server Execute Package Utility', ''), 'Version 10.0.5500.0', ''), 'Code: 0xC0202009', '') from msdb..sysjobhistory where run_status = 0

It only takes three to four nested replace statements to make the whole select statement look like a wiggly snake, not to mention those hard to track commas and brackets.

Below is a much better way of writing nested REPLACE statements:

select replace(replace(replace(message
    , 'Microsoft (R) SQL Server Execute Package Utility', '')
    , 'Version 10.0.5500.0', '')
    , 'Code: 0xC0202009', '')
from msdb..sysjobhistory
where run_status = 0

By utilising one line per REPLACE, it instantly becomes clear what you are replacing and the people maintaining you code will thank you for the readability.

Duplicate Checksum

I was using checksum() to perform a reconciliation task and found that for a decimal number, the positive and the negative value will produce the same checksum.

image

Even though I knew that checksum() is not perfect for checking differences, but I was still quite surprised to find how easy it is to produce duplicate checksums.

The following table lists some of my observations.

Expression Checksum
checksum(1)
checksum(-1)
1
-1
binary_checksum(1)
binary_checksum(-1)
1
-1
checksum(1.0)
checksum(-1.0)
-1374215283
-1374215283
binary_checksum(1.0)
binary_checksum(-1.0)
-1374215283
-1374215283
checksum(‘1’)
checksum(‘-1’)
65
65
binary_checksum(‘1’)
binary_checksum(‘-1’)
49
737
checksum(‘1.0’)
checksum(‘-1.0’)
-1043840145
-1043840145
binary_checksum(‘1.0’)
binary_checksum(‘-1.0’)
13264
189392

Looking at the table above, I think I will stop using checksum() in the future.

Database ‘xxx’ cannot be opened because it is offline

Got an error the other day which seemed pretty straight forward at first.

Msg 942, Level 14, State 4, Procedure test, Line 4
Database 'ghost' cannot be opened because it is offline.

I will provide some context to the cause of the error message before I go on with the story. A database was being migrated from one server to the other. As part of the migration, a DBA has implemented a strategy using a bridging database so that the database would appear available on both servers.

image

It was all nice and slick during the transition phase of the migration project. Then on the day of the cut-over, the DBA migrated the database following these steps:

1. Rename the database on Server B. For example, sp_renamedb ‘active’, ‘ghost’

2. Migrate the database from Server A to Server B.

3. Setting the renamed database ‘ghost’ to offline so no one can access it anymore.

In hindsight, the ‘ghost’ database should’ve been removed instead of the rename and offline approach.

After the database was migrated, users started to get error messages like the one shown at the start of the blog post. My initial assumption was that somewhere someone was still referencing the bridging database.

The next natural thing to do was to search in all the code and see if anyone anywhere was still referencing the database ‘ghost’. You probably already guessed, the search revealed nothing at all. Since I wasn’t the person working on the migration project, I wasn’t sure if I understood everything the DBA had done. Whilst discussing the error with the DBA and getting him to confirm that all the migration steps had been done, another senior DBA listening on the side interrupted with a comment that was like a lightning bolt from the clear sky “It would be the execution plans.

Because the offline ‘ghost’ database was not removed from the server, the database id was still cached in the execution plans.

image

The solution was then easy once the root cause was determined. The execution plan just needed to be refreshed by detaching the database.

Which table do I join first?

From time to time, I get confused about the order and the outcome of joins when presented with multiple options. I have decided to make a cheat sheet for future reference.

You can either follow the examples step by step below or jump straight to the summary at the end.

Let’s prepare the sample tables and data:

use tempdb

create table Security
(
    SecurityID int
    , SecurityName varchar(100)
)

create table SecurityCodeType
(
    SecurityCodeTypeID int
    , SecurityCodeType varchar(100)
)

create table SecurityCode
(
    SecurityID int
    , SecurityCodeTypeID int
    , SecurityCode varchar(100)
)
    
insert Security values (123, 'BHP BILLITON LIMITED')
insert SecurityCodeType values (1, 'ASX')
insert SecurityCode values (123, 1, 'BHP')

Now, looking at the following three queries, they all return the same result.

select a.SecurityName, b.SecurityCode, c.SecurityCodeType
from Security a
left join SecurityCode b on a.SecurityID = b.SecurityID
join SecurityCodeType c  on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                            and c.SecurityCodeType = 'ASX'

image

select a.SecurityName, b.SecurityCode, c.SecurityCodeType
from Security a
left join SecurityCode b        on a.SecurityID = b.SecurityID
left join SecurityCodeType c    on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                                    and c.SecurityCodeType = 'ASX'

image

select a.SecurityName, b.SecurityCode, b.SecurityCodeType
from Security a
left join 
(
    select b.SecurityID, b.SecurityCode, c.SecurityCodeType
    from SecurityCode b    
    join SecurityCodeType c    on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                                and c.SecurityCodeType = 'ASX'                                    
) b on a.SecurityID = b.SecurityID

image

This is only true if the matching data is found in all three tables. In other words, the LEFT OUTER JOINs don’t serve much purpose here.

Now if we manipulate the queries so the LEFT OUTER JOINs start to have effects, the results will become:

select a.SecurityName, b.SecurityCode, c.SecurityCodeType
from Security a
left join SecurityCode b    on a.SecurityID = b.SecurityID
join SecurityCodeType c        on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                                and c.SecurityCodeType = 'SEDOL'

image

select a.SecurityName, b.SecurityCode, c.SecurityCodeType
from Security a
left join SecurityCode b        on a.SecurityID = b.SecurityID
left join SecurityCodeType c    on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                                    and c.SecurityCodeType = 'SEDOL'

image

select a.SecurityName, b.SecurityCode, b.SecurityCodeType
from Security a
left join 
(
    select b.SecurityID, b.SecurityCode, c.SecurityCodeType
    from SecurityCode b    
    join SecurityCodeType c    on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                                and c.SecurityCodeType = 'SEDOL'                                    
) b on a.SecurityID = b.SecurityID

image

If the differences in the above queries are not obvious to you, see if it is any better if we read them in English:

1. Return all securities which has a SEDOL code.

2. Return all securities AND their codes if available AND indicate the SEDOL codes.

3. Return all securities AND the codes only if they are SEDOL codes.

Now I hope you understand the strategic placement of the LEFT OUTER JOIN is a critical decision to make that it may totally change the information you intended to extract from the database.

I have summarised the above observations in a table below for quick future reference.

Pattern Mandatory Optional
from Table1
left join Table2
join Table3
Table1, Table3. Table2.
from Table1
left join Table2
left join Table3
Table1. Table2, Table3.
from Table1
left join
(
    from Table2
    join Table3
)
Table1. Table2 + Table3.