SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Bit Bad
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

SamC
White Water Yakist

USA
3461 Posts

Posted - 06/07/2004 :  16:25:16  Show Profile  Reply with Quote
Hmmm... Did you know that using EM to change the order of columns in a table does not cause the views referencing that table to recompile?

Just imagine the damage.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 06/07/2004 :  16:32:48  Show Profile  Reply with Quote
OK...speak slowly so I can understand...

Why is that a problem?

And why are you doing this in EM anyway?

How much data?



Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36851 Posts

Posted - 06/07/2004 :  16:36:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why would the view be recompiled? Why would the ordering of columns in a table affect a view? Can a view even be recompiled? Isn't that only for stored procedures and triggers?

Modifying a table in EM is pretty much the same as modifying it in QA. Take a look at the change script. Almost always, EM generates the same code that you would use in QA. I'll often have EM generate the code to save time typing, I'll then copy it into QA and make changes as needed.

Tara
Go to Top of Page

SamC
White Water Yakist

USA
3461 Posts

Posted - 06/07/2004 :  16:38:57  Show Profile  Reply with Quote
Using EM's DESIGN TABLE feature, you can change the order of columns. Why would you want to do that ? I wanted the frequently viewed columns to display on the left of the window in QA.

Anyway, it seems the VIEW has to be dropped and recreated as the VIEW wasn't aware the columns had moved. This particular VIEW actually displayed an IDENTITY key in the EMail field.

Depending on the usage of the VIEW, things could get very dirty (or disasterous).

Sam
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36851 Posts

Posted - 06/07/2004 :  16:41:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
Are you using SELECT * in the view?

Tara
Go to Top of Page

SamC
White Water Yakist

USA
3461 Posts

Posted - 06/07/2004 :  16:46:41  Show Profile  Reply with Quote
Tara: yes. But the error manifested itself using SPs which don't use SELECT *.

Sam

ed: Argh!

Edited by - SamC on 06/07/2004 17:01:12
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36851 Posts

Posted - 06/07/2004 :  16:47:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by SamC

Tara: yes. But I the error manifested itself using SPs which don't use SELECT *.

Sam





Tara
Go to Top of Page

SamC
White Water Yakist

USA
3461 Posts

Posted - 06/07/2004 :  17:01:44  Show Profile  Reply with Quote
Anyone have a SP that'll tag all views to be recompiled? (hopeful look)

Sam
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36851 Posts

Posted - 06/07/2004 :  17:06:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why the use of SELECT * in views?

Tara
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 06/07/2004 :  17:13:52  Show Profile  Reply with Quote
You could run sp_recompile tablename to cause the views using the table to be recompiled.

I believe views are compiled the first time they are referenced after sql server is started. SP's are compiled the first time they are executed and the same query plan is used from that point forward.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36851 Posts

Posted - 06/07/2004 :  17:16:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
From BOL:

sp_recompile
Causes stored procedures and triggers to be recompiled the next time they are run.



Tara
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 06/07/2004 :  17:18:00  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
I believe views are compiled the first time they are referenced after sql server is started. SP's are compiled the first time they are executed and the same query plan is used from that point forward.

ummm, nope. :)

There are all kinds of things that cause procedures to recompile (temp tables, cursors, dynamic SQL, updated statatistics, significantly different operations, etc. etc.). It's quite the fun list. You also might have things compile a plan because plans are getting pushed out of the cache too quickly.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 06/07/2004 :  17:25:05  Show Profile  Reply with Quote
That's interesting. It's been years since I researched it. I recall "bad" plans being generated due to the first set of paramaters passed to a SP. I knew updateing statistics invalidates query plans but I didn't know cache had anything do do with it.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 06/07/2004 :  19:05:10  Show Profile  Reply with Quote
The real problem with using SELECT * in a view is if you change the underlying tables. You could add a column for example, and that would not show up when you select from your view.

Do as Tara says and get rid of the SELECT *



-ec
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/07/2004 :  19:23:32  Show Profile  Reply with Quote
That's a worst practice there Sam.. Never use * in a view... You might get away with it if you add SCHEMABINDING..

In my current shop, view creation must be in the ANSI-Standard form...


CREATE VIEW ViewDetails (Column1, Column2)
AS
SELECT Column1 , Column2 from ATable


When a view is created, all table and column references are resolved and placed in the underlying system tables. When it is first executed, the query tree is placed in the cache, but it is recompiled at every execution.. That is why I like them so much! Optimised plan each time...

As an example, we have a type-subtype schema in one our DB's.. A view (A join between the type and subtype) for each subtype is created.. When you select columns from the view that reference only the sub-type table, the view is recompiled and ignores the base table, and thus eliminates the JOIN... Just beautiful.....



DavidM

"Always pre-heat the oven"
Go to Top of Page

SamC
White Water Yakist

USA
3461 Posts

Posted - 06/07/2004 :  20:29:10  Show Profile  Reply with Quote
Reread my post. I wasn't using SELECT * anywhere in production code VIEWs or SPs. SELECT * was a test of the VIEW I executed in QA.

Anyway, I tried to reproduce this problem in NorthWind with no luck.

Nuts.

Sam
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 06/07/2004 :  20:34:38  Show Profile  Reply with Quote
I did..

Tara: Are you using SELECT * in the view?
SamC: yes.



DavidM

"Always pre-heat the oven"
Go to Top of Page

SamC
White Water Yakist

USA
3461 Posts

Posted - 06/07/2004 :  20:46:36  Show Profile  Reply with Quote
quote:
Originally posted by byrmol

I did..

Tara: Are you using SELECT * in the view?
SamC: yes.
Could you be more cryptic?

How did you reproduce the problem?

And yes to what?

Sam
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 06/08/2004 :  07:54:09  Show Profile  Reply with Quote
Yes:....as in what you wrote above (6th reply from the top)?

"Tara: yes. But the error manifested itself using SPs which don't use SELECT *."
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/08/2004 :  09:13:53  Show Profile  Reply with Quote
quote:
Originally posted by byrmol

I did..

Tara: Are you using SELECT * in the view?
SamC: yes.



DavidM

"Always pre-heat the oven"





I too...(reread the thread Sam)

Come on David 6 more....

Now you haven't spoken slowly enough for me to understand why this is a problem....




Brett

8-)
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 06/08/2004 :  11:42:49  Show Profile  Reply with Quote
Here is a test case that shows why 'SELECT *' in a view is a bad thing.

1. Create the table and then seed some data

create table foo ([id] int identity, value1 varchar(20), value2 char(10));

insert into foo values ('foo', 'bar');
insert into foo values ('bar', 'bar');
insert into foo values ('foo', 'foo');

2. Create the view
Create view v_foo as Select * from foo
go

3. Test the view with an initial select
select * from v_foo

4. Alter the base table and add a column, insert more data
alter table foo add  value3 varchar(30);
insert into foo values (NULL, NULL, 'foobar');

5. Do another select from our view. Notice a problem?
select * from v_foo


It doesn't work even if I select the specific column i want:

select value3 from v_foo


If you drop and recreate the view it will work. Restarting the database will fix it also. The other solution is to not use a SELECT * from inside your view.




-ec

Edited by - eyechart on 06/08/2004 11:46:47
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.64 seconds. Powered By: Snitz Forums 2000