Best Primary Key Solution?

Hi there,

Looking for a bit of help with my problem:

Say i have 3 tables-

tblClients
clientID (primary key identity/autonumber)
clientName (varchar 50)

tblCities
cityID (primary key identity/autonumber)
cityName (varchar 50)

tblClientsCities
ID (primary key identity/autonumber)
clientID (int)
cityID (int)

A client can be located in more than 1 city so i have tblClientsCities (think thats the right way to do it). Say i add a new client and the autonumber changes to "10" which is that client's identifier. How do i then add that identifier to tblClientsCities? I mean it could have been 3,7,205 absolutley anything.

I thought is would be easier to make up a unique key for each client with a script eg

client name: PJ Computers
Unique key Generated: PJCOMP58784

Now that the primary key is known in advance it can be added to tblClients and then tblClientCities. But! i was reading around and many seem to think primary key's like this will slow things down.

So my question is what's the best way of accomplishing this?

Any help would be much appreciated, thanks :)Check out @.@.identity (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp) and/or scope_identity (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sa-ses_6n8p.asp). These allow you to work with IDENTITY columns.

"Smart key" values like you suggested are bad for many reasons. The biggest practical problem is key colisions. The biggest theoretical problem is data changes and how those affect the smart key. There are many other problems, these are just the tip of the iceberg.

