Clustered DB Server: key

Showing posts with label key. Show all posts

Sunday, March 11, 2012

CodeAssist Err:35602 Key is not unique in collection

hi
i use CodeAssist application from Shredian Company.
its good application to make VB/SQL Code from any Database (Code Generator).
but now when i connect to SQL2000 Database by ODBC and open any table in
database i have this error:
35602 Key is not unique in collection
any one know why? or how i can solve this problem?
if someone have code generator application like codeAssist please tell me.
--
Tarek M. SialaHi ,
I found the following article for the cause of this error Err:35602 Key is
not unique in collection. Hope it help
POTENTIAL CAUSES :
1. The Integrity Wizard has been run on a database that has already
completed the Integrity Checks.
Resolution - Delete the Integrity Wizard Upgrade tables from the Application
database directory.
CORRECTION STEPS:
1. Using Explorer locate and delete the following .DAT files from the
Application database directory:
DATECHECK.DAT DATECKERR.DAT INTCKFLOW.DAT
Sylvana Mounir
Devloper Support Engineer
Micorosft MEA Developer Support Center
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:e3nO5cIbGHA.2372@.TK2MSFTNGP03.phx.gbl...
> hi
> i use CodeAssist application from Shredian Company.
> its good application to make VB/SQL Code from any Database (Code
> Generator).
> but now when i connect to SQL2000 Database by ODBC and open any table in
> database i have this error:
> 35602 Key is not unique in collection
> any one know why? or how i can solve this problem?
> if someone have code generator application like codeAssist please tell me.
> --
> Tarek M. Siala
>|||Some times is better to buy books and learn how to write the code from your
self. You save lot of money and time
Need more help? Search the web http://search.elakbar.net
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message news:e3nO5cIbGHA.237
2@.TK2MSFTNGP03.phx.gbl...
hi
i use CodeAssist application from Shredian Company.
its good application to make VB/SQL Code from any Database (Code Generator).
but now when i connect to SQL2000 Database by ODBC and open any table in
database i have this error:
35602 Key is not unique in collection
any one know why? or how i can solve this problem?
if someone have code generator application like codeAssist please tell me.
--
Tarek M. Siala

Thursday, February 16, 2012

Clustered vs Nonclustered PK

Ok, let me try to set the stage.

Between 2m and 5m inserts per day. NO UPDATES.

Table has a 4 part primary key. All BigInt data types. Key value 1 and 2 have a range between 1 and 100. Key values 3 and 4 are auto incrementing values (forign key values) from other tables.

Space is an issue, so we have chosen not to have an additional column for a counter field for the PK. (We would never use the field for querying.)

Users complained of query speeds, so we added a couple non clustered indexs. This brought up the query speeds a lot. But of course it slowed down the input speed a bit. Nothing dramatic, but enough so we could tell.

Now the users was to increase the amount of data by about 5X. Obviosly I'm somewhat concerend, as SQL is already spending a lot of the day pegged.

So, in looking around, since the new indexes seem to be the most help in querying, I'm thinking of dropping the PK back to a nonclustered index, so I can get rid of the over head of restructring the data table on every insert. Then maybe making one of the other indexes the clustered index. (only 2 columns in this index)

Thoughts?

I think the clustered index should be your most selective and help the most queries - it doesn't really matter if its the primary key or not as its just a constraint across the 4 fields.

As you're getting a lot of new rows each day, i'd pay some attention to the fillfactor of the indexes as this will allow space for new rows to the index and reduce fragmentation.

However, its difficult to recommend anything without knowing the query types. Perhaps post a few examples and potential frequencies.

Also, the Tuning Wizard could be your friend here and the new system views and funcs in sql2005 are really handy when it comes to assessing the cost/benefit of indexes.
|||

IF you really mean that the first two key values are ONLY between 1 and 100, then those are not near selective enough to be used for clustered indexes.

And it doesn't seem as if there there is a reason for them to be bigints.

I would most certainly engage the index tuning wizard, and a adequate 'workload'.

You may be better off with not having a clustered index on the table (at least not these combination of fields) to eliminate the constant page splits/reorganization.

|||

Well, we moved the clustered index to the index that's being used for queries.

The PK is now a nonclustered index.

Got a 30% increase in insert performance, and a notable increase in query preformance.

Oh, and the X and Y were only INTs. Not big ints. My bad.

Tuesday, February 14, 2012

Clustered Primary Key and Foreign Key: T-SQL

I am having a little trouble getting this to work right, but have come a ways since I started this...
...other tables created first and with no problems.... then these two with the last table being the problem
I need to set one foreign key in the second table referencing the first table.
But, the primary key is clustered with the two foreign keys and I get the error...
There are no primary or candidate keys in the referenced table 'courseScores'
that match the referencing column list in the foreign key 'FK_course'.

CREATE TABLE dbo.courseScores (
courseId varchar(20) NOT NULL
CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId)
REFERENCES courseStructure (courseId),

studentId varchar(20) NOT NULL
CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId)
REFERENCES students (studentId),

CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)

)
CREATE TABLE dbo.objScores ( tmp int IDENTITY(1,1) PRIMARY KEY,
objective varchar(50) NOT NULL,

courseIdvarchar(20)NOT NULL
CONSTRAINT FK_course FOREIGN KEY (courseId)
REFERENCES courseScores (courseId)
)

Once I get it working, then the tmp will be gone and then set 3 foreign keys as the clustered primary, fyi.
Not sure how to reference half a primary key?
Any help is greatly appreciated....
Thanks all,
Zath

A primary key guarantees uniqueness of rows, thereby also creating aguarantee that any foreign key referencing that primary key will bereferencing exactly one row. Your primary key is guaranteeinguniqueness of the combination of columns (courseid, studentid) -- butno such guarantee is made for ONLY courseid. If that column isindeed unique, apply a UNIQUE constraint to it and your foreign keywill work. Otherwise, you are going to have to propagate thestudentid column into the objScores table to get the full reference.
By the way, what do these tables represent? Perhaps we shouldback up before figuring out how to create these keys and see if there'sa better way to model the data.

|||

Thanks! Setting the previous field to UNIQUE did the trick!
Worked on this all day yesterday and databases are not my forte. I'll stick to code thank you Smile [:)]
But, if you want to see the complete working version and have suggestions, I'm open...
CREATE TABLE dbo.courseStructure (courseID varchar(20) NOT NULL PRIMARY KEY,
courseName varchar(256) NOT NULL
)

CREATE TABLE dbo.objStructure (objID varchar(20) NOT NULL PRIMARY KEY,
objName varchar(256) NOT NULL,
courseID varchar(20) NOT NULL,
CONSTRAINT FK_courseID_courseStructure FOREIGN KEY (courseID)
REFERENCES courseStructure (courseID)
)

CREATE TABLE dbo.students (studentId varchar(20) NOT NULL PRIMARY KEY
CONSTRAINT FK_studentId_students FOREIGN KEY (studentId)
REFERENCES userAccount (userID)
)

CREATE TABLE dbo.courseScores (
courseId varchar(20) NOT NULL UNIQUE
CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId)
REFERENCES courseStructure (courseId),

studentId varchar(20) NOT NULL
CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId)
REFERENCES students (studentId),

lessonLocation varchar(20),
lessonStatus varchar(20),
lessonScoreRaw varchar(20),
lessonScoreMin varchar(20),
lessonScoreMax varchar(20),
startDate datetime,
completeDate datetime,
CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)

)

CREATE TABLE dbo.objScores (
objective varchar(50) NOT NULL,

objID varchar(20) NOT NULL
CONSTRAINT FK_objId_objstructure FOREIGN KEY (objID)
REFERENCES objStructure (objID),

studentId varchar(20) NOT NULL
CONSTRAINT FK_studentId_students3 FOREIGN KEY (studentId)
REFERENCES students (studentId),

courseId varchar(20) NOT NULL
CONSTRAINT FK_course FOREIGN KEY (courseId)
REFERENCES courseScores (courseId),

objStatus varchar(20),
objScoreRaw varchar(20),
objScoreMin varchar(20),
objScoreMax varchar(20)

CONSTRAINT PK_objScores PRIMARY KEY CLUSTERED (objID, studentId, courseId)
)
Zath

|||What do these tables represent? What is an 'objScore'?

|||Doing a SCORM and LMS thing and for testing, we are using sql server db.
It is a testing site.
A student may enroll in multiple courses.
Each course has multiple objectives and scores...
There are many other tables in the db, but for this part, only the useraccount table needed to be accessed for the student ID.
But, it seems to be running ok for now.....
But always open to suggestions to improve it.
Zath

Clustered Primary Key

Hello!
How can I create a clustered primary key?
This alter table statement get the following error:
ALTER TABLE test ADD CONSTRAINT
PK_Table_1 Primary Key CLUSTERED
(
[Field 1],
[Field 2]
)
The constraint specified is not valid.

Thank you
SaschaThe CLUSTERED keyword is not supported by SQL Compact Edition. If you drop it, the statement should run OK.

Clustered Indexes / Primary Key

Hi All,
I am converting an Access app. from .mdb to ODBC link to a SQL Server.
The first thing I have run into is Clustered Indexes not sorted. I have a
small lookup table with 2 fields. From what I was told here by our DBA,
every key has to have a primary key/clustered index. So, the lookup tables
first field is nothing but Category Numbers which are unique and created by
the user. The table has 121 records. The 2nd field is the Category Name.
The Cat. No. is the Primary Key, Clustered, Unique, No-Nulls. The Cat. Name
is indexed non-clustered. When you open the table, it is sorted by the Cat.
Name and not by the Primary Key! Why?
This is not the behavior I'm looking for. Just learning SQL Server.
Don
The only way you are guaranteed any type of sorting is by
using a query and using an order by clause. You don't want
to depend on a clustered index for sorting your data. You
use an order by.
-Sue
On Thu, 11 Aug 2005 07:59:02 -0700, "Donald King"
<DonaldKing@.discussions.microsoft.com> wrote:

>Hi All,
> I am converting an Access app. from .mdb to ODBC link to a SQL Server.
>The first thing I have run into is Clustered Indexes not sorted. I have a
>small lookup table with 2 fields. From what I was told here by our DBA,
>every key has to have a primary key/clustered index. So, the lookup tables
>first field is nothing but Category Numbers which are unique and created by
>the user. The table has 121 records. The 2nd field is the Category Name.
>The Cat. No. is the Primary Key, Clustered, Unique, No-Nulls. The Cat. Name
>is indexed non-clustered. When you open the table, it is sorted by the Cat.
>Name and not by the Primary Key! Why?
>This is not the behavior I'm looking for. Just learning SQL Server.
>Don
|||Sue,
Thanks, I figured out that a Primary key DOES NOT have to be clustered
which I was misinformed by the DBA here. The ORDER BY clause in a query will
execute faster I believe if the field that your ORDER BY clause refers to is
Indexed.
Tks again,
Don
"Sue Hoegemeier" wrote:

> The only way you are guaranteed any type of sorting is by
> using a query and using an order by clause. You don't want
> to depend on a clustered index for sorting your data. You
> use an order by.
> -Sue
> On Thu, 11 Aug 2005 07:59:02 -0700, "Donald King"
> <DonaldKing@.discussions.microsoft.com> wrote:
>
>
|||I think the clustering PK issues hits people because if you
don't have a clustered index on a table and create a PK the
default is for it to be clustered. But it does not mean it
has to be that way - you can specify non-clustered for the
PK. And yes, if you are accessing the data sequentially that
column may be a good candidate for the clustered index. It's
always good to look at the whole picture with the table
though. You get one clustered index so its important to
choose wisely.
-Sue
On Tue, 16 Aug 2005 10:01:02 -0700, "Donald King"
<DonaldKing@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
> Thanks, I figured out that a Primary key DOES NOT have to be clustered
>which I was misinformed by the DBA here. The ORDER BY clause in a query will
>execute faster I believe if the field that your ORDER BY clause refers to is
>Indexed.
>Tks again,
>Don
>--
>"Sue Hoegemeier" wrote:

Clustered Indexes / Primary Key

Hi All,
I am converting an Access app. from .mdb to ODBC link to a SQL Server.
The first thing I have run into is Clustered Indexes not sorted. I have a
small lookup table with 2 fields. From what I was told here by our DBA,
every key has to have a primary key/clustered index. So, the lookup tables
first field is nothing but Category Numbers which are unique and created by
the user. The table has 121 records. The 2nd field is the Category Name.
The Cat. No. is the Primary Key, Clustered, Unique, No-Nulls. The Cat. Name
is indexed non-clustered. When you open the table, it is sorted by the Cat.
Name and not by the Primary Key! Why'
This is not the behavior I'm looking for. Just learning SQL Server.
DonThe only way you are guaranteed any type of sorting is by
using a query and using an order by clause. You don't want
to depend on a clustered index for sorting your data. You
use an order by.
-Sue
On Thu, 11 Aug 2005 07:59:02 -0700, "Donald King"
<DonaldKing@.discussions.microsoft.com> wrote:

>Hi All,
> I am converting an Access app. from .mdb to ODBC link to a SQL Server.
>The first thing I have run into is Clustered Indexes not sorted. I have a
>small lookup table with 2 fields. From what I was told here by our DBA,
>every key has to have a primary key/clustered index. So, the lookup tables
>first field is nothing but Category Numbers which are unique and created by
>the user. The table has 121 records. The 2nd field is the Category Name.
>The Cat. No. is the Primary Key, Clustered, Unique, No-Nulls. The Cat. Nam
e
>is indexed non-clustered. When you open the table, it is sorted by the Cat
.
>Name and not by the Primary Key! Why'
>This is not the behavior I'm looking for. Just learning SQL Server.
>Don|||Sue,
Thanks, I figured out that a Primary key DOES NOT have to be clustered
which I was misinformed by the DBA here. The ORDER BY clause in a query wil
l
execute faster I believe if the field that your ORDER BY clause refers to is
Indexed.
Tks again,
Don
--
"Sue Hoegemeier" wrote:

> The only way you are guaranteed any type of sorting is by
> using a query and using an order by clause. You don't want
> to depend on a clustered index for sorting your data. You
> use an order by.
> -Sue
> On Thu, 11 Aug 2005 07:59:02 -0700, "Donald King"
> <DonaldKing@.discussions.microsoft.com> wrote:
>
>|||I think the clustering PK issues hits people because if you
don't have a clustered index on a table and create a PK the
default is for it to be clustered. But it does not mean it
has to be that way - you can specify non-clustered for the
PK. And yes, if you are accessing the data sequentially that
column may be a good candidate for the clustered index. It's
always good to look at the whole picture with the table
though. You get one clustered index so its important to
choose wisely.
-Sue
On Tue, 16 Aug 2005 10:01:02 -0700, "Donald King"
<DonaldKing@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
> Thanks, I figured out that a Primary key DOES NOT have to be clustered
>which I was misinformed by the DBA here. The ORDER BY clause in a query wi
ll
>execute faster I believe if the field that your ORDER BY clause refers to i
s
>Indexed.
>Tks again,
>Don
>--
>"Sue Hoegemeier" wrote:
>

Clustered Indexes

