Bit Bad - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Bit Bad

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-07 : 16:25:16

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 - 2004-06-07 : 16:32:48

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-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-07 : 16:36:34

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

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-07 : 16:38:57

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-07 : 16:41:36

Are you using SELECT * in the view?

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-07 : 16:46:41

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

Sam

ed: Argh!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-07 : 16:47:53

quote:
Originally posted by SamC

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

Sam

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-07 : 17:01:44

Anyone have a SP that'll tag all views to be recompiled? (hopeful look)

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-07 : 17:06:40

Why the use of SELECT * in views?

Tara

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-07 : 17:13:52

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-07 : 17:16:34

From BOL:

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

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-07 : 17:18:00

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.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-07 : 17:25:05

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.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-06-07 : 19:05:10

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

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-06-07 : 19:23:32

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"

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-07 : 20:29:10

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

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-06-07 : 20:34:38

I did..

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

DavidM

"Always pre-heat the oven"

SamC
White Water Yakist

3467 Posts

Posted - 2004-06-07 : 20:46:36

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-06-08 : 07:54:09

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 *."

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-08 : 09:13:53

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-)

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-06-08 : 11:42:49

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources