automatic number increment in ms sql 2005

will it be possible to increase number as below automatically
00000001
00000002
00000003
...

whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record
like that?
thanksHanderson,

If you set up a column as an INTEGER IDENTITY column (see BOL), SQL Server
will not store leading zeros. You could get leading zeros out of your select
statments as follows:

select right('00000000' + cast(MyColumn as varchar(25)), 8)

But, the best thing to do is manage leading zeros at the application layer.
Don't have the database always doing that work for you.

-- Bill

"HandersonVA" <handersonva@.hotmail.comwrote in message
news:1169678553.956380.205080@.q2g2000cwa.googlegro ups.com...

Quote:

Originally Posted by

will it be possible to increase number as below automatically
00000001
00000002
00000003
...
>
whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record
like that?
thanks
>

|||When creating the table, use the Identity keyword, and an int or bigint
type.

Thus (from BOL)
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)

On Jan 24, 2:42 pm, "HandersonVA" <handerso...@.hotmail.comwrote:

Quote:

Originally Posted by

will it be possible to increase number as below automatically
00000001
00000002
00000003
...
>
whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record
like that?
thanks

|||"amaxen" <Amaxen1@.gmail.comwrote in message
news:1169681707.525338.145700@.13g2000cwe.googlegro ups.com...

Quote:

Originally Posted by

>
When creating the table, use the Identity keyword, and an int or bigint
type.
>

NOTE: The numbers will be sequential but not necessarily contiguous.

If you have a rollback for example the numbers wioll be "used" up.

Quote:

Originally Posted by

>
Thus (from BOL)
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)
>
>
>
>
On Jan 24, 2:42 pm, "HandersonVA" <handerso...@.hotmail.comwrote:

Quote:

Originally Posted by

>will it be possible to increase number as below automatically
>00000001
>00000002
>00000003
>...
>>
>whenever the row is inserted, number will be increased like above
>format.
>which data type should I select and do some other setting to record
>like that?
>thanks

>

|||HandersonVA (handersonva@.hotmail.com) writes:

Quote:

Originally Posted by

will it be possible to increase number as below automatically
00000001
00000002
00000003
...
>
whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record like
that? thanks

First of all: do you need the numbers to be contiguous. If you cannot
accept gaps, you need to roll your own:

BEGIN TRANSACTION

SELECT @.id = coalesce(MAX(id), 0) + 1 FROM tbl WITH (UPDLOCK)

INSERT tbl (id, ...)
VALUES (@.id, ...)

COMMIT TRANSACTION

If you want to includ the leading zeroes, I would recommend that you
add a computed column that you persist and can index:

