Hi All
I am looking for a bit of advice.
I am in the process of creating a database in which it has been decided that
all primary keys are going to varchar(40). Not my decision, but anyway.
When inserting into each table it will be possible to specify a value for
the primary, but if not specified a value should be auto-generated. That
means that the values in the primary key field can be a mixture of both
numbers and letters, but if auto-generated it should just be a number.
What be the best way to make this autogenerated values if no value is being
specified in the insert?
TIA
KlausDepends really, if auto-generated does it just need to be a number? Any old
number, or a specific format and range?
I always recommend putting a surrogate key on the tables and use that as the
foriegn key and inside the application (not for display purposes, but for
use as the value in a listbox for instance), that can be a int column with
the IDENTITY property, not null and have a unique constraint on it.
You could set the value of the primary key to that if not specified, that
would save calculating a new unique number.
Otherwise, you could use an 'instead of' trigger, for example...
Instead of using MAX, you could take the value from a table that holds the
last number used.
create table testtrg (
mycol int not null unique
)
go
insert testtrg ( mycol ) values ( 1 )
go
create trigger trgTestTrg on testtrg instead of insert
as
begin
if @.@.rowcount = 0
return
declare @.nextid int
begin tran
set @.nextid = ( select max( mycol )
from testtrg with (tablockx) )
set @.nextid = isnull( @.nextid, 0 ) + 1
insert testtrg values( @.nextid )
commit tran
end
go
-- Note, inserting 1 but it already exists so should give a key violation,
-- but the instead of trigger code kicks in and gives the next id.
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Klaus" <Klaus@.discussions.microsoft.com> wrote in message
news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
> Hi All
> I am looking for a bit of advice.
> I am in the process of creating a database in which it has been decided
> that
> all primary keys are going to varchar(40). Not my decision, but anyway.
> When inserting into each table it will be possible to specify a value for
> the primary, but if not specified a value should be auto-generated. That
> means that the values in the primary key field can be a mixture of both
> numbers and letters, but if auto-generated it should just be a number.
> What be the best way to make this autogenerated values if no value is
> being
> specified in the insert?
> TIA
> Klaus
>|||Thanks a lot, Tony. That was very helpfull.
I will create a unique field on each of my tables. The value for this will
be auto-generated using identity. A trigger will then keep an eye on the
inserts. If no value is being specified for the Primary key, the Identity
value will be copied into the varchar(40) primary key field.
-- Klaus
"Tony Rogerson" wrote:
> Depends really, if auto-generated does it just need to be a number? Any ol
d
> number, or a specific format and range?
> I always recommend putting a surrogate key on the tables and use that as t
he
> foriegn key and inside the application (not for display purposes, but for
> use as the value in a listbox for instance), that can be a int column with
> the IDENTITY property, not null and have a unique constraint on it.
> You could set the value of the primary key to that if not specified, that
> would save calculating a new unique number.
> Otherwise, you could use an 'instead of' trigger, for example...
> Instead of using MAX, you could take the value from a table that holds the
> last number used.
> create table testtrg (
> mycol int not null unique
> )
> go
>
> insert testtrg ( mycol ) values ( 1 )
> go
>
> create trigger trgTestTrg on testtrg instead of insert
> as
> begin
> if @.@.rowcount = 0
> return
>
> declare @.nextid int
>
> begin tran
>
> set @.nextid = ( select max( mycol )
> from testtrg with (tablockx) )
>
> set @.nextid = isnull( @.nextid, 0 ) + 1
>
> insert testtrg values( @.nextid )
>
> commit tran
>
> end
> go
>
> -- Note, inserting 1 but it already exists so should give a key violation
,
> -- but the instead of trigger code kicks in and gives the next id.
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> go
>
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Klaus" <Klaus@.discussions.microsoft.com> wrote in message
> news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
>
>|||The second part of Tony's point should not be lost. You should use that int
key as the FK for relationships with other tables. If you need to show your
client the benefit of using an int instead of a varchar(40). Load up a
couple of tables with some test data. Perform join's using varchar(40) as
the keys and then the same using int as the key. The performance difference
is noticable.
So use an int (or even bigint) PK, put a unique constraint on the
varchar(40) column and for all business logic purposes, the varchar(40) fiel
d
is the "key". But behind the scenes in the database the far more efficient
int is the key.
John Scragg
"Klaus" wrote:
> Thanks a lot, Tony. That was very helpfull.
> I will create a unique field on each of my tables. The value for this will
> be auto-generated using identity. A trigger will then keep an eye on the
> inserts. If no value is being specified for the Primary key, the Identity
> value will be copied into the varchar(40) primary key field.
> -- Klaus
> "Tony Rogerson" wrote:
>|||Also, if you need the data in a varchar(40) field you can use a calculated
column (if they dont need to enter it).
I concurr with Tony & John, if you're doing joins, definately use the INT
field as the joining field, joining on varchar fields gets very slow at
medium to high data volumes.
create table ( id int identity(1,1) primary key , myPK AS cast( ID as
varchar(40)) )
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:EDB86798-0F96-415A-9D8D-733ED2E0CA02@.microsoft.com...
> The second part of Tony's point should not be lost. You should use that
int
> key as the FK for relationships with other tables. If you need to show
your
> client the benefit of using an int instead of a varchar(40). Load up a
> couple of tables with some test data. Perform join's using varchar(40) as
> the keys and then the same using int as the key. The performance
difference
> is noticable.
> So use an int (or even bigint) PK, put a unique constraint on the
> varchar(40) column and for all business logic purposes, the varchar(40)
field
> is the "key". But behind the scenes in the database the far more
efficient
> int is the key.
> John Scragg
> "Klaus" wrote:
>
will
Identity
Any old
as the
for
with
that
the
violation,
decided
anyway.
value for
That
both
number.
is
. And thank again for the pointing.
. That's exactly what I had in mind.
Leo Mwangi wrote: