Please Help - I've gone cross eyed.

Question

Please Help - I've gone cross eyed.

allan.kendall

SSC Enthusiast

Points: 114
More actions
February 26, 2010 at 6:40 am

#156720

Hello Everyone,
I have a specific requirement that sounds a little more complicated than it is and it is hard to decide whether it is an ASP issue or something geared towards SQL Sever 2005. None the less I shall try to explain what the problem/project is.
I have a simple web form with text boxes in it for users to enter data into. I want to make it so that when this form/page is opened up, it gets the next available auto number from sql database and displays it to the user inside a text box.
So what you have is a form that gets the next available auto number from my database (which will probably be a primary key identity number) and displays it in the text box.
At the moment you only generate the next number once the form has been submitted. I need to get the next number when the form loads, not after it has been submitted.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

Very, Very Poor design. Your database will many empty rows. What happens when you insert a new blank record just to get SQL to create an ID, and then the person walks away, or the connection is lost, or the browser is closed, your database will be left with a row in multiple tables that have no data. Whom ever came up with an idea like that, is totally stupid, that is very poor design.

There is absolutely no reason that the front-end would need to know the ID of the record that has no data in it. You can easily return the ID of the row that was just entered. Why do you believe you would need the RowID first?

Andrew SQLDBA

Luke L SSC-Dedicated Points: 35545 More actions · Answer 2

I agree that this is perhaps not the best design decision. Perhaps if you're a bit more clear on what the business requirement is, we'll be able to help you implement a better solution. The biggest issue I see is the case where more than 1 person opens the form for data entry at the same time. If you display the same next identity value to each user, you may end up violating a constraint when you attempt to insert the record.

The only way around that is actually inserting a blank record and then updating, but this will cause issues like those discussed by Andrew above.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Not a good design.

The only way to ensure no duplicates and no blanks would be to hold an open transaction, locking enough rows that an incorrect id can't be inserted, while the user is entering data, meaning that non one else can enter data.

This kind of design leads to :

* Extremely poor performance

* Occasional duplicate key errors

* Blank rows

Pick any 1

Why does the screen need to show the ID at all? It's a meaningless value, the user shouldn't even know there is an indentity column, let alone its values for a particular row.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

allan.kendall SSC Enthusiast Points: 114 More actions · Answer 4

AndrewSQLDBA (2/26/2010)
Very, Very Poor design. Your database will many empty rows. What happens when you insert a new blank record just to get SQL to create an ID, and then the person walks away, or the connection is lost, or the browser is closed, your database will be left with a row in multiple tables that have no data. Whom ever came up with an idea like that, is totally stupid, that is very poor design.
There is absolutely no reason that the front-end would need to know the ID of the record that has no data in it. You can easily return the ID of the row that was just entered. Why do you believe you would need the RowID first?
Andrew SQLDBA

There is a way to make a point and a way to offer advice. Your post does neither.

allan.kendall SSC Enthusiast Points: 114 More actions · Answer 5

Luke L (2/26/2010)
I agree that this is perhaps not the best design decision. Perhaps if you're a bit more clear on what the business requirement is, we'll be able to help you implement a better solution. The biggest issue I see is the case where more than 1 person opens the form for data entry at the same time. If you display the same next identity value to each user, you may end up violating a constraint when you attempt to insert the record.
The only way around that is actually inserting a blank record and then updating, but this will cause issues like those discussed by Andrew above.
-Luke.

Thanks for the response. What I am doing is working with a purchase order form that is built in ASP.NET and it is replacing and old redundant Access Database that was previously used.

The PO form has data that is entered by the user and they also add stock to the PO and then submit it. The user data is submitted to one table and the stock is submitted to a different table and I have to tie them together. I was originally going to tie them by POID.

I have had it suggested to me that I should/could use a transaction to carry this out. The transaction would submit the user form data first, then get the ID from the new created row in Table 1 then return that ID and post it along with the stock entries to Table 2 and roll back if there are any errors.

GSquared SSC Guru Points: 260824 More actions · Answer 6

This kind of design can be done efficiently if you have the front end generate a GUID, instead of using a database-generated integer ID. The GUID can then be passed to other parts of the application, inserted into the database (either as a surrogate primary key or as a "row identifier" separate from the PK).

This also saves you from the minor network and server hit of having to connect to the database to get the ID. In a very high traffic situation, that can make a measurable difference in overall performance.

And, if the data never ends up making it into the database, for whatever reason, it doesn't matter, since it doesn't leave empty rows behind.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

allan.kendall (2/26/2010)
I have had it suggested to me that I should/could use a transaction to carry this out. The transaction would submit the user form data first, then get the ID from the new created row in Table 1 then return that ID and post it along with the stock entries to Table 2 and roll back if there are any errors.

You can. If there's user interaction anywhere within the transaction, you risk them going to lunch and coming back hours later, and your app being locked an unusable fir the duration. If there isn't, if you're inserting IDs, committing, then waiting for more info, you risk the user closing the app (switching off the machine) and leaving you with half-complete records in the database.

Seriously, there is no good way of getting an ID value from the database, then later putting the rest of the row in.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

GSquared (2/26/2010)
This kind of design can be done efficiently if you have the front end generate a GUID, instead of using a database-generated integer ID. The GUID can then be passed to other parts of the application, inserted into the database (either as a surrogate primary key or as a "row identifier" separate from the PK).

Yup. Just don't make that GUID column the clustered primary key.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150457 More actions · Answer 9

GilaMonster (2/26/2010)
Seriously, there is no good way of getting an ID value from the database, then later putting the rest of the row in.

There is, and it's called a Sequence Table. Look out for an article heading this way very shortly 😀

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Paul White (2/27/2010)
GilaMonster (2/26/2010)
Seriously, there is no good way of getting an ID value from the database, then later putting the rest of the row in.
There is, and it's called a Sequence Table. Look out for an article heading this way very shortly 😀
Paul

Did you ever send that to me or did I just miss it somewhere in the daily flood of email?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 11

Jeff Moden (2/27/2010)
Did you ever send that to me or did I just miss it somewhere in the daily flood of email?

Barry distracted me with his interesting solution to Itzik's Concurrent Sessions problem :blush:

And, there have been some really interesting questions on the SSC forums just recently...

It's nearly ready for review, it'll be in your inbox real soon now :-).

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

allan.kendall SSC Enthusiast Points: 114 More actions · Answer 12

GilaMonster (2/26/2010)
GSquared (2/26/2010)
This kind of design can be done efficiently if you have the front end generate a GUID, instead of using a database-generated integer ID. The GUID can then be passed to other parts of the application, inserted into the database (either as a surrogate primary key or as a "row identifier" separate from the PK).
Yup. Just don't make that GUID column the clustered primary key.

I am very interested in this suggestion, do you perhaps have or could offer more details about it? I have never done anything like that before so I would take any guidence I can. Many thanks for your replies and input thus far.