Autonumber...

Sorry for my bad English but I'm Italian...

I have a question for you...
I delete all record of table but I don't set autonumber field to start with
value 1...
What do you do to set a values of autonumber colomns with Query Analizer or
Enterprise Manager...?I guess you are looking for a code to reset the identity seed for a table.
Here you go:

DBCC CHECKIDENT (TableName, RESEED, 1)

Shervin

"Matrix" <wdilan_NOSPAM@.tin.it> wrote in message
news:bkp1t5$a7j$1@.grillo.cs.interbusiness.it...
> Sorry for my bad English but I'm Italian...
> I have a question for you...
> I delete all record of table but I don't set autonumber field to start
with
> value 1...
> What do you do to set a values of autonumber colomns with Query Analizer
or
> Enterprise Manager...?|||In article <bkp1t5$a7j$1@.grillo.cs.interbusiness.it>,
wdilan_NOSPAM@.tin.it says...
> What do you do to set a values of autonumber colomns with Query Analizer or
> Enterprise Manager...?

I worried about that for a long time, too. It just seemed unnatural to
have a bunch of missing numbers. But after continually resetting them
(there are a couple of ways to do it -- I see someone already is helping
with that) I finally realized that there is no reason to do so.

Think what the numbers will look like after a few weeks of production.

-- Rick|||Since you want to delete all the rows in the table you can use

TRUNCATE TABLE table_name

That will delete all rows AND reset the seed in one command.

- Jason

> "Matrix" <wdilan_NOSPAM@.tin.it> wrote in message
> news:bkp1t5$a7j$1@.grillo.cs.interbusiness.it...
> > Sorry for my bad English but I'm Italian...
> > I have a question for you...
> > I delete all record of table but I don't set autonumber field to start
> with
> > value 1...
> > What do you do to set a values of autonumber colomns with Query Analizer
> or
> > Enterprise Manager...?|||On 25 Sep 2003 10:57:39 -0700 in comp.databases.ms-sqlserver,
JayCallas@.hotmail.com (Jason) wrote:

>Since you want to delete all the rows in the table you can use
>TRUNCATE TABLE table_name
>That will delete all rows AND reset the seed in one command.

Doesn't seem to work if you have foreign key constraints.

--
A)bort, R)etry, I)nfluence with large hammer.

Autonumber...

Sorry for my bad English but I'm Italian...
I have a question for you...
I delete all record of table but I don't set autonumber field to start with
value 1...
What do you do to set a values of autonumber colomns with Query Analizer or
Enterprise Manager...?See my reply in other group. Please don't multipost.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Matrix" <wdilan_NOSPAM@.tin.it> wrote in message news:bkp1m7$kp9$1@.fata.cs.interbusiness.it...
> Sorry for my bad English but I'm Italian...
> I have a question for you...
> I delete all record of table but I don't set autonumber field to start with
> value 1...
> What do you do to set a values of autonumber colomns with Query Analizer or
> Enterprise Manager...?
>
>

AutoNumber with specified format

Hi all,
In SQLServer 2005, are there any method to create the autonumber with
specific format?
for example,
I would like to have a key which the pattern is ABC00001 and the next
autonumber is ABC00002 and the next autonumber is ABC00003...
However, I won't like to handle it in the front-end application. Any idea?
Regards
alexGood wrote:
> Hi all,
> In SQLServer 2005, are there any method to create the autonumber with
> specific format?
> for example,
> I would like to have a key which the pattern is ABC00001 and the next
> autonumber is ABC00002 and the next autonumber is ABC00003...
> However, I won't like to handle it in the front-end application. Any
> idea?
> Regards
> alex
You'll have to roll that solution yourself. Your proposed key will
consume 8 bytes and give you only 100,000 values - unless you change the
prefix. In any case, I would store the next value as a numeric in a
"next key table" and use an instead of insert trigger on the table to
automatically create the key values. For example:
Set nocount on
go
create table dbo.NextKey (TableName nvarchar(255) not null primary key,
NextKeyVal int not null default 1)
insert into dbo.NextKey (TableName) Values ('TestTable')
Select * from dbo.NextKey
go
Create Table dbo.TestTable (SomeID char(8) not null primary key, SomeCol
int not null)
go
Drop Trigger TestTableIOI
go
Create Trigger TestTableIOI on dbo.TestTable Instead Of Insert
as
Begin
Declare @.NextKeyVal int
If (Select count(*) from inserted) != 1
RAISERROR ('Only one row inserted at a time please', 15, 1)
Else Begin
Update dbo.NextKey Set @.NextKeyVal = NextKeyVal = NextKeyVal + 1
Where TableName = N'TestTable'
If @.NextKeyVal Is Null
RAISERROR ('Could not determine next key value for table: %s', 15,
1, N'TestTable')
Else Begin
Insert dbo.TestTable (SomeID, SomeCol)
Select 'ABC' + right('00000' + cast(@.NextKeyVal as varchar(5)),
5), SomeCol From inserted
End
End
End
Go
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note: Must
supply a dummy value for PK
Select * from dbo.TestTable
Go
Drop Table dbo.TestTable
Drop Table dbo.NextKey
David Gugick - SQL Server MVP
Quest Software|||Alternatively, go with the identity column and use a computed column to gene
rate
the value derived from the identity column. In your example it looks like
the identity you need is just a concatenation of the identity value with
a fixed prefix. For example,
create function dbo.IdAsMyId (@.id int)
returns char(8)
as begin
declare @.prefix char(3);
set @.prefix = 'ABC';
declare @.leadingZeros int;
set @.leadingZeros = 8 - len(@.id) - len(@.prefix);
declare @.myId char(8)
set @.myId = 'ABC' + replicate('0', @.leadingZeros) + cast(@.id as varchar(5));
return @.myId;
end
go
create table CustomIdentity (
id int identity(1,1),
myId as dbo.IdAsMyId(id),
data varchar(255))
go
insert CustomIdentity (data) values ('One')
insert CustomIdentity (data) values ('Two')
insert CustomIdentity (data) values ('Three')
go
select *
from CustomIdentity
go
You probaby want to index the computed column if you're going to search on
it.
Cheers,
Stefan
http://www.fotia.co.uk

