Solved

Numbering In SQL

Posted on 1998-11-21
7
218 Views
Last Modified: 2010-03-19
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
Comment
Question by:lobos
  • 4
  • 3
7 Comments
 

Author Comment

by:lobos
ID: 1091926
Like what is the syntax..
and eg table will work..

create table books (number.....
                    title varchar(25),
                    chapter varchar(15))
0
 
LVL 11

Accepted Solution

by:
mikeblas earned 20 total points
ID: 1091927
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
 

Author Comment

by:lobos
ID: 1091928
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:lobos
ID: 1091929
Sorry I mean I will give the points but if you can tell me more that would be great!
0
 
LVL 11

Expert Comment

by:mikeblas
ID: 1091930
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
 

Author Comment

by:lobos
ID: 1091931
Thanks your a great help....can I get your e-mail for any more future possible questions..
0
 
LVL 11

Expert Comment

by:mikeblas
ID: 1091932
I'm sorry, I don't have that kind of time.

B ekiM
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now