Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Index on Computed Columns?

DZone's Guide to

Index on Computed Columns?

· Java Zone
Free Resource

Are you joining the containers revolution? Start leveraging container management using Platform9's ultimate guide to Kubernetes deployment.

Introduction

One question that I always find in blog posts or in community posts is “Can we create the Index on Computed Columns”

The answer is not so easy. To explain it properly let’s try an example. Hope you find it informative.

Simple Test to Understand the Index in Computed Columns

Step 1 - Create a Function with SCHEMA Binding

 IF OBJECT_ID(N'dbo.func_TOTMARKSWITHPRACTICAL', N'FN')IS NOT NULL
   BEGIN
      DROP FUNCTION  [dbo].[func_TOTMARKSWITHPRACTICAL];
   END
GO
CREATE FUNCTION [dbo].[func_TOTMARKSWITHPRACTICAL]
     (
        @p_MARKS    INT
     ) 
RETURNS INT     
WITH SCHEMABINDING       
AS    
BEGIN
    DECLARE @v_TOTALMARKS INT;
   
    SET @v_TOTALMARKS = @p_MARKS + 50;
   
    RETURN @v_TOTALMARKS;
END
GO
Step 2 - Create the Base Table to Use SCHEMA Binding Function and Insert Records
 IF OBJECT_ID(N'dbo.tbl_STUDENTDTLS', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_STUDENTDTLS];
   END
GO
CREATE TABLE [dbo].[tbl_STUDENTDTLS]
       (
         STDID                               INT                    NOT NULL PRIMARY KEY,
         STDNAME                       VARCHAR(50) NOT NULL,
         STDMARKS                     INT                    NOT NULL,
         STDTOTALMARKS       AS [dbo].[func_TOTMARKSWITHPRACTICAL](STDMARKS)
       );   
      
GO
INSERT INTO [dbo].[tbl_STUDENTDTLS]           
       (STDID, STDNAME, STDMARKS)
VALUES (101, 'Joydeep Das', 100),
               (102, 'Anirudha Dey', 150);
      
GO
Step 3 - Check the IsIndexTable Property of Computed Columns

 SELECT  (SELECT CASE COLUMNPROPERTY( OBJECT_ID('dbo.tbl_STUDENTDTLS'),
          'STDTOTALMARKS','IsIndexable')
                WHEN 0 THEN 'No'
                WHEN 1 THEN 'Yes'
         END) AS 'STDTOTALMARKS is Indexable ?'
STDTOTALMARKS is Indexable ?
----------------------------
Yes
Step 4 - Check the IsDeterministic Property of Computed Columns

 SELECT  (SELECT CASE COLUMNPROPERTY( OBJECT_ID('dbo.tbl_STUDENTDTLS'),
                                               'STDTOTALMARKS','IsDeterministic')
                 WHEN 0 THEN 'No'
                WHEN 1 THEN 'Yes'
         END) AS 'STDTOTALMARKS is IsDeterministic?'
STDTOTALMARKS is IsDeterministic?
---------------------------------
Yes
Step 5 - Check the USERDATTACCESS Property of Computed Columns
 SELECT  (SELECT CASE COLUMNPROPERTY( OBJECT_ID('dbo.tbl_STUDENTDTLS'),
                                               'STDTOTALMARKS','USERDATAACCESS')
                WHEN 0 THEN 'No'
                WHEN 1 THEN 'Yes'
         END) AS 'STDTOTALMARKS is USERDATAACCESS?'
 STDTOTALMARKS is USERDATAACCESS?
--------------------------------
No
Step 6 - Check the IsSystemVerified Property of Computed Columns
 SELECT  (SELECT CASE COLUMNPROPERTY( OBJECT_ID('dbo.tbl_STUDENTDTLS'),
                                               'STDTOTALMARKS','IsSystemVerified')
                WHEN 0 THEN 'No'
                WHEN 1 THEN 'Yes'
         END) AS 'STDTOTALMARKS is IsSystemVerified?'
 STDTOTALMARKS is IsSystemVerified?
----------------------------------
Yes
Step 7 - Analyzing All Property output of Computed Columns
Property Name
Output

IsIndexable
Yes

IsDeterministic
Yes

USERDATAACCESS
No

IsSystemVerified
Yes
Step 8 - So we can Crete Index on Computed Columns in this Situation
CREATE NONCLUSTERED INDEX IX_NON_tbl_STUDENTDTLS_STDTOTALMARKS
ON [dbo].[tbl_STUDENTDTLS](STDTOTALMARKS);
Step 9 - Now Check the Same thing with Function Without Schema Binding
 IF OBJECT_ID(N'dbo.func_TOTMARKSWITHPRACTICAL', N'FN')IS NOT NULL
   BEGIN
      DROP FUNCTION  [dbo].[func_TOTMARKSWITHPRACTICAL];
   END
GO
CREATE FUNCTION [dbo].[func_TOTMARKSWITHPRACTICAL]
     (
        @p_MARKS    INT
     ) 
RETURNS INT     
AS    
BEGIN
    DECLARE @v_TOTALMARKS INT;
   
    SET @v_TOTALMARKS = @p_MARKS + 50;
   
    RETURN @v_TOTALMARKS;
END
GO
Step 10 - Now analyze the same property again
Property Name
Output

IsIndexable
No

IsDeterministic
No

USERDATAACCESS
Yes

IsSystemVerified
No
Step 11 - In this scenario we are unable to Create index on Computed Columns
 CREATE NONCLUSTERED INDEX IX_NON_tbl_STUDENTDTLS_STDTOTALMARKS
ON [dbo].[tbl_STUDENTDTLS](STDTOTALMARKS);
 Error:

Msg 2729, Level 16, State 1, Line 1
Column 'STDTOTALMARKS' in table 'dbo.tbl_STUDENTDTLS'
cannot be used in an index or statistics or as a partition key
because it is non-deterministic.


Moving towards a private or Hybrid cloud infrastructure model? Get started with our OpenStack Deployment Models guide to learn the proper deployment model for your organization.

Topics:

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}