Reseed my identity column

  • Thread starter Thread starter Mario
  • Start date Start date
M

Mario

I have identity int column with increment 1 and seed 1.
I wish to reseed ID column when I delete some rows and inserting new
one. I try to create trigger like this one:

ALTER TRIGGER Trigger2
ON Table2
FOR DELETE
AS
/*IF UPDATE(id) */
DBCC CHECKIDENT ('Table2', RESEED)

But inserting continuous with the next int value in ID column, although
I deleted some rows.

How to reseed my ID column with the last one existing in a database?
 
Mario said:
I have identity int column with increment 1 and seed 1.
I wish to reseed ID column when I delete some rows and inserting new one.
I try to create trigger like this one:

ALTER TRIGGER Trigger2
ON Table2
FOR DELETE
AS
/*IF UPDATE(id) */
DBCC CHECKIDENT ('Table2', RESEED)

But inserting continuous with the next int value in ID column, although I
deleted some rows.

How to reseed my ID column with the last one existing in a database?
Click to expand...

You're more likely to get useful responses if you ask in a discussion group
for the DBMS you are using. This group is for .NET programming questions.

If you are using SQL Server, there are numerous groups that begin with:

microsoft.public.sqlserver

where you'll quickly get the assistance you need.
 
Mario said:
I have identity int column with increment 1 and seed 1.
I wish to reseed ID column when I delete some rows and inserting new
one. I try to create trigger like this one:

ALTER TRIGGER Trigger2
ON Table2
FOR DELETE
AS
/*IF UPDATE(id) */
DBCC CHECKIDENT ('Table2', RESEED)

But inserting continuous with the next int value in ID column,
although I deleted some rows.

How to reseed my ID column with the last one existing in a database?
Click to expand...


If you are deleting all the ending rows, then this is useful ...
perhaps. it is more useful for human dynmics than anything else. If you
are deleting out of "random" spots in the table, this will have no
effect.

NOTE: The CHECKIDENT works best, for the purpose you desire, when you
actually set up a reseed value.

Having "holes" in a table should not matter, except to humans who look
at them. And reseeding after deletion has some possible disastrous
effects, esp. if you have not been diligent with your referential
integrity.

Peace and Grace,


--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
You're more likely to get useful responses if you ask in a discussion
group for the DBMS you are using. This group is for .NET programming
questions.

If you are using SQL Server, there are numerous groups that begin
with:

microsoft.public.sqlserver

where you'll quickly get the assistance you need.
Click to expand...

People see .General and ignore everything else. ;-)

Peace and Grace,

--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
People see .General and ignore everything else. ;-)

Peace and Grace,
Click to expand...
Ok. To redefine a question ie to ask another thing I need is how to fill
the first column of the ListView with the row number? Consist the row
deleting.
 
If you are deleting all the ending rows, then this is useful ...
perhaps. it is more useful for human dynmics than anything else. If you
are deleting out of "random" spots in the table, this will have no
effect.

NOTE: The CHECKIDENT works best, for the purpose you desire, when you
actually set up a reseed value.

Having "holes" in a table should not matter, except to humans who look
at them. And reseeding after deletion has some possible disastrous
effects, esp. if you have not been diligent with your referential
integrity.

Peace and Grace,
Click to expand...
Ok. I must reformulate questions which bother me. In VS2008 I create a
table with ID and Text column. Then create DataSet for that table,
DataSource and finally ListView.
First question: If I don't want to end user see ID column, for example
if the column is uniqueidentifier it is unnecessary for end user. How
and where to hide or remove that column which is necessary for queries
which insert update and delete from the table?
Second: How to add column with row numbers read only data?
Thrid: Is it out of best practice to put primary key on article column ?
Why examples always start with ID column which is int or uniqueidentifier?
 
Mario said:
First question: If I don't want to end user see ID column, for
example if the column is uniqueidentifier it is unnecessary for end
user. How and where to hide or remove that column which is necessary
for queries which insert update and delete from the table?
Click to expand...

You have to turn off the automatic column generation bits with any of
the .NET drag and drop controls. After that, you can define what the
user does and does not see. If the info is important, you simply set it
up as the key for the "row" and you can still get it when the page
submits back.

I will have to find a code sample of this and post.
Second: How to add column with row numbers read only data?
Click to expand...

read only data or row numbers as read only? Not sure I am getting the
question.

The row number can be pulled with any interaction, so you need never
expose it. The ID, as stated above, can be made the key for the "row",
so that is not an issue.

Read only is not an issue as you simply do not allow editing of the
item.
Thrid: Is it out of best practice to put primary key on article column
? Why examples always start with ID column which is int or
uniqueidentifier?
Click to expand...

In general, in datbase design, the primary key is some form of numeric
and is NOT human readable data. This is not true for all database
systems, but it is both

a) more efficient
b) Less likely to end up with data problems from human intervention

when the key is derived and has no real bearing on the data. This is why
most SQL Server/Oracle/Postgress/etc (RDBMS) developers create derived
keys. In SQL Server, the most common are IDENTITY fields (some type of
integer) and row GUIDS because both can be auto-generated.

The problem with non-derived keys is there comes a time when the actual
"key" ends up changing, if only for one individual, which means you have
to create a mechanism to change it throghout the database. If this is
only one individual, it is a lot of work for little gain. Even when it
is common, it is dangerous.

Peace and Grace,

--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Back
Top