June 22, 2012 at 5:43 pm
I have a sproc that accepts a tvp. The tvp is defined as a one column user defined table type. The column is named procedure and has a type of nvarchar(50). The table also defines a non-clustered primary key on the column.
My problem is that for some reason the physical order of the table seems like it's being altered. If I insert ccc,bbb,aaa into this table I would expect them back in that order when I do an unordered select, BUT I'm getting them back aaa,bbb,ccc. At first I thought this was because the primary key was clustered so of course the physical order of the table IS the primary key order. I asked the dba to change it to non-clustered thinking this would fix the problem but it still insists on ordering the selects. It seems like the physical order of the table is altered even though the index is non-clustered. That surprises me.
In my opinion a primary key on this table is unneccessary. It should just be a heap. There will never be more than a few rows passed in. But I'm not the dba so it's not my call.
1.) Why are the selects coming back ordered
2.) Is there any point in having a primary key on a table like this?
.
June 22, 2012 at 10:00 pm
BSavoie (6/22/2012)
... I would expect them back in that order when I do an unordered select,...
Really???
Why would you expect any particular order when you do an unordered select? Your statement sounds really foolish when you say it out loud. try this: "My unordered select is returning the rows in the wrong order." or this: "I want my rows returned in some particular order, but I don't want to be bothered with telling sql server what order I want them returned in"
Now, you're not silly enough to actually DEPEND on a particular order coming out of an unordered select, and you will admit that doing such a thing is an act of lunacy? You will? Good.
Of course internally the data is stored somewhere and if you happened to have a non-clustered covering index on a column in the table, then you couldn't fault sql server for using that non-clustered index to answer your query. Especially for really low row counts where all the data fits on 1 page, the cost of reading that one page from the heap or from a non-clustered index is identical.
June 22, 2012 at 10:06 pm
oh, and to answer your second question, a primary key exists to enforce uniqueness, nothing else.
June 23, 2012 at 12:25 am
Not worried about uniqueness in this case. I want the rows back in the same order they were entered (the physical order). If I issue a "select" with no "order by" on a table and that table has no index (heap) the rows should be returned in the physical order. In my world that physical order is in fact an "order". I guess in your world a heap has no order it simply vibrates in some abstract chaotic dark matter sort of state.
My mistake was thinking that only a clustered index would eliminte the ability to ever get the data back in the original physical order but that's not true. ANY type of index apparently eliminates the ability to select the data in the original, physical order. Lesson learned.
Thanks SpringTown for lowering yourself to my level for the painful amount of time it took you to craft your oh so clever post.
.
June 23, 2012 at 5:49 am
BSavoie (6/23/2012)
I want the rows back in the same order they were entered (the physical order).
Then you need a column that defines that order and you need to order by that column.
If I issue a "select" with no "order by" on a table and that table has no index (heap) the rows should be returned in the physical order. In my world that physical order is in fact an "order".
Not at all. Unless there is an order by statement, SQL is free to return data in any order whatsoever as there is no requirement to use any order over any other. There's no implication that leaving out the order by means 'ORDER BY InsertTimeStamp'
ANY type of index apparently eliminates the ability to select the data in the original, physical order.
Not true, there's no such thing as 'original physical order' for any table, index or otherwise. If you want a specific order of rows, you have to have a column that specifies that order and you have to order by that column. Relying on some default order is just asking for trouble. Maybe it'll work, maybe it won't.
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
June 24, 2012 at 5:03 pm
Just think about it.
You go to a grocery market and see this nice bucket of tomatoes (they are like in a heap) and say "I want them all". So the merchant nicely takes them one by one, puts them totally randomly in a paper bag (we're environmentally friendly) and hands them to you. You expect any order within that bag? No, you don't, and actually you don't care, do you?
Next time you get there again, the grocer has got clever. He's got a little door at the back of the bucket, that lets the tomatoes out by size, smallest first (your clustered index), he's clever coz he charges per tomatoe. Now of course you are not satisfied, coz you are not getting what you want, so he starts taking out from the top, and he totally stuffs up the order you expected them to get into your bag.
Now, that's what SQL does too.
Remember, SQL is based on relational algebra, which in turn is based on set theory. Sets have no order within their tuples. Hence you need to tell SQL in what order you want your tuples to be returned, no matter what order you put them in in the first place.
Hope this didn't sound condescending. 🙂
June 25, 2012 at 10:54 pm
Wow, thanks all. Sure didn't know that. I always thought that I could depend on a heap being ordered in the order the data was entered. In fact the test programs I put together to test that theory seemed to prove the theory. Must be a coincidence. I guess I'll have to get the dba to add another column to the tvp to which specifies the exact order the user entered the data. Very humbling. Every time I feel like I understand things pretty well, something basic bites me!
FYI, here's the test script that I THOUGHT proved my theory if anyone is interested. It sure seems to prove that a heap retains the order of entry, but a few rows never proves very much I guess.
--* Table with non-clustered index
create table #test1 (
c1 varchar(10)
)
Create NonClustered Index [idxtest] on #test1([c1] ASC)
insert into #test1
select 'ccc'
union all select 'bbb'
union all select 'aaaa'
select * from #test1
drop table #test1
--* Heap
create table #test2 (
c1 varchar(10)
)
insert into #test2
select 'ccc'
union all select 'bbb'
union all select 'aaaa'
select * from #test2
drop table #test2
.
June 25, 2012 at 11:34 pm
The problem is that the order is non-determined, not random. So you can run a test 10000 times and the data will come back ordered the way you think it should. Then the 10001st, it's different
With small row counts it's extremely likely that the data will come back ordered by the index used to retrieve it or by the physical storage order (which is not necessarily the order of insert) if there's no index at all because of the way the engine works. But it's 'extremely likely', not guaranteed.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply