Geek Answers Handbook

Non-deterministic data type in SQL Server

I was creating a table in my SQL Server database when I was attracted by the specific Deterministic attribute of the database columns. See the screenshot below:

enter image description here

I already know about the deterministic and non-deterministic functions of SQL Server, but I want to know that it applies even to data types in SQL Server in any way?

The reason I ask is because I literally looked at all the data types available in SQl Server v2008 and v2012, but the value of the Deterministic field showed Yes for all of them. It did not show No for any one data type.

So my question is, is it a proper attribute of any data type in SQL Server that still affects how the values ​​are stored in the column or is it just a legacy of the past, maybe from SQL Server 2000 or SQL Server 2005, where there used to be some types of data that were not deterministic in nature. Any information would be very helpful in understanding this characteristic of data types in SQL Server. Do we have any data type in SQL Server today that is not deterministic in nature?

Since I did not see No for any of the data types, I got more confused. I also searched Google a lot, but each search leads me to the deterministic and non-deterministic functions of SQL Server, as below, and no one talks about the Non-deterministic characteristic regarding SQL Server data types.

https://technet.microsoft.com/en-us/library/ms178091(v=sql.110).aspx

+5
source share
1 answer

If you carefully read this MSDN , you will find:

IsDeterministic - The column is deterministic. This property applies only to calculated columns and column views.

It applies to columns (computed, view) obtained from other columns with unindexed functions involved.

<strong> Examples:

 CREATE TABLE Deterministic ( ID int, Calculated AS SYSDATETIME() ) SELECT COLUMNPROPERTY(OBJECT_ID('Deterministic'), 'Calculated', 'IsDeterministic') IsDeterministic --Returns 0

If you create a view in this table as follows and run the following query

 CREATE VIEW vDeterministic AS SELECT ID, Calculated, DATEADD(D, 1, Calculated) Tomorrow FROM Deterministic GO SELECT 'Calculated' ColumnName, COLUMNPROPERTY(OBJECT_ID('vDeterministic'), 'Calculated', 'IsDeterministic') IsDeterministic UNION ALL SELECT 'Tomorrow', COLUMNPROPERTY(OBJECT_ID('vDeterministic'), 'Tomorrow', 'IsDeterministic')

You also get non-deterministic columns

 ColumnName IsDeterministic ---------- --------------- Calculated 0 Tomorrow 0
+5
source

More articles:


All Articles