March 27, 2012 at 9:49 am
davidandrews13 (3/27/2012)
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.
It appears to break out the * to the actual columns if you reopen the view in the graphic designer again.
But if you --> Right-click, View Script As, Create To, New Query Editor Window
you will see the * is part of the view's definition.
March 27, 2012 at 9:57 am
Stewart "Arturius" Campbell (3/27/2012)
Great question, Ronthanks
As to who creates views with "SELECT * FROM...":
we recently had a vendor app breaking due to schema changes to the underlying tables (an "upgrade" by the vendor), where the relevant forms use views to reflect data.
upon investigation, we found "SELECT *..." in almost all these views...
I see that kind of VIEW coding on a regular basis.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 27, 2012 at 9:59 am
Rose Bud (3/27/2012)
davidandrews13 (3/27/2012)
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.
It appears to break out the * to the actual columns if you reopen the view in the graphic designer again.
But if you --> Right-click, View Script As, Create To, New Query Editor Window
you will see the * is part of the view's definition.
i see. i created another view in the Designer as SELECT * and then Scripted it as Create To and it showed all the columns
i then created another view as a CREATE View statement as SELECT *, Scripted it as Create To and it shows SELECT *.
this is where i was getting mixed up.
i never doubted that SELECT * was bad practice, i just thought that Management Studio changed it for you.
thanks
March 27, 2012 at 10:45 am
cengland0 (3/27/2012)
You got me on this one. I didn't know this would produce an error and according to the current stats on who answered it incorrectly, I'm not the only one.
I'm honored to be in your company :-). FTR, in my limited experience, I had not seen another programmer's VIEW simply coded as a SELECT *, nor had I coded my own VIEWs that way (can someone explain what's the immediate purpose of a VIEW which only does a SELECT of one table's columns in the order they are defined?).
March 27, 2012 at 10:58 am
Nice question Ron! I have had to deal with this once or twice before. Why you would write a view that just performs a select * from a single table is beyond me. I have seen it done but never knew the point.
March 27, 2012 at 12:04 pm
Great question! It was worth the time to look at the examples and understand what really happens to data results from views when changes are made to underlying objects. Thanks 🙂
March 27, 2012 at 1:28 pm
The only two cases when I use "SELECT *" are:
1) Quick ad-hoc queries that will be used only once;
2) On the outer query when the inner queries already defined the columns to retrieve like below:
SELECT TB.*
FROM (SELECT NAME, OBJECT_ID
FROM SYS.OBJECTS
WHERE TYPE = 'P') AS TB;
But I didn't know about the issue with views. Another good reason to start modifying old inherited objects from the database.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
March 27, 2012 at 1:41 pm
A very interesting question -- thanks!
March 27, 2012 at 2:29 pm
Good back to basics question - do not use 'select *' in a view defination! Thanks.
March 27, 2012 at 4:13 pm
That's what I get for reading too fast and my brain read, "I deleted a row" instead of the correct "I deleted a column" statement. Oh well.
J
March 27, 2012 at 4:35 pm
Great question. I remember some time ago somebody said about a best practice he had about creating views using "SELECT *", but he had documentation on every database object and he would drop and recreate all views that depended on the modified table.
Personally I still prefer not to use the SELECT *.
"El" Jerry.
March 28, 2012 at 12:32 am
Nice question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 28, 2012 at 12:49 am
Hugo Kornelis (3/27/2012)
palotaiarpad (3/27/2012)
I'm a bit confused.BOL: If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.
What exactly are you confused about? This question is not at odds with the "unexpected results" quote in Books Online, as this error is just one of the possible unexpected results you can get; you only get this when the number of columns has been reduced.
I'm confused maybe due to my poore english. 😀
I wanted to solve the question - as usual - without running the code in SSMS.
I learned in my previous FoxPro sessions, that Select * is stored as Select field1,field2,field3... I thought, in SQL Server it should also give an error.
I turned myself to BOL and found the above sentence about it. For me unexpected result means, i'll have no error and the returned values are time to time different.
The question is great, i'll never forget the right answer, even if i'm not using * in views.
March 28, 2012 at 5:57 am
palotaiarpad (3/28/2012)
Hugo Kornelis (3/27/2012)
palotaiarpad (3/27/2012)
I'm a bit confused.BOL: If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.
What exactly are you confused about? This question is not at odds with the "unexpected results" quote in Books Online, as this error is just one of the possible unexpected results you can get; you only get this when the number of columns has been reduced.
I'm confused maybe due to my poore english. 😀
I wanted to solve the question - as usual - without running the code in SSMS.
I learned in my previous FoxPro sessions, that Select * is stored as Select field1,field2,field3... I thought, in SQL Server it should also give an error.
I turned myself to BOL and found the above sentence about it. For me unexpected result means, i'll have no error and the returned values are time to time different.
The question is great, i'll never forget the right answer, even if i'm not using * in views.
Easy enough to check how SQL Server stores the definition:
CREATE TABLE ViewTest
(
ID INT IDENTITY(1,1),
SomeText VARCHAR(12) DEFAULT 'abc'
)
GO
CREATE VIEW vTest As (SELECT * FROM ViewTest);
GO
--Following returns: CREATE VIEW vTest As (SELECT * FROM ViewTest)
sp_helptext vTest
--We even preserve the case of the view definition (we're running a CI collation here)
DROP VIEW vTest;
GO
CREATE VIEW vTest As (SELECT * fRoM vIeWtEsT);
GO
--Following returns: CREATE VIEW vTest As (SELECT * fRoM vIeWtEsT);
sp_helptext vTest
DROP VIEW vTest;
DROP TABLE ViewTest;
Rich
March 28, 2012 at 8:02 pm
Good question.
But sad to see that this is not generally regarded as a massive bug. If and when I write 'SELECT *' then I really mean 'SELECT *' at execution time, regardless of what was there at design time.
I think it's OK to strongly discourage the use of 'SELECT *' but resorting to the introduction and whitewashing of bugs to do so is a no-go as far as I am concerned. Disallow it completely or support it properly!
Viewing 15 posts - 31 through 45 (of 69 total)
You must be logged in to reply to this topic. Login to reply