idasstr AS replicate('0', 10 - len(ltrim(str(id))) + ltrim(str(id))
PERSISTED

(Note: the PERSISTED keyword is available in SQL 2005 only.)

If you don't contiguous numbers you can use IDENTITY instead, and this is
partiucularly important if you expect a high insertion frequency from
multiple clients, as the scheme above will incur a serialisation that
reduces throughput. Even with IDENTITY you can would have a computed
column with the leading zeroes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Autoincrement record position in a view

Does anybody know the function or any other way in MS SQL Server 2000 or in MS Access or in MS FoxPro that I can get an increment record position in a view?

For example let's say that I have a table with only one field named persons. The table has three records person1, person2 and person3. What is the way in MS SQL Server 2000 or in MS Access or in MS FoxPro of retrieving the records in a view with an extra field named for example recno which will indicate the record autoincrement number in the view as it is below?

recno persons
1person1
2person2
3person3

Please help me

I will be very grateful if you also reply your answers also and to my email

Email: stavrinc@.hotmail.com

Thank you

Christos StavrinouWith FoxPro you can use the RECNO() function:

SELECT RECNO() AS recno, persons FROM myTable

This is not possible with SQL Server, and I am relatively sure it is not possible with Access.

Terri|||I should have said that this is possible with SQL Server only by inserting your result set into a #TEMP table which has an Identity column.view post 368541

Terri

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

Auto-increment my primary key: why 2 instead of 1?

Hi all,

I have a table where I have my ProdPK set up as Primary key, turned on "Is Identity" and set the Identity increment to 1. But each time I add a new item, the number incremented by 2... I have couple of other tables and they are all fine, just this particular table increased twice as it should. I check the setting against other tables and everything seems to be the same.

By the way, this is adding the data to the table inside MS SQL Server Management Studio manually. I haven't done anything in the ASP.NET page yet.

Thank you very much,

Kenny.

That's weird. I would double check the identity column settings, and maybe check for triggers on the table?

|||

Thanks. I did checked the table's setting and the auto-increment was set to "1". Other settings are identical to my other tables. Any suggestions?

Thanks again,

Kenny.

|||

Can you post the table script and the results you observe by executing "dbcc checkident ( YourTableName )" ? Have you made sure that there are no triggers defined on the table ?

|||

Here is the table's script:

1USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]2GO3/****** Object: Table [dbo].[T_PRODUCTS] Script Date: 12/22/2007 11:58:52 ******/4SET ANSI_NULLS ON5GO6SET QUOTED_IDENTIFIER ON7GO8SET ANSI_PADDING ON9GO10CREATE TABLE [dbo].[T_PRODUCTS](11[PROD_ID] [int] IDENTITY(1,1) NOT NULL,12[ACC_TYPE_ID_FK] [int] NOT NULL,13[PROD_NAME] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,14[PROD_DESCR] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,15[PROD_MODEL] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,16[PROD_STATUS] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,17[PROD_PRICE] [smallmoney] NULL,18[PROD_DATE_ADDED] [datetime] NOT NULL CONSTRAINT [DF_T_PRODUCTS_PROD_DATE_ADDED] DEFAULT (getdate()),19[PROD_ADDED_BY] [uniqueidentifier] NULL,20[PROD_NO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,21[PROD_NOTES] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,22[PROD_PIC_S] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,23[PROD_PIC_L] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,24[PROD_NEW] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,25 CONSTRAINT [PK_T_PRODUCTS] PRIMARY KEY CLUSTERED26(27[PROD_ID] ASC28)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]29) ON [PRIMARY]3031GO32SET ANSI_PADDING OFF33GO34USE [C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.2\MSSQL\DATA\MTRENZ.MDF]35GO36ALTER TABLE [dbo].[T_PRODUCTS] WITH NOCHECK ADD CONSTRAINT [ACT_TYPE_ID_FK] FOREIGN KEY([ACC_TYPE_ID_FK])37REFERENCES [dbo].[T_ACC_TYPES] ([ACC_TYPE_ID])38NOT FOR REPLICATION

This is what it return when I run the dbcc:

Checking identity information: current identity value '4', current column value '4'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Currently there are only two rows in my database, with the primary key values 2 and 4. It skipped 1 and 3.

Thank you,

Kenny.

|||

I don't know what exactly is the problem, but my database primary key started at 2, then 4, then 8... After 8, everything then become normal, meaning they increased correctly by 1!!!

I still don't know why, but it solved the problem itself I guess!

Thanks all,

Kenny.

Auto_Increment?

Is there a way to add a column in my sql server db that is ging to auto increment? I would like to start at 000001 and work right on up. I can't see to find a way to do this. Thanks.Yes. Set that column as the "Identity column" for the table.|||you're the man!!! without having access to my DB now, is there a way to set the mask so I can have the leading zeros I would like?|||The identity column has to be of type int, so there is no such thing as "leading zeros", you can only set the initial value and the step. You should format the number in your application the way you want, or if you really insist on doing it in the DB, then do a view.|||

smalltalk:

The identity column has to be of type int, so there is no such thing as "leading zeros", you can only set the initial value and the step. You should format the number in your application the way you want, or if you really insist on doing it in the DB, then do a view.

awesome. The reason for the leading zeros is just for formatting consistancey, It's a ui thing rather than a database thing so I can just slap some 0's on there after I get the value from the database

Auto_Increment?

Hi all.

I'm trying to transfer a mysql file to mssql. Is there a field type of auto increment in mssql.
If not is there anyway to make a field auto increment in the SQL Studio express?

Thanks in advance for help with this!
Ron

In Microsoft SQL, you set the column's "Identity Specification" equal to Yes

Auto Increment Starting 1 after deleting records

Hi,
How can I make the auto increment number start from 1 again after deleting
records in the table? I should have written it down somewhere when I knew
it last year.
YontaekYou can use DBCC CHECKIDENT or TRUNCATE TABLE. See Books Online for more
details.
"Asp Psa" <asppsa@.hotmail.com> wrote in message
news:uqVv5akjGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Hi,
> How can I make the auto increment number start from 1 again after deleting
> records in the table? I should have written it down somewhere when I knew
> it last year.
> Yontaek
>

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me..."Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||You could also script the table, drop the table and recreate
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I deleted a table which contains 200 rows using delete statement. In
>> that table i have used a identity column.
>> Now i inserted more data in to that table, but i am getting the identity
>> column series from 201........
>> So how can i reset the identity column to 1 without recreating the table.
>> Please help me...
>|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I deleted a table which contains 200 rows using delete statement. In
>> that table i have used a identity column.
>> Now i inserted more data in to that table, but i am getting the identity
>> column series from 201........
>> So how can i reset the identity column to 1 without recreating the table.
>> Please help me...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

auto increment problem

hi
I am facing problem in auto increment
when i enter to commit the row if data is right then incerment is 1
if enter data is wrong then error shows after correcting error
it increments not by 1 as shows in picture

does auto increment supports numeric(10)
as
id numeric(10) IDENTITY (1, 1),if you make an error while inserting, then that particular number is not re-used

which should not matter to your app (if the gaps in the numbers are a concern, then you are doing something wrong)

yes, you could use NUMERIC(10) for an IDENTITY column, but why would you?

you can also use BIGINT like you are currently using, but unless you know you're going to have more than two billion rows, use INTEGER|||using numeric(10) is need of our project

but just you see in figure when first i enter wrong string then error message come when I enter ok on that message then renter right string then
just see on ID column which is primary key and auto increment but
one time error occors one number scips as in id 17 to 19 not 18
so whats the problem what to do to overcome|||there is no problem, and you don't need to do anything to overcome it

gaps in IDENTITY values are irrelevant|||This is simply how identity columns work in SQL Server (and Sybase for that matter). If you are looking to "overcome" this, you will have to create your own auto-increment functionality. As r937 said, if you can not stand the gaps in the sequence, the application is doing something wrong.|||This is simply how identity columns work in SQL Server (and Sybase for that matter). If you are looking to "overcome" this, you will have to create your own auto-increment functionality. As r937 said, if you can not stand the gaps in the sequence, the application is doing something wrong.

so please can you give an example to create our own autoincrement
how to create ?|||ankur, we advise against creating your own autoincrement

why do you want no gaps in the numbers?|||so please can you give an example to create our own autoincrement
Seeing as I have never had the occasion or inclination to reinvent identity column functionality to support a bad design, no|||ankur, we advise against creating your own autoincrement

why do you want no gaps in the numbers?

because gaps in ID is primary key and gaps in numbers will make inconsistency
so is there some simple way to avoid

but

if we run query explicitly we can fill those gaps

but how can we do in gui mode|||gaps in numbers will make inconsistency

What will be inconsistant?|||inconsistency?

please give an example of where gaps would affect your application, and we will explain why it is a bad design choice|||many moons ago I programmed something with a logical delete process instead of real delete process because some auditor did not like gaps in ids provided by IDENTITY. Other than that, I can not think of any reason this should matter.|||That's not even a good reason :p|||people tell me to do stuff and I get a check every couple of weeks. I was younger.|||you can also use BIGINT like you are currently using, but unless you know you're going to have more than two billion rows, use INTEGER

or more than 2 billion insert errors ;)

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me...
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||You could also script the table, drop the table and recreate
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

Auto increment problem

Hi,
I deleted a table which contains 200 rows using delete statement. In that
table i have used a identity column.
Now i inserted more data in to that table, but i am getting the identity
column series from 201........
So how can i reset the identity column to 1 without recreating the table.
Please help me..."Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>
Check out
DBCC CHECKIDENT
in Books Online (online help).
Note however, IDENTITY columns are only guaranteed to be sequential, NOT
contiguous
If you roll back a transaction for example, you will lose the contiguous
nature.
create table test_foo
(
id int identity (1,1),
foo varchar(15)
)
insert into test_foo (foo) values ('first row')
begin tran
insert into test_foo (foo) values ('second row')
commit tran
begin tran
insert into test_foo (foo) values ('third row')
rollback tran
insert into test_foo (foo) values ('fourth row')
select * from test_foo
drop table test_foo
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||You could also script the table, drop the table and recreate
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||1) drop FK's if any
2) TRUNCATE TABLENAME
Mex
"Binoy" <binoy.a@.eostek.com> wrote in message
news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I deleted a table which contains 200 rows using delete statement. In that
> table i have used a identity column.
> Now i inserted more data in to that table, but i am getting the identity
> column series from 201........
> So how can i reset the identity column to 1 without recreating the table.
> Please help me...
>|||2) TRUNCATE TABLE NAME
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:eWEvyTWYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> 1) drop FK's if any
> 2) TRUNCATE TABLENAME
>
> Mex
>
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
>|||Thanks very much ofr your reply , i got the correct solution.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uyQtu3UYHHA.3824@.TK2MSFTNGP02.phx.gbl...
> "Binoy" <binoy.a@.eostek.com> wrote in message
> news:OeSyTwUYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> Check out
> DBCC CHECKIDENT
> in Books Online (online help).
> Note however, IDENTITY columns are only guaranteed to be sequential, NOT
> contiguous
> If you roll back a transaction for example, you will lose the contiguous
> nature.
> create table test_foo
> (
> id int identity (1,1),
> foo varchar(15)
> )
>
> insert into test_foo (foo) values ('first row')
> begin tran
> insert into test_foo (foo) values ('second row')
> commit tran
> begin tran
> insert into test_foo (foo) values ('third row')
> rollback tran
> insert into test_foo (foo) values ('fourth row')
> select * from test_foo
>
> drop table test_foo
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>

