to黄果树:执行计划没法帖,你可以试试。 select * from v1 where datetimes between '1990-12-31' and '1999-11-11' 按照分区的概念,执行计划中应该只显示Customer_33,但现在三个表在执行计划中都体现了。 to 和尚: 你检索的select * from v1,肯定查询了3个表。
to 黄果树:你测试结果是扫描的索引吗,只扫描了Customer_33表吗?但我这里扫描了3个表,与补丁有关吗
哈哈,不好意思 select * from v1 确实对3个表都进行了扫描
to 和尚:贵在参与,怎么才能让sql语句只扫描符合约束条件的表呢?
执行 select * from v1 where datetimes between convert(datetime,'1990-12-31') and convert(datetime,'1999-11-11' )
--创建分区视图的基表
CREATE TABLE Customer_33
(CustomerID INTEGER,
Datetimes DATETIME
CHECK (Datetimes BETWEEN '1900-01-01 00:00:00' AND '1999-12-31 23:59:59'),
CONSTRAINT g33_key PRIMARY KEY (CustomerID, Datetimes) )CREATE TABLE Customer_66
(CustomerID INTEGER,
Datetimes DATETIME
CHECK (Datetimes BETWEEN '2000-01-01 00:00:00' AND '2004-12-31 23:59:59'),
CONSTRAINT g66_key PRIMARY KEY (CustomerID, Datetimes) )
CREATE TABLE Customer_99
(CustomerID INTEGER,
Datetimes DATETIME
CHECK (Datetimes BETWEEN '2005-01-01 00:00:00' AND '2005-12-31 23:59:59'),
CONSTRAINT g99_key PRIMARY KEY (CustomerID, Datetimes) )
GO
--创建分区视图
CREATE VIEW V1 AS
SELECT * FROM Customer_33
UNION ALL
SELECT * FROM Customer_66
UNION ALL
SELECT * FROM Customer_99
GO
--向分区视图插入数据
insert into v1 select 1,'1998-12-31'
insert into v1 select 1,'2001-12-31'
GO
select * from v1
--执行结果如下
1 1998-12-31 00:00:00.000
1 2001-12-31 00:00:00.000
--是对的呀
--我也分别查询了3个表
--结果也对--请确保你的SQLSERVER打了SP3补丁
==============================
我也测试了一把 因为datetimes上有约束 要不全表扫描 要不索引扫描 因为datetimes是主键的一部分 所以扫描的是索引
select * from v1 where datetimes between '1990-12-31' and '1999-11-11'
按照分区的概念,执行计划中应该只显示Customer_33,但现在三个表在执行计划中都体现了。
to 和尚: 你检索的select * from v1,肯定查询了3个表。
select * from v1
确实对3个表都进行了扫描
select * from v1 where datetimes between convert(datetime,'1990-12-31') and convert(datetime,'1999-11-11' )