I currently have a site with a table that has Lat/Long float columns, and an index over those 2 columns plus another one I need to retrieve. I'm constantly querying this table to get the rows that fall within a radius from a certain point (I'm actually getting a square for speed), but I only need the fields that are already indexed, so ...
For example: Given columns A,B,C,D, IX_A is an index on 'A' IX_AB is a covering index on 'AB' IX_A can be safely removed, for it is redundant: IX_AB will be used in its place. I want to know if this generalizes: If I have: IX_AB IX_ABC IX_ABCD and so forth, Can the lesser indices still be safely removed? That is, does IX_ABC ma...
What methods are there for indentifying superfluous columns in covering indices: columns which are never searched against, and therefore may be extracted into Include's, or even removed completely without affecting the applicability of the index? ...
Suppose we have this index CREATE INDEX IX_test ON t1(c1) INCLUDE (c2) Does this mean that we will have c2 in both index page and the actual data page? The real question is - does updating c2 mean that SQL Server will have to update IX_test and the actual data row (clustered index)? ...
Suppose I need to update myTab from luTab as follows update myTab set LookupVale = (select LookupValue from luTab B where B.idLookup = myTab.idLookup) luTab consists of 2 columns (idLookup(unique), LookupValue) Which is preferable : a unique clustered index on idLookup, or one on idLookup and...
I recently encountered an index in a database I maintain that was of the form: CREATE INDEX [IX_Foo] ON [Foo] ( Id ASC ) INCLUDE ( SubId ) In this particular case, the performance problem that I was encountering (a slow SELECT filtering on both Id and SubId) could be fixed by simply moving the SubId column into the index proper rathe...
When will the below be necessary: create index i_t_a_b on t(a,b); create index i_t_b_a on t(b,a); ...
I've downloaded the employees database and executed some queries for benchmarking purposes. Then I noticed that one query didn't use a covering index, although there was a corresponding index that I created earlier. Only when I added a FORCE INDEX clause to the query, it used a covering index. I've uploaded two files, one is the executed...
Hi, I'm working on a database system and it's indexes, but I'm having a really hard time seing the clear difference between a covering index and a clustered index. I've googled my way around but hasn't got a clear cut answer on: What is the differences between the two types of indexes When do I use Covering index and when do I use Cl...
Hi, I’m having a question about the fine line between the gain of an index to a table there is growing steadily in size every month and the gain of queries with an index. The situation is, that I’ve two tables, Table1 and Table2. Each table grows slowly but regularly each month (with about 100 new rows for Table1 and a couple of rows f...
Hi, I am running some queries in my database and want to increase the performance and have created some indexes but I still think that the response time is to great so I want to see if I can create a better or another index to increase the speed. My schema for the table I think has the biggest bottleneck look like this: R_D( **id** i...
SQL Server 2005 includes "covering index" feature which allows us to select more than one non key column to be included to the existing non clustered index. For example, I have the following columns: EmployeeID, DepartmentID, DesignationID, BranchID Here are two scenarios: EmployeeID is a primary key with clustered index and the re...