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

Code Review

I need to get information on all the unique constraints for a table. I
needed info on whether a constraint is clustered or not, what filegroup
it resides in, columns that comprise the unique index (in a comma
separated list) and it's name. So I wrote the code (see below).
However, SQL is not my first language, so I was looking to see whether a
seasoned SQL expert can recommend anyway to speed up this query. Or
maybe I should take change my approach?
SET NOCOUNT ON
create table #UniqueConstraints
(
ID int IDENTITY,
Name varchar(255),
IndexID int,
IsClustered bit NULL,
FileGroup varchar(255),
Columns varchar(255) NULL
)
create table #ColumnList
(
ID int IDENTITY,
Name varchar(255)
)
-- set to non-clustered by default
insert #UniqueConstraints(Name, IndexID, FileGroup, IsClustered)
SELECT I.name,I.indid,FILEGROUP_NAME(I.groupid) as filegroup, 0
FROM sysindexes I, sysconstraints c
WHERE I.id=OBJECT_ID('dbo.Customers') AND (((I.status & 0x800)=0x800) OR
((I.status & 0x1000)=0x1000))
and i.id = c.id
and (C.status & 0xf)=2
and OBJECT_NAME(C.constid) = I.name
ORDER BY I.indid
-- mark clustered indexees
update #UniqueConstraints
set IsClustered = 1
where IndexID = 1
-- now get columns
DECLARE @.min_ID int, @.indexID int, @.minColumn_ID int
DECLARE @.columnList varchar(255), @.columnName varchar(255), @.comma
varchar(1)
SELECT @.min_ID = min(ID) from #UniqueConstraints
WHILE @.min_ID IS NOT NULL BEGIN
-- get the index id
SELECT @.indexID = IndexID from #UniqueConstraints where ID = @.min_ID
-- freshen up this table
truncate table #ColumnList
-- get the list of involved columns
INSERT #ColumnList(Name)
SELECT COL_NAME(id, colid)
FROM sysindexkeys
WHERE id=OBJECT_ID('dbo.Customers') AND indid=@.indexID ORDER BY
keyno
-- convert the table contents into a comma separated list
SELECT @.minColumn_ID = min(ID) from #ColumnList
-- reinitialize
SET @.comma = ''
SET @.columnList = ''
WHILE @.minColumn_ID IS NOT NULL BEGIN
SELECT @.columnName = Name FROM #ColumnList WHERE ID = @.minColumn_ID
SET @.columnList = @.columnList + @.comma + @.columnName
set @.comma = ','
SELECT @.minColumn_ID = min(ID) FROM #ColumnList WHERE ID >
@.minColumn_ID
END
UPDATE #UniqueConstraints
SET Columns = @.columnList
WHERE ID = @.min_ID
-- get the next value from the table
SELECT @.min_ID = min(ID) FROM #UniqueConstraints WHERE ID > @.min_ID
END
select Name, IsClustered, FileGroup, Columns from #UniqueConstraints
drop table #UniqueConstraints
drop table #ColumnListThe first part of your requirement can be solved in an easier way, like
this:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
The comma separated list of the columns that are part of each unique
constraint is a little bit more complicated. I would use an UDF, like
this:
CREATE FUNCTION dbo.IndexColumns(@.id int, @.indid int)
RETURNS nvarchar(4000)
AS BEGIN
DECLARE @.List nvarchar(4000), @.ColName sysname
DECLARE Columns CURSOR LOCAL READ_ONLY FOR
SELECT COL_NAME(id,colid) FROM sysindexkeys
WHERE id=@.id AND indid=@.indid
ORDER BY keyno
OPEN Columns
WHILE 1=1 BEGIN
FETCH NEXT FROM Columns INTO @.ColName
IF @.@.FETCH_STATUS<>0 BREAK
SET @.List=ISNULL(@.List+',','')+@.ColName
END
CLOSE Columns
DEALLOCATE Columns
RETURN @.List
END
GO
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup,
dbo.IndexColumns(i.id,i.indid) AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
Another way would be to rely on the fact that there are a maximum of 16
columns for an index:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup, (
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=1
)+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=2
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=3
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=4
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=5
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=6
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=7
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=8
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=9
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=10
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=11
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=12
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=13
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=14
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=15
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=16
),'') AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
There is a small difference between the above solutions and your
solution: in the above solutions, the columns in the comma separated
list are presented in the order that is used when the constraint was
defined; in your solution, they are presented in the order in which
they appear in the table.
Razvan

Code Review

