Default Column Values

I want to be able to set the default value of a column to be the next number
available, ie. max(MyColumn) + 1 (?).
Ordinarily, you would use an identity field for this, but a) we already have
one (primary key) and b) this value will possibly change such that several
rows will have the same MyColumn value.
Any suggestions?
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]You could either a) let your application control the insertion of data
(which is probaby the best solution because it simplifies the
validation of data on the data level) OR b) write a INSERT trigger to
find the max value and if this column is not specified, then insert the
business rule you specified.
I try to avoid triggers when I can, because I think it places a burden
on your database performance, and I do a lot of DTS bulk inserts (which
don't fire triggers by default).
Stu|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1124361243.951718.201220@.g49g2000cwa.googlegroups.com...
> You could either a) let your application control the insertion of data
> (which is probaby the best solution because it simplifies the
> validation of data on the data level) OR b) write a INSERT trigger to
> find the max value and if this column is not specified, then insert the
> business rule you specified.
> I try to avoid triggers when I can, because I think it places a burden
> on your database performance, and I do a lot of DTS bulk inserts (which
> don't fire triggers by default).
>
So the formula can't be used in the columns Default Value property?
Why would a simple trigger like that burden the server any more than an
extra query to the Db to determine the appropriate value? I'm not
disagreeing with you, I'm just curious...
Server load is not such a big issue for me, but then again, that's not
really a reason to ignore it...
Chris|||You could write a function that returns the max + 1, and use it as a default
value, but this solution will have issues:
create function dbo.fn_nextkey() returns int
as
begin
return coalesce((select max(keycol) + 1 from t1), 1);
end
go
create table t1
(
keycol int not null primary key default dbo.fn_nextkey(),
datacol varchar(10) not null
);
go
insert into t1(datacol) values('a');
insert into t1(datacol) values('b');
insert into t1(datacol) values('c');
select * from t1;
keycol datacol
-- --
1 a
2 b
3 c
Multiple processes inserting at the same time will get the same value, and
you will get pk violation errors that you'd need to trap and handle.
A better option would be to create a table that maintains the last assigned
value:
create table seq(val int not null);
insert into seq values(0);
And increment the value every time you need a new key using a stored
procedure:
create proc usp_nextkey @.o as int output
as
update seq set @.o = val = val + 1;
go
When you need a new key, invoke the proc as follows:
declare @.i as int;
exec usp_nextkey @.i output;
insert into t1 values(@.i, 'd');
BG, SQL Server MVP
www.SolidQualityLearning.com
"CJM" wrote:

> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1124361243.951718.201220@.g49g2000cwa.googlegroups.com...
>
> So the formula can't be used in the columns Default Value property?
> Why would a simple trigger like that burden the server any more than an
> extra query to the Db to determine the appropriate value? I'm not
> disagreeing with you, I'm just curious...
> Server load is not such a big issue for me, but then again, that's not
> really a reason to ignore it...
> Chris
>
>|||> (which is probaby the best solution because it simplifies the
> validation of data on the data level)
In general, unless you have a magical way of preventing users from accessing
the data *except* through your application, there is no good place to put
data validation *except* in the data layer. YMMV.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXs1FL$oFHA.1044@.tk2msftngp13.phx.gbl...
> In general, unless you have a magical way of preventing users from
> accessing the data *except* through your application, there is no good
> place to put data validation *except* in the data layer. YMMV.
>
Aaron,
I agree with you here... So out of interest, would you calculate the next
value within the same SP that inserts the row, or would you us a trigger?
(or another alternative?)
Chris|||I guess it's a matter of scale; we tend to insert a lot of data at one
time, and I try to minimize the queries to my database as much as
possible. In this particular case, the trigger would not be onerous,
but I've seen some really, really bad triggers written that can suck
the life out a server. I just tend to avoid them; not that they're
always bad, but in most of our applications we try to have the data be
as clean as possible before inserting it into the database. In other
words, we do all the lookups and data prep on the business logic layer,
not in the database.
Again, it's a matter of scale; we insert a lot of data at a very high
rate of speed; the simpler the INSERT process is, the better.
Stu|||Personally, I like Itzik's solution, I just kind of cringe a bit at the
syntax:
update table set @.variable = column = column + 1;
But that's just a minor pet peeve I guess.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OSaS2Z$oFHA.568@.TK2MSFTNGP10.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uXs1FL$oFHA.1044@.tk2msftngp13.phx.gbl...
> Aaron,
> I agree with you here... So out of interest, would you calculate the next
> value within the same SP that inserts the row, or would you us a trigger?
> (or another alternative?)
> Chris
>|||no magic; we just lock our data servers down pretty tight, using
application roles, etc. You are correct in that someone could bypass
our application, but we do our best to limit that possibility.
As far as validation goes, I agree. I'm just saying that validation
should be as simple as possible on the database level (e.g., is the
value with constrained parameters? Does it exist in a relationship
with other values?), and that more complex permutations should be
assigned at the business tier level before it gets written to the
database.
Stu
PS: in my previous posts, I used the term application in a broad sense,
encompassing both presentation and business logic tiers. Just wanted
to clarify.