Index Rebuilds on Clustered NON-UNIQUE Index

I have read (elsewhere, not in this forum) that if you have a clustered "NON-
UNIQUE" index, that if you rebuild this type of index, the associated non-
clustered indexes on this same table are rebuilt automatically.
The basis for this is that when a "NON-UNIQUE" clustered index is created, a
8-byte unique identifier is added to non-unique key for each duplicate row,
making it a unique "clusturing key value". When this index is rebuilt, this 8-
byte identifier is updated, if necessary. This causes the "clustering key
value" to change, and there-by effecting the non-clustered indexes; resulting
in their rebuild.
So this is what I have done:
1. I issue a DBCC ShowContig to get before reindexing results.
2. I then execute the following on a table with a clustered "NON-UNIQUE"
index:
DBCC DBReindex (',Table_Name', 'Name of Clustered NON-UNIQUE Index', 90)
3. I issue a DBCC ShowContig to get the after reindexing results.
When I compare the Before Showcontig results to the After Showcontig results,
comparing the ScanDensity and LogicalFragmentation values, the Clustered "NON-
UNIQUE" index has been rebuilt and the fragmentation no longer exists, where
the ScanDensity is now 100.00 and the Logical Fragmentation is 0.00.
So far, all is well and good. However, from what I have read, as stated in
the second paragraph above, "When this index [a clustered "NON-UNIQUE" index]
is rebuilt, this 8-byte identifier is updated, if necessary. This causes the
"clustering key value" to change, and there-by effecting the non-clustered
indexes; resulting in their rebuild." How can this be true, when the Before
and After Showcontig results show the clustered "NON-UNIQUE" index was
rebuilt (the fragmentation no longer exists), yet the nonclustered indexes on
the table remain fragmented? It appears the rebuild of the clustered "NON-
UNIQUE" index DID NOT rebuild the non-clustered indexes simply by rebuilding
the clustered "NON-UNIQUE" index. What this tells me is that simply
rebuilding the clustered "NON-UNIQUE" index, does not "automatically" rebuild
the nonclustered indexes on the table. The nonclustered indexes on the table
have to be rebuilt too in order to reduce their individual fragmentation. Is
that correct?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1What version and service pack are you using? In SQL2005 this is no longer
true and may also not be true of the latest sp in 2000 (but I am not
positive on that one). They changed the way in which they assign the
uniqueafier and it is not necessary to rebuild the non-clustered indexes
when the clustered is rebuilt. If your goal is to rebuild them all you
should simply just put the table name in the DBREINDEX command and do not
specify any indexes. Then they will all be rebuilt in the most efficient
order.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:680e3c18982e8@.uwe...
>I have read (elsewhere, not in this forum) that if you have a clustered
>"NON-
> UNIQUE" index, that if you rebuild this type of index, the associated non-
> clustered indexes on this same table are rebuilt automatically.
> The basis for this is that when a "NON-UNIQUE" clustered index is created,
> a
> 8-byte unique identifier is added to non-unique key for each duplicate
> row,
> making it a unique "clusturing key value". When this index is rebuilt,
> this 8-
> byte identifier is updated, if necessary. This causes the "clustering key
> value" to change, and there-by effecting the non-clustered indexes;
> resulting
> in their rebuild.
> So this is what I have done:
> 1. I issue a DBCC ShowContig to get before reindexing results.
> 2. I then execute the following on a table with a clustered "NON-UNIQUE"
> index:
> DBCC DBReindex (',Table_Name', 'Name of Clustered NON-UNIQUE Index', 90)
> 3. I issue a DBCC ShowContig to get the after reindexing results.
> When I compare the Before Showcontig results to the After Showcontig
> results,
> comparing the ScanDensity and LogicalFragmentation values, the Clustered
> "NON-
> UNIQUE" index has been rebuilt and the fragmentation no longer exists,
> where
> the ScanDensity is now 100.00 and the Logical Fragmentation is 0.00.
> So far, all is well and good. However, from what I have read, as stated in
> the second paragraph above, "When this index [a clustered "NON-UNIQUE"
> index]
> is rebuilt, this 8-byte identifier is updated, if necessary. This causes
> the
> "clustering key value" to change, and there-by effecting the non-clustered
> indexes; resulting in their rebuild." How can this be true, when the
> Before
> and After Showcontig results show the clustered "NON-UNIQUE" index was
> rebuilt (the fragmentation no longer exists), yet the nonclustered indexes
> on
> the table remain fragmented? It appears the rebuild of the clustered "NON-
> UNIQUE" index DID NOT rebuild the non-clustered indexes simply by
> rebuilding
> the clustered "NON-UNIQUE" index. What this tells me is that simply
> rebuilding the clustered "NON-UNIQUE" index, does not "automatically"
> rebuild
> the nonclustered indexes on the table. The nonclustered indexes on the
> table
> have to be rebuilt too in order to reduce their individual fragmentation.
> Is
> that correct?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||I am running SQL 2000, standard edition, SP4.
I was trying to get away from rebuilding them all, if it eliminated the
fragmentation too. But from my findings, the only way to eliminate the
fragmentation on nonclustered indexes on the same table as a clustered NON-
UNIQUE index is to rebuild them all.
Andrew J. Kelly wrote:
>What version and service pack are you using? In SQL2005 this is no longer
>true and may also not be true of the latest sp in 2000 (but I am not
>positive on that one). They changed the way in which they assign the
>uniqueafier and it is not necessary to rebuild the non-clustered indexes
>when the clustered is rebuilt. If your goal is to rebuild them all you
>should simply just put the table name in the DBREINDEX command and do not
>specify any indexes. Then they will all be rebuilt in the most efficient
>order.
>>I have read (elsewhere, not in this forum) that if you have a clustered
>>"NON-
>[quoted text clipped - 49 lines]
>> Is
>> that correct?
--
Message posted via http://www.sqlmonster.com|||I am a little confused by what you say. The only way to remove fragmentation
on any index is to rebuild it regardless of clustered or non-clustered. The
fragmentation is unique to each index not just the clustered index.
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6810fddec11f9@.uwe...
>I am running SQL 2000, standard edition, SP4.
> I was trying to get away from rebuilding them all, if it eliminated the
> fragmentation too. But from my findings, the only way to eliminate the
> fragmentation on nonclustered indexes on the same table as a clustered
> NON-
> UNIQUE index is to rebuild them all.
> Andrew J. Kelly wrote:
>>What version and service pack are you using? In SQL2005 this is no longer
>>true and may also not be true of the latest sp in 2000 (but I am not
>>positive on that one). They changed the way in which they assign the
>>uniqueafier and it is not necessary to rebuild the non-clustered indexes
>>when the clustered is rebuilt. If your goal is to rebuild them all you
>>should simply just put the table name in the DBREINDEX command and do not
>>specify any indexes. Then they will all be rebuilt in the most efficient
>>order.
>>I have read (elsewhere, not in this forum) that if you have a clustered
>>"NON-
>>[quoted text clipped - 49 lines]
>> Is
>> that correct?
> --
> Message posted via http://www.sqlmonster.com
>|||I'm wondering why you want to rebuild the CIX without rebuilding the NCIXs?
Given that most queries are serviced by NCIXs & they fragment faster than
CIXs, it's usually more important to rebuild NCIXs than CIXs so I'm
wondering why you'd want to rebuild CIXs without rebuilding NCIXs?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:680e3c18982e8@.uwe...
>I have read (elsewhere, not in this forum) that if you have a clustered
>"NON-
> UNIQUE" index, that if you rebuild this type of index, the associated non-
> clustered indexes on this same table are rebuilt automatically.
> The basis for this is that when a "NON-UNIQUE" clustered index is created,
> a
> 8-byte unique identifier is added to non-unique key for each duplicate
> row,
> making it a unique "clusturing key value". When this index is rebuilt,
> this 8-
> byte identifier is updated, if necessary. This causes the "clustering key
> value" to change, and there-by effecting the non-clustered indexes;
> resulting
> in their rebuild.
> So this is what I have done:
> 1. I issue a DBCC ShowContig to get before reindexing results.
> 2. I then execute the following on a table with a clustered "NON-UNIQUE"
> index:
> DBCC DBReindex (',Table_Name', 'Name of Clustered NON-UNIQUE Index', 90)
> 3. I issue a DBCC ShowContig to get the after reindexing results.
> When I compare the Before Showcontig results to the After Showcontig
> results,
> comparing the ScanDensity and LogicalFragmentation values, the Clustered
> "NON-
> UNIQUE" index has been rebuilt and the fragmentation no longer exists,
> where
> the ScanDensity is now 100.00 and the Logical Fragmentation is 0.00.
> So far, all is well and good. However, from what I have read, as stated in
> the second paragraph above, "When this index [a clustered "NON-UNIQUE"
> index]
> is rebuilt, this 8-byte identifier is updated, if necessary. This causes
> the
> "clustering key value" to change, and there-by effecting the non-clustered
> indexes; resulting in their rebuild." How can this be true, when the
> Before
> and After Showcontig results show the clustered "NON-UNIQUE" index was
> rebuilt (the fragmentation no longer exists), yet the nonclustered indexes
> on
> the table remain fragmented? It appears the rebuild of the clustered "NON-
> UNIQUE" index DID NOT rebuild the non-clustered indexes simply by
> rebuilding
> the clustered "NON-UNIQUE" index. What this tells me is that simply
> rebuilding the clustered "NON-UNIQUE" index, does not "automatically"
> rebuild
> the nonclustered indexes on the table. The nonclustered indexes on the
> table
> have to be rebuilt too in order to reduce their individual fragmentation.
> Is
> that correct?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||When I run the repro script below, I definitely see the elimination of
logical fragmentation in the nonclustered index.
When I run this on SQL Server 2000 Enterprise Edition (8.00.2040), then
these are the relevant lines in the output. Notice there are two DBCC
messages, indicating the rebuilt of the NCIX (which isn't part of the
repro script).
Table: 'test' (1504827452); index ID: 1, database ID: 7
- Scan Density [Best Count:Actual Count]......: 12.78% [255:1995]
- Logical Scan Fragmentation ..............: 48.72%
Table: 'test' (1504827452); index ID: 2, database ID: 7
- Scan Density [Best Count:Actual Count]......: 12.61% [221:1752]
- Logical Scan Fragmentation ..............: 48.70%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Table: 'test' (1504827452); index ID: 1, database ID: 7
- Scan Density [Best Count:Actual Count]......: 99.04% [206:208]
- Logical Scan Fragmentation ..............: 0.06%
Table: 'test' (1504827452); index ID: 2, database ID: 7
- Scan Density [Best Count:Actual Count]......: 99.47% [188:189]
- Logical Scan Fragmentation ..............: 0.07%
There is the repro script:
create table test
(id int not null
,rest varchar(16) not null
)
create clustered index test_id on test(id)
create index test_rest on test(rest)
go
set nocount on
declare @.i int
set @.i=100
while @.i>0
begin
set @.i=@.i-1
insert into test
select id,checksum(newid())
from sysobjects
end
dbcc showcontig("test",'test_id')
dbcc showcontig("test",'test_rest')
go
dbcc dbreindex("test",'test_id',90)
go
dbcc showcontig("test",'test_id')
dbcc showcontig("test",'test_rest')
go
drop table test
By the way: AFAIK, the uniquefier that is added to the nonunique
clustered key is 4 bytes, not 8 bytes.
HTH,
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> I have read (elsewhere, not in this forum) that if you have a clustered "NON-
> UNIQUE" index, that if you rebuild this type of index, the associated non-
> clustered indexes on this same table are rebuilt automatically.
> The basis for this is that when a "NON-UNIQUE" clustered index is created, a
> 8-byte unique identifier is added to non-unique key for each duplicate row,
> making it a unique "clusturing key value". When this index is rebuilt, this 8-
> byte identifier is updated, if necessary. This causes the "clustering key
> value" to change, and there-by effecting the non-clustered indexes; resulting
> in their rebuild.
> So this is what I have done:
> 1. I issue a DBCC ShowContig to get before reindexing results.
> 2. I then execute the following on a table with a clustered "NON-UNIQUE"
> index:
> DBCC DBReindex (',Table_Name', 'Name of Clustered NON-UNIQUE Index', 90)
> 3. I issue a DBCC ShowContig to get the after reindexing results.
> When I compare the Before Showcontig results to the After Showcontig results,
> comparing the ScanDensity and LogicalFragmentation values, the Clustered "NON-
> UNIQUE" index has been rebuilt and the fragmentation no longer exists, where
> the ScanDensity is now 100.00 and the Logical Fragmentation is 0.00.
> So far, all is well and good. However, from what I have read, as stated in
> the second paragraph above, "When this index [a clustered "NON-UNIQUE" index]
> is rebuilt, this 8-byte identifier is updated, if necessary. This causes the
> "clustering key value" to change, and there-by effecting the non-clustered
> indexes; resulting in their rebuild." How can this be true, when the Before
> and After Showcontig results show the clustered "NON-UNIQUE" index was
> rebuilt (the fragmentation no longer exists), yet the nonclustered indexes on
> the table remain fragmented? It appears the rebuild of the clustered "NON-
> UNIQUE" index DID NOT rebuild the non-clustered indexes simply by rebuilding
> the clustered "NON-UNIQUE" index. What this tells me is that simply
> rebuilding the clustered "NON-UNIQUE" index, does not "automatically" rebuild
> the nonclustered indexes on the table. The nonclustered indexes on the table
> have to be rebuilt too in order to reduce their individual fragmentation. Is
> that correct?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||Gert-Jan
>> > By the way: AFAIK, the uniquefier that is added to the nonunique
> clustered key is 4 bytes, not 8 bytes.
>
This is correct, and can be easily observed by running DBCC PAGE.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:453BE6C4.2DD97820@.toomuchspamalready.nl...
> When I run the repro script below, I definitely see the elimination of
> logical fragmentation in the nonclustered index.
> When I run this on SQL Server 2000 Enterprise Edition (8.00.2040), then
> these are the relevant lines in the output. Notice there are two DBCC
> messages, indicating the rebuilt of the NCIX (which isn't part of the
> repro script).
> Table: 'test' (1504827452); index ID: 1, database ID: 7
> - Scan Density [Best Count:Actual Count]......: 12.78% [255:1995]
> - Logical Scan Fragmentation ..............: 48.72%
> Table: 'test' (1504827452); index ID: 2, database ID: 7
> - Scan Density [Best Count:Actual Count]......: 12.61% [221:1752]
> - Logical Scan Fragmentation ..............: 48.70%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Table: 'test' (1504827452); index ID: 1, database ID: 7
> - Scan Density [Best Count:Actual Count]......: 99.04% [206:208]
> - Logical Scan Fragmentation ..............: 0.06%
> Table: 'test' (1504827452); index ID: 2, database ID: 7
> - Scan Density [Best Count:Actual Count]......: 99.47% [188:189]
> - Logical Scan Fragmentation ..............: 0.07%
> There is the repro script:
> create table test
> (id int not null
> ,rest varchar(16) not null
> )
> create clustered index test_id on test(id)
> create index test_rest on test(rest)
> go
> set nocount on
> declare @.i int
> set @.i=100
> while @.i>0
> begin
> set @.i=@.i-1
> insert into test
> select id,checksum(newid())
> from sysobjects
> end
>
> dbcc showcontig("test",'test_id')
> dbcc showcontig("test",'test_rest')
> go
> dbcc dbreindex("test",'test_id',90)
> go
> dbcc showcontig("test",'test_id')
> dbcc showcontig("test",'test_rest')
> go
> drop table test
>
> By the way: AFAIK, the uniquefier that is added to the nonunique
> clustered key is 4 bytes, not 8 bytes.
> HTH,
> Gert-Jan
>
> "cbrichards via SQLMonster.com" wrote:
>> I have read (elsewhere, not in this forum) that if you have a clustered
>> "NON-
>> UNIQUE" index, that if you rebuild this type of index, the associated
>> non-
>> clustered indexes on this same table are rebuilt automatically.
>> The basis for this is that when a "NON-UNIQUE" clustered index is
>> created, a
>> 8-byte unique identifier is added to non-unique key for each duplicate
>> row,
>> making it a unique "clusturing key value". When this index is rebuilt,
>> this 8-
>> byte identifier is updated, if necessary. This causes the "clustering key
>> value" to change, and there-by effecting the non-clustered indexes;
>> resulting
>> in their rebuild.
>> So this is what I have done:
>> 1. I issue a DBCC ShowContig to get before reindexing results.
>> 2. I then execute the following on a table with a clustered "NON-UNIQUE"
>> index:
>> DBCC DBReindex (',Table_Name', 'Name of Clustered NON-UNIQUE Index',
>> 90)
>> 3. I issue a DBCC ShowContig to get the after reindexing results.
>> When I compare the Before Showcontig results to the After Showcontig
>> results,
>> comparing the ScanDensity and LogicalFragmentation values, the Clustered
>> "NON-
>> UNIQUE" index has been rebuilt and the fragmentation no longer exists,
>> where
>> the ScanDensity is now 100.00 and the Logical Fragmentation is 0.00.
>> So far, all is well and good. However, from what I have read, as stated
>> in
>> the second paragraph above, "When this index [a clustered "NON-UNIQUE"
>> index]
>> is rebuilt, this 8-byte identifier is updated, if necessary. This causes
>> the
>> "clustering key value" to change, and there-by effecting the
>> non-clustered
>> indexes; resulting in their rebuild." How can this be true, when the
>> Before
>> and After Showcontig results show the clustered "NON-UNIQUE" index was
>> rebuilt (the fragmentation no longer exists), yet the nonclustered
>> indexes on
>> the table remain fragmented? It appears the rebuild of the clustered
>> "NON-
>> UNIQUE" index DID NOT rebuild the non-clustered indexes simply by
>> rebuilding
>> the clustered "NON-UNIQUE" index. What this tells me is that simply
>> rebuilding the clustered "NON-UNIQUE" index, does not "automatically"
>> rebuild
>> the nonclustered indexes on the table. The nonclustered indexes on the
>> table
>> have to be rebuilt too in order to reduce their individual fragmentation.
>> Is
>> that correct?
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||Sorry Andrew. Let me try to clarify. I have a procedure (on SQL 2000, SP4)
that rebuilds all indexes (clustered and nonclustered) using DBCC DBReindex
based upon Scan Density and Logical Fragmentation. I have a window of time in
order to perform this every week. As the database grows, the rebuilding of
indexing takes longer, but my window of time to perform this weekly index
rebuild stays the same. The day will come when the rebuild will take longer
than the window of time allotted.
As I stated in my original email, I read that if you rebuild a "NON-UNIQUE"
Clustered Index, this would automatically rebuild all the nonclustered
indexes on the table too. That would save a little bit of time, not having to
loop through stored procedure code to redundantly rebuild the nonclustered
indexes individually, since they would get rebuilt automatically.
So what I meant when I said getting "away from rebuilding them all", was
actually getting away from looping through stored procedure code to rebuild
the nonclustered indexes individually, when they are already getting rebuilt
when the "NON-UNIQUE" clustered index is getting rebuilt.
Andrew J. Kelly wrote:
>I am a little confused by what you say. The only way to remove fragmentation
>on any index is to rebuild it regardless of clustered or non-clustered. The
>fragmentation is unique to each index not just the clustered index.
>>I am running SQL 2000, standard edition, SP4.
>[quoted text clipped - 18 lines]
>> Is
>> that correct?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||I ran your script and came up with the same results.
I then ran your script, to go against an existing table of mine that contains
approximately 220,000 rows, and has a "NON-UNIQUE" clustered index, and the
results show the "NON-UNIQUE" clustered index was rebuilt, but the
nonclustered index was not rebuilt. I have included the DBCC Showcontig
results and the Create statement for the table and indexes below. Can you
explain why it works (rebuilding the clustered, rebuilds the nonclustered) in
your example, but does not work for my existing table?
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (562101043); index ID: 1, database ID: 13
- Pages Scanned........................: 12364
- Scan Density [Best Count:Actual Count]......: 41.19% [1546:3753]
- Logical Scan Fragmentation ..............: 20.71%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (562101043); index ID: 42, database ID: 13
LEAF level scan performed.
- Pages Scanned........................: 2988
- Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
- Logical Scan Fragmentation ..............: 29.79%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
dbcc showcontig("MyTable",'PK_MyTable')
dbcc showcontig("MyTable",'IDX_Sort')
go
dbcc dbreindex("MyTable",'PK_MyTable',90)
go
dbcc showcontig("MyTable",'PK_MyTable')
dbcc showcontig("MyTable",'IDX_Sort')
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (562101043); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned........................: 11645
- Scan Density [Best Count:Actual Count]......: 100.00% [1456:1456]
- Logical Scan Fragmentation ..............: 0.00%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (562101043); index ID: 42, database ID: 13
LEAF level scan performed.
- Pages Scanned........................: 2988
- Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
- Logical Scan Fragmentation ..............: 29.79%
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
CREATE TABLE [dbo].[MyTable](
[KeyID] [int] NOT NULL,
[PersonID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [varchar](35) NOT NULL,
[FirstName] [varchar](35) NOT NULL,
[MiddleName] [varchar](35) NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
(
[PersonID] ASC,
[KeyID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IDX_Sort] ON [dbo].[MyTable]([LastName], [FirstName]
, [MiddleName], [KeyID]) ON [PRIMARY]
GO
Gert-Jan Strik wrote:
>When I run the repro script below, I definitely see the elimination of
>logical fragmentation in the nonclustered index.
>When I run this on SQL Server 2000 Enterprise Edition (8.00.2040), then
>these are the relevant lines in the output. Notice there are two DBCC
>messages, indicating the rebuilt of the NCIX (which isn't part of the
>repro script).
>Table: 'test' (1504827452); index ID: 1, database ID: 7
>- Scan Density [Best Count:Actual Count]......: 12.78% [255:1995]
>- Logical Scan Fragmentation ..............: 48.72%
>Table: 'test' (1504827452); index ID: 2, database ID: 7
>- Scan Density [Best Count:Actual Count]......: 12.61% [221:1752]
>- Logical Scan Fragmentation ..............: 48.70%
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>Table: 'test' (1504827452); index ID: 1, database ID: 7
>- Scan Density [Best Count:Actual Count]......: 99.04% [206:208]
>- Logical Scan Fragmentation ..............: 0.06%
>Table: 'test' (1504827452); index ID: 2, database ID: 7
>- Scan Density [Best Count:Actual Count]......: 99.47% [188:189]
>- Logical Scan Fragmentation ..............: 0.07%
>There is the repro script:
>create table test
>(id int not null
>,rest varchar(16) not null
>)
>create clustered index test_id on test(id)
>create index test_rest on test(rest)
>go
>set nocount on
>declare @.i int
>set @.i=100
>while @.i>0
>begin
> set @.i=@.i-1
> insert into test
> select id,checksum(newid())
> from sysobjects
>end
>dbcc showcontig("test",'test_id')
>dbcc showcontig("test",'test_rest')
>go
>dbcc dbreindex("test",'test_id',90)
>go
>dbcc showcontig("test",'test_id')
>dbcc showcontig("test",'test_rest')
>go
>drop table test
>By the way: AFAIK, the uniquefier that is added to the nonunique
>clustered key is 4 bytes, not 8 bytes.
>HTH,
>Gert-Jan
>> I have read (elsewhere, not in this forum) that if you have a clustered "NON-
>> UNIQUE" index, that if you rebuild this type of index, the associated non-
>[quoted text clipped - 37 lines]
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
--
Message posted via http://www.sqlmonster.com|||The only explanation I can think of, is that the clustered index does
not contain any duplicate value. Because then, rebuilding the NC indexes
would not be possible. It is quite likely that there are no duplicates
because of the use of the Identity.
You can check your table with the query below. If it returns nothing,
then there are no duplicates.
SELECT PersonID, KeyID
FROM MyTable
GROUP BY PersonID, KeyID
HAVING COUNT(*)>1
You could try adding two duplicate dummy rows...
HTH,
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> I ran your script and came up with the same results.
> I then ran your script, to go against an existing table of mine that contains
> approximately 220,000 rows, and has a "NON-UNIQUE" clustered index, and the
> results show the "NON-UNIQUE" clustered index was rebuilt, but the
> nonclustered index was not rebuilt. I have included the DBCC Showcontig
> results and the Create statement for the table and indexes below. Can you
> explain why it works (rebuilding the clustered, rebuilds the nonclustered) in
> your example, but does not work for my existing table?
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (562101043); index ID: 1, database ID: 13
> - Pages Scanned........................: 12364
> - Scan Density [Best Count:Actual Count]......: 41.19% [1546:3753]
> - Logical Scan Fragmentation ..............: 20.71%
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (562101043); index ID: 42, database ID: 13
> LEAF level scan performed.
> - Pages Scanned........................: 2988
> - Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
> - Logical Scan Fragmentation ..............: 29.79%
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> dbcc showcontig("MyTable",'PK_MyTable')
> dbcc showcontig("MyTable",'IDX_Sort')
> go
> dbcc dbreindex("MyTable",'PK_MyTable',90)
> go
> dbcc showcontig("MyTable",'PK_MyTable')
> dbcc showcontig("MyTable",'IDX_Sort')
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (562101043); index ID: 1, database ID: 13
> TABLE level scan performed.
> - Pages Scanned........................: 11645
> - Scan Density [Best Count:Actual Count]......: 100.00% [1456:1456]
> - Logical Scan Fragmentation ..............: 0.00%
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> DBCC SHOWCONTIG scanning 'MyTable' table...
> Table: 'MyTable' (562101043); index ID: 42, database ID: 13
> LEAF level scan performed.
> - Pages Scanned........................: 2988
> - Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
> - Logical Scan Fragmentation ..............: 29.79%
> DBCC execution completed. If DBCC printed error messages, contact your system
> administrator.
> CREATE TABLE [dbo].[MyTable](
> [KeyID] [int] NOT NULL,
> [PersonID] [int] IDENTITY(1,1) NOT NULL,
> [LastName] [varchar](35) NOT NULL,
> [FirstName] [varchar](35) NOT NULL,
> [MiddleName] [varchar](35) NOT NULL,
> CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
> (
> [PersonID] ASC,
> [KeyID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IDX_Sort] ON [dbo].[MyTable]([LastName], [FirstName]
> , [MiddleName], [KeyID]) ON [PRIMARY]
> GO
> Gert-Jan Strik wrote:
> >When I run the repro script below, I definitely see the elimination of
> >logical fragmentation in the nonclustered index.
> >
> >When I run this on SQL Server 2000 Enterprise Edition (8.00.2040), then
> >these are the relevant lines in the output. Notice there are two DBCC
> >messages, indicating the rebuilt of the NCIX (which isn't part of the
> >repro script).
> >
> >Table: 'test' (1504827452); index ID: 1, database ID: 7
> >- Scan Density [Best Count:Actual Count]......: 12.78% [255:1995]
> >- Logical Scan Fragmentation ..............: 48.72%
> >
> >Table: 'test' (1504827452); index ID: 2, database ID: 7
> >- Scan Density [Best Count:Actual Count]......: 12.61% [221:1752]
> >- Logical Scan Fragmentation ..............: 48.70%
> >
> >DBCC execution completed. If DBCC printed error messages, contact your
> >system administrator.
> >DBCC execution completed. If DBCC printed error messages, contact your
> >system administrator.
> >
> >Table: 'test' (1504827452); index ID: 1, database ID: 7
> >- Scan Density [Best Count:Actual Count]......: 99.04% [206:208]
> >- Logical Scan Fragmentation ..............: 0.06%
> >
> >Table: 'test' (1504827452); index ID: 2, database ID: 7
> >- Scan Density [Best Count:Actual Count]......: 99.47% [188:189]
> >- Logical Scan Fragmentation ..............: 0.07%
> >
> >There is the repro script:
> >
> >create table test
> >(id int not null
> >,rest varchar(16) not null
> >)
> >create clustered index test_id on test(id)
> >create index test_rest on test(rest)
> >go
> >
> >set nocount on
> >declare @.i int
> >set @.i=100
> >while @.i>0
> >begin
> > set @.i=@.i-1
> >
> > insert into test
> > select id,checksum(newid())
> > from sysobjects
> >end
> >
> >dbcc showcontig("test",'test_id')
> >dbcc showcontig("test",'test_rest')
> >go
> >
> >dbcc dbreindex("test",'test_id',90)
> >go
> >
> >dbcc showcontig("test",'test_id')
> >dbcc showcontig("test",'test_rest')
> >
> >go
> >drop table test
> >
> >By the way: AFAIK, the uniquefier that is added to the nonunique
> >clustered key is 4 bytes, not 8 bytes.
> >
> >HTH,
> >Gert-Jan
> >
> >> I have read (elsewhere, not in this forum) that if you have a clustered "NON-
> >> UNIQUE" index, that if you rebuild this type of index, the associated non-
> >[quoted text clipped - 37 lines]
> >> Message posted via SQLMonster.com
> >> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
> --
> Message posted via http://www.sqlmonster.com|||Gert-Jan Strik wrote:
> The only explanation I can think of, is that the clustered index does
> not contain any duplicate value. Because then, rebuilding the NC indexes
> would not be possible. It is quite likely that there are no duplicates
> because of the use of the Identity.
I meant to say "necessary" here, instead of "possible".
> You can check your table with the query below. If it returns nothing,
> then there are no duplicates.
> SELECT PersonID, KeyID
> FROM MyTable
> GROUP BY PersonID, KeyID
> HAVING COUNT(*)>1
> You could try adding two duplicate dummy rows...
> HTH,
> Gert-Jan|||Ahh, forget this theory. I just added an identity column to the repro
script (and to the clustered index), and a DBCC DBREINDEX of the
clustered index would still cause the nonclustered index to be rebuilt.
I have no idea why it doesn't work for you. Are you sure you have SQL
Server SP4a installed? What version are you running (the output of
SELECT @.@.version)? I think it should be 8.00.2039 or 8.00.2040.
Gert-Jan
Gert-Jan Strik wrote:
> The only explanation I can think of, is that the clustered index does
> not contain any duplicate value. Because then, rebuilding the NC indexes
> would not be possible. It is quite likely that there are no duplicates
> because of the use of the Identity.
> You can check your table with the query below. If it returns nothing,
> then there are no duplicates.
> SELECT PersonID, KeyID
> FROM MyTable
> GROUP BY PersonID, KeyID
> HAVING COUNT(*)>1
> You could try adding two duplicate dummy rows...
> HTH,
> Gert-Jan
> "cbrichards via SQLMonster.com" wrote:
> >
> > I ran your script and came up with the same results.
> >
> > I then ran your script, to go against an existing table of mine that contains
> > approximately 220,000 rows, and has a "NON-UNIQUE" clustered index, and the
> > results show the "NON-UNIQUE" clustered index was rebuilt, but the
> > nonclustered index was not rebuilt. I have included the DBCC Showcontig
> > results and the Create statement for the table and indexes below. Can you
> > explain why it works (rebuilding the clustered, rebuilds the nonclustered) in
> > your example, but does not work for my existing table?
> >
> > DBCC SHOWCONTIG scanning 'MyTable' table...
> > Table: 'MyTable' (562101043); index ID: 1, database ID: 13
> > - Pages Scanned........................: 12364
> > - Scan Density [Best Count:Actual Count]......: 41.19% [1546:3753]
> > - Logical Scan Fragmentation ..............: 20.71%
> > DBCC execution completed. If DBCC printed error messages, contact your system
> > administrator.
> >
> > DBCC SHOWCONTIG scanning 'MyTable' table...
> > Table: 'MyTable' (562101043); index ID: 42, database ID: 13
> > LEAF level scan performed.
> > - Pages Scanned........................: 2988
> > - Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
> > - Logical Scan Fragmentation ..............: 29.79%
> > DBCC execution completed. If DBCC printed error messages, contact your system
> > administrator.
> >
> > dbcc showcontig("MyTable",'PK_MyTable')
> > dbcc showcontig("MyTable",'IDX_Sort')
> > go
> >
> > dbcc dbreindex("MyTable",'PK_MyTable',90)
> > go
> >
> > dbcc showcontig("MyTable",'PK_MyTable')
> > dbcc showcontig("MyTable",'IDX_Sort')
> >
> > DBCC SHOWCONTIG scanning 'MyTable' table...
> > Table: 'MyTable' (562101043); index ID: 1, database ID: 13
> > TABLE level scan performed.
> > - Pages Scanned........................: 11645
> > - Scan Density [Best Count:Actual Count]......: 100.00% [1456:1456]
> > - Logical Scan Fragmentation ..............: 0.00%
> > DBCC execution completed. If DBCC printed error messages, contact your system
> > administrator.
> >
> > DBCC SHOWCONTIG scanning 'MyTable' table...
> > Table: 'MyTable' (562101043); index ID: 42, database ID: 13
> > LEAF level scan performed.
> > - Pages Scanned........................: 2988
> > - Scan Density [Best Count:Actual Count]......: 33.19% [374:1127]
> > - Logical Scan Fragmentation ..............: 29.79%
> > DBCC execution completed. If DBCC printed error messages, contact your system
> > administrator.
> >
> > CREATE TABLE [dbo].[MyTable](
> > [KeyID] [int] NOT NULL,
> > [PersonID] [int] IDENTITY(1,1) NOT NULL,
> > [LastName] [varchar](35) NOT NULL,
> > [FirstName] [varchar](35) NOT NULL,
> > [MiddleName] [varchar](35) NOT NULL,
> >
> > CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
> > (
> > [PersonID] ASC,
> > [KeyID] ASC
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE CLUSTERED INDEX [IDX_Sort] ON [dbo].[MyTable]([LastName], [FirstName]
> > , [MiddleName], [KeyID]) ON [PRIMARY]
> > GO
> >
> > Gert-Jan Strik wrote:
> > >When I run the repro script below, I definitely see the elimination of
> > >logical fragmentation in the nonclustered index.
> > >
> > >When I run this on SQL Server 2000 Enterprise Edition (8.00.2040), then
> > >these are the relevant lines in the output. Notice there are two DBCC
> > >messages, indicating the rebuilt of the NCIX (which isn't part of the
> > >repro script).
> > >
> > >Table: 'test' (1504827452); index ID: 1, database ID: 7
> > >- Scan Density [Best Count:Actual Count]......: 12.78% [255:1995]
> > >- Logical Scan Fragmentation ..............: 48.72%
> > >
> > >Table: 'test' (1504827452); index ID: 2, database ID: 7
> > >- Scan Density [Best Count:Actual Count]......: 12.61% [221:1752]
> > >- Logical Scan Fragmentation ..............: 48.70%
> > >
> > >DBCC execution completed. If DBCC printed error messages, contact your
> > >system administrator.
> > >DBCC execution completed. If DBCC printed error messages, contact your
> > >system administrator.
> > >
> > >Table: 'test' (1504827452); index ID: 1, database ID: 7
> > >- Scan Density [Best Count:Actual Count]......: 99.04% [206:208]
> > >- Logical Scan Fragmentation ..............: 0.06%
> > >
> > >Table: 'test' (1504827452); index ID: 2, database ID: 7
> > >- Scan Density [Best Count:Actual Count]......: 99.47% [188:189]
> > >- Logical Scan Fragmentation ..............: 0.07%
> > >
> > >There is the repro script:
> > >
> > >create table test
> > >(id int not null
> > >,rest varchar(16) not null
> > >)
> > >create clustered index test_id on test(id)
> > >create index test_rest on test(rest)
> > >go
> > >
> > >set nocount on
> > >declare @.i int
> > >set @.i=100
> > >while @.i>0
> > >begin
> > > set @.i=@.i-1
> > >
> > > insert into test
> > > select id,checksum(newid())
> > > from sysobjects
> > >end
> > >
> > >dbcc showcontig("test",'test_id')
> > >dbcc showcontig("test",'test_rest')
> > >go
> > >
> > >dbcc dbreindex("test",'test_id',90)
> > >go
> > >
> > >dbcc showcontig("test",'test_id')
> > >dbcc showcontig("test",'test_rest')
> > >
> > >go
> > >drop table test
> > >
> > >By the way: AFAIK, the uniquefier that is added to the nonunique
> > >clustered key is 4 bytes, not 8 bytes.
> > >
> > >HTH,
> > >Gert-Jan
> > >
> > >> I have read (elsewhere, not in this forum) that if you have a clustered "NON-
> > >> UNIQUE" index, that if you rebuild this type of index, the associated non-
> > >[quoted text clipped - 37 lines]
> > >> Message posted via SQLMonster.com
> > >> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
> >
> > --
> > Message posted via http://www.sqlmonster.com|||Yes if you want to rebuild all of them as I stated before just specify the
table name and NOT the index name. That will rebuild all indexes on the
table regardless of if the clustered index is unique or not. If you specify
the index name and it happens to be the clustered index it works in this
fashion.
DBCC DBREINDEX(TableName, CI IndexName)
CI = Unique
Rebuilds only the CI
CI = Not Uniquie
Rebuilds all indexes
I suspect that the clustered index is Unique even though you may not have
specified it as such. Can you show the actual DDL for the table with all the
indexes?
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6833048040ad2@.uwe...
> Sorry Andrew. Let me try to clarify. I have a procedure (on SQL 2000, SP4)
> that rebuilds all indexes (clustered and nonclustered) using DBCC
> DBReindex
> based upon Scan Density and Logical Fragmentation. I have a window of time
> in
> order to perform this every week. As the database grows, the rebuilding of
> indexing takes longer, but my window of time to perform this weekly index
> rebuild stays the same. The day will come when the rebuild will take
> longer
> than the window of time allotted.
> As I stated in my original email, I read that if you rebuild a
> "NON-UNIQUE"
> Clustered Index, this would automatically rebuild all the nonclustered
> indexes on the table too. That would save a little bit of time, not having
> to
> loop through stored procedure code to redundantly rebuild the nonclustered
> indexes individually, since they would get rebuilt automatically.
> So what I meant when I said getting "away from rebuilding them all", was
> actually getting away from looping through stored procedure code to
> rebuild
> the nonclustered indexes individually, when they are already getting
> rebuilt
> when the "NON-UNIQUE" clustered index is getting rebuilt.
> Andrew J. Kelly wrote:
>>I am a little confused by what you say. The only way to remove
>>fragmentation
>>on any index is to rebuild it regardless of clustered or non-clustered.
>>The
>>fragmentation is unique to each index not just the clustered index.
>>I am running SQL 2000, standard edition, SP4.
>>[quoted text clipped - 18 lines]
>> Is
>> that correct?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||I ran the statement you included in your earlier post, and indeed, the
clustered index does
not contain any duplicate values. Your theory appears to be correct.
By the way, I am running 8.00.2040.
Gert-Jan Strik wrote:
>Ahh, forget this theory. I just added an identity column to the repro
>script (and to the clustered index), and a DBCC DBREINDEX of the
>clustered index would still cause the nonclustered index to be rebuilt.
>I have no idea why it doesn't work for you. Are you sure you have SQL
>Server SP4a installed? What version are you running (the output of
>SELECT @.@.version)? I think it should be 8.00.2039 or 8.00.2040.
>Gert-Jan
>> The only explanation I can think of, is that the clustered index does
>> not contain any duplicate value. Because then, rebuilding the NC indexes
>[quoted text clipped - 166 lines]
>> > --
>> > Message posted via http://www.sqlmonster.com
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1

Index question

I have the following index:
CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
logindate]) ON [PRIMARY]
GO
Do I still need to create the following index ?
CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
GO
Thanks for any feedback........
DXC,
SQL Server stores statistics for the more left column in the key. This index
could be used for logic expressions referencing [Loginid] or ([Loginid] and
[logindate]). If you create the second index, may be SQL Server can decide to
use it because the key is shorter than the first one, so more rows can fit in
a page and less IO operations will be required.
Try some "select" statements with just the first index. If you are ok with
the response time and execution plan selected by SQL Server then do not
create the second one. Remember, indexes help sql server to find the data
faster, but also put more load for insert, delete, and update operations.
AMB
"DXC" wrote:

> I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........
|||No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
on Loginid
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
>I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........
|||That's what I thought............Thanks.
"Paul Wehland" wrote:

> No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
> on Loginid
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
>
>

Index Question

I have a series of many-to-many relationships, so obviously have a number of
'join' tables. These join tables store only the unique key from the other
tables.
Is there any advantage to indexing the fields in the join tables or is it
not necessary because the data in them is technically indexed in another
table or do they still need indexing in their own right?
Thanks
Keith
It is always good practice to create an index on foreign key column as the
linking column
( I assume you have pimary key with clustered index)
An index on a foreign key column can substantially boost the performance of
many joins.
"Keith" <@..> wrote in message news:O1DllPVSEHA.2408@.tk2msftngp13.phx.gbl...
> I have a series of many-to-many relationships, so obviously have a number
of
> 'join' tables. These join tables store only the unique key from the other
> tables.
> Is there any advantage to indexing the fields in the join tables or is it
> not necessary because the data in them is technically indexed in another
> table or do they still need indexing in their own right?
> Thanks
>

Index question

If I create two indexes for the same column, how would I know which index is
used?
for example,
create unique index UNI_INDX1 ON table1(column1 desc)
create unique index UNI_INDX1 ON table1(column1 asc)
You cannot create more than 1 index with the same name. If you run the
statement you will get the following error:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'Customers' named 'UNI_INDX1'.
If you want to see the execution plan in QA hit Ctl+k or select it from the
Query menu.
If you want to see the plan in text, use the statement
SET SHOWPLAN_ALL ON
GO
before your DML statement.
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>
|||In addition to Bryan's post:
There's absolutely no reason to create both ASC and DESC index on one column. SQL Server can
traverse an index in both directions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>
|||Bryan and Tibor,
Thanks for the idea. Bryan is right that I can't have same name for two
different index.
Well I am not understanding when, if there is a need, to create multiple
types of Index for one or muiltiple columns.
Another word, why would SQLsrvr2k allow muliple index in one column?
Ideas? Thanks again.
|||Say you have a query like:
...
ORDER BY col1 ASC, col2 DESC
In this case you'd want an index defined in the same way as your ORDER BY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:86DCF615-C593-476A-89CD-7A418F8E1436@.microsoft.com...
> Bryan and Tibor,
> Thanks for the idea. Bryan is right that I can't have same name for two
> different index.
> Well I am not understanding when, if there is a need, to create multiple
> types of Index for one or muiltiple columns.
> Another word, why would SQLsrvr2k allow muliple index in one column?
> Ideas? Thanks again.
>
|||On Thu, 2 Sep 2004 08:29:06 -0700, light_wt wrote:

>Bryan and Tibor,
>Thanks for the idea. Bryan is right that I can't have same name for two
>different index.
>Well I am not understanding when, if there is a need, to create multiple
>types of Index for one or muiltiple columns.
>Another word, why would SQLsrvr2k allow muliple index in one column?
>Ideas? Thanks again.
Hi light_wt,
Two indexes on one columns is redundancy. But two indexes on a set of two
columns may be interesting. If you have an index on (colA, colB), it can
be used for queries where both colA and colB must be equal to some value;
it can also be used if only colA is known. But this index serves no
purpose if I have to find all rows where colB = some value. If I often
have to search for colB, I might wish to create another index on only
colB, or on colB plus one or more other columns.
Another reason why SQL Server MUST allow multiple index in one column is
that indexes are used to check UNIQUE and PRIMARY KEY constraints. There
are lots of scenario's where both (Col1, Col2) and (Col1, Col3) are unique
combinations.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks, Hugo. Your answer make sense.
light_wt.

Index Question

Hello,

In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys be
disabled? If Indexes (PK's and UN Constraints) can be disabled what happens
if data is inserted while disable? Will the index be rebuilt when enabled?

Thanks,
Rob PanoshRob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> happens if data is inserted while disable? Will the index be rebuilt
> when enabled?

You can disable foreign-key constraints. When you re-enable them, SQL
Server verifies that the data comply to the constraint.

You cannot disable primary-key or unique constraints, nor indexes.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks ...

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns940E15C48E6CYazorman@.127.0.0.1...
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
> You cannot disable primary-key or unique constraints, nor indexes.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Unfortunately, SQL-Server does not automatically check the existing
table data after turning a constraint back on. IMO it is a Microsoft
mistake to allow a database to get corrupted this way.

Gert-Jan

Erland Sommarskog wrote:
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
> You cannot disable primary-key or unique constraints, nor indexes.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> Unfortunately, SQL-Server does not automatically check the existing
> table data after turning a constraint back on.

You're right, Gert-Jan. Thanks for correcting my mistake.

> IMO it is a Microsoft mistake to allow a database to get corrupted this
> way.

I can see situations where you may want this, but its deceivable that
the constraint is not rechecked. Not only it makes you think that you
have a sound table. If you use the column with a CHECK constraint in a
partitioned view, you will scratch your hair, trying to find out why
SQL Server accesses all tables after this operation.

It is possible to identify this situation though. The example is
augmented script from Books Online:

SET QUOTED_IDENTIFIER OFF
go
CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)
go
-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
go
-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)
go
-- Returns 1, because constraint has been disabled.
select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')"

Interesting, I did not know this yet. Something for a standard script to
check the database...

Gert-Jan

Erland Sommarskog wrote:
> Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> > Unfortunately, SQL-Server does not automatically check the existing
> > table data after turning a constraint back on.
> You're right, Gert-Jan. Thanks for correcting my mistake.
> > IMO it is a Microsoft mistake to allow a database to get corrupted this
> > way.
> I can see situations where you may want this, but its deceivable that
> the constraint is not rechecked. Not only it makes you think that you
> have a sound table. If you use the column with a CHECK constraint in a
> partitioned view, you will scratch your hair, trying to find out why
> SQL Server accesses all tables after this operation.
> It is possible to identify this situation though. The example is
> augmented script from Books Online:
> SET QUOTED_IDENTIFIER OFF
> go
> CREATE TABLE cnst_example
> (id INT NOT NULL,
> name VARCHAR(10) NOT NULL,
> salary MONEY NOT NULL
> CONSTRAINT salary_cap CHECK (salary < 100000)
> )
> go
> -- Valid inserts
> INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
> INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
> go
> -- This insert violates the constraint.
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Disable the constraint and try again.
> ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Reenable the constraint and try another insert, will fail.
> ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (4,"Eric James",110000)
> go
> -- Returns 1, because constraint has been disabled.
> select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Index question

I have the following index:
CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Log
inid],
logindate]) ON [PRIMARY]
GO
Do I still need to create the following index ?
CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) O
N [PRIMARY]
GO
Thanks for any feedback........DXC,
SQL Server stores statistics for the more left column in the key. This index
could be used for logic expressions referencing [Loginid] or ([Login
id] and
[logindate]). If you create the second index, may be SQL Server can deci
de to
use it because the key is shorter than the first one, so more rows can fit i
n
a page and less IO operations will be required.
Try some "select" statements with just the first index. If you are ok with
the response time and execution plan selected by SQL Server then do not
create the second one. Remember, indexes help sql server to find the data
faster, but also put more load for insert, delete, and update operations.
AMB
"DXC" wrote:

> I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([
Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]
) ON [PRIMARY]
> GO
>
> Thanks for any feedback........|||No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
on Loginid
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
>I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([L
oginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid])
ON [PRIMARY]
> GO
>
> Thanks for any feedback........|||That's what I thought............Thanks.
"Paul Wehland" wrote:

> No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to se
ek
> on Loginid
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
>
>

Index question

If I create two indexes for the same column, how would I know which index is
used?
for example,
create unique index UNI_INDX1 ON table1(column1 desc)
create unique index UNI_INDX1 ON table1(column1 asc)You cannot create more than 1 index with the same name. If you run the
statement you will get the following error:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'Customers' named 'UNI_INDX1'.
If you want to see the execution plan in QA hit Ctl+k or select it from the
Query menu.
If you want to see the plan in text, use the statement
SET SHOWPLAN_ALL ON
GO
before your DML statement.
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||In addition to Bryan's post:
There's absolutely no reason to create both ASC and DESC index on one column
. SQL Server can
traverse an index in both directions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||Bryan and Tibor,
Thanks for the idea. Bryan is right that I can't have same name for two
different index.
Well I am not understanding when, if there is a need, to create multiple
types of Index for one or muiltiple columns.
Another word, why would SQLsrvr2k allow muliple index in one column?
Ideas? Thanks again.|||Say you have a query like:
...
ORDER BY col1 ASC, col2 DESC
In this case you'd want an index defined in the same way as your ORDER BY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:86DCF615-C593-476A-89CD-7A418F8E1436@.microsoft.com...
> Bryan and Tibor,
> Thanks for the idea. Bryan is right that I can't have same name for two
> different index.
> Well I am not understanding when, if there is a need, to create multiple
> types of Index for one or muiltiple columns.
> Another word, why would SQLsrvr2k allow muliple index in one column?
> Ideas? Thanks again.
>|||On Thu, 2 Sep 2004 08:29:06 -0700, light_wt wrote:

>Bryan and Tibor,
>Thanks for the idea. Bryan is right that I can't have same name for two
>different index.
>Well I am not understanding when, if there is a need, to create multiple
>types of Index for one or muiltiple columns.
>Another word, why would SQLsrvr2k allow muliple index in one column?
>Ideas? Thanks again.
Hi light_wt,
Two indexes on one columns is redundancy. But two indexes on a set of two
columns may be interesting. If you have an index on (colA, colB), it can
be used for queries where both colA and colB must be equal to some value;
it can also be used if only colA is known. But this index serves no
purpose if I have to find all rows where colB = some value. If I often
have to search for colB, I might wish to create another index on only
colB, or on colB plus one or more other columns.
Another reason why SQL Server MUST allow multiple index in one column is
that indexes are used to check UNIQUE and PRIMARY KEY constraints. There
are lots of scenario's where both (Col1, Col2) and (Col1, Col3) are unique
combinations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo. Your answer make sense.
light_wt.

Index Question

I have a series of many-to-many relationships, so obviously have a number of
'join' tables. These join tables store only the unique key from the other
tables.
Is there any advantage to indexing the fields in the join tables or is it
not necessary because the data in them is technically indexed in another
table or do they still need indexing in their own right?
ThanksKeith
It is always good practice to create an index on foreign key column as the
linking column
( I assume you have pimary key with clustered index)
An index on a foreign key column can substantially boost the performance of
many joins.
"Keith" <@..> wrote in message news:O1DllPVSEHA.2408@.tk2msftngp13.phx.gbl...
> I have a series of many-to-many relationships, so obviously have a number
of
> 'join' tables. These join tables store only the unique key from the other
> tables.
> Is there any advantage to indexing the fields in the join tables or is it
> not necessary because the data in them is technically indexed in another
> table or do they still need indexing in their own right?
> Thanks
>

