sql-serverviewvarcharnvarchar

Nvarchar table column gets as varchar in view

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:

Solution

  • 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.