November 24, 2011 at 4:28 am
I have 2 tables employee and employee_address
CREATE TABLE employee
(
employee_code varchar(10),
employee_name varchar(100)
)
CREATE TABLE employee_address
(
employee_code varchar(10),
address_type int,
address varchar(100)
)
Now I have a View say (EmployeeAddress)
CREATE VIEW EmployeeAddress
AS
SELECT em.*,ed.address
FROM employee em inner join employee_address ed ON em.employee_code = ed.employee_code
Now the issue is, I have added one more column to the table employee (say remarks)
ALTER TABLE employee Add remarks varchar(100)
So what is happening now, when i use the view (eg select * from EmployeeAddress),
the new column (remarks ) data is appearing is address column but the column header still says address. The remarks column is not included in the output.
I thought is should always return all columns from the employee table. To fix this I have recompile the veiw (Altered).
Is there any reason this happening or should I have to do something else to fix this, so that If I add any new column to the table, I don't have to recompile it again.
Sample Data:
INSERT INTO employee
SELECT '100','Ajit'
UNION
SELECT '200', 'Deepak'
UNION
SELECT '300', 'Peeyush'
UNION
SELECT '400', 'Rohit'
GO
INSERT INTO employee_address
SELECT '100',1,'Delhi'
UNION
SELECT '100',2,'110045'
UNION
SELECT '200', 1,'Haryana'
UNION
SELECT '200', 2, '122002'
UNION
SELECT '300', 1,'Noida'
UNION
SELECT '300', 2,'130012'
UNION
SELECT '400', 1,'UP'
UNION
SELECT '400', 2,'140001'
GO
select * from EmployeeAddress
-- After alter table, update to have some sample data.
UPdate employee
SET remarks = employee_name + '(' + employee_code + ')'
Now run and see the out. Now the address column will have the remarks column output, and remarks column will not be there.
select * from EmployeeAddress
November 25, 2011 at 1:44 am
Try
EXEC sp_refreshview 'EmployeeAddress'
The view is non-schemabound so you have to refresh it's metadata when altering the underlying tables.
November 25, 2011 at 2:02 am
Awesome....that worked like a charm.
Thanks
November 25, 2011 at 8:30 am
This is one of the reasons why it is not recommended that you use '*' in production code. When you modify the underlying tables, the views will not reflect the changes until you refresh them.
If you had specified all of the columns you wouldn't have had a problem.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 25, 2011 at 8:40 am
Jeffrey Williams 3188 (11/25/2011)
This is one of the reasons why it is not recommended that you use '*' in production code. When you modify the underlying tables, the views will not reflect the changes until you refresh them.If you had specified all of the columns you wouldn't have had a problem.
Although in general I do not recommend using '*' either, in this case it doesn'nt really matter. Clearly, the OP expected the view to return the new column of the underlying table. Specifying all columns in the view requires the view to be modified. Using '*' you only have to refresh to view. So one way or the other, it doesn't come for free.
November 25, 2011 at 10:46 am
Peter Brinkhaus (11/25/2011)
Jeffrey Williams 3188 (11/25/2011)
This is one of the reasons why it is not recommended that you use '*' in production code. When you modify the underlying tables, the views will not reflect the changes until you refresh them.If you had specified all of the columns you wouldn't have had a problem.
Although in general I do not recommend using '*' either, in this case it doesn'nt really matter. Clearly, the OP expected the view to return the new column of the underlying table. Specifying all columns in the view requires the view to be modified. Using '*' you only have to refresh to view. So one way or the other, it doesn't come for free.
Not really - using '*' you may need to refresh any SSIS packages, SSRS reports, application code or other objects that reference the view. And that could present serious problems for your users.
If the view specifies just the columns it needs - then downstream applications will not break when a column is added to the underlying tables. If the columns are needed in those applications, then the views can be updated in conjunction with the application changes.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 25, 2011 at 11:23 am
Jeffrey Williams 3188 (11/25/2011)
Peter Brinkhaus (11/25/2011)
Jeffrey Williams 3188 (11/25/2011)
This is one of the reasons why it is not recommended that you use '*' in production code. When you modify the underlying tables, the views will not reflect the changes until you refresh them.If you had specified all of the columns you wouldn't have had a problem.
Although in general I do not recommend using '*' either, in this case it doesn't really matter. Clearly, the OP expected the view to return the new column of the underlying table. Specifying all columns in the view requires the view to be modified. Using '*' you only have to refresh to view. So one way or the other, it doesn't come for free.
Not really - using '*' you may need to refresh any SSIS packages, SSRS reports, application code or other objects that reference the view. And that could present serious problems for your users.
If the view specifies just the columns it needs - then downstream applications will not break when a column is added to the underlying tables. If the columns are needed in those applications, then the views can be updated in conjunction with the application changes.
Agreed, one of the reasons why I usually don't use '*'. But there are situations where I prefer to let SSIS packages, application code or whatsoever fail rather than producing wrong results. It just tell me I forgot to update the view/package/application code.
BTW: it's easy to automatically refresh views by creating a DDL-trigger.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply