[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Numbering In SQL

Posted on 1998-11-21
7
Medium Priority
?
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 80 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

656 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