Samantha J Samantha J - 7 months ago 10
SQL Question

Can I create an index on a computed column?

My table has this structure.

CREATE TABLE [dbo].[Word] (
[WordId] VARCHAR (20) NOT NULL,
[CategoryId] INT DEFAULT ((1)) NOT NULL,
PRIMARY KEY CLUSTERED ([WordId] ASC),
);


What I would like to do is to add a column called
Ascii
with a definition as
(ASCII([WordId]))


Is there a way that I can add this and then create an index on this column + WordId + CategoryId ? Also can I do this and have it automatically updated based on the data I already have existing?

Answer

You can do that by making the computed column persisted

From MSDN

Specifies that the SQL Server Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Marking a computed column as PERSISTED lets you create an index on a computed column that is deterministic

CREATE TABLE [dbo].[Word] 
(
    [WordId]       VARCHAR (20) NOT NULL,
    [CategoryId]   INT          DEFAULT ((1)) NOT NULL,
    [Ascii]        as (ASCII([WordId])) persisted,
    PRIMARY KEY CLUSTERED ([WordId] ,[CategoryId],[Ascii] ),
);

To alter the table with addition of computed column use this

ALTER TABLE [dbo].[Word] ADD [Ascii] AS (ASCII([WordId])) persisted;