How Do You Determine An Index Is Unique From Sysindexes?

Mar 17, 2002

I need to write a script which will respond differently if a table's index forces uniqueness.

I am using SQL 7.0 and I am using the [sysindexes] table of my database to examine those indexes. I am using the [id] field from the [sysobjects] table for the table I am examining and I am using that [id] field to find like-valued [id] fields in the [sysindexes] table.

My problem is that I can't seem to determine, from an examination of the [sysindexes] table, whether, or not, an index is unique.

When adding an index, and checking the unique property of that index, the values that I get in the [status] field of [sysindexes] are different from the values which are supposed to show, at least according to the sysindexes (T-SQL) entry in SQL Server Books Online.

For instance, I have a table which combines two text fields which comprise the only key on a table. The unique and clustered boxes are checked in the index setup screen. When I look in the [sysindexes] table, the value in the [status] field is 2113554, which is not a value I see in the books online page. According to my books online page, a unique index should have a value of 2 in the [status] field and a clustered index should have a value of 16 in the [status] field. My assumption is that I should see a value of 18 in that [status] field, not 2113554.

I looks like the books online entry might be out-of-date because the field that is labeled [reserved1] in my books online page, is labeled [StatVersion] in my actual [sysindexes] table. That [StatVersion] field looks suspiciously like a Status Version field, possibly indicating that the Status field has undergone some sort of version revision?

Is anyone familiar with this stuff?

Thanks.

Ken

View 1 Replies


ADVERTISEMENT

Could Not Find Row In Sysindexes With Index ID

Dec 19, 2005

Please help
1. my database can not truncate log and I use this command
BACKUP LOG IMVDB2 WITH NO_LOG
and after that I received result as below
->The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

2. so I drop all replicate but it's still return same error
so I try to run
DBCC CHECKDB --> but no error meesage
DBCC CHECKALLOC --> but no error meesage
DBCC CHECKTABLE ('sysindexes') --> but no error meesage
but I tried to run
3. DBCC PAGE (IMVDB2, 1, 13015, 3) i recieved
-->Server: Msg 2591, Level 16, State 14, Line 1
Could not find row in sysindexes with index ID 2 for table 'object ID (334220541)'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

so what can I do the next to fix this problem, please help[/b]

View 4 Replies View Related

Sysindexes - How To Distinguish Primary Key Index

Apr 22, 2003

indid =1 works, is this the correct way

View 3 Replies View Related

SYSINDEXES Index Fail, Error 644

Jan 16, 2006

A few days ago a sproc stopped working, only noticed it this morning, when checking information to see what's up I get this:

Server: Msg 644, Level 21, State 1, Procedure uspV2InventoryFetch, Line 83
Could not find the index entry for RID '45574f44523738313834202020202020202020202020202020202020202020' in index page (1:11690152), index ID 2, database 'ASOS'.

Connection Broken

I've treid DBCC CHECKDBing all related tables and everything else. Even tried dbreindexing a couple of them too, everything reports as correct. When I looked further at the problem I spotted "index ID 2" in there, on checking this out in sysindexes it seems that the name field of sysindexes where id = 2 is SYSINDEXES and TSYSINDEXES.

I can't seem to do a DBB DBRECINDEX against a system table.

Any help appreciated.

John

View 3 Replies View Related

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

This index is not unique

ix_report_history_creative_id

Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.

The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161

The statement has been terminated.

Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).

The statement has been terminated.

View 6 Replies View Related

What Is The Difference Between A UNIQUE INDEX And A UNIQUE CONSTRAINT?

Sep 22, 2004

A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.

View 8 Replies View Related

Unique Constraint Vs Unique Index In MS SQL 2000

Jul 20, 2005

HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil

View 5 Replies View Related

Unique Constraint And Unique Index, What's The Difference?

Jun 24, 2006

What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED

I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?

View 1 Replies View Related

Determine That ID Is Unique In Customer Table

Oct 12, 2014

I would need to rewrite SQL code to determine that id is unique in the Customer table.

My two tables are:

CREATE TABLE CUSTOMER(
[CUSTNO] varchar(5) NOT NULL,
[ID] CHAR(9) NOT NULL,
[NAME] VARCHAR(128) NOT NULL,
[ADDRESS] VARCHAR(128) NOT NULL,
[DATEOFBIRTH] DATE NOT NULL,

[Code] ....

View 2 Replies View Related

How To Determine The Unique IDs Of Duplicated Records

Feb 21, 2006

> This is a common problem with some solution[color=blue]>>[/color]/************************************************** *********************************** Problem:* Determine the Duplicated Records in a table using single SELECT.** We shall be using Northwind database, add some duplicate records.** Here we want to know if 2 columns (CompanyName,* PHone) are duplicated in a table.*** ShipperID CompanyName Phone* ----------- ------------------------- ------------------* 1 Speedy Express (503) 555-9831* 2 United Package (503) 555-3199* 3 Federal Shipping (503) 555-9931* 4 Federal Shipping (503) 555-9931* 5 Speedy Express (503) 555-9831* 6 Federal Shipping (503) 555-9931***************************************************** **/================================================== SOLUTION 1: Gives me the IDs that are duplicated.================================================== SELECTShipperID, CompanyName, PhoneFROMSHIPPERSWHEREEXISTS (SELECTNULLFROMSHIPPERS bWHEREb.CompanyName = SHIPPERS.CompanyNameAND b.Phone = SHIPPERS.PhoneGROUP BYb.CompanyName, b.PhoneHAVINGSHIPPERS.ShipperID < MAX( b.ShipperID ))/* ********************* Output results********************/ShipperID CompanyName Phone----------- ----------------------------------------------------------------1 Speedy Express (503) 555-98313 Federal Shipping (503) 555-99314 Federal Shipping (503) 555-9931(3 row(s) affected)================================================== ===========SOLUTION 2: Gives me the data which are duplicate butnot the IDs================================================== ===========SELECTCompanyName, PhoneFROMSHIPPERSGROUP BYCompanyName, PhoneHAVINGCOUNT(*) > 1/* ********************* Output results********************/CompanyName Phone---------------------------------------- ------------------------Speedy Express (503) 555-9831Federal Shipping (503) 555-9931(2 row(s) affected)

View 1 Replies View Related

Insert Diffrent Columns,Determine Non Unique Fields

Sep 19, 2006

andre writes "I have two questions:

1.)How do i determine if fields are not unique??
2) How to update one table with values from another table when the row names are diffrent??

Thank you!!"

View 1 Replies View Related

Unique Index Vs Unique Constraint

Mar 7, 2001

Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?

Which one do one use ?

thanks
sonali

View 4 Replies View Related

Unique Constraint Vs Unique Index

Jan 20, 2006

BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?

View 2 Replies View Related

Unique Index Vs Unique Constraints

Mar 26, 2008



hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?

View 12 Replies View Related

What 's Difference Between Unique Key And Unique Index

Nov 13, 2007

What 's difference between Unique key and unique index in SQL server 2005?

View 9 Replies View Related

How To Determine When To Re-Index Programmatically?

May 13, 2002

Great Monday Morning to one and all,

Setup: SQL7 w/SP4 running on W2K Pro

Table in Question:


I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).

The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.

For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.

View 3 Replies View Related

How To Determine When To Re-Index Programmatically?

May 13, 2002

Great Monday Morning to one and all,

Setup: SQL7 w/SP4 running on W2K Pro

Table in Question:


I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).

The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.

For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.

View 2 Replies View Related

How Can I Determine An Index Exists?

Dec 6, 1999

Hi,
I'm trying to run a script to add an index to a table. The index might already exist so I want to drop it 1st if it does.

Is there an equivalent to the following that I use for tables for indexes?

if exists (select * from sysobjects where id = object_id('dbo.tblFred') and sysstat & 0xf = 3)
drop table dbo.tblFred

Thanks,
Martin

View 6 Replies View Related

Transact SQL :: How To Determine Index Create Date

Nov 7, 2012

Is it possible to determine an index create date in SQL Server 2008 R2?  The index in question is not a primary key.

View 8 Replies View Related

Unique Index

Mar 29, 2004

Just found out that creating a unique index does not create a unique constraint, but creating a unique constraint creates unique index.

But effectively they do the same thing.

View 3 Replies View Related

Unique Index

May 8, 2008

How to findout whether a Index is unique or not?

------------------------
I think, therefore I am - Rene Descartes

View 2 Replies View Related

Unique Index

Mar 26, 2007