If you want to pursue an avenue a lot like your "smart key" approach that does not have the problems, consider using GUID values using NewId (http://msdn.microsoft.com/library/en-us/tsqlref/ts_na-nop_4pt0.asp) and UniqueIdentifier (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_6dyq.asp) columns.

-PatP

best practice retrieveing current identity value

HI all,
I have a claim table. An insert trigger, in that insert trigger I want to
retrieve the current identity value of that insert, to be used elswhere
which is best scope_identity(), ident_current(), or @.@.identity
Thanks
RobertRobert,
All three will (in theory) work, and each has it's own values for a given
situation.
I tend to use @.@.identity, though, as BoL explains, this is not limited to
the current scope, and so they would suggest using SCOPE_IDENTITY which woul
d
be more exacting.
Hope this assists,
Tony
"Robert Bravery" wrote:

> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
>
>|||Robert Bravery wrote:
> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
Did you read about the differences between those functions in Books
Online? Either may be appropriate depending on requirements.
BUT - a big but - they are all unlikely to be useful in a trigger.
That's because well-written trigger code should always assume that more
than one row may be updated in the table that invokes the trigger.
Triggers fire once per statement, NOT once per row, so usually in a
trigger if you want to retrieve the inserted IDENTITY values you do so
by referencing the virtual table called INSERTED. That table could
contain 0,1,2 or any number of rows.
The IDENTITY functions would probably only be useful in a trigger if
your trigger contained a single row INSERT to another table regardless
of how many rows were updated by the statement that prompted the
trigger. In that case you would probably use SCOPE_IDENTITY.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Interesting point. Where would that leave us when using either
SCOPE_IDENTITY or @.@.Identity from a SProc? I was under the belief that the
correct identity was always returned from that thread used. Would you say
then that a trigger could be less precise than a SProc for retrieving the
correct Identity for a given inert transaction?
Any further insight would be useful,
Thanks,
Tony
"David Portas" wrote:

> Robert Bravery wrote:
> Did you read about the differences between those functions in Books
> Online? Either may be appropriate depending on requirements.
> BUT - a big but - they are all unlikely to be useful in a trigger.
> That's because well-written trigger code should always assume that more
> than one row may be updated in the table that invokes the trigger.
> Triggers fire once per statement, NOT once per row, so usually in a
> trigger if you want to retrieve the inserted IDENTITY values you do so
> by referencing the virtual table called INSERTED. That table could
> contain 0,1,2 or any number of rows.
> The IDENTITY functions would probably only be useful in a trigger if
> your trigger contained a single row INSERT to another table regardless
> of how many rows were updated by the statement that prompted the
> trigger. In that case you would probably use SCOPE_IDENTITY.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Tony Scott wrote:
> David,
> Interesting point. Where would that leave us when using either
> SCOPE_IDENTITY or @.@.Identity from a SProc? I was under the belief that the
> correct identity was always returned from that thread used. Would you say
> then that a trigger could be less precise than a SProc for retrieving the
> correct Identity for a given inert transaction?
> Any further insight would be useful,
> Thanks,
> Tony
>
SCOPE_IDENTITY does always have local scope but so does the INSERTED
virtual table in a trigger. In that respect a trigger is no less
precise in the way IDENTITY is retrieved - it's just that you have to
allow for multiple-row inserts. Example:
CREATE TABLE T1 (x INTEGER NOT NULL IDENTITY PRIMARY KEY, z INTEGER NOT
NULL UNIQUE)
GO
CREATE TRIGGER trg ON T1 FOR INSERT
AS
SELECT SCOPE_IDENTITY() AS [scope_identity] ;
SELECT @.@.IDENTITY AS [@.@.identity] ;
SELECT IDENT_CURRENT('T1') AS [ident_current] ;
GO
INSERT INTO T1 (z)
SELECT 1 UNION ALL
SELECT 2 ;
scope_identity
---
NULL
(1 row(s) affected)
@.@.identity
---
2
(1 row(s) affected)
ident_current
---
2
(1 row(s) affected)
The first result (NULL) is wrong because SCOPE_IDENTITY has local scope
so it doesn't see the INSERT at all.
The second is wrong because although it returns one of the IDENTITY
values there were actually two rows inserted. We can't predict the row
for which the IDENTITY will be returned - it will just be the highest
numbered IDENTITY value. This isn't good in a trigger because we can
only use this method to reference a single row and triggers should
always assume multiple rows may be updated.
The third result may be wrong if other connections also update the
table because IDENT_CURRENT is scoped to the table and not to the
session.
The reliable method uses the INSERTED table:
CREATE TRIGGER trg ON T1 FOR INSERT
AS
SELECT x FROM inserted ;
GO
If your procs insert multiple rows you also need to think about the
same issue in procs when you need the IDENTITY value. Don't assume the
values inserted will be contiguous with the value returned by
SCOPE_IDENTITY. There are at least some conditions where they may not
be.
In SQL Server 2005 you can address things slightly differently using
the OUPUT clause of the INSERT, UPDATE and DELETE statements. These
could eliminate the need for some triggers.
In SQL Server 2000 you should also be able to retrieve multiple
IDENTITY values using an alternate key of the table. There should
always be another candidate key. If you don't have such a key then you
have a significant design flaw which you should fix.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Thank you for an excellent answer, very informative.
Tony
"David Portas" wrote:

> Tony Scott wrote:
> SCOPE_IDENTITY does always have local scope but so does the INSERTED
> virtual table in a trigger. In that respect a trigger is no less
> precise in the way IDENTITY is retrieved - it's just that you have to
> allow for multiple-row inserts. Example:
> CREATE TABLE T1 (x INTEGER NOT NULL IDENTITY PRIMARY KEY, z INTEGER NOT
> NULL UNIQUE)
> GO
> CREATE TRIGGER trg ON T1 FOR INSERT
> AS
> SELECT SCOPE_IDENTITY() AS [scope_identity] ;
> SELECT @.@.IDENTITY AS [@.@.identity] ;
> SELECT IDENT_CURRENT('T1') AS [ident_current] ;
> GO
> INSERT INTO T1 (z)
> SELECT 1 UNION ALL
> SELECT 2 ;
> scope_identity
> ---
> NULL
> (1 row(s) affected)
> @.@.identity
> ---
> 2
> (1 row(s) affected)
> ident_current
> ---
> 2
> (1 row(s) affected)
>
> The first result (NULL) is wrong because SCOPE_IDENTITY has local scope
> so it doesn't see the INSERT at all.
> The second is wrong because although it returns one of the IDENTITY
> values there were actually two rows inserted. We can't predict the row
> for which the IDENTITY will be returned - it will just be the highest
> numbered IDENTITY value. This isn't good in a trigger because we can
> only use this method to reference a single row and triggers should
> always assume multiple rows may be updated.
> The third result may be wrong if other connections also update the
> table because IDENT_CURRENT is scoped to the table and not to the
> session.
> The reliable method uses the INSERTED table:
> CREATE TRIGGER trg ON T1 FOR INSERT
> AS
> SELECT x FROM inserted ;
> GO
> If your procs insert multiple rows you also need to think about the
> same issue in procs when you need the IDENTITY value. Don't assume the
> values inserted will be contiguous with the value returned by
> SCOPE_IDENTITY. There are at least some conditions where they may not
> be.
> In SQL Server 2005 you can address things slightly differently using
> the OUPUT clause of the INSERT, UPDATE and DELETE statements. These
> could eliminate the need for some triggers.
> In SQL Server 2000 you should also be able to retrieve multiple
> IDENTITY values using an alternate key of the table. There should
> always be another candidate key. If you don't have such a key then you
> have a significant design flaw which you should fix.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Hi,
Thanks for the reply David.

> Did you read about the differences between those functions in Books
> Online? Either may be appropriate depending on requirements.
Yes I did. And I also got the idea that they all might be appropiate. But no
quite understanding the exact process of a insert trigger (as you explained)
I was as to what was the more correct way

> BUT - a big but - they are all unlikely to be useful in a trigger.
> That's because well-written trigger code should always assume that more
> than one row may be updated in the table that invokes the trigger.
> Triggers fire once per statement, NOT once per row, so usually in a
> trigger if you want to retrieve the inserted IDENTITY values you do so
> by referencing the virtual table called INSERTED. That table could
> contain 0,1,2 or any number of rows.
At this point I am assumning a single row insert transaction. The table
involved is a claims table, needing a single input for each claim
as in:
INSERT INTO [RASRMIS].[dbo].[Claim]([divid], [dhid], [LayerID], [peril],
[cause], [resource], [fault], [DOL], [DREP])
VALUES(1812, 1237, 5, 1, 1, 1, 1, getdate()-1650, getdate())
Would this be considered as a single row insert, on a single statement,
single transaction
I then issued:
Select @.@.identity [@.@.Identity], SCOPE_IDENTITY() [SCOPE_IDENTITY()],
ident_current('claim') [ident_current()]
But got back all the same value.
But if I put the above statement into a trigger, I receive the dame values
from the @.@.identity and ident_current() functions. Scope_identity() returns
null. I would have thought that the insert trigger is within scope here
Hopefully I have explained correctly
Thankls
Robert|||Robert Bravery wrote:
> At this point I am assumning a single row insert transaction.
Why would you bother to assume that? It doesn't help you. It just means
the DBA will curse you one day when he needs to do some ad-hoc
maintenance... or integrate some external data... or the user needs an
enhancement to the application, or...

> The table
> involved is a claims table, needing a single input for each claim
> as in:
> INSERT INTO [RASRMIS].[dbo].[Claim]([divid], [dhid], [LayerID], [peril],
> [cause], [resource], [fault], [DOL], [DREP])
> VALUES(1812, 1237, 5, 1, 1, 1, 1, getdate()-1650, getdate())
> Would this be considered as a single row insert, on a single statement,
> single transaction
> I then issued:
> Select @.@.identity [@.@.Identity], SCOPE_IDENTITY() [SCOPE_IDENTITY()],
> ident_current('claim') [ident_current()]
> But got back all the same value.
> But if I put the above statement into a trigger, I receive the dame values
> from the @.@.identity and ident_current() functions. Scope_identity() return
s
> null. I would have thought that the insert trigger is within scope here
>
No. The trigger runs in its own scope.
The solution is:
SELECT id FROM inserted ;
"Inserted" is a virtual table only visible in triggers.
This isn't a good example because you don't usually want to return
results from triggers. The exact solution of course depends on what you
want to do with the IDENTITY value(s) after you retrieve them. For
example you can insert them to another table:
INSERT INTO other_table (id, ...)
SELECT id
FROM inserted ;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Unless you're using an INSTEAD OF INSERT trigger, then you shouldn't rely on
the value returned by @.@.IDENTITY; under no circumstances should you rely on
the value returned by IDENT_CURRENT()--at least not for what you're looking
for. There can be more than one FOR or AFTER INSERT trigger on a table, and
any one of them could affect @.@.IDENTITY. IDENT_CURRENT() changes whenever
any INSERT occurs on any connection, so it could change between the time
that you read it and the time that you're ready to use it, or even worse: it
could change during whatever modification you're trying to make. In a FOR
or AFTER trigger, the only reliable ways to retrieve the generated IDENTITY
values is to use the inserted pseudotable, or to use another candidate key.
An INSTEAD OF trigger fires instead of the action specified, so new IDENTITY
values haven't yet been generated at the time that the inserted pseudotable
is populated. You can use SCOPE_IDENTITY() if you use a cursor to process
the contents of the inserted pseudotable, but I would recommend against it.
Using cursors in triggers is like publishing uncomplimentary caricatures of
Mohammed: you're bound to get burned. In an INSTEAD OF INSERT trigger,
you're best bet is to use another candidate key to obtain the IDENTITY
values.
"Robert Bravery" <me@.u.com> wrote in message
news:u07T2i9KGHA.1424@.TK2MSFTNGP12.phx.gbl...
> HI all,
> I have a claim table. An insert trigger, in that insert trigger I want to
> retrieve the current identity value of that insert, to be used elswhere
> which is best scope_identity(), ident_current(), or @.@.identity
> Thanks
> Robert
>

Best practice

Hi,
What is best, do relation direct or create id (IDENTITY) column?
for exemple: (direct)
---
CREATE TABLE Test
(
TestDes CHAR(20)
CONSTRAINT Test__TestDes __pk PRIMARY KEY(TestDes)
NOT NULL
)
CREATE TABLE Test2
(
TestDes CHAR(20)
CONSTRAINT Test2_TestDes__fk FOREIGN KEY(TestDes)
References Test(TestDes )
ON UPDATE CASCADE
ON DELETE CASCADE,
NOT NULL
)
for exemple: (id)
---
CREATE TABLE Test
(
id_num int IDENTITY(1,1),
CONSTRAINT Test__id_num __pk PRIMARY KEY(id_num),
TestDes CHAR(20)
NOT NULL
)
CREATE TABLE Test2
(
TestDes CHAR(20)
NOT NULL,
id_num int
CONSTRAINT Test2_num __fk FOREIGN KEY(num )
References Test(num )
ON UPDATE CASCADE
ON DELETE CASCADE,
NOT NULL
)ReTF
It's hard to suggest soemthing because I don't know your business
requirements.
An IDENITY property is the best candidate for artificial keys.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:eXF%23nF2lFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> What is best, do relation direct or create id (IDENTITY) column?
> for exemple: (direct)
> ---
> CREATE TABLE Test
> (
> TestDes CHAR(20)
> CONSTRAINT Test__TestDes __pk PRIMARY KEY(TestDes)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> CONSTRAINT Test2_TestDes__fk FOREIGN KEY(TestDes)
> References Test(TestDes )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
> for exemple: (id)
> ---
> CREATE TABLE Test
> (
> id_num int IDENTITY(1,1),
> CONSTRAINT Test__id_num __pk PRIMARY KEY(id_num),
> TestDes CHAR(20)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> NOT NULL,
> id_num int
> CONSTRAINT Test2_num __fk FOREIGN KEY(num )
> References Test(num )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
>|||Are you asking if it is better to enforce referential integrity using a
natural key or a surrogate key? It depends on your specific cirsumstance. If
the natural key is subject to change (for example LastName, EMail, or
ZipCode) then definately use a surrogate key. You don't want to propogate
updates throughout your system every time someone changes their name or
invoice number coding scheme, especially if these foreign keys have been
migrated to external databases like a data warehouse. Also, there are cases
where the natural key is so wide (for example a multi varchar column key)
that it would be a waste of disk storage and memory to attempt to use it in
foreign key relationships. If you do choose to use a surrogate key (such as
an identity column), then you still need to have a unique constraint on the
natural key. However, be forwarned that using a surrogate key for
referential integrity will tick off some relational database theorists.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:eXF%23nF2lFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> What is best, do relation direct or create id (IDENTITY) column?
> for exemple: (direct)
> ---
> CREATE TABLE Test
> (
> TestDes CHAR(20)
> CONSTRAINT Test__TestDes __pk PRIMARY KEY(TestDes)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> CONSTRAINT Test2_TestDes__fk FOREIGN KEY(TestDes)
> References Test(TestDes )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
> for exemple: (id)
> ---
> CREATE TABLE Test
> (
> id_num int IDENTITY(1,1),
> CONSTRAINT Test__id_num __pk PRIMARY KEY(id_num),
> TestDes CHAR(20)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> NOT NULL,
> id_num int
> CONSTRAINT Test2_num __fk FOREIGN KEY(num )
> References Test(num )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
>|||First of all, cascading updates are not possible when using IDENTITY,
because you can't change an identity value.
If your database engine uses locking to isolate transactions, then you
should definitely use surrogate keys.
If you have a limited budget for application development, then you should
definitely use surrogate keys.
Cascading updates can cause deadlocks and reduce concurrency.
Collisions occur more often when using optimistic concurrency without
surrogate keys.
An application that selects from two or more tables without surrogate
keys must issue all of the individual
selects within the same transaction or must include additional logic
to detect and handle key value changes.
Every application that updates a table without a surrogate key must
include additional logic to detect and
handle key value changes.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:eXF#nF2lFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> What is best, do relation direct or create id (IDENTITY) column?
> for exemple: (direct)
> ---
> CREATE TABLE Test
> (
> TestDes CHAR(20)
> CONSTRAINT Test__TestDes __pk PRIMARY KEY(TestDes)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> CONSTRAINT Test2_TestDes__fk FOREIGN KEY(TestDes)
> References Test(TestDes )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
> for exemple: (id)
> ---
> CREATE TABLE Test
> (
> id_num int IDENTITY(1,1),
> CONSTRAINT Test__id_num __pk PRIMARY KEY(id_num),
> TestDes CHAR(20)
> NOT NULL
> )
> CREATE TABLE Test2
> (
> TestDes CHAR(20)
> NOT NULL,
> id_num int
> CONSTRAINT Test2_num __fk FOREIGN KEY(num )
> References Test(num )
> ON UPDATE CASCADE
> ON DELETE CASCADE,
> NOT NULL
> )
>|||Thanks for clarification.
One more question:
What I should use instead CASCADE?
Trigger?
thanks
"Brian Selzer" <brian@.selzer-software.com> escreveu na mensagem
news:%230qTUK3lFHA.3380@.TK2MSFTNGP10.phx.gbl...
> First of all, cascading updates are not possible when using IDENTITY,
> because you can't change an identity value.
> If your database engine uses locking to isolate transactions, then you
> should definitely use surrogate keys.
> If you have a limited budget for application development, then you should
> definitely use surrogate keys.
> Cascading updates can cause deadlocks and reduce concurrency.
> Collisions occur more often when using optimistic concurrency without
> surrogate keys.
> An application that selects from two or more tables without surrogate
> keys must issue all of the individual
> selects within the same transaction or must include additional
> logic
> to detect and handle key value changes.
> Every application that updates a table without a surrogate key must
> include additional logic to detect and
> handle key value changes.
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:eXF#nF2lFHA.1372@.TK2MSFTNGP10.phx.gbl...
>|||Since you can't change an IDENTITY, there is no need for ON UPDATE CASCADE.
I prefer to disallow cascading deletes as well, because they can also cause
deadlocks. In my opinion, the best way to cascade deletes in SQL Server
2000 is in an INSTEAD OF trigger. The reason is that you have control over
the order in which locks are obtained by either issuing select statements
WITH(UPDLOCK), or by specifying the individual delete statements in the
correct order.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:O2Rrop3lFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Thanks for clarification.
> One more question:
> What I should use instead CASCADE?
> Trigger?
> thanks
> "Brian Selzer" <brian@.selzer-software.com> escreveu na mensagem
> news:%230qTUK3lFHA.3380@.TK2MSFTNGP10.phx.gbl...
should
>|||Hi,
If you have time.
Can you show me how to use ' INSTEAD OF trigger' in my sample? Thanks
IF EXISTS(SELECT NAME
FROM sysobjects
WHERE NAME = N'TPARENT'
AND type = 'U')
DROP TABLE TPARENT
GO
CREATE TABLE TPARENT
(
CONSTRAINT pk_tpid
PRIMARY KEY(tpid),
tpid int IDENTITY(1,1),
TPARENT CHAR(30)
NOT NULL
)
go
IF EXISTS(SELECT NAME
FROM sysobjects
WHERE NAME = N'CLILD'
AND type = 'U')
DROP TABLE CLILD
GO
CREATE TABLE CLILD
(
CONSTRAINT fk_tpid
FOREIGN KEY(tpid )
References TPARENT (tpid ),
tpid int,
TPARENT CHAR(30)
NOT NULL
)
go
INSERT INTO TPARENT VALUES ('Test 01')
INSERT INTO TPARENT VALUES ('Test 02')
INSERT INTO TPARENT VALUES ('Test 03')
insert into CLILD VALUES (1, 'Test 01')
insert into CLILD VALUES (2, 'Test 02')
insert into CLILD VALUES (3, 'Test 03')
SELECT * FROM TPARENT
SELECT * FROM CLILD
DELETE TPARENT
DELETE CLILD
UPDATE TPARENT SET tpid = 4 WHERE tpid = 1
UPDATE CLILD SET tpid = 4 WHERE tpid = 1
UPDATE TPARENT SET TPARENT = '' WHERE tpid = 1
UPDATE CLILD SET TPARENT = '' WHERE tpid = 1
"Brian Selzer" <brian@.selzer-software.com> escreveu na mensagem
news:OTI0d53lFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Since you can't change an IDENTITY, there is no need for ON UPDATE
> CASCADE.
> I prefer to disallow cascading deletes as well, because they can also
> cause
> deadlocks. In my opinion, the best way to cascade deletes in SQL Server
> 2000 is in an INSTEAD OF trigger. The reason is that you have control
> over
> the order in which locks are obtained by either issuing select statements
> WITH(UPDLOCK), or by specifying the individual delete statements in the
> correct order.
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:O2Rrop3lFHA.1444@.TK2MSFTNGP10.phx.gbl...
> should
>|||You should always use a relational key. IDENTITY is completely
non-relational and proprietary. The rules are to first look for an
industry standard key. If that fails, then for a natural key. If both
those fail, then very carefully design a key tha tyou can validate and
verify yourself. Get a copy of SQL PROGRAMMING STYLE for details.|||First of all, your example doesn't make any sense. It appears as if you are
trying to create a parent-child relationship, but there isn't a primary key
in the child table, and it appears that the natural key from the parent
table is duplicated in the child.
Here's a simple example of how to do cascade deletes within an instead of
trigger.
CREATE TABLE SalesOrder
(
SalesOrderKey INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_SalesOrder
PRIMARY KEY CLUSTERED,
SalesOrderNumber CHAR(12) NOT NULL CONSTRAINT AK_SalesOrder UNIQUE
NONCLUSTERED,
CustomerKey INT NOT NULL -- REFERENCES Customer(CustomerKey)
-- more columns here
)
CREATE TABLE SalesOrderDetail
(
SalesOrderDetailKey INT IDENTITY(1, 1) NOT NULL CONSTRAINT
PK_SalesOrderDetail PRIMARY KEY CLUSTERED,
SalesOrderKey INT NOT NULL REFERENCES SalesOrder (SalesOrderKey),
SalesOrderLineNo INT NOT NULL,
CONSTRAINT AK_SalesOrderDetail UNIQUE NONCLUSTERED (SalesOrderKey,
SalesOrderLineNo)
-- more columns here
)
GO
CREATE TRIGGER tIOD_SalesOrder ON SalesOrder INSTEAD OF DELETE AS
BEGIN
DECLARE @.X INT
SELECT @.X = SalesOrder.SalesOrderKey
FROM SalesOrder WITH(UPDLOCK)
JOIN deleted
ON (deleted.SalesOrderKey = SalesOrder.SalesOrderKey)
DELETE SalesOrderDetail FROM deleted WHERE
SalesOrderDetail.SalesOrderKey = deleted.SalesOrderKey
DELETE SalesOrder FROM deleted WHERE SalesOrder.SalesOrderKey =
deleted.SalesOrderKey
END
GO
Note how you can control the order in which the SalesOrder and
SalesOrderDetail tables can be locked.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:#TMuRs4lFHA.3780@.tk2msftngp13.phx.gbl...
> Hi,
> If you have time.
> Can you show me how to use ' INSTEAD OF trigger' in my sample? Thanks
>
> IF EXISTS(SELECT NAME
> FROM sysobjects
> WHERE NAME = N'TPARENT'
> AND type = 'U')
> DROP TABLE TPARENT
> GO
> CREATE TABLE TPARENT
> (
> CONSTRAINT pk_tpid
> PRIMARY KEY(tpid),
> tpid int IDENTITY(1,1),
> TPARENT CHAR(30)
> NOT NULL
> )
> go
> IF EXISTS(SELECT NAME
> FROM sysobjects
> WHERE NAME = N'CLILD'
> AND type = 'U')
> DROP TABLE CLILD
> GO
> CREATE TABLE CLILD
> (
> CONSTRAINT fk_tpid
> FOREIGN KEY(tpid )
> References TPARENT (tpid ),
> tpid int,
> TPARENT CHAR(30)
> NOT NULL
> )
> go
>
> INSERT INTO TPARENT VALUES ('Test 01')
> INSERT INTO TPARENT VALUES ('Test 02')
> INSERT INTO TPARENT VALUES ('Test 03')
> insert into CLILD VALUES (1, 'Test 01')
> insert into CLILD VALUES (2, 'Test 02')
> insert into CLILD VALUES (3, 'Test 03')
> SELECT * FROM TPARENT
> SELECT * FROM CLILD
> DELETE TPARENT
> DELETE CLILD
> UPDATE TPARENT SET tpid = 4 WHERE tpid = 1
> UPDATE CLILD SET tpid = 4 WHERE tpid = 1
> UPDATE TPARENT SET TPARENT = '' WHERE tpid = 1
> UPDATE CLILD SET TPARENT = '' WHERE tpid = 1
> "Brian Selzer" <brian@.selzer-software.com> escreveu na mensagem
> news:OTI0d53lFHA.3144@.TK2MSFTNGP12.phx.gbl...
statements
you
must
>|||ReTF,
During initial database design, you should follow Joe Celko's advice and try
to find an industry standard key, and with very few exceptions, every table
should have a natural key. At some point, however, the relational database
engine must be chosen, and if it employs locking to isolate transactions,
then you must transform the database to use surrogate keys, or as close to
them as is possible and practical (IDENTITY). Relational database purists
like Joe tend to ignore the significant costs associated with using natural
keys--that is, the additional development, testing, troubleshooting, and
maintenance that is required for every application program; the
consequential performance and concurrency degradation; and the loss of
flexibility inherent in a database that does not use surrogate keys. (I
think its from spending too much time in the world of Academia.)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123009314.481833.229810@.f14g2000cwb.googlegroups.com...
> You should always use a relational key. IDENTITY is completely
> non-relational and proprietary. The rules are to first look for an
> industry standard key. If that fails, then for a natural key. If both
> those fail, then very carefully design a key tha tyou can validate and
> verify yourself. Get a copy of SQL PROGRAMMING STYLE for details.
>

Best GUID Storage

Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?

Best GUID Storage

Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?

Beginner @IDENTITY question

Hello,
When I execute the following code on my localhost the INSERT statement is successfull and my ID is entered. But for some reason when I uploaded to my remote host the exact same database (MS SQL) and code it keeps trying to enter a NULL value into the ID column- which causes an error? I don't understand becuase it is the exact same app and database but at a remote location.

Is @.IDENTITY somehow saved in the session and my remote servers session settings?? are off?

strSql = "insert into Login (UserName,Password) VALUES ('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "') select @.ID = @.@.IDENTITY";

Thanks in advance for any responses.
-WileyHi Wiley,

First, you should have a ; between the statements:

strSql = "insert into Login (UserName,Password) VALUES ('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "'); select @.ID = @.@.IDENTITY";

Second, you should probably be using SCOPE_IDENTITY instead of @.@.IDENTITY. The two are similar, but SCOPE_IDENTITY returns values inserted only within the current scope. The problem with @.@.IDENTITY is that you might get the ID value from another insert operation. Check out SQL Server Books Online for a more complete description of the issue.

But that doesn't explain your problem. My best guess is that the ID field isn't defined as an identity field in the database on the remote server. That's the first thing to check.

There is a slight chance that SET IDENTITY_INSERT is off in the database. I don't recall that you can set that as the normal setting, but something in the connection might be setting it. Definitely a longshot.

Third,DON'T USE DYNAMIC SQL THIS WAY!!!!! It opens up SQL injection attacks, particularly for fields that are obviously user input. If you don't know the issues, just ask. You are setting yourself up to have your app hacked and attacked.

Don|||Hello,
Part of my problem was that I created the database locally from a .dat file in a database called Resume. Everthing worked great. When I upoaded the tables to my remote assigned database which is called "mydatabase" not Resume I ran into permission problems becuase locally the owner was dbo and remotely the owner was "mydatabase". Also some of the ID fields were not set to IDENTITY.

I basically have my application working(sort of ) now but I have to allow NULLS on every column that allows them in every table for anything to (sort of ) work -which I DID NOT have to do locally and I can't figure out why, maybe someone can shed some light on that for me.

Also, in reference to SQL injection attacks that you mentioned above , maybe you can point me to a link on ms sql security and explain to me what is wrong with using the dynamic sql statement in the way i did. I definitely don't want to start using asp.net and start off on the wrong foot opening myself up for attacks.

thanks,
-Wiley|||there's a lot of info about sql inject that you can find on the web. some of them are:

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=533341

http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

google rocks!|||wrong text on the first link. my mistake.