When you create a computed column in SQL Server, the expression you use for the column will either be deterministic or nondeterministic. This can have implications, such as whether or not you can use it in an index or flag it as “persisted”.

A deterministic column is one that will return the same value for a specific set of input values and given the same state of the database. A nondeterministic column can return a different value even when given the same input even if the database state remains the same. For example, a function that returns the current date is nondeterministic, because it will return a different value each day.

You can use the COLUMNPROPERTY() function with the IsDeterministic argument to find out whether or not a computed column is deterministic.

Example

Here’s an example to demonstrate.

SELECT 
  COLUMNPROPERTY(
    OBJECT_ID('dbo.Products'), 
    'TotalValue', 
    'IsDeterministic') 
    AS IsDeterministic;

Result:

+-------------------+
| IsDeterministic   |
|-------------------|
| 1                 |
+-------------------+

In this case the TotalValue column from the dbo.Products table is deterministic. If it wasn’t, the result would be 0.

The IsDeterministic property only applies to computed columns and view columns.