We use uniqueidentifiers as our primary key because we have many oltp
systems that get merged into one corporate reporting structure.
Some tables accepted the default behaviour of SQL Server and made the
primary key the clustered index.
Does a clustered index on a GUID sort them in order or does it use them like
a heap table and insert all rows at the end?
Thanks in advance.Yes, the values are sorted, which can also lead to frequent page splits,
which in effect downgrades insert performance. Columns of type
UNIQUEIDENTIFIER are IMHO a lousy choice for clustered indexes for two main
reasons:
1) the size of the data; and
2) high selectiveness (pages get reordered quite frequently).
A performance gain can be achieved, though, by spreading the table on
several files, while keeping the FILL FACTOR of the clustered index low. Thi
s
would increase possibility of paralelism by enabling paralel inserts to occu
r
on different files concurrently.
I'd use a different column for a clustered index - where inserts are
frequent the best candidate IMHO is a column with a small datatype (int,
bigint) with incremental values (such as an IDENTITY column).
ML
http://milambda.blogspot.com/|||Rather than always inserting at the bottom page, a clustered index will
attempt to insert the row within a page so that the rows are clustered
(sorted) based on the value of the key. It's best not to use a clustered
index unless you are attempting to achieve a specific outcome.
You can use DBCC SHOWCONTIG to check for index fragmentation:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"JI" <jidawgs@.gmail.com> wrote in message
news:OW%23SXUMLGHA.2704@.TK2MSFTNGP15.phx.gbl...
> We use uniqueidentifiers as our primary key because we have many oltp
> systems that get merged into one corporate reporting structure.
> Some tables accepted the default behaviour of SQL Server and made the
> primary key the clustered index.
> Does a clustered index on a GUID sort them in order or does it use them
> like a heap table and insert all rows at the end?
> Thanks in advance.
>|||GUIDs are probably the worst choice for a clustered index, at least as far
as INSERTs go. IDENTITY is probably the best choice for a clustered index
because of its increasing nature, but with many data sources feeding one, it
can be tricky to manage. If the database is designed correctly, each table
should have at least one alternate key, so I'd put the clustered index
there, unless you expect a lot of ranged queries, in which case it may be
better to put the clustered index on the target of the ranged query.
Neither solution may offer much INSERT performance improvement, but it may
significantly improve query performance.
"JI" <jidawgs@.gmail.com> wrote in message
news:OW%23SXUMLGHA.2704@.TK2MSFTNGP15.phx.gbl...
> We use uniqueidentifiers as our primary key because we have many oltp
> systems that get merged into one corporate reporting structure.
> Some tables accepted the default behaviour of SQL Server and made the
> primary key the clustered index.
> Does a clustered index on a GUID sort them in order or does it use them
> like a heap table and insert all rows at the end?
> Thanks in advance.
>|||For sql2k, you can use Gert's xp_new_sequential_guid. This should eliminate
the issues (fragmentation, page split, etc.) with using guid as the primary
key.
http://sqldev.net/xp/xpguid.htm
-oj
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:eb86LrNLGHA.2780@.tk2msftngp13.phx.gbl...
> GUIDs are probably the worst choice for a clustered index, at least as far
> as INSERTs go. IDENTITY is probably the best choice for a clustered index
> because of its increasing nature, but with many data sources feeding one,
> it can be tricky to manage. If the database is designed correctly, each
> table should have at least one alternate key, so I'd put the clustered
> index there, unless you expect a lot of ranged queries, in which case it
> may be better to put the clustered index on the target of the ranged
> query. Neither solution may offer much INSERT performance improvement, but
> it may significantly improve query performance.
> "JI" <jidawgs@.gmail.com> wrote in message
> news:OW%23SXUMLGHA.2704@.TK2MSFTNGP15.phx.gbl...
>|||This is interesting. It generates a GUID based on the MAC address of the
NIC in the server. The only problem I see is that there are NICs out there
with the same MAC address--even though that's not supposed to happen.
Therefore it's possible for duplicates to be generated on different
machines. Barring that extremely remote possibility, this looks like a good
answer to the problem. SQL 2005 apparently has a NEWSEQUENTIALID() function
that provides this same functionality.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23GEoJ4NLGHA.668@.TK2MSFTNGP11.phx.gbl...
> For sql2k, you can use Gert's xp_new_sequential_guid. This should
> eliminate the issues (fragmentation, page split, etc.) with using guid as
> the primary key.
> http://sqldev.net/xp/xpguid.htm
>
> --
> -oj
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:eb86LrNLGHA.2780@.tk2msftngp13.phx.gbl...
>|||Actually it does not depend on the NIC (as with newid()). ;-)
http://msdn.microsoft.com/library/e...esequential.asp
-oj
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%238WfHXOLGHA.360@.TK2MSFTNGP12.phx.gbl...
> This is interesting. It generates a GUID based on the MAC address of the
> NIC in the server. The only problem I see is that there are NICs out
> there with the same MAC address--even though that's not supposed to
> happen. Therefore it's possible for duplicates to be generated on
> different machines. Barring that extremely remote possibility, this looks
> like a good answer to the problem. SQL 2005 apparently has a
> NEWSEQUENTIALID() function that provides this same functionality.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23GEoJ4NLGHA.668@.TK2MSFTNGP11.phx.gbl...
>|||Only if you don't have a NIC in the box. If there's a NIC, then it uses the
MAC.
"oj" <nospam_ojngo@.home.com> wrote in message
news:eYvlNvOLGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Actually it does not depend on the NIC (as with newid()). ;-)
> http://msdn.microsoft.com/library/e...esequential.asp
> --
> -oj
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:%238WfHXOLGHA.360@.TK2MSFTNGP12.phx.gbl...
>

Clustered index vs. nonclustered index for GUID primary key

Hello,
Would someone out there understand and can explain to me why clustered index
for GUID primary key will result a faster select operation when we look up
one row by a primary key like below comparing with non-clustered index for
the primary key? I experimented this myself, so there is no doubt that
clustered index is better but I don't know why.
select *
from MyTable -- there is no other index in this table.
where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
I read through several documentations and the B-tree seems similar enough
not to make a difference. In fact, my common sense (not very reliable now)
tells me that the result should be the other way around. Help!!!
Thank you very much in advance,Regardless of the data type, a clustered index seek is more efficient than a
non-clustered seek because a bookmark lookup is not needed. Note that a
clustered index is a B-tree with the actual data pages as leaf nodes. A
non-clustered index is also a B-tree but the leaf nodes are 'pointers' (RID
or clustering key) to the data rows so additional i/o is needed for the
bookmark lookup.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> Hello,
> Would someone out there understand and can explain to me why clustered
index
> for GUID primary key will result a faster select operation when we look up
> one row by a primary key like below comparing with non-clustered index for
> the primary key? I experimented this myself, so there is no doubt that
> clustered index is better but I don't know why.
> select *
> from MyTable -- there is no other index in this table.
> where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> I read through several documentations and the B-tree seems similar enough
> not to make a difference. In fact, my common sense (not very reliable now)
> tells me that the result should be the other way around. Help!!!
> Thank you very much in advance,
>|||Dan correctly answered your question but I wanted to make sure you need to
be aware of more than the effects of the Bookmark or not when comparing a
clustered index to Non-clustered on a GUID. Since Guid's are random by
nature you can get a tremendous amount of page splitting that can certainly
slow down inserts and increase the size of the table dramatically. The Guid
as a CI will also add an additional 16 bytes on to each row of every
non-clustered index as well. I am not trying to talk you out of using a
GUID as your CI but wanted to be sure you understood the other ramifications
of it. There has been a lot of discussion on this topic in these newsgroups
that you may want to google on before you decide one way or the other.
--
Andrew J. Kelly SQL MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> Hello,
> Would someone out there understand and can explain to me why clustered
index
> for GUID primary key will result a faster select operation when we look up
> one row by a primary key like below comparing with non-clustered index for
> the primary key? I experimented this myself, so there is no doubt that
> clustered index is better but I don't know why.
> select *
> from MyTable -- there is no other index in this table.
> where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> I read through several documentations and the B-tree seems similar enough
> not to make a difference. In fact, my common sense (not very reliable now)
> tells me that the result should be the other way around. Help!!!
> Thank you very much in advance,
>|||thanks but that is still very vague for me, you said the leaf nodes are
pointers, hm you mean leaf nodes contain bunch of pointers. Don't all index
nodes contain bunch of pointers to other index nodes or data pages? Why no
bookmark lookup needed when the acutal data pages are leaf nodes? Please
help...
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23KLT4bwiEHA.2052@.TK2MSFTNGP15.phx.gbl...
> Regardless of the data type, a clustered index seek is more efficient than
a
> non-clustered seek because a bookmark lookup is not needed. Note that a
> clustered index is a B-tree with the actual data pages as leaf nodes. A
> non-clustered index is also a B-tree but the leaf nodes are 'pointers'
(RID
> or clustering key) to the data rows so additional i/o is needed for the
> bookmark lookup.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > Would someone out there understand and can explain to me why clustered
> index
> > for GUID primary key will result a faster select operation when we look
up
> > one row by a primary key like below comparing with non-clustered index
for
> > the primary key? I experimented this myself, so there is no doubt that
> > clustered index is better but I don't know why.
> >
> > select *
> > from MyTable -- there is no other index in this table.
> > where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> >
> > I read through several documentations and the B-tree seems similar
enough
> > not to make a difference. In fact, my common sense (not very reliable
now)
> > tells me that the result should be the other way around. Help!!!
> >
> > Thank you very much in advance,
> >
> >
>|||With the exception of clustered index leaf nodes, all index nodes contain 'a
bunch of' pointers'. Clustered index leaf nodes contain the actual data
rows instead of pointers so no additional data access are needed.
You might peruse Books Online topics Clustered Indexes
<architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
<architec.chm::/8_ar_da2_75mb.htm> for a thorough discussion on the
differences.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23EYAw0wiEHA.704@.TK2MSFTNGP09.phx.gbl...
> thanks but that is still very vague for me, you said the leaf nodes are
> pointers, hm you mean leaf nodes contain bunch of pointers. Don't all
index
> nodes contain bunch of pointers to other index nodes or data pages? Why
no
> bookmark lookup needed when the acutal data pages are leaf nodes? Please
> help...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23KLT4bwiEHA.2052@.TK2MSFTNGP15.phx.gbl...
> > Regardless of the data type, a clustered index seek is more efficient
than
> a
> > non-clustered seek because a bookmark lookup is not needed. Note that a
> > clustered index is a B-tree with the actual data pages as leaf nodes. A
> > non-clustered index is also a B-tree but the leaf nodes are 'pointers'
> (RID
> > or clustering key) to the data rows so additional i/o is needed for the
> > bookmark lookup.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Zeng" <zzy@.nonospam.com> wrote in message
> > news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> > > Hello,
> > >
> > > Would someone out there understand and can explain to me why clustered
> > index
> > > for GUID primary key will result a faster select operation when we
look
> up
> > > one row by a primary key like below comparing with non-clustered index
> for
> > > the primary key? I experimented this myself, so there is no doubt
that
> > > clustered index is better but I don't know why.
> > >
> > > select *
> > > from MyTable -- there is no other index in this table.
> > > where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> > >
> > > I read through several documentations and the B-tree seems similar
> enough
> > > not to make a difference. In fact, my common sense (not very reliable
> now)
> > > tells me that the result should be the other way around. Help!!!
> > >
> > > Thank you very much in advance,
> > >
> > >
> >
> >
>|||Just to add to what others have said...
If a clustered index has been created for a table, the clustered index *is*
the table - all the columns of the table (and all the rows) are present in
the clustered index. So when you find 'DAF02BF0...' in the clustered index
(which is fast, since it's an equality search on the key, just what indexes
are for), you have also arrived at the entire row of the table and no
further data retrieval is needed to get the columns SELECT * must return.
A non-clustered index is more like a complete index for a book, or a
concordance. A concordance contains every word in the book, and for each
word, the page number(s) where that word appears in the book. If there are
pictures, punctuation, or other things in the book, you'll have to go to the
page to find them - they won't be in the index. If you look up and find
'DAF02BF0...' in a nonclustered index, you won't also find the other values
from that row you need to return SELECT *. You will only find out where to
go to find them, and will need to do more work - the index tells you where
to go by storing either the physical page location of the row or the
clustered index key of the row (the former if the table is not a clustered
index, the latter if the table is a clustered index).
I think a lot of this becomes easier to understand once you realize that the
clustered index is the table. A nonclustered index is only some of the
columns of the table, with references to direct you to the values for the
missing columns.
Steve Kass
Drew University
"Zeng" <zzy@.nonospam.com> wrote in message
news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> Hello,
> Would someone out there understand and can explain to me why clustered
index
> for GUID primary key will result a faster select operation when we look up
> one row by a primary key like below comparing with non-clustered index for
> the primary key? I experimented this myself, so there is no doubt that
> clustered index is better but I don't know why.
> select *
> from MyTable -- there is no other index in this table.
> where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> I read through several documentations and the B-tree seems similar enough
> not to make a difference. In fact, my common sense (not very reliable now)
> tells me that the result should be the other way around. Help!!!
> Thank you very much in advance,
>|||Andrew J. Kelly wrote:
> Dan correctly answered your question but I wanted to make sure you
> need to be aware of more than the effects of the Bookmark or not when
> comparing a clustered index to Non-clustered on a GUID. Since Guid's
> are random by nature you can get a tremendous amount of page
> splitting that can certainly slow down inserts and increase the size
> of the table dramatically. The Guid as a CI will also add an
> additional 16 bytes on to each row of every non-clustered index as
> well. I am not trying to talk you out of using a GUID as your CI but
> wanted to be sure you understood the other ramifications of it.
> There has been a lot of discussion on this topic in these newsgroups
> that you may want to google on before you decide one way or the
> other.
>
I agree with Andrew. If you can, use an IDENTITY column as your PK
instead of a GUID, especially if you are going to use a clustered index.
Not only will you eliminate page splitting, but your clustered key will
be much smaller and this will translate to smaller non-clustered indexes
as well.
--
David G.|||On 2004-08-26, David G. <david_nospam@.nospam.com> wrote:
> Andrew J. Kelly wrote:
>> Dan correctly answered your question but I wanted to make sure you
>> need to be aware of more than the effects of the Bookmark or not when
>> comparing a clustered index to Non-clustered on a GUID. Since Guid's
>> are random by nature you can get a tremendous amount of page
>> splitting that can certainly slow down inserts and increase the size
>> of the table dramatically. The Guid as a CI will also add an
>> additional 16 bytes on to each row of every non-clustered index as
>> well. I am not trying to talk you out of using a GUID as your CI but
>> wanted to be sure you understood the other ramifications of it.
>> There has been a lot of discussion on this topic in these newsgroups
>> that you may want to google on before you decide one way or the
>> other.
> I agree with Andrew. If you can, use an IDENTITY column as your PK
> instead of a GUID, especially if you are going to use a clustered index.
> Not only will you eliminate page splitting, but your clustered key will
> be much smaller and this will translate to smaller non-clustered indexes
> as well.
What about rowguid columns used for replication? Replication wizzard adds
rowguid columns, and creates clustered index on them if I don't allready
have clustered index on that particular table. Is it then better to change
that rowguid index to non-clustered one?
Mike
--
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr|||Mario Splivalo wrote:
> On 2004-08-26, David G. <david_nospam@.nospam.com> wrote:
>> Andrew J. Kelly wrote:
>> Dan correctly answered your question but I wanted to make sure you
>> need to be aware of more than the effects of the Bookmark or not
>> when comparing a clustered index to Non-clustered on a GUID. Since
>> Guid's are random by nature you can get a tremendous amount of page
>> splitting that can certainly slow down inserts and increase the size
>> of the table dramatically. The Guid as a CI will also add an
>> additional 16 bytes on to each row of every non-clustered index as
>> well. I am not trying to talk you out of using a GUID as your CI
>> but wanted to be sure you understood the other ramifications of it.
>> There has been a lot of discussion on this topic in these newsgroups
>> that you may want to google on before you decide one way or the
>> other.
>>
>> I agree with Andrew. If you can, use an IDENTITY column as your PK
>> instead of a GUID, especially if you are going to use a clustered
>> index. Not only will you eliminate page splitting, but your
>> clustered key will be much smaller and this will translate to
>> smaller non-clustered indexes as well.
> What about rowguid columns used for replication? Replication wizzard
> adds rowguid columns, and creates clustered index on them if I don't
> allready have clustered index on that particular table. Is it then
> better to change that rowguid index to non-clustered one?
> Mike
If the table experiences a lot of inserts (highly transactional), then
you will likely see page splits and the hard drive array may be writing
all over the place to get the row on the proper page. You can limit page
splits using a fill factor on the index and reindexing periodically to
keep the free page space available. But the table will be larger as a
result and the drive heads still may be moving around more than
necessary.
I'm not sure why SQL Server adds the index as clustered. Probably just a
default like it uses when adding a PK constraint.
Remember that clustered index keys are part of all non-clustered
indexes. So adding a 16-byte GUID CI adds an additional 16-bytes to each
key in a non-clustered index. If you have a non-clustered index on an
INT IDENTITY, then you'll be increasing storage from 4-bytes to 20-bytes
for each key, which is substantial (makes storage much more costly, not
to mention backups and restore operations, and writing).
I would suggest, as a matter of practice, you add a clustered index to
all tables. That way, SQL Server doesn't do it for you on a column that
probably shouldn't have one.
David G.|||I wasn't aware that the wizard made these clustered. The Guid used for
replication does not need to be the PK or clustered and often is not.
--
Andrew J. Kelly SQL MVP
"Mario Splivalo" <majk@.fly.srk.fer.hr> wrote in message
news:slrncir0tn.8sl.majk@.fly.srk.fer.hr...
> On 2004-08-26, David G. <david_nospam@.nospam.com> wrote:
> > Andrew J. Kelly wrote:
> >> Dan correctly answered your question but I wanted to make sure you
> >> need to be aware of more than the effects of the Bookmark or not when
> >> comparing a clustered index to Non-clustered on a GUID. Since Guid's
> >> are random by nature you can get a tremendous amount of page
> >> splitting that can certainly slow down inserts and increase the size
> >> of the table dramatically. The Guid as a CI will also add an
> >> additional 16 bytes on to each row of every non-clustered index as
> >> well. I am not trying to talk you out of using a GUID as your CI but
> >> wanted to be sure you understood the other ramifications of it.
> >> There has been a lot of discussion on this topic in these newsgroups
> >> that you may want to google on before you decide one way or the
> >> other.
> >>
> >
> > I agree with Andrew. If you can, use an IDENTITY column as your PK
> > instead of a GUID, especially if you are going to use a clustered index.
> > Not only will you eliminate page splitting, but your clustered key will
> > be much smaller and this will translate to smaller non-clustered indexes
> > as well.
> What about rowguid columns used for replication? Replication wizzard adds
> rowguid columns, and creates clustered index on them if I don't allready
> have clustered index on that particular table. Is it then better to change
> that rowguid index to non-clustered one?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr|||"David G." <david_nospam@.nospam.com> wrote in message
news:O5nSdgziEHA.396@.TK2MSFTNGP12.phx.gbl...
> Mario Splivalo wrote:
> > On 2004-08-26, David G. <david_nospam@.nospam.com> wrote:
> >> Andrew J. Kelly wrote:
> >> Dan correctly answered your question but I wanted to make sure you
> >> need to be aware of more than the effects of the Bookmark or not
> >> when comparing a clustered index to Non-clustered on a GUID. Since
> >> Guid's are random by nature you can get a tremendous amount of page
> >> splitting that can certainly slow down inserts and increase the size
> >> of the table dramatically. The Guid as a CI will also add an
> >> additional 16 bytes on to each row of every non-clustered index as
> >> well. I am not trying to talk you out of using a GUID as your CI
> >> but wanted to be sure you understood the other ramifications of it.
> >> There has been a lot of discussion on this topic in these newsgroups
> >> that you may want to google on before you decide one way or the
> >> other.
> >>
> >>
> >> I agree with Andrew. If you can, use an IDENTITY column as your PK
> >> instead of a GUID, especially if you are going to use a clustered
> >> index. Not only will you eliminate page splitting, but your
> >> clustered key will be much smaller and this will translate to
> >> smaller non-clustered indexes as well.
> >
> > What about rowguid columns used for replication? Replication wizzard
> > adds rowguid columns, and creates clustered index on them if I don't
> > allready have clustered index on that particular table. Is it then
> > better to change that rowguid index to non-clustered one?
> >
> > Mike
> If the table experiences a lot of inserts (highly transactional), then
> you will likely see page splits and the hard drive array may be writing
> all over the place to get the row on the proper page. You can limit page
> splits using a fill factor on the index and reindexing periodically to
> keep the free page space available. But the table will be larger as a
> result and the drive heads still may be moving around more than
> necessary.
> I'm not sure why SQL Server adds the index as clustered. Probably just a
> default like it uses when adding a PK constraint.
> Remember that clustered index keys are part of all non-clustered
> indexes. So adding a 16-byte GUID CI adds an additional 16-bytes to each
> key in a non-clustered index. If you have a non-clustered index on an
> INT IDENTITY, then you'll be increasing storage from 4-bytes to 20-bytes
> for each key, which is substantial (makes storage much more costly, not
> to mention backups and restore operations, and writing).
Not quite. An index on an integer column contains more than 4 bytes per row
regardless of whether there is a clustered index on the table. The index
must have some kind of reference to the location of the full table row to
begin with, in addition to the 4 bytes for the integer. Adding a GUID CI
adds fewer than 16 bytes to each non-clustered index. The addition of the
CI replaces a row locator (assuming no CI to begin with) with the new CI key
(or new CI key plus 4-byte uniquifier if the CI is not declared as unique).
So if there was no clustered index to begin with, I think the new 16-byte
reference replaces an existing 8-byte row locator, increasing the size of
each index row by 8 bytes, not 16.
SK
> I would suggest, as a matter of practice, you add a clustered index to
> all tables. That way, SQL Server doesn't do it for you on a column that
> probably shouldn't have one.
>
> --
> David G.
>|||Is this true? Basically, there are 2 differences between clustered and
nonclustered index:
1) Nonclustered index will typically require *one* extra disk reading
operations because the leaf nodes are not the data itself, AND
2) Nonclustered index will require a few more disk reading operations
because the data of each row is not stored together in one place. I have
looked at these references that Dan Clustered Indexes
<architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
<architec.chm::/8_ar_da2_75mb.htm> but doesn't find anything about this
point. And I wonder how come SqlServer doesn't always store each row
together by default' And is this the bookmark operation that the document
refering to?
Is there anything else?
"Steve Kass" <skass@.drew.edu> wrote in message
news:uLKdk8xiEHA.2808@.TK2MSFTNGP10.phx.gbl...
> Just to add to what others have said...
> If a clustered index has been created for a table, the clustered index
*is*
> the table - all the columns of the table (and all the rows) are present in
> the clustered index. So when you find 'DAF02BF0...' in the clustered
index
> (which is fast, since it's an equality search on the key, just what
indexes
> are for), you have also arrived at the entire row of the table and no
> further data retrieval is needed to get the columns SELECT * must return.
> A non-clustered index is more like a complete index for a book, or a
> concordance. A concordance contains every word in the book, and for each
> word, the page number(s) where that word appears in the book. If there
are
> pictures, punctuation, or other things in the book, you'll have to go to
the
> page to find them - they won't be in the index. If you look up and find
> 'DAF02BF0...' in a nonclustered index, you won't also find the other
values
> from that row you need to return SELECT *. You will only find out where
to
> go to find them, and will need to do more work - the index tells you where
> to go by storing either the physical page location of the row or the
> clustered index key of the row (the former if the table is not a clustered
> index, the latter if the table is a clustered index).
> I think a lot of this becomes easier to understand once you realize that
the
> clustered index is the table. A nonclustered index is only some of the
> columns of the table, with references to direct you to the values for the
> missing columns.
> Steve Kass
> Drew University
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:eMeR3SwiEHA.3988@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > Would someone out there understand and can explain to me why clustered
> index
> > for GUID primary key will result a faster select operation when we look
up
> > one row by a primary key like below comparing with non-clustered index
for
> > the primary key? I experimented this myself, so there is no doubt that
> > clustered index is better but I don't know why.
> >
> > select *
> > from MyTable -- there is no other index in this table.
> > where FileId = 'DAF02BF0-A809-4D59-8887-A2D3ED5C81B1'
> >
> > I read through several documentations and the B-tree seems similar
enough
> > not to make a difference. In fact, my common sense (not very reliable
now)
> > tells me that the result should be the other way around. Help!!!
> >
> > Thank you very much in advance,
> >
> >
>
>|||Zeng wrote:
> Is this true? Basically, there are 2 differences between clustered and
> nonclustered index:
> 1) Nonclustered index will typically require *one* extra disk reading
> operations because the leaf nodes are not the data itself, AND
> 2) Nonclustered index will require a few more disk reading operations
> because the data of each row is not stored together in one place. I
> have looked at these references that Dan Clustered Indexes
> <architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
> <architec.chm::/8_ar_da2_75mb.htm> but doesn't find anything about
> this point. And I wonder how come SqlServer doesn't always store each
> row together by default' And is this the bookmark operation that the
> document refering to?
>
I'm not sure what you mean by "how come SqlServer doesn't always store
each
> row together by default?". In a clustered index, the rows on a given
page are in order, although pages themselves may be out of sequence.
The clustered index is itself the table. That's why you can only have
one on a table. It would be impossible to sort the data two different
ways.
On a non-clustered index, as others have mentioned, you only have
pointers to the actual data. To get there, SQL Server performs a
bookmark lookup, which is a very fast operation, just not as fast as if
the query could use a clustered index. Unless the query can make use of
a covering index (all columns and parameters are in the index itself), a
bookmark lookup will occur.
David G.|||From Steve Cass comment, the data of one row in non-clustered index (for
table w/o clustered index) is not stored in one place, it's fragmented. If
I understand him correctly, if my table has 40 columns, then the data of
those 40 columns even for just one row might be scattered in different
places; so I'm was wondering how come SqlServer doesn't store data of all
columns belonging to one row in one place just like when there is clustered
index in the table.
"David G." <david_nospam@.nospam.com> wrote in message
news:OZawwB5iEHA.2848@.TK2MSFTNGP10.phx.gbl...
> Zeng wrote:
> > Is this true? Basically, there are 2 differences between clustered and
> > nonclustered index:
> > 1) Nonclustered index will typically require *one* extra disk reading
> > operations because the leaf nodes are not the data itself, AND
> > 2) Nonclustered index will require a few more disk reading operations
> > because the data of each row is not stored together in one place. I
> > have looked at these references that Dan Clustered Indexes
> > <architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
> > <architec.chm::/8_ar_da2_75mb.htm> but doesn't find anything about
> > this point. And I wonder how come SqlServer doesn't always store each
> > row together by default' And is this the bookmark operation that the
> > document refering to?
> >
> >
> I'm not sure what you mean by "how come SqlServer doesn't always store
> each
> > row together by default?". In a clustered index, the rows on a given
> page are in order, although pages themselves may be out of sequence.
> The clustered index is itself the table. That's why you can only have
> one on a table. It would be impossible to sort the data two different
> ways.
> On a non-clustered index, as others have mentioned, you only have
> pointers to the actual data. To get there, SQL Server performs a
> bookmark lookup, which is a very fast operation, just not as fast as if
> the query could use a clustered index. Unless the query can make use of
> a covering index (all columns and parameters are in the index itself), a
> bookmark lookup will occur.
>
> --
> David G.
>|||> If
> I understand him correctly, if my table has 40 columns, then the data of
> those 40 columns even for just one row might be scattered in different
> places;
No. one row is always stored together. In fact, one row always fit on one page. (With the exception of text
and image columns, of course.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zeng" <zzy@.nonospam.com> wrote in message news:uD%23bdn6iEHA.396@.TK2MSFTNGP12.phx.gbl...
> From Steve Cass comment, the data of one row in non-clustered index (for
> table w/o clustered index) is not stored in one place, it's fragmented. If
> I understand him correctly, if my table has 40 columns, then the data of
> those 40 columns even for just one row might be scattered in different
> places; so I'm was wondering how come SqlServer doesn't store data of all
> columns belonging to one row in one place just like when there is clustered
> index in the table.
>
> "David G." <david_nospam@.nospam.com> wrote in message
> news:OZawwB5iEHA.2848@.TK2MSFTNGP10.phx.gbl...
> > Zeng wrote:
> > > Is this true? Basically, there are 2 differences between clustered and
> > > nonclustered index:
> > > 1) Nonclustered index will typically require *one* extra disk reading
> > > operations because the leaf nodes are not the data itself, AND
> > > 2) Nonclustered index will require a few more disk reading operations
> > > because the data of each row is not stored together in one place. I
> > > have looked at these references that Dan Clustered Indexes
> > > <architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
> > > <architec.chm::/8_ar_da2_75mb.htm> but doesn't find anything about
> > > this point. And I wonder how come SqlServer doesn't always store each
> > > row together by default' And is this the bookmark operation that the
> > > document refering to?
> > >
> > >
> >
> > I'm not sure what you mean by "how come SqlServer doesn't always store
> > each
> > > row together by default?". In a clustered index, the rows on a given
> > page are in order, although pages themselves may be out of sequence.
> >
> > The clustered index is itself the table. That's why you can only have
> > one on a table. It would be impossible to sort the data two different
> > ways.
> >
> > On a non-clustered index, as others have mentioned, you only have
> > pointers to the actual data. To get there, SQL Server performs a
> > bookmark lookup, which is a very fast operation, just not as fast as if
> > the query could use a clustered index. Unless the query can make use of
> > a covering index (all columns and parameters are in the index itself), a
> > bookmark lookup will occur.
> >
> >
> > --
> > David G.
> >
>|||Zeng,
The data in a non-clustered index is duplicate data. Suppose table T
has columns A, B, C, D, and E. The data is stored together - all 5
columns and all rows, either in the clustered index, if there is one, or
in what is called a heap. In either case, let's just call it the table.
The table, or the clustered index
A B C D E
A B C D E
A B C D E
...
A B C D E
If we now add a nonclustered index on column D, we will have a second
copy of column D stored away from the table. To make the connection
between these D values and the rest of the table, we need to store
additional information with each D value so we can find the A, B, C, and
E values in the same row as the D value. So the nonclustered index
looks like this (remember, this is stored in addition to the table - the
table also contains a copy of the D values)
The nonclustered index on D
D <location of this row in the table>
D <location of this row in the table>
D <location of this row in the table>
...
D <location of this row in the table>
So now the D values are stored in two places. Once with the whole
table, but probably not in an order that lets us search for a particular
D value, and a second time in the nonclustered index, in order by D
value, but without the rest of the column values - instead of that
information, a reference to the full table row is stored alongside each
D value. The way I think of it, the nonclustered index has "rows" just
like the table does, but they don't include as much information.
So to answer your question, SQL Server always stores the data of all
columns in one place. It may, however, store the data from some columns
in another place (a nonclustered index) *in addition*.to where all the
data is stored together.
SK
Zeng wrote:
>From Steve Cass comment, the data of one row in non-clustered index (for
>table w/o clustered index) is not stored in one place, it's fragmented. If
>I understand him correctly, if my table has 40 columns, then the data of
>those 40 columns even for just one row might be scattered in different
>places; so I'm was wondering how come SqlServer doesn't store data of all
>columns belonging to one row in one place just like when there is clustered
>index in the table.
>
>"David G." <david_nospam@.nospam.com> wrote in message
>news:OZawwB5iEHA.2848@.TK2MSFTNGP10.phx.gbl...
>
>>Zeng wrote:
>>
>>Is this true? Basically, there are 2 differences between clustered and
>>nonclustered index:
>>1) Nonclustered index will typically require *one* extra disk reading
>>operations because the leaf nodes are not the data itself, AND
>>2) Nonclustered index will require a few more disk reading operations
>>because the data of each row is not stored together in one place. I
>>have looked at these references that Dan Clustered Indexes
>><architec.chm::/8_ar_da2_1tbn.htm> and Non-Clustered Indexes
>><architec.chm::/8_ar_da2_75mb.htm> but doesn't find anything about
>>this point. And I wonder how come SqlServer doesn't always store each
>>row together by default' And is this the bookmark operation that the
>>document refering to?
>>
>>
>>I'm not sure what you mean by "how come SqlServer doesn't always store
>>each
>>
>>row together by default?". In a clustered index, the rows on a given
>>
>>page are in order, although pages themselves may be out of sequence.
>>The clustered index is itself the table. That's why you can only have
>>one on a table. It would be impossible to sort the data two different
>>ways.
>>On a non-clustered index, as others have mentioned, you only have
>>pointers to the actual data. To get there, SQL Server performs a
>>bookmark lookup, which is a very fast operation, just not as fast as if
>>the query could use a clustered index. Unless the query can make use of
>>a covering index (all columns and parameters are in the index itself), a
>>bookmark lookup will occur.
>>
>>--
>>David G.
>>
>
>

Sunday, February 12, 2012

Clustered Index Primary/Non-Primary Key

What difference does a Clustered Index have, if was created from a primary
key or non-primary key?
Please help me with this questions?
Thank You,
Paul
Start with
http://www.sql-server-performance.co...ed_indexes.asp
http://www.sql-server-performance.co...ed_indexes.asp
"Paul T." <Paul T.@.discussions.microsoft.com> wrote in message
news:F9E308EA-F871-447C-B8BE-2C14347FB80E@.microsoft.com...
> What difference does a Clustered Index have, if was created from a primary
> key or non-primary key?
> Please help me with this questions?
> Thank You,
|||UNIQUE
"Paul T." <Paul T.@.discussions.microsoft.com> wrote in message
news:F9E308EA-F871-447C-B8BE-2C14347FB80E@.microsoft.com...
> What difference does a Clustered Index have, if was created from a primary
> key or non-primary key?
> Please help me with this questions?
> Thank You,
|||Paul,
The selection of a clustered index is more related to how you use those
columns (the ones that participate in the index key) in your queries than if
they are or not primary keys. The links posted by Uri are a good start to
learn about indexes, and I also recommend BOL and sql server books about
internals and architecture.
Inside Microsoft SQL Server 2000
http://www.amazon.com/gp/product/073...books&v=glance
Microsoft SQL Server 2000 Unleashed
http://www.amazon.com/gp/product/067...lance&n=283155
The Guru's Guide to SQL Server Architecture and Internals
http://www.amazon.com/gp/product/020...lance&n=283155
AMB
"Paul T." wrote:

