CODEWITHSUNDEEP
sql-server
Chandra_S
Chandra_S

Reputation: 319

SQL Server: does data type change of column (kept column name same) requires recreation of views?

I changed data type of column from nvarchar to datetime keeping the column name same in SQL server. does it requires to drop and recreate the views which depends on that column?

Upvotes: 0

Views: 454

Answers (2)

Rachel Ambler
Rachel Ambler

Reputation: 1604

Not as an answer but to further sepupic's answer above (who beat me to the punch!), consider the following:

Begin Transaction
Create Table dbo.SOTest(col1 Numeric(12,2));
Go
Create View dbo.SOTestV As Select col1 From dbo.SOTest
Go
Select c.name, t.name
From sys.columns As c
Join sys.types As t
On c.user_type_id = t.user_type_id
Where c.object_id = Object_Id('dbo.SoTestV')
Go
Alter Table dbo.SOTest Alter Column col1 Int
Go
Select c.name, t.name
From sys.columns As c
Join sys.types As t
On c.user_type_id = t.user_type_id
Where c.object_id = Object_Id('dbo.SoTestV')
Go
Exec sys.sp_refreshview N'dbo.SOTestV'
Go
Select c.name, t.name
From sys.columns As c
Join sys.types As t
On c.user_type_id = t.user_type_id
Where c.object_id = Object_Id('dbo.SoTestV')
Go
Rollback Transaction

The results of which will be

col1   numeric

(1 row(s) affected)

col1   numeric

(1 row(s) affected)

col1   int

Thus proving that the metadata in the view is still showing the previous datatype until refreshed.

Upvotes: 0

sepupic
sepupic

Reputation: 8687

When you change base table column types you should always do at least refresh view:

exec sp_refreshview 'yourView'

And that is why. When you change your table definition, the view metadata is not refreshed. Now immagine that you have users that have no permission on the base table, but have SELECT permission on the view. If they ask sp_help about the columns of this view, or if they open Columns folder in OE, it will still show the old types. Then the users can make illegal queries an they'll be going crazy to figure out what happens. I give you this example to show what can happen.

create view dbo.vw_test_types as
select art_code, art_desc
from dbo.test_types;
go

insert into dbo.test_types (art_code, art_desc)
values ('123', 'trekking shoes');
go

alter table dbo.test_types alter column art_code int;
go

exec sp_help 'dbo.vw_test_types'

select art_desc + ' ' + art_code as full_art_desc
from dbo.vw_test_types;

--Msg 245, Level 16, State 1, Line 33
--Conversion failed when converting the varchar value 'trekking shoes ' to data type int.


exec sp_refreshview 'dbo.vw_test_types'
go

exec sp_help 'dbo.vw_test_types'
go

Here you create a table containing only varchar columns. You then create a view based on this table and enter a row. At this point you decide to change the art_code type to int, the command works fine. But look at your view's metadata (exec sp_help 'dbo.vw_test_types'), it still shows you only varchar columns. Now there is a user that has no access to the base table, he wants to show up the whole description including the art_code. He opens SSMS -> OE -> dbo.vw_test_types -> Columns and see that all columns are varchar, so he just concatenates art_desc and art_code. And gets the error! And he can really be perplessed about it, he SEES varchar only but the error told him abot int type

And even worse. Think if the user has built some reports based on that query. One day all these reports do not work at all and they can be configured to NOT show the error to the user, simply do not work showing "an error happen when processing the query"

Upvotes: 2

Related Questions