If you have ever run into the problem of implicit type conversions you will be aware that the fix for it involves a code change or a table change.  In a previous post I showed a common cause of such implicit conversions.  In this post I will show a fix for this problem that does not require any code changes.

Sample Query

The following query is using an NVARCHAR type to query a VARCHAR column.  This type of comparison in the predicate will cause a scan:

SELECT * FROM CustomerAccount WHERE AccountNbr = N'2'

Computed Column

We can fix this implicit conversion without needing to modify the original query.  We do this by creating a computed column.  Note, this does not need to be a persisted column.

ALTER TABLE CustomerAccount ADD AccountNbrNVARCHAR AS CONVERT(NVARCHAR(40), AccountNbr)

After you create the computed column you will see that a missing index is now popping up in the execution plan.

Looks promising!

Now we’ll create the suggested index:

CREATE INDEX IX_CustomerAccount_AccountNbrNVARCHAR ON CustomerAccount(AccountNbrNVARCHAR)

After creating the suggested index we now get an index seek.  And once again no code changes were made to take advantage of this.

Caveats

Of course adding a computed column with an index on it will add overhead to DML operations.  Also, if your database has implicit conversion problems all over the place you should probably be looking at making more permanent fixes.  This approach outlined is really a quick way to get some performance gains on heavy hitters without needing to wait for major schema changes to be made, or for developers to make code changes.

Happy tuning!