June 17, 2015 at 11:16 am
Hello,
please try the below script step by step and you will see null values in view after adding a column to the base table. looks like its bug.
If you anyone saw this issue, please let me know.
i know by providing the column names the issue will go away.
--Create table test
CREATE TABLE [dbo].[test](
[c1] [varchar](30) NULL,
[c2] [varchar](20) NULL
) ON [PRIMARY]
--Insert values in to the test table
Insert into test values ('533','Robin')
Insert into test values ('655','Steve')
--Create View
create view [dbo].[test_vw]
as
select *, total = CAST ( c1 as money)
from test
--Verify data in the table and view. Specifically, check Total column.
SELECT [c1],[c2] FROM [dbo].[test]
select * from test_vw
--Add a column to Test Table
ALTER TABLE test add Terms varchar(30)
--Verify data in the table and view. Specifically, check Total column.
SELECT [c1],[c2] FROM [dbo].[test]
select * from test_vw
--Drop table and view
DROP TABLE test
DROP VIEW test_vw
June 17, 2015 at 11:37 am
Try this:
--Create table test
CREATE TABLE [dbo].[test](
[c1] [varchar](30) NULL,
[c2] [varchar](20) NULL
) ON [PRIMARY]
--Insert values in to the test table
Insert into test values ('533','Robin')
Insert into test values ('655','Steve')
GO
--Create View
create view [dbo].[test_vw]
as
select *, total = CAST ( c1 as money)
from test
GO
--Verify data in the table and view. Specifically, check Total column.
SELECT [c1],[c2] FROM [dbo].[test]
select * from test_vw
--Add a column to Test Table
ALTER TABLE test add Terms varchar(30)
--Verify data in the table and view. Specifically, check Total column.
EXEC sp_RefreshView test_vw --UPDATES METADATA
SELECT [c1],[c2] FROM [dbo].[test]
select * from test_vw
--Drop table and view
DROP TABLE test
DROP VIEW test_vw
You can read about it here:
https://msdn.microsoft.com/en-us/library/ms187821.aspx
Essentially the view didn't update it's metadata after you made changes the underlying table.
June 17, 2015 at 11:39 am
This is a classic "it's not a bug, it's a feature" things...
This is one of those funky MS SQL Server View behaviors. Because the view is not Schema Bound you need to run sp_refreshview after you make the change to the underlying tables.
This is another thing to consider when making the decision to use "SELECT *".
As a matter of practice I prefer to add SCHEMA BINDING to my views in case I want to index them.
-- Itzik Ben-Gan 2001
June 17, 2015 at 12:19 pm
thanks for the reply.
yeah refreshing the view will solve the issue but when i query the view after adding a column to table, i get the following result.
c1 c2 total
533 Robin NULL
655 Steve NULL
Column Total values are NULL, which i feel is a bug.
June 17, 2015 at 12:37 pm
Did you copy and paste my code and tried it?
My results are:
c1 c2 Terms total
533 Robin NULL 533.00
655 Steve NULL 655.00
June 17, 2015 at 12:54 pm
no, you are refreshing the view and executing the view again. That makes sense you are seeing new column and seeing the correct values for Total column as well.
My question is without refreshing the view ( i know we wont see the newly added column) why would it effect the existing 'Total' column ?
c1 c2 total
533 Robin NULL
655 Steve NULL
June 17, 2015 at 12:54 pm
<Duplicate Post Removed>
SSC Bug? :hehe:
-- Itzik Ben-Gan 2001
June 17, 2015 at 12:55 pm
Robin35 (6/17/2015)
thanks for the reply.yeah refreshing the view will solve the issue but when i query the view after adding a column to table, i get the following result.
c1 c2 total
533 Robin NULL
655 Steve NULL
Column Total values are NULL, which i feel is a bug.
Nope. It's not a bug. It's working as documented.
From BOL (emph mine):
If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.
A bug is when something is not working as advertised. In your case the view is working exactly as advertised. You did not use SCHEMABINDING. You did not run sp_refreshview after changing the underlying object. The view produced unexpected results afterwards. If you created a view that was SCHEMA BOUND and had the same issue it would be a bug.
-- Itzik Ben-Gan 2001
June 17, 2015 at 1:17 pm
Thanks for the confirmation Alan. It makes sense now.
i thought of opening a case with Microsoft but before that i wanted to confirm with the experts here.
Thanks for your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply