“where ISNULL(column, ‘DontMatch’) = ‘Match’” - is kind of a bad thing to do in SQL as it means that any index on column can’t be used. It is a really simple thing that is a waste of time, fixing it is pretty simple but I see it a lot and so I thought it would be a perfect candidate for some SSDT Dev Pack t-sql re-writing :).

So the dev pack now has the ability to re-write any non-sargable IsNull statements into ones that are sargable (if you don’t know what sargable means and you are a SQL dev, look it up, it is really important!)

So if you have:

select * from dbo.tableaaa where isnull(a.a_column, 'abc') = 'abc'

it will be re-written to:

select * from dbo.tableaaa where (a.a_column is null or a.a_column = 'abc')

In fact it turns out that there ar 4 variations on the same theme:

non-sargable

type

sargable

select * from dbo.tableaaa
where isnull(a.a_column, ‘abc’) = ‘abc’

Equals To the Same Literal

select * from dbo.tableaaa
where (a.a_column is null or a.a_column = ‘abc’)

select * from dbo.tableaaa
where isnull(a.a_column, ‘abc’) ‘abc’

NOT Equals To the Same Literal

select * from dbo.tableaaa
(a.a_column is not null and a.a_column ‘abc’)

select * from dbo.tableaaa
where isnull(a.a_column, ‘abc’) ‘DEF’

NOT Equal To a Different Literal

select * from dbo.tableaaa
(a_column is null or a_column ‘DEF’)

select * from dbo.tableaaa
where isnull(a.a_column, ‘abc’) = ‘DEF’

Equals To a Different Literal

select * from dbo.tableaaa
(a.a_column = ‘DEF’)

The last one is the worst case of where ISNULL is used, converting the column to a value that doesn’t match the literal you are comparing it to is just a real waste of time.

What happens?

So if you do tools–>SSDT Dev Pack–>“Re-write Non Sargable IsNulls” then firstly you will be amazed by the catchy name and secondly it will take all isnulls that are in where clauses in an open document and if they:

• have a column reference in the first parameter
• have a literal in the second parameter
• are compared to a literal

then they are re-written following one of the four variations above which boil down to:

• Do they have an =, or != after the function
• Do they have the same or different literal in the function and the literal they compare to

Context Sensitive

This is only insterested in isnulls which are used as predicates which stop SQL Server using indexes so if you had this:

select isnull(column, 'default value') from table_name where isnull(column, 'default value') = 'abc';

then this would be re-written as:

select isnull(column, 'default value') from table_name where column = 'abc';

The isnull on the list of things to select will be ignored (however if you have a scalar subquery that has a re-writable isnull on the select list this will also be re-written).

What does it not change

It is important where in the isnull is literals or other things, if you don’t have a literal as the first parameter but instead do something like:

isnull(upper(column), 'BLAH') = 'SOMETHING'

then I ignore it.

How do I know what has happened

If you look at the output window it will show you the before t-sql and the t-sql that replaces it, if you don’t like what you see then just do a ctrl+z to undo.

Protecting yourself

This is a fairly blunt tool to help in one specific case, if you start refactoring code like this then you really need a suite of unit and integration tests to validate that what it has done is correct - no matter whether you make a change manually or using a tool you need tests to validate what you have done is correct.

You also need source control so you can go back and see what changes were made and when.

If you have neither tests or source control then I would get that working before you start refactoring code at all.

What will this look like

I am really interested in exploring how we can use the ScriptDom to do things like understand the meaning of different parts of t-sql code and would love to get to the point where on the document certain things like non-sargable queries are pointed out and potential improvements offered and then performed. I know this is easily possible it is just finding the time to figure out the best way to do it and then implement it.

How do I get it?

i have now pushed it to the visual studio gallery and will use this for all future updates so feel free to grab it from:

https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5…

---

Comments:

Anonymous

December 11, 2015 - 08:43

VS2012 support?

Hello,
I was wondering if this addon would also work on VS2012.
At this moment we are still stuck in VS2012 due to SQL2012 SSIS support (hopefully the new VS2015 based SSDT tooling will include this soon though).

Ed Elliott

December 11, 2015 - 13:27

Hi No sorry - the way ad dins

Hi No sorry - the way ad dins are done has changed quite a lot since 2012 so they aren’t backwards compatible.

When I have to use an older version of SSIS I just use whatever version for that and have a separate project with the database code in a different version of Visual Studio.