Index question

I have the following index:
CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
logindate]) ON [PRIMARY]
GO
Do I still need to create the following index ?
CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
GO
Thanks for any feedback........DXC,
SQL Server stores statistics for the more left column in the key. This index
could be used for logic expressions referencing [Loginid] or ([Loginid] and
[logindate]). If you create the second index, may be SQL Server can decide to
use it because the key is shorter than the first one, so more rows can fit in
a page and less IO operations will be required.
Try some "select" statements with just the first index. If you are ok with
the response time and execution plan selected by SQL Server then do not
create the second one. Remember, indexes help sql server to find the data
faster, but also put more load for insert, delete, and update operations.
AMB
"DXC" wrote:
> I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........|||No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
on Loginid
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
>I have the following index:
> CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> logindate]) ON [PRIMARY]
> GO
> Do I still need to create the following index ?
>
> CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> GO
>
> Thanks for any feedback........|||That's what I thought............Thanks.
"Paul Wehland" wrote:
> No. MYINDEX2 is redundant. SQL Server can use MYINDEX1 if it needs to seek
> on Loginid
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:34A7F75B-5178-4DD1-BD31-AFEE69C13458@.microsoft.com...
> >I have the following index:
> >
> > CREATE UNIQUE INDEX [MYINDEX1] ON [dbo].[LOGIN_TABLE]([Loginid],
> > logindate]) ON [PRIMARY]
> > GO
> >
> > Do I still need to create the following index ?
> >
> >
> > CREATE INDEX [MYINDEX2] ON [dbo].[LOGIN_TABLE]([Loginid]) ON [PRIMARY]
> > GO
> >
> >
> > Thanks for any feedback........
>
>sql

Index Question

I have a series of many-to-many relationships, so obviously have a number of
'join' tables. These join tables store only the unique key from the other
tables.
Is there any advantage to indexing the fields in the join tables or is it
not necessary because the data in them is technically indexed in another
table or do they still need indexing in their own right?
ThanksKeith
It is always good practice to create an index on foreign key column as the
linking column
( I assume you have pimary key with clustered index)
An index on a foreign key column can substantially boost the performance of
many joins.
"Keith" <@..> wrote in message news:O1DllPVSEHA.2408@.tk2msftngp13.phx.gbl...
> I have a series of many-to-many relationships, so obviously have a number
of
> 'join' tables. These join tables store only the unique key from the other
> tables.
> Is there any advantage to indexing the fields in the join tables or is it
> not necessary because the data in them is technically indexed in another
> table or do they still need indexing in their own right?
> Thanks
>

Index question

If I create two indexes for the same column, how would I know which index is
used?
for example,
create unique index UNI_INDX1 ON table1(column1 desc)
create unique index UNI_INDX1 ON table1(column1 asc)You cannot create more than 1 index with the same name. If you run the
statement you will get the following error:
Server: Msg 1913, Level 16, State 1, Line 1
There is already an index on table 'Customers' named 'UNI_INDX1'.
If you want to see the execution plan in QA hit Ctl+k or select it from the
Query menu.
If you want to see the plan in text, use the statement
SET SHOWPLAN_ALL ON
GO
before your DML statement.
--
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index
is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||In addition to Bryan's post:
There's absolutely no reason to create both ASC and DESC index on one column. SQL Server can
traverse an index in both directions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:D555C184-17FB-4B5A-8F31-F659C605F2C0@.microsoft.com...
> If I create two indexes for the same column, how would I know which index is
> used?
> for example,
> create unique index UNI_INDX1 ON table1(column1 desc)
> create unique index UNI_INDX1 ON table1(column1 asc)
>|||Say you have a query like:
...
ORDER BY col1 ASC, col2 DESC
In this case you'd want an index defined in the same way as your ORDER BY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"light_wt" <light_wt@.discussions.microsoft.com> wrote in message
news:86DCF615-C593-476A-89CD-7A418F8E1436@.microsoft.com...
> Bryan and Tibor,
> Thanks for the idea. Bryan is right that I can't have same name for two
> different index.
> Well I am not understanding when, if there is a need, to create multiple
> types of Index for one or muiltiple columns.
> Another word, why would SQLsrvr2k allow muliple index in one column?
> Ideas? Thanks again.
>|||On Thu, 2 Sep 2004 08:29:06 -0700, light_wt wrote:
>Bryan and Tibor,
>Thanks for the idea. Bryan is right that I can't have same name for two
>different index.
>Well I am not understanding when, if there is a need, to create multiple
>types of Index for one or muiltiple columns.
>Another word, why would SQLsrvr2k allow muliple index in one column?
>Ideas? Thanks again.
Hi light_wt,
Two indexes on one columns is redundancy. But two indexes on a set of two
columns may be interesting. If you have an index on (colA, colB), it can
be used for queries where both colA and colB must be equal to some value;
it can also be used if only colA is known. But this index serves no
purpose if I have to find all rows where colB = some value. If I often
have to search for colB, I might wish to create another index on only
colB, or on colB plus one or more other columns.
Another reason why SQL Server MUST allow multiple index in one column is
that indexes are used to check UNIQUE and PRIMARY KEY constraints. There
are lots of scenario's where both (Col1, Col2) and (Col1, Col3) are unique
combinations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo. Your answer make sense. :)
light_wt.

