0

I have the following table

CREATE TABLE [OMEGACA].[ACC_POL] ( [POLICY_ID] [int] IDENTITY(1,1) NOT NULL, [POLICY_NAME] [nvarchar](200) NOT NULL, [POLICY_DESC] [nvarchar](1000) NULL, [STATUS_ID] [int] NOT NULL, [RULE_EVAL] [int] NOT NULL, [AUDIT_OPTION] [int] NOT NULL, [FORMULA] [nvarchar](2000) NULL, [DEBUG_LOG] [int] NOT NULL, [USER_APPLY] [int] NOT NULL, [USE_CACHE] [int] NOT NULL, [DATE_UPD] [datetime] NULL, [USER_UPD] [nvarchar](200) NULL, [DATE_CREATED] [datetime] NOT NULL, [USER_CREATED] [nvarchar](200) NOT NULL, ..... )

And I also have the following view:

enter image description here

When I query the view V_ACC_POL, I get datatype VARCHAR for column POLICY_NAME from the view:

enter image description here

I was expecting it to be NVARCHAR (as in the base table).

Questions:

  • Why is the NVARCHAR column in the base table turn into a VARCHAR column in the view?
  • How can I have it in the view as NVARCHAR?
Improve this question
6
  • 3
    As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. Commented Mar 6 at 23:22
  • 4
    Could be you changed datatype of the table column without doing a: exec sys.sp_refreshsqlmodule @name = 'V_ACC_POL', @namespace = N'OBJECT' which refreshes view, this isn't done automatically Commented Mar 6 at 23:32
  • 4
    ^ was the column varchar in the past? Also I would rather trust sys.columns and not bother with INFORMATION_SCHEMA: sqlblog.org/2011/11/03/… Commented Mar 7 at 1:25
  • Thnx @AaronBertrand. It is the same with sys.columns - still varchar Commented Mar 7 at 14:09
  • 2
    Yeah, I wasn't suggesting sys.columns as a fix. Just as a much better option in general. Commented Mar 7 at 17:57

1 Answer 1

Reset to default
2

Why the NVARCHAR column in the base table is made a VARCHAR type in the view ?

It's because you have first created table and view and then after creating view you altered column size in table.

Any schema change in base table not reflects automatically in the view.

How can I have it in the view as NVARCHAR ?

You have two options:

  1. If schema change is intended and you want to reflect in view then execute sp_refreshview stored procedure immediately after column changes:
EXEC sp_refreshview V_ACC_POL
  1. If schema change is not intended which is done by some another developer and you want to prevent this kind of unintended changes then create view with schema binding option which will prevent others by modifying table schema:
ALTER VIEW [dbo].[V_ACC_POL] WITH SCHEMABINDING AS SELECT [ID], [Name] FROM [dbo].[ACC_POL]

This will give below error if someone tries to update table schema:

Msg 5074, Level 16, State 1, Line 12
The object 'V_ACC_POL' is dependent on column 'Name'.

Msg 4922, Level 16, State 9, Line 12
ALTER TABLE ALTER COLUMN Name failed because one or more objects access this column.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

THnx @Harsh Varde I did a "alter view <same body>" instead of v view. it was fixed

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.