SQL Index query

Question

Can some assist me with a tutorial on how to create and index base upon three fields, and then how to call that index in code.

table is call ColorTable

Items i want to index on are ColorTable.ID, ColorTable.ColorSerial, ColorTable.Part,

thanks in advanced

Jignesh Trivedi · Answer

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 (ColorSerialPart); 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.

Senthilkumar · Answer

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 If you want to know more about sql server indexing then you can refer the following. http://msdn.microsoft.com/en-us/library/ms188783.aspx