Conditionally add a column in the table

Sometimes we need to add a column in an already existing table but we need to make sure that this column should not be present already. This conditional column creation requires a way to check column existence in the given table. SQL Server 2005 INFORMATION_SCHEMA.COLUMNS view is an standard way to do this. Following script gives you the ability to conditionally add a column in the table, if that column is not already present.

Use tempdb
Go

Create table testTable (id int, city varchar(50))
Go

—Check for column existence
If not exists
(
Select * from INFORMATION_SCHEMA.COLUMNS
Where
TABLE_CATALOG=‘tempdb’
AND TABLE_SCHEMA=‘dbo’
AND TABLE_NAME=‘testTable’
AND COLUMN_NAME=‘id’
)
Begin
–Add new column
ALTER Table dbo.testTable Add id int Null
Print ‘Column added successfuly’
End
Else
Begin
–Column already exists. leave the table as it is.
Print ‘Column already exists.’
End
Go

Drop table testTable
Go

SQL Server Performance Dashboard Reports

Out of many good things I love following two great features in SQL Server 2005

1. SQL Server Dynamic Management Views or DMVs
2. SQL Server Reporting Services or SSRS

SQL Server 2005 exposes too much information about its internal working for a DBA to better tune the server but this information is hidden in different dynamic management views, counters and other SQL Server 2005 components. If you really want to master SQL Server 2005, you must have a very good knowledge of dynamic management views or DMVs.
Plus, if you want to master Reporting Services 2005 i.e. SSRS with techniques like:

1. Charting
2. Linked Reports
3. Creating Reports Portal
4. Passing Parameters between reports etc.

then look no further and download SQL Server Performance Dashboard Reports from here.
These reports not just exposes SQL Server 2005 internal statistics in an excellent graphical manner, they also provide queries to find SQL Server statistics by DMVs. In addition with these queries, you will also learn how Reporting Services works. You will learn how you can implement graphs, linked reports etc. So don’t waste more time:):) Start downloading SQL Server Performance Dashboard Reports.

Decide new indexes easily by SQL Server 2005 DMVs

Deciding a proper index to optimize a poorly running query is more art than science. It requires statistics of object usages, their impacts on already running queries etc. SQL Server dynamic management views have a set of DMV which provide excellent and detailed information for object usage. Following query gives you the clear picture of what will happen if you apply the suggested index.

– Potentially Useful Indexes
select d.*
, s.avg_total_user_cost , s.avg_user_impact, s.last_user_seek
,s.unique_compiles
from sys.dm_db_missing_index_group_stats s ,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle
order by s.avg_user_impact desc
go

The above query lists the column where an index can be beneficial and its impact on the user. Please note that these statistics will be reset once SQL Server will be restarted. Indexes suggested by the above query will greatly reduce your efforts to decide the best indexes.
But, again as I said above, these are just suggestions actual performance improvements can only be decided by you because no one else knows your requirements better than you:)