Wednesday, 18 November 2009

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 SyntaxCREATE INDEX Index Name ON Table Name (Columns..) Filter Criteria ExampleCREATE INDEX IX_RegistrationDate ON Employee (RegistrationDate) WHERE RegistrationDate IS NOT NULLAdvantages 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"

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

Wednesday, 4 November 2009

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

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.