Auto Increment Primary Key

How do I make my Primary Key Auto Increment from Enterprise Manager?

ThanksIn the table design, make sure Identity is set to Yes.

Auto Increment on a Filed in a Table

I have a table with two primary key fileds. I will be running a process to populate the data into the table.For each process the first field is constant and second field is auto increment one.

My statements are insert into select ** from ** type one.

How do I auto increment second filed per run. Any delivered function available or any suggestion Pl.............It would help us answer you question a lot faster if you supplied DDL, sample Data, and the expected result...|||DDL:
Table1-

id: PK
lin_nbr: PK
filed1
field2

Table2-

filed1
filed2
filed3

Data:

insert into table1 (id, lin_nbr, filed1, filed2) (select 'a001', auto incr , field1,filed2 from table2 )

Result in table2 should be

a001 1 abc def
a001 2 ghi jkl
a001 3 xyz gfg|||Make your lin_nbr an identity column and when you insert, insert all but the lin_nbr column. SQL Server will auto-increment that column for you!

Table1-

id_col int not null,
lin_nbr int identity not null,
filed1 varchar(52) null, -- (or not null, whichever suits your purposes)
field2 varchar(52) null -- (same applies)|||I want that increament is based on the another pk. pl have a look at the data below

a001 1 abc def
a001 2 ghi jkl
a001 3 xyz gfg
a002 1 abc def
a002 2 ghi jkl
a002 3 xyz gfg

auto increment in SServer 2005

I want to create a table under sqlserver 2005 with a primary key field as: auto increment concatenated with with the current year.

example:

For year 2007, the primary key field must be like:

02007

12007

22007
.
.
.
3652007
For year 2008, the primary key field must be like:
02008
12008
32008
.
.
.
3652008
etc

Can you help to do it.

Thanks.

HI!,

Sorry for the blank post, my suggestion is to do it from front end. Since you can't use auto number, If you are using stored procedure for inserting then you can keep the logic inside it also.

Hope this will help

Auto increment in SQL

Hi. I'm trying to use an auto increment field in SQL, but it isn't quite doing what I want. I have a parent table and the child tables are set up with IDENTITY. The only problem is that SQL auto increments over all the tables, instead of auto-incrementing for each table. For example, KB table 1 has CDSS_key fields 1, 3, 4, 6 and KB table 2 has CDSS_key fields 2, 5, 7. I would like to have KB table 1 to have CDSS_key fields 1, 2, 3, 4 and KB table 2 to have CDSS_key fields 1, 2, 3, 4. Can anyone help? This is my create script:

CREATE TABLE CDSS (
CDSS_app char(10),
timestamp datetime DEFAULT getdate(),
PRIMARY KEY (CDSS_app) )

CREATE TABLE KB (
CDSS_key int IDENTITY(1, 1),
submit_by char(50),
timestamp datetime DEFAULT getdate(),
common_prob char(100),
prob_sol char(511),
CDSS_app char(10),
PRIMARY KEY (CDSS_key),
FOREIGN KEY (CDSS_app) REFERENCES CDSS (CDSS_app) )Please explain more clearly. KB table 1? KB table 2? What are they really named? Do they have identical structures? I only see you creating a single "KB" table.

blindman|||Originally posted by blindman
Please explain more clearly. KB table 1? KB table 2? What are they really named? Do they have identical structures? I only see you creating a single "KB" table.

blindman

Here's the script. The results are below. Thanks in advance!

