Deterministic Functions in SQL

Deterministic functions always return the same result whenever they are called with a specific set of input values provided the database state remains unchanged. Non-deterministic functions may return different results each time they are called with a specific set of input values even if the database state remains unchanged.

In another word, we can predict the output easily for deterministic function where as result from non-deterministic functions is not predictable.

There are few functions that are generally deterministic in nature, however behaves different under certain circumstances. So it is very important to know the behavior. The best example is RAND being deterministic  only when the SEED value is specified or CAST being non-deterministic when used with datetime, smalldatetime, or sql_variant.

Per BOL all of the aggregate and string built-in functions are deterministic and you cannot influence the deterministic behavior of the build in functions. Its important (and interesting) that the GETDATE() is classified as a non-deterministic function. A function being deterministic or non-deterministic decides the capability to have a primary key or a clustered index on computed columns using these functions. 

We could look at the IS_DETERMINISTIC column in INFORMATION_SCHEMA to see if a UDF is deterministic or not. Sometimes we could make it deterministic minor hints. 

CREATE FUNCTION ReturnNum(@num AS BIGINT)
RETURNS DECIMAL
AS
BEGIN
  DECLARE  @val INT
  SET @val=@num/10
  RETURN @val
END
GO
SELECT ROUTINE_NAME,IS_DETERMINISTIC FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'ReturnNum'

You will notice that that function is marked as non-deterministic. Lets try to create a table with persisted computed column using this function.

CREATE TABLE dbo.Test
(
  ID int NOT NULL,
  NewID AS dbo.ReturnNum(ID) PERSISTED
)

It will throw an error because the function is not deterministic. 

Msg 4936, Level 16, State 1, Line 1
Computed column 'NewID' in table 'Test' cannot be persisted because the column is non-deterministic.

 You can fix this error easily by using SCHEMABINDING option while creating the function. 

CREATE FUNCTION ReturnNum(@num AS BIGINT)
RETURNS DECIMAL
WITH SCHEMABINDING
AS
BEGIN
  DECLARE  @val INT
  SET @val=@num/10
  RETURN @val
END
GO
CREATE TABLE dbo.Test
(
  ID int NOT NULL,
  NewID AS dbo.ReturnNum(ID) PERSISTED
)
GO
SELECT ROUTINE_NAME,IS_DETERMINISTIC FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'ReturnNum'

Here is the list of deterministic function(as listed in BOL)  

Nondeterministic functions

Few exception

Reference:
http://technet.microsoft.com/en-us/library/ms178091.aspx