RSS

How Can i Create index on Non-Deterministic Column in SQL Server

17 May

Question 1 :

How Can i Create index on Non-Deterministic Column in table or View ?

Answer :

I can’t Create Index on Non-Deterministic Column in table or View because this is the limitation of the index .

Question 2 :

What is the Deterministic and Non-Deterministic Function in SQL Server ?

Answer :

These built-in functions from categories of built-in functions other than aggregate and string functions are always deterministic:

ABS DATEDIFF PARSENAME
ACOS DAY POWER
ASIN DEGREES RADIANS
ATAN EXP ROUND
ATN2 FLOOR SIGN
CEILING ISNULL SIN
COALESCE ISNUMERIC SQUARE
COS LOG SQRT
COT LOG10 TAN
DATALENGTH MONTH YEAR
DATEADD NULLIF

These built-in functions from other categories are always nondeterministic:

@@ERROR FORMATMESSAGE NEWID
@@IDENTITY GETANSINULL PATINDEX
@@ROWCOUNT GETDATE PERMISSIONS
@@TRANCOUNT GetUTCDate SESSION_USER
APP_NAME HOST_ID STATS_DATE
CHARINDEX HOST_NAME SYSTEM_USER
CURRENT_TIMESTAMP IDENT_INCR TEXTPTR
CURRENT_USER IDENT_SEED TEXTVALID
DATENAME IDENTITY USER_NAME

to know more information about this Check this link Check this Link

http://msdn.microsoft.com/en-us/library/aa214775(v=sql.80).aspx

Question 3 :

How can i check the Column is deterministic or deterministic ?

Answer :

you can check this by retrieving the Column properties with COLUMNPROPERTY .

LIKE :

Select COLUMNPROPERTY (OBJECT_ID( ‘Table_Name‘ ), ‘Column_Name‘, ‘IsDeterministic‘);

the result return from this Select Statement one of them :

1 = TRUE
0 = FALSE
NULL = Input is not valid. Not a computed column or view column.

Note : You can Change the value of  ‘IsDeterministic‘ to another value like (‘IsIndexable‘,’IsComputed‘)

to know more about this values Check this link

http://www.mssqltips.com/sqlservertip/1298/retrieving-sql-server-column-properties-with-columnproperty/

Example :

—Create Table name Computed_Col

CREATE TABLE [dbo].[Computed_Cal](
[ID] [int] NULL,
[Sallary] [bigint] NULL,
[Bouns] [bigint] NULL,
[Date] [datetime] NULL,
[Total] AS ([Sallary]+[Bouns]),
[ADDDATE] AS (dateadd(day,(20),[Date])),
[returndate] AS (getdate()),
[Calc_Year] AS (DATENAME(YEAR,[DATE]))
) ON [PRIMARY]

—-Insert Data

SET DATEFORMAT DMY
INSERT INTO Computed_Cal (ID,SALLARY,BOUNS,[DATE])
VALUES (1,2500,500,’16-05-2013′)

Computed_Cal

now i created 4 Computed Column (Total,ADDDATE,returndate,Calc_Year)

if you select * from this table you will find the Column Working good but the Question what will happened when i try to Create index on this Columns let’s see i will Create 2 index one in Column name (ADDDATE) and one in (returndate)

—-Create Index on Column name (ADD_DATE) :

CREATE NONCLUSTERED INDEX ind2_ADD_DATE
ON Computed_Cal ([ADD_DATE]);
GO

the index created Successfully because this column not NonDeterministic Column Meaning i don’t use NonDeterministic Function in the Computed Formula

now i will Check the Column property for column name ADDDATE to know IsDeterministic and IsIndexable if the result is (( 1 )) its will Accept the index and it not have any function form NonDeterministic Function the If it (( 0 )) it will not Accept like we will see in another Creating index

—CHECK THE COLUMNPROPERTY
SELECT COLUMNPROPERTY (OBJECT_ID(‘Computed_Cal’), ‘ADDDATE’, ‘IsDeterministic’);
SELECT COLUMNPROPERTY (OBJECT_ID(‘Computed_Cal’), ‘ADDDATE’, ‘IsIndexable’);

Check Columnproperty

—-Create Index on Column name (returndate) :

in this Column if you check the formula you will see i select to getdate() Function to retrieve the time of the insert data  in this Function is one form the NonDeterministic Function .

At the First i will Check the COLUMNPROPERTY for this column (returndate):

—CHECK THE COLUMNPROPERTY
SELECT COLUMNPROPERTY (OBJECT_ID(‘Computed_Cal’), ‘returndate’, ‘IsDeterministic’);
SELECT COLUMNPROPERTY (OBJECT_ID(‘Computed_Cal’), ‘returndate’, ‘IsIndexable’);

Result is 0 so it meaning this Column have NonDeterministic Function and it will not Accept index .

NonDeterministic

Now i f  i try to Crate This Index on this Column :

— index 2

CREATE NONCLUSTERED INDEX ind3_return_date
ON Computed_Cal (returndate);
GO

SQl Serve will give me Error:

Msg 2729, Level 16, State 1, Line 3
Column ‘returndate’ in table ‘Computed_Cal’ cannot be used in an index or statistics or as a partition key because it is non-deterministic.

Non-Determimistic2

In the Finally we must be take care when we build the table Structure 

Eng.Mostafa Elmasry

Database Administrator

 
Leave a comment

Posted by on May 17, 2013 in Index

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s