How to run a SQL batch multiple times

Today I came across one surprising use of GO statement in SQL Server which is solely used as batch terminator in SQL Server utilities such as SQL Server Management Studio etc. Though “GO” is not a Transact –SQL statement it can be used in multiple ways .One such example is to run a batch of statements as illustrated below

CREATE TABLE TestData ( Name VARCHAR(100), Age INT)
GO
Insert into TestData
SELECT 'John' ,28
GO 100

In the above example I have inserted 100 rows of test data by just specifying 100 next to the GO statement. All these days I have used the traditional while loop statement for doing this, now just specify the number next to GO statment the job is done. This feature is no where documented and supported only in SQL Server 2005 and greater versions

How to recover a Database in suspect mode

I was struck with a database which is on suspect mode and need to recover it without any recent backups. I searched across the net and found these steps which are quite useful

Step 1:
Clear the suspect mode of the database using sp_resetstatus DatabaseName. This will clear the suspect flag and make the database available online

Step 2:
Change the database status to Emergency using the following command. Emergency mode allows you to access the databases as normal but with no consistency guarantee. This option also allows us to export the table data so that we can minimize the damage.
ALTER DATABASE DatabaseName SET EMERGENCY;

Step 3:
Restrict database to single user by changing the access mode as mentioned below
ALTER DATABASE DatabaseName SET SINGLE_USER;

Step 4:
Run the CHECKDB command with “REPAIR_ALLOW_DATA_LOSS” option. This option should be tried as last option as it always behaves the way it is named. We are not sure of what data it removes.
DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

There are some best (simple) practices which prevents us from such failures. Below are some of them

• Backup your data frequently ( daily or once in two days)
• Have multiple backups. Move the backups to external drives or tapes frequently
• Validate that your backups are good by doing trial restores to alternate server
• Run CHECKDB regularly

Filter Index in SQL Server 2008

SQL Server 2008 introduces a new type of index called “Filtered Index” which is basically a covered indexed designed to retrieve a smaller set of qualified data from a table. This can be a very hand feature particularly in working with larger data tables.
A Filter index allows us to apply filter criteria on the index definition so that a particular sub set of rows in a table alone can be indexed. Filter indexes can be only created as non clustered index

Syntax
CREATE INDEX Index Name ON Table Name (Columns..) Filter Criteria

Example
CREATE INDEX IX_RegistrationDate ON Employee (RegistrationDate) WHERE RegistrationDate IS NOT NULL

Advantages of Filtered Index

• Improved Performance: The performance of the query is improved especially with larger tables as it has to scan through as lesser number of records
  
• Lesser Maintenance Cost: Since the size of the index is smaller compared to full table index the index maitntenance cost will be much lesser. Also index maintenance jobs like update statics could be faster.


• Lesser Storage: The amount of space required for index storage will also be very less since the size of the index is smaller compared to the full table index

Analysis

I have created a table patient with 1 lakh records of different Organisations and populated 70% data with OwnerOrganisation value 10 and selected the record with OwnerOrganisation value ="6"

Normal Index

CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID)

Filterer Index to exclude records of Organisation =10

CREATE INDEX IX_OwnerOrganisation ON Patient(OwnerOrganisationUID) WHERE OwnerOrganisationUID <>10




Conclusion

Index creation is always case to case basis as the need to create a filtered index should be carefully analysed based on the WHERE clause and the data distribution in the table. It is recommended to create filtered indexes if the data retrieved to be a smaller subset. Scenarios like columns with NULL data as major set and NOT NULL values of defined subsets could be a suitable candidate

How to select records with in Date Range

Selecting records within a given date range is one of the common requirement these days, but many people find it difficult. The most common mistake people make is always try to do the comparison as the same as the way we do for numbers as shown below

Wrong comparison
SELECT *
FROM Table
WHERE StartDate >= @P_StartDate
AND EndDate =< @P_EndDate

This wills not retrieve the qualified records as the comparison will not be against the range instead it will be against two dates and it will ignore any records fall within the range. The trick is to change the parameter to check the date in the reverse order as shown below.

Modified Query
SELECT *
FROM Table
WHERE EndDate > = @P_StartDate
AND StartDate =< @P_EndDate

Parameterized sorting in SQL Server

Applications that allow users to sort data by different columns of the table might need to go for dynamic stored procedures or will end up in adding multiple procedures based on the number of combinations. In SQL Server we can achieve this easily through parameterized sorting.
As shown in the below example we can have a parameter which says the column on which the sort criteria can be applied and using the case statement we achieve the results without going for a dynamic /multiple stored procedures.

DECLARE @SortOrder INT
SET @SortOrder =1
SELECT ForeName,
SurName,
PASID
FROM PAtient
WHERE Forename LIKE 'A%'
ORDER BY CASE WHEN @SortOrder = 1 THEN ForeName
WHEN @SortOrder = 2 THEN SurName
ELSE PASID
END

Generate Index scripts using included columns

This article is a continuation of the previous article http://samsudeenb.blogspot.com/2007/11/scripts-to-drop-and-recreate-indexes-in.html with small enhancement to generate the index scripts with included columns. The below function “fGetIncludedIndex is used to get the included columns for a given index.

CREATE FUNCTION fGetIncludedIndex (
@objname VARCHAR(50),
@indid INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
DECLARE @V_IncludedCol NVARCHAR(200)
select @V_IncludedCol = COALESCE(@V_IncludedCol + ',', '') +
( select name from sys.syscolumns where id =a.Object_id and colid =a.column_id)
from sys.index_columns a
where object_name(a.object_id) =@objname
and a.index_id =@indid
and a.Is_included_column =1
order by a.index_column_id
return @V_IncludedCol
END

After creating the function in the DB make a small change to the existing query( highlighted in bold) to generate the index scripts with included columns

SELECT 'CREATE ' +
CASE IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +
'NONCLUSTERED ' + ' INDEX '+
NAME + ' ' +' ON ' +
OBJECT_NAME (OBJECT_ID) +
'('+
DBO.fGetIndexCols (object_NAME(OBJECT_ID), index_id) + ')'
+Case when dbo.fGetIncludedIndex(object_NAME(OBJECT_ID), index_id) is null then '' else ' INCLUDE ('+dbo.fGetIncludedIndex(object_NAME(OBJECT_ID), index_id)+')' end +
' ON ['+
( SELECT GROUPNAME
FROM SYSFILEGROUPS
WHERE GROUPID = DATA_SPACE_ID
) + ']' IndexScript
FROM SYS.INDEXES
WHERE NAME IS NOT NULL
AND Is_Primary_Key =0
AND type_desc ='NONCLUSTERED'
AND OBJECT_ID > 97

How to take week or month wise report in SQL Server

I was breaking my head whole day for writing a stored procedure which produces week wise report for plotting a revenue trend. I came across this solution which is fairly a simple one. Just thought of sharing this

The below code is written using the SQL Server built in functions DATEDIFF & DATEADD to produce the report

Select DATEADD(WK, datediff(WK, 0, CollectionDate),0) as week,
SUM(Amount)
from Revenue
group by dateadd(WK, datediff(WK 0, CollectionDate),0)
order by 1 asc

The logic here is to find out the corresponding week from Default date (01-01-1900) and adding the default date again to the result will get the corresponding week in date format. The same can be used for generating month wise report as well by just replacing "wk" with "MM"

Select DATEADD(MM, datediff(MM, 0, CollectionDate),0) as week,
SUM(Amount)
from Revenue
group by dateadd(MM, datediff(MM 0, CollectionDate),0)
order by 1 asc

Note: adding “0” will be automatically converted to default date “01-01-1900”