Sql server 2008 idiosyncrasy

[fullyautomatix]

This SQL works in SQL 2000

SELECT LogId, * FROM Logs
Order by LogId DESC

This fails in SQL 2008 with the error message

Ambiguous column name 'LogId'.

How ridiculous ? Luckily not affected too much but this could have easily meant a big migration headache.

 

[PerlOfWisdom]

This SQL works in SQL 2000

SELECT LogId, * FROM Logs
Order by LogId DESC

This fails in SQL 2008 with the error message

Ambiguous column name 'LogId'.

How ridiculous ? Luckily not affected too much but this could have easily meant a big migration headache.

You're selecting [LogID] twice (once in the *) so it doesn't know which one to sort by.

 

[lilelvis2000]

You're selecting [LogID] twice (once in the *) so it doesn't know which one to sort by.

Correct. Either alias the column or use Orderby 1 desc

Assuming that does work T-SQL.

 

[Spacecadet]

Assuming that does work T-SQL.

It does

This SQL works in SQL 2000

SELECT LogId, * FROM Logs
Order by LogId DESC

This fails in SQL 2008 with the error message

Ambiguous column name 'LogId'.

How ridiculous ? Luckily not affected too much but this could have easily meant a big migration headache.

This is another reason why "select *" should NOT be used anywhere near production! Microsoft themselves advise against it.

 

[VectraMan]

This is another reason why "select *" should NOT be used anywhere near production! Microsoft themselves advise against it.

Surely there's nothing wrong with the "select *". It's using select * and a column by name that doesn't make any sense and shouldn't suprise anyone that the behaviour might vary. Even I know that with my very limited SQL ability.

 

[kandr]

This SQL works in SQL 2000

SELECT LogId, * FROM Logs
Order by LogId DESC

This fails in SQL 2008 with the error message

Ambiguous column name 'LogId'.

How ridiculous ? Luckily not affected too much but this could have easily meant a big migration headache.

The SELECT LogId is redundant as you are doing a SELECT *.

 

[Spacecadet]

Surely there's nothing wrong with the "select *". It's using select * and a column by name that doesn't make any sense and shouldn't suprise anyone that the behaviour might vary. Even I know that with my very limited SQL ability.

problem with using select * is that if anything changes to the table you're selecting from then your SQL is still valid when it should more than likely be invalid.

 

[PerlOfWisdom]

The SELECT LogId is redundant as you are doing a SELECT *.

The reason for using it would be to see the whole table but with the field of interest on the left. This would never be used programatically.

 

[jmo21]

Using * can also cause a creep in performance degradation as more columns get added to a table over time.

 

[Zippy]

Using * can also cause a creep in performance degradation as more columns get added to a table over time.

It causes a performance degradation from the start. * is shorthand for 'look up what the table cols are in the system tables'.