1

Specifically I need to know if the query 

select * from [some_table]

will always return the columns in the same order.

I've seen no indication that it is non deterministic but I cannot assume this is true due to the specifications of my application. Can anyone point me at documentation one way or the other? I've had no luck with my searches.

Thanks in advance.

DocSmiley
  • 123
  • 2
  • 12

3 Answers3

0
SELECT * FROM [some_table]

returns always the same order of column in the same DB.

N.B. I assume you have two dbs First DB named DBA Second DB named DBB

In either DB exists a table TRIAL

In DBA TRIAL table has these fields in this order: id, name, surname

In DBB TRIAL table has these fields in this order: id, surname, name

When you execute 

SELECT * FROM DBA..TRIAL

you'll have id, name, surname

The same query on DBB will result: id, surname, name

Joe Taras
  • 15,166
  • 7
  • 42
  • 55
0

When using SELECT * the columns are returned in a) the order the tables appear in the FROM statement b) the order the columns appear in the table in the database.

From MSDN: "The columns are returned by table or view, as specified in the FROM clause, and in the order in which they exist in the table or view."

http://msdn.microsoft.com/en-us/library/ms176104.aspx

JeremiahDotNet
  • 910
  • 4
  • 9
0

It is deterministic as long as the schema of the database is not modified. Here is a example where the select * will change the order of the fields without changing the actual structure of the table:

Create table AAA
( 
   field1 varchar(10),
   field2 varchar(10),
   field3 varchar(10)
);

select * --> field1 ,field2 ,field3

Now you do

alter table AAA drop column field2;
alter table AAA add field2 varchar(10)

select * --> field1 ,field3 , field2

Basically, I would not count on the order of the fields and would definitely specify them in the select clause.

Skyp
  • 1,185
  • 11
  • 20