> Good wrote:
>
> You'll have to roll that solution yourself. Your proposed key will
> consume 8 bytes and give you only 100,000 values - unless you change
> the prefix. In any case, I would store the next value as a numeric in
> a "next key table" and use an instead of insert trigger on the table
> to automatically create the key values. For example:
> Set nocount on
> go
> create table dbo.NextKey (TableName nvarchar(255) not null primary
> key,
> NextKeyVal int not null default 1)
> insert into dbo.NextKey (TableName) Values ('TestTable')
> Select * from dbo.NextKey
> go
> Create Table dbo.TestTable (SomeID char(8) not null primary key,
> SomeCol
> int not null)
> go
> Drop Trigger TestTableIOI
> go
> Create Trigger TestTableIOI on dbo.TestTable Instead Of Insert
> as
> Begin
> Declare @.NextKeyVal int
> If (Select count(*) from inserted) != 1
> RAISERROR ('Only one row inserted at a time please', 15, 1)
> Else Begin
> Update dbo.NextKey Set @.NextKeyVal = NextKeyVal = NextKeyVal + 1
> Where TableName = N'TestTable'
> If @.NextKeyVal Is Null
> RAISERROR ('Could not determine next key value for table: %s',
> 15,
> 1, N'TestTable')
> Else Begin
> Insert dbo.TestTable (SomeID, SomeCol)
> Select 'ABC' + right('00000' + cast(@.NextKeyVal as varchar(5)),
> 5), SomeCol From inserted
> End
> End
> End
> Go
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Insert into dbo.TestTable (SomeID, SomeCol) Values ('', 5) -- Note:
> Must
> supply a dummy value for PK
> Select * from dbo.TestTable
> Go
> Drop Table dbo.TestTable
> Drop Table dbo.NextKey|||Stefan Delmarco wrote:
> Alternatively, go with the identity column and use a computed column
> to generate the value derived from the identity column. In your
> example it looks like the identity you need is just a concatenation of
> the identity value
> with a fixed prefix. For example,
>
I think if you go that route you are better off using a computed column
without the scalar function.
create table TestTable (id int identity not null primary key, ak as
'ABC' + right('00000' + cast(id as varchar(5)), 5), col1 int not null)
create unique index TestTable_ak on TestTable (ak)
go
insert into TestTable (col1) values (1)
insert into TestTable (col1) values (2)
insert into TestTable (col1) values (3)
Select * from TestTable
go
drop table TestTable
David Gugick - SQL Server MVP
Quest Software

Autonumber when inserting

Hi i have a problem that i can′t solve!
I have three tables last_id, customer, prospect. The "last_id" table have
one colum that stores the last used id number (int), customer tables stores
info about the customer and a "last_id" number and the prospect stores new
customers!
I want to make an insert into customer table from the prospect table and ad
one extra column with the last_id number +1
insert into customer(name,adress,l_id)
select p_name,p_adress,l_id
from prospect,last_id
i don′t want alter the table and use identity
i can make i work with a cursor, is there any other way to do it'Hi
CREATE TABLE #Test
(
col INT
)
INSERT INTO #Test VALUES (1)
GO
INSERT INTO #Test SELECT COALESCE(MAX(col),0)+1 FROM #Test
GO
SELECT * FROM #test
DROP TABLE #Test
"LeSurfer" <LeSurfer@.discussions.microsoft.com> wrote in message
news:8C74D3BF-12D2-4FED-B07D-5070867310B4@.microsoft.com...
> Hi i have a problem that i cant solve!
> I have three tables last_id, customer, prospect. The "last_id" table have
> one colum that stores the last used id number (int), customer tables
stores
> info about the customer and a "last_id" number and the prospect stores new
> customers!
> I want to make an insert into customer table from the prospect table and
ad
> one extra column with the last_id number +1
> insert into customer(name,adress,l_id)
> select p_name,p_adress,l_id
> from prospect,last_id
> i dont want alter the table and use identity
> i can make i work with a cursor, is there any other way to do it'
>|||I couldn′t make it work, i tried this
select coalesce(max(l_id),0)+1,name
from ct_contact cross join kundfil
this is the error message:
Server: Msg 8118, Level 16, State 1, Line 1
Column 'Kundfil.kund' is invalid in the select list because it is not
contained in an aggregate function and there is no GROUP BY clause.
any ideas on what i could try'
"Uri Dimant" wrote:

> Hi
> CREATE TABLE #Test
> (
> col INT
> )
> INSERT INTO #Test VALUES (1)
> GO
> INSERT INTO #Test SELECT COALESCE(MAX(col),0)+1 FROM #Test
> GO
> SELECT * FROM #test
> DROP TABLE #Test
>
> "LeSurfer" <LeSurfer@.discussions.microsoft.com> wrote in message
> news:8C74D3BF-12D2-4FED-B07D-5070867310B4@.microsoft.com...
> stores
> ad
>
>|||Hi
Add this column 'Kundfil.kund' into GROUP BY clause
"LeSurfer" <LeSurfer@.discussions.microsoft.com> wrote in message
news:D5A79FA4-6E93-4EB7-870D-F754B26DF287@.microsoft.com...
> I couldnt make it work, i tried this
> select coalesce(max(l_id),0)+1,name
> from ct_contact cross join kundfil
> this is the error message:
> Server: Msg 8118, Level 16, State 1, Line 1
> Column 'Kundfil.kund' is invalid in the select list because it is not
> contained in an aggregate function and there is no GROUP BY clause.
> any ideas on what i could try'
> "Uri Dimant" wrote:
>
have
new
and

AutoNumber vs. Key

Hi,

I am always confused about what to use as the key for a table.

Let's say I have a company-employee table.

[company]--*[employee]

My co-worker likes to use an AutoNumber or Counter as the key for the
[employee] table (and everything).
I personally use an actual key set for the [employee] table.

So, his table will have one (Autonumber or LONG) column as the key. The
company_id is just another attribute.
Mine, on the other hand, has 2 columns as the key. (i.e.) company_id +
employee_id

So, what is the deal?

--
> There is no answer.
> There has not been an answer.
> There will not be an answer.
> That IS the answer!
> And I am screwed.
> Deadline was due yesterday.
> There is no point to life.
> THAT IS THE POINT.
> And we are screwed.
> We will run out of oil soon.always use an autonumber identity for everythign you do

single-column primary key|||"For everything" is a pretty obscure answer. Other keys are important
too. In a joining table containing only two foreign keys for example
what purpose would IDENTITY serve?

Tom, for the debate you can Google for "Natural Key" and "Surrogate
Key" in this group and in microsoft.public.sqlserver.programming.

--
David Portas
SQL Server MVP
--|||Hi,

Is there a reason for using one and only one column as the key?
I find it hard to enforce the integrity of the database with this design.

for example,
let's say the [company]-[employee]-[sale] tables.
company A has an employee B.
employee B makes a sale C.

If employee B moves to another company, the sale C in the [sale] table will
move along with employee B to the new company.
This doesn't look correct to me.

<dbahooker@.hotmail.com> wrote in message
news:1129059631.679988.51060@.g47g2000cwa.googlegro ups.com...
> always use an autonumber identity for everythign you do
> single-column primary key|||Thanks for the actual terms for these key thingies.

I've searched google but found a lot of different opinions.
Some like autonumber but some like... huh... natural key.

To sum it up from all the posts I've read:
1)
The people who use autonumber said that it performed better and easier to
update the record without affecting the database integrity. (imagine
someone needs to change his or her social security number!!!! all hell broke
lose!!!)

2)
The people who use natural key said that people who use surrogate were just
lazy and not experience with database design. And using natural key could
better model database to the business model.

So... I don't know... that's why I am so confused.
One has physical advantage. The other has logical advantage.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129060967.267313.134080@.z14g2000cwz.googlegr oups.com...
> "For everything" is a pretty obscure answer. Other keys are important
> too. In a joining table containing only two foreign keys for example
> what purpose would IDENTITY serve?
> Tom, for the debate you can Google for "Natural Key" and "Surrogate
> Key" in this group and in microsoft.public.sqlserver.programming.
> --
> David Portas
> SQL Server MVP
> --|||Tom (nospam@.yahoo.com) writes:
> I am always confused about what to use as the key for a table.
> Let's say I have a company-employee table.
> [company]--*[employee]
> My co-worker likes to use an AutoNumber or Counter as the key for the
> [employee] table (and everything).
> I personally use an actual key set for the [employee] table.
> So, his table will have one (Autonumber or LONG) column as the key. The
> company_id is just another attribute.
> Mine, on the other hand, has 2 columns as the key. (i.e.) company_id +
> employee_id

I would say that the two alternatives describes two different things.
In the one-column case, an employee is rather a person, and if he
moves to another company all data that is tied to him are brought with
him.

In the two-column case, you get a new row when he gets a new job.

The latter appears more useful to me. If we want to know who much sales
did everyone in company X generate last year, we can't tell with the
first setup, since we don't know who worked where that year.

If want to know how much aales Bob has generated over the years in
different company, maybe there should be a person table, to which
employees have a reference.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I come to the same conclusion. (see the 10/11/2005 1:03pm post)

As I search Google, this thing is getting very confusing. All the posts
point to the one dead end conclusion -- "It depends".

I need something solid so I don't have to reinvent the wheel (or think about
it). The "It depends" thing just doesn't cut it.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96ECF3C9E8AE7Yazorman@.127.0.0.1...
> Tom (nospam@.yahoo.com) writes:
>> I am always confused about what to use as the key for a table.
>>
>> Let's say I have a company-employee table.
>>
>> [company]--*[employee]
>>
>> My co-worker likes to use an AutoNumber or Counter as the key for the
>> [employee] table (and everything).
>> I personally use an actual key set for the [employee] table.
>>
>> So, his table will have one (Autonumber or LONG) column as the key. The
>> company_id is just another attribute.
>> Mine, on the other hand, has 2 columns as the key. (i.e.) company_id +
>> employee_id
> I would say that the two alternatives describes two different things.
> In the one-column case, an employee is rather a person, and if he
> moves to another company all data that is tied to him are brought with
> him.
> In the two-column case, you get a new row when he gets a new job.
> The latter appears more useful to me. If we want to know who much sales
> did everyone in company X generate last year, we can't tell with the
> first setup, since we don't know who worked where that year.
> If want to know how much aales Bob has generated over the years in
> different company, maybe there should be a person table, to which
> employees have a reference.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||"Tom" <nospam@.yahoo.com> wrote in message
news:hiY2f.481$7h7.465@.newssvr21.news.prodigy.com. ..
> I come to the same conclusion. (see the 10/11/2005 1:03pm post)
> As I search Google, this thing is getting very confusing. All the posts
> point to the one dead end conclusion -- "It depends".
> I need something solid so I don't have to reinvent the wheel (or think
about
> it). The "It depends" thing just doesn't cut it.

"It depends" .

Basically I tend to agree with Celko's philosophy that wheer possible the
key should model reality. If your employees already have some sort of
employee number assigned, use that.

That said, even in many of my tables with natural keys I'll drop in an
Identity column as it can be useful for some ad-hoc queries.

>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96ECF3C9E8AE7Yazorman@.127.0.0.1...
> > Tom (nospam@.yahoo.com) writes:
> >> I am always confused about what to use as the key for a table.
> >>
> >> Let's say I have a company-employee table.
> >>
> >> [company]--*[employee]
> >>
> >> My co-worker likes to use an AutoNumber or Counter as the key for the
> >> [employee] table (and everything).
> >> I personally use an actual key set for the [employee] table.
> >>
> >> So, his table will have one (Autonumber or LONG) column as the key.
The
> >> company_id is just another attribute.
> >> Mine, on the other hand, has 2 columns as the key. (i.e.) company_id +
> >> employee_id
> > I would say that the two alternatives describes two different things.
> > In the one-column case, an employee is rather a person, and if he
> > moves to another company all data that is tied to him are brought with
> > him.
> > In the two-column case, you get a new row when he gets a new job.
> > The latter appears more useful to me. If we want to know who much sales
> > did everyone in company X generate last year, we can't tell with the
> > first setup, since we don't know who worked where that year.
> > If want to know how much aales Bob has generated over the years in
> > different company, maybe there should be a person table, to which
> > employees have a reference.
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techin.../2000/books.asp|||Just my .02:

