第一章 第一节 SQL Server 数据库规划和分区技术

1.1数据库
1.1.1创建
CREATE DATABASE name
[ [ WITH ][ OWNER [=]user_name ]
[ TEMPLATE [=]template ]
[ ENCODING [=]encoding ]
[ LC_COLLATE [=]lc_collate ]
[ LC_CTYPE [=]lc_ctype ]
[ TABLESPACE [=]tablespace_name ]
[ CONNECTION LIMIT [=]connlimit ] ]
1.1.2删除
DROP DATABASE db_name01
1.1.3修改
1.1.3.1查看当前的存放位置
select database_id,name,physical_name AS CurrentLocation,state_desc,size from sys.master_files where database_id=db_id(N'数据库名');
1.1.3.2修改默认的数据库文件存放位置（即时生效）
EXEC xp_instance_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name='DefaultData',
@type=REG_SZ,
@value='E:\MSSQL_MDF\data'
GO
1.1.3.3修改默认的日志文件存放位置（即时生效）
EXEC master..xp_instance_regwrite
@rootkey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\MSSQLServer',
@value_name='DefaultLog',
@type=REG_SZ,
@value='E:\MSSQL_MDF\log'
GO
1.1.3.4修改数据库文件自动增长
ALTER DATABASE db_003MODIFY FILE (NAME=N'db_003a',FILEGROWTH= 10%)
ALTER DATABASE db_016MODIFY FILE (NAME=N'db_016',maxsize= UNLIMITED)
1.1.3.5重命名数据库
EXEC sp_dboption'OldDbName','Single User','TRUE'
EXEC sp_renamedb'OldDbName','NewDbName'
EXEC sp_dboption'NewDbName','Single User','FALSE'
1.1.3.6向数据库添加数据文件或日志文件
USE master
GO
ALTER DATABASE db_test
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE db_test
ADD FILE
(
NAME=test1dat3,
FILENAME='d:\MSSQLSERVER\DATA\t1dat3.ndf',
SIZE= 5MB,
MAXSIZE= 100MB,
FILEGROWTH= 5MB
),
(
NAME=test1dat4,
FILENAME='d:\MSSQLSERVER\DATA\t1dat4.ndf',
SIZE= 5MB,
MAXSIZE= 100MB,
FILEGROWTH= 5MB
)
TO FILEGROUP Test1FG1;
GO
1.1.3.7数据库规划和分区技术（新建）
第一步：首先建立我们要使用的数据库，最重要的是建立多个文件组
我们先新建立四个目录，来组成文件组，一个用来存放主文件的目录：Primary
三个数据文件目录：FG1、FG2、FG3
建立库：
create database Sales on primary
(
name=N'Sales',
filename=N'C:\data\Primary\Sales.mdf',
size=3MB,
maxsize=100MB,
filegrowth=10%
),
filegroup FG1
(
NAME=N'File1',
FILENAME=N'C:\data\FG1\File1.ndf',
SIZE= 1MB,
MAXSIZE= 100MB,
FILEGROWTH= 10%
),
FILEGROUP FG2
(
NAME=N'File2',
FILENAME=N'C:\data\FG2\File2.ndf',
SIZE= 1MB,
MAXSIZE= 100MB,
FILEGROWTH= 10%
),
FILEGROUP FG3
(
NAME=N'File3',
FILENAME=N'C:\data\FG3\File3.ndf',
SIZE= 1MB,
MAXSIZE= 100MB,
FILEGROWTH= 10%
)
LOG ON
(
NAME=N'Sales_Log',
FILENAME=N'C:\data\Primary\Sales_Log.ldf',
SIZE= 1MB,
MAXSIZE= 100MB,
FILEGROWTH= 10%
)
GO
第二步：建立分区函数
目的是用来规范不同数据存放到不同目录的标准，简单讲就是如何分区。

USE Sales
GO
CREATE PARTITION FUNCTION pf_OrderDate(datetime)
AS RANGE RIGHT
FOR VALUES ('2003/01/01','2004/01/01')
GO
第三步：创建分区方案，关联到分区函数
目的就是我们将已经建立好的分区函数组织成一套方案，简单点将就是我们在哪里对数据进行分区。

创建分区方案的Transact-SQL语法：
CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]
参数：
partition_scheme_name：分区方案的名称。

分区方案名称在数据库中必须是唯一的，并且符合标识符规则。