> What difference does a Clustered Index have, if was created from a primary
> key or non-primary key?
> Please help me with this questions?
> Thank You,

Clustered Index Primary/Non-Primary Key

What difference does a Clustered Index have, if was created from a primary
key or non-primary key?
Please help me with this questions?
Thank You,Paul
Start with
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
"Paul T." <Paul T.@.discussions.microsoft.com> wrote in message
news:F9E308EA-F871-447C-B8BE-2C14347FB80E@.microsoft.com...
> What difference does a Clustered Index have, if was created from a primary
> key or non-primary key?
> Please help me with this questions?
> Thank You,|||UNIQUE
"Paul T." <Paul T.@.discussions.microsoft.com> wrote in message
news:F9E308EA-F871-447C-B8BE-2C14347FB80E@.microsoft.com...
> What difference does a Clustered Index have, if was created from a primary
> key or non-primary key?
> Please help me with this questions?
> Thank You,|||Paul,
The selection of a clustered index is more related to how you use those
columns (the ones that participate in the index key) in your queries than if
they are or not primary keys. The links posted by Uri are a good start to
learn about indexes, and I also recommend BOL and sql server books about
internals and architecture.
Inside Microsoft SQL Server 2000
http://www.amazon.com/gp/product/07...=books&v=glance
Microsoft SQL Server 2000 Unleashed
http://www.amazon.com/gp/product/06...glance&n=283155
The Guru's Guide to SQL Server Architecture and Internals
http://www.amazon.com/gp/product/02...glance&n=283155
AMB
"Paul T." wrote:

