This might be long, but please bear with me. It's quite important to understand indexes because not knowing will only lead to problems with everybody, developers, end users, etc.
Let me start off with a simple table. A person table. All this table does is store name, and a few other tidbits of information:
Code:
CREATE TABLE [dbo].[person]
(
id_person INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
[sys] BIT NOT NULL DEFAULT 0,
date_added DATETIME NOT NULL DEFAULT GETDATE(),
date_updated DATETIME,
inactive BIT NOT NULL DEFAULT 0,
name_prefix NVARCHAR(10),
name_last NVARCHAR(50),
name_first NVARCHAR(50),
name_middle NVARCHAR(50),
name_suffix NVARCHAR(10),
)
CREATE NONCLUSTERED INDEX NameLast_NameFirst_NameSuffix ON [dbo].person
(name_last, name_first, name_suffix) INCLUDE(date_updated);
Understand, that this table we just created here has 2 indexes.
The primary key column id_person which is the clustered index (by the keyword there), and then another index that contains last name, first name, and suffix, and includes date_updated.
A table can only have 1 clustered index. A clustered index means this : Sort the underlying table by this value, and store all the information for the row in the clustered index. Think of it as the main data file for the table. If we insert into an identity column, the field is auto generated, the first insert into this table will have an id_person = 1, the next insert id_person = 2... Since that is a clustered index, the table is sorted that way by only that column. So if you were to search for id_person = 2, the database can easily find the information and all the information from the columns are available quickly.
A non clustered index is basically any other index on the table, and it be used to find information in the non clustered index, or to assist the database to find the row in the CLUSTERED index. Non clustered are also sorted by their columns. So my non clustered index will sort by last name, first name, and suffix.
Now. When we query:
Code:
SELECT name_last, name_first, name_suffix FROM person where name_last = 'BOB' and name_first = 'JOE'
It can get all that information from the non clustered index. Which is fast, because all the information you need is sorted in the non clustered index, and all the data/columns you are selecting are in the non clustered index.
If we search by:
Code:
SELECT name_last, name_first, name_suffix, [b]date_updated[/b] FROM person where name_last = 'BOB' and name_first = 'JOE'
That is also fast, because all the information is in that index. The include portion of the index means include the value into the index but do not search for the row by that column (so the database doesn't have to use CPU to find the row as date_updated is not considered when searching, but is there in case you need it)
If we search by:
Code:
SELECT name_last, name_first, name_suffix, [b]date_added[/b] FROM person where name_last = 'BOB' and name_first = 'JOE'
This is going to be slower.
Why? Because date_added is not part of the index. The Database can use the non clustered index to find the row you want, but the columns you need are not part of the index so it does a thing called a "key lookup." Every non clustered index MUST have a key to the clustered index... So the index sort of looks like: (but the primary key is hidden when using non clustered index, but its no different than doing the below)
Code:
CREATE NONCLUSTERED INDEX NameLast_NameFirst_NameSuffix ON [dbo].person
(name_last, name_first, name_suffix) INCLUDE(date_updated, [b]id_person[/b]);
Now that it has the CLUSTERED KEY in the index, and when pulling information that is not in the non clustered index, it uses the id_person to find the data/columns you want from the CLUSTERED index. That means for every query will essentially have 2 searches internally, one on the non clustered index to get the primary key, then another search on the clustered index (main table data) because it contains all the data.
That is the quick rundown on clustered/nonclustered.
Now the question might be... What if you never want to search on id_person, and only search on last name, first name, and suffix? Why not have that as your clustered index?
The answer to that is simple. Because when you INSERT things into the database, it has to find a spot to place it, since its sorted. All the data is separated by 8k pages. If you have columns (like above) at 361 bytes (consider unicode so all the NVARCHAR's are twice the size) that means you can have exactly 22 rows of data in each page. So id_person of 1 to 22 are in the first page, 23-44 in page 2... But lets say you insert something that would end up in page 1 (not that this is possible with an identity column but for sake of explination) insert an id_person of 10.5... The database has to create 2 new pages: And now page 1 is updated to point at these 2 new pages. The new pages contain exactly half of what was in page 1 and with the new row added on one of the two new pages, and page 1 is essentially "blank", and is only used to point to these pages.
That is very ineffecient. My guess something like this is happening with your database, but is sometimes necessary.
That is why I designed the person table to have an identity CLUSTERED index so every new person is always added to the end of the database and prevents those page splits on the main clustered index. The non clustered indexes are designed to be small, so they can hold more rows per page, so page splits happen less frequently, and that scanning will be faster.
Now back to your problem:
If nothing is searching by those indexes, they can be removed. That reduces the work necessary when inserting new rows by preventing page splits in the indexes and all the unnecessary overhead.
But my guess is something is using those indexes. There has to be queries out there searching by FILENO. If there are, and the number of rows is fairly small per FILENO (like 10,000 or less), then I'd remove all those FILENO (first column) indexes and create just a single FILENO index with no additional columns, because things will still be fast when searching, but you don't have to worry about the other columns (within that index) causing page splits.
I'd suggest you figure out what uses this table and how they are searching for data. This will help determine which indexes can be removed. I also suggest putting on a clustered index of some sort (one that minimizes page splits) if the table does not have one already. Because if you have no clustered index, every single time the database does a key lookup (pulling information about columns not in the non clustered index) it will scan the entire main data file (clustered index which is not sorted) to find your columns. (Actually this last sentence might not be true, SQL might store the page number in the index rather than the clustered key, not sure)
But without knowing more about the nature of the searchings I am not going to suggest removing any indexes.
