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.

Reset Identity Column

If we want to check current identify value is. We can use this command to do so:


DBCC CHECKIDENT (‘tablename’, NORESEED)


If we wanted to check the next ID value of my orders table, I could use this command:


DBCC CHECKIDENT (orders, NORESEED)


To set the value of the next ID to be 100, I can use this command:


DBCC CHECKIDENT('Customer', RESEED, 99)

If we want to reset the identity column so we can use the previous command as follows:

DBCC CHECKIDENT('Customer', RESEED, 99)

SQL-Server Performance Tips

Hi,


Query optimization is the required thing in SQL-Server. If we use the following steps in our query then we can get the better performance in SQL-Server.

• Provide the primary key in all the tables
• Avoid using the Boolean operators >, <, >=, <=, is null, is not null
• Avoid using the Not in, !=, Like '%pattern', not exists
• Avoid using the Calculations on unindexed columns or (use union instead), Having (use a WHERE clause instead)
• Don't use function in where clause, instead of use that funtion in where clause.
• Enable aliases to prefix all columns
• use with no lock after the table name
• Return only that column which are required
• Those table which have less number of record use that table first in inner join
• Try to avoid sorting in the select Statement. If possible then do the sorting at the client level.

Find the dependency on an object

In SQL we can find the dependency of an object with the help of TSQL. I am writing the query below which returns the all the function, stored procedure and tables which are used in that object.



SELECT DISTINCT OBJECT_NAME(d.DEPID) DEPENDENT_ON_OBJECT, OBJECT_NAME (d.ID) OBJECTNAME,o.type_desc
FROM SYS.SYSDEPENDS D inner join sys.objects o on d.depid = o.OBJECT_ID
WHERE d.ID =  OBJECT_ID('<OBJECT NAME>')

Add a Hyperlink to a URL in SSRS

There is no direct option to create a hyperlink in SSRS, however you still can create links. This is how. To add a hyperlink

• In report design view, right-click the text box, image, or chart to which you want to add a link and then click Properties.
• In the Properties dialog box, click Action.
• Select Go to URL. An additional section appears in the dialog box for this option.
• In Select URL, type or select a URL or an expression that evaluates to a URL, or click the drop-down arrow and click the name of a field that contains a URL.
• Click OK.
• (Optional) The text is not automatically formatted as a link. For text, it is helpful to change the color and effect of the text to indicate that the text is a link. For example, change the color to blue and the effect to underline in the Font section in the Home tab of the Ribbon.
• To test the link, click Run to preview the report, and then click the report item that you set this link on.