I've always used the identity field in SQL server to maintain the unique id for a table. With the new DB design at work we brought in a dba and she made us move away from allowing SQL maintain the unique field and having us maintain the unique field in code. To do that we had to begin a transaction, do a select max(id) + 1, insert into table, commit transaction. Doing it this way, I'm starting to see deadlocks due to the transactions locking the table.
Getting down to what I wanted to know, what is the pro's/con's you guys see in maintaining the unique ID this way and is there a better way of creating an unique id in T-SQL code?
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).
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.
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?
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 ?
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?
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 ?
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today. Here is the code
Code Snippet create procedure SearchPartKeyword ( @Keyword varchar(250) = null, @Universal_Id varchar(10) = null ) as select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p inner join notes n on p.PartNumber = n.Identifier where n.Universal_ID = @Universal_ID and p.Description like @Keyword union select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p where p.Description like @Keyword
and the results: PartNo Description SO Notes LA DMM Legacy Folder Printer de90008 MAIN BOARD 1 DGF1 114688 0 0 0 de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
create table Test ( [recId] [int] identity(1, 1) not null, [code] [varchar](50) not null, [prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)), constraint [PK_Test] primary key clustered ( [recId] ) with fillfactor = 90 on [primary] ) on [primary] go
insert into Test (code, prime) values ('AVA', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('CAR', cast(1 as bit)) insert into Test (code, prime) values ('CAR', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(1 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit))
select * from Test
I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:
-- This should fail insert into Test (code, prime) values ('RLW', cast(1 as bit))
in sql server 2000 i know how to make primary key using enterprise manager i want to make one of the columns foreign key,how to do that using enterprise manager.and what is the difference between both.
In this table there are two columns, one called "company number" (NUMBER) and one called "company name" (VARCHAR). I have an index called agents_PK that are unique and indexes both columns with company number as first and company name as second in the column order.
But when I look at the data in the column company number its not unique, I find several rows with the same number.
I know that primary key is a unique key .I read somewhere that a table can have both primary key and unique key at a time .Am I Right?Pl give clarification as asap. Thank U
I am using the sql function unique id ( create a new default call it newid and then give it the value newid() ) The problem is I'm trying to automatically populate that uniqueid field with the random 36 bit character. if I create a new record it will create the 36bit character, but how do I add the 36bit character to an existing table?
Dear experts, if i've created one unique key on one column, will it creates an index on that? if so, is there any way to find how every index was created?
i mean wether it was created with create index or it was created while the primary key or unique key creation.
thank you very much....i've been using the PBGUY query
Hello SQL gurus, can you help me with a problem. I have an Access Database and a table containing a list of currency values. I want to query this table to find out if there are any unique values, that is, an amount that only appears once in the table. Its probably very simple but I cannot work it out. The table is called Bids and the Field that I want to query is called Bids_value. Any ideas would be greatly appreciated.
Lorsque tu as edité un nouveau projet, tu a sans doute du prendre le même nom qu'un précédent. Lors du déploiement, l'ID du model ne peux pas être actualisé, alors il veux creer un nouveaux sur le server de rapport. Or, c'est impossible puisqu'il existe déja...
Suprime le rapport qui porte le meme identifiant et re déploie le, ca fonctionnera sans souci, j'éspère
Hi I have a table as follows ID Cat Date -------------------- Id1 cat1 d1 Id2 cat1 d2
Id3 cat1 d3 Id4 cat2 d4
I wanted to retrived unique Cat for given ID's. I accept two ID's always Select distinct(Cat) from Table where ID='id1' or ID='Id2' This works fine for me and returns distincts values.
Result -------- Cat1
But i also want the date along with Cat ( which ever is greater ) from theresult set. Expected result ---------- Cat1 D2( latest)
Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks
I've got a table name Messages and each message has a MessageID (the primary, auto-generated column) and a ThreadID (for grouping replies together). When I create a new message, I want to insert a new ThreadID if none is given to the stored procedure. I'm looking for something like NewID() as the default value but that inserts a NewInt. Do I have to write my own trigger?And I know ThreadID should be a foreign key to a Threads table but I'm keeping it simple for now. I have the option of making that later if I like. Thanks.
I am looking for advice on how to handle the following situation.Data being saved to a database table. Unique index on column in table. If the constraint is violated, what is the best way in alerting the user? Catch the exception and display error message to user? How do I know the exception is because of a unique index violation?
<disclaimer>I'm only using Web Matrix and have very little SQL knowledge</disclaimer>
I am doing an INSERT into a file where the primary key (UniqueID) is automatically generated. However, as soon as the record is inserted I need the UniqueID for further processing.
The only way I can see for me to get the UniqueID is to do a SELECT immediately after the INSERT, which seems a bit of an overhead.
So, just checking really, is this my only option or is there a super impressive way of passing the UniqueID back via the INSERT.
I have a list of zones and IP addresses assigned to those zones. On the page that manages those zones and their IP addresses needs to only display the RANGES of IP addresses that exist for that zone. For example if this is my data for Zone 1:IPAddress (removed first octet)002.202.148002.202.149187.202.243187.202.244187.202.245187.202.246187.202.247187.202.248187.203.187I want to display it as this on the management page:Zone 1 : IPAddress Ranges From 2.202.148 to 2.202.149 From 187.202.243 to 187.202.248 From 187.203.187 to 187.203.187Is there any way to make the SQL statement list the mins and maxes for every range? Or is this something I just HAVE to do in the code? I would like a record to return:MIN | MAX2.202.148 | 2.202.149187.202.243 | 187.202.248187.203.187 | 187.203.187I think I'm trying to expect a lot from SQL but I thought i would ask anyway. Thanks!~Cattrah~
Hi again, When developing web sites with Asp, DreamWeaver and Access, I've always used Unique IDs for setting things like userID or productID. However, with VWD Express and a .mdf db, this doesn't seem like an easy thing to do, at least not when inserting test data during development. The reason is that the uniqueIDs are 32 or so digits, and I have no idea what to write, and it's tiresome anyway. I used a book from Wrox (Begninning Asp.Net 2.0) to learn VWD and Asp.Net, and in their examples they actually use ints for setting their productIDs and the like. I'm really surprised to see that in a text book, but that's the way they've done it. So what's the best practice? What should I do? Please help me out here as I'm really confused! Thanks in advance, Pettrer
hai I have to delete a single row from a table , where there are multiple rows with the same data and i want to delete only one row and i cannot alter my table structure. Thanks in advance satya
I've always used the identity field in SQL server to maintain the unique id for a table. With the new DB design at work we brought in a dba and she made us move away from allowing SQL maintain the unique field and having us maintain the unique field in code. To do that we had to start a transaction, do a select max(id) + 1, insert into table, commit transaction. Doing it this way, I'm starting to see deadlocks due to the transactions locking the table.
Getting down to what I wanted to know, what is the pro's/con's you guys see in maintaining he unique ID this way and is there a better way of creating an unique id in T-SQL code?
I am new to MS SQL and I was wondering is it possible to create a table with unique rows?? By this I mean if a table has two columns then a duplicate row would be if BOTH columns matched two columns of another row.
