SQL and the like

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

Browse by Tags

All Tags » SQLServer (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...
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...
Challenge 19 – An Explanation of a Query
I have received a number of requests for an explanation of my winning query of TSQL Challenge 19 . This involved traversing a hierarchy of employees and rolling a count of orders from subordinates up to superiors. The first concept I shall address is...
BUG - Use of ranking functions result in an inefficient query plan
In SQL2005 (9.00.4207.00), if you use a ranking function within a view or CTE, then an inefficient query plan can be produced. First off in Adventure works create this index Create Index idxLastName on Person . Contact ( LastName ) and then consider these...