partition_function_name：使用分区方案的分区函数的名称。

分区函数
所创建的分区将映射到在分区方案中指定的文件组。

partition_function_name 必须已经存在于数据库中。

单个分区不能同时包含FILESTREAM 和非FILESTREAM 文件组。

ALL：指定所有分区都映射到在file_group_name 中提供的文件组，或映射到主文件组（如果指定了[PRIMARY]。

如果指定了ALL，则只能指定一个file_group_name。

file_group_name | [ PRIMARY ] [ ,...n]：指定用来持有由
partition_function_name 指定的分区的文件组的名称。

file_group_name 必须已经存在于数据库中。

如果指定了[PRIMARY]，则分区将存储于主文件组中。

如果指定了ALL，则只能指定一个file_group_name。

分区分配到文件组的顺序是从分区 1 开始，按文件组在[,...n] 中列出的顺序进行分配。

在[,...n] 中，可以多次指定同一个file_group_name。

如果n 不足以拥有在
partition_function_name 中指定的分区数，则CREATE PARTITION SCHEME 将失败，并返回错误。

如果partition_function_name 生成的分区数少于文件组数，则第一个未分配的文件组将标记为NEXT USED，并且出现显示命名NEXT USED 文件组的信息。

如果指定了ALL，则单独的file_group_name 将为该partition_function_name 保持它的NEXT USED 属性。

如果在ALTER PARTITION FUNCTION 语句中创建了一个分区，则NEXT USED 文件组将再接收一个分区。

若要再创建一个未分配的文件组来拥有新的分区，请使用ALTER PARTITION SCHEME。

在file_group_name[ 1,...n] 中指定主文件组时，必须像在[PRIMARY] 中那样分隔PRIMARY，因为它是关键字。

Use Sales
go
create partition scheme ps_OrderDate
as partition pf_OrderDate
to(FG2,FG2,FG3)
go
第四步：创建分区表
创建表并将其绑定到分区方案上。

我们首先建立两个表，一张原始表另一张用来归档数据，保存归档数据。

Use Sales
go
create table Orders
(
OrderID int identity(10000,1),
OrderDate datetime not null,
CustomerID int not null,
constraint PK_Orders primary key(OrderID,OrderDate)
)
on ps_OrderDate(OrderDate)
go
create table OrdersHistory
(
OrderID int identity(10000,1),
OrderDate datetime not null,
CustomerID int not null,
constraint PK_OrdersHistory primary key(OrderID,OrderDate)
)
on ps_OrderDate(OrderDate)
go
首先，因为是用2003年1月1号作为区分点的，我们先向数据表中写入2002
年的规范数据
USE Sales
GO
INSERT INTO dbo.Orders(OrderDate,CustomerID)VALUES ('2002/6/25', 1000)
INSERT INTO dbo.Orders(OrderDate,CustomerID)VALUES ('2002/8/13', 1000)
INSERT INTO dbo.Orders(OrderDate,CustomerID)VALUES ('2002/8/25', 1000)
INSERT INTO dbo.Orders(OrderDate,CustomerID)VALUES ('2002/9/23', 1000)
GO
同样我们写入2003年四条数据
USE Sales
GO
INSERT INTO dbo.Orders(OrderDate,CustomerID)VALUES ('2003/6/25', 1000)
INSERT INTO dbo.Orders(OrderDate,CustomerID)VALUES ('2003/8/13', 1000)
INSERT INTO dbo.Orders(OrderDate,CustomerID)VALUES ('2003/8/25', 1000)
INSERT INTO dbo.Orders(OrderDate,CustomerID)VALUES ('2003/9/23', 1000)
GO
我们来查看这些数据是否完整录入：
因为OrdersHistory表我们还没有归档数据，所以为空。

我们来分条件查询下：
我们来查询分区表Orders的第一个分区，代码如下：
select*from dbo.orders where$PARTITION.pf_Orderdate(orderdate)=1 可以看到我们查询出来的数据全部为2002年的，也就是说在第一分区中我们
存入的数据都是小于2003年，按照此推断2003年的数据，就应该存在第二分区中：
select*from dbo.orders where$PARTITION.pf_Orderdate(orderdate)=2 结果如我们所料，我们可以按照这个分区进行分组来查看各个分区的数据行多少，代码如下：
select$partition.pf_OrderDate(OrderDate)as Patition,COUNT(*) countRow from dbo.Orders
group by$partition.pf_OrderDate(OrderDate)
2、归档数据
假如现在是2003年年初，那么我们就可以把2002您所有的交易记录归档到我们刚才建立的历史订单表HistroryOrder中。

