27,579
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('dbo.A_L') IS NOT NULL
DROP TABLE dbo.A_L;
CREATE TABLE dbo.A_L
(
ID INT NOT NULL
,Word VARCHAR(100) NOT NULL CHECK (Word LIKE '[a-l]%' OR Word LIKE '[A-L]%')
)
ALTER TABLE dbo.A_L
ADD CONSTRAINT pk_A_L PRIMARY KEY(ID,Word)
CREATE NONCLUSTERED INDEX idx_A_L_ID ON dbo.A_L(ID)
IF OBJECT_ID('dbo.M_Z') IS NOT NULL
DROP TABLE dbo.M_Z;
CREATE TABLE dbo.M_Z
(
ID INT NOT NULL
,Word VARCHAR(100) NOT NULL CHECK (Word LIKE '[m-z]%' OR Word LIKE '[M-Z]%')
)
ALTER TABLE dbo.M_Z
ADD CONSTRAINT pk_M_Z PRIMARY KEY(ID,Word)
CREATE NONCLUSTERED INDEX idx_M_Z_ID ON dbo.M_Z(ID)
GO
IF OBJECT_ID('dbo.v_Word') IS NOT NULL
DROP VIEW dbo.v_Word;
GO
CREATE VIEW v_Word
AS
SELECT ID,Word FROM A_L
UNION ALL SELECT ID,Word FROM M_Z
INSERT INTO v_Word SELECT 230,'Alike'
--Msg 4436, Level 16, State 12, Line 1
--UNION ALL view 'testdb.dbo.v_Word' is not updatable because a partitioning column was not found.
INSERT INTO A_L SELECT 230,'Alike'
--(1 row(s) affected)
UPDATE v_Word SET Word = 'Blike' WHERE ID = 230
--Msg 4436, Level 16, State 12, Line 1
--UNION ALL view 'testdb.dbo.v_Word' is not updatable because a partitioning column was not found.
CREATE TABLE ta(ID INT ,Orderdate DATETIME CHECK (Orderdate>='1996-01-01' AND Orderdate<'1997-01-01'),PRIMARY KEY (ID,Orderdate))
CREATE TABLE tb(ID INT ,Orderdate DATETIME CHECK (Orderdate>='1997-01-01' AND Orderdate<'1998-01-01'),PRIMARY KEY (ID,Orderdate))
CREATE TABLE tc(ID INT ,Orderdate DATETIME CHECK (Orderdate>='1998-01-01' AND Orderdate<'1999-01-01'),PRIMARY KEY (ID,Orderdate))
CREATE VIEW v_tt
AS
SELECT ID,Orderdate FROM ta
UNION ALL SELECT ID,Orderdate FROM tb
UNION ALL SELECT ID ,Orderdate FROM tc
DROP VIEW v_tt
DROP TABLE ta,tb,tc
INSERT INTO ta SELECT 1,'1996-12-08 00:00:00.000'
INSERT INTO tb SELECT 1,'1997-12-08 00:00:00.000'
UPDATE v_tt SET Orderdate = '1997-12-30' WHERE ID = 5 AND Orderdate = '1997-12-08 00:00:00.000'
-- Result :
(0 row(s) affected)