If you have a natural key that is VERY constant (more constant than a
name, because names can change) and is simple, use it. If you don't,
use a surrogate key, but be sure that your application and database can
handle data verification (avoiding duplicate records, etc).

Stu|||Tom (nospam@.yahoo.com) writes:
> I come to the same conclusion. (see the 10/11/2005 1:03pm post)
> As I search Google, this thing is getting very confusing. All the posts
> point to the one dead end conclusion -- "It depends".

Yes, that is a very common answer in the database world. And that does
not only apply to data modelling, but also how you should apply indexes,
write queries, implement backup strategies, you name it. It all matters
very much on the actual situations, and which trade-offs you want to make.

So in the end, it boils down do common sense and gut feeling for the
actual case at hand. What is bad is to follow a checklist that says
"Always do X".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 11 Oct 2005 20:44:08 GMT, "Tom" <nospam@.yahoo.com> wrote:

>Thanks for the actual terms for these key thingies.
>I've searched google but found a lot of different opinions.
>Some like autonumber but some like... huh... natural key.
>To sum it up from all the posts I've read:
>1)
>The people who use autonumber said that it performed better and easier to
>update the record without affecting the database integrity. (imagine
>someone needs to change his or her social security number!!!! all hell broke
>lose!!!)
>2)
>The people who use natural key said that people who use surrogate were just
>lazy and not experience with database design. And using natural key could
>better model database to the business model.
>So... I don't know... that's why I am so confused.
>One has physical advantage. The other has logical advantage.

Unfortunately, all any of us can do is add more opinions to the pile, which is
not likely to generate any final closure, just give you more to ponder. I'll
try to offer what I think is useful, though.

- A table may have one key or more than one "key". It's not so much a
quesiton of whether a table should have a logical or surrogate key as whether
it should have a surrogate key in addition to one or more logical keys, and
use the surrogate key for joins.

- When multiple levels of 1-to-many relationship exist, surrogate keys can
prevent adding additional key fields at each level and coupling fine levels of
detail too closely to the models at courser levels.

- When a table has a surrogate key that is always of the same type and
following the same naming convention, it's easier to generate reusable
client-side code that can do things like keep track of a record, requery it,
save updates after an off-line editing session, etc.

- Surrogate keys tend to be 32-bit numbers that are fairly small, and easy for
the database engine to process.

- Using cascading updates to handle logical key change propagation can result
in very expensive updates with wide-ranging run-time side effects.

- Using surrogate keys to identify record links across subsystems can lead to
excessively tight coupling and interdependency. If 2 subsystems should be
relatively autonomous, they should very likely -not- reference each other's
records by internal surrogate key. Instead, they might use the logical key,
or they might use some kind of globally unique identifier. They should also
be built to tolerate broken links.|||On Tue, 11 Oct 2005 20:03:49 GMT, "Tom" <nospam@.yahoo.com> wrote:

>Hi,
>Is there a reason for using one and only one column as the key?
>I find it hard to enforce the integrity of the database with this design.
>for example,
>let's say the [company]-[employee]-[sale] tables.
>company A has an employee B.
>employee B makes a sale C.
>If employee B moves to another company, the sale C in the [sale] table will
>move along with employee B to the new company.
>This doesn't look correct to me.

How does this problem relate to the use of logical vs surrogate keys? The
real, problem is that this model does not track what company the sale is made
for, and traversing the employee doesn't tell what company that is, it tells
what company the employee who made the sale -currently- works for. [sale]
needs its own direct link to [company] if that information needs to be
retrieved later.|||dbahooker@.hotmail.com wrote:
> always use an autonumber identity for everythign you do
> single-column primary key

And this advice is based on what computer science class taught where?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)|||Your whole mental model of RDBMS is wrong.

Exposed locators such as IDENTITY are not surrogate keys. A surrogate
is totally hidden from uers and maintained by the system -- think of
how an index works. If you change a natural key, the DRI actions will
cascade it for you. Hell does not break loose. But if your autonumber
and real key are out of synch,then you are screwed. Just enter the
same record several times to get different autonumbers on duplicate
rows. Drop all but one of the dups and then try to find all of the
referenced rows in other tables.

If you maintain the exposed locator by hand, you have extra work, extra
disk seeks and will eventually make a mistake, thus destroying your
data integrity. How would you validate and verify your data?

The UPC code just went from 10 to 13 digits. It does not matter if you
used an autonumber or the UPC, the UPC has to be updated. If you had
used a proper RDBMS design and SQL-92, you would do an ALTER DOMAIN and
a single update with CASCADE. This is one of the MANY reasons that
rows are not records. This is a problem in SQL Server because it is
still based on a contigous storage model, but other products are not,
so wind up using tools to write scripts.

Finally have you bothered to time the two approaches with small and
large tables? It does not make a difference until the table has to do
a lot of paging and redundant autonumbers accumulate. This is the
21-st century; we have 32 and 64 bit hardware, nano-second speeds,
multi-word instructions, parallel processing and really good disk
drives.|||Must be from somewhere in India where they program $QL $erver for Bill
Gates.

Mahahahaa!!! <G!
"DA Morgan" <damorgan@.psoug.org> wrote in message
news:1129217676.921233@.yasure...
> dbahooker@.hotmail.com wrote:
>> always use an autonumber identity for everythign you do
>>
>> single-column primary key
> And this advice is based on what computer science class taught where?
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan@.x.washington.edu
> (replace x with u to respond)|||thanks for the tips.

But I need to follow the specs. The specs says that cascade update or
delete is not supported (along many other things like joinning tables is not
allowed). And it comes to a point that I have to say screw this. I am
going to use autonumber.

I am trying to look for any sounded and proven pattern. But searching
Google gives me only the "it depends" solution.

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129224960.345778.104210@.z14g2000cwz.googlegr oups.com...
> Your whole mental model of RDBMS is wrong.
> Exposed locators such as IDENTITY are not surrogate keys. A surrogate
> is totally hidden from uers and maintained by the system -- think of
> how an index works. If you change a natural key, the DRI actions will
> cascade it for you. Hell does not break loose. But if your autonumber
> and real key are out of synch,then you are screwed. Just enter the
> same record several times to get different autonumbers on duplicate
> rows. Drop all but one of the dups and then try to find all of the
> referenced rows in other tables.
> If you maintain the exposed locator by hand, you have extra work, extra
> disk seeks and will eventually make a mistake, thus destroying your
> data integrity. How would you validate and verify your data?
> The UPC code just went from 10 to 13 digits. It does not matter if you
> used an autonumber or the UPC, the UPC has to be updated. If you had
> used a proper RDBMS design and SQL-92, you would do an ALTER DOMAIN and
> a single update with CASCADE. This is one of the MANY reasons that
> rows are not records. This is a problem in SQL Server because it is
> still based on a contigous storage model, but other products are not,
> so wind up using tools to write scripts.
> Finally have you bothered to time the two approaches with small and
> large tables? It does not make a difference until the table has to do
> a lot of paging and redundant autonumbers accumulate. This is the
> 21-st century; we have 32 and 64 bit hardware, nano-second speeds,
> multi-word instructions, parallel processing and really good disk
> drives.|||> The specs says [...] joinning tables is not allowed [!!]

Quit now. Time spent on such a spec is time wasted.

--
David Portas
SQL Server MVP
--|||They do pay the bills though. ;)

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129227183.899975.133870@.g47g2000cwa.googlegr oups.com...
>> The specs says [...] joinning tables is not allowed [!!]
> Quit now. Time spent on such a spec is time wasted.
> --
> David Portas
> SQL Server MVP
> --|||On 13 Oct 2005 10:36:00 -0700, --CELKO-- wrote:

>Your whole mental model of RDBMS is wrong.

Hi Joe,

You're whole mental model of surrogate keys is wrong.

>Exposed locators such as IDENTITY are not surrogate keys. A surrogate
>is totally hidden from uers and maintained by the system -- think of
>how an index works.

So don't display the surrogate key on the data entry screen.

>If you change a natural key, the DRI actions will
>cascade it for you. Hell does not break loose.

If you change a natural key in a table that has a surrogate key as well,
there's no need to cascade it, as there's no redundant storage of the
key values. Hell does not break losse AND you don't need to get locks on
half the tables in the database.

>But if your autonumber
>and real key are out of synch,then you are screwed.

Eh? How can they "get out of synch"? Please post proper DDL and DML to
clarify what you mean.

>Just enter the
>same record several times to get different autonumbers on duplicate
>rows. Drop all but one of the dups and then try to find all of the
>referenced rows in other tables.

That's not a result of IDENTITY, that's a result of forgetting to
include a CHECK constraint. Database theory 101.

>If you maintain the exposed locator by hand, you have extra work, extra
>disk seeks and will eventually make a mistake, thus destroying your
>data integrity.

That's the point - you DON'T maintain the surrogate key by hand.

>How would you validate and verify your data?

Same as you would do it: use the natural key.

>The UPC code just went from 10 to 13 digits. It does not matter if you
>used an autonumber or the UPC, the UPC has to be updated. If you had
>used a proper RDBMS design and SQL-92, you would do an ALTER DOMAIN and
>a single update with CASCADE.

For starters, SQL Server 2000 doesn't have a switch that customers can
just flick to get full SQL-92 compliance. There is no ALTER DOMAIN
command in SQL Server 2000.

Fortunately, those who had the foresight to add a surrogate key to the
Products table don't need ALTER DOMAIN, nor CASCADE. A simple ALTER
TABLE and a single UPDATE on one single table suffices.

>This is one of the MANY reasons that
>rows are not records. This is a problem in SQL Server because it is
>still based on a contigous storage model, but other products are not,
>so wind up using tools to write scripts.

You mean to imply that other RDBMS's have no autonumber facility? Get
off it. Less than one minute of googling gave me:

- SQL Server, DB2: IDENTITY (with some variations in syntax);
- MySQL, SQLite: AUTO_INCREMENT (with some variations in syntax);
- Postgres, Oracle, Sybase, Mimer: CREATE SEQUENCE (with some variations
in syntax).

http://sqlzoo.net/howto/source/z.dir/tip000001

>Finally have you bothered to time the two approaches with small and
>large tables?

The OP was not asking for an advice for a specific situation, but for a
generic truth on whether to use or avoid identity. Which of course there
isn't - since your religious insisting that IDENTITY should never be
used is just as fundamentally wrong as brainlessly whacking an IDENTITY
column onto each and every table.

In databases, "it depends" is the only reliable answer.

>It does not make a difference until the table has to do
>a lot of paging and redundant autonumbers accumulate.

Redundant autonumbers? Care to elaborate?

This is the
>21-st century; we have 32 and 64 bit hardware, nano-second speeds,
>multi-word instructions, parallel processing and really good disk
>drives.

But we also have applications that gather hundreds of rows per second,
and queries that run on million-row tables - situations that can't be
"tuned" by just throwing more and better hardware at it. Performance s
just as important a consideration as it ever was. And including or
removing a surrogate key can (in some situations) improve or damage
performance by several degrees of magnitude.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Tom wrote:
> thanks for the tips.
> But I need to follow the specs.

Not if the specification is valueless or harmful.

Part of what you are supposed to bring to the party is expert knowledge
with respect to relational database systems. You are supposed to
contribute ... not just follow orders.

Go back, with cogent reasoned arguments, and ask for changes.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)|||>> The specs says that cascade update or delete is not supported (along many other things like joinning tables is not allowed). <<

No joins means that you have a (realllly crappppy) file system and not
an RDBMS.

>> I am trying to look for any sounded and proven pattern. <<

The only proven patern I know in this situation based on 35+ yerts in
the trade is to update your resume and leave the sinking ship. Do it
fast so the stink of failure does not hang on your clothing.

..|||Tom wrote:
> Hi,
> Is there a reason for using one and only one column as the key?
> I find it hard to enforce the integrity of the database with this design.
> for example,
> let's say the [company]-[employee]-[sale] tables.
> company A has an employee B.
> employee B makes a sale C.
> If employee B moves to another company, the sale C in the [sale] table will
> move along with employee B to the new company.
> This doesn't look correct to me.

As a programmer, if you work for a company you already know that code
you write at such company belongs to the company, not to you. Why would
the sale in your example belong to the salesman and not the company in
the first place. The sale would be a sub record of the company, the
salesman is just an attribute.|||Hahahaha!!!
huh... <G!>
Well... they do pay the bill... and they do pay the bill ON TIME.
So... it is all good to me.
not to mention that I've already left my dignity in my house before I set
foot in my cubical.

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129252447.261815.6780@.z14g2000cwz.googlegrou ps.com...
>>> The specs says that cascade update or delete is not supported (along
>>> many other things like joinning tables is not allowed). <<
> No joins means that you have a (realllly crappppy) file system and not
> an RDBMS.
>>> I am trying to look for any sounded and proven pattern. <<
> The only proven patern I know in this situation based on 35+ yerts in
> the trade is to update your resume and leave the sinking ship. Do it
> fast so the stink of failure does not hang on your clothing.
> .

Autonumber question

Does anyone know how to reset an autonumber field in SQLExpress 2005?

In MSAccess it was a simple "compress" of the database. My .mdf files are getting very large and I would like to shrink them down for easier mobility. A blank database takes up aprox. 30 mbs. Most all of the autonumber fields are currently in the 200,000 - 800,000 range. When I compressed my Access database prior to transfering to SQLExpress it significantly decreased the size of the empty database file.

Thank you for any help you can provide.

hi,

please have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=769553&SiteID=1

regards

Autonumber problems

Hello,
I have 2 problems with autonumber in a form. I use sql server as back-end,
but when adding records i don't see the autonumber.
Also i use primary field where it values comes from the autonumber, which
results in a error that it cannot insert null values.
How can i resolve this issue?
ThxHi,
Can you please send me the Table structure as well the script which try to
insert into table.
Normally the primary key field will be kept as identity and we do not want
to insert value for that column.
Thanks
Hari
MCDBA
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:OzTdDga5DHA.2496@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hello,
> I have 2 problems with autonumber in a form. I use sql server as back-end,
> but when adding records i don't see the autonumber.
> Also i use primary field where it values comes from the autonumber, which
> results in a error that it cannot insert null values.
> How can i resolve this issue?
> Thx
>

---

|||Unlike Access/Jet, you don't see identity column numbers in a form
until AFTER the row is committed. You cannot insert nulls in a primary
key column. That causes an error in both Jet and SQL Server.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Wed, 28 Jan 2004 15:07:03 +0100, "Ezekil" <ezekiel@.lycos.nl>
wrote:

quote:

---

>Hello,
>I have 2 problems with autonumber in a form. I use sql server as back-end,
>but when adding records i don't see the autonumber.
>Also i use primary field where it values comes from the autonumber, which
>results in a error that it cannot insert null values.
>How can i resolve this issue?
>Thx
>

---

|||Hi,
I don't use any script to insert records. I add records directly into the
table. As for the table structure, see below:
1 AutoNr int 4 0 (autonumber)
2 PersonIdnr int 4 0 (primary)
0 CarIdnr int 4 0
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KB%23zwa5DHA.2300@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi,
> Can you please send me the Table structure as well the script which try to
> insert into table.
> Normally the primary key field will be kept as identity and we do not want
> to insert value for that column.
> Thanks
> Hari
> MCDBA
>
> "Ezekil" <ezekiel@.lycos.nl> wrote in message
> news:OzTdDga5DHA.2496@.TK2MSFTNGP09.phx.gbl...
back-end,[QUOTE]
which[QUOTE]
>

---

|||Hi,
To overcome these , can you try the below to insert data,
insert into table(PersonIdnr,CarIdnr ) values (2,0)
It is not required to explicitly provide data for Identity (Autonumber)
column.
Thanks
hari
MCDBA
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:8ajf1010po8jlt48nuct0i64gmgch3637t@.
4ax.com...

quote:

---

> Unlike Access/Jet, you don't see identity column numbers in a form
> until AFTER the row is committed. You cannot insert nulls in a primary
> key column. That causes an error in both Jet and SQL Server.
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
> On Wed, 28 Jan 2004 15:07:03 +0100, "Ezekil" <ezekiel@.lycos.nl>
> wrote:
>
back-end,[QUOTE]
>

---

Autonumber problems

