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

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 :

https://skydrive.live.com/?cid=4004b6a3bc887e2c&id=4004B6A3BC887E2C%21216

You will need the AdventureWorks2008r2 release to run these, feel free to mail me (dave.ballantyne@live.co.uk) with any… Read more

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

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

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

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

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

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

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) 
       SalesOrderHeader.SalesOrderID,
       SalesOrderNumber
from Sales.SalesOrderHeader
join Sales.SalesOrderDetail
  on SalesOrderHeader.SalesOrderID 
   = SalesOrderDetail.SalesOrderID

Read more

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

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

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

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

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

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>)

and

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

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

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

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. 

http://www.qa.com/training-courses/technical-it-training/microsoft/microsoft-sql-server/microsoft-sql-server-2008-and-r2/advanced-t-sql-querying,-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

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,
       Rowguid,
       dbo.udfCleanGuid(rowguid)…

Read more

Select a,b,c 
  from table

Select c,b,a
  from table

Read more

At the Kent user group we have two upcoming events.  Both are to be held at F-Keys Training suite http://f-keys.co.uk/ in Rochester, Kent.

If you haven’t attended before please note the location here.

14-June

Is your code S.O.L.I.D ?

Nathan Gloyn

Everybody keeps on about SOLID principles but what… Read more