|
|
Posted by raylopez99 on 07/15/07 21:02
On Jul 15, 10:52 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
> > I entered the parameters I wanted into the query, and it
> > worked (I was entering a new row in my Authors dB--which also gave
> > rise to a new question, kind of trivial, but why the new row is
> > automatically numbered with a successive number that does not repeat
> > even when you delete the new row later and run the query again... must
> > have something to do with a "seed" of some sort.. that is, after the
> > "3rd" row is entered, and I manually delete the row, the next time I
> > run the query the new row is not "3" but 4. And if I delete again,
> > the next time the new row is 5, not "3". And again, the autonumber
> > becomes 6, not "3", etc. Not a big deal but I'm curious as to
> > why...must be some parameter that's akin to "consecutive renumbering"
> > for new rows).
>
> It appears that your table has the IDENTITY property, and it is an
> inherit characteristic of this property that it gives you gaps. If you
> want consecutive numbers, you should generate your surrogate keys yourself.
>
> The reason IDENTITY gives you gaps, has nothing to do with relational
> theory per se, but it's all about concurrency. The design permits very
> many concurrent inserts being carried out.
>
Thanks again Erland--you are a treasure trove of information! If I
could pay you I would; it's like having a virtual tutor in SQL!
I did indeed study the IDENTITY property (in VS2005 Visual C++/C#
"properties" are the new "global variable" of an object). Below is
what I found. I played around a bit and found that you cannot 'turn
off' the Identity Property easily. Your insertion of a record (row)
fails if Identity is turned off. Thus I am puzzled by the statement
below (from the Help file): ***Note If an identity column exists
for a table with frequent deletions, gaps can occur between identity
values. If you want to avoid such gaps, do not use the identity
property.***
How can you avoid such gaps by not using the Identity property? How
will your rows be autonumbered? Right now my AuthorID is simply a int
value that is autonumbered using the IDENTITY property.
Please be advised: I don't care if there are "gaps"--it makes no
difference to me--this is an academic question. No need to explain,
as I feel I've already taken up too much of your time (later, when I
actually do some serious programming in SQL, I'll probably need your
advice so I have to conserve on my goodwill here, haha). I'm sure you
can somehow set up a complicated (or maybe for you, simple) stored
procedure of some sort to increment a number used in a column and
decrement the same number whenever rows are added or deleted,
respectively. I can do the same in C++. And again, I don't see the
need (unless you are a neat freak and like consecutive numbers with no
gaps for your AuthorID). Just a curiousity at this point. [UPDATE:
rereading this before I hit the Send key, I notice a way out: perhaps
the trick is to use the AuthorID as a NON-Primary Key Column. RIght
now, in my Authors table, I use a Primary Key column with the Identity
Property, and you cannot use a Primary Key column that contains a NULL
value. Inspiration for this thought: "Also, it cannot be set for a
primary key column."]{Update to the Update: tried using Identity
Property turned OFF for a new column that allows Nulls, and it did not
work to generate a consecutive number, in fact the insert Record/Row
procedure**(below reproduced) failed to work}
Thanks again,
Ray
>From Visual Studio 2005's huge Help file on the IDENTITY property
(huge but often quite useless!):
How to: Modify Column Identity Properties
You can change the identity properties of a column if you want to
redefine the sequential numbers that are automatically generated and
stored in that column when new records are added to the table. You can
set the identity properties on only one column per table.
Columns that have the identity property contain system-generated
sequential values that uniquely identify each row within a table (for
example, employee identification numbers). When inserting values into
a table with an identity column, Microsoft SQL Server automatically
generates the next identifier based on the last used identity value
(the identity seed property) and the increment value (the identity
increment property) specified during the creation of the column.
Note
The identity property can be set only for a column that disallows null
values and whose data type is decimal, int, numeric, smallint, bigint,
or tinyint. Also, it cannot be set for a primary key column.
To modify the identity properties for a column
In Server Explorer, right-click the table with identity properties you
want to modify and click Open Table Definition.
The table opens in Table Designer.
[STUFF DELETED ON HOW TO DO THIS FROM THE TABLE DESIGNER GUI]
For example, suppose you want to automatically generate a 5-digit
Order ID for each row added to the orders table, beginning with 10000
and incremented by a value of 10. To do this, you would type an
Identity Seed of 10000, and type an Identity Increment of 10.
If you change any of the identity properties for a table, the existing
identity values will be preserved. Your new settings apply only to new
rows that are added to the table.
***Note
If an identity column exists for a table with frequent deletions, gaps
can occur between identity values. If you want to avoid such gaps, do
not use the identity property.***
--
** the Stored Procedure in question in this thread, to insert a new
row in the Authors table, which has three columns, AuthorID, LastName,
FirstName:
ALTER PROCEDURE dbo.InsertAuthor2
(
@LastName NVARCHAR(32) = NULL,
@FirstName NVARCHAR(32) = NULL
)
AS
/* SET NOCOUNT ON */
INSERT INTO Authors (LastName, FirstName)
VALUES (@LastName, @FirstName)
RETURN
Navigation:
[Reply to this message]
|