SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / Updating Views / Latest Posts

RE: Updating Views

sqlcentral-bugmenot — Tue, 26 Feb 2013 06:38:33 GMT

Really good one! Didn't know about the SP but got it right through exclusion- of course, the only really correct answer wasn't offered.[size="-3"](drop this badly designed view and fire any dev that complains about his app crashing as a result)[/size]

RE: Updating Views

opc.three — Mon, 11 Feb 2013 07:08:39 GMT

Nice question.

RE: Updating Views

kapfundestanley — Mon, 11 Feb 2013 03:46:24 GMT

Nince question,Thank you Steve.

RE: Updating Views

mmartin1 — Thu, 07 Feb 2013 15:55:47 GMT

I've had to correct views I've inherited that used select * and returned many unneeded columns. A quick and dirty way to correct them is to right click a view, select Open, select the 'show sql pane' button in the toolbar, and add to the displayed query WHERE 1=0 (or something of the like). I click the exclamation point and the resulting sql query string now explicitly shows all columns. You can now copy this and alter your view in a normal ssms window. It is a time saver when the view returns tens of columns.

RE: Updating Views

KWymore — Thu, 07 Feb 2013 13:25:20 GMT

[quote][b]L' Eomot Inversé (2/7/2013)[/b][hr][quote][b]EL Jerry (2/7/2013)[/b][hr]Got it. Here it is:[url]http://www.sqlservercentral.com/Forums/FindPost1349883.aspx[/url]Saludos,[/quote]Wow, that is a humorous thread! I am glad I don't work with that lead. Talk about misdirection...

RE: Updating Views

L' Eomot Inversé — Thu, 07 Feb 2013 13:00:28 GMT

[quote][b]EL Jerry (2/7/2013)[/b][hr]Got it. Here it is:[url]http://www.sqlservercentral.com/Forums/FindPost1349883.aspx[/url]Saludos,[/quote]That is a version with some problems (misplaced '[' and ']', misplaced 'with scheamabinding' clause, using [] on identifiers in drop but not on create) which are easily fixed. I don't know whether Barry posted a corrected version later.It was an amazing thread - I pity the OP having to put up with a lead who bases his decisions on a vivid imagination with a strong inclination towards fiction.

RE: Updating Views

EL Jerry — Thu, 07 Feb 2013 10:51:13 GMT

Got it. Here it is:[url]http://www.sqlservercentral.com/Forums/FindPost1349883.aspx[/url]Saludos,

RE: Updating Views

EL Jerry — Thu, 07 Feb 2013 10:43:27 GMT

Nice question, Steve. I knew the answer right away from previous posts in the forums.By the way, I remember RBarry Young posted some code that he used to run overnight in order to rebuild all views. The code looked interesting and it made sure when a table was altered its views would be accurate next day. However, I'm unable to find the post. I will do some further search and post the link to it when I find it.Saludos,

RE: Updating Views

Steve Jones - SSC Editor — Thu, 07 Feb 2013 10:22:04 GMT

[quote][b]alex.d.garland (2/7/2013)[/b][hr][quote][b]Hugo Kornelis (2/7/2013)[/b][hr][quote][b]paul s-306273 (2/7/2013)[/b][hr]Steve - 0 points to you for creating a view using 'select *'...:-P[/quote]So how should Steve have made a QotD about the dangers of select * in a view without using select * ???[/quote]+1 - but I note that if a warning about using "SELECT *..." had been added to the explanation, this would have been even more helpful.[/quote]Fair point and added.

RE: Updating Views

Miles Neale — Thu, 07 Feb 2013 10:03:07 GMT

[quote][b]Hugo Kornelis (2/7/2013)[/b][hr][quote][b]paul s-306273 (2/7/2013)[/b][hr]Steve - 0 points to you for creating a view using 'select *'...:-P[/quote]So how should Steve have made a QotD about the dangers of select * in a view without using select * ???[/quote]Good question, great discussion, and learned from both. Hugo has a point you cannot illustrate the problems and pitfalls without illustrating the problem or pitfall. Our teachers use to say "Don't ever say lookin, say looking! " And then some smarty would say "But you just said lookin, why can't we?":-)

RE: Updating Views

KWymore — Thu, 07 Feb 2013 10:00:28 GMT

Good question, thanks Steve. Definitely highlights an important point when using views.

RE: Updating Views

SQLRNNR — Thu, 07 Feb 2013 08:58:35 GMT

Thanks Steve

RE: Updating Views

jagpatel9 — Thu, 07 Feb 2013 07:52:25 GMT

Nice 1 Steve. However, I don't recommend anyone to use SELECT * FROM in view.

RE: Updating Views

sestell1 — Thu, 07 Feb 2013 07:26:53 GMT

Good question!I wondered about an ALTER VIEW statement being there as well since altering the view inherently refreshes it, but then recalled that *'s aren't allowed in schema-bound objects. Nice!

RE: Updating Views

mtassin — Thu, 07 Feb 2013 07:02:54 GMT

Good question, I learned something.Generally I don't ever write views with SELECT * in them.but even if I did... If I had to refresh the view, I'd typically just alter the view and leave the code as is... that works too, or did... I haven't written a view with SELECT * in it since before SQL 2005

RE: Updating Views

(Bob Brown) — Thu, 07 Feb 2013 05:56:34 GMT

Good question. Thanks.

RE: Updating Views

Stewart "Arturius" Campbell — Thu, 07 Feb 2013 04:28:56 GMT

Good question, Steve, thanks

RE: Updating Views

Raghavendra Mudugal — Thu, 07 Feb 2013 03:17:30 GMT

RE: Updating Views

alex.d.garland — Thu, 07 Feb 2013 03:14:33 GMT

