view which has select * won't pick up the new columns

Question

view which has select * won't pick up the new columns

sqlfriend

SSC Guru

Points: 52794
More actions
March 7, 2012 at 2:00 pm

#252723

We have a ssis package that basically is importing data into tables from flat files. recently we added a column in the flat file, so the column needs to be added to the table too.
We also have a view that selects from one tables for other packages.
The view i something like this:
SELECT S.*, SCH.SchoolName, SCH.SchoolShortName
FROM Students.AllStudents S
JOIN Schools.CurrentSchools SCH ON (S.School = SCH.School)
WHERE (StatusCode = 'A')
Last week there are some other process failed, one of the developers said he traced back, he thinks the reason is that it is the view is not updated after new columns are added. He said the view either needs to be recreated, or use explicit column names.
I know using explict column name is best practice, but for my understanding, if I use select * , if a new column is added, then it should automatically include that column. Is that correct?
But he said it is not, because select * won't pick up the meta data change, unless the view is recreated, the new columns will be reflected even we still use select *.
Is that true?
Thanks

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

yes.

if the view is not schema bound, if you created the view with select *...that gets compiled to a select featuring the actual columns at the time of creation.

you can run exec sp_refreshview [viewname], or alter the view with the same definition, or drop and recreate it.

all three will fix the issue if you added additional columns.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323547 More actions · Answer 2

also, you'd get an error if you try to use * and also schemabind a view:

CREATE VIEW v_YOURTABLE

WITH SCHEMABINDING

AS

SELECT * FROM YOURTABLE

Msg 1054, Level 15, State 6, Procedure v_YOURTABLE , Line 4
Syntax '*' is not allowed in schema-bound objects.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

Lowell (3/7/2012)
also, you'd get an error if you try to use * and also schemabind a view:
CREATE VIEW v_YOURTABLE
WITH SCHEMABINDING
AS
SELECT * FROM YOURTABLE
Msg 1054, Level 15, State 6, Procedure v_YOURTABLE , Line 4
Syntax '*' is not allowed in schema-bound objects.

Another good reason to not use Select * in this way.

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

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

Thanks, for our case it is not a schema bound view, and if I use select column names, it has to explictily list 40 columns, I just think if we add a new column, it will be easier either by refresh it or recreate it, or alter the view but still using select *.

Is that OK? Thanks

Jared SSC Guru Points: 61793 More actions · Answer 5

Since you know that the view will change definition and want it to pick up the changes, I would stick with the select * and add a refresh to whatever is calling the view.

Jared
CE - Microsoft

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Nah do it the easy way .. go the the view .. change the word CREATE to ALTER .. run the code .. then go back to the view and change the word ALTER to CREATE .. and viola done . That is assuming you have the necessary permissions to CREATE and or ALTER a view ... once back to the original as defined no need to alter any security permissions to anyone who uses the view in their T-SQL statements

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

SQLKnowItAll (3/7/2012)
Since you know that the view will change definition and want it to pick up the changes, I would stick with the select * and add a refresh to whatever is calling the view.

This would be ugly form a security side, since you have to have ALTER permissions in order to run the refresh. You likely would not want to be handing out expanded privileges to simple service accounts

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jared SSC Guru Points: 61793 More actions · Answer 8

Matt Miller (#4) (3/7/2012)
SQLKnowItAll (3/7/2012)
Since you know that the view will change definition and want it to pick up the changes, I would stick with the select * and add a refresh to whatever is calling the view.
This would be ugly form a security side, since you have to have ALTER permissions in order to run the refresh. You likely would not want to be handing out expanded privileges to simple service accounts

Touche... Better idea, run it as a part of the SSIS package.

Jared
CE - Microsoft

SQLRNNR SSC Guru Points: 281344 More actions · Answer 9

sqlfriends (3/7/2012)
Thanks, for our case it is not a schema bound view, and if I use select column names, it has to explictily list 40 columns, I just think if we add a new column, it will be easier either by refresh it or recreate it, or alter the view but still using select *.
Is that OK? Thanks

40 Columns is not that many. Besides, is the view touching multiple tables or just one table?

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

sqlfriend SSC Guru Points: 52794 More actions · Answer 10

two tables, see my top message shows the view definition

SQLRNNR SSC Guru Points: 281344 More actions · Answer 11

sqlfriends (3/7/2012)
two tables, see my top message shows the view definition

My bad. I didn't understand that to be the actual view definition since it said "something like this".

I was just curious. Number of tables has no bearing on whether I recommend listing the columns or not. In this case, I would list all of the columns. With only two tables it is easy. Drag and drop from Object explorer.

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

sqlfriend SSC Guru Points: 52794 More actions · Answer 12

I just added as a step in the ssis to alter the view.

Then I thought I 'd better to check the security, then I noticed the entire package is running under sql service account, which is using a domain account - mydomain\myservice user,

I don't see this account has specific permission to the database which is used in the ssis pacakge, but all the time the package runs successfully, and in the package it drops tables and recreate tables, recreate constraints, it has all these permissions, but I just cannot see these permissions are granted to this service account in the database in SSMS, so now I got confused about the security.

I do see there is a Login called NT SERVICE\SQLSERVERAGENT which is a sysadmin, but my sql agent service is running under account mydomain\myserviceUser.

Confused...