Insert Question

I have found a proc that reads as follows:

CREATE PROCEDURE InsertArea
@.AreaDescr as varchar(50)
as
insert into tblAreas
select isnull(max(AreaID)+1,1), @.AreaDescr
from tblAreas
GO

My question: tblAreas is a simple lookup table but why would I use this method of managing the AreaID instead of just inserting the record using an Identity column and being done with it? So I am confused on whether there is a hidden secret here or if the person wasn't aware of the Identity field = not knowing what they were doing? All of the tables in this database are like this as well, not just an isolated few.

Thanks for any input.
GregJust use an identity column, if you can. This kind of logic is leftover from either an old database engine, or an old database designer.|||Generally speaking, I agree with blindman. The only time I would use this type of process is when the "business logic" dictates that there can not be missing numbers, which as we all know can happen with an identity column.|||Any logic that relies upon the relative values of a surrogate key is not business logic. It's just bad logic.|||I respect your opinion. How would you populate a field that needed to increment by one, and could not skip numbers? Say, a check number field or something like that.|||How would you populate a field that needed to increment by one, and could not skip numbers? Say, a check number field or something like that.if that is indeed a requirement, then i would pre-populate the numbers manually

in other words, if you issue a cheque book with cheques numbered 001 through 150, then insert those numbers into (some) table and indicate that they have been issued but not disbursed|||if that is indeed a requirement, then i would pre-populate the numbers manually

in other words, if you issue a cheque book with cheques numbered 001 through 150, then insert those numbers into (some) table and indicate that they have been issued but not disbursed

Huh? I am not a big fan of identity either but can you explain the rationale for this for us lesser mortals. Pre-populate? How do you make this scalable? A really big table with empty records waiting to be filled in? Do you add so many empty records a day? What if you go over your set amount. It's early, am I misreading this?|||Huh? I am not a big fan of identity either but can you explain the rationale for this for us lesser mortals.please, sean, that's inflammatory language, let's not get into another flamefest

buddy asked a question, i told him one way how, and you're questioning why

it's not a "why" question, just accept the requirement to generate a sequence of numbers with absolutely no gaps, and let's see your suggestion for this|||I did not think it was inflamatory at all. I was asking a question.

~"The IT world can be harsh" - Rudy from the other day.

Any time you poke fun at me it can be justified. When I ask a legit question, it's a flame war.

And you did not answer me.

First I would ask this guy why it is a requirement that there are no missing numbers. I suspect deep down there is some suspect logic here. Then I would ask him how this works out if one of these rows are deleted. Does he repopulate this column and re-establish his relationships each time? The whole thing is a bad idea.|||I think it was more of an inphlegmatory statement.

I like Rudy's idea of prepopulating check numbers when they are issued, and then complete them when they are disbursed. Remember, even check numbers these days are not always sequential. Sometimes I write checks from two different sets alternately, and then there are counter-checks and electronic checks too.
But I also suspect, like Sean, that if there is a need for a gapless incrementing value then there is a flaw in the application concept.|||Get the hose...

Damn.

us mere mortals...Sean, you still smarting from the pad left with zeroes thread?

The answer to this thread is "Wake up and rework your model"

Plus the sproc he "found" is a poor construction to replace identity. It show lack of forsight in a multiuser environment, and can get hosed easily, and rather quickly.

And the check analogy, what if I cancel a check..bad analogy too, but is a "missing" number in a sequence, at least from the banks perspective.

And if you want a non-identity, Identity column

Try this

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx|||And the check analogy, what if I cancel a check..bad analogy too, but is a "missing" number in a sequence, at least from the banks perspective.no, it isn't, it's an excellent example

and no, it isn't missing at all, it's just cancelled

it's the same situation joe celko refers to in several of his examples -- what did we do in the old days of pre-printed order forms, when you spilled coffee on an order form, you couldn't use that one, but you couldn't just destroy it, you have to account for that order number in the order number sequence somehow

just saying "rework the model" does not really address the business requirement adequately

rework it how, exactly?|||just saying "rework the model" does not really address the business requirement adequatelySo when the business requirement is flawed, then we fix it with code? I usually go back to the Business Analyst and send them back to the project sponsor to fix the underlying problem... I rarely fix a bad business process by adding code to it.

-PatP|||I rarely fix a bad business process by adding code to it.geez you guys are stubborn

okay, let's suppose neil diamond has personally autographed a series of 100 gold-plated cds of "margaritaville" and these are now up for sale

your job is to record who purchased each one

you need to assign a "record number" (pun partially intentional) numbered 001 through 100 to each of these discs

you're going to go back to the sponsor and tell them this is a bad business decision? tell them you can't assign a sequential number because it might have gaps and you don't know how to do handle them?

sheesh!!!|||Ok what happens when one of the records (pun partially intended again) turns out to be non readable and the purchaser rejects the record.|||then you update the record (heh) and mark it as non-readable or other such status

the point is, you cannot delete it, you must account for that number|||What if I don't use a sequential number and I use a GUID?

I think you're mixing apples and oranges here....

And if I was worried about the sequence of things, I think I'd rather use datetime

Has the original posters head exploded yet?

And remember, yesterday was hump day, so if you missed out, it's your own fault...|||What if I don't use a sequential number and I use a GUID?

I think you're mixing apples and oranges here....no, i think you are missing the point

your client says "i want these 100 discs numbered as 001 through 100"

and you come back with "okay, let's use a GUID for that!"

i feel like asking "have you ever actually worked with clients?" but that would be rude and demeaning

the point is not what you would rather use, the point is what would you actually use to record the numbers 001 through 100

i don't see why everybody's having such a hard time with that concept|||In this situation the number of the record would be field in the record or property of my object but it would not be my primary key or record identifier.|||In this situation the number of the record would be field in the record or property of my object but it would not be my primary key or record identifier.excellent answer, and now we are getting somewhere|||no, i think you are missing the point

your client says "i want these 100 discs numbered as 001 through 100"

and you come back with "okay, let's use a GUID for that!"

i feel like asking "have you ever actually worked with clients?" but that would be rude and demeaning

the point is not what you would rather use, the point is what would you actually use to record the numbers 001 through 100

i don't see why everybody's having such a hard time with that concept

Well you'd have to try harder to get me pissed off.

I beleive the discussion was about the relative offset of a number and it's relationship to the other numbers in the sequence. Which is meaningless.

What for example, does the fact that record 5 is before record 6, except that?

Or that record 7 was destroyed in a fire and no longer exist?

The numbers themselves hold no special meaning. And we all know this. It is about the number only that we are discussing, not the other stuff that you are alluding to, that would require other, specific information, which of course would have to be recorded in the database. Which is not what this thread is about.

It about the poster trying to derive valuable information, using a sequence number, alone, by itself, with no other facts. Period.

Damn hangover.|||In this situation the number of the record would be field in the record or property of my object but it would not be my primary key or record identifier.

The above plus...

Deny delete permissions on the table, use IDENTITY or SCOPE_IDENTITY or some nonsense to populate.

That is my final answer. Sorry for the wait, been busy writing documentation.|||Wow, this thread exploded overnight (my overnight anyway). I'm not the OP, but I did raise the issue of some applications requiring sequential numbers with no gaps. I'll avoid the debate about whether you should use that field as PK or not. The fact is, as anyone who has worked in an accounting, gaming or other environment that gets audited knows, there are situations that have this requirement.

In the earlier example of the records numbered 1-100 and one gets destroyed, it's already been pointed out that the destroyed record must still be accounted for. An auditor is going to come in and ask where number XX is, and you'd better be able to tell him, not just say "the system skipped that number". Missed numbers equal possible theft to an auditor.|||arggghhh. accounting. the natural enemy of every dba. buy something of the shelf like Great Pains I mean Great Plains, ACCPAC, MAS90... The rules... the pain... the spreadsheets... the humanity...|||The pile. The only natural enemy of the hole.

More out of curiosity than to prolong the conversation, do banks actually care if I void a check in order to set up a direct deposit? Or do they let that check go as MIA?|||While I can't cite the exact section and paragraph, under GAAP (http://cpaclass.com/gaap/gaap-us-01a.htm) rules any documents that need to be serialized must be serialized before use. In other words, it has to be done while the forms are blank, with no data on them at all. Once any data has been recorded on the documents, any serialization is unacceptable under GAAP (it might be convenient, it is certainly permitted, but not admissable for any kind of GAAP compliance).

I work with bean counters every day. I've had this stuff drilled into my very soul. If you are adding serial numbers after the form has been filled out (or worse yet already stored in the database), the auditors had better cite that as a GAAP failure on any opinion they issue.

-PatP|||More out of curiosity than to prolong the conversation, do banks actually care if I void a check in order to set up a direct deposit? Or do they let that check go as MIA?Most banks don't even insist that check numbers be unique or even provided. I don't know of any that care if checks are AWOL, although they often report missing checks for customer convenience.

-PatP|||While I can't cite the exact section and paragraph, under GAAP (http://cpaclass.com/gaap/gaap-us-01a.htm) rules any documents that need to be serialized must be serialized before use. In other words, it has to be done while the forms are blank, with no data on them at all. Once any data has been recorded on the documents, any serialization is unacceptable under GAAP (it might be convenient, it is certainly permitted, but not admissable for any kind of GAAP compliance).

I work with bean counters every day. I've had this stuff drilled into my very soul. If you are adding serial numbers after the form has been filled out (or worse yet already stored in the database), the auditors had better cite that as a GAAP failure on any opinion they issue.

-PatP

And who, pray tell, does this serialization?

Why not serialize on inception?

You don't pre-serialize a trade do you?

I trust what you say, but I don't understand, or have ever seen it, even with all the gov work I've done, or street work.|||Hi Guys

I'm not sure I want to get involved in this thread as some of you seem a little tetchy but ......

a) Purchase Order No's - Goods Despatch Numbers (GDN's) & Goods Receipt Numbers (GRN's) are often serialized for very good business reasons.

b) I have always found it annoying that new Developers automatically use Identity fields without thinking about the fact that Primary Keys should be used for LOGICAL uniqueness not just a method of achieving system Ref Integrity and trying to improve performance by using a single Int field, especially on small databases
ie. I say use a real world Logicaly unique composite primary key (even if they're VarChars) IF POSSIBLE.

c) An exclusive lock on the previous max(id) would prevent problems if you think 2 people may run this statement at the exact same time (takes milliseconds to run).

d) please don't shout at me it's all IMHO ;-)

GW|||When Auditors worry about serial numbers, they are looking to be able to guarantee something. Specifically, that all of the serialized items can be accounted for throughout an entire process.

In order to make that guarantee about forms, those forms need to be uniquely identifiable in some way that has nothing to do with the way the form is used. The most commonly accepted way to do this is to pre-print serial numbers on those forms.

