Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

When is a whitespace not a whitespace ?

As I'm sure I must have mentioned in the past, I’m presently involved in a large ALM project at a client.  Part of this project is using Sql Server Data Tools (SSDT) to aid the developer experience and to establish the schema under source control as “The Truth”.

SSDT, has… Read more

1 comments, 947 reads

Posted in Dave Ballantynes blog on 4 January 2013

SQL Saturday 194 - Exeter

Many kudos goes to Jonathan and Annette Allen and the others on the team for confirming SQL Saturday 194 in Exeter on the 8th and 9th of March.  The event home page is here and I delighted that myself and Dave Morrison will be presenting a full day pre-con… Read more

0 comments, 687 reads

Posted in Dave Ballantynes blog on 6 December 2012

My new favourite traceflag

As we are all aware, there are a number of traceflags.  Some documented, some semi-documented and some completely undocumented.  Here is one that is undocumented that Paul White(b|t) mentioned almost as an aside in one of his excellent blog posts.

Much has been written about… Read more

0 comments, 733 reads

Posted in Dave Ballantynes blog on 6 December 2012

When row estimation goes wrong

Whilst working at a client site, I hit upon one of those issues that you are not sure if that this is something entirely new or a bug or a gap in your knowledge.

The client had a large query that needed optimizing.  The query itself looked pretty good, no… Read more

0 comments, 1,922 reads

Posted in Dave Ballantynes blog on 26 November 2012

Advanced TSQL training

Over the past few years, Ive had it on my to do list to write and deliver and full-scale SQLServer training course and not just an hour long bite size session at user groups and conferences.  To me, SQLServer development is not just knowing and remembering the syntax of commands. … Read more

0 comments, 806 reads

Posted in Dave Ballantynes blog on 28 October 2012

SQL Saturday 162

SQL Saturday 162 was a great event, kudos to the team involved and I took a lot of pleasure in presenting “What’s new in SQL Server 2012 – TSQL”.

A number of people asked for the scripts, they are now here :


Any questions ? Please mail me… Read more

0 comments, 585 reads

Posted in Dave Ballantynes blog on 9 September 2012

Reporting on common code smells : A POC

Over the past few blog entries, I’ve been looking at parsing TSQL scripts in a variety of ways for a variety of tasks.  In my last entry ‘How to prevent ‘Select *’ : The elegant way’, I looked at parsing SQL to report upon uses of SELECT *. … Read more

1 comments, 1,066 reads

Posted in Dave Ballantynes blog on 29 August 2012

How to prevent ‘Select *’ : The elegant way

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

I’ve been doing a lot of work with the “Microsoft SQL Server 2012 Transact-SQL Language Service” recently, see my post here and article here for more details on its use and some uses.

An obvious use is to… Read more

2 comments, 1,607 reads

Posted in Dave Ballantynes blog on 9 August 2012

Joining on NULLs

A problem I see on a fairly regular basis is that of dealing with NULL values.  Specifically here, where we are joining two tables on two columns, one of which is ‘optional’ ie is nullable.  So something like this:

i.e. Lookup where all the columns are equal, even when NULL.  … Read more

5 comments, 1,164 reads

Posted in Dave Ballantynes blog on 26 July 2012

Indexed view deadlocking

Deadlocks can be a really tricky thing to track down the root cause of.  There are lots of articles on the subject of tracking down deadlocks, but seldom do I find that in a production system that the cause is as straightforward.  That being said,  deadlocks are always caused by… Read more

1 comments, 2,810 reads

Posted in Dave Ballantynes blog on 13 June 2012

“Query cost (relative to the batch)” <> Query cost relative to batch

OK, so that is quite a contradictory title, but unfortunately it is true.  There is a common misconception that the query with the highest percentage relative to batch is the worst performing.  Simply put, it is a lie, or more accurately we dont understand what these figures mean.

Consider the two below… Read more

1 comments, 1,515 reads

Posted in Dave Ballantynes blog on 10 May 2012

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,549 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,700 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, 610 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,061 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,557 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,734 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,639 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,237 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, 500 reads

Posted in Dave Ballantynes blog on 6 November 2011

Newer posts

Older posts