what type of indexes exists in Sql Server ?which one is the fastest and why can we only have one of those per table ?
firoz.raj -3 Posting Pro in Training
darkagn 315 Veteran Poster Featured Poster
Check out this great article by Alexander Chigrik. His explanations of indexes and their function in MSSQL is great, and he gives some really good insight into the optimisation of your database.
sknake 1,622 Senior Poster Featured Poster
firoz -- "fastest" isn't always best. The only type of index limited to one per table is a clustered index. The table's data is physically stored in order of the clustered index. Now image this for example: You have an "Invoice" table with "CreateDate" column and have a clustered index against that column in descending order. Now your searches based on the CreateDate column will be fast, the inserts will be slow. When you insert a new record it will have a newer createdate and have to physically shove all of the records down a notch to write at the top portion of the file descriptor, giving you very slow inserts. Everything in computing is a cost-benefit tradeoff, indexes are no exception.
Why don't you explain what you're trying to accomplish or learn?
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.