Is it possible for the data to be returned in different column order between execution when you run SELECT * FROM multiple table joins multiple times?

Question

For example I have the following tables resulting from:

CREATE TABLE A (Id int, BId int, Number int)
CREATE TABLE B (Id int, Number decimal(10,2))
GO
INSERT INTO A VALUES(1, 3, 10)
INSERT INTO B VALUES(3, 50)
INSERT INTO A VALUES(2, 5, 20)
INSERT INTO B VALUES(5, 671.35)
GO

And I run the following query multiple times:

SELECT * FROM A INNER JOIN B ON A.BId = B.Id

I should get something like:

ID  BId  Number  ID  Number
 1    3      10   3   50.00
 2    5      20   5  671.35

But is it possible for A.Number and column B.Number be in different position (also ID in that respect) so I'll get something like:

ID  Number  ID  BId  Number
 3   50.00   1    3      10
 5  671.35   2    5      20

We are currently experiencing some weird problem that might be resulting from something like this. We have an ASP.NET application, executing a custom reflection based code generated data mapper that is connecting to SQL Server 2008 cluster.

We found sometimes that we get an error like so:

Object of type 'System.Decimal' cannot be converted to type 'System.Int32'

Trying to figure out if this is a behaviour in SQL Server or it's something in the reflection based data mapper engine.

As you can see the field names are the same in the two tables. Thinking perhaps when we tried to do DataReader.GetValue(DataReader.GetOrdinal("Number")), it will return B.Number which is a decimal instead of A.Number which is an int.

To complicate the matter further, this only happen intermittently (but consistently after it happened once on a particular IIS web server in a web farm only).

Say Web Server A is doing okay up until 2:00pm and suddenly, we got this error and we'll keep getting that error on Web Server A until we reset IIS on that server and then it will be okay again.

Something to do w/ connection pooling and how SQL query plan cache perhaps?

Answer 1

I don't think I've ever seen it swap the order, but personally I don't use "SELECT *" in production code, either:

SELECT A.[Id], A.[Bid], A.[Number], B.[Id], b.[Number]
FROM A INNER JOIN B ON A.[BId] = B.[Id]

Or at worst:

SELECT A.*, B.* FROM A INNER JOIN B ON A.[BId] = B.[Id]

Answer 2

SQL is a relational algebra - the standard does not specify what order columns will be returned in if you don't explicitly state the order yourself.

I tend to avoid "select *" as much as possible since it can clog up the network with unnecessary traffic and makes it harder to catch things like column renames and ordering until it's too late.

Just select the columns you actually need.

For your specific case, I would also just return the shared ID once since it has to be equal due to your join (I tend to prefer the "old" style as the DBMS should be smart enough to optimize this to an inner join anyway):

select
    a.Id as Id,
    a.BId as BId,
    a.Number as Number,
    b.Number as BNumber
from
    a, b
where
    a.BId = b.Id

Answer 3

The second scenario is possible only if you interchange table orders.

Something like SELECT * FROM B INNER JOIN A ON A.BId = B.Id.

Otherwise its not possible.

Answer 4

The second scenario is possible only if you interchange table orders.

Order of joins has nothing to do with position of columns, except if you use simple SELECT * which is not recommended.

Even in that case you can use

SELECT B., A. without changing order of joins

The best and recommended solution is to put column names instead of * (as posted in 1st two answers)

-- added later

I forgot one more thing I wanted to point on: Use column aliases for the columns with the same names e.g.

Select A.Number as NumberA, B.Number as NumberB