Use Sales
go
alter table orders switch partition 1 to ordersHistory partition 1 go
现在我们再重新查看这两张表的数据：
select*from orders
select*from ordershistory
这时候Orders表只剩下2003年的数据，而OdersHistory表中包含了2002年的数据。

当然如果到了2004年年初，我们就可以归档2003年的所有交易数据。

Use Sales
go
alter table orders switch partition 2 to ordersHistory partition 2 go
这里需要注意的是我们按照区进行数据修改的时候，必须是同一种分区函数下的分区表进行操作，并且分区结构相对应，如果不这样会报错，例如：
3、添加分区
当我们需要新添加分区的时候，我们需要修改分区方案，比如现在我们到了2005年年初，我们需要为2005年的交易记录准备分区，就需要添加分区：USE[master]
GO
ALTER DATABASE[Sales]ADD FILEGROUP[FG4]
GO
ALTER DATABASE[Sales]ADD FILE (NAME=N'File4',FILENAME=
N'C:\data\FG4\File4.ndf',SIZE= 3072KB,FILEGROWTH= 1024KB)TO FILEGROUP[FG4]
GO
我们新建立了一个文件组，然我们同样按照上面的方法，进行修改分区函数和方案：
use Sales
go
alter partition scheme ps_OrderDate next used[FG4]
alter partition function pf_OrderDate()split range('2005/01/01') go
我们这里用alter partition Scheme ps_OrderDate Next Used FG4用来指定新分区的数据在那个文件，而这里split range是创建新分区的关键语法。

4、删除分区
删除分区又称合并分区，简单讲就是两个分区的数据进行合并，比如我们想合并2002年的分区和2003年的分区到一个分区，我们可以用如下的代码：use Sales
go
alter partition function pf_OrderDate()merge range('2003/01/01') go
也就是将2003年这个分区点去掉，里面分区里面的数据会自动合并到一起。

SELECT*
FROM dbo.OrdersHistory
WHERE$PARTITION.pf_OrderDate(OrderDate)= 2
结果一行数据都没返回，事实就这样，因为OrderHistroy表中只存储了2002和2003年的历史数据，在没有合并分区之前，执行上面的代码肯定会查询出2003年的数据，但是合并了分区之后，上面代码实际查询的是第二个分区中2004年的数据。

不过我们改成如下代码：
SELECT*
FROM dbo.OrdersHistory
WHERE$PARTITION.pf_OrderDate(OrderDate)= 1
便会查询出8行数据，包括2002年和2003年的数据，因为合并分区后2002年和2003年的数据都成了第1分区的数据了。

1.1.3.8普通表转为分区表
如果数据库已经投入使用一段时间了，但是当时没有创建创建分区表，现在我们需要做的是将普通表转换成分区表，但是并不能影响我们数据库里
面的数据，那么我们应该如何做呢？只需在该表上创建一个聚集索引，并在该聚集索引中使用分区方案即可。

说的很简单，但是在实现实现可就没有那么容易了，因为你的数据库中存在主键，外键等约束关系，那么我们在将普通表转换成分区表时，首先就需要解决这些问题。

我们知道分区表时某个字段为分区条件的，除了这个字段之外的其他字段是不能创建聚集索引的，所以我们将普通表转换成分区表时，必须要删除聚集索引，然后再重新创建一个新的聚集索引，在该聚集索引中使用分区方案。

但是我们需要修改的tabl_name表中的orderId既是主键又是聚集索引，而且还是其它表的外键。

因此，我们只能先删除外键关联，再删除主键，然后重新创建orderId为主键，但是设置为非聚集索引，然后将我们的sellTi me字段设置为聚集索引，最后添加上我们的外键约束，至此普通表转换成分区表的工作结束。