Hello,
I have 2 problems with autonumber in a form. I use sql server as back-end,
but when adding records i don't see the autonumber.
Also i use primary field where it values comes from the autonumber, which
results in a error that it cannot insert null values.
How can i resolve this issue?
ThxHi,
Can you please send me the Table structure as well the script which try to
insert into table.
Normally the primary key field will be kept as identity and we do not want
to insert value for that column.
Thanks
Hari
MCDBA
"Ezekiël" <ezekiel@.lycos.nl> wrote in message
news:OzTdDga5DHA.2496@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have 2 problems with autonumber in a form. I use sql server as back-end,
> but when adding records i don't see the autonumber.
> Also i use primary field where it values comes from the autonumber, which
> results in a error that it cannot insert null values.
> How can i resolve this issue?
> Thx
>|||Unlike Access/Jet, you don't see identity column numbers in a form
until AFTER the row is committed. You cannot insert nulls in a primary
key column. That causes an error in both Jet and SQL Server.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Wed, 28 Jan 2004 15:07:03 +0100, "Ezekiël" <ezekiel@.lycos.nl>
wrote:
>Hello,
>I have 2 problems with autonumber in a form. I use sql server as back-end,
>but when adding records i don't see the autonumber.
>Also i use primary field where it values comes from the autonumber, which
>results in a error that it cannot insert null values.
>How can i resolve this issue?
>Thx
>|||Hi,
I don't use any script to insert records. I add records directly into the
table. As for the table structure, see below:
1 AutoNr int 4 0 (autonumber)
2 PersonIdnr int 4 0 (primary)
0 CarIdnr int 4 0
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KB%23zwa5DHA.2300@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can you please send me the Table structure as well the script which try to
> insert into table.
> Normally the primary key field will be kept as identity and we do not want
> to insert value for that column.
> Thanks
> Hari
> MCDBA
>
> "Ezekiël" <ezekiel@.lycos.nl> wrote in message
> news:OzTdDga5DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > I have 2 problems with autonumber in a form. I use sql server as
back-end,
> > but when adding records i don't see the autonumber.
> > Also i use primary field where it values comes from the autonumber,
which
> > results in a error that it cannot insert null values.
> >
> > How can i resolve this issue?
> >
> > Thx
> >
> >
>|||Hi,
To overcome these , can you try the below to insert data,
insert into table(PersonIdnr,CarIdnr ) values (2,0)
It is not required to explicitly provide data for Identity (Autonumber)
column.
Thanks
hari
MCDBA
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:8ajf1010po8jlt48nuct0i64gmgch3637t@.4ax.com...
> Unlike Access/Jet, you don't see identity column numbers in a form
> until AFTER the row is committed. You cannot insert nulls in a primary
> key column. That causes an error in both Jet and SQL Server.
> -- Mary
> Microsoft Access Developer's Guide to SQL Server
> http://www.amazon.com/exec/obidos/ASIN/0672319446
> On Wed, 28 Jan 2004 15:07:03 +0100, "Ezekiël" <ezekiel@.lycos.nl>
> wrote:
> >Hello,
> >
> >I have 2 problems with autonumber in a form. I use sql server as
back-end,
> >but when adding records i don't see the autonumber.
> >Also i use primary field where it values comes from the autonumber, which
> >results in a error that it cannot insert null values.
> >
> >How can i resolve this issue?
> >
> >Thx
> >
>

Autonumber problem

Hello all,
Has anyone heard about autonumber fields skipping one number? For example, the autonumber field goes from 39 to 41 skipping 40 entirely.

Any ideas why it would do this?
Thanks in advance.
Richard M.if you delete records, the number does not get reassigned. this is to maintain the uniqueness of the number assigned to a record.

hth|||It does the same if an insert fails (in EM for sure, probably in SQL too). the number that WOULD have been assigned gets bypassed

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
DavidDavid C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com|||David Gugick wrote:
> David C wrote:
>> We are testing the upload of an Access 2002 data database to SQL
>> Server 2000. Some of the tables had AutoNumber fields that were
>> Random (due to it being a replicated table in Access). The upload
>> created triggers similar to the example below. However, Access
>> applications fail on insert because the primary key field is not
>> populated until the trigger runs. Any help is appreciated.
>> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
>> INSERT AS SET NOCOUNT ON
>> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
>> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
>> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
>> SELECT @.newc = (SELECT DocID FROM inserted)
>> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
>> David
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
David
David C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com
|||David Gugick wrote:
> David C wrote:
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
DavidDavid C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com|||David Gugick wrote:
> David C wrote:
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.

AutoNumber PK on Most Tables?

I tend to create a "RecordID" IDENTITY-driven PK on subtables where I think most
people wouldn't because there is no immediate obvious need.
My reasoning is "why not?"...seems like a significant convenience later on when
faced with unexpected situations that require identification of specific
records...and the cost seems minimal.
Comments?
--
PeteCresswellI agree with you. I do the same too, which is to create a PK for all tables.
But one thing that I watch for is to see the query patterns on these tables
so that I can use clustered indexes on them. PK's are automatically created
as clustered (unless the nonclustered is specified). For example, if my
subtable resulted because of normalization, I would rather put a clustered
index on the FK and not on the PK.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"(Pete Cresswell)" <x@.y.z> wrote in message
news:3apnsvcaak4cvco9re9vr7h7tuu06384v9@.4ax.com...
> I tend to create a "RecordID" IDENTITY-driven PK on subtables where I
think most
> people wouldn't because there is no immediate obvious need.
> My reasoning is "why not?"...seems like a significant convenience later
on when
> faced with unexpected situations that require identification of specific
> records...and the cost seems minimal.
> Comments?
> --
> PeteCresswell

Autonumber or unidue id generation

Hi,
I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ?
Thanks
-SudhakarHi,

I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ?

Thanks
-Sudhakar

Make the datatype int and set the Identity constraint on that column.
Joydeep|||Your two best choices are either an Identity column (integer), or a GUID with the default set to NewID().|||I would STRONGLY recommend using the GUID if that option is available to you. IDENTITY values are fine for many purposes, and are easy for legacy code to manage, but GUID values are much simpler in the long run. They are easier to use, easier to generate, easier to manage, and very nearly foolproof, at least in my experience.

-PatP|||Well, I use GUIDs almost exclusively, but I don't know that I would recommend them as strongly as Pat. They do have some drawbacks, and I am not talking about their size or the fact they they are difficult to type (stupid arguments...). One of my pet peeves is that I can't apply aggregate functions such as MAX and MIN to GUIDs, which, believe or not, can be handy thing to do some times, such as when eliminating duplicates. You have to cast the GUID as char(36) first, which throws any indexes out the window.

autonumber is resetting

i am using autonumber as a primary key in one table. in my app., row from that table will be moved to another table after some time but that autonumber is important. when there are no any data in first table, and new row is inserted, autonumber starts from 1 which i don't want as there will be data redundancy when it will be moved to another table. so is there any way i can force the autonumber to start from previous value rathe than 1?Are you using an IDENTITY column? If so, I do not understand why you would have the problem you describe.|||i am using identity datatype.

yeah, it's strange. i added that data via webform interface and it was 1. then i tried adding directly in db but it gave me 10 as new value which is correct.

so just wondering if there is any settings i need to use just to make sure that it doesn't happen again.

AutoNumber in SQLServer 2000

Hi
Please help me URGENT
How do i make an tableField with a
Auto-Incr, the same like in an access DB where it's
called AutoNumber
Thanx in advance
__________________________________________________________________ Flemming
Paulsen ICQ#: 270065050 Current ICQ status: + More ways to contact me
__________________________________________________________________Please dpo not post the same message independently to multiple groups. If
you must post o . 1 group then you can include them all in the same post.
See Andrew's post in pne of the other groups.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"VideoSmeden" <software@.picsign.dk> wrote in message
news:%23qO7%23oThDHA.2164@.TK2MSFTNGP09.phx.gbl...
> Hi
> Please help me URGENT
> How do i make an tableField with a
> Auto-Incr, the same like in an access DB where it's
> called AutoNumber
> Thanx in advance
> __________________________________________________________________
Flemming
> Paulsen ICQ#: 270065050 Current ICQ status: + More ways to contact me
> __________________________________________________________________
>

autonumber in SQL Server Express

Does anyone know how to add an autonumber field into a table? Is this the same as uniqueidentifier? I have a table and I need to create a field to use as Primary Key. I would appreciate any help.

-Brad B

identity is the autonumber keyword.

create table tblWhatever

(

id int identity,

name nvarchar(50) /* whatever */

)

|||Ok... I'm going to give that a try! Thanks Chris.

AutoNumber in SQL 2005

Hello friends,

I am using Sql Server 2005 in my web application. In one scenario I had made one field of a table as Autonumber by keeping its datatype as int and then setting its Identity flag to true. Now is there any inbuild function that can give us the next number tat will be generated if a new record will be inserted. This is needed in the case if last 1 or 2 records are deleted for e.g.

last record has value 7 and if the record is deleted still for the new record the number generated will be 8 i.e. after 6 there is 8. So by any means can we get the next number, or can we keep that autonumber to move serially even in case some record is deleted it should go for the max + 1 number.

Please let me know.

Thanks & Regards
Girish Nehte

sql server doesn't guarantee sequential order numbers, though there are some tricks to force it (reseeding the table via DBCC checkident). I'd recommend that you not rely on a function that assumes the max+1, but instead use scope_identity to return the actual value -http://www.sqlteam.com/article/understanding-identity-columns andhttp://www.sqlteam.com/article/creating-a-sequential-record-number-field are worth reading to detail you some options.

|||

Why should you care what the next number will be? If this is important then there is something conceptually wrong with your design. Identity columns are inherently dataless, they have no intrinsic meaning. What are you trying to accomplish?

|||

Hi dbland,

Actually I have to save two records in two different tables and after one records save I have to take fetch the identity of that record and set that identity aas the fireign key for the second records but as both the records we are entering are in the same transaction i.e. after both the queries executed we commit the transaction due to which before commiting we are unable to get the identity key of first record which we will be needing in entering the second record.

I dont know whether I was able to make clear my requirements or not but if you please got it let me know any better way to accomplish it.

Thanks & Regards
Girish Nehte

|||

i think if you are still going to implement this then use max in the query to retrive the highest figure in the column.like select max(id) as maximum from ur table.

|||

Thanks, Girish, for that clarification. What you want to do is very common and easily accomplished using the SCOPE_IDENTITY() function (seehttp://msdn2.microsoft.com/en-us/library/ms190315(SQL.90).aspx). Here's an example

declare @.LastId int
begin tran
insert ... into table1
select @.LastId = SCOPE_IDENTITY()
insert ... @.LastId,... into table2 (...FK_field,...)
commit tran

In my example, I save SCOPE_IDENTITY() to a variable and use the variable, this is not necessary, I just consider it good programming practice to always save @.@. fields and the like into temp variables so they don't get overwritten.

If you rollback the identity counters will still have been incremented, but it doesn't matter.

BTW, note in the MSDN article how SCOPE_IDENTITY() differs from @.@.identity

Autonumber field type in SQL Express 2005

Hope that this nisn the best place for this posting.
I am new to SQL Server using 2005 express with Visual basic express. I have
used Access databases before and used a AutoNumber field type to help with
indexing. Is this available from SQL Express 2005?
Hope you can assistOpps, found it using IDENTITY property.
any know of good book to get me up and running on SQL Server from VB.NET?
Matt A
"matt a" wrote:
> Hope that this nisn the best place for this posting.
> I am new to SQL Server using 2005 express with Visual basic express. I have
> used Access databases before and used a AutoNumber field type to help with
> indexing. Is this available from SQL Express 2005?
> Hope you can assist|||> any know of good book to get me up and running on SQL Server from VB.NET?
I don't believe there are any books out on SQL Server 2005, but here's on
for SQL Server 2000 + VB.Net:
http://www.amazon.com/exec/obidos/tg/detail/-/0735615357/
Also see http://www.aspfaq.com/2423

Autonumber field type in SQL Express 2005

Hope that this nisn the best place for this posting.
I am new to SQL Server using 2005 express with Visual basic express. I have
used Access databases before and used a AutoNumber field type to help with
indexing. Is this available from SQL Express 2005?
Hope you can assist
Opps, found it using IDENTITY property.
any know of good book to get me up and running on SQL Server from VB.NET?
Matt A
"matt a" wrote:

> Hope that this nisn the best place for this posting.
> I am new to SQL Server using 2005 express with Visual basic express. I have
> used Access databases before and used a AutoNumber field type to help with
> indexing. Is this available from SQL Express 2005?
> Hope you can assist
|||> any know of good book to get me up and running on SQL Server from VB.NET?
I don't believe there are any books out on SQL Server 2005, but here's on
for SQL Server 2000 + VB.Net:
http://www.amazon.com/exec/obidos/tg.../-/0735615357/
Also see http://www.aspfaq.com/2423