CREATE TABLE [dbo].[tbs](
[id] [int] NOT NULL PRIMARY KEY CLUSTERED,
[name] [nchar](10) NULL,
[tel] [varchar](11) NULL,
) ON [PRIMARY]
go
insert into tbs select 1,'abc','25445'
union all select 2,'wssd','454'
union all select 4,'niaw','12341'
union all select 5,'vnais','1455'
go
insert into tbs select 3,'vmioas','61586'
go
select * from tbs
go
drop table tbs
go
/*
id name tel
----------- ---------- -----------
1 abc 25445
2 wssd 454
3 vmioas 61586
4 niaw 12341
5 vnais 1455(5 行受影响)
*/
CREATE TABLE [dbo].[tbs](
[id] [int] NOT NULL PRIMARY KEY NONCLUSTERED,
[name] [nchar](10) NULL,
[tel] [varchar](11) NULL,
) ON [PRIMARY]
go
insert into tbs select 1,'abc','25445'
union all select 2,'wssd','454'
union all select 4,'niaw','12341'
union all select 5,'vnais','1455'
go
insert into tbs select 3,'vmioas','61586'
go
select * from tbs
go
drop table tbs
go
/*
id name tel
----------- ---------- -----------
1 abc 25445
2 wssd 454
4 niaw 12341
5 vnais 1455
3 vmioas 61586(5 行受影响)
*/
[id] [int] NOT NULL PRIMARY KEY CLUSTERED,
[name] [nchar](10) NULL,
[tel] [varchar](11) NULL,
) ON [PRIMARY]
go
insert into tbs select 1,'abc','25445'
union all select 2,'wssd','454'
union all select 4,'niaw','12341'
union all select 5,'vnais','1455'
go
insert into tbs select 3,'vmioas','61586'
go
select * from tbs
go
drop table tbs
go
/*
id name tel
----------- ---------- -----------
1 abc 25445
2 wssd 454
3 vmioas 61586
4 niaw 12341
5 vnais 1455(5 行受影响)
*/
CREATE TABLE [dbo].[tbs](
[id] [int] NOT NULL PRIMARY KEY NONCLUSTERED,
[name] [nchar](10) NULL,
[tel] [varchar](11) NULL,
) ON [PRIMARY]
go
insert into tbs select 1,'abc','25445'
union all select 2,'wssd','454'
union all select 4,'niaw','12341'
union all select 5,'vnais','1455'
go
insert into tbs select 3,'vmioas','61586'
go
select * from tbs
go
drop table tbs
go
/*
id name tel
----------- ---------- -----------
1 abc 25445
2 wssd 454
4 niaw 12341
5 vnais 1455
3 vmioas 61586(5 行受影响)
*/
解决方案 »
- SQL Serverv安装问题
- 请教mssql中 not like,not in的区别
- 要清空一个表,并且让自动增长字段的初始值变成1
- 把查询出来的编号导入到EXCEL问题
- 请问sp3补丁怎么打? 我下载了 不知道怎么打!
- 不知道算不算难题,大家来看看,这样的语句怎么写?
- Microsoft Visual Stadio 2010 在与 SQL Server (2008)建立连接时出现与网络相关的或特定于实例的错误
- 用ADOCommand控件删除指定的几条记录,他又没有返回值,请问程序怎么判断他执行是否成功?
- 问一个问题,高手请进!
- 求助,sql语句,包含三个表联接和列转行
- SQL Server 安装过程中出现 “版本变更检查” 警告,如何解决?(急!!!)
- 如何解决SQL2000数据库“置疑”的问题?
再建一个除了名字其他一模一样(包括索引)的表,先把数据插入到这个表中,在用alter table .... switch 连到主表上,眨眼就完,还不影响主表的查询。
下面是一个例子SET NOCOUNT ON--建数据库,包括几个filegroupCREATE DATABASE partitiontest
ON PRIMARY
( NAME = db_dat,
FILENAME = 'c:\test\db.mdf',
SIZE = 2MB),
FILEGROUP FG1
( NAME = FG1_dat,
FILENAME = 'c:\test\FG1.ndf',
SIZE = 2MB),
FILEGROUP FG2
( NAME = FG2_dat,
FILENAME = 'c:\test\FG2.ndf',
SIZE = 2MB),
FILEGROUP FG3
( NAME = FG3_dat,
FILENAME = 'c:\test\FG3.ndf',
SIZE = 2MB),
FILEGROUP FG4
( NAME = FG4_dat,
FILENAME = 'c:\test\FG4.ndf',
SIZE = 2MB)
LOG ON
( NAME = db_log,
FILENAME = 'c:\test\log.ndf',
SIZE = 2MB,
FILEGROWTH = 10% );
GO
USE partitiontest
GO--建partition function
create partition function partfunc(int) as
range left for values(1000, 2000)go--建partition schemecreate partition scheme partscheme as
partition partfunc to
([FG1],[FG2],[FG3])--在partition scheme上建表
create table t1(
id int,
v char(1000) default 'aaaaa',
constraint ci_t1_id primary key clustered (id)
) on partscheme(id)go--插入数据
declare @ii int
set @ii=0while(@ii<=3000)
begin
insert into t1(id) values(@ii)
set @ii=@ii+1
endgo--加一个file group 进原来的partition scheme
alter partition scheme partscheme
next used [FG4]
go
--加一个split point进partition function
alter partition function partfunc()
split range(6000)
go--建新表,并插入数据
CREATE TABLE dbo.t2 (
id INT
, v CHAR(1000) DEFAULT 'bbbb',
CONSTRAINT ci_t2_id PRIMARY KEY CLUSTERED (id)
, CONSTRAINT check_t2 CHECK (ID>6000)
) ON [FG3]
GO
--Insert rows into staging table t2.
DECLARE @i INT
SET @i= 6001
WHILE @i<= 9000
BEGIN
INSERT dbo.t2 (id) SELECT @i
SET @i=@i+10
END
GO
--两表中原来数据的行数
SELECT COUNT(*), MIN(id), MAX(id) FROM dbo.t2
GO
SELECT COUNT(*), MIN(id), MAX(id) FROM dbo.t1
GO--switch
alter table t2
switch to t1 partition 4
go--看看结果是什么SELECT COUNT(*), MIN(id), MAX(id) FROM dbo.t2
GO
SELECT COUNT(*), MIN(id), MAX(id) FROM dbo.t1
GO
如果是 2005 的话, 则可以使用分区表。
另外,2005可以考虑做一个Snapshot Database,主database中的table不要建索引,专门负责插入数据
snapshot database建索引,用於查询。具体我没做过哈,自己去尝试看下。
怎么老是这个问题??
怎么老是这个问题??