CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
HappyTown
HappyTown

Reputation: 6504

Does SQL Server allow including a computed column in a non-clustered index? If not, why not?

When a column is included in non-clustered index, SQL Server copies the values for that column from the table into the index structure (B+ tree). Included columns don't require table look up.

If the included column is essentially a copy of original data, why does not SQL Server also allow including computed columns in the non-clustered index - applying the computations when it is copying/updating the data from table to index structure? Or am I just not getting the syntax right here?

Assume:

This works:

create nonclustered index ixn_DateOpened_CustomerAccount
on dbo.CustomerAccount(DateOpened)
include(PlanID)

This does not work with left(PlanID, 3):

create nonclustered index ixn_DateOpened_CustomerAccount
on dbo.CustomerAccount(DateOpened)
include(left(PlanID, 3))

or

create nonclustered index ixn_DateOpened_CustomerAccount
on dbo.CustomerAccount(DateOpened)
include(left(PlanID, 3) as PlanType)

My use case is somewhat like below query. 

select 
    case 
        when left(PlanID, 3) = '100' then 'Basic'
        else 'Professional'
    end as 'PlanType'
from 
    CustomerAccount
where
    DateOpened between '2016-01-01 00:00:00.000' and '2017-01-01 00:00:00.000'

The query cares only for the left 3 of PlanID and I was wondering instead of computing it every time the query runs, I would include left(PlanID, 3) in the non-clustered index so the computations are done when the index is built/updated (fewer times) instead at the query time (frequently)

EDIT: We use SQL Server 2014.

Upvotes: 1

Views: 732

Answers (3)

Jeroen Mostert
Jeroen Mostert

Reputation: 28769

SQL Server 2014 allows creating indexes on computed columns, but you're not doing that -- you're attempting to create the index directly on an expression. This is not allowed. You'll have to make PlanType a column first:

ALTER TABLE dbo.CustomerAccount ADD PlanType AS LEFT(PlanID, 3);

And now creating the index will work just fine (if your SET options are all correct, as outlined here):

CREATE INDEX ixn_DateOpened_CustomerAccount ON CustomerAccount(DateOpened) INCLUDE (PlanType)

It is not required that you mark the column PERSISTED. This is required only if the column is not precise, which does not apply here (this is a concern only for floating-point data).

Incidentally, the real benefit of this index is not so much that LEFT(PlanType, 3) is precalculated (the calculation is inexpensive), but that no clustered index lookup is needed to get at PlanID. With an index only on DateOpened, a query like

SELECT PlanType FROM CustomerAccounts WHERE DateOpened >= '2012-01-01'

will result in an index seek on CustomerAccounts, followed by a clustered index lookup to get PlanID (so we can calculate PlanType). If the index does include PlanType, the index is covering and the extra lookup disappears.

This benefit is relevant only if the index is truly covering, however. If you select other columns from the table, an index lookup is still required and the included computed column is only taking up space for little gain. Likewise, suppose that you had multiple calculations on PlanID or you needed PlanID itself as well -- in this case it would make much more sense to include PlanID directly rather than PlanType.

Upvotes: 1

Alan Burstein
Alan Burstein

Reputation: 7918

As Laughing Vergil stated - you CAN index persisted columns provided that they are persisted. You have a few options, here's a couple:

Option 1: Create the column as PERSISTED then index it

(or, in your case, include it in the index)

First the sample data:

CREATE TABLE dbo.CustomerAccount
(
  PlanID int PRIMARY KEY, 
  DateOpened datetime NOT NULL,
  First3 AS LEFT(PlanID,3) PERSISTED
);
INSERT dbo.CustomerAccount (PlanID, DateOpened)
VALUES (100123, '20160114'), (100999, '20151210'), (255657, '20150617');

and here's the index: 

CREATE NONCLUSTERED INDEX nc_CustomerAccount ON dbo.CustomerAccount(DateOpened) 
INCLUDE (First3);

Now let's test:

-- Note: IIF is available for SQL Server 2012+ and is cleaner
SELECT PlanID, PlanType = IIF(First3 = 100, 'Basic', 'Professional')
FROM dbo.CustomerAccount;

Execution Plan: enter image description here As you can see- the optimizer picked the nonclustered index.

Option #2: Perform the CASE logic inside your table DDL First the updated table structure:

DROP TABLE dbo.CustomerAccount;
CREATE TABLE dbo.CustomerAccount
(
  PlanID int PRIMARY KEY, 
  DateOpened datetime NOT NULL,
  PlanType AS 
    CASE -- NOTE: casting as varchar(12) will make the column a varchar(12) column:
      WHEN LEFT(PlanID,3) = 100 THEN CAST('Basic' AS varchar(12))
      ELSE 'Professional' 
    END 
  PERSISTED
);
INSERT dbo.CustomerAccount (PlanID, DateOpened)
VALUES (100123, '20160114'), (100999, '20151210'), (255657, '20150617');

Notice that I use CAST to assign the data type, the table will be created with this column as varchar(12).

Now the index:

CREATE NONCLUSTERED INDEX nc_CustomerAccount ON dbo.CustomerAccount(DateOpened) 
INCLUDE (PlanType);

Let's test again:

SELECT DateOpened, PlanType FROM dbo.CustomerAccount;

Execution plan:

enter image description here

... again, it used the nonclustered index

A third option, which I don't have time to go into, would be to create an indexed view. This would be a good option for you if you were unable to change your existing table structure.

Upvotes: 3

Laughing Vergil
Laughing Vergil

Reputation: 3756

Computed columns are only allowed in indexes if they are Persisted - that is, if the data is written to the table. If the information is not persisted, then the information isn't even calculated / available until the field is queried.

Upvotes: 0

Related Questions