一、创建文件组（同上面新建方案）
二、创建文件（同上面新建方案）
三、删除约束，主键，聚集索引并重建
--查看外键约束
use db_test
EXEC sp_helpconstraint tabl_name
--删除外键约束
ALTER table tabl_name DROP constraint FK_tabl_name_t_User
--删掉主键
ALTER TABLE tabl_name DROP constraint PK_tabl_name
--创建主键，但不设为聚集索引
ALTER TABLE tabl_name ADD CONSTRAINT PK_tabl_name PRIMARY KEY NONCLUSTERED(
orderId ASC
)
ON[PRIMARY]
--添加删除掉的外键约束（具体自己根据实际情况自己实现）
四、创建一个分区函数
--创建一个分区函数
CREATE PARTITION FUNCTION part_month_func_range_test(datetime) AS RANGE RIGHT FOR VALUES (
'2017-7-1 00:00:00',
'2017-8-1 00:00:00',
'2017-8-1 00:00:00',
'2017-9-1 00:00:00',
'2017-10-1 00:00:00',
'2017-11-1 00:00:00',
)
五、创建一个分区方案
CREATE PARTITION SCHEME partsch_CX
AS PARTITION part_month_func_range_test
TO (
FC201706,
FC201707,
FC201708,
FC201709,
FC201710,
FC201711,
FC201712
)
注意：方案中文件组比函数中要多一个
六、按分区方案创建聚集索引
--创建一个新的聚集索引，在该聚集索引中使用分区方案
CREATE CLUSTERED INDEX Ctabl_name ON tabl_name(sellTime)
ON partsch_CX([sellTime])--partsch_CX为分区方案
为表创建了一个使用分区方案的聚集索引之后，该表就变成了一个分区表了。

七、查看分区相关元数据信息
1)查看分区函数，分区方案，边界值点
我们可以通过三个系统视图来查看我们的分区函数，分区方案，边界值点等。

select*from sys.partition_functions
select*from sys.partition_range_values
select*from sys.partition_schemes
转换成功之后，我们可以通过下面代码查看每个分区表中的记录数：
2)统计所有分区表中的记录总数
select$PARTITION.partfun_CX([sellTime])as分区编
号,count(orderId)as记录数from tabl_name GROUP by
$PARTITION.partfun_CX([sellTime])
我们还可以通过下面的代码，查看数据库库中的数据在哪个分区中：
3)查看数据库表中的数据在哪个分区中
select$PARTITION.partfun_CX('2010-10-1')--查询年月日的数据在哪个
分区中
select$PARTITION.partfun_CX('2011-01-1')--查询年月日的数据在哪个
分区中如果你想比较一下我们使用分区方案之后和之前程序有多少效率提高，我们可以通过下面的语句来看看一下脚本的执行时间就OK了，我经过测试的数据是快了.017秒，一方面由于我们的测试数据量比较小，另一方面我的机器配置还是蛮不错的。