I need to get information on all the unique constraints for a table. I
needed info on whether a constraint is clustered or not, what filegroup
it resides in, columns that comprise the unique index (in a comma
separated list) and it's name. So I wrote the code (see below).
However, SQL is not my first language, so I was looking to see whether a
seasoned SQL expert can recommend anyway to speed up this query. Or
maybe I should take change my approach?
SET NOCOUNT ON
create table #UniqueConstraints
(
ID int IDENTITY,
Name varchar(255),
IndexID int,
IsClustered bit NULL,
FileGroup varchar(255),
Columns varchar(255) NULL
)
create table #ColumnList
(
ID int IDENTITY,
Name varchar(255)
)
-- set to non-clustered by default
insert #UniqueConstraints(Name, IndexID, FileGroup, IsClustered)
SELECT I.name,I.indid,FILEGROUP_NAME(I.groupid) as filegroup, 0
FROM sysindexes I, sysconstraints c
WHERE I.id=OBJECT_ID('dbo.Customers') AND (((I.status & 0x800)=0x800) OR
((I.status & 0x1000)=0x1000))
and i.id = c.id
and (C.status & 0xf)=2
and OBJECT_NAME(C.constid) = I.name
ORDER BY I.indid
-- mark clustered indexees
update #UniqueConstraints
set IsClustered = 1
where IndexID = 1
-- now get columns
DECLARE @.min_ID int, @.indexID int, @.minColumn_ID int
DECLARE @.columnList varchar(255), @.columnName varchar(255), @.comma
varchar(1)
SELECT @.min_ID = min(ID) from #UniqueConstraints
WHILE @.min_ID IS NOT NULL BEGIN
-- get the index id
SELECT @.indexID = IndexID from #UniqueConstraints where ID = @.min_ID
-- freshen up this table
truncate table #ColumnList
-- get the list of involved columns
INSERT #ColumnList(Name)
SELECT COL_NAME(id, colid)
FROM sysindexkeys
WHERE id=OBJECT_ID('dbo.Customers') AND indid=@.indexID ORDER BY
keyno
-- convert the table contents into a comma separated list
SELECT @.minColumn_ID = min(ID) from #ColumnList
-- reinitialize
SET @.comma = ''
SET @.columnList = ''
WHILE @.minColumn_ID IS NOT NULL BEGIN
SELECT @.columnName = Name FROM #ColumnList WHERE ID = @.minColumn_ID
SET @.columnList = @.columnList + @.comma + @.columnName
set @.comma = ','
SELECT @.minColumn_ID = min(ID) FROM #ColumnList WHERE ID >
@.minColumn_ID
END
UPDATE #UniqueConstraints
SET Columns = @.columnList
WHERE ID = @.min_ID
-- get the next value from the table
SELECT @.min_ID = min(ID) FROM #UniqueConstraints WHERE ID > @.min_ID
END
select Name, IsClustered, FileGroup, Columns from #UniqueConstraints
drop table #UniqueConstraints
drop table #ColumnListThe first part of your requirement can be solved in an easier way, like
this:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
The comma separated list of the columns that are part of each unique
constraint is a little bit more complicated. I would use an UDF, like
this:
CREATE FUNCTION dbo.IndexColumns(@.id int, @.indid int)
RETURNS nvarchar(4000)
AS BEGIN
DECLARE @.List nvarchar(4000), @.ColName sysname
DECLARE Columns CURSOR LOCAL READ_ONLY FOR
SELECT COL_NAME(id,colid) FROM sysindexkeys
WHERE id=@.id AND indid=@.indid
ORDER BY keyno
OPEN Columns
WHILE 1=1 BEGIN
FETCH NEXT FROM Columns INTO @.ColName
IF @.@.FETCH_STATUS<>0 BREAK
SET @.List=ISNULL(@.List+',','')+@.ColName
END
CLOSE Columns
DEALLOCATE Columns
RETURN @.List
END
GO
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup,
dbo.IndexColumns(i.id,i.indid) AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
Another way would be to rely on the fact that there are a maximum of 16
columns for an index:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup, (
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=1
)+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=2
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=3
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=4
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=5
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=6
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=7
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=8
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=9
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=10
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=11
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=12
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=13
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=14
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=15
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=16
),'') AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
There is a small difference between the above solutions and your
solution: in the above solutions, the columns in the comma separated
list are presented in the order that is used when the constraint was
defined; in your solution, they are presented in the order in which
they appear in the table.
Razvan

Clustered indexes - unique and not unique

