Sql server 2008 idiosyncrasy

[original PM]

oh yeah absolutley no qualms about it

I only really use it to generate a query I then thrown into report builder 2 to create an .rdl file so the user can run the query to their hearts content.

I am not a developer so only really do this sort of stuff as a side line!

[Spacecadet]

If you are a bit of an SQL noob (like me) it is often easier to use the design query in editor option

You do realise you're shooting yourself in the foot using that and its obvious to anyone else how you generated the SQL.

[rsingh]

never managed to get my head around grouping yet though.

Patience young grasshopper...

[original PM]

If you are a bit of an SQL noob (like me) it is often easier to use the design query in editor option

then you can select exactly the fields you want from each table in the order you want - also you can get the joins sorted by just dragging a line between the primary key on the relevant tables..

and also easy to set parameters and other things


never managed to get my head around grouping yet though.

[Sysman]

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.

Correct, but if the order of the columns is important you should really specify each by name.

As others have suggested.

[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'.

[jmo21]

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

[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.

[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.

[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 *.

[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.

[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.

[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.

[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.

[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.