SQL Server 2005, wide indexes, computed columns, and sargable queries

Posted by luksan on Stack Overflow See other posts from Stack Overflow or by luksan
Published on 2010-06-02T19:31:33Z Indexed on 2010/06/02 19:44 UTC
Read the original article Hit count: 309

Filed under:
|
|
|

In my database, assume we have a table defined as follows:

CREATE TABLE [Chemical](
    [ChemicalId] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [Name] nvarchar(max) NOT NULL,
    [Description] nvarchar(max) NULL
)

The value for Name can be very large, so we must use nvarchar(max). Unfortunately, we want to create an index on this column, but nvarchar(max) is not supported inside an index.

So we create the following computed column and associated index based upon it:

ALTER TABLE [Chemical]
ADD [Name_Indexable] AS LEFT([Name], 20)

CREATE INDEX [IX_Name] 
ON [Chemical]([Name_Indexable]) 
INCLUDE([Name])

The index will not be unique but we can enforce uniqueness via a trigger.

If we perform the following query, the execution plan results in a index scan, which is not what we want:

SELECT [ChemicalId], [Name], [Description] 
FROM [Chemical] 
WHERE [Name]='[1,1''-Bicyclohexyl]-2-carboxylic acid, 4'',5-dihydroxy-2'',3-dimethyl-5'',6-bis[(1-oxo-2-propen-1-yl)oxy]-, methyl ester'

However, if we modify the query to make it "sargable," then the execution plan results in an index seek, which is what we want:

SELECT [ChemicalId], [Name], [Description] 
FROM [Chemical] 
WHERE [Indexable_Name]='[1,1''-Bicyclohexyl]-' AND [Name]='[1,1''-Bicyclohexyl]-2-carboxylic acid, 4'',5-dihydroxy-2'',3-dimethyl-5'',6-bis[(1-oxo-2-propen-1-yl)oxy]-, methyl ester'

Is this a good solution if we control the format of all queries executed against the database via our middle tier? Is there a better way? Is this a major kludge? Should we be using full-text indexing?

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server