Clustered Index

Hi everybody.
I've have a few questions about Clustered Index.
Somebody can help me?
1.- How many Clustered Index can i have a table?
2.- How many fields can i include in a Clustered Table?
3.- Can exists a clustered and unclustered index in a table?
Thanks for your advice.
Pablo Salazar.1.One - clustered indexes physically order the data, so more than one would
be impossible.
2.16
3.Yes
All this and more is available in books online under "specifications-SQL
Server objects"
HTH,
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
news:ugLICulIEHA.3276@.TK2MSFTNGP09.phx.gbl...
> Hi everybody.
> I've have a few questions about Clustered Index.
> Somebody can help me?
> 1.- How many Clustered Index can i have a table?
> 2.- How many fields can i include in a Clustered Table?
> 3.- Can exists a clustered and unclustered index in a table?
> Thanks for your advice.
> Pablo Salazar.
>
>|||Pablo
You can only have 1 clustered index on any given table. seeing as how it is
Clustered and ordered only by that column. You cannot order it another phy
sical way also.
You can have up to 16 columns specified in the index.
and Yes you can have multiple clustered indexes and only 1 clustered.
You normally try and make the Clustered index out of the most unique column
in the database. Usually a Primary Key field. Then you can build the noncl
ustered indexes.
Jeff|||If you use the Enterprise Manager tool to create yoouable, and add the
PK...that PK field is going to be your clustered index, even if it is not
what you wanted.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:OeNp9xlIEHA.3248@.TK2MSFTNGP12.phx.gbl...
> 1.One - clustered indexes physically order the data, so more than one
would
> be impossible.
> 2.16
> 3.Yes
> All this and more is available in books online under "specifications-SQL
> Server objects"
> HTH,
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> "Pablo Salazar" <pabloesch@.yahoo.com> wrote in message
> news:ugLICulIEHA.3276@.TK2MSFTNGP09.phx.gbl...
>|||Hi Jeff
I'm sure you didn't mean to say 'multiple clustered indexes'.
However, I'm not sure you didn't mean the last sentence. This is definitely
not a universal truth. A clustered index can retrieve duplicate data much
more quickly than a nonclustered, for example if you want everyone in the
same zip code. WIth a clustered index on zipcode, all the relevant rows will
be together. A nonclustered works well for unique data where you're
retrieving a single row, so why not use a nc index there, and save your
clustered for where it will do more good and a nc won't help?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:3855777C-E84F-4FB7-AC62-95F1C7C22A9C@.microsoft.com...
> Pablo
> You can only have 1 clustered index on any given table. seeing as how it
is Clustered and ordered only by that column. You cannot order it another
physical way also.
> You can have up to 16 columns specified in the index.
> and Yes you can have multiple clustered indexes and only 1 clustered.
> You normally try and make the Clustered index out of the most unique
column in the database. Usually a Primary Key field. Then you can build
the nonclustered indexes.
> Jeff|||Thank you Kalen.
I did not mean to mislead him. My fingers were faster than my head. Of cou
rse I did not mean that.
Thanks
Jeff|||We develop K-12 school admin packages and Municipal accounting packages...
Most of our tables have SCHOOL YEAR (or FISCAL YEAR) and BUILDING as the fir
st two columns.
We then CLUSTER our PRIMARY INDEX with these first two columns.
For example, our STUDENT CLASS table has a primary clustered index of:
YEAR, BUILDING, COURSE, SECTION, MEETING TIME, STUDENT ID
then our MEETING TIME table has a primary clustered index of:
YEAR, BUILDING, COURSE, SECTION, MEETING TIME
The JOINS are clear - the data is obvious.
If the SQL DB engine is going to page several rows with one I/O, they are re
lated (same year, same building).
Seems to be working very well for us.|||Hi Steve
I didn't mean to imply that clustering the primary key (or other unique
data) was always a bad idea. I just told Jeff that putting the clustered
index on a unique key wasn't a universally accepted rule of thumb. There is
no 'one size fits all' to determining the best columns for the clustered
index.
YMMV
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:5E8B6B28-83F6-47FD-94F4-85ADF2CC4293@.microsoft.com...
> We develop K-12 school admin packages and Municipal accounting packages...
> Most of our tables have SCHOOL YEAR (or FISCAL YEAR) and BUILDING as the
first two columns.
> We then CLUSTER our PRIMARY INDEX with these first two columns.
> For example, our STUDENT CLASS table has a primary clustered index of:
> YEAR, BUILDING, COURSE, SECTION, MEETING TIME, STUDENT ID
> then our MEETING TIME table has a primary clustered index of:
> YEAR, BUILDING, COURSE, SECTION, MEETING TIME
> The JOINS are clear - the data is obvious.
> If the SQL DB engine is going to page several rows with one I/O, they are
related (same year, same building).
> Seems to be working very well for us.|||Kalen
I must agree - it is so much about the data. In the example I described, ac
tually, the keys, although unique, are barely unique. We typically have 100
,000+ rows with the same YEAR+BLDG.
I recall situations where "reversing" the ASN # of a record created a better
"key" value for binary tree searching.
When we migrated from VAX/VMS ISAM files to SQL SERVER three years ago, your
book "Inside SQL Server 2000" helped us understand what was under the hood
of this DB engine (and it was very familiar).
The reason I posted my reply was to promote that "multi-column" keys are ok
in the world (although I still get resisitance from ACCESS programmers who w
orship "identity" columns for primary keys).
Steve|||> The reason I posted my reply was to promote that "multi-column" keys are
ok in the world (although I still get resisitance from ACCESS programmers
who worship "identity" columns for primary keys).
Some of us have crossed over :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Steve Z" <szlamany@.antarescomputing.com> wrote in message
news:A83AFDE9-1246-4B65-9E0C-7F467CCEBA37@.microsoft.com...
> Kalen
> I must agree - it is so much about the data. In the example I described,
actually, the keys, although unique, are barely unique. We typically have
100,000+ rows with the same YEAR+BLDG.
> I recall situations where "reversing" the ASN # of a record created a
better "key" value for binary tree searching.
> When we migrated from VAX/VMS ISAM files to SQL SERVER three years ago,
your book "Inside SQL Server 2000" helped us understand what was under the
hood of this DB engine (and it was very familiar).
> The reason I posted my reply was to promote that "multi-column" keys are
ok in the world (although I still get resisitance from ACCESS programmers
who worship "identity" columns for primary keys).
> Steve