• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

Numbering In SQL

I am trying to setup a list of books in table using SQL I have the list setup with all the data.  But I want to have an autonumber field.  So each book would have a number any body know a fix.  I believe the "Identity" will work but I need more info about it.  Like if I want a field called number (which will be the autonumber) do I set it to identity.  please help anyone?
0
lobos
Asked:
lobos
  • 4
  • 3
1 Solution
 
lobosAuthor Commented:
Like what is the syntax..
and eg table will work..

create table books (number.....
                    title varchar(25),
                    chapter varchar(15))
0
 
mikeblasCommented:
IDENTITY will do what you want, yes.  It's an attribute you can apply to numeric types in your table:

CREATE TABLE books
(
  id_number int IDENTITY(10, 5),
  title varchar(25),
  author varchar(15)
)

INSERT books (title, chapter)
values
("Moby Dick", "Melville")

INSERT books (title, chapter)
values
("Catcher in the Rye, The", "Sallinger")

Note that you don't even mention the IDENTITY column in the INSERT; it's implicit. You can reference it in SELECT and UPDATE and DELETE statements, tho.

IDENTITY takes two parameters; the first is the number you'll start with and the second is the increment. I used a start of 10 and an increment of 5 just to prove the point, above.  Normally, you'll use IDENTITY(1, 1).

B ekiM

 
0
 
lobosAuthor Commented:
Cool, I have not tried this, but are you possitive this will work.  Cause I cannot find no information on this...(Identity)...so where did you get this infomation from.
Plus I was wondering are there any dissavantages to using this...
I will grant you the point but let me know about his!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lobosAuthor Commented:
Sorry I mean I will give the points but if you can tell me more that would be great!
0
 
mikeblasCommented:
It's in the online help. I'm looking at the online help for SQL Server 7.0, and it's there. I'm pretty sure the feature was added in SQL Server 6.5, but I don't have any 6.5 books here at home to double-check that. It's _not_ listed in "new feautres" in the 7.0 help, so that seems to support the 6.5 idea.

I'm sure the feature works.

The disadvantages are just that you have a monotonically increasing key. You might not want that because it sometimes isn't good for lock locality. A monotonically increasing key makes all the locks on the table appear near a few pages. So If your users are always more interested in looking at the newest items in a table, you might consider something else.

On the other hand, IDENTITY makes dealing with system-assigned primary keys far more effiicnet and very trivial.

This feature has existing in Oracle for years, and SQL Server is only just now getting around to it.

B ekiM
0
 
lobosAuthor Commented:
Thanks your a great help....can I get your e-mail for any more future possible questions..
0
 
mikeblasCommented:
I'm sorry, I don't have that kind of time.

B ekiM
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now