Need confirm this problem

Question

Post reply

Need confirm this problem

nguyennd

SSCommitted

Points: 1881
More actions
January 14, 2010 at 2:49 am

#209128

what are diffrence about 2 SELECT query? Can i remove ORDER BY from SQL 2?
Please resolve for me.
CREATE TABLE TEMPTEST
(
INT NOT NULL,
FIELDA INT NOT NULL,
CONSTRAINT [PK_TEMP] PRIMARY KEY CLUSTERED
(
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
);
SELECT * FROM TEMPTEST
SELECT * FROM TEMPTEST ORDER BY
http://unitech.vn

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

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

If you need a resultset ordered you HAVE to specify order by.

So remove it if you dont care about order , but use it if you do.

Clear Sky SQL
My Blog[/url]

nguyennd SSCommitted Points: 1881 More actions · Answer 2

nguyennd

SSCommitted

Points: 1881

January 14, 2010 at 3:20 am

#1103480

But 2 result set are same. Why?

http://unitech.vn

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Because by sheer luck ( not true, but the phrase will do) the rows are returned in an ordered list.

This is cannot be guaranteed.

Clear Sky SQL
My Blog[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Because order is not guaranteed unless you specify an order by. It may be the same this time, but that doesn't mean it always will.

If there's no order by, SQL is allowed to return the rows in any order. Hence, if reordering the roes will make the execution faster, it will do that and suddenly the two won't have the same order.

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

nguyennd SSCommitted Points: 1881 More actions · Answer 5

CREATE TABLE TEMPTEST

(

INT NOT NULL,

FIELDA INT NOT NULL,

CONSTRAINT [PK_TEMP] PRIMARY KEY CLUSTERED

(

ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

);

INSERT INTO TEMPTEST (,FIELDA) VALUES

(1,11),

(3,22),

(5,33),

(7,44),

(9,55),

(2,66),

(4,77),

(6,88),

(8,99),

(10,100);

SELECT * FROM TEMPTEST

SELECT * FROM TEMPTEST ORDER BY

I don't understand the result always is:

KEYFIELDA
111
266
322
477
533
688
744
899
955
10100

http://unitech.vn

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

In this very small, very specific case, yes. However order is not guaranteed unless you specify an Order By. The SQL query processor is allowed to change the rows as necessary unless there is an order by clause in the query.

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

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 7

Hi nguyennd,

The answer is simple. you do have a clustered index(Primary Key) on the key column. SQL will store data based on this column.

If i have to alter your create table Statement and create the primary key on the 'FiledA' column, when running the same select, the data will be sorted based on FieldA.

try this

create TABLE TEMPTEST

(

INT NOT NULL,

FIELDA INT NOT NULL,

CONSTRAINT [PK_TEMP] PRIMARY KEY CLUSTERED

(

FIELDA ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

);

INSERT INTO TEMPTEST (,FIELDA) VALUES

(1,11),

(3,22),

(5,33),

(7,44),

(9,55),

(2,66),

(4,77),

(6,88),

(8,99),

(10,100);

SELECT * FROM TEMPTEST

Your result should equate to this

KEYFIELDA

111

322

533

744

955

266

477

688

899

10100

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

shanu.hoosen (1/18/2010)
The answer is simple. you do have a clustered index(Primary Key) on the key column. SQL will store data based on this column.
If i have to alter your create table Statement and create the primary key on the 'FiledA' column, when running the same select, the data will be sorted based on FieldA.

That is not guaranteed. While it may often occur that data is returned in the order of the clustered index, it is not guaranteed in any way and there are a number of things that will cause the order of returned rows to differ.

If order is necessary in a result set, an ORDER BY clause must be specified. If not, then no assumptions can be made about the order of the returned rows.

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

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 9

nguyennd (1/14/2010)
But 2 result set are same. Why?

True,

I was just answering nguyennd's question above:-)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Just in case you didn't read the 3 previous correct answers by Gail.

Returned data order is NOT garanteed if you DON'T use order by.

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 11

I agree, thus the "True" below,

shanu.hoosen (1/18/2010)
nguyennd (1/14/2010)
But 2 result set are same. Why?
True,
I was just answering nguyennd's question above:-)

Again i was answering the 'why is the data set the same question'

talk about frustration

nguyennd SSCommitted Points: 1881 More actions · Answer 12

I understand about this case, thank all.

http://unitech.vn