Deterministic and Non Deterministic Function in SQL

Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Non deterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same. 

Deterministic functions - SUM, AVG, DAY, ISNUMERIC, ISNULL, CONVERT 
Non deterministic functions - GETDATE, RAND, @@ROWCOUNT. USER_NAME, IDENTITY


Below is the query by which we can find that our function or Stored Procedure is deterministic or not.


IF OBJECTPROPERTY (OBJECT_ID('Function or Stored Procedure Name'),'IsDeterministic') = 1
   PRINT 'Function is detrministic.'
ELSE IF OBJECTPROPERTY (OBJECT_ID ('Function or Stored Procedure Name') ,'IsDeterministic') = 0
   PRINT 'Function is NOT detrministic'
GO