THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

A TOP Query

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at:

Published Monday, March 11, 2013 7:02 PM by Rob Farley



tobi said:

So what's the recommendation regarding that trace flag? Apply it by default on new servers?

Is it meaningful to 2012? the article does not list anything regarding 2012.

March 11, 2013 6:57 PM

Rob Farley said:

All the scripts here are from SQL 2012. I would suggest that 4199 should be applied by default on new servers, yes. By all means do some testing though. says:

"Starting with Microsoft SQL Server 2000 Service Pack 3 (SP3), the SQL Server query processor team adopted a policy that any hotfix that could potentially affect the execution plan of a query must be controlled by a trace flag. Except for fixes to bugs that can cause incorrect results or corruption, these hotfixes are turned off by default, and a trace flag is required to enable the fix. This policy change helps avoid unexpected changes to the execution plan that may occur when a hotfix or a security update is installed."

So if something in the QO has been deemed worthy of a hotfix, you still need to turn on a traceflag to get the benefit of that fix. 4199 is how you turn these things on. I'd be surprised if you found a situation where 4199 was detrimental.

March 11, 2013 7:05 PM

Rob Farley said:

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

March 11, 2013 8:20 PM

Nick said:

This is my absolute favourite sequence generator. Employed here to find gaps in a primary key.

No idea about the plan though...


digits (i) as(

select 1 as i

union all select 2

union all select 3

union all select 4

union all select 5

union all select 6

union all select 7

union all select 8

union all select 9

union all select 0


sequence (i) as (

select D1.i

+ (10*D2.i)

+ (100*D3.i)

+ (1000*D4.i)

+ (10000*D5.i)

+ (100000*D6.i)

from digits as D1,

digits as D2,

digits as D3,

digits as D4,

digits as D5,

digits as D6



(select top 1 admcdt from adm where admkey < i order by admkey desc) as before,

i as 'missing adm',

(select top 1 admcdt from adm where admkey > i order by admkey asc) as after

from sequence

where i between 1 and (select max(admkey) from adm)

and not exists (select 1 from adm where admkey=i)

order by i

All credit to --!80677FB08B3162E4!1232.entry

May 12, 2014 11:45 AM

Rob Farley said:

Hi Nick,

Using ROW_NUMBER() is a better option for generating a table of numbers, as it realises it can stop if you only need a few dozen rows. Your version doesn't get to leverage that. But actually, to find gaps, you don't even need a table of numbers. Just use ROW_NUMBER(), and look for the times when the difference between the values with gaps and the rownum increases (indicating that things have been missed).

Hope this helps,


May 12, 2014 8:56 PM

Nick said:

Hi Rob,

yes, you're right of course.

Though one thing is to identify the extents (upper and lower limit) of gaps, another is to identify the actual missing keys within the gaps, which is what mine does.

I gave it a try with ROW_NUMBER, but before I got it working I suddenly also remembered the new LAG and LEAD functions. This is what I got working:

;with cte as



lag(key) over(order by key) as previous_key,

key as current_key

from tbl



previous_key as gap_start,

current_key as gap_end

from cte

where current_key-previous_key > 1

order by current_key

May 13, 2014 8:34 AM

Rob Farley said:

Hi Nick,

Yes, that method works very well for finding gaps too.


May 13, 2014 9:06 PM
Anonymous comments are disabled

This Blog



No tags have been created or used yet.


News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG


Privacy Statement