Skip to main content


Showing posts from June, 2007

ISQL Vs SQLCMD - How they return value?!

ISQL is not supported in SQL Server 2005. SQLCMD is a replacement for ISQL utility. I don’t go in detail about SQLCMD. Instead I’m going to explain one big difference between these two utilities that require attention.SQLCMD does not support passing of stored procedure return value to the exit value. Let me explain this with examples.Consider a simple Stored Procedure.CREATEPROC dbo.Sample@Val INTASBEGIN IF @Val = 1 RETURN 1 ELSE RETURN 0ENDThis stored proc is self explanatory. When this SP is called from ISQL, the RETURN value is passed to EXIT command of ISQL. That means we can capture this return value and process accordingly.In case of SQLCMD, the RETURN value will not be passed to EXIT command. Because, SQLCMD expects SP to SELECT return value. That is, if we change SP as shown below, this works with SQLCMD.CREATEPROC dbo.Sample@Val INTASBEGINIF @Val = 1SELECT 1ELSESELECT 0ENDAssume that you have batch files where you are calling many SPs using ISQL. And you have to chang…

Index Usage Statistics

Dynamic Management Viewsys.dm_db_index_usage_statsshows counts of different types of index operations. This DMV can be used to know whether particular index is used frequently or not or which index is incurring maintenance overhead.Let us take an example.SELECTOBJECT_NAME(object_id) Table_Name,*FROMsys.dm_db_index_usage_stats
WHEREDB_NAME( database_id )=' MyDB'
ANDOBJECT_NAME(object_id)='Order' -- Commenting this line displays result for all tablesColumn index_id specifies type of index being used. 0 for heap, 1 for clustered index and >1 for non-clustered index.user_seeks, user_scans, user_lookups and user_updates columns specify no. of respective operations by user queries.last_user_seek, last_user_scan, last_user_lookup and last_user_update columns specify time of last respective operations done.Similarly, there are other 8 columns which are recorded against system queries.If any of the indexes are not used by queries, you may want to consider dropping the index an…