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?