Hi friends,
In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
get rebuilt too along with it, and it's clear why.
In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
Why is that?
Thanks in advance.
Because the uniqueifier that is used in 2005 does not change when the
clustered index is rebuilt. So the key appended to the non-clustered index
will still point to the proper clustered index row after a rebuild.
Andrew J. Kelly SQL MVP
"Falconer" <me@.isp.net> wrote in message
news:C28zh.38401$Oa.29862@.edtnps82...
> Hi friends,
> In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
> get rebuilt too along with it, and it's clear why.
> In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
> Why is that?
> Thanks in advance.
>
|||Thanks Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJ5Ee9KTHHA.4252@.TK2MSFTNGP05.phx.gbl...
> Because the uniqueifier that is used in 2005 does not change when the
> clustered index is rebuilt. So the key appended to the non-clustered index
> will still point to the proper clustered index row after a rebuild.
> --
> Andrew J. Kelly SQL MVP
> "Falconer" <me@.isp.net> wrote in message
> news:C28zh.38401$Oa.29862@.edtnps82...
>

Clustered indexes - unique and not unique

Hi friends,
In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
get rebuilt too along with it, and it's clear why.
In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
Why is that?
Thanks in advance.Because the uniqueifier that is used in 2005 does not change when the
clustered index is rebuilt. So the key appended to the non-clustered index
will still point to the proper clustered index row after a rebuild.
Andrew J. Kelly SQL MVP
"Falconer" <me@.isp.net> wrote in message
news:C28zh.38401$Oa.29862@.edtnps82...
> Hi friends,
> In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
> get rebuilt too along with it, and it's clear why.
> In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
> Why is that?
> Thanks in advance.
>|||Thanks Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJ5Ee9KTHHA.4252@.TK2MSFTNGP05.phx.gbl...
> Because the uniqueifier that is used in 2005 does not change when the
> clustered index is rebuilt. So the key appended to the non-clustered index
> will still point to the proper clustered index row after a rebuild.
> --
> Andrew J. Kelly SQL MVP
> "Falconer" <me@.isp.net> wrote in message
> news:C28zh.38401$Oa.29862@.edtnps82...
>

Clustered indexes - unique and not unique

Hi friends,
In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
get rebuilt too along with it, and it's clear why.
In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
Why is that?
Thanks in advance.Because the uniqueifier that is used in 2005 does not change when the
clustered index is rebuilt. So the key appended to the non-clustered index
will still point to the proper clustered index row after a rebuild.
--
Andrew J. Kelly SQL MVP
"Falconer" <me@.isp.net> wrote in message
news:C28zh.38401$Oa.29862@.edtnps82...
> Hi friends,
> In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
> get rebuilt too along with it, and it's clear why.
> In 2005, as far as I know, NCI don't get rebuilt along with CI whatsoever.
> Why is that?
> Thanks in advance.
>|||Thanks Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJ5Ee9KTHHA.4252@.TK2MSFTNGP05.phx.gbl...
> Because the uniqueifier that is used in 2005 does not change when the
> clustered index is rebuilt. So the key appended to the non-clustered index
> will still point to the proper clustered index row after a rebuild.
> --
> Andrew J. Kelly SQL MVP
> "Falconer" <me@.isp.net> wrote in message
> news:C28zh.38401$Oa.29862@.edtnps82...
>> Hi friends,
>> In SQL Server 2000 if non-unique CI gets rebuilt then all NCI for a table
>> get rebuilt too along with it, and it's clear why.
>> In 2005, as far as I know, NCI don't get rebuilt along with CI
>> whatsoever. Why is that?
>> Thanks in advance.
>>
>

clustered index on nvarchar column or int...

Users can approach their userprofile on my site using:www.mysite.com/name=peter
Name is a unique value within my database (db type: nvarchar(50))

Now, I have created a clustered index on the username column.
However, IMHO its faster to create a clustered index on the (also unique) usercode column since that is of type int.

BUT since a user can approach my site based on username I feel that I HAVE to live with this setback in performance...

Is that true or is there a better way to solve this issue?Your clustered index does not have to include the primary key column. Check out this page fortips on performance tuning clustered indexes.

clustered index and nonclustered index

I have a table with 2 indexes:
1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
2 - nonclustered, unique, unique key located on PRIMARY
the second index it's over three columns (all int).
If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
hope than a "select * from table" returns the following:
1, 3, 7, 7
2, 3, 5, 6
but instead i get the following:
2, 3, 5, 6
1, 3, 7, 7
why this happen ? should'nt the PK clustered index order the results by the
first column? how can i make that results can be like the first case?
thanks in advance
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
>
The table's physical design does not guarantee any logical ordering to
results. The only way to get ordered results is to use an ORDER BY clause
in the query.
David
|||With SQL, there is NO guarantee about the order of data retrieval UNLESS you
explicitly request the order by using the ORDER BY statement.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>
|||I have found that doing a reindex seems to order the rows correctly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>

clustered index and nonclustered index