[quote][b]Hugo Kornelis (2/7/2013)[/b][hr][quote][b]paul s-306273 (2/7/2013)[/b][hr]Steve - 0 points to you for creating a view using 'select *'...:-P[/quote]So how should Steve have made a QotD about the dangers of select * in a view without using select * ???[/quote]+1 - but I note that if a warning about using "SELECT *..." had been added to the explanation, this would have been even more helpful.

RE: Updating Views

Hugo Kornelis — Thu, 07 Feb 2013 02:14:00 GMT

[quote][b]paul s-306273 (2/7/2013)[/b][hr]Steve - 0 points to you for creating a view using 'select *'...:-P[/quote]So how should Steve have made a QotD about the dangers of select * in a view without using select * ???

RE: Updating Views

Raghavendra Mudugal — Thu, 07 Feb 2013 02:04:46 GMT

Awesome QtoD, thank you Steve.(was thinking if the 4th option will come true in future versions, would be cool, and then thought again.. setting auto to everything is not a acceptable way to go, but it would be a nice feature and a lot to consider before really using itUsing "schemabinding" and "select *" ... I do not feel comfortable using it.. i create VIEW with the specifying column names, and if needed, then alter the view to suit the new needs and modify the code to the new adjustments. Earlier i had views with select * in one of the web projects and then later I changed them to the column specifics.. and by doing that whenever it is needed I kind of gained control over the objects by knowing which object needs modification and which doesn't and i was knowing each and every VIEW object which are the tables it refers.)

RE: Updating Views

Stuart Davies — Thu, 07 Feb 2013 02:02:28 GMT

[quote][b]paul s-306273 (2/7/2013)[/b][hr]Steve - 0 points to you for creating a view using 'select *'...:-P[/quote]+1 - Apart from that - good question :-D

RE: Updating Views

Hugo Kornelis — Thu, 07 Feb 2013 01:42:25 GMT

Good question, Steve!Here is a link to the most recent version of the relevant Books Online article: [url=http://msdn.microsoft.com/en-us/library/ms187821.aspx]http://msdn.microsoft.com/en-us/library/ms187821.aspx[/url]. It's the same aas the one in the explantion, but without the explicit version number added. (Why do people so often use links to older versions when the question is not about that older version? Why????)The true danger of using SELECT * in a view becomes apparent if, after adding that new column, the original column is then dropped. The view will still return data, still use the name of the old (no longer existing) column, but return data from the new column.

RE: Updating Views

paul s-306273 — Thu, 07 Feb 2013 01:28:05 GMT

Steve - 0 points to you for creating a view using 'select *'...:-P

RE: Updating Views

Dineshbabu — Thu, 07 Feb 2013 01:03:24 GMT

Please some one list the limitations of schema binding a view.

RE: Updating Views

Dineshbabu — Thu, 07 Feb 2013 01:02:20 GMT

Hi mani,Thanks for spending your time to explain about schema Binding. [quote][b]manik123 (2/7/2013)[/b][hr]If MyView is schema bind no one will be able to alter the dbo.MyTable table[b] unless they drop the table[/b]. [/quote]If a view is schema bound, even dropping the table is also not possible. Either you need to drop the view or you need to remove schema binding. You can just modify the columns which is not included in view definition.[quote][b]manik123 (2/7/2013)[/b][hr]Just think that someone drops/alters the table dbo.MyTable without paying any heed to our view. Now that would leave our view nowhere. Hence schema bind it, this will prevent any such accidents from happening. [/quote]There are few limitations in schema binding like all the objects should be within the same database. You cannot use synonyms in view definitions etc...

RE: Updating Views

demonfox — Thu, 07 Feb 2013 00:36:04 GMT

[quote][b]Lokesh Vij (2/6/2013)[/b][hr]Nice question Steve![/quote]+1Thanks for the question.......

RE: Updating Views

Dineshbabu — Thu, 07 Feb 2013 00:23:07 GMT

Nice and easy one one. I got a second thought whether there is any database level set option to make a view referesh automatically.. finally I ensured there is no such option..

RE: Updating Views

manik123 — Thu, 07 Feb 2013 00:21:24 GMT

nice quetion... EXEC sp_refreshview MyView is correct answer. but what is the use of 'ALTER VIEW MyView ADD SCHEMABINDING'. Schema binding binds your views to the dependent physical columns of the accessed tables specified in the contents of the view, i.e. if MyView is schema bind no one will be able to alter the dbo.MyTable table unless they drop the table. Just think that someone drops/alters the table dbo.MyTable without paying any heed to our view. Now that would leave our view nowhere. Hence schema bind it, this will prevent any such accidents from happening. ManikGo fast as possible

RE: Updating Views

Koen Verbeeck — Wed, 06 Feb 2013 23:19:04 GMT

Nice question Steve, thanks.

RE: Updating Views

Lokesh Vij — Wed, 06 Feb 2013 21:35:58 GMT

[quote][b]bitbucket-25253 (2/6/2013)[/b][hr]Nice question .... but Steve had I know it was you who created the QOD would still having me wondering what the answer might be.[/quote]+1Definitely, I would have also thought that there is some kind of gotcha :-)

RE: Updating Views

Lokesh Vij — Wed, 06 Feb 2013 21:34:22 GMT

Nice question Steve!

RE: Updating Views

bitbucket-25253 — Wed, 06 Feb 2013 20:57:59 GMT

Nice question .... but Steve had I know it was you who created the QOD would still having me wondering what the answer might be.

Updating Views

Steve Jones - SSC Editor — Wed, 06 Feb 2013 20:56:08 GMT

Comments posted to this topic are about the item [B]Updating Views[/B]