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 table
thanxs
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
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example:
Row 1 - Column 1 = "cool"
Row 1 - Column 3 = 91
Row 3 - Column 1 = "cool"
Row 3 - Column 3 = 91
I dont care about one column having duplicate values, I want to protect against Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?
Hi there ...here comes a tricky one.
I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables.
This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one!
I can find those rows by
SELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0
WHERE EXISTS (
SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1
WHERE table0.ParentREF = table1.ParentREF
AND table0.UniqueName = table1.UniqueName
AND table0.OID != table1.OID
)
ORDER BY ParentREF, UniqueName, ModifiedUTC desc
...but I struggle to make the required SQL (SP?) to update the "invalid" rows.
Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged!
ATM the preferred (cause easiest) way is to rename the invalid rows with
UniqueName = OID
because if I use any other name I risk to create another double entry.
Thanks in advance to whoever can help me
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.
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 RelatedHelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies View RelatedWhat'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?
Is index created on only primary key or it can be created on secondary key ?
View 2 Replies View RelatedHi 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
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
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 ?
What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies View RelatedHow can I create secondary database on the server i.e. .ndf file?
View 2 Replies View Related
Dear All.
We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.
table is >> Order_Dtl
Columns are:-
Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
I am thank u to all who read or reply.
Arshad
Manager Database
Esoulconsultancy.com
(Teradata Master)
10g OCP
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.
How to findout whether a Index is unique or not?
------------------------
I think, therefore I am - Rene Descartes
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
i have created a new login in primary server and provided dbowner permission to primary db.how do i transfer this login to secondary server and assign the same permission to secondary db ?
View 2 Replies View RelatedThis 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.
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?
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
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
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
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
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
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 RelatedWhen 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
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
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
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();
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
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