May 27, 2013 at 7:13 am
Hi all
Just a quick one. As we all know a view is a set of columns from a table we want non-admins to see.
Now I am having a strange problem. When I make an amendment to a table (structural wise), the view breaks.
All my view are written as "select * from"...and idea's why this happens?
May 27, 2013 at 7:19 am
That is because SELECT * does not automatically refresh as the underlying table(s) are modified.
IF the views aren't schema bound you should run sp_refreshview 'viewname' for each view that needs to updated.
May 27, 2013 at 7:22 am
When using "SELECT *" in a view the metadata is not automaticly updated when the related table(s) are altered. Use "sp_refreshview" to update the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.
Syntax
--------------------------------------------------------------------------------
sp_refreshview [ @viewname = ] 'viewname'
EDITED:
Sigh, I need to typ faster. Lynn beat me to it.
May 27, 2013 at 7:48 am
thanks all!!!
May 28, 2013 at 7:43 am
Yet another reason NOT to use select *. 😛 I have answered this exact same question at work twice in the last week.
_______________________________________________________________
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/
May 28, 2013 at 8:08 am
Sean Lange (5/28/2013)
Yet another reason NOT to use select *. 😛 I have answered this exact same question at work twice in the last week.
I have to agree on NOT using SELECT * in views. Also, it may be a good reason to schema bind views to prevent changes that may affect views that already exist.
May 28, 2013 at 8:27 am
Lynn Pettis (5/28/2013)
Sean Lange (5/28/2013)
Yet another reason NOT to use select *. 😛 I have answered this exact same question at work twice in the last week.I have to agree on NOT using SELECT * in views. Also, it may be a good reason to schema bind views to prevent changes that may affect views that already exist.
Of course I mean not using select * anywhere, but I know that I am preaching to the choir here. 😉
I agree that schema binding your views can be a really good idea so you don't get bitten by unexpected changes to the underlying table.
_______________________________________________________________
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/
May 28, 2013 at 8:31 am
Sean Lange (5/28/2013)
Lynn Pettis (5/28/2013)
Sean Lange (5/28/2013)
Yet another reason NOT to use select *. 😛 I have answered this exact same question at work twice in the last week.I have to agree on NOT using SELECT * in views. Also, it may be a good reason to schema bind views to prevent changes that may affect views that already exist.
Of course I mean not using select * anywhere, but I know that I am preaching to the choir here. 😉
I agree that schema binding your views can be a really good idea so you don't get bitten by unexpected changes to the underlying table.
I have actually seen a few places where I don't mind the SELECT * being used. I have seen it where all the columns are defined in the subquery or OPENQUERY and the SELECT * is selecting everything from there. Seems redundant to specify the column names twice there.
May 28, 2013 at 8:34 am
Lynn Pettis (5/28/2013)
Sean Lange (5/28/2013)
Lynn Pettis (5/28/2013)
Sean Lange (5/28/2013)
Yet another reason NOT to use select *. 😛 I have answered this exact same question at work twice in the last week.I have to agree on NOT using SELECT * in views. Also, it may be a good reason to schema bind views to prevent changes that may affect views that already exist.
Of course I mean not using select * anywhere, but I know that I am preaching to the choir here. 😉
I agree that schema binding your views can be a really good idea so you don't get bitten by unexpected changes to the underlying table.
I have actually seen a few places where I don't mind the SELECT * being used. I have seen it where all the columns are defined in the subquery or OPENQUERY and the SELECT * is selecting everything from there. Seems redundant to specify the column names twice there.
Fair enough...but certainly the exception to the rule. As with anything in sql server there are no absolutes, there is always a case where what is normally considered taboo is the best approach.
_______________________________________________________________
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/
May 28, 2013 at 8:36 am
Sean Lange (5/28/2013)
Lynn Pettis (5/28/2013)
Sean Lange (5/28/2013)
Lynn Pettis (5/28/2013)
Sean Lange (5/28/2013)
Yet another reason NOT to use select *. 😛 I have answered this exact same question at work twice in the last week.I have to agree on NOT using SELECT * in views. Also, it may be a good reason to schema bind views to prevent changes that may affect views that already exist.
Of course I mean not using select * anywhere, but I know that I am preaching to the choir here. 😉
I agree that schema binding your views can be a really good idea so you don't get bitten by unexpected changes to the underlying table.
I have actually seen a few places where I don't mind the SELECT * being used. I have seen it where all the columns are defined in the subquery or OPENQUERY and the SELECT * is selecting everything from there. Seems redundant to specify the column names twice there.
Fair enough...but certainly the exception to the rule. As with anything in sql server there are no absolutes, there is always a case where what is normally considered taboo is the best approach.
Trust me, I am right there with you. We are in the same choir after all.
May 28, 2013 at 11:37 am
How about in a WHERE EXISTS clause?
According the the Querying SQL2012 exam prep book, a SELECT * inside an EXISTS clause returns true or false only - not all all the columns - so there is no penalty in not spelling out a column name.
May 29, 2013 at 6:46 am
dan-572483 (5/28/2013)
How about in a WHERE EXISTS clause?According the the Querying SQL2012 exam prep book, a SELECT * inside an EXISTS clause returns true or false only - not all all the columns - so there is no penalty in not spelling out a column name.
you have to use sp_refreshview
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 29, 2013 at 7:16 am
dan-572483 (5/28/2013)
How about in a WHERE EXISTS clause?According the the Querying SQL2012 exam prep book, a SELECT * inside an EXISTS clause returns true or false only - not all all the columns - so there is no penalty in not spelling out a column name.
That is because inside an exists you are not selecting the data you are only checking for the existence. Of course using * in that scenario is acceptable and the most common.
_______________________________________________________________
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/
May 30, 2013 at 1:22 am
Sean Lange (5/29/2013)
dan-572483 (5/28/2013)
How about in a WHERE EXISTS clause?According the the Querying SQL2012 exam prep book, a SELECT * inside an EXISTS clause returns true or false only - not all all the columns - so there is no penalty in not spelling out a column name.
That is because inside an exists you are not selecting the data you are only checking for the existence. Of course using * in that scenario is acceptable and the most common.
I got beaten enough with the "SELECT *" stick that as a nature of habit, my WHERE EXISTS now contain (SELECT 1 FROM ...) 😛
May 30, 2013 at 8:49 am
rrn 62873 (5/30/2013)
Sean Lange (5/29/2013)
dan-572483 (5/28/2013)
How about in a WHERE EXISTS clause?According the the Querying SQL2012 exam prep book, a SELECT * inside an EXISTS clause returns true or false only - not all all the columns - so there is no penalty in not spelling out a column name.
That is because inside an exists you are not selecting the data you are only checking for the existence. Of course using * in that scenario is acceptable and the most common.
I got beaten enough with the "SELECT *" stick that as a nature of habit, my WHERE EXISTS now contain (SELECT 1 FROM ...) 😛
Actually, mine tend to be the same (EXISTS(SELECT 1 FROM ...)).
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply