SharePoint

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. CREATE PROC dbo . Sample @Val INT AS BEGIN IF @Val = 1 RETURN 1 ELSE RETURN 0 END This 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. CREATE PROC dbo . Sample @Val INT AS BEGIN IF @Val = 1 SELECT 1 ELSE SELECT 0 END Assume that you have batch files where you are calling ma

Dynamic Management View sys.dm_db_index_usage_stats shows 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. SELECT OBJECT_NAME ( object_id ) Table_Name , * FROM sys.dm_db_index_usage_stats WHERE DB_NAME ( database_id ) = ' MyDB' AND OBJECT_NAME ( object_id ) = 'Order' -- Commenting this line displays result for all tables Column 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