Index on Computed Columns ?
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.
Some Common Query related to FILTERED INDEX
Introduction
The Filtered Index is introduced at MS SQL Server 2008. Here in this article we are not directly discuss the Filtered Index, but solve some common query in mind Related to Filter Index.
An Example of Filtered Index
-- Base table
IF OBJECT_ID(N'dbo.tbl_EMPLOYEE',N'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_EMPLOYEE];
END
GO
CREATE TABLE [dbo].[tbl_EMPLOYEE]
(EMPID INT NOT NULL IDENTITY PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL,
DOB DATE NOT NULL,
GRADE CHAR(1) NOT NULL);
GO
-- Inserting Records
INSERT INTO [dbo].[tbl_EMPLOYEE]
(EMPNAME, DOB, GRADE)
VALUES('Joydeep Das', '1974-12-17', 'A'),
('Jukamal Jana','1974-10-11', 'A'),
('Chandan Bannerjee','1973-06-09', 'B'),
('Soumen Bhomik', '2008-11-28', 'C');
GO
-- Create Filtered Index
CREATE NONCLUSTERED INDEX IX_NON_GRADE_tbl_EMPLOYEE
ON [dbo].[tbl_EMPLOYEE](GRADE)
WHERE GRADE = 'A';
Now we are trying to solve some Query
Query – 1
What Type of Comparison operator used in Filter Criteria in Filtered Index
Comparison Operator
|
=
|
!=
|
<
|
<=
|
>
|
>=
|
IS NULL
|
IS NOT NULL
|
Query – 2
Is BETWEEN Clause is used in Filter Criteria of Filtered Index
NO. The BETWEEN Clause is not used in Filter Criteria of FILTERED Index. We can use >= and <= to get the output like BETWEEN Clause.
CREATE NONCLUSTERED INDEX IX_NON_GRADE_tbl_EMPLOYEE
ON [dbo].[tbl_EMPLOYEE](GRADE)
WHERE DOB >= '1974-01-01' AND DOB <= '1974-12-31';
Query – 3
Is it possible to add more data to Filtered Index without dropping and re-creating it
Yes it is possible.
Query – 4
Can we use the SUB QUERY in the Filtered Index WHERE Clause like
WHERE Grate=(SELECT grade FROM tbal_2)
NO it is not Possible. WHERE Clause of Filtered Index always needs a Scalar value.
As per MSDN
Under the "WHERE" section of the Arguments heading:
The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.
If you have any question in mind related to FILTERED INDEX please make some comments to solve it.
Because of the multiply of Quantity and Cost will lead us to Investment costs, it would be useful if we define Investment as variable storing the data of multiplication of these 2 variables.
Computed Columns
This article illustrate about the feature of computed columns.
A table can have one or more computed columns. It is an expression defining the value of the specified columns.
Let's take an example
CREATE TABLE ComputedColumns
(
QUANTITY INT NULL,
COST MONEY NULL,
INVESTMENT AS (QUANTITY * COST)
);
We're creating a table named "ComputedColumns" and setting 3 columns "Quantity", "Cost" and "Investment"
INSERT INTO ComputedColumns
(QUANTITY, COST)
VALUES (10, 1200.00)
GO
SELECT * FROM ComputedColumns
Result set
QUANTITY COST INVESTMENT
10 1200.00 12000.00
After this process, it automatically calculates the data as you can see.
Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query.
The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise. Additionally, if a computed column references a CLR function, the Database Engine cannot verify whether the function is truly deterministic. In this case, the computed column must be PERSISTED so that indexes can be created on it.
Hope you like it.
Script related to DB Index Tune
When the data is modified (INSERT, UPDATE or DELETE) the table fragmentation can occurs. To rebuild the index again we used the DBCC DBREINDEX statements can used to rebuilds all the indexes of the table.
The syntax is mentioned bellow
DBCC DBREINDEX
(
table_name
[ , index_name [ , fillfactor ] ]
)
[ WITH NO_INFOMSGS ]
table_name
Is the name of the table for which to rebuild the specified index or indexes.
Index_name
Is the name of the index to rebuilds. If the index is not specified it means all the index is going to rebuild.
fillfactors
is the percentage (%) of space on each index page used storing data when index is rebuild.
WITH NO_INFOMSGS
Suppresses all informational messages that have severity levels from 0 through 10.
sp_updatestats
is used to run the update statistics against all user-defined and internal tables of the current database.
Using this technique, I am trying to create a stored procedure that retunes your index. The stored procedure is ready to use.
The parameters @P_TBLNAME
If you supply the specified table name, it retunes all the index of the supplied table objects and updates the statistics. If not then it works with all table objects of the current database.
/*
To Tune the Database. Reindexing The Entire DB
Date: 31-March-2012
by : joydeep Das
EXEC up_RUN_REINDEXTUEN
@P_TBLNAME=''
*/
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name ='up_RUN_REINDEXTUEN')
BEGIN
DROP PROCEDURE up_RUN_REINDEXTUEN
END
GO
CREATE Procedure [dbo].[up_RUN_REINDEXTUEN]
(
@P_TBLNAME VARCHAR(MAX) = NULL
)
AS
DECLARE @v_TblName VARCHAR(MAX)
BEGIN
-- Cursor Declerations
IF ISNULL(@P_TBLNAME,'')=''
BEGIN
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.sysobjects
WHERE xtype='U'
AND name LIKE@P_TBLNAME+'%'
END
ELSE
BEGIN
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.sysobjects
WHERE xtype='U'
AND name = @P_TBLNAME
END
-- Cursor Open
OPEN db_cursor
-- Fatching the Cursor
FETCH NEXT FROM db_cursor
INTO @v_TblName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @v_TblName
DBCC DBREINDEX(@v_TblName, '', 80)
FETCH NEXT FROM db_cursor
INTO @v_TblName
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- update teh Statistics of the Table After Reindex.
EXEC sp_updatestats
END
GO
To execute
EXEC up_RUN_REINDEXTUEN
@P_TBLNAME='My_Table'
OR
EXEC up_RUN_REINDEXTUEN
@P_TBLNAME=''
