Default constraints in SQL Server 2000

8 Jun 2006 6:03 PM

deathtospam

I have a int column, SortOrder, that explicitly defines the order in
which rows should be sorted. Is it possible to assign each new row a
default value equal to the number of existing rows plus one? For
example, if I have a blank table and I execute the following query on a
blank table, I get "1" back:

SELECT COUNT(*)+1 FROM tblSample

If I run it again with one row in it, I get "2". Is it possible to use
this formula, or something like it, to assign default values to a
column? And if so, how?

-= Tek Boy =-

8 Jun 2006 6:11 PM

Jim Underwood

Have you looked into using IDENTITY for this?

You may get gaps in your sequence, but the order will still be the same.

<deathtospam@gmail.com> wrote in message
Show quoteHide quote

news:1149789811.900319.245860@h76g2000cwa.googlegroups.com...
> I have a int column, SortOrder, that explicitly defines the order in
> which rows should be sorted. Is it possible to assign each new row a
> default value equal to the number of existing rows plus one? For
> example, if I have a blank table and I execute the following query on a
> blank table, I get "1" back:
>
> SELECT COUNT(*)+1 FROM tblSample
>
>
> If I run it again with one row in it, I get "2". Is it possible to use
> this formula, or something like it, to assign default values to a
> column? And if so, how?
>
>
> -= Tek Boy =-
>

8 Jun 2006 6:24 PM

deathtospam

Jim Underwood wrote:

> Have you looked into using IDENTITY for this?
>
> You may get gaps in your sequence, but the order will still be the same.

I already have an IDENTITY column as my primary key. What I want is a
non-key column that I can assign sequential numbers to upon row
creation, based on the number of existing rows in the table.

-= Tek Boy =-

8 Jun 2006 7:04 PM

Steve Kass

Tek Boy,

Can you say more about your requirements so it's clear why
the identity column doesn't do what you need? Will you want
to change these values from the default value at some point, or
do you need these to tell you more than just the sorting order?

Steve Kass
Drew University
http://www.stevekass.com

deathtospam@gmail.com wrote:

Show quoteHide quote

>Jim Underwood wrote:
>
>
>>Have you looked into using IDENTITY for this?
>>
>>You may get gaps in your sequence, but the order will still be the same.
>>
>>
>
>I already have an IDENTITY column as my primary key. What I want is a
>non-key column that I can assign sequential numbers to upon row
>creation, based on the number of existing rows in the table.
>
>
>-= Tek Boy =-
>
>
>

8 Jun 2006 8:16 PM

deathtospam

Steve Kass wrote:

> Can you say more about your requirements so it's clear why
> the identity column doesn't do what you need? Will you want
> to change these values from the default value at some point, or
> do you need these to tell you more than just the sorting order?

Let's say I'm creating a Departments table:

CREATE TABLE [Departments] (
    [DepartmentID] [int] IDENTITY (1, 1) NOT NULL ,
    [DepartmentName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
    [SortOrderID] [int] NOT NULL ,
    CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED
    (
        [DepartmentID]
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO Departments VALUES ('MIS', 1)
INSERT INTO Departments VALUES ('Human Resources', 2)
INSERT INTO Departments VALUES ('Astronomy', 3)

I want to make sure that the next row inserted into the Departments
table has a SortOrderID value of "4". Eventually, these orders may
change -- for example, some departments may want to be displayed more
prominently. I also don't want to be so strict as to prevent two
departments from having the same SortOrderID value, which is why I
avoided the IDENTITY field idea.

Does that make a bit more sense?

-= Tek Boy =-

9 Jun 2006 1:02 PM

Jim Underwood

Maybe put a trigger on the table that sets sort order equal to the identity,
if sort order is null.

If sort order is specified, then leave the value as is.

This will give you the order, but allow you to change it later.

<deathtospam@gmail.com> wrote in message
Show quoteHide quote

news:1149797808.793070.83710@h76g2000cwa.googlegroups.com...
> Steve Kass wrote:
> > Can you say more about your requirements so it's clear why
> > the identity column doesn't do what you need? Will you want
> > to change these values from the default value at some point, or
> > do you need these to tell you more than just the sorting order?
>
> Let's say I'm creating a Departments table:
>
> CREATE TABLE [Departments] (
> [DepartmentID] [int] IDENTITY (1, 1) NOT NULL ,
> [DepartmentName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [SortOrderID] [int] NOT NULL ,
> CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED
> (
> [DepartmentID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> INSERT INTO Departments VALUES ('MIS', 1)
> INSERT INTO Departments VALUES ('Human Resources', 2)
> INSERT INTO Departments VALUES ('Astronomy', 3)
>
>
>
> I want to make sure that the next row inserted into the Departments
> table has a SortOrderID value of "4". Eventually, these orders may
> change -- for example, some departments may want to be displayed more
> prominently. I also don't want to be so strict as to prevent two
> departments from having the same SortOrderID value, which is why I
> avoided the IDENTITY field idea.
>
> Does that make a bit more sense?
>
>
> -= Tek Boy =-
>

8 Jun 2006 7:30 PM

Vern Rabe

You can create a function that returns the rowcount + 1, and use the function
as a DEFAULT. The following may do what you want. Note that if you do
multiple inserts with one statement (the last insert example below does
that), then you'll have duplicate values.

CREATE FUNCTION dbo.fnNext()
returns int as
BEGIN
    DECLARE @Cnt int;
    SELECT @Cnt = count(*) + 1 FROM dbo.TestTable;
    RETURN @Cnt;
END
go

CREATE TABLE dbo.TestTable (col1 int DEFAULT dbo.fnNext(), col2 int);
go

INSERT INTO dbo.TestTable (col2) VALUES (1);
INSERT INTO dbo.TestTable (col2) VALUES (2);
INSERT INTO dbo.TestTable (col2) VALUES (3);
INSERT INTO dbo.TestTable (col2) VALUES (4);
INSERT INTO dbo.TestTable (col2) VALUES (5);

INSERT INTO dbo.TestTable (col2)
SELECT 6
    UNION ALL
SELECT 7;

SELECT * FROM dbo.TestTable;

DROP TABLE dbo.TestTable;
DROP FUNCTION dbo.fnNext;

HTH
Vern

Show quoteHide quote

"deathtospam@gmail.com" wrote:

> I have a int column, SortOrder, that explicitly defines the order in
> which rows should be sorted. Is it possible to assign each new row a
> default value equal to the number of existing rows plus one? For
> example, if I have a blank table and I execute the following query on a
> blank table, I get "1" back:
>
> SELECT COUNT(*)+1 FROM tblSample
>
>
> If I run it again with one row in it, I get "2". Is it possible to use
> this formula, or something like it, to assign default values to a
> column? And if so, how?
>
>
> -= Tek Boy =-
>
>

8 Jun 2006 8:20 PM

deathtospam

Vern Rabe wrote:

> You can create a function that returns the rowcount + 1, and use the function
> as a DEFAULT. The following may do what you want. Note that if you do
> multiple inserts with one statement (the last insert example below does
> that), then you'll have duplicate values.

Having duplicate values doesn't bother me, if it comes down to it. And
thanks to Steve Kass, I recently discovered how to use UDFs in default
constraints and calculated/virtual fields. However, if there's an
easier way to do this, I'd really like to know how -- I don't want to
create a UDF, if there's a less intensive way of doing it.

Thank you for the advice, though... I may very well use your method, if
I can't find one that doesn't rely on UDFs.

-= Tek Boy =-