Baskar B.V

Automated Index Defrag Script

Baskar B.V — Thu, 14 May 2009 19:36:43 GMT

Check this link for a great post on index defrag script

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

Drop Database - Tip

Baskar B.V — Tue, 12 May 2009 21:15:12 GMT

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.

Converting short month name strings to full month name

Baskar B.V — Thu, 26 Feb 2009 17:06:16 GMT

The below query would help if want to convert short month string (e.g) Jan, Feb to full month string (e.g) January, February...

Assuming mth_shrt in table1 has value like Jan, Feb, Mar and after the execution of the below query the result would be

Result:

January

February

Hope this helps. Post your views on this.

select datename(month,convert(varchar(20),'01/'+ mth_shrt_nm + '/1900',121)) from table1

Column Pivot using COALESCE

Baskar B.V — Thu, 03 Jul 2008 19:32:54 GMT

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.

forced index using WITH INDEX

Baskar B.V — Thu, 26 Jun 2008 18:59:43 GMT

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.