Non deterministic data type in SQL Server

Question

I was creating a table in my SQL Server database when a specific attribute Deterministic of the database columns caught my attention. Please see the screenshot below:

I am already aware of Deterministic and Non-Deterministic SQL Server functions but I want to know that does it apply even to data data types in SQL Server in any way?

The reason why I'm asking is that I literally scanned through all the data types available in SQl Server v2008 and v2012 but the value of Deterministic field showed Yes for all of them. It didn't show No for any single data type.

So my question is that is it a proper attribute of any data type in SQL Server which still affects the way values are stored in the column or it is just a legacy from the past, may be from SQL Server 2000 or SQL Server 2005 where there used to be some data types which were non-deterministic in nature. Any information will be very useful to understand this characteristic of data types in SQL Server. Do we have any data type in SQL Server as of today which is non-deterministic in nature?

Since I didn't see No for any of the data types I got more got confused. I also googled a lot but every search takes me to Deterministic and Non-Deterministic SQL Server functions like the one below and nobody talks about Non-deterministic characteristic relating to SQL Server data types.

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

Answer 1

If you read this MSDN documentation carefully, you'll find:

IsDeterministic - Column is deterministic. This property applies only to computed columns and view columns.

It applies to columns (computed, view) derived from another columns with non-deterministic functions involved.

Examples:

CREATE TABLE Deterministic
(
    ID int,
    Calculated AS SYSDATETIME()
)

SELECT COLUMNPROPERTY(OBJECT_ID('Deterministic'), 'Calculated', 'IsDeterministic') IsDeterministic
--Returns 0

If you create view on this table as follows and execute 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 receive non deterministic columns

ColumnName   IsDeterministic
----------   ---------------
Calculated   0
Tomorrow     0