all the type of indexes in MS-SQL Server

Question

Post reply

all the type of indexes in MS-SQL Server

gago.99

SSC Enthusiast

Points: 179
More actions
July 14, 2010 at 4:32 pm

#223390

hi all how are you
i have questions about the index i need to know whats all the type
of index in MS SQL Server 2005 2008 all the type of indexes at the sql server database
when i search i found some of them and i collect it
types of indexes in a MS-SQL Server database. These are: Clustered Index, Dense Index, Sparse Index, Nonclustere Index, Covering Index, Unique Index, Binary Trees Index, Composite Index, Spatial Index, ISAM (Indexed Sequential Access Method) Index and Hash Index???
Is that all the type of index in sql server?? if not tell me whats right?
Please answer me

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

You are mixing index structures with index types and also mixing that with index components.

I think this article will help to distinguish the differences and help to understand the different principles.

http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

gago.99 (7/14/2010)
Dense Index, Sparse Index,

Not SQL indexes

Covering Index, Unique Index,

These aren't index types, they're just properties of an index and covering isn't even that. One index can be covering for one query and not covering for another

Binary Trees Index,

Not a SQL index type

Composite Index

Not an index type, just a name given to an index with more than one column.

ISAM (Indexed Sequential Access Method) Index and Hash Index???

Not SQL indexes. ISAM sounds like MySQL, maybe.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 3

There's also XML indexes (http://msdn.microsoft.com/en-us/library/ms345121(SQL.90).aspx) in 2005 onwards.

I wouldn't call filtered indexes a separate type of index. Oracle also has the concept of a bitmap index, but SQL Server doesn't.

gago.99 SSC Enthusiast Points: 179 More actions · Answer 4

thanks a lot for your comment

can you tell me about all the type of an index that use or not use

in SQL SERVER ??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

CirquedeSQLeil (7/14/2010)
I think this article will help to distinguish the differences and help to understand the different principles.
http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

Also: http://www.sqlservercentral.com/articles/Indexing/68439/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gago.99 SSC Enthusiast Points: 179 More actions · Answer 6

GilaMonster (7/15/2010)
CirquedeSQLeil (7/14/2010)
I think this article will help to distinguish the differences and help to understand the different principles.
http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/
Also: http://www.sqlservercentral.com/articles/Indexing/68439/

thanks a lot

Mr . GilaMonster

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

FYI, Gail is a Gal.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

george sibbald SSC Guru Points: 104210 More actions · Answer 8

ISAM is a type of File that can be queried, Used a lot on mainframes in the good old days.

Still might be for all I know.

---------------------------------------------------------------------

gago.99 SSC Enthusiast Points: 179 More actions · Answer 9

thanks for everyones helps me spicily GilaMonster really your articles help me so much:-D

but i still confused coz i read a lot of articles of some authors focus on two type then i collect that

[:(]

last question

Is this all the type of indexes on the SQL SERVER?? or i forget something???

1- clustered index

2-Nonclustered Index

3- unique index

4- spatial index

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

You've forgotten two. Hit google and search for index SQL Server, you should be able to find the last two.

Also, unique index is not as type of index. It's just a property of the index. You can define a clustered or nonclustered index as unique or not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 11

Jim mentioned XML indexes earlier.

My contribution is: full-text indexes.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

gago.99 SSC Enthusiast Points: 179 More actions · Answer 12

thanks a lot for all replays thats really helps me

gago.99 SSC Enthusiast Points: 179 More actions · Answer 13

Now all type of indexes

1-Clustered

2-Nonclustered

3-Spatial

4-XML

5-Full Text Index

is that true

and as Gila said the unique just property of an index