Index on Computed Columns ?

Introduction

The Question that I always find in the bog post or in community post 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.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Columns Without Data Type

Introduction

 "Can you make a table with CREATE TABLE statement, where there are 4 columns and 1 of   the columns  is without data type?"

If we heard this above statement, we definitely think for 2 to 3 seconds. That the columns without data type?

The fact is not like that. The columns without data type are not possible. If we look at the above statement carefully it says CREATE TABLE statement… some kind of syntax.

It is taking about COMPUTED COLUMNS.

Here in this article, I am not going to discuss about the COMPUTED COLUMNS. Here I am trying to discuss about the DATA TYPE, PRECISION and SCALE of the computed columns.

Example -1

First we take an example of COMPUTED COLUMNS with CREATE TABLE statement to understand the data type of computed columns.

IF OBJECT_ID('TBL_EMPLOYEE') IS NOT NULL

   BEGIN

     DROP TABLE TBL_EMPLOYEE;

   END

GO  

CREATE TABLE TBL_EMPLOYEE

       (

          EMPID    INT           IDENTITY(1,1) PRIMARY KEY,

          EMPSAL   DECIMAL(20,2) NOT NULL,

          EMPGRADE AS (CASE WHEN EMPSAL>=20000 THEN  'A'

                            WHEN EMPSAL>=10000 AND EMPSAL<20000 THEN  'B'

                            WHEN EMPSAL>=1 AND EMPSAL<10000 THEN  'C' END)

       );

GO      

-- Insert Some record

INSERT INTO  TBL_EMPLOYEE                          

       (EMPSAL)

VALUES (5000),(10000),(12000),(15000),(2000),(22000)   

-- Dispaly records

SELECT * FROM TBL_EMPLOYEE;

Result set:

EMPID       EMPSAL                                  EMPGRADE

----------- --------------------------------------- --------

1           5000.00                                 C

2           10000.00                                B

3           12000.00                                B

4           15000.00                                B

5           2000.00                                 C

6           22000.00                                A

(6 row(s) affected)

Now we type to find the Data type of COMPUTED COLUMNS named "EMPGRADE".

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,

       NUMERIC_PRECISION, NUMERIC_SCALE

FROM   INFORMATION_SCHEMA.COLUMNS

WHERE  TABLE_NAME = 'TBL_EMPLOYEE'

Result set:

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE
EMPID	int	NULL	10	0
EMPSAL	decimal	NULL	20	2
EMPGRADE	varchar	1	NULL	NULL

So for the COMPUTED COLUMNS named "EMPGRADE" the data type is VARCHAR and the size is 1. So it the DATA TYPE of COMPUTED COLUMNS depends on what it stores. Please have a look of the CREATE TABLE syntax example again.

CREATE TABLE TBL_EMPLOYEE

       (

          EMPID    INT           IDENTITY(1,1) PRIMARY KEY,

          EMPSAL   DECIMAL(20,2) NOT NULL,

          EMPGRADE AS (CASE WHEN EMPSAL>=20000 THEN  'A'

                            WHEN EMPSAL>=10000 AND EMPSAL<20000 THEN  'B'

                            WHEN EMPSAL>=1 AND EMPSAL<10000 THEN  'C' END)

       );

Please look at the marked line. In columns named "EMPGRADE" is CASE statement the input value is one character length. So it takes VARCHAR(1) as data types.

Example -2

To understand it properly, we are taken an little bit complex example to understand data type and width.

IF OBJECT_ID('TBL_EMPLOYEE') IS NOT NULL

   BEGIN

     DROP TABLE TBL_EMPLOYEE;

   END

GO  

CREATE TABLE dbo.TBL_COLUMNSPLEX

(

       COLUMNS1 DECIMAL(20,2),

       COLUMNS2 NVARCHAR(10),

       COLUMNS3 DATETIME,

       COLUMNS4 DECIMAL(10,2),

       COLUMNS5 AS COLUMNS1 + COLUMNS4,

       COLUMNS6 AS '1 ST COLUMNS :' + CAST(COLUMNS1 AS NVARCHAR(10)) +

                   '2 ND COLUMNS :' + COLUMNS2 +

                   '3 RD COLUMNS :' + CONVERT(NVARCHAR(20), COLUMNS3, 120),

       COLUMNS7 AS COLUMNS2 + ' : ' + CAST(COLUMNS4 AS NVARCHAR(36))

)

GO 

-- Insert Some record

INSERT INTO TBL_COLUMNSPLEX

       (COLUMNS1, COLUMNS2, COLUMNS3, COLUMNS4)

VALUES (100, 'JOYDEEP', GETDATE(), 200.22)      

GO

-- Dispaly records

SELECT * FROM TBL_COLUMNSPLEX

Now we type to find the Data type of COMPUTED COLUMNS.

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,

       NUMERIC_PRECISION, NUMERIC_SCALE

FROM   INFORMATION_SCHEMA.COLUMNS

WHERE  TABLE_NAME = 'TBL_COLUMNSPLEX'

COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE
COLUMNS1	decimal	NULL	20	2
COLUMNS2	nvarchar	10	NULL	NULL
COLUMNS3	datetime	NULL	NULL	NULL
COLUMNS4	decimal	NULL	10	2
COLUMNS5	decimal	NULL	21	2
COLUMNS6	nvarchar	82	NULL	NULL
COLUMNS7	nvarchar	49	NULL	NULL

Now discuss about DATATYPE and size of the COMPUTED COLUMNS.

Here are the computed columns are "COLUMNS5", "COLUMNS6", "COLUMNS7".

Here "COLUMNS5" Data type is DECIMAL.  Precision is 21 and the Scale is 2.  To understand it properly, how the precision and scale is set, we aging make a closer look of CREATE TABLE statement.

 CREATE TABLE dbo.TBL_COLUMNSPLEX

(

       COLUMNS1 DECIMAL(20,2),

       COLUMNS2 NVARCHAR(10),

       COLUMNS3 DATETIME,

       COLUMNS4 DECIMAL(10,2),

       COLUMNS5 AS COLUMNS1 + COLUMNS4,

       COLUMNS6 AS '1 ST COLUMNS :' + CAST(COLUMNS1 AS NVARCHAR(10)) +

                   '2 ND COLUMNS :' + COLUMNS2 +

                   '3 RD COLUMNS :' + CONVERT(NVARCHAR(20), COLUMNS3, 120),

       COLUMNS7 AS COLUMNS2 + ' : ' + CAST(COLUMNS4 AS NVARCHAR(36))

)

GO 

Here we are taking Precision and P, Scale as S and Expression E.

Precision Calculation

Here the COLUMNS5 = COLUMNS1(20,2) + COLUMNS4(10,2)

So    the COLUMNS5 = E1 + E2

Formula COLUMNS5 = MAX(S1, S2) + MAX(P1 – S1, P2 – S2) + 1

Putting the Values     = MAX(2, 2) + MAX(20 - 2 , 10 – 2) + 1

                                = MAX(2, 2) + MAX(18 , 8) + 1

                                = 2 + 18 +1

                                = 21 

Scale Calculation

Here the COLUMNS5 = COLUMNS1(20,2) + COLUMNS4(10,2)

So    the COLUMNS5 = E1 + E2

Formula COLUMNS5  = MAX(S1, S2)

Putting the Values      = MAX(2, 2)

                                 = 2

               

             

When two char, varchar, binary, or varbinary expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 8,000 characters, whichever is less.

When two nchar or nvarchar expressions are concatenated, the length of the resulting expression is the sum of the lengths of the two source expressions or 4,000 characters, whichever is less.

The numeric operations chart for computed columns are mentioned bellow

Operation	Precision	Scale
e1 + e2	MAX(S1, S2) + MAX(P1-S1, P2-S2) + 1	MAX(S1, S2)
e1 - e2	MAX(S1, S2) + MAX(P1-S1, P2-S2) + 1	MAX(S1, S2)
e1 * e2	P1 + P2 + 1	S1 + S2
e1 / e2	P1 - S1 + S2 + MAX(6, S1 + P2 + 1)	MAX(6, S1 + P2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2	MAX(S1, S2) + MAX(P1-S1, P2-S2)	MAX(S1, S2)
e1 % e2	MIN(P1-S1, P2 -S2) + MAX( S1,S2 )	MAX(S1, S2)

References

http://msdn.microsoft.com/en-us/library/ms190476.aspx

Related tropics

http://sqlknowledgebank.blogspot.in/2012/05/computed-columns.html

Hope you like it.

Posted by: MR. JOYDEEP DAS