> What difference does a Clustered Index have, if was created from a primary
> key or non-primary key?
> Please help me with this questions?
> Thank You,

Clustered Index Primary/Non-Primary Key

What difference does a Clustered Index have, if was created from a primary
key or non-primary key?
Please help me with this questions?
Thank You,Paul
Start with
http://www.sql-server-performance.com/clustered_indexes.asp
http://www.sql-server-performance.com/nonclustered_indexes.asp
"Paul T." <Paul T.@.discussions.microsoft.com> wrote in message
news:F9E308EA-F871-447C-B8BE-2C14347FB80E@.microsoft.com...
> What difference does a Clustered Index have, if was created from a primary
> key or non-primary key?
> Please help me with this questions?
> Thank You,|||UNIQUE
"Paul T." <Paul T.@.discussions.microsoft.com> wrote in message
news:F9E308EA-F871-447C-B8BE-2C14347FB80E@.microsoft.com...
> What difference does a Clustered Index have, if was created from a primary
> key or non-primary key?
> Please help me with this questions?
> Thank You,|||Paul,
The selection of a clustered index is more related to how you use those
columns (the ones that participate in the index key) in your queries than if
they are or not primary keys. The links posted by Uri are a good start to
learn about indexes, and I also recommend BOL and sql server books about
internals and architecture.
Inside Microsoft SQL Server 2000
http://www.amazon.com/gp/product/0735609985/qid=1134575378/sr=8-1/ref=pd_bbs_1/104-0418743-6931921?n=507846&s=books&v=glance
Microsoft SQL Server 2000 Unleashed
http://www.amazon.com/gp/product/0672324679/qid=1134575416/sr=2-1/ref=pd_bbs_b_2_1/104-0418743-6931921?s=books&v=glance&n=283155
The Guru's Guide to SQL Server Architecture and Internal
http://www.amazon.com/gp/product/0201700476/qid=1134575442/sr=2-3/ref=pd_bbs_b_2_3/104-0418743-6931921?s=books&v=glance&n=283155
AMB
"Paul T." wrote:
> What difference does a Clustered Index have, if was created from a primary
> key or non-primary key?
> Please help me with this questions?
> Thank You,

