Sargable

I didn't know this world. It comes from Search ARGument ABLE (according to wikipedia) and it means (in databases world) a condition that enables the use of indexes

null is not nothing

Let's assume we have a table @a  Do you think

select * from @a
where a = a

is the same as writing

select * from @a

?

Well, think at it for a second before reading furher. Now run this code

declare @p as varchar(5) = null
declare @A as table (a varchar(5))
insert into @A values ('a1')
insert into @A values ('a2')
insert into @A values ('a3')
insert into @A values (null)
select * from @a where a = ISNULL(@p, a)

Why ? hmm, there is a nice discussion about DB NULL values here. And in many many other places on the web

Fast Query but Slow Stored Procedure

Few days ago we had a problem that I encountered years ago while working in Italy.
Basically a Stored Procedure is very slow. So slow that we didn't wait for it to finish, it probably more than 30 minutes. Let's not forget to mention that I'm running it on SQLServer 2008.
That query taken outside the stored procedure an run with the same parameters in SQLServer Management Studio runs in only few seconds. Since back in Milan I saw the same behaviour (on SQLServer 2005 that time) I thought I have the answer and can impress the colleagues with a fast solution: using  WITH RECOMPILE clause.
However, the result was the same...very slow stored procedure.
After searching the Internet we found the answer here. Parameters sniffing.  Explained here as "the process of using the parameter value to estimate selectivity and cardinality".
Indeed, our query is pretty complex and it looks like SQLServer did not know to re-create the execution plan. After changing the parameters' names, everything was OK.

Don't get over OVER

So I knew about over but didn't have the opportunity to use it every day. But today, I needed a query that would group the data by two criteria; for each group I have a number and then for each group I need the row with that number to be the MAX in the group by first criteria.

Like this: I have table T with Columns A, B and theNumber and I do a

SELECT A,b, sum(theNumber) 
FROM T 
GROUP BY A, B

And from this I need the rows with the max sum(theNumber) per A

That resulted into

SELECT A, min(B), theNb FROM ( 
 SELECT A, B, theNb,  MAX(theNb) OVER (PARTITION BY A) maxim
 FROM ( 
  SELECT A,B, sum(thenumber) as theNb FROM T
        WHERE someCondition
        GROUP BY A, B) aa 
 )  bb 
WHERE theNb=  maxim

instead of big subQueries and clumsy code

Be Discrete

Well, sometimes being discreet is being efficient :)
I recently had to optimize some reports for speed and I found one query that took around 8 seconds.
The query, a pretty simple select was doing

SELECT blah, blah
FROM theTable
WHERE Year <= @Year
AND Year > @Year - 10

What I did was using the ugly, but efficient

SELECT blah, blah
FROM theTable
WHERE Year IN (@Year-9, @Year-8, @Year-7, @Year-6, @Year-5, @Year-4, @Year-3, @Year-2, @Year-1, @Year)


The execution time dropped from 8 seconds to about 900 ms.