Collation Changed after restore SQL7 DB into SQL2000 DB

Hi Good Day everybody.
Thanks a lot for Hari and I managed resolved the problem on restore SQL7 DB
into SQL2000 DB.
I am encountered the problem is the default collation was changed after I
restore the data from SQL7 into SQL2000. Meaning that when I resotred from
SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS" and not
"Latin1_General_CI_AS".
a) Default collation for SQL7 DB Server is:
"SQL_Latin1_General_CP1_CI_AS".
b) Default collation for SQL2000 DB Server is (this is required collation
for the new upgrade and use by application):
" Latin1_General_CI_AS" .
I am wondering whether have any impact to the database if I execute the
command below to change the collation.
ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
Please advise.
Polar Bear
=?Utf-8?B?UG9sYXIgQmVhcg==?= (PolarBear@.discussions.microsoft.com) writes:
> Hi Good Day everybody.
> Thanks a lot for Hari and I managed resolved the problem on restore SQL7
> DB into SQL2000 DB.
> I am encountered the problem is the default collation was changed after
> I restore the data from SQL7 into SQL2000. Meaning that when I resotred
> from SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS"
> and not "Latin1_General_CI_AS".
That is because the sortorder in the SQL 7 corresponds to SQL collations
in SQL 2000. There is nothing corresponding to the Windows collations in
SQL 7.

> I am wondering whether have any impact to the database if I execute the
> command below to change the collation.
> ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
The immediate impact of the change is little. New tables and columns
will use that collation, as will variables in stored procedures etc.
However, existing tables will not, but will retain the SQL collation.
Thus a query like:
SELECT ... WHERE col = @.value
could fail with a collation conflict.
Most likely you want to change the collation throughout the database.
In this case, you need to bulk out the data, build a new database
from scripts, and bulk data back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Collation Changed after restore SQL7 DB into SQL2000 DB

Hi Good Day everybody.
Thanks a lot for Hari and I managed resolved the problem on restore SQL7 DB
into SQL2000 DB.
I am encountered the problem is the default collation was changed after I
restore the data from SQL7 into SQL2000. Meaning that when I resotred from
SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS" and not
"Latin1_General_CI_AS".
a) Default collation for SQL7 DB Server is:
"SQL_Latin1_General_CP1_CI_AS".
b) Default collation for SQL2000 DB Server is (this is required collation
for the new upgrade and use by application):
" Latin1_General_CI_AS" .
I am wondering whether have any impact to the database if I execute the
command below to change the collation.
ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
Please advise.
Polar Bearexamnotes (PolarBear@.discussions.microsoft.com) writes:
> Hi Good Day everybody.
> Thanks a lot for Hari and I managed resolved the problem on restore SQL7
> DB into SQL2000 DB.
> I am encountered the problem is the default collation was changed after
> I restore the data from SQL7 into SQL2000. Meaning that when I resotred
> from SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS"
> and not "Latin1_General_CI_AS".
That is because the sortorder in the SQL 7 corresponds to SQL collations
in SQL 2000. There is nothing corresponding to the Windows collations in
SQL 7.

> I am wondering whether have any impact to the database if I execute the
> command below to change the collation.
> ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
The immediate impact of the change is little. New tables and columns
will use that collation, as will variables in stored procedures etc.
However, existing tables will not, but will retain the SQL collation.
Thus a query like:
SELECT ... WHERE col = @.value
could fail with a collation conflict.
Most likely you want to change the collation throughout the database.
In this case, you need to bulk out the data, build a new database
from scripts, and bulk data back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Collation Changed after restore SQL7 DB into SQL2000 DB

Hi Good Day everybody.
Thanks a lot for Hari and I managed resolved the problem on restore SQL7 DB
into SQL2000 DB.
I am encountered the problem is the default collation was changed after I
restore the data from SQL7 into SQL2000. Meaning that when I resotred from
SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS" and not
"Latin1_General_CI_AS".
a) Default collation for SQL7 DB Server is:
"SQL_Latin1_General_CP1_CI_AS".
b) Default collation for SQL2000 DB Server is (this is required collation
for the new upgrade and use by application):
" Latin1_General_CI_AS" .
I am wondering whether have any impact to the database if I execute the
command below to change the collation.
ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
Please advise.
Polar Bear=?Utf-8?B?UG9sYXIgQmVhcg==?= (PolarBear@.discussions.microsoft.com) writes:
> Hi Good Day everybody.
> Thanks a lot for Hari and I managed resolved the problem on restore SQL7
> DB into SQL2000 DB.
> I am encountered the problem is the default collation was changed after
> I restore the data from SQL7 into SQL2000. Meaning that when I resotred
> from SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS"
> and not "Latin1_General_CI_AS".
That is because the sortorder in the SQL 7 corresponds to SQL collations
in SQL 2000. There is nothing corresponding to the Windows collations in
SQL 7.
> I am wondering whether have any impact to the database if I execute the
> command below to change the collation.
> ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
The immediate impact of the change is little. New tables and columns
will use that collation, as will variables in stored procedures etc.
However, existing tables will not, but will retain the SQL collation.
Thus a query like:
SELECT ... WHERE col = @.value
could fail with a collation conflict.
Most likely you want to change the collation throughout the database.
In this case, you need to bulk out the data, build a new database
from scripts, and bulk data back.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.aspsqlsql

Cognos Powerplay and Analysis 2005

Hi everybody,

we have an AS2000 cube and we use Cognos Powerplay for reporting.
We are investigating about migrating to AS2005 and we are looking for information about compatibility between 2005 and Powerplay.

If anyone can help us.
Thank you.We are also using PowerPlay to deliver MSAS 2000 cubes. According to Cognos Support: "At this time, MS SAS 2005 is not supported with Cognos including the latest version Cognos 8." I have not attempted to install PTS9.exe and SQL Native Client on our production server to test the connections. I have verified it does not work with the older version.

Clustering of the Data

Hi Everybody !
I am working on Clustering of the data.I have database of the dictionary including Antonyms and Synonyms.
I have to detect the sense and throw a output for the relevant words [ you can say including the Antonyms].
please tell me how to cluster the data so that it can be more efficient and it can throw more relevant words.

For expamles : if i put the input " Hotel"
it can throw "public house, resort, roadhouse, rooming house, scratch crib, spa, tavern"

like more near to these words....

Please help me to find the good algorithm.

Regards,You must be using Wordnet for extracting the semantic relation among words. If yes, then, Wordnet, would directly give you the sense and related terms.
You may also use the "Cosine Similarity" Algorithm, for clustering.

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 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
|||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 course 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 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.
|||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, 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
|||> 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

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

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.
>
>|||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 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.
> >
> >
> >
>|||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|||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 I
then our MEETING TIME table has a primary clustered index of
YEAR, BUILDING, COURSE, SECTION, MEETING TIM
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.|||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.|||Kale
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|||> 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