Index properties

Hi group.
Where can i find in the system tables of the SQL 2000, all the propreties of
an index. For example, if a create an index unique but not constraint and
'Ignore duplicate key' checked?. Where the SQL 2000 store this information?
Thanks
Nuno Teixeira
PortugalLook at the system function INDEXPROPERTY in Books Online.
"Nuno Teixeira" <NunoTeixeira@.discussions.microsoft.com> wrote in message
news:0A1884EE-1B7D-460C-B6AC-6F0582A5B7E7@.microsoft.com...
> Hi group.
> Where can i find in the system tables of the SQL 2000, all the propreties
> of
> an index. For example, if a create an index unique but not constraint and
> 'Ignore duplicate key' checked?. Where the SQL 2000 store this
> information?
> Thanks
> Nuno Teixeira
> Portugal|||See if this helps:
http://www.microsoft.com/communitie...7d47&sloc=en-us
AMB
"Nuno Teixeira" wrote:

> Hi group.
> Where can i find in the system tables of the SQL 2000, all the propreties
of
> an index. For example, if a create an index unique but not constraint and
> 'Ignore duplicate key' checked?. Where the SQL 2000 store this information
?
> Thanks
> Nuno Teixeira
> Portugal|||Forget this.
AMB
"Alejandro Mesa" wrote:
> See if this helps:
> http://www.microsoft.com/communitie...7d47&sloc=en-us
>
> AMB
> "Nuno Teixeira" wrote:
>|||Maybe this can help:
http://milambda.blogspot.com/2005/0...-with-kick.html
ML

