Solved

Numbering In SQL

Posted on 1998-11-21
7
226 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Check ALL SP in database make sure there are no errors 17 45
SQL Group By Question 4 20
Shrink multiple databases at once 4 28
job schedule 8 20
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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 to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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