Board index » delphi » Unique record guarantee?

2006-03-07 02:03:55 PM
delphi42
Hi, if I insert a record to a table in sqlserver2000, the record is added
even if the same record aready exists (without any error)!
in MS Access there is AutoNumber type that increaments for every incoming
new record; so there are no 2 records identical.
what is solution for sqlserver2000?
 

the column as a guid.
Oliver Townshend
 

--
Martijn Tonies
Database Workbench - development tool for MS SQL, and more!
Upscene Productions
www.upscene.com
My thoughts:
blog.upscene.com/martijn/
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Use IDENTITY
Example
IF NOT EXISTS(SELECT [name] FROM sysobjects
WHERE [name] = 'tblAttachment' AND [xtype] = 'U')
BEGIN
CREATE TABLE [tblAttachment]
(
AttachmentID INTEGER IDENTITY(1, 1) NOT NULL,
AttachmentName VARCHAR(255) NOT NULL,
Attachment IMAGE
)
END;
DON'T USE GUID as a column type unless you absolutely absolutely need them.
They tend to kill the performance. Besides IDENTTY makes sure that the
number always stays UNIQUE.
"Oliver Townshend" <oliveratzipdotcomdotau>writes

makes sure it stays unique within any database. Not much call for the
latter, but it does come up.
Oliver Townshend
 

for the given table.
A constraint will make sure it stays unique.
As the original poster said: when inserting the same record ->
so that could include a primary key value, when not enforced.
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
www.upscene.com
My thoughts:
blog.upscene.com/martijn/
Database development questions? Check the forum!
www.databasedevelopmentforum.com
 

Thanks to all friends!
"Naser Fadaei" <XXXX@XXXXX.COM>writes

Oliver Townshend writes:
We have databases on many locations simultaneously and the data
gets merged at our office into one single database. This is only
possible with GUID primary keys.
In my experience GUID performance is not a problem at all.
I think it is a myth.
A guid is internally stored as a 16-byte number if I am not mistaking,
so two guids can be compared by SQL server by simply performing
4 DWORD compares. I wouldn't be surprised if a GUID primary key
is faster than a varchar primary key.
--
Arthur Hoornweg
(In order to reply per e-mail, please just remove the ".net"
from my e-mail address. Leave the rest of the address intact
including the "antispam" part. I had to take this measure to
counteract unsollicited mail.)