The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query.
The plan produced by the query processor incorrectly handles NULLs
for the ImageObjectID
column. It incorrectly reasons that the view query rejects NULLs
for this column, when it does not. Thinking that NULLs
are excluded, it is able to match the filtered nonclustered index on the Users
table that filters on ImageObjectID IS NOT NULL
.
By producing a plan that uses this filtered index, it ensures that rows with NULL
in ImageObjectID
are not encountered. These rows are returned (correctly) from the view index, so it appears there is a corruption when there is not.
The view definition is:
SELECT
dbo.Universities.ID AS Universities_ID,
dbo.Users.ImageObjectID AS Users_ImageObjectID
FROM dbo.Universities
JOIN dbo.Users
ON dbo.Universities.AdminUserID = dbo.Users.ID
The ON
clause equality comparison between AdminUserID
and ID
rejects NULLs
in those columns, but not from the ImageObjectID
column.
Part of the DBCC generated query is:
SELECT [Universities_ID], [Users_ImageObjectID], 0 as 'SOURCE'
FROM [dbo].[mv_Universities_Users_ID] tOuter WITH (NOEXPAND)
WHERE NOT EXISTS
(
SELECT 1
FROM [dbo].[mv_Universities_Users_ID] tInner
WHERE
(
(
(
[tInner].[Universities_ID] = [tOuter].[Universities_ID]
)
OR
(
[tInner].[Universities_ID] IS NULL
AND [tOuter].[Universities_ID] IS NULL
)
)
AND
(
(
[tInner].[Users_ImageObjectID] = [tOuter].[Users_ImageObjectID]
)
OR
(
[tInner].[Users_ImageObjectID] IS NULL
AND [tOuter].[Users_ImageObjectID] IS NULL
)
)
)
)
OPTION (EXPAND VIEWS);
This is generic code that compares values in a NULL
-aware fashion. It is certainly verbose, but the logic is fine.
The bug in the query processor's reasoning means that a query plan that incorrectly uses the filtered index may be produced, as in the example plan fragment below:
The DBCC query takes a different code path through the query processor from user queries. This code path contains the bug. When a plan using the filtered index is generated, it cannot be used with the USE PLAN
hint to force that plan shape with the same query text submitted from a user database connection.
The main optimizer code path (for user queries) does not contain this bug, so it is specific to internal queries like those generated by DBCC.
When you are working with partition switching, SQL Server will need to verify that the source table/partition boundaries can fit in the destination table/partition boundaries. In other words, you're trying to switch data from dbo.temp_table
to dbo.play_table
's partition 2. Think of it like this, the data for the c1
in dbo.temp_table
is constrained only by the data type (int
), so you can have values ranging from -2,147,483,648 to 2,147,483,647. But conversely, you're destination (dbo.play_table
partition 2) has a range from 4 to 2,147,483,647.
Your data does not violate this, but it is the metadata that can't allow this. You could just as easily insert the value -10 into dbo.temp_table
. The partition switching would fail the same way and make more sense, as -10 does not fit in dbo.play_table
's 2nd partition boundaries.
If you wanted to make this code work, you'd need to explicitly tell SQL Server that dbo.temp_table
will never have any data that won't fit in dbo.play_table
's 2nd partition. You could do this with a check constraint:
/******************************************************************************
your code omitted for brevity
******************************************************************************/
-- move contents of play_table to temp_table, which is not partitioned
-- but is in the same filegroup
ALTER TABLE dbo.play_table
SWITCH PARTITION 2 TO temp_table;
PRINT 'Switched from partitioned table to non-partitioned table';
/******************************************************************************
added check constraint so that data can fit in the destination partition
******************************************************************************/
alter table dbo.temp_table
add constraint CK_TempTable_C1 check (c1 >= 4);
go
/******************************************************************************
end of added code
******************************************************************************/
-- move data back to partitioned play_table from unpartitioned temp_table
-- this will no longer FAIL
ALTER TABLE dbo.temp_table
SWITCH TO play_table partition 2;
PRINT 'Switched from non-partitioned table to partitioned table';
/******************************************************************************
your code omitted for brevity
******************************************************************************/
That above sample addition to your code makes this a working solution. Now SQL Server knows that the data in dbo.temp_table
can fit in partition 2 of dbo.play_table
because of the added check constraint to dbo.temp_table
.
Best Answer
You need an indexed view on the staging table that matches the definition of the one on the production table, and indexes on the staging view that match every index on the production view.
See SqlFiddle
The idea is that the engine must replace every index, including the ones declared on the views. If it has build an index (ie. if there is an index on production but not on staging) then the switch will fail. Also all constraints, filters etc must match so that the engine knows that the data is valid (staging data will not violate production constraints).
Not sure why you need outer joins or anything similar, this should be straight forward.