4)查看SQL脚本的执行时间
select getDate()
select*from tabl_name
select getDate()
5) 查看数据库分区信息
SELECT OBJECT_NAME(p.object_id)AS ObjectName,
AS IndexName,
p.index_id AS IndexID,
AS PartitionScheme,
AS Partitionfunctions,
p.partition_number AS PartitionNumber,
AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right
WHEN 1 THEN'RIGHT'
ELSE'LEFT'END AS Range,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i
ON i.object_id=p.object_id AND i.index_id=p.index_id JOIN sys.data_spaces AS ds
ON ds.data_space_id=i.data_space_id
JOIN sys.partition_schemes AS ps
ON ps.data_space_id=ds.data_space_id
JOIN sys.partition_functions AS pf
ON pf.function_id=ps.function_id
JOIN sys.destination_data_spaces AS dds2
ON dds2.partition_scheme_id=ps.data_space_id AND
dds2.destination_id=p.partition_number
JOIN sys.filegroups AS fg
ON fg.data_space_id=dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
ON ps.function_id=prv_left.function_id AND
prv_left.boundary_id=p.partition_number- 1
LEFT JOIN sys.partition_range_values AS prv_right
ON ps.function_id=prv_right.function_id AND
prv_right.boundary_id=p.partition_number
WHERE OBJECTPROPERTY(p.object_id,'ISMSShipped')= 0
--AND OBJECT_NAME(p.object_id)='tabl_name' --查看使用了分区的数据表相关信息
UNION ALL
SELECT
OBJECT_NAME(p.object_id)AS ObjectName,
AS IndexName,
p.index_id AS IndexID,
NULL AS PartitionScheme,
NULL AS Partitionfunctions,
p.partition_number AS PartitionNumber,
AS FileGroupName,
NULL AS LowerBoundaryValue,
NULL AS UpperBoundaryValue,
NULL AS Boundary,
p.rows AS Rows
FROM sys.partitions AS p
JOIN sys.indexes AS i ON i.object_id=p.object_id AND i.index_id= p.index_id
JOIN sys.data_spaces AS ds ON ds.data_space_id=i.data_space_id JOIN sys.filegroups AS fg ON fg.data_space_id=i.data_space_id WHERE OBJECTPROPERTY(p.object_id,'ISMSShipped')= 0
--AND OBJECT_NAME(p.object_id)='tabl_name'
ORDER BY ObjectName,IndexID,PartitionNumber
1.1.4数据库空间管理
1.1.4.1合理组织数据库文件和日志文件
1.主文件组完全独立,只存放系统对象,所有的用户对象都不在主文件组中,主文件组也不应该设为默认文件组,将系统对象和用户对象分开可以获得更好的性能
2.如果有多块硬盘,可以将每个文件组中的文件分配到每个硬盘上,这样可实现分布式磁盘I/O,提高读写速度
3.将访问频繁的表及其索引放到一个独立的文件组中,这样可以提高读取数据和索引的速度.
4.将访问频繁的包含TEXT和IMAGE数据类型的列的表放到一个单独的文件组中,最好将TEXT 和IMAGE放在一个独立的硬盘中
5.将事务日志文件放到一个独立的硬盘上,千万不要和DATEFILE共用一个硬盘;日志操作属于密集型操作.
6.将'只读'表单放到一个单独的文件组中,同样,'只写'也是
7.不要过度使用'自动增长',设置自动增长值为一个合适的值,如:一周,同样,'自动收缩'也是如此
1.1.4.2文件的分配方式及文件空间检查方法
--------------------sp_spaceused------------------------------
-- 不加任何参数，sp_spaceused返回当前数据库的空间使用情况sp_spaceused
-- 使用表名作为参数，sp_spaceused返回指定表的空间使用情况sp_spaceused"Person.Address"
go
1.1 数据文件分配
--------------------dbcc page------------------------------
--语法：DBCC Page(<db_id>, <file_id>, <page_id>, <format_id>) -- db_id的获得
-- 返回所有数据库的ID信息
sp_helpdb
-- 返回当前数据库的ID
select DB_ID()
-- 返回指定名称数据库的ID
select DB_ID('AdventureWorks')
-- file_id的获得
sp_helpfile
-- 返回指定名称表的ID
select file_id('AdventureWorks_Data')
-- 查看某个page的信息
-- 先打开dbcc开关
dbcc traceon(3604)
-- 查看指定页面的信息
dbcc page(5,1,3230,3)
1.2 数据文件空间使用计算方法
-- 按照区得到数据库文件大小
dbcc showfilestats
1.3 查看日志使用情况
dbcc sqlperf(logspace)
1.4 案例：通过脚本监视tempdb空间使用
1. 脚本1
-- 监视tempdb存储空间使用
-- 在连接B中，选择Query-Results To-Results to file
-- 然后运行如下脚本
use tempdb
-- 每隔秒钟运行一次，直到用户手工终止脚本运行
while 1=1 begin
select getdate()
-- 从文件级看tempdb使用情况
dbcc showfilestats
-- Query 1
-- 返回所有做过空间申请的session信息
Select'Tempdb'as DB,getdate()as Time,
SUM(user_object_reserved_page_count)*8 as user_objects_kb,
SUM(internal_object_reserved_page_count)*8 as
internal_objects_kb,
SUM(version_store_reserved_page_count)*8 as version_store_kb, SUM(unallocated_extent_page_count)*8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id= 2 -- Query 2
-- 这个管理视图能够反映当时tempdb空间的总体分配
SELECT t1.session_id,
t1.internal_objects_alloc_page_count,
er_objects_alloc_page_count,
t1.internal_objects_dealloc_page_count,
er_objects_dealloc_page_count,
t3.*
from sys.dm_db_session_space_usage t1,
-- 反映每个session累计空间申请
sys.dm_exec_sessions as t3
-- 每个session的信息
where
t1.session_id=t3.session_id
and(t1.internal_objects_alloc_page_count>0
or er_objects_alloc_page_count>0
or t1.internal_objects_dealloc_page_count>0
or er_objects_dealloc_page_count>0)
-- Query 3
-- 返回正在运行并且做过空间申请的session正在运行的语句
SELECT t1.session_id,
st.text
from sys.dm_db_session_space_usage as t1,
sys.dm_exec_requests as t4
CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle)AS st
where t1.session_id=t4.session_id
and t1.session_id>50 and(t1.internal_objects_alloc_page_count>0
or er_objects_alloc_page_count>0
or t1.internal_objects_dealloc_page_count>0
or er_objects_dealloc_page_count>0)
waitfor delay'0:0:1'
end
--脚本1结束
2. 脚本2
-- 在连接A中运行如下脚本
-- 以下连接会用不同的方式使用Tempdb
select@@spid
go
use adventureworks
go
select getdate()
go
select*into#mySalesOrderDetail
from Sales.SalesOrderDetail
-- 创建一个temp table
-- 这个操作应该会申请user objects page
go
waitfor delay'0:0:2'
select getdate()
go
drop table#mySalesOrderDetail
-- 删除一个temp table
-- 这个操作后user object page数量应该会下降
go
waitfor delay'0:0:2'
select getdate()
go
select top 100000 *from
[Sales].[SalesOrderDetail]
INNER JOIN[Sales].[SalesOrderHeader]
ON[Sales].[SalesOrderHeader].[SalesOrderID]=
[Sales].[SalesOrderHeader].[SalesOrderID];
-- 这里做了一个比较大的join.
-- 应该会有internal objects的申请.
go
select getdate()
-- join 语句做完以后internal objects page数目应该下降
go
-- 脚本2结束
1.5日志现在使用情况
dbcc sqlperf(logspace)
go
select
name,recovery_model_desc,log_reuse_wait,,log_reuse_wait_desc from sys.dababases
go
1.1.4.3日志文件不停增长
1.3.1 日志文件里到底有什么
--首先，我们在范例数据库AdventureWorks里面创建一个只有一个int类型字段的表格。