I have a table with 2 indexes:
1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
2 - nonclustered, unique, unique key located on PRIMARY
the second index it's over three columns (all int).
If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
hope than a "select * from table" returns the following:
1, 3, 7, 7
2, 3, 5, 6
but instead i get the following:
2, 3, 5, 6
1, 3, 7, 7
why this happen ? should'nt the PK clustered index order the results by the
first column? how can i make that results can be like the first case?
thanks in advance"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
>
The table's physical design does not guarantee any logical ordering to
results. The only way to get ordered results is to use an ORDER BY clause
in the query.
David|||With SQL, there is NO guarantee about the order of data retrieval UNLESS you
explicitly request the order by using the ORDER BY statement.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>|||I have found that doing a reindex seems to order the rows correctly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>|||byteman,
As mentioned by others, the only way to guarantee any sorting is to use
the ORDER BY clause.
If the four columns you describe are the entire table, then the
optimizer could use the clustered index to satisfy the query, or use
index 2. Both cover the query. Index 1 covers the query because the leaf
level includes all table column. Index 2 covers the query because the
clustered index key is always included in a nonclustered index.
HTH,
Gert-Jan
byteman wrote:
> I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by th
e
> first column? how can i make that results can be like the first case?
> thanks in advance

clustered index and nonclustered index

I have a table with 2 indexes:
1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
2 - nonclustered, unique, unique key located on PRIMARY
the second index it's over three columns (all int).
If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
hope than a "select * from table" returns the following:
1, 3, 7, 7
2, 3, 5, 6
but instead i get the following:
2, 3, 5, 6
1, 3, 7, 7
why this happen ? should'nt the PK clustered index order the results by the
first column? how can i make that results can be like the first case?
thanks in advance"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
>
The table's physical design does not guarantee any logical ordering to
results. The only way to get ordered results is to use an ORDER BY clause
in the query.
David|||With SQL, there is NO guarantee about the order of data retrieval UNLESS you
explicitly request the order by using the ORDER BY statement.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>|||I have found that doing a reindex seems to order the rows correctly.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"byteman" <byteman@.discussions.microsoft.com> wrote in message
news:4282B889-3616-4598-AE8F-9BF3D2453DCD@.microsoft.com...
>I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by
> the
> first column? how can i make that results can be like the first case?
> thanks in advance
>|||byteman,
As mentioned by others, the only way to guarantee any sorting is to use
the ORDER BY clause.
If the four columns you describe are the entire table, then the
optimizer could use the clustered index to satisfy the query, or use
index 2. Both cover the query. Index 1 covers the query because the leaf
level includes all table column. Index 2 covers the query because the
clustered index key is always included in a nonclustered index.
HTH,
Gert-Jan
byteman wrote:
> I have a table with 2 indexes:
> 1 - clustered, unique, primary key located on PRIMARY (identity 1,1)
> 2 - nonclustered, unique, unique key located on PRIMARY
> the second index it's over three columns (all int).
> If I insert the values 3, 7, 7 and then insert the values 3, 5, 6, I would
> hope than a "select * from table" returns the following:
> 1, 3, 7, 7
> 2, 3, 5, 6
> but instead i get the following:
> 2, 3, 5, 6
> 1, 3, 7, 7
> why this happen ? should'nt the PK clustered index order the results by the
> first column? how can i make that results can be like the first case?
> thanks in advance

Clustered constraint v clustered index

I inherited a database where the designer uses unique clustered constraints instead of indexes for primary keys.

Now as far as I can make out, there is no performance issue because it is clustered. My question is, why only create a unique clustered constraint instead of a unique clustered index?

My thinking is that a primary key should be ceated as an index rather than a constraint. Does it matter?

Hi Kenster.

A unique clustered constraint, a unique clustered index, and a primary key are all really different objects/types.

A primary key is a constraint, so when you create a primary key, you'll end up with a primary key constraint to enforce it...a primary key uses a special unique constraint behind the scenes as well.

A unique constraint (whether clustered or not) uses a similar structure as a unique index (again, whether clustered or not) to enforce uniqueness (and to provide seeking when appropriate)...i.e. a b-tree structure...

In reality, the differences between creating a unique index and a unique constraint are pretty minimal (there are a few though...just do a web search for it and you'll find lots of articles discussing it).

HTH,

|||So is one better than the other when creating a primary key?|||

When you create a primary key, you have no option - you get a primary key constraint, period...behind the scenes, you end up with a unique index on the columns that make up the key and absolutely no null values are allowed.

As for a unique constraint vs. unique index, it's not to say that one is any better than the other, they both provide very, very, very similar functionality - If you create a unique constraint, you end up with a unique index behind the scenes.

Differences between constraints vs. indexes:

The columns that make up the unique key can allow nulls but not for more than one complete key.

A unique key can be referenced by a foreign key constraint and a column which has only a unique index cannot be referenced.

Constraints are checked before indexes and this can lead to a large multi-row insert/select or update to fail before modification.

There's probably some more as well...