Index problem...

I'm trying to delete rows from a database, ad I get an error that says "Specified index does not exist. [name of my UQ__foo__0x000BF unique index). What happend was I used to have a colum that was ment to be unique, then I made it un-unique and tried to delete the rows from the table. Something strange happend where the table is looking for a unique key that no longer exists but I can remake the unique key because it says "duplicate key" does not exist. I looked in all the system tables for references to UQ__foo__blah but didnt see anything.

Am I hosed? Do I need to drop the whole table?

Quote:

Originally Posted by RedSon

I'm trying to delete rows from a database, ad I get an error that says "Specified index does not exist. [name of my UQ__foo__0x000BF unique index). What happend was I used to have a colum that was ment to be unique, then I made it un-unique and tried to delete the rows from the table. Something strange happend where the table is looking for a unique key that no longer exists but I can remake the unique key because it says "duplicate key" does not exist. I looked in all the system tables for references to UQ__foo__blah but didnt see anything.

Am I hosed? Do I need to drop the whole table?

Had that one time...try reindexing the table see if it clears or puts it back then go for DROP INDEX of the specific index after rebuilding

This example rebuilds all indexes on the authors table using a fillfactor value
of 70.

DBCC DBREINDEX (authors, '', 70)

Index problem

this my script
CREATE UNIQUE INDEX [RELBFM].[UQ_PMT_PMTNAME] ON [RELBFM]. [ISMPMT] ("PMTNAME" ASC);
following errors occur
where i am facing problemSyntax for a create index statement:CREATE UNIQUE NONCLUSTERED INDEX ix_t_name_c_name_nu_nc
ON schema_name.t_name (c_name ASC)
Spot the diff ;)|||The original is correct for Oracle, but indexes in SQL Server are assumed to be part of the table's schema. In fact, the "index owner" is not even a concept in SQL Server.|||[QUOTE=pootle flump]Syntax for a create index statement:CREATE UNIQUE NONCLUSTERED INDEX ix_t_name_c_name_nu_nc
ON schema_name.t_name (c_name ASC)
Spot the diff ;) [/QUOso

so when we use CLUSTERED AND NONCLUSTERED INDEX

I had used clustered primary key so what type of index should I use|||Actually the clustered\ nonclustered bit is not mandatory. I just like to put it in so it is explicit. It also appears that there was a copy and paste problem (apologies) - there should be a space between nonclustered and index. The difference was actually as mcrowley said - you "qualified" the index name when you should not have.

A pk and a not null unique index are exactly the same as far as sql server is concerned.

Index from two fields

Hello,
How can I create an index that would make sure that the combination of given
two fields is unique (something like when primary key is assigned to two
fields)? Or, perhaps it's a constraint?
Thanks,
Pavils.Just create the primary key, Unique constraint or unique index over the two columns (col1, col2).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Hello,
> How can I create an index that would make sure that the combination of given
> two fields is unique (something like when primary key is assigned to two
> fields)? Or, perhaps it's a constraint?
> Thanks,
> Pavils.
>|||Pavlis
CREATE TABLE Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
PRIMARY KEY(col1,col2)
)
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Hello,
> How can I create an index that would make sure that the combination of
given
> two fields is unique (something like when primary key is assigned to two
> fields)? Or, perhaps it's a constraint?
> Thanks,
> Pavils.
>|||There's the thing that the primary key is already used, it's on one int type
field and serves as unique identifier for internal use. But, those two
fields I am talking about, they are of int and text type and due to the
nature of data their combination should not be equal to any other record in
the table.
Pavils
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u1Mfdx$jEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Just create the primary key, Unique constraint or unique index over the
two columns (col1, col2).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
> news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > How can I create an index that would make sure that the combination of
given
> > two fields is unique (something like when primary key is assigned to two
> > fields)? Or, perhaps it's a constraint?
> >
> > Thanks,
> >
> > Pavils.
> >
> >
>|||Pavlis
Perhasp you want to look at CHECK Constraints in the BOL. It does not make
for me any sense , what are trying to accomplish?
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:Oso7m3$jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> There's the thing that the primary key is already used, it's on one int
type
> field and serves as unique identifier for internal use. But, those two
> fields I am talking about, they are of int and text type and due to the
> nature of data their combination should not be equal to any other record
in
> the table.
> Pavils
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u1Mfdx$jEHA.3536@.TK2MSFTNGP12.phx.gbl...
> > Just create the primary key, Unique constraint or unique index over the
> two columns (col1, col2).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
> > news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > How can I create an index that would make sure that the combination of
> given
> > > two fields is unique (something like when primary key is assigned to
two
> > > fields)? Or, perhaps it's a constraint?
> > >
> > > Thanks,
> > >
> > > Pavils.
> > >
> > >
> >
> >
>|||Ok, here's the case:
The table contains agreement information. Besides the other fields,
agreement has the company id (there are about 5 companies), and agreement
number (which may contain digits, letters, and dashes). The style of
agreement number is different for every company, but, by coincidence,
sometimes there could be case that two entries have equal agreement numbers
but different company ids, and that's Ok. The agrement table also has a
primary key field of type int. Thus, the primary key is already taken.
Now, what I want to enforce, is that SQL server does not allow insertion of
new record, if the table already containd entry with matching company id and
agreement number.
So, perhaps I have to use constraints rather than index. But, what would be
syntax of such constraint?
Thanks,
Pavils.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eI6VG9$jEHA.484@.TK2MSFTNGP10.phx.gbl...
> Pavlis
> Perhasp you want to look at CHECK Constraints in the BOL. It does not make
> for me any sense , what are trying to accomplish?
>
> "Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
> news:Oso7m3$jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> > There's the thing that the primary key is already used, it's on one int
> type
> > field and serves as unique identifier for internal use. But, those two
> > fields I am talking about, they are of int and text type and due to the
> > nature of data their combination should not be equal to any other record
> in
> > the table.
> >
> > Pavils
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:u1Mfdx$jEHA.3536@.TK2MSFTNGP12.phx.gbl...
> > > Just create the primary key, Unique constraint or unique index over
the
> > two columns (col1, col2).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
> > > news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > >
> > > > How can I create an index that would make sure that the combination
of
> > given
> > > > two fields is unique (something like when primary key is assigned to
> two
> > > > fields)? Or, perhaps it's a constraint?
> > > >
> > > > Thanks,
> > > >
> > > > Pavils.
> > > >
> > > >
> > >
> > >
> >
> >
>|||What you have is a surrogate key (the current primary key), but you have not enforced the uniqueness
of your natural key. Use a UNIQUE constraint for that. Something like:
ALTER TABLE tblname
ADD CONSTRAINT name_of_constraint UNIQUE(companyid, agreementnumber)
Unfortunately, this is called UNIQUE constraint. Id' be much happier if ANSI decided to name it
ALTERNATE KEY constraint.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23Z86xZAkEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Ok, here's the case:
> The table contains agreement information. Besides the other fields,
> agreement has the company id (there are about 5 companies), and agreement
> number (which may contain digits, letters, and dashes). The style of
> agreement number is different for every company, but, by coincidence,
> sometimes there could be case that two entries have equal agreement numbers
> but different company ids, and that's Ok. The agrement table also has a
> primary key field of type int. Thus, the primary key is already taken.
> Now, what I want to enforce, is that SQL server does not allow insertion of
> new record, if the table already containd entry with matching company id and
> agreement number.
> So, perhaps I have to use constraints rather than index. But, what would be
> syntax of such constraint?
> Thanks,
> Pavils.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eI6VG9$jEHA.484@.TK2MSFTNGP10.phx.gbl...
> > Pavlis
> > Perhasp you want to look at CHECK Constraints in the BOL. It does not make
> > for me any sense , what are trying to accomplish?
> >
> >
> >
> > "Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
> > news:Oso7m3$jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> > > There's the thing that the primary key is already used, it's on one int
> > type
> > > field and serves as unique identifier for internal use. But, those two
> > > fields I am talking about, they are of int and text type and due to the
> > > nature of data their combination should not be equal to any other record
> > in
> > > the table.
> > >
> > > Pavils
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > in
> > > message news:u1Mfdx$jEHA.3536@.TK2MSFTNGP12.phx.gbl...
> > > > Just create the primary key, Unique constraint or unique index over
> the
> > > two columns (col1, col2).
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
> > > > news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > > > > Hello,
> > > > >
> > > > > How can I create an index that would make sure that the combination
> of
> > > given
> > > > > two fields is unique (something like when primary key is assigned to
> > two
> > > > > fields)? Or, perhaps it's a constraint?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Pavils.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Index from two fields

Hello,
How can I create an index that would make sure that the combination of given
two fields is unique (something like when primary key is assigned to two
fields)? Or, perhaps it's a constraint?
Thanks,
Pavils.
Just create the primary key, Unique constraint or unique index over the two columns (col1, col2).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Hello,
> How can I create an index that would make sure that the combination of given
> two fields is unique (something like when primary key is assigned to two
> fields)? Or, perhaps it's a constraint?
> Thanks,
> Pavils.
>
|||Pavlis
CREATE TABLE Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
PRIMARY KEY(col1,col2)
)
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Hello,
> How can I create an index that would make sure that the combination of
given
> two fields is unique (something like when primary key is assigned to two
> fields)? Or, perhaps it's a constraint?
> Thanks,
> Pavils.
>
|||There's the thing that the primary key is already used, it's on one int type
field and serves as unique identifier for internal use. But, those two
fields I am talking about, they are of int and text type and due to the
nature of data their combination should not be equal to any other record in
the table.
Pavils
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u1Mfdx$jEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Just create the primary key, Unique constraint or unique index over the
two columns (col1, col2).[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
> news:%23nxg%23r$jEHA.3612@.TK2MSFTNGP12.phx.gbl...
given
>
|||Pavlis
Perhasp you want to look at CHECK Constraints in the BOL. It does not make
for me any sense , what are trying to accomplish?
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:Oso7m3$jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> There's the thing that the primary key is already used, it's on one int
type
> field and serves as unique identifier for internal use. But, those two
> fields I am talking about, they are of int and text type and due to the
> nature of data their combination should not be equal to any other record
in
> the table.
> Pavils
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:u1Mfdx$jEHA.3536@.TK2MSFTNGP12.phx.gbl...
> two columns (col1, col2).
> given
two
>
|||Ok, here's the case:
The table contains agreement information. Besides the other fields,
agreement has the company id (there are about 5 companies), and agreement
number (which may contain digits, letters, and dashes). The style of
agreement number is different for every company, but, by coincidence,
sometimes there could be case that two entries have equal agreement numbers
but different company ids, and that's Ok. The agrement table also has a
primary key field of type int. Thus, the primary key is already taken.
Now, what I want to enforce, is that SQL server does not allow insertion of
new record, if the table already containd entry with matching company id and
agreement number.
So, perhaps I have to use constraints rather than index. But, what would be
syntax of such constraint?
Thanks,
Pavils.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eI6VG9$jEHA.484@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Pavlis
> Perhasp you want to look at CHECK Constraints in the BOL. It does not make
> for me any sense , what are trying to accomplish?
>
> "Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
> news:Oso7m3$jEHA.1348@.TK2MSFTNGP15.phx.gbl...
> type
> in
> in
the[vbcol=seagreen]
of
> two
>
|||What you have is a surrogate key (the current primary key), but you have not enforced the uniqueness
of your natural key. Use a UNIQUE constraint for that. Something like:
ALTER TABLE tblname
ADD CONSTRAINT name_of_constraint UNIQUE(companyid, agreementnumber)
Unfortunately, this is called UNIQUE constraint. Id' be much happier if ANSI decided to name it
ALTERNATE KEY constraint.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Pavils Jurjans" <pavils@.mailbox.riga.lv> wrote in message
news:%23Z86xZAkEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Ok, here's the case:
> The table contains agreement information. Besides the other fields,
> agreement has the company id (there are about 5 companies), and agreement
> number (which may contain digits, letters, and dashes). The style of
> agreement number is different for every company, but, by coincidence,
> sometimes there could be case that two entries have equal agreement numbers
> but different company ids, and that's Ok. The agrement table also has a
> primary key field of type int. Thus, the primary key is already taken.
> Now, what I want to enforce, is that SQL server does not allow insertion of
> new record, if the table already containd entry with matching company id and
> agreement number.
> So, perhaps I have to use constraints rather than index. But, what would be
> syntax of such constraint?
> Thanks,
> Pavils.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eI6VG9$jEHA.484@.TK2MSFTNGP10.phx.gbl...
> the
> of
>