Ordering Inserts into a Table
-
I'm using SQL Server 2000. I have a simple stored proc which has several insert statements inserting into a single table. This is a simple 1 column table of varchar type. There is no primary key no index nothing on this table.
The order of inserted values is important. They should be inserted into the table in the same order as the insert statements in the proc i.e. each insert statement should just append at the bottom of the table.
But currently the inserts are jumbled up. The third insert may show up first and others later.
This was working fine for many days and suddenly the problem came. We’ve tried dropping the stored proc, table and recreating them also.
Please suggest what could be the problem.
Thanks
Naveen
-
Lots of things can be causing this.
1) Physical pages have limited space, but will "fill in the gaps" when a new record is inserted. SQL Server writes a row down, where there is space on a page. If you have varchar inserts, this means that a different number of inserts will take place on a single page. and if there is a gap, (caused by a delete of a row or a long line that cannot fit on to the remaining space on the page follwed by a short row insert that can fit in the remaining space) then it might get filled in with a new insert that is out of order. If this is the problem???? then go with a char structure instead of Varchar. This way you will be guranteed the same number of rows will be on every page. and no GAPS in the data.
2) Make sure that they go in in order, one row at at time, single threaded with no one else on the system.
3) Alter the table and add a column of int with Identity. Now your guranteed to have a sort column even if the db returns the data out of order.
Just some ideas of the top of my head.
Eric
-
>The problem is that you have no idea what your are doing.
Apparently, Joe, you don't either because you provided no help to solve the problem... only caustic criticism on a newbie. Really bad form.
Naveen, Eric's third item should do the trick for you.
--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) -
Naveen,
As Joe less than tactfully explained, SQL Server does not store data in the sequence in which you insert it, even when using an identity column. Neither does it present the data in the order that it is inserted. I am sure that your goal is not necessarily to guarantee that the data is STORED in a particular order, but rather that it is PRESENTED in a particular order. As Eric and Jeff both stated, an identity column will achieve this for you, as would a datetime stamp. But, to achieve the presentation of the data in that order, you must also tell SQL that you wish to see the data in that order. Otherwise, you will still experience the problem that you are currently experiencing, because the data STILL is not being STORED in the desired order, as Joe pointed out in his last post.
So, the way to ensure that the data is PRESENTED to you in chronological order, you should create either an identity column, or a date/time stamp column to mark each record chronologically. Then, when you retrieve the data, you must use an ORDER BY clause in your select statement, ordering by your identity or datetime column.
You can think of it this way - If you're storing your golf balls in a bucket, but you want to know the order in which they were placed in the bucket, the only way to achieve that is to mark each ball as they go into the bucket. Mark the first one with a "1", then "2", and so on. When you take a golf ball from the bucket, just because you reach to the bottom of the bucket, doesn't mean that you'll get number 1. To do that, you must look at the mark and verify that it is number 1.
Now, if you create the identity column, you probably should place a clustered index on the table, using the identity column.
Steve
-
Wow, while I personally think, one shouldn't over-interpret each of Joe's words (or even take it personally) this was a nice explanation, Steve. Though I don't agree with having the clustered index on the column with the IDENTITY property. But that just might only be me.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Joe,
Perhaps you would have been happier if I had said, "Your data model really doesn't belong in a SQL table. Perhaps you should consider a sequential file for what you are doing." If so, why did you not say that in the beginning, rather than "The problem is that you have no idea what you are doing."
Joe, I respect your knowledge of SQL and your ABILITY to dispense that knowledge to others (as seen in your books and some of your other posts). But I do NOT respect the manner in which you have responded to this thread or to a few others recently. I believe that you have a GREAT DEAL to offer this forum. But I also believe that you need to remember that the people who are asking the questions are seeking help, not a condescending attitude and ridicule.
While you are absolutely correct that my recommendation that Naveen create a clustered index in and of itself will not work, which a person of your knowledge of SQL should have been able to ascertain from other information in my post, you have left out the part that I said MUST be included. That is the ORDER BY clause.
Frank, thanks for the vote of confidence, and I'm sorry if I've just fallen short of that confidence by taking further offense at Joe's further offensive behaviour.
-
Steve,
no need to worry.
Let me say, I follow silently the english MS newsgroups for quite a long time now. And when I realise one thing it is that Joe is just Joe. Neither the regulars on the newsgroup nor any member here will change this. And while I personally sometimes think, that his wording might be better a little bit more diplomatic, I am also aware that it sometimes need a big, big hammer and a rather harsh tone to wake someone up and show him what's right or wrong. And I also think that it is completely up to oneself to take Joe's rants personally and offending or amusing and entertaining or none of the above. There is no built-in automatism that questioners must feel in a specific way only because the answer is given by Joe Celko. Sometimes there is even some truth in his answer anyway.
Joe,
I for myself think it's absolutely fantastic to see you here. However, one reason why I prefer this place here rather than the english newsgroups is that you will see almost no ranting or personal attacks in this fora. Sure, discussions get heated at times, and that's absolutely okay, but all members behave in a very professional manner. Among other thing that means, that everybody is trying to help to the best of his knowledge. And while this best might sometimes be more or less "good enough" it yields absolutely nothing for nobody to just answer the original questioner, that his approach is wrong without also providing a way out or at least a workaround.
So, having that said (without having a good deal of coffee yet), what I miss in the whole discussion yet, is an explanation from the original questioner, why he want to have such an ordering. Perhaps there is a good reson, we just don't know yet.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you, Frank.
Steve
-
This forum is for assistance to others and we appreciate everyone remaining poite and professional towards one another.
The comments above from Mr. Celko are not appropriate nor necessary to get the point across. Please refrain from berating others lack of knowledge. We all have some level of ignorance in many areas. Please be more respectful in the future.
-
Folks,
Thanks a lot for all the wonderful responses to this newbie. Your responses have really worked and I truly appreciate all of them especially Steve who had put it down very well.
It's true that I'm new to SQLserver and probably avoid posting such basic questions to this forum which may be catering to pros.
Regards
Naveen
-
... probably avoid posting such basic questions to this forum which may be catering to pros.
I strongly disagree with this!!!!
Nobody is born as a pro. Everybody started somehow someday and grows by learning and understanding. So, please don't stop asking basic questions or pick on an aspect you haven't understood yet in an answer. You are here not on certain Java lists that would nearly kill for asking what a class is. This is and will be a good place to ask questions. No matter if on a basic, intermediate or expert level.
Having that said, glad to hear it worked for you.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Naveen,
So glad we've been able to help! I have to second Frank's comment that we are NOT here to cater to pros. These forums are for SQL users of ALL levels, but probably more for the less experienced than for the more experienced. Please don't be afraid to post any question about SQL, regardless of how basic you think it is. The vast majority here will be more than happy to try to help!
Steve
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply