Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Offset without OFFSET

A while ago Robert Cary posted an article on SQL Server Central entitled 2005 Paging – The Holy Grail which is, as the title would suggest about paging in SQL Server.  This article provoked some really interesting chat around the subject and is well worth a read.

This is now… Read more

5 comments, 1,520 reads

Posted in Dave Ballantynes blog on 26 April 2012

Parsing T-SQL – The easy way

UPDATE 2012-09-12 : For my latest adventures with TSQL Parsers please see this post

Every once in a while, I hit an issue that would require me to interrogate/parse some T-SQL code.  Normally, I would shy away from this and attempt to solve the problem in some other way. … Read more

2 comments, 5,469 reads

Posted in Dave Ballantynes blog on 13 March 2012

SQLMidlands & SQLLunch

Many thanks to all those that turned out to see my presentation on Thursday (16th of Feb) of “Cursors are Evil” at SQLMidlands.  The scripts i used are here :

You will need the AdventureWorks2008r2 release to run these, feel free to mail me ( with any… Read more

0 comments, 599 reads

Posted in Dave Ballantynes blog on 18 February 2012

[BUG] Inserts to tables with an index view can fail

Unfortunately some of the more troubling bugs can be very hard to reproduce succinctly.  Here is one that has been troubling me for a little while :

The issue is using indexed views with a calculated column. Indexed views, despite their restrictions, are a very handy addition to SQL Server… Read more

2 comments, 1,044 reads

Posted in Dave Ballantynes blog on 6 January 2012

Book review - SQL Server Secret Diary (Know the unknown secrets of SQL Server)

Like a lot of people within the SQL community, I can never read enough on the subject.  Books, whitepapers, academic research and blogs can all be valuable source of information, so whilst browsing Amazon I found this book on a free kindle download.  The preface makes some bold claims indeed… Read more

2 comments, 1,495 reads

Posted in Dave Ballantynes blog on 31 December 2011

Extended Events - inaccurate_cardinality_estimate

Extended events have been a bit of a personal “Elephant in the room” for me.  I know they are there and I should really get on a start using them but never *quite* have a compelling enough reason. 

So now i really do,  after comparing the events in sys.dm_xe_objects between… Read more

2 comments, 1,653 reads

Posted in Dave Ballantynes blog on 6 December 2011

MythBusting–“Table variables have no statistics”

Ok, as myths go, its a pretty weak one.  In fact, it is true, this whitepaper explicitly states that.  But hand in hand with that statement goes another one, “Table variables will always estimate to one row”.  This is most definitely false,  if there are no statistics then sql server… Read more

5 comments, 1,561 reads

Posted in Dave Ballantynes blog on 2 December 2011

Execution plan warnings–The final chapter

In my previous posts (here and here), I showed examples of some of the execution plan warnings that have been added to SQL Server 2012.  There is one other warning that is of interest to me : “Unmatched Indexes”.

Firstly, how do I know this is the final… Read more

0 comments, 1,181 reads

Posted in Dave Ballantynes blog on 29 November 2011

Blogging from 37,000ft

Im currently on my way to Sql Rally nordic and looking forward to a few days of full on SQL geekery and “Unleashing my inner Viking”.  I shall be speaking on Wednesday afternoon on one of my favourite subjects “Cursors are Evil”.  Ok,  so lets put it into perspective, “Evil”… Read more

2 comments, 489 reads

Posted in Dave Ballantynes blog on 6 November 2011

Execution plan warnings–All that glitters is not gold

In a previous post, I showed you the new execution plan warnings related to implicit and explicit warnings.  Pretty much as soon as i hit ’post’,  I noticed something rather odd happening.

This statement :

select top(10) 
from Sales.SalesOrderHeader
join Sales.SalesOrderDetail
on SalesOrderHeader.SalesOrderID
= SalesOrderDetail.SalesOrderID

Read more

0 comments, 509 reads

Posted in Dave Ballantynes blog on 6 November 2011

More Denali Execution Plan Warning Goodies

In my last blog, I showed how the execution plan in denali has been enhanced by 2 new warnings ,conversion affecting cardinality and conversion affecting seek, which are shown when a data type conversion has happened either implicitly or explicitly.

That is not all though, there is more .  Also… Read more

0 comments, 539 reads

Posted in Dave Ballantynes blog on 19 October 2011

Implicit Conversions warning

After adding an index,  ensuring that an index is actually used is probably the biggest win you will get in terms of performance in SqlServer.  There are many ways that an query can be non-sargable, and therefore not using an available index,  a common one is implicit (or even explicit)… Read more

1 comments, 793 reads

Posted in Dave Ballantynes blog on 17 October 2011

Rows or Range, What’s the difference ?

With the release of Denali CTP3 came an extension of the over clause to allow for a sliding window of data.  This allows us to , amongst other things, to efficiently and neatly calculate rolling balances.  This is a very common requirement for a database system and one which crops… Read more

0 comments, 380 reads

Posted in Dave Ballantynes blog on 22 September 2011

Unleashing my inner Viking, SQLBits and more…

In case you didn't know SQLBits 9 ,”Query across the Mersey”, is taking place in Liverpool from September 21st to October 1st.  3 days of the usual excellent quality presentations, networking interspersed with the odd beer or two should all make for an informative and entertaining conference.  Rumor has… Read more

1 comments, 895 reads

Posted in Dave Ballantynes blog on 31 August 2011

Rolling your own index lookups

Over the past few weeks, I’ve been training developers in what they really need to know about SQL Server to produce efficient and robust SQL code.  One of the areas we looked at was the “Index Lookup” aka “Bookmark Lookup”.  An index lookup occurs when a non-clustered index seek is… Read more

2 comments, 945 reads

Posted in Dave Ballantynes blog on 1 August 2011

Denali–IIF and CHOOSE

In Denali CTP3, we have a new of new functions to play with.  For this post, im going to focus on IIF and CHOOSE.

The syntax of these are :

IIF(<condition>,<true expresssion>,<false expression>)


CHOOSE(<value>,<when 1 expression>,<when 2 expression>………)

On the face of it these are simple replacements for… Read more

4 comments, 461 reads

Posted in Dave Ballantynes blog on 19 July 2011

Non use of persisted data – Part deux

In my last blog I showed how persisted data may not be used if you have used the base data on an include on an index.

That wasn't the only problem ive had that showed the same symptom.  Using the same code as before,  I was executing similar to the… Read more

0 comments, 290 reads

Posted in Dave Ballantynes blog on 20 June 2011

Itzik Ben-Gan is in town

Not that you would know it from the page below,  but Itzik Ben-Gan is back in London to do a 5 day training course, start 03october.,-programming-and-tuning-for-sql-server-2005--2008

Why QA are not screaming this from the rafters, I will never be able to fathom.  Its kind of like going for a physics… Read more

1 comments, 931 reads

Posted in Dave Ballantynes blog on 17 June 2011

Non use of persisted data

Working at a client site, that in itself is good to say, I ran into a set of circumstances that made me ponder, and appreciate, the optimizer engine a bit more.

Working on optimizing a stored procedure, I found a piece of code similar to :

select BillToAddressID,

Read more

2 comments, 419 reads

Posted in Dave Ballantynes blog on 13 June 2011

Column order can matter

Ordinarily, column order of a SQL statement does not matter.

Select a,b,c 
from table

will produce the same execution plan as
Select c,b,a
from table

However, sometimes it can make a difference.
Consider this statement (maxdop is used to make a simpler plan and has no impact to…

Read more

10 comments, 1,584 reads

Posted in Dave Ballantynes blog on 6 June 2011

Newer posts

Older posts