CLUSTERED INDEX or NONCLUSTERED

I have 3 table A, B, C

Table A (15 field, 4 fields indexed and Primary Key) approximate rows: 50.000 60.000

Table B (18 field, 6 fields indexed and Primary Key) approximate rows: 350.000 500.000

Table C (16 filed, 9 fields indexed and Primary Key) approximate rows: 500.000 1.000.000

Structure is something like this:
A (master) --> B (detail) --> C (sub detail)

On each 3 table is added new record, in table C the record is added after a search in table B.
My question is: Which is the best method? CLUSTERED INDEX or NONCLUSTERED INDEX

Thanks
Sorry for my englishIt is not clear about relations between tables (number of fields, etc.) by anyway clustered index for PK and nonclustered for others will be OK.|||Not enough info, these links may help you:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TuningofaDifferentSort.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp|||Thank you for your answer.
The diagram is attached, form left to right table A; B; C|||The diagram|||Still not enough info. Some questions:

What is your ratio of inserts to queries? Are you heavy insert or heavy queries or both?

What is typically used for your select criterias?

I would reccomend you start with reading those articles and you may play around with "set statistics IO on" to evaluate your logical IO when you have added a clustered index, taken it off, added a nonclustered index, etc. This to me is the best advice to become self sufficient on indexing questions.

HTH

Clustered Index on Date Field or Identity Field ....

Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s master table.
I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 w

or so.
> Im planning to add a clustered index on DTTM field so that the reports
> would become faster compared to a table scan what its doing now.
> My question is, is it a good idea to create a clustered index on a
> Datetime field?
> or is it a better way to make ID the clustered index and then create a
> non clustered index on DTTM?
> But I always had the doubt that, what is the purpose of creating a
> clustered index on an identity field that too which is already a primary
> key,
> since an identity field is already ordered. Does it make sense to create
> a clustered in index on Identity field.
> Add to this most of my Stored procedures which are used to retrieve uses
> ID to join with its master table.
> DTTM would be used only in reports...
> Thanks,
> Prad
>
it seems that a clustered index is better. try both ways and look at the
execution plan(s)|||Pradeep,

>Im planning to add a clustered index on DTTM field so that the reports would become
faster compared to a table scan what its doing now.
Thats a good idea because clustered index is ideal for range search.

>But I always had the doubt that, what is the purpose of creating a clustere
d index on an identity field that too which is already a primary key,
>since an identity field is already ordered. Does it make sense to create a clustere
d in index on Identity field.
One advantage of having a clustered index on the IDENTITY column is that it
will help you avoid page split problems.
But your assumption about the order of IDENTITY value is wrong. IDENTITY onl
oy provides a logical sequence, whereas a clustered index
controls the order in which the rows are physically stored.
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message news:%23EVpg8TrF
HA.716@.TK2MSFTNGP10.phx.gbl...
Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s master table.
I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 w

or so.
Im planning to add a clustered index on DTTM field so that the reports would
become faster compared to a table scan what its doing now.
My question is, is it a good idea to create a clustered index on a Datetime
field?
or is it a better way to make ID the clustered index and then create a non c
lustered index on DTTM?
But I always had the doubt that, what is the purpose of creating a clustered
index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a c
lustered in index on Identity field.
Add to this most of my Stored procedures which are used to retrieve uses ID
to join with its master table.
DTTM would be used only in reports...
Thanks,
Prad|||If it is used in joins, then I would put the clustered index on the IDENTITY
column. This can speed up inserts into this table, inserts into related ta
bles, and joins between this table and related tables. If the order of the
IDENTITY increment matches the order of the clustered index on the IDENTITY
column, then all inserts will occur at the end of the table, which minimizes
the required index maintenance operations.
If a table has a clustered index, then all nonclustered indexes use the clus
tered index key to locate rows in the table. If you put a nonclustered inde
x on the primary key, then every join will result in an additional step in t
he execution plan--a bookmark lookup. This extra level of indirection can s
ignificantly reduce the performance of every join. In addition, if you use
a clustered index on a datetime column, and the datetime column is not a can
didate key, then SQL Server will add a 4-byte uniqifier to every index row s
o that the index key can be used in nonclustered indexes to locate rows. Th
is increases the size of each nonclustered index, and can further reduce que
ry performance, especially with respect to joins.
To boost performance for reporting, you have other options aside from simply
adding an index. Here are a couple: (1) use a covering index so that the b
ookmark lookup will not be necessary, or (2) create an indexed view, and use
both the datetime and the identity column (in that order) as the clustered
index key for the view. If all of the columns necessary for the query exist
in the index key, then there is no need for SQL Server to access the actual
data row, so the performance degradation resulting from the use a noncluste
red index will be minimized. If that doesn't provide adequate reporting per
formance, the indexed view option will at least meet the select performance
of accessing a table with a clustered index directly, without degrading the
performance of the joins. It should be noted, however, that insert performa
nce will be degraded by the addition of any index or indexed view.
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message news:#EVpg8TrFHA
.716@.TK2MSFTNGP10.phx.gbl...
Hi All,
I have a table (detail table) with fields ID (Identity) primary Key and a DT
TM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with it
s master table.
I have many reports which uses this table and for all the reports the basic
criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 w

or
> so.
> Im planning to add a clustered index on DTTM field so that the
> reports would become faster compared to a table scan what its doing
> now.
> My question is, is it a good idea to create a clustered index on a
> Datetime field?
> or is it a better way to make ID the clustered index and then create
> a non clustered index on DTTM?
> But I always had the doubt that, what is the purpose of creating a
> clustered index on an identity field that too which is already a
> primary key,
> since an identity field is already ordered. Does it make sense to
> create a clustered in index on Identity field.
> Add to this most of my Stored procedures which are used to retrieve
> uses ID to join with its master table.
> DTTM would be used only in reports...
Either I overlooked it or nobody actually mentioned a composite index. If
you always do queries that join by your PK and use only a date range then
a composite clustered index on (timestamp, ID) might also be worth
considering. Or am I missing something here?
Kind regards
robert

Sunday, March 11, 2012

CodeAssist Err:35602 Key is not unique in collection

Thursday, February 16, 2012

Clustered vs Nonclustered PK

Tuesday, February 14, 2012

Clustered Primary Key and Foreign Key: T-SQL

Clustered Primary Key

Clustered Indexes / Primary Key

Clustered Indexes / Primary Key

Clustered Indexes

Clustered index vs. nonclustered index for GUID primary key

Clustered index vs. nonclustered index for GUID primary key

Clustered index vs. nonclustered index for GUID primary key

Sunday, February 12, 2012

Clustered Index Primary/Non-Primary Key

Clustered Index Primary/Non-Primary Key

Clustered Index Primary/Non-Primary Key

CLUSTERED INDEX or NONCLUSTERED

Clustered Index on Date Field or Identity Field ....

Clustered DB Server

Blog Archive

About Me