Identity specification

Hi,

How do I determine what the next "id" will be in a collumn where the Identity Specification is turned on with Identity Increment?

I have two tables, the one contains product information and the other is a photo album. When I create a new product, then I automatically create an associated photo album. It all works fine, but I have found some problems after using this setup for a while to manage products.

For example, say I have created 10 products (id's 1 to 10) , and then I decide to remove the last 5 products from the list. The next time I add a new product, it will be product number 6, but the id will be 11 (seeing that the id's will always be unique). How do I determine what the next Identity value will be seeing that the actual last id might not be the last one that was allocated?

Regards
Jan

You can't know in advance what the next id field will be, but you can easily find out what the id was generated and use it in your proc. SCOPE_IDENTITY () returns the last id that was generated within your scopehttp://msdn2.microsoft.com/en-us/library/ms190315.aspx and would be used as follows

SET NOCOUNT ON
SET XACT_ABORT ON // guarantees that an error rolls back the transaction

declare @.lastId as int

begin tran
insert into table1...
select @.lastId = SCOPE_IDENTITY()

insert into table2 values (@.lastId,...)
end tran

|||

Hi David

Thanks for your response. I have read everything I could lay my hands on, and the long and short of my story is that I will have to rethink my whole implementation.

Thanks in any case - I have learned something that I am sure will be helpfull in future.

Regards

Jan

Identity Field plus leading characters

I was wondering if the identity collumn of a table can include the same
first 3 characters. All I want to do is add DMC to the front. If I cant
do this in the database, I guess I could just append it to the data when
displaying it. But if this is possible I could really do with using it.
Thanks in advance!
SteveWhy don't you use a view:
CREATE VIEW dbo.v_foo
AS
SELECT idColumn = 'DMC'+RTRIM(fooID) FROM dbo.foo
Or a computed column:
CREATE TABLE dbo.foo
(
fooID INT IDENTITY(1,1),
idColumn AS CONVERT(VARCHAR(12),'DMC'+RTRIM(fooID))
)
"Dooza" <steve@.dont.spam.me.dooza.tv> wrote in message
news:%23CwbtDvsFHA.1256@.TK2MSFTNGP09.phx.gbl...
>I was wondering if the identity collumn of a table can include the same
>first 3 characters. All I want to do is add DMC to the front. If I cant do
>this in the database, I guess I could just append it to the data when
>displaying it. But if this is possible I could really do with using it.
> Thanks in advance!
> Steve|||It is doable, but it does not mean it is helpful. You will be wasting disk
space.
create table t1 (
c1 int not null identity unique,
c2 as 'DMC' + ltrim(c1)
)
insert into t1 default values
insert into t1 default values
insert into t1 default values
select * from t1
drop table t1
go
AMB
"Dooza" wrote:

> I was wondering if the identity collumn of a table can include the same
> first 3 characters. All I want to do is add DMC to the front. If I cant
> do this in the database, I guess I could just append it to the data when
> displaying it. But if this is possible I could really do with using it.
> Thanks in advance!
> Steve
>|||Hi Aaron,
Thats not a bad idea. Thanks for your help.
Steve
Aaron Bertrand [SQL Server MVP] wrote:
> Why don't you use a view:
> CREATE VIEW dbo.v_foo
> AS
> SELECT idColumn = 'DMC'+RTRIM(fooID) FROM dbo.foo
> Or a computed column:
> CREATE TABLE dbo.foo
> (
> fooID INT IDENTITY(1,1),
> idColumn AS CONVERT(VARCHAR(12),'DMC'+RTRIM(fooID))
> )
>
>
> "Dooza" <steve@.dont.spam.me.dooza.tv> wrote in message
> news:%23CwbtDvsFHA.1256@.TK2MSFTNGP09.phx.gbl...
>
>
>|||Hi there,
How much wasted space are we talking about here? I dont think I will be
having more than 5000 records.
Steve
Alejandro Mesa wrote:
> It is doable, but it does not mean it is helpful. You will be wasting disk
> space.
> create table t1 (
> c1 int not null identity unique,
> c2 as 'DMC' + ltrim(c1)
> )
> insert into t1 default values
> insert into t1 default values
> insert into t1 default values
> select * from t1
> drop table t1
> go
>
> AMB
> "Dooza" wrote:
>|||> How much wasted space are we talking about here? I dont think I will be
> having more than 5000 records.
Well, that's really not the point, I suppose. I see no reason to store
'DMC' in the table at all, if *every signle row* will always be prefixed by
it. This sounds more like a job for the presentation layer or, as I
previously suggested, a view. Taking more disk space than necessary is a
subjective thing, but in general, I think it is a mistake to do it when it
buys you nothing, such as in this case.
Driving 120 mph is dangerous, but how dangerous is it really? Not very, so
long as you don't hit something. :-)|||Aaron Bertrand [SQL Server MVP] wrote:
>
> Well, that's really not the point, I suppose. I see no reason to store
> 'DMC' in the table at all, if *every signle row* will always be prefixed b
y
> it. This sounds more like a job for the presentation layer or, as I
> previously suggested, a view. Taking more disk space than necessary is a
> subjective thing, but in general, I think it is a mistake to do it when it
> buys you nothing, such as in this case.
> Driving 120 mph is dangerous, but how dangerous is it really? Not very, s
o
> long as you don't hit something. :-)
I completely agree with you. I will do as you suggest, and use a view.
Thanks for your help!
Steve|||Dooza wrote:
> Aaron Bertrand [SQL Server MVP] wrote:
>
>
> I completely agree with you. I will do as you suggest, and use a view.
> Thanks for your help!
> Steve
One extra thing I have thought about. Say I wanted the ID to start with
DMC0001, the first ID is going to be 1, not 0001. How can I do this?
Steve|||> One extra thing I have thought about. Say I wanted the ID to start with
> DMC0001, the first ID is going to be 1, not 0001. How can I do this?
'DCM'+RIGHT('0000'+RTRIM(col_name),4)|||> One extra thing I have thought about. Say I wanted the ID to start with
> DMC0001, the first ID is going to be 1, not 0001. How can I do this?
And just as a warning, you said before that you would only have 5000 rows,
however please remember that an IDENTITY column is prone to gaps
(transaction rollbacks, deletes) so I'm not so sure I agree that reserving
only 4 digits is a wise choice. Or, at least, I hope you are prepared to
re-work everything when you need to make more room.
A