SQL Server 2005 Computed Column Is Persisted

"Persisted" means "stored physically" in this context

It means that the computed value is computed once on insert (and on updates) and stored on disc, so it does not have to be computed again on every select.

Persisted also causes a performance penalty on insert and updates, since the column must be computed, but will increase the performance on subsequent select queries.

So, it depends on your usage pattern, which approach to follow: if you update infrequently, but query a lot, you should set persisted = true.

If you update frequently, or if you do not care about retrieval performance, you should consider setting persisted = false

If column is not persisted then we can't create the indexes on it.

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

Alter the Computed Column

ALTER COMPUTED COLUMN is not allowed if the compatibility level is 65 or lower. For more checking the compatibility level use sp_dbcmptlevel (Transact-SQL).

so you would need to Drop the computed column and re-create it.

ALTER TABLE [MyTable] DROP COLUMN MyColumn

GO

ALTER TABLE [MyTable] ADD MyColumn AS (A+B+C) PERSISTED 

Performance Tuning and Query Analysis in MS SQL Server 08

Hi


This a good video for performance tunig and query analysis

How to check given year is leap year or not

There is a function which gives wheather given year is leapyear or not



CREATE FUNCTION dbo.IsLeapYear (@year INT)
RETURNS INT
AS
BEGIN
DECLARE @value INT
DECLARE @datetime DATETIME

SET @datetime = CONVERT(DATETIME,'02/01/'+CONVERT(VARCHAR,@year),101)

IF (DATEPART(DD,DATEADD(DD,-1,DATEADD(MM,1,CAST(CAST(YEAR(@datetime) AS VARCHAR)+'-'+CAST(MONTH(@datetime) AS VARCHAR)+'-01' AS DATETIME)))) = 29 )
BEGIN SET @value = 1 END ELSE BEGIN SET @value = 0 END 

RETURN @value
END


we can use this function to check the year if the given year is leap year then It will return 1 Otherwise it will return 0.