March 27, 2012 at 8:44 am
Carlo Romagnano (3/27/2012)
Easy question!Good practice is never use "SELECT * FROM" in view, too.
😉
surely it doesn't matter if you do SELECT *?
every time i create or update a view to '*' instead of naming each individual column name, it expands it out to the individual column names anyway.
and if you do want to bring back all columns, type * and let it expand out all the column names itself instead of manually typing them yourself.
March 27, 2012 at 8:45 am
Learned something new today. Thank you!
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
March 27, 2012 at 8:53 am
davidandrews13 (3/27/2012)
surely it doesn't matter if you do SELECT *?
It definitely matters. In the case of this question, you get an error either way. But in an earliers message, I posted a script where, after making some changes in the table, the view produces unexpected (no, a better way to word that is "incorrect") results, without giving an error or even warning message. Replace the * with a column-list in that code, and you'll get an error message. Now, getting an error unexpectedly after changing a table might suck - but not nearly as much as the damage that ccan be caused by a view returning incorrect results without you being alerted to it!
every time i create or update a view to '*' instead of naming each individual column name, it expands it out to the individual column names anyway.
That's not standard behaviour. Do you have Red Gate's SQLPrompt installed? Or another, similar product?
March 27, 2012 at 8:55 am
davidandrews13 (3/27/2012)
Carlo Romagnano (3/27/2012)
Easy question!Good practice is never use "SELECT * FROM" in view, too.
😉
surely it doesn't matter if you do SELECT *?
every time i create or update a view to '*' instead of naming each individual column name, it expands it out to the individual column names anyway.
and if you do want to bring back all columns, type * and let it expand out all the column names itself instead of manually typing them yourself.
Ah but if you drop a column from the underlying table, then SELECT * will not perform properly. So yes it does matter.
March 27, 2012 at 8:56 am
Hugo Kornelis (3/27/2012)
davidandrews13 (3/27/2012)
every time i create or update a view to '*' instead of naming each individual column name, it expands it out to the individual column names anyway.
That's not standard behaviour. Do you have Red Gate's SQLPrompt installed? Or another, similar product?
i dont, no.
to create a view I right-click teh 'Views' node in Object Explorer and click 'New View...'. I then click to 'Close' the dialog box that asks me to Add Table and type my SELECT statement directly into the pain. when Saving it (by clicking the blue save disk) it expands the * to name all columns.
that's the reason why i thought it didn't matter if you specified SELECT *, or not - because it always expanded it out for me.
EDIT: actually, going back to the actual question, they do specifically create the view by running a CREATE View statement. so in that situation, you shouldn't do SELECT *. i guess its just that i've never created views in that manner!
March 27, 2012 at 8:57 am
bitbucket-25253 (3/27/2012)
davidandrews13 (3/27/2012)
Carlo Romagnano (3/27/2012)
Easy question!Good practice is never use "SELECT * FROM" in view, too.
😉
surely it doesn't matter if you do SELECT *?
every time i create or update a view to '*' instead of naming each individual column name, it expands it out to the individual column names anyway.
and if you do want to bring back all columns, type * and let it expand out all the column names itself instead of manually typing them yourself.
Ah but if you drop a column from the underlying table, then SELECT * will not perform properly. So yes it does matter.
but if you say 'SELECT Col1,Col2 FROM Table' and then drop Col2, it will also error. 😉
March 27, 2012 at 9:02 am
davidandrews13 (3/27/2012)
i dont, no.to create a view I right-click teh 'Views' node in Object Explorer and click 'New View...'. I then click to 'Close' the dialog box that asks me to Add Table and type my SELECT statement directly into the pain. when Saving it (by clicking the blue save disk) it expands the * to name all columns.
Ah, you use the graphical view designer. That explains.
I never use that, so I didn't know how it handles the *. I never use that thing; it has other issues that are far worse than this one.
March 27, 2012 at 9:05 am
davidandrews13 (3/27/2012)
bitbucket-25253 (3/27/2012)
davidandrews13 (3/27/2012)
Carlo Romagnano (3/27/2012)
Easy question!Good practice is never use "SELECT * FROM" in view, too.
😉
surely it doesn't matter if you do SELECT *?
every time i create or update a view to '*' instead of naming each individual column name, it expands it out to the individual column names anyway.
and if you do want to bring back all columns, type * and let it expand out all the column names itself instead of manually typing them yourself.
Ah but if you drop a column from the underlying table, then SELECT * will not perform properly. So yes it does matter.
but if you say 'SELECT Col1,Col2 FROM Table' and then drop Col2, it will also error. 😉
Errors are not the problem.
Try what happens if you drop a column, then create another one. Or even drop the table, and then create a completely different table with the same name (with at least as many columns). You'll get very weird results.
March 27, 2012 at 9:19 am
Hugo Kornelis (3/27/2012)
davidandrews13 (3/27/2012)
bitbucket-25253 (3/27/2012)
davidandrews13 (3/27/2012)
Carlo Romagnano (3/27/2012)
Easy question!Good practice is never use "SELECT * FROM" in view, too.
😉
surely it doesn't matter if you do SELECT *?
every time i create or update a view to '*' instead of naming each individual column name, it expands it out to the individual column names anyway.
and if you do want to bring back all columns, type * and let it expand out all the column names itself instead of manually typing them yourself.
Ah but if you drop a column from the underlying table, then SELECT * will not perform properly. So yes it does matter.
but if you say 'SELECT Col1,Col2 FROM Table' and then drop Col2, it will also error. 😉
Errors are not the problem.
Try what happens if you drop a column, then create another one. Or even drop the table, and then create a completely different table with the same name (with at least as many columns). You'll get very weird results.
i decided to test this out fully.
i created my table with three columns Col1,Col2,Col3 and added 3 test rows in.
i then created a view that looks at that table like so
create view vTestTable_View as
select * from TestTable_View
i viewed the View by right clicking it in Object Explorer and selecting Design. It had already expanded it out to show each individual column (rather than showing SELECT *).
i ran a SELECT of the View and that worked.
select * from vTestTable_View
i then went to the Designer of the table and deleted Col3 and reran the above select on the View and it said:
View or function 'vTestTable_View' has more column names specified than columns defined.
the only *strange* thing i get is when i create another column, Col4, after deleting Col3. The view shows Col3 instead.
it seems my results differ from everyone elses expected results?
that is:
a. i create a view by using the CREATE VIEW As statement with a SELECT * and the view automatically expands out the column names.
b. i delete a column in my table and SELECT the View again and it gives me an error instead of strange results.
March 27, 2012 at 9:27 am
another good question Ron.
+1 for those not a fan of SELECT *
March 27, 2012 at 9:27 am
davidandrews13 (3/27/2012)
a. i create a view by using the CREATE VIEW As statement with a SELECT * and the view automatically expands out the column names.
That is, apparently, what happens if you use the graphical view designer. I guess that most people don't use that. I know I don't, and I advice anyone not to touch it with a 12" pole.
b. i delete a column in my table and SELECT the View again and it gives me an error instead of strange results.
That's what everyone gets. The strange results happen if, after making changes to the table, the number of columns is at least the same as it was when you created the view. In that case, data from the first n columns will be returned, but with the original column names.
If you go back to the first page of this topic, you'll see a message I posted that includes a fragment of SQL code. Copy it, paste it in SSMS, then hit the Execute button to see how this can cause dates to appear in numerical columns, strings in date columns, etc.
March 27, 2012 at 9:28 am
davidandrews13 (3/27/2012)
Hugo Kornelis (3/27/2012)
davidandrews13 (3/27/2012)
bitbucket-25253 (3/27/2012)
davidandrews13 (3/27/2012)
Carlo Romagnano (3/27/2012)
Easy question!Good practice is never use "SELECT * FROM" in view, too.
😉
surely it doesn't matter if you do SELECT *?
every time i create or update a view to '*' instead of naming each individual column name, it expands it out to the individual column names anyway.
and if you do want to bring back all columns, type * and let it expand out all the column names itself instead of manually typing them yourself.
Ah but if you drop a column from the underlying table, then SELECT * will not perform properly. So yes it does matter.
but if you say 'SELECT Col1,Col2 FROM Table' and then drop Col2, it will also error. 😉
Errors are not the problem.
Try what happens if you drop a column, then create another one. Or even drop the table, and then create a completely different table with the same name (with at least as many columns). You'll get very weird results.
i decided to test this out fully.
i created my table with three columns Col1,Col2,Col3 and added 3 test rows in.
i then created a view that looks at that table like so
create view vTestTable_View asselect * from TestTable_View
i viewed the View by right clicking it in Object Explorer and selecting Design. It had already expanded it out to show each individual column (rather than showing SELECT *).
i ran a SELECT of the View and that worked.
select * from vTestTable_Viewi then went to the Designer of the table and deleted Col3 and reran the above select on the View and it said:
View or function 'vTestTable_View' has more column names specified than columns defined.the only *strange* thing i get is when i create another column, Col4, after deleting Col3. The view shows Col3 instead.
it seems my results differ from everyone elses expected results?
that is:
a. i create a view by using the CREATE VIEW As statement with a SELECT * and the view automatically expands out the column names.
b. i delete a column in my table and SELECT the View again and it gives me an error instead of strange results.
Important to note. You are creating the view via SSMS and NOT a create view statement. I am not surprised that SSMS expands the actual sql to include the column names. This is further proof that select * is not a good choice. It is in fact such a bad idea that MS had to make their own graphical tool not use it on auto generated code.
The error you received is 100% inline with the point of this QOTD. I would expect that a column not being found could be considered unexpected results when selecting *.
Maybe the real lesson here is to write your views yourself.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2012 at 9:38 am
Sean Lange (3/27/2012)
davidandrews13 (3/27/2012)
Important to note. You are creating the view via SSMS and NOT a create view statement. I am not surprised that SSMS expands the actual sql to include the column names. This is further proof that select * is not a good choice. It is in fact such a bad idea that MS had to make their own graphical tool not use it on auto generated code.The error you received is 100% inline with the point of this QOTD. I would expect that a column not being found could be considered unexpected results when selecting *.
Maybe the real lesson here is to write your views yourself.
hi Sean.
i thought
create view vTestTable_View as
select * from TestTable_View
was a Create View Statement?
if i was then to right click the view that i had just created, by navigating to it in the object explorer, it shows the fully expanded column names.
thanks for your input.
March 27, 2012 at 9:40 am
Hugo Kornelis (3/27/2012)
davidandrews13 (3/27/2012)
a. i create a view by using the CREATE VIEW As statement with a SELECT * and the view automatically expands out the column names.That is, apparently, what happens if you use the graphical view designer. I guess that most people don't use that. I know I don't, and I advice anyone not to touch it with a 12" pole.
b. i delete a column in my table and SELECT the View again and it gives me an error instead of strange results.
That's what everyone gets. The strange results happen if, after making changes to the table, the number of columns is at least the same as it was when you created the view. In that case, data from the first n columns will be returned, but with the original column names.
If you go back to the first page of this topic, you'll see a message I posted that includes a fragment of SQL code. Copy it, paste it in SSMS, then hit the Execute button to see how this can cause dates to appear in numerical columns, strings in date columns, etc.
aah, i see what your saying now. its good to see this happening. i'll know for future reference.
thanks!
March 27, 2012 at 9:49 am
davidandrews13 (3/27/2012)
Sean Lange (3/27/2012)
davidandrews13 (3/27/2012)
Important to note. You are creating the view via SSMS and NOT a create view statement. I am not surprised that SSMS expands the actual sql to include the column names. This is further proof that select * is not a good choice. It is in fact such a bad idea that MS had to make their own graphical tool not use it on auto generated code.The error you received is 100% inline with the point of this QOTD. I would expect that a column not being found could be considered unexpected results when selecting *.
Maybe the real lesson here is to write your views yourself.
hi Sean.
i thought
create view vTestTable_View asselect * from TestTable_View
was a Create View Statement?
if i was then to right click the view that i had just created, by navigating to it in the object explorer, it shows the fully expanded column names.
thanks for your input.
I thought you said you were using the visual tool to create your view. I just tried creating a view as select *. When navigating to the object in object explorer it does show all the columns. However, if you script the create statement it is still select *.
The discussion about select * has been going on for a long time. It is considered best practice by nearly every single person you talk to in the sql universe. There are so many tools available for making it easy to select specific columns.
Here is just one that is built right in to SSMS which I suspect is where you write most of your views. In object explorer you can expand a table and there is a columns folder. If you drag that folder to a query it will automatically generate a perfectly formatted comma separated list of all column names. It is then really simple to just remove the column(s) you don't want/need.
The only person that benefits from select * is the original author of the query. It WILL break at some point in the future. It takes only an extra second or so to get a fully qualified list.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 69 total)
You must be logged in to reply to this topic. Login to reply