The Question
that I always find in the bog post or in community post is
“Can we create the Index on Computed Columns”
“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
|
|||||||||||||
|
||||||||||||||
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
|
|||||||||||||
|
||||||||||||||
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
grt Sir..
ReplyDelete