Unique Index, SQL 2005

Jan 30, 2008



Is there a simple way to find properties of an index for a table? Specifically I am looking for the unique property. I currently have code to to do this, however I feel like there must be some easier way.

I am using .net 2005 and SQL 2005 coding in c#. Thanks for any help!



Code Snippet

_conn.Open();
SqlCommand cmd = new SqlCommand("sp_helpindex", _conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@objname", "TABLENAME"));
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(results);


foreach (DataRow row in results.Tables[0].Rows)

{


ArrayList names = new ArrayList();

DataTable dtUnique = new DataTable();

SqlCommand cmd2 = this._conn.CreateCommand();

cmd2.CommandText = String.Format("SELECT INDEXPROPERTY(OBJECT_ID('TABLENAME'), 'INDEXNAME', 'IsUnique') AS isUnique");

dtUnique.Load(cmd2.ExecuteReader());


bool unique = Convert.ToBoolean(dtUnique.Rows[0]["isUnique"]);

if (unique)


names.Add(tableName);

}

_conn.Close();

View 9 Replies


ADVERTISEMENT

SQL Server 2005; Unique Constraint, Covering Index Question

Aug 9, 2006

Hi,

When I create a unique constraint, SQL Server automatically creates an index on this constraint. So when I run the following...

ALTER TABLE PersonsProjects
WITH NOCHECK ADD CONSTRAINT NoDupes UNIQUE NONCLUSTERED (PersonID, ProjectID)

...SQL Server will create a composite index on PersonsProjects called NoDupes on PersonIDand ProjectID. Thing is, I need this index to include a third column Status since most queries use this column in conjunction with PersonID and ProjectID. If there was no index on this table, I would have created it as follows:

CREATE UNIQUE INDEX NoDupes ON PersonsProjects (PersonID, ProjectID) INCLUDE (Status) WITH IGNORE_DUP_KEY

But this won't enforce the unique constraint on PersonID and ProjectID when performing inserts and updates. Is there any way of creating a unique constraint with an included column?

I would rather not have two indexes...

NoDupes: PersonID,ProjectID

New Index: PersonID,ProjectID INCLUDE Status

...so I'm trying to determine what other options that might be available...please advise.

Thanks much.

View 10 Replies View Related

Converting DB2 Timestamp Data To SQL Server 2005 - Problems With Unique Index

May 24, 2007

I am attempting to move a timestamp data column from DB2 to SQL Server 2005. Normally not a big deal but the column is part of unique index.



The DB2 timestamp has seconds of ss.ssssss but SQL Server only has ss.sss.



Most all the times entered into this column are a from an automated process so they are really close together timewise.



Here is what I have come up with so far:

1. Fast Load OLEDB with a batch of 10,000 records at a time

2. On the fail of the batch redirect rows to a regular table load OLEDB insert task

3. On the fail of the single insert redirect rows to script that ups the seconds one tick.

4. Attempt one last insert of the modified rows

5. If fail, then store the record off to a delimited text file



I am hoping to get the number of records that wind up in the delimited text file to be a very small number and not in the 1,000+.



Any help would be appreciated.

View 5 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

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

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

How To Create Unique Secondary Index ?? HELP

Apr 3, 2007

I have created the companyid as Primary Key.How to create a unique secondary index on Company Name. To avoid inserting duplicate records in database with the same companyname. I m creatin database in sql server 2005 with asp.net C# 2005. I know one way is write the query if not exists at the time of insert.But,i want to know is there anyother way to make a unique secondary index for the companyname on the company tablethanxs

View 1 Replies View Related

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 View Related

Error While Droping Unique Index

Apr 4, 2002

Hi,

I get the following error message while droping unique index.

Server: Msg 3723, Level 16, State 5, Line 1
An explicit DROP INDEX is not allowed on index 'dbo.ALEG.IX_ALEG'. It is being used for UNIQUE KEY constraint enforcement.

Could you please how the index can be droped.

Thanks

View 1 Replies View Related

Create Unique Index Error

Jan 11, 2005

Does anybody know the significance of 3 in the following error message?

"CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 3. "

Thank you

View 3 Replies View Related

Unique Index On X Columns But Not Primary Key

Feb 23, 2006

my table :

CREATE TABLE [dbo].[users] (
[ID] [int] NOT NULL ,
[A1] [nvarchar] (100) NULL ,
[A2] [nvarchar] (100) NULL ,
[A3] [nvarchar] (100) NULL
) ON [PRIMARY]

i must keep ID columns as primary key

ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]


but now A1+A2 must be unique

how can i do it ?

thank you

View 5 Replies View Related

How To Know If A Table Has Non-unique Clustered Index

Oct 31, 2015

Give a user table MyTable. How to know whether the table contains a non-unique clustered index by using SQL query?

View 1 Replies View Related

Unique Index Conflict On Insert Into

Jun 18, 2008

I get this message when doing an insert into :

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.ElementLocalCharacterised' with unique index 'ElementLocalCharacterised_uq'.
The statement has been terminated.

Now my question is : does this error mean that all inserts are done, despite those where the unique key produced a conflict? Or does it mean that none of the inserts are done (all are rejected because at least one gave a conflict) ?

If the second is the case I would like to know if it is possible to execute the insert into in a way that all inserts are done despite those that raise a conflict?

Thanks a lot for any suggestion !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

View 3 Replies View Related

Unique Case Sensitive Index - Possible?

May 6, 2007

Hello,

I would like to know how I may create a unique case sensitive index?

Thanks you very much for any advice!

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

View 1 Replies View Related







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