if this question is inappropriate here, I apologize (it's at least obliquely related). I have been using ssno as a unique key in a datawarehouse I have been working on because all of the component systems have had it. I now have a database to add where ssno is not available. I have first, last address, city, state,zip and dob.

Question is, how to construct a unique identifier from those components. If not unique, then at least usable?

Again, if this post is wrong here, I apologize

Thanks for any input

Walter

View 1 Replies View Related

Can't Add Bit Column To Unique Index

Feb 6, 2007

This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.
Can anyone shed any light on the problem?
Thanks.

View 5 Replies View Related

Better Performance With Unique Index?

May 21, 2002

I have read that you get better performance with unique indexes rather than non-unique indexes.
I have experimented with this in SQL 2000. I have two identical tables (with about 250000 rows each) with a 12-character unique column. In one table I define it as a regular index and in the other I define it as a unique index. No matter what I try I get identical performance, and the query optimizer shows an identical plan.
I even tried clauses such as
WHERE 1 < (SELECT COUNT(*) FROM TheTable
WHERE key_column = OtherTable.key_column)
which should obviously return nothing if TheTable.key_column is unique. However the query still ran a long time no matter if the index is unique or not.
I have also tried a unique constraint instead of a unique index and got the same (non)results.
Can anyone come up with an example where creating a unique index actually makes a performance difference?

View 1 Replies View Related

UNIQUE INDEX If Not NULL

Dec 17, 2006

Hello !

for MS SQL 2000
how can i set an unique index on Serial column but only if Serial IS NOT NULL

CREATE UNIQUE INDEX [IX_Product] ON [Product]([Serial]) ON [PRIMARY]

i can have 100 rows with a NULL Serial


thank you

View 9 Replies View Related

Unique Index On 2 Columns?

Apr 19, 2013

I find to be able to have multiple NULL entries in the following constraint:

Code:

CREATE UNIQUE INDEX my_uidx ON my_table(my_col1,my_col2) WHERE ??? IS NOT NULL;

But is not possible to check multiple columns in "WHERE".

I using SQL Server 2012 Express

View 3 Replies View Related

Unique Constraint/index

Feb 12, 2004

I'm trying to weight the pros and cons of unique constraints and unique indexes. I understand that creating a unique constraint also creates an index. If that is the case, why not just use a unique index? Could someone give me an example of when you would want an unique constraint over an unique indexes

Thanks in advance

View 7 Replies View Related

Unique Constraint/Index

May 7, 2008

What is the difference between unqiue constraint and unique index? What are the pros and cons? Are they interchangable?

------------------------
I think, therefore I am - Rene Descartes

View 4 Replies View Related

Unique Index In Sql Server

Mar 4, 2008

What is unique index in sql server 2k?
plz some one help me out, thanx

Rahul Arora
07 Batch
NCCE Israna,


######################
IMPOSSIBLE = I+M+POSSIBLE

View 2 Replies View Related

Creating A Unique Index

Jul 20, 2005

HiI tried the following from the help file...When you create or modify a unique index, you can set an option toignore duplicate keys. If this option is set and you attempt to createduplicate keys by adding or updating data that affects multiple rows(with the INSERT or UPDATE statement), the row that causes theduplicates is not added or, in the case of an update, discarded.For example, if you try to update "Smith" to "Jones" in a table where"Jones" already exists, you end up with one "Jones" and no "Smith" inthe resulting table. The original "Smith" row is lost because anUPDATE statement is actually a DELETE followed by an INSERT. "Smith"was deleted and the attempt to insert an additional "Jones" failed.The whole transaction cannot be rolled back because the purpose ofthis option is to allow a transaction in spite of the presence ofduplicates.But when I did it the original "Smith" row was not lost.I am doing something wrong or is the help file incorrect.Dan

View 3 Replies View Related

Unique Constraint Index

Jul 20, 2005

When I add a unique key constraint to column in SQL 6.5 why does it alsocreate an index. e.g. In the table subaccounts I added a unique keyconstraint for the column login and SQL creates an index with the nameUQ_SubAccounts_2__19 (UKC).Does this also mean that there is no need to create an index for thiscolumn?thxMansoor

View 2 Replies View Related

Double Unique Index

Aug 21, 2007



Hello.

I have a question. I need to make a double unique index on a table. for example: I have 2 columns, ColumnA and ColumnB. ColumnA can have duplicate values, so is ColumnB, but it should be impossible to have duplicate values on both columns. for example:

Row 1:
ColumnA = 1, ColumnB = 2

Row2:
ColumnA = 1, ColumnB = 2

this shouldn't be possible.

Row1:
ColumnA = 1, ColumnB = 2

Row2:
ColumnA = 1, ColumnB = 3

this should be possible

is there any way I can do this?

thanks in advance

View 1 Replies View Related

PK Vs Unique Clustered Index

Aug 20, 2007

When not using any enforced relationships in a database (which I know we should, but I like most of you, have inherited sub-optimal design), is there any real difference between using a Primary Key (to which no foreign keys are tied) and using a Unique Clustered index?

Thanks for your thoughts

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved