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
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 :
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 :
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 :
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>)
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.
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 :
Ordinarily, column order of a SQL statement does not matter.
However, sometimes it can make a difference.