Baskar B.V

Check this link for a great post on index defrag script

http://sqlfool.com/2009/03/automated-index-defrag-script/

After dropping a database you should backup "master" database because deleting database update master database. If not, then if you want to restore "master" at some point then it would have reference to the non-existing databases.

Column Pivot using COALESCE command:

I read this in an article today and thought worth sharing with you.

DECLARE @val VARCHAR(1000)

SELECT @val = COALESCE(@val,'') + col1 + ';' 
FROM table1

SELECT @val

The above command would concatinate the output of the column col1 in table1(say 100 rows) into a single column value. We can avoid using cursors.

When sql queries running very slower we can consider forcing an index using WITH INDEX command

Syntax: Select * from <tablename> with (index(<index name>))

This we found very useful in one of our project where we were faced performance issues. Our code is basically querying a table with date condition.

select @st_dt =  max(field1)   from table1 with (index (index_nm))  where field1 <= @start_dt

The date value is passed through sql variable. The query is running for ever even though it has the index on field1. We tried to run the query using WITH INDEX by mentioning the index name on the field. This query ran just like that in few minutes.

You can think of using forced index when you pass some values in where clause thru variables and only the indexed fields are used in the where clause. 

Let me know your thoughts on this.