????InvalidRegionIsInactiveButOfficeIsActiveView???

???????????????????Region??????????????????????′???Office??????????????仰???????????????????????′?????active??????1?????????????????IsActive?0???????????′????????????Region?????????View??????????Region??IsActive?0????????????offce??IsActive?1?????????Υ??????????????.??dbo.DuplicateRows????Cross Join???????????Where?????????????????????С?

CREATE View dbo.InvalidRegionIsInactiveButOfficeIsActiveView With SchemaBinding

As

SELECT     dbo.Region.RegionId
FROM       dbo.Region
INNER JOIN dbo.Office
ON         dbo.Region.RegionId = dbo.Office.RegionId
CROSS JOIN dbo.DuplicateRows
WHERE      dbo.Region.IsActive = Convert(bit?? 0)
AND        dbo.Office.IsActive = Convert(bit?? 1)
 


?????????????????????????????????????????У???????????????????е?????Cross Join?????????????????????????????е???????????Υ???????????????????????????????????

CREATE UNIQUE CLUSTERED INDEX IX_RegionInvalidOfficeIsActiveView_RegionId ON dbo.InvalidRegionIsInactiveButOfficeIsActiveView
 (RegionId ASC) WITH (PAD_INDEX = OFF?? STATISTICS_NORECOMPUTE = OFF?? SORT_IN_TEMPDB = OFF?? IGNORE_DUP_KEY = OFF?? DROP_EXISTING = OFF?? ONLINE = OFF?? ALLOW_ROW_LOCKS = ON?? ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


??????????????Υ????????

????????????Antarctica????

????????????????????Antarctica?????????????????????????Office???????????????Region??IsActive?0??

UPDATE dbo.Region
 SET dbo.Region.IsActive  = Convert(bit?? 0)??
     dbo.Region.ExpirationDate = GetDate()
 WHERE dbo.Region.RegionName  = N'Antarctica'


????????к????????′???

Msg 2601?? Level 14?? State 1?? Line 1
Cannot insert duplicate key row in object 'dbo.InvalidRegionIsInactiveButOfficeIsActiveView' with unique index 'IX_RegionInvalidOfficeIsActiveView_RegionId'. The duplicate key value is (5).


????????Antarctica????????Byrd Station???′????????ó?????????????????????????????????????????′????κ?????????????????′??????????????

UPDATE dbo.Office
 SET dbo.Office.RegionId = (SELECT dbo.Region.Regionid
                             FROM dbo.Region
                             WHERE dbo.Region.RegionName = N'South'
                            )
 FROM dbo.Office
 WHERE dbo.Office.RegionId =(SELECT dbo.Region.Regionid
                              FROM dbo.Region
                              WHERE dbo.Region.RegionName = N'Antarctica'
                             )


???????????κλ?????′???Antarctica???????????????????Update????????Antarctica???????

?????????????????????????????

???????漸????????????????????????????

????1??????dbo.DuplicateRows table???????????????

????2??д???Υ????????????????????????

????3??????????????dbo.DuplicateRows????Cross Join

????4?????????????SchemaBinding???????????????????

????5????????????????

???????

??????????????????????е????????????????????????????Ч????Σ???????????????????????????????Instead of???????????????????“Before??????”???Υ???????????????????????????????????????????????????????????????????ε???????????”before ??????”??