然后将数据库日志文件清空。

接着运行DBCC Log命令。

找到这时日志文件的最后一条记录。

use adventureworks
go
create table a(a int)
go
checkpoint
go
backup log adventureworks with truncate_only
go
dbcc log(5,3)
-- 5是adventureworks数据库的编号，每个SQL Server可能不同
-- 可以用sp_helpdb来查到数据库编号
go
--接着，我们在表格里插入一条记录。

insert into a values (1)
go
dbcc log(5,3)
go
--我们再插一条记录。

insert into a values (100)
go
dbcc log(5,3)
go
--可以看到新的三条记录（图-27）。

新的记录有不同的LSN编号。

update a set a= 2
go
1.3.3 案例：日志增长原因定位
-- 检查日志现在使用情况和数据库状态
dbcc sqlperf(logspace)
go
select name,recovery_model_desc,
log_reuse_wait,log_reuse_wait_desc from sys.databases
go
-- 检查最老的活动事务
dbcc opentran
go
SELECT st.text,t2.*
from
sys.dm_exec_sessions as t2,
sys.dm_exec_connections as t1
CROSS APPLY sys.dm_exec_sql_text(t1.most_recent_sql_handle)AS st where
t1.session_id=t2.session_id
and t1.session_id>50
1.1.4.4修改数据库物理文件的存在位置
前言
大家应该都知道SQL Server创建新库时，默认会把数据存放在C盘中，一旦数据库中的存储数据多了以后，C盘的空间就会所剩无几。

解决方案是将存放数据的物理文件迁移到其他盘。

具体流程为：
1、将现有的数据库脱机
ALTER DATABASE DB1SET OFFLINE WITH ROLLBACK IMMEDIATE;
2、将数据库文件移到新的位置
文件复制完成以后需要：右键-属性-安全-在组或用户名处添加Authenticated Users-更改该组权限为完全权限，否则接下来的操作会报错
中间可能存在的问题：
消息 5120，级别 16，状态 101，第 17 行
无法打开物理文件“D:\MSSQL\DATA\testdb.mdf”。

操作系统错误5:“5(拒绝访
问。

)”。

消息 5120，级别 16，状态 101，第 17 行
无法打开物理文件“D:\MSSQL\DATA\testdb _log.ldf”。

操作系统错误5:“5(拒绝访问。

)”。

消息 5181，级别 16，状态 5，第 17 行
无法重新启动数据库“ctrip”。

将恢复到以前的状态。

消息 5069，级别 16，状态 1，第 17 行
ALTER DATABASE 语句失败。