CREATE TABLE CDSS (
CDSS_app char(10),
timestamp datetime DEFAULT getdate(),
PRIMARY KEY (CDSS_app) )

INSERT INTO CDSS (CDSS_app) VALUES ('OASIS')
INSERT INTO CDSS (CDSS_app) VALUES ('CIS')

CREATE TABLE KB (
CDSS_key int IDENTITY(1, 1),
submit_by char(50),
timestamp datetime DEFAULT getdate(),
common_prob char(100),
prob_sol char(511),
CDSS_app char(10),
PRIMARY KEY (CDSS_key),
FOREIGN KEY (CDSS_app) REFERENCES CDSS (CDSS_app) )

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test1', 'test1', 'test1', 'OASIS')

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test2', 'test2', 'test2', 'OASIS')

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test3', 'test3', 'test3', 'CIS')

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test4', 'test4', 'test4', 'OASIS')

SELECT CDSS_key, CDSS_app FROM KB

Results:

CDSS_key CDSS_app

1 OASIS
2 OASIS
3 CIS
4 OASIS

I want it to be:

CDSS_key CDSS_app

1 OASIS
2 OASIS
1 CIS
3 OASIS|||I'm sorry but you can't do this automatically. You would need to create your index field as a simple integer value and then write code that would update new values to the maximum existing value for the CDSS_app + 1. You could put it in a trigger, or in the stored procedure used to populate the table.

Generally, an issue such as this indicates a problem with the database design. I encourage you to rethink your application and see if you can come up with a better implementation.

blindman

auto increment in SQL

Hi.
Am creating a database in SQL and one of the field of my table is customer_id. I want this customer_id to be auto generated and auto increment. I was reading about Identity but it was too complicated to grasp. So how can that be done in a very simple way.
Thanks

As you create a table you specify the identity attribute. Then whenever you insert a row into the table, you will not need to specify a value for the identity column rather the database will do it for you.

Code Snippet

CREATE TABLE customers ( cust_id int identity(1,1), Name char(20) )

The first value is the seed and the second value is the increment. The seed is what value the sequence starts at and the increment is how much the identity column gets incremented after each insert.

Code Snippet

insert customers (Name) values ('Bill')
insert customers (Name) values ('Hank')
insert customers (Name) values ('Joe')
select *
from customers

Notice how you didn't need to specify a value for the identity column.

Code Snippet

cust_id Name
--
1 Bill
2 Hank

3 Joe

Bill gets an id of 1 since he was the first row inserted and thus gets the seed value. The identity value is now at 1. Hank gets a 2 because he gets the current identity value + increment value (1+1). The identity value is now at 2. Joe gets a 3 because he gets the current identity value + increment value (2+1).

|||Hi.
Thanks for replying.
Well to be more precise i am creating the SQL database within Visual Basic Express Edition(http://msdn2.microsoft.com/en-us/library/ms172599(VS.80).aspx).
As such am not typing any code, just creating table like in access.
So how should i do from there. Dont know where to find the coding when i have creating the table.|||

I believe that if you examine the properties for each column (in the lower part of the table design window), you will see that there is a property for IDENTITY. Set that property to Yes/True.

|||Hi.
thanks for replying. so stupid from my part.

Auto increment help

Is there an auto increment feature for integer key fields. I'm using VS2005 to configure my DB and I do not see a way to set this.

Thanks in Advance
MoonWa

CREATE TABLE tblArtists
(
artistID int identity primary key,
artistName nvarchar(50) NOT NULL
)

this SQL creates a table with two fields, the keyword 'identity' makes a field auto increment. In the UI there's a property 'identity specification', or something similar. If you open this, you can check 'is identity'.

|||

DataColumn type has a property AutoIncrement:

dtEmployees.Columns[0].AutoIncrement = true;
dtEmployees.Columns[0].AutoIncrementSeed = -1;
dtEmployees.Columns[0].AutoIncrementStep = -1;

SQL Server table may also have one identity column, as Christian mentioned. But you should be aware of some points:

1. SQL Server by default doesn't allows inserting in Identity columns (and never allows updates)
2. Your DataColumn instance knows nothing about current SQL Server Identity value and will possibly generate duplicate values (which will fail on insert if column is constrained via Primary Key or Unique, as usually).

So, if you let SQL Server to generate values, you also should update your column' values with generated one from stored proc output parameters or from output from FOR INSERT trigger on your table (or so) using DataAdapter. For example:

daEmployees.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

WBR, Evergray
--
Words mean nothing...

|||Thanks for your help.

MoonWa