Solved

Numbering In SQL

Posted on 1998-11-21
7
200 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 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

13 Experts available now in Live!

Get 1:1 Help Now