If you know that a particular branch/office/business/whatever started with forms X through Y, you can ensure that all of the forms are either present or properly accounted for... If these numbers are added after the fact (once the forms have been under control of the party(s) you want to be able to make that guarantee about, the numbers are useless for the purpose of making the guarantee.

If you add the serialization after the data is transcribed (copied from the original form to another media such as a database), that makes the serial numbers even less useful because at that point they have no relationship to the physical (controlled) forms at all.

This would be a textbook case of a serial number that was meaningless from a GAAP standpoint.

-PatP|||Exactly

This is why I would use the preprinted Hardcopy GDN/GRN number as the PrimaryKey and ask the input clerk to enter it into the system (with a bit of business logic to check format etc).

Purchase Orders are usually generated by the system so can be generated at point of creation. As far as I concerned the PO does not exist till it's commited to the system, at point of commit it's serialized, i imagine this method then complys with GAAP.

GW|||I don't think that GAAP rules apply outside of the United States, so you probably don't need to worry about them. I know there is an organization like the AICPA in the United Kingdom (I think it is the SBA, but I'm not sure) and I'm sure that they have similar rules.

Because the PO numbers are generated for internal purposes only, there is no point in auditing them. Who would care if PO #1234567 was missing? That wouldn't hurt anyone that I can imagine.

An auditor only cares about the things that they need to prove or certify. They might only be engaged to prove one specific thing, or they might be engaged for a full systemic audit (an awful thing).

-PatP|||Let me pitch in with my two cents...

If you want a running sequence number without using identity column, there are two ways this can be achieved: 1) use max as said before 2) use a separate table to hold a value and increment it as required. E.g. if you have multiple such requirements, you can have each row containing a number. I know 'Blindman' will jump in and try to get me with something here. What the heck? There is no such thing as old style and new style. Use the one that is convenient as long as that does not hurt the system. By the way, Windows has lot of VAX VMS concepts built-in anyway.|||:o :o :o :o|||I know 'Blindman' will jump in and try to get me with something here.Of course. I can't let bad advice slip by, since beginners tend to rely upon the information they get from these forums.
There is no such thing as old style and new style. Sure there is. That's a really uninformed statement. If you insist there is no difference between old style and new style, you can "Goto" heck. And there is also a difference between good programming and sucky programming, and using either of the methods you suggest is both slow from a processing standpoint, and needlessly complex from a programming standpoint.|||Go back to your own advice. Reading BOL... If you do not understand then don't think that it is uninformed statement. You stated that database cannot be shrunk... Go and check the BOL under DBCC SHRINKFILE... By the way, before you jump and attack me, you better get your side straight. You talk about programming...

Of course. I can't let bad advice slip by, since beginners tend to rely upon the information they get from these forums.
Sure there is. That's a really uninformed statement. If you insist there is no difference between old style and new style, you can "Goto" heck. And there is also a difference between good programming and sucky programming, and using either of the methods you suggest is both slow from a processing standpoint, and needlessly complex from a programming standpoint.|||I never stated that databases can't be shrunk. I said they can't be shrunk below their original size. Books Online:
You cannot shrink an entire database to be smaller than its original size.
Therefore, if a database was created with a size of 10 megabytes (MB) and
grew to 100 MB, the smallest the database could be shrunk to, assuming all
the data in the database has been deleted, is 10 MB. You are a noob.|||Here is your famous BOL for your reference...

--

DBCC SHRINKFILE
Shrinks the size of the specified data file or log file for the related database.

Syntax
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)

Arguments
file_name

Is the logical name of the file shrunk. File names must conform to the rules for identifiers. For more information, see Using Identifiers.

file_id

Is the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sysfiles in the current database.

target_size

Is the desired size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size.

If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

--

Call yourself whatever you want and it can be shrunk below 10MB if required. Don't pat yourself. Will you?

I never stated that databases can't be shrunk. I said they can't be shrunk below their original size. Books Online:
You cannot shrink an entire database to be smaller than its original size.
Therefore, if a database was created with a size of 10 megabytes (MB) and
grew to 100 MB, the smallest the database could be shrunk to, assuming all
the data in the database has been deleted, is 10 MB. You are a noob.|||In this situation the number of the record would be field in the record or property of my object but it would not be my primary key or record identifier.

wtf, so now there are 2 keys ?
the number does happen to be unique and immutable, what 's the point in adding yet another key ?|||wtf, so now there are 2 keys ?
the number does happen to be unique and immutable, what 's the point in adding yet another key ?some people just cannot sleep at night unless they have assigned the key|||some people just cannot sleep at night unless they have assigned the key

Haha, and a table must have at least one identity!

Won't start that key debate.
Redundancy is meaningless.|||some people just cannot sleep at night unless they have assigned the key Guilty as charged... :(