SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

Browse by Tags

All Tags » Optimizer » TSQL (RSS)
Sub query pass through
Occasionally in forums and on client sites I see conditional subqueries in statements. This is where the developer has decided that it is only necessary to process some data under a certain condition.  By way of example, something like this : Create...
Denali Paging–Key seek lookups
In my previous post “ Denali Paging – is it win.win ? ” I demonstrated the use of using the Paging functionality within Denali.  On reflection,  I think i may of been a little unfair and should of continued always planned to continue my investigations...
Denali Paging–Is it win/win ?
UPDATE : 2012-07-26 : My latest commentry of the paging subject can be found here : http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/04/26/offset-without-offset.aspx I'm not one for automatically poo-pooing new ideas, new practices and new...
The best things in life are free
Greg Gonzalez ( blog | twitter ) of SqlSentry , last night released a very cool new and free product.  Plan explorer is a tool for examining query plans and overcomes many of the limitations of viewing plans within Management studio.  If you...
An impossible case
In a previous blog entry ( Inconsistent NullIf behaviour ), Jeff Moden and Dan Halliday ( twitter ) both made a very similar observation. When executing this code Code Snippet select top ( 10 ) abs ( checksum ( newid ()))% 4 from sys . objects the only...
Inconsistent NullIF Behaviour ?
NullIf can be a very handy function sometimes. For those who have not used it, if the result of the two parameters are equal then null is returned. So if we execute : Code Snippet Select nullif ( 10 , 10 ) as Result1 Select nullif ( 10 , 9 ) as Result2...
Using a UDF as a SARG. Make a hash of it!
I am somewhat uncomfortable blogging this, as I feel that it falls into “Polish a turd” category. Having said that, it is a particularly ugly turd and it does polish up rather well. Though as the saying goes, it is still a turd. The evils and inadequacies...
SQL 101 : Without “ORDER BY”, order is not guaranteed.
It’s as simple as that. If you don’t specify ORDER BY, then the order of your result set is not guaranteed. Oh sure , there may be situations where the data looks like it is being returned ordered, but this is not really the case. So, for example : CREATE...
SQL and contiguous data ranges.
As the saying goes "work smarter, not harder", this is definitely the case for SQL. If you have a poorly performing query, then you are not only affecting that one user but diverting precious machine resources needlessly. This is doubly true...
Optimal Query Plans With Unbalanced Data Loads
Sql Server is great at producing and caching efficient query plans.  However these plans may not be the most efficient based upon the parameters passed into subsequent calls off a stored procedure. In this article I am assuming that you have read...