Filtered Index is a new feature in SQL SERVER 2008. Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
SQL SERVER – 2008 – Introduction to Online Indexing Operation
When index is created or recreated it usually decreases performance of database. Either SQL takes long time for response or it does not response at all as transactions are blocked. When new table or database goes live it is not possible to find out exactly how many indexes are needed.…
Read MoreSQL SERVER – Effect of Order of Join In Query
Let us try to understand this subject with example. We will use Adventurworks database for this purpose. Table which we will be using are HumanResources.Employee (290 rows), HumanResources.EmployeeDepartmentHistory (296 rows) and HumanResources.Department (16 rows). We will be running following two queries and observe the output. In the resultset the order…
Read MoreSQL SERVER – Find Current Identity of Table
Many times we need to know what is the current identity of the column. I have found one of my developer using aggregated function MAX() to find the current identity. USE AdventureWorks GO SELECTÂ MAX(AddressID) FROM Person.Address GO However, I prefer following DBCC command to figure out current identity. USE AdventureWorks…
Read MoreSQL SERVER – Order Of Column In Index
I just found one of my Jr. DBA to create many indexes with lots of column in it. After talking with him I found out that he really does not understand how really Index works. He was under impression that if he has more columns in one index, that index…
Read MoreSQL SERVER – Optimization Rules of Thumb – Best Practices
There are few rules for optimizing slow running query. Let us look at them one by one see how it can help. Rule # 1 : Always look at query plan first. I always start looking at query plan. There is always something which catches eyes. I pay special attention…
Read MoreSQL SERVER – Introduction to Heap Structure – What is Heap?
Sometime simple questions are very interesting. A day ago, jr. developer asked me question : What is Heap? In SQL Server 2005 data is stored within tables. Data within a table is grouped together into allocation unites based on their column data types, what it means is one kind of…
Read More