3、修改数据库关联文件的指向
ALTER DATABASE DB1MODIFY FILE(NAME=DB1,FILENAME=
'X:\SQLServer\DB1.mdf');
ALTER DATABASE DB1MODIFY FILE(NAME=DB1_Log,FILENAME=
'X:\SQLServer\DB1_Log.ldf');
--注:先使用sp_helpdb DB1,查看数据库的逻辑名和物理文件名,对应修改上面语
句中的内容
4、将数据库进行联机
ALTER DATABASE DB1SET ONLINE;
1.1.4.5查看所有用户表所在的文件组
SELECT o.[name],o.[type],i.[name],i.[index_id],f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id=f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id]=o.[object_id]
WHERE o.type='u'and i.index_id in(0,1)
order by
1.1.4.6SQLSERVER将一个文件组的数据移动到另一个文件组
案例一:
--对现有数据库增加文件组和文件
USE master
GO
ALTER DATABASE db_test ADD FILEGROUP account;
ALTER DATABASE db_test ADD FILEGROUP SEC;
ALTER DATABASE db_test ADD FILEGROUP sale;
ALTER DATABASE db_test ADD FILEGROUP sort;
ALTER DATABASE db_test ADD FILEGROUP base;
ALTER DATABASE db_test ADD FILEGROUP auditing;
ALTER DATABASE db_test ADD FILEGROUP img;
GO
ALTER DATABASE db_test ADD FILE
(
NAME=account,
FILENAME='D:\工作资料\database\db_test\db_test_account.mdf', SIZE= 5MB,
MAXSIZE= 100MB,
FILEGROWTH= 5MB
)
TO FILEGROUP account;
GO
ALTER DATABASE db_test ADD FILE
(
NAME=C2,
FILENAME='D:\工作资料\database\db_test\db_test_C2.mdf',
SIZE= 5MB,
MAXSIZE= 100MB,
FILEGROWTH= 5MB
)
TO FILEGROUP SEC;
GO
ALTER DATABASE db_test ADD FILE
(
NAME=Csale,
FILENAME='D:\工作资料\database\db_test\db_test_Csale.mdf', SIZE= 5MB,
MAXSIZE= 100MB,
FILEGROWTH= 5MB
)
TO FILEGROUP sale;
GO
ALTER DATABASE db_test ADD FILE
(
NAME=Csort,
FILENAME='D:\工作资料\database\db_test\db_test_Csort.mdf', SIZE= 5MB,
MAXSIZE= 100MB,
FILEGROWTH= 5MB
)
TO FILEGROUP sort;
GO
ALTER DATABASE db_test ADD FILE
(
NAME=Cbase,
FILENAME='D:\工作资料\database\db_test\db_test_Cbase.mdf', SIZE= 5MB,
MAXSIZE= 100MB,
FILEGROWTH= 5MB
)
TO FILEGROUP base;
GO
ALTER DATABASE db_test ADD FILE
(
NAME=auditing,
FILENAME='D:\工作资料\database\db_test\db_test_auditing.mdf', SIZE= 5MB,
MAXSIZE= 100MB,
FILEGROWTH= 5MB
)
TO FILEGROUP auditing;
GO
ALTER DATABASE db_test ADD FILE
(
NAME=img,
FILENAME='D:\工作资料\database\db_test\db_test_img.mdf',
SIZE= 5MB,
MAXSIZE= 100MB,
FILEGROWTH= 5MB
)
TO FILEGROUP img;
GO
/*由于每个数据库中同样一个表，但存在聚集索引名不同，或者非聚集索引个数或者键值不同的情况，所以必须根据实际的数据库进行增删索引*/
--第步:所有需要迁移文件组的表的索引及键值写入临时表，下列表为标准库中已规划好的文件组，可自行修改增加
if object_id('tempdb..#sql_clustered')is not null drop table [dbo].[#sql_clustered]
create table#sql_clustered(sql varchar(500))
if object_id('tempdb..#index_temp')is not null drop table
[dbo].[#index_temp]
select object_name(a.object_id)as table_name, as
column_name, as index_name,c.type_desc,a.index_column_id
into#index_temp
from sys.index_columns as a,sys.columns b,sys.indexes c
where a.object_id=b.object_id and a.column_id=b.column_id and
a.object_id=c.object_id and a.index_id=c.index_id
and object_name(a.object_id)in('tab_name01','tab_name02','
tab_name03','tab_name04','tab_name05','tab_name06')
order by object_name(a.object_id),,a.index_column_id
---重建聚集索引
declare@table_name varchar(40)
declare@clu_column_name varchar(200)
declare@noclu_column_name varchar(200)
declare@index_noclu varchar(100)
declare clu_cursor cursor local for select distinct table_name from #index_temp where type_desc='CLUSTERED'
open clu_cursor
fetch next from clu_cursor into@table_name
while@@fetch_status= 0
begin
--ALTER TABLE c_auditing DROP CONSTRAINT PK__c_auditing__3EC74557 --拼接聚集索引主键
set@clu_column_name=''
select@clu_column_name=@clu_column_name+column_name+','
from#index_temp where type_desc='CLUSTERED'and
table_name=@table_name
select
@clu_column_name=SUBSTRING(@clu_column_name,1,datalength(@clu_col umn_name)-1)
insert into#sql_clustered(sql)
select distinct'ALTER TABLE ['+table_name+'] DROP CONSTRAINT '+index_name
from#index_temp where type_desc='CLUSTERED'and
table_name=@table_name
union all
select distinct'ALTER TABLE ['+table_name+'] ADD CONSTRAINT '+ index_name+' PRIMARY KEY CLUSTERED ('+@clu_column_name+') ON ' +case when table_name='tab_name01'then'[auditing]'
when table_name in(' tab_name02')then'SEC'
when table_name in(' tab_name03')then'account'
when table_name in(' tab_name04')then'sale'
when table_name in(' tab_name05')then'base'
when table_name in(' tab_name06')then'img'
else'PRIMARY'end
from#index_temp where type_desc='CLUSTERED'and
table_name=@table_name
--------------------------------------begin重建非聚集索引
----------------------------------------------------------- insert into#sql_clustered(sql)
select distinct'DROP INDEX ['+index_name+'] ON ['+table_name +']'
from#index_temp where type_desc='NONCLUSTERED'and
table_name=@table_name
declare noclu_cursor cursor local for select distinct index_name from#index_temp
where type_desc='NONCLUSTERED'and table_name=@table_name
open noclu_cursor
fetch next from noclu_cursor into@index_noclu
while@@fetch_status= 0
begin
--拼接聚集索引主键
--select * from #index_temp
--declare @noclu_column_name varchar(100)
set@noclu_column_name=''
select
@noclu_column_name=@noclu_column_name+column_name+','
from#index_temp where type_desc='NONCLUSTERED'
--'c_auditing' and index_name='I_c_auditing_execdate'--
and table_name=@table_name and index_name=@index_noclu
select
@noclu_column_name=SUBSTRING(@noclu_column_name,1,datalength(@noc
lu_column_name)-1)
--select @noclu_column_name
insert into#sql_clustered(sql)
select distinct'CREATE NONCLUSTERED INDEX ['+index_name+'] ON dbo.['+table_name+']('+@noclu_column_name+') ON '
+case when table_name='tab_name01'then'[auditing]'
when table_name in('tab_name02')then'SEC'
when table_name in('tab_name03')then'account'
when table_name in('tab_name04')then'sale'
when table_name in('tab_name05')then'base'
when table_name in('tab_name06')then'img'
else'PRIMARY'end
from#index_temp
where type_desc='NONCLUSTERED'and table_name=@table_name and index_name=@index_noclu
IF@@error<> 0
BEGIN
close noclu_cursor
deallocate noclu_cursor
ROLLBACK
RAISERROR 20001 '生成非聚集索引失败'
RETURN
END
fetch next from noclu_cursor into@index_noclu
END
close noclu_cursor
deallocate noclu_cursor
--------------------------------------end重建非聚集索引
----------------------------------------------------------- IF@@error<> 0
BEGIN
close clu_cursor
deallocate clu_cursor
ROLLBACK
RAISERROR 20001 '生成聚集索引脚本失败'
RETURN
END
fetch next from clu_cursor into@table_name
END
close clu_cursor
deallocate clu_cursor
--select * from #sql_clustered
----执行创建语句-----
declare@sql varchar(500)
declare mov_index_cursor cursor local for select sql from
#sql_clustered
open mov_index_cursor
fetch next from mov_index_cursor into@sql
while@@fetch_status= 0
begin
exec(@sql)
IF@@error<> 0
BEGIN
close mov_index_cursor
deallocate mov_index_cursor
ROLLBACK
RAISERROR 20001 '迁移文件组失败'
RETURN
END
fetch next from mov_index_cursor into@sql END
close mov_index_cursor
deallocate mov_index_cursor。