模拟海量数据查询

制作大量数据方法：
1.新建数据库test，日志文件存在d:/log中，主文件存e:/data中
2.建立测试基表datable
CREATE TABLE [dbo].[datable](
 [startip] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [endip] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [province] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [city] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [scompip] [decimal](18, 0) NULL,
 [ecompip] [decimal](18, 0) NULL
)
3.导入数据，以bcp in方式导入文本文档30万条记录
EXEC master..xp_cmdshell 'bcp test..datable in e:/ip.txt -c -q  -U "sa" -P "sinodigital" -t","'
4.将数据文件加入混合标识并单独导出txt中备用（此文本文档不应删除，在做数据分区时可用bcpin方式导入数据）
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""1""",city+"""1""",scompip,ecompip from test.dbo.datable" queryout e:/yao1.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""2""",city+"""2""",scompip,ecompip from test.dbo.datable" queryout e:/yao2.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""3""",city+"""3""",scompip,ecompip from test.dbo.datable" queryout e:/yao3.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""4""",city+"""4""",scompip,ecompip from test.dbo.datable" queryout e:/yao4.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""5""",city+"""5""",scompip,ecompip from test.dbo.datable" queryout e:/yao5.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""6""",city+"""6""",scompip,ecompip from test.dbo.datable" queryout e:/yao6.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""7""",city+"""7""",scompip,ecompip from test.dbo.datable" queryout e:/yao7.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""8""",city+"""8""",scompip,ecompip from test.dbo.datable" queryout e:/yao8.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""9""",city+"""9""",scompip,ecompip from test.dbo.datable" queryout e:/yao9.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""10""",city+"""10""",scompip,ecompip from test.dbo.datable" queryout e:/yao10.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""11""",city+"""11""",scompip,ecompip from test.dbo.datable" queryout e:/yao11.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""12""",city+"""12""",scompip,ecompip from test.dbo.datable" queryout e:/yao12.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""13""",city+"""13""",scompip,ecompip from test.dbo.datable" queryout e:/yao13.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""14""",city+"""14""",scompip,ecompip from test.dbo.datable" queryout e:/yao14.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""15""",city+"""15""",scompip,ecompip from test.dbo.datable" queryout e:/yao15.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""16""",city+"""16""",scompip,ecompip from test.dbo.datable" queryout e:/yao16.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""17""",city+"""17""",scompip,ecompip from test.dbo.datable" queryout e:/yao17.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""18""",city+"""18""",scompip,ecompip from test.dbo.datable" queryout e:/yao18.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""19""",city+"""19""",scompip,ecompip from test.dbo.datable" queryout e:/yao19.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""20""",city+"""20""",scompip,ecompip from test.dbo.datable" queryout e:/yao20.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""21""",city+"""21""",scompip,ecompip from test.dbo.datable" queryout e:/yao21.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""22""",city+"""22""",scompip,ecompip from test.dbo.datable" queryout e:/yao22.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""23""",city+"""23""",scompip,ecompip from test.dbo.datable" queryout e:/yao23.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""24""",city+"""24""",scompip,ecompip from test.dbo.datable" queryout e:/yao24.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""25""",city+"""25""",scompip,ecompip from test.dbo.datable" queryout e:/yao25.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""26""",city+"""26""",scompip,ecompip from test.dbo.datable" queryout e:/yao26.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""27""",city+"""27""",scompip,ecompip from test.dbo.datable" queryout e:/yao27.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""28""",city+"""28""",scompip,ecompip from test.dbo.datable" queryout e:/yao28.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""29""",city+"""29""",scompip,ecompip from test.dbo.datable" queryout e:/yao29.txt -c -U"sa" -P"sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp "select Startip,Endip,province+"""30""",city+"""30""",scompip,ecompip from test.dbo.datable" queryout e:/yao30.txt -c -U"sa" -P"sinodigital" -t","'
5.将准备数据批量插入datable中
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao1.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao2.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao3.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao4.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao5.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao6.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao7.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao8.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao9.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao10.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao11.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao12.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao13.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao14.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao15.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao16.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao17.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao18.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao19.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao20.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao21.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao22.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao23.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao24.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao25.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao26.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao27.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao28.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao29.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao30.txt -c -q  -U "sa" -P "sinodigital" -t","'
6.导入数据后执行语句
select * from dbo.datable where province='黑龙江省1'
分析结果为：
(14015 行受影响)
表 'datable'。扫描计数 1，逻辑读取 93608 次，物理读取 0 次，预读 93555 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
7.为province列建非聚集索引
CREATE NONCLUSTERED INDEX INDEX_Province  ON datable (province)--如果有多个cpu可以加上"WITH (MAXDOP=n)"数据库引擎会将执行索引操作时使用的处理器数量限制为n个或少于n个
执行select * from dbo.datable where province='黑龙江省1'
分析结果为：
(14015 行受影响)
表 'datable'。扫描计数 1，逻辑读取 14066 次，物理读取 203 次，预读 93 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
执行select province= from dbo.datable where province='黑龙江省1'
分析结果为：
(14015 行受影响)
表 'datable'。扫描计数 1，逻辑读取 51 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
8.分布式分区视图方案（缺少唯一约束，大致思想为将所有数据平分为三表分放在三个服务器中）
服务器A:
执行建表语句
CREATE TABLE [dbo].[datable1](
 [startip] [varchar](50) NULL,
 [endip] [varchar](50) NULL,
 [province] [varchar](50) NULL,
 [city] [varchar](50) NULL,
 [scompip] [decimal](18, 0) NULL,
 [ecompip] [decimal](18, 0) NULL
)
将yao1.txt-yao10.txt拷备至A中用bcp导入数据
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao1.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao2.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao3.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao4.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao5.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao6.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao7.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao8.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao9.txt -c -q  -U "sa" -P "sinodigital" -t","'
EXEC master..xp_cmdshell 'bcp test..datable in e:/yao10.txt -c -q  -U "sa" -P "sinodigital" -t","'
CREATE NONCLUSTERED INDEX INDEX_Province1 ON datable1 (province)
在服务器B,C上分别建立表datable1插入11－30．txt数据
在本地服务器上执行
EXEC sp_addlinkedserver @server='serverA', @srvproduct='', @provider='SQLOLEDB', @datasrc='192.168.0.28'
EXEC sp_addlinkedserver @server='serverB', @srvproduct='', @provider='SQLOLEDB', @datasrc='192.168.0.24'
EXEC sp_addlinkedsrvlogin @rmtsrvname='serverA', @useself='false', @rmtuser ='sa',@rmtpassword = 'aaa'
EXEC sp_addlinkedsrvlogin @rmtsrvname='serverB', @useself='false', @rmtuser ='sa',@rmtpassword = 'sa'
Exec sp_serveroption 'serverA', 'lazy schema validation', 'true'
Exec sp_serveroption 'serverB', 'lazy schema validation', 'true'
建立链接服务器
执行建立视图语句
Create view VIEW_DATA  As
Select *  from datable1
Union all
Select *  from  serverA.test.dbo.datable1
Union all
Select *  from  serverB.test.dbo.datable1
在视图中查询结果
-----------------------------------------------------
declare @d datetime
set @d=getdate()
select * from dbo.datable where province='广州省21'
select datediff(ms,@d,getdate())--4123 索引查找42% rid查找58%
-----------------------------------------------------
declare @d datetime
set @d=getdate()
select * from dbo.VIEW_DATA where province='广州省21'
select datediff(ms,@d,getdate())--220  索引查找12% rid查找14% 远程37%,37%
-----------------------------------------------------

declare @d datetime
set @d=getdate()
select * from dbo.datable where province='广州省11'
select datediff(ms,@d,getdate())--140 索引查找42% rid查找58%
-----------------------------------------------------
declare @d datetime
set @d=getdate()
select * from dbo.VIEW_DATA where province='广州省11'
select datediff(ms,@d,getdate())--263  索引查找12% rid查找14% 远程37%,37%
-----------------------------------------------------
declare @d datetime
set @d=getdate()
select * from dbo.datable where province='广州省1'
select datediff(ms,@d,getdate())--173 索引查找42% rid查找58%
-----------------------------------------------------
declare @d datetime
set @d=getdate()
select * from dbo.VIEW_DATA where province='广州省1'
select datediff(ms,@d,getdate())--296  索引查找12% rid查找14% 远程37%,37%
-----------------------------------------------------
select top 10 province from dbo.datable where province like '%广州省%'--10s
select top 10 province from dbo.VIEW_DATA where province like '%广州省%'--30s
select top 10 province from dbo.datable where  charindex('广州省',province)>0--10s
select top 10 province from dbo.VIEW_DATA where charindex('广州省',province)>0--3s
9.本地分区视图(环境sqlserver2000)
drop TABLE Sales98
CREATE TABLE Sales98
   (
iden int,
OrderID   INT      ,
   CustomerID      varchar(50)         NOT NULL,
   OrderDate      DATETIME      NULL,
   OrderMonth      INT

   )
truncate table Sales98
select count(1) from Sales98
declare @i int
set @i=0
while @i<10000
begin
  insert into Sales98(OrderID,CustomerID,OrderDate,OrderMonth)
select Northwind.dbo.Orders.OrderID,Northwind.dbo.Customers.CustomerID,Northwind.dbo.Orders.OrderDate,month(Northwind.dbo.Orders.OrderDate) as OrderMonth from Northwind.dbo.Orders inner join Northwind.dbo.Customers on  Northwind.dbo.Orders.CustomerID = Northwind.dbo.Customers.CustomerID where year(Northwind.dbo.Orders.orderdate)=1998
  set @i = @i+1
end
insert into Sales98(OrderID,CustomerID,OrderDate,OrderMonth)
select Northwind.dbo.Orders.OrderID,Northwind.dbo.Customers.CustomerID,Northwind.dbo.Orders.OrderDate,month(Northwind.dbo.Orders.OrderDate) as OrderMonth from Northwind.dbo.Orders inner join Northwind.dbo.Customers on  Northwind.dbo.Orders.CustomerID = Northwind.dbo.Customers.CustomerID where year(Northwind.dbo.Orders.orderdate)=1998

CREATE TABLE Jan1998Sales
   (OrderID   INT      ,
   CustomerID      varchar(50)         NOT NULL,
   OrderDate      DATETIME      NULL
      CHECK (DATEPART(yy, OrderDate) = 1998),
   OrderMonth      INT
      CHECK (OrderMonth = 1)
)
CREATE TABLE Feb1998Sales
   (OrderID   INT      ,
   CustomerID      varchar(50)         NOT NULL,
   OrderDate      DATETIME      NULL
      CHECK (DATEPART(yy, OrderDate) = 1998),
   OrderMonth      INT
      CHECK (OrderMonth = 2)
)
CREATE TABLE Mar1998Sales
   (OrderID   INT      ,
   CustomerID      varchar(50)         NOT NULL,
   OrderDate      DATETIME      NULL
      CHECK (DATEPART(yy, OrderDate) = 1998),
   OrderMonth      INT
      CHECK (OrderMonth = 3)
)
CREATE TABLE Apr1998Sales
   (OrderID   INT      ,
   CustomerID      varchar(50)         NOT NULL,
   OrderDate      DATETIME      NULL
      CHECK (DATEPART(yy, OrderDate) = 1998),
   OrderMonth      INT
      CHECK (OrderMonth = 4)
)
CREATE TABLE May1998Sales
   (OrderID   INT      ,
   CustomerID      varchar(50)         NOT NULL,
   OrderDate      DATETIME      NULL
      CHECK (DATEPART(yy, OrderDate) = 1998),
   OrderMonth      INT
      CHECK (OrderMonth = 5)
)
CREATE TABLE Jun1998Sales
   (OrderID   INT      ,
   CustomerID      varchar(50)         NOT NULL,
   OrderDate      DATETIME      NULL
      CHECK (DATEPART(yy, OrderDate) = 1998),
   OrderMonth      INT
      CHECK (OrderMonth = 6)
)

insert into Jan1998Sales select OrderID,CustomerID,OrderDate,OrderMonth from Sales98 where OrderMonth = 1
insert into Feb1998Sales select OrderID,CustomerID,OrderDate,OrderMonth from Sales98 where OrderMonth = 2
insert into Mar1998Sales select OrderID,CustomerID,OrderDate,OrderMonth from Sales98 where OrderMonth = 3
insert into Apr1998Sales select OrderID,CustomerID,OrderDate,OrderMonth from Sales98 where OrderMonth = 4
insert into May1998Sales select OrderID,CustomerID,OrderDate,OrderMonth from Sales98 where OrderMonth = 5
insert into Jun1998Sales select OrderID,CustomerID,OrderDate,OrderMonth from Sales98 where OrderMonth = 6

CREATE VIEW Year1998Sales
AS
SELECT * FROM Jan1998Sales
UNION ALL
SELECT * FROM Feb1998Sales
UNION ALL
SELECT * FROM Mar1998Sales
UNION ALL
SELECT * FROM Apr1998Sales
UNION ALL
SELECT * FROM May1998Sales
UNION ALL
SELECT * FROM Jun1998Sales

SELECT *
FROM Year1998Sales
WHERE OrderMonth IN (5,6) AND CustomerID = 'TORTU'--1秒

SELECT *
FROM Sales98
WHERE OrderMonth IN (5,6) AND CustomerID = 'TORTU'--7秒

select count(1) from Year1998Sales --6秒4666950行

select count(1) from Sales98 --1秒4666950行

10.分区表
--建立逻辑分区,以iden为分区依据
CREATE PARTITION FUNCTION callRangePFN(int)
AS
RANGE LEFT FOR VALUES
(
100000,
200000,
300000
)
--建立物理分区
--1.新建文件组
ALTER DATABASE callbook_dw ADD FILEGROUP [callRange10w]
ALTER DATABASE callbook_dw ADD FILEGROUP [callRange20w]
ALTER DATABASE callbook_dw ADD FILEGROUP [callRange30w]
--2.创建物理文件
ALTER DATABASE callbook_dw
ADD FILE
(NAME = N'filecallRange10w',FILENAME = N'c:/callbook_dw/filecallRange10w.ndf',SIZE = 50MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [callRange10w]
go
ALTER DATABASE callbook_dw
ADD FILE
(NAME = N'filecallRange20w',FILENAME = N'd:/callbook_dw/filecallRange20w.ndf',SIZE = 50MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [callRange20w]
go
ALTER DATABASE callbook_dw
ADD FILE
(NAME = N'filecallRange30w',FILENAME = N'e:/callbook_dw/filecallRange30w.ndf',SIZE = 50MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [callRange30w]
--建立分区架构,关联逻辑分区与物理分区
CREATE PARTITION SCHEME callRangeScheme
AS
PARTITION callRangePFN
TO ([callRange10w],[callRange20w],[callRange30w],[PRIMARY])
GO
--建立分区表
CREATE TABLE [dbo].[callRange] 
(
[Startip] [nvarchar](50) NOT NULL,
[Endip] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[stipcompute] [decimal](18, 0) NULL,
[edipcompute] [decimal](18, 0) NULL,
[s_province] [varchar](50) NULL,
[city2] [varchar](50)  NULL,
[iden] [int] NOT NULL
CONSTRAINT callRangeiden
CHECK ([iden] > 0
AND [iden] <= 100000)
)
ON callRangeScheme (iden)
--建立分区表2
CREATE TABLE [dbo].[callRange2] 
(
[Startip] [nvarchar](50) NOT NULL,
[Endip] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[stipcompute] [decimal](18, 0) NULL,
[edipcompute] [decimal](18, 0) NULL,
[s_province] [varchar](50) NULL,
[city2] [varchar](50)  NULL,
[iden] [int] NOT NULL
CONSTRAINT callRangeiden2
CHECK ([iden] > 100000
AND [iden] <= 200000)
)
ON callRangeScheme (iden)
--建立分区表3
CREATE TABLE [dbo].[callRange3] 
(
[Startip] [nvarchar](50) NOT NULL,
[Endip] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[stipcompute] [decimal](18, 0) NULL,
[edipcompute] [decimal](18, 0) NULL,
[s_province] [varchar](50) NULL,
[city2] [varchar](50)  NULL,
[iden] [int] NOT NULL
CONSTRAINT callRangeiden3
CHECK ([iden] > 200000
AND [iden] <= 300000)
)
ON callRangeScheme (iden)
--
--插入数据
INSERT INTO callRange
           ([Startip]
           ,[Endip]
           ,[Country]
           ,[stipcompute]
           ,[edipcompute]
           ,[s_province]
           ,[city2]
   ,[iden])
select
[Startip]
           ,[Endip]
           ,[Country]
           ,[stipcompute]
           ,[edipcompute]
           ,[s_province]
           ,[city2]
   ,[iden]
from dbo.calltest
where iden>0 and iden <=100000

--插入第二表数据
INSERT INTO callRange2
           ([Startip]
           ,[Endip]
           ,[Country]
           ,[stipcompute]
           ,[edipcompute]
           ,[s_province]
           ,[city2]
   ,[iden])
select
[Startip]
           ,[Endip]
           ,[Country]
           ,[stipcompute]
           ,[edipcompute]
           ,[s_province]
           ,[city2]
   ,[iden]
from dbo.calltest
where iden>100000 and iden <=200000
--插入第三表数据
INSERT INTO callRange3
           ([Startip]
           ,[Endip]
           ,[Country]
           ,[stipcompute]
           ,[edipcompute]
           ,[s_province]
           ,[city2]
   ,[iden])
select
[Startip]
           ,[Endip]
           ,[Country]
           ,[stipcompute]
           ,[edipcompute]
           ,[s_province]
           ,[city2]
   ,[iden]
from dbo.calltest
where iden>200000 and iden <=300000
--
ALTER TABLE callRange
ADD CONSTRAINT callRangePK
PRIMARY KEY CLUSTERED (iden)
ON callRangeScheme(iden)

ALTER TABLE callRange2
ADD CONSTRAINT callRange2PK
PRIMARY KEY CLUSTERED (iden)
ON callRangeScheme(iden)

ALTER TABLE callRange3
ADD CONSTRAINT callRange3PK
PRIMARY KEY CLUSTERED (iden)
ON callRangeScheme(iden)
/*
--修改表的分区键
ALTER TABLE [ObjTeaching]
ADD CONSTRAINT [ObjTeaching_PK]
PRIMARY KEY CLUSTERED ([TeachingID], [TeachingDate])
GO
*/
--
--select $partition.callRangePFN(iden),*
--from callRange a
--order by a.iden asc
--
SELECT $partition.callRangePFN(o.iden)
AS [Partition Number]
, min(o.iden) AS [Min call iden]
, max(o.iden) AS [Max call iden]
, count(*) AS [Rows In Partition]
FROM dbo.callRange3 AS o
GROUP BY $partition.callRangePFN(o.iden)
ORDER BY [Partition Number]
GO
验证本机上分区表不快。理论上多cpu会更快。