Order of inserted rows problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Order of inserted rows problem

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-12 : 04:46:19

Good morning,

I have the following simple insert into statement


if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'MyTypeLessTable')
drop table dbo.MyTypeLessTable

create table dbo.MyTypeLessTable (
		Ident		int identity(1, 1),
		ColumnNames varchar(200))

insert into dbo.MyTypeLessTable (ColumnNames)
select 'Column1' union
select 'DD_Addr1' union 
select 'DD_Addr2' union
select 'DD_Addr3' union
select 'DD_County' union
select 'Column6' union
select 'Column7' union
select 'Column8'

when I do a select on my table, I see the column have been ordered alphabetically. That is, Column1 has Ident of 1, Column6 with Ident = 2 and so on. Is this a server setting and if so, how do I over ride it?

Thanks for your help in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 04:54:47

Try like this & see:-

insert into dbo.MyTypeLessTable (ColumnNames)
select 'Column1' union all
select 'DD_Addr1' union all
select 'DD_Addr2' union all
select 'DD_Addr3' union all
select 'DD_County' union all
select 'Column6' union all
select 'Column7' union all
select 'Column8'

--select * from dbo.MyTypeLessTable

the way it orders alphabetically when you use union operator is because union sorts internally in alphabetical order and takes distinct values from them.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 04:58:30

You can't.
See http://blogs.msdn.com/craigfr/archive/2008/01/30/maintaining-unique-indexes-with-ignore-dup-key.aspx

and here http://weblogs.sqlteam.com/peterl/archive/2008/02/06/Curiosity-found.aspx
and here http://weblogs.sqlteam.com/peterl/archive/2008/02/07/Curiosity-found-the-wrap.aspx

E 12°55'05.25"
N 56°04'39.16"

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-12 : 05:07:57

That's smashing! Thanks for your prompt help.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-12 : 05:19:30

Peso, in your blog post [http://weblogs.sqlteam.com/peterl/archive/2008/02/07/Curiosity-found-the-wrap.aspx], you have a identity column and another int column which is primary key of your table. Now, when you try to insert duplicate primary key value into your table ,it gives error message and doesnot insert. It also increases identity value. Also, in craig's article, he is having non clustered index on columns where values are being inserted.
What OP has posted is a table with a identity column and a varchar column with no index on it. If there is no index on second column here, then, why sql server is inserting rows in alphabatical order? Hope I have made my query clear.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 06:27:28

What i could find was it was doing this only with union but was inserting in given order when using union all. I really think this is due to grouping operation happening inside by union to take distinct values

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-12 : 06:39:05

Visakh, my point is that what distinct values have to do when you are inserting to a column which has no indexes.I read blog posts in peso post. What I could get is that if you have indexes on columns and try to insert, you see this behaviour.In this case when there is no index on "ColumnNames" column, then why it is inserting in sorted manner.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-12 : 07:19:23

Stored Order in a table doesnt matter as long as as you explicitly order the results in your select statement

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 07:27:56

quote:
Originally posted by sunil

Visakh, my point is that what distinct values have to do when you are inserting to a column which has no indexes.I read blog posts in peso post. What I could get is that if you have indexes on columns and try to insert, you see this behaviour.In this case when there is no index on "ColumnNames" column, then why it is inserting in sorted manner.

i was not commenting on your point. I just said what i saw here

. I understood what you said Sunil (and read peso's blog too).

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-12 : 07:39:56

quote:
Originally posted by visakh16

quote:
Originally posted by sunil

Visakh, my point is that what distinct values have to do when you are inserting to a column which has no indexes.I read blog posts in peso post. What I could get is that if you have indexes on columns and try to insert, you see this behaviour.In this case when there is no index on "ColumnNames" column, then why it is inserting in sorted manner.

i was not commenting on your point. I just said what i saw here . I understood what you said Sunil (and read peso's blog too).

No Problem

But my query is still open. Can anyone explain this.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-12 : 23:50:37

Sunil,
You are making your point based on wrong assumption. Peter always used UNION ALL operator in his examples, and not UNION operator.

If you run OP's example with UNION ALL and UNION and check the execution plans, you will see that UNION results in merge join which is performed on two sorted input sets, whereas UNION ALL simply concatenates all the inputs. Hence, it is obvious that OP should get the result which he got. It doesn't matter if the column has index or not, when you are using UNION operator.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-13 : 00:06:15

quote:
Originally posted by harsh_athalye

Sunil,
You are making your point based on wrong assumption. Peter always used UNION ALL operator in his examples, and not UNION operator.

If you run OP's example with UNION ALL and UNION and check the execution plans, you will see that UNION results in merge join which is performed on two sorted input sets, whereas UNION ALL simply concatenates all the inputs. Hence, it is obvious that OP should get the result which he got. It doesn't matter if the column has index or not, when you are using UNION operator.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Thanks for providing explanation. Visakh also gave similar explanation (Posted - 02/12/2008 : 04:54:47) , but perhaps I was dreaming

and did not notice it.

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-13 : 06:08:38

Thanks for your input guys. I usually use union all when I create test record sets but as I had unique values I opted for a UNION. I should have looked at the execution plan before posting but anyway, thanks for the education.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 06:28:33

quote:
Originally posted by Abu-Dina

Thanks for your input guys. I usually use union all when I create test record sets but as I had unique values I opted for a UNION. I should have looked at the execution plan before posting but anyway, thanks for the education.

why should you use UNION when you have unique values? Shouldnt it be other way around?

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-02-13 : 07:53:56

quote:
why should you use UNION when you have unique values? Shouldn’t it be other way around?

LOL... I know what you're saying but I used UNION as I knew I wasn't going to lose any of my values. Sounds strange as people use UNION to eliminate duplicates from two or more record sets but for my simple exercise, the part which creates the test data didn't require eliminating duplicates. Does that make sense?

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-13 : 08:03:14

quote:
Originally posted by Abu-Dina

quote:
why should you use UNION when you have unique values? Shouldn’t it be other way around?

LOL... I know what you're saying but I used UNION as I knew I wasn't going to lose any of my values. Sounds strange as people use UNION to eliminate duplicates from two or more record sets but for my simple exercise, the part which creates the test data didn't require eliminating duplicates. Does that make sense?

What I now know about UNION and UNION ALL is that if you are sure you don't want to worry about duplicates in your result set, then use UNION ALL otherwise UNION. Hope this makes sense too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources