[SQL Server] modifying the base table in the partition View

[SQL Server] how to modify the base table in the partition view-1 problem Arrangement

--- First, I created two tables.
Create Table [work_detail_h] (
[ID] [int] not null,
[LB] [varchar] (2) not null check (Lb = 'H '),
[Fxph] [varchar] (20) Collate chinese_prc_ci_as null
Primary Key (ID, LB)
)
Go

Create Table [work_detail_s] (
[ID] [int] not null,
[LB] [varchar] (2) not null check (Lb ='s '),
[Fxph] [varchar] (20) Collate chinese_prc_ci_as null
Primary Key (ID, LB)
)
Go

--- Create a partition View
Create view view_work_detail
As
Select * From work_detail_h
Union all
Select * From work_detail_s

--- Then insert data
Insert into view_work_detail values (2, 'h', '09h001 ')
--
(The number of affected rows is 1)

------------ Then let's get a question
-- If I use the Enterprise Manager to change the fxph field of these two basic tables to not null
-- Then execute

Alter view view_work_detail
As
Select * From work_detail_h
Union all
Select * From work_detail_s

--- Execute
Insert into view_work_detail values (1, 'h', '09h001 ')

-- An error occurs.
-- The 'view _ work_detail 'of the Union all view cannot be updated because no partition column is found.

 

-- 2 cause search

-- Impact of table modification through SQL Server Enterprise Manager (2000) or SSMs (2005)
-- For example, if the field [fxph] is modified from null-> not null, the modification statement is as follows:

/* To prevent any possible data loss problems, you should first carefully check the script and then run it outside the context of the Database Designer. */
Begin transaction
Set quoted_identifier on
Set arithabort on
Set numeric_roundabort off
Set concat_null_yields_null on
Set ansi_nulls on
Set ansi_padding on
Set ansi_warnings on
Commit
Begin transaction
Go
Create Table DBO. tmp_work_detail_h
(
Id int not null,
LB varchar (2) not null,
Fxph varchar (20) null
) On [primary]
Go
If exists (select * From DBO. work_detail_h)
Exec ('insert into DBO. tmp_work_detail_h (ID, LB, fxph)
Select ID, LB, fxph from DBO. work_detail_h with (holdlock tablockx )')
Go
Drop table DBO. work_detail_h
Go
Execute sp_rename n 'dbo. tmp_work_detail_h ', n' work _ detail_h', 'object'
Go
Alter table dbo. work_detail_h add Constraint
Pk1_work_detail_h1_0cd0c267 primary key clustered
(
ID,
LB
) On [primary]

Go
Alter table dbo. work_detail_h with nocheck add Constraint
Ck1_work_detail1__lb1_0dc4e6a0 check ([LB] = 'H '))
Go
Commit

-- The partition view is affected by the with nocheck (constraints reference) method when the check constraint is added,
-- That is, the last sentence above will prompt when the view is isnert again. The internal reasons are unknown.

/*
Message 4436, level 16, status 12, 1st rows
'View _ work_detail 'of the Union all view cannot be updated because no partition column is found.
*/

-- 3 corrective action to make constraints check once

Alter table work_detail_h with check constraint all
Alter table work_detail_s with check constraint all

 

 

Attached original question Stickers: http://topic.csdn.net/u/20100713/15/3526bf42-b46c-4a0d-bc7c-e961118acd30.html? Seed = 453101024 & R = 66937691 # r_66937691