I'm not "cobol person" but now, I have to bind a cobol application (from mainframe) to query SQL 2005
Have somebody had this task?No, but it should be pretty straightforward... All of the mini / mainframe products I've seen use ODBC to allow native applications running on the host (the mini or the mainframe) to bind to the PC database. Operative word being "should", this should be quite straightforward, simply changing the ODBC driver referenced and setting the server / database / security settings appropriately.
-PatP|||No, but it should be pretty straightforward... All of the mini / mainframe products I've seen use ODBC to allow native applications running on the host (the mini or the mainframe) to bind to the PC database. Operative word being "should", this should be quite straightforward, simply changing the ODBC driver referenced and setting the server / database / security settings appropriately.
-PatP
Thanks Pat
I try to find communication between Mainframe and PC db throgh ODBC :).
This cobol-programm never have been running to bind to the PC db - it is working with mainframe DB2. This is tamporary deal (we are going from DB2-to SQL2005) I could write this from PC using VS C# for example and hit mainframe, but I have to save even interface of this "stupid" programm :).
thanks again|||how about "Connect Direct" or "Direct Connect" ?|||wath do you mean?
Actually, now I'm studing how to do this for IBM CICS environment that uses EXEC SQL CONNECT to DB2 to change to SQL2005
Showing posts with label somebody. Show all posts
Showing posts with label somebody. Show all posts
Wednesday, March 7, 2012
Friday, February 10, 2012
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
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
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
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
ClusterDistance value
Can somebody please define the result of the ClusterDistance() function for me. BOL and MSDN are vague about what the output means and I am getting some cases that have a value of zero and sometimes there are more than one per cluster.
Thanks in advance,
Dan Meyers
I believe the clusterdistance() is simply 1-clusterprobability(). Are you saying you are getting more that one "0" value of cluster distance for different clusters on a single case?|||No. I am getting more than one case per cluster that has a ClusterDistance() value of "0".|||That's ok - it's an issue if you get more than one cluster per case with a ClusterDistance() of 0. Imagine if you have two identical cases - it would make sense that they have the same ClusterDistance(), would it not?|||It does make sense. I just wanted to make sure that I was understanding everything correctly and I was not sure because the documentation is a bit vague in its definition of the function.
Thanks Jamie.
Subscribe to:
Posts (Atom)