CODEWITHSUNDEEP
sqlsql-servercalculated-columns
Samantha J T Star
Samantha J T Star

Reputation: 32758

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?

Upvotes: 3

Views: 335

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

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;

Upvotes: 4

Related Questions