SQL Index query

hi,

consider following table.
CREATE TABLE [dbo].[ColorTable](
 [ID] [nchar](10) NOT NULL,
 [ColorSerial] [varchar](50) NULL,
 [Part] [varchar](50) NULL,
 CONSTRAINT [PK_ColorTable] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now create Index on ColorSerial and Part fileds,

CREATE INDEX IX_ColorTable_ColorSerial_Part
    ON dbo.ColorTable (ColorSerial,Part);

drop index.

Drop index IX_ColorTable_ColorSerial_Part on dbo.ColorTable

use with select statement.
select * from ColorTable with(index (IX_ColorTable_ColorSerial_Part))

hope this help.

There are several types of indexing mechanism in sql server.

You have asked to create index on three columns. 

If ColorTable.ID is primary column then you no need to create any index for that column. Because every primary key will create clustered index automatically. More over every table can have only one clustered index.

The clustered index what it will have is it will have the actual value in the node itself. When the search happens it will retrieve immediately.

For the other columns you need to create the non clustered index. When you create the index on those columns it will create the index table for this colorTable. It will store the index value and it will have the reference to the base table. When ever you search then it will do the Index Scan first and if it is found then it will do the mapping with the base table.

 CREATE INDEX IX_ColorTable_ColorSerial      ON ColorTable (ColorSerial);  GO  CREATE INDEX IX_ColorTable_Part      ON ColorTable (Part);  GO