首页 > 代码库 > MS SQL Server 2008 R2 常规操作

MS SQL Server 2008 R2 常规操作

2024-08-27 06:20:04   223人阅读

列出当前数据库使用的所有文件

DBCC showfilestats

 转移当前文件组中某个文件的数据到同文件组中的其他文件中

DBCC SHRINKFILE (‘file‘, EMPTYFILE);

 移除文件组中的某个文件

ALTER DATABASE [db] REMOVE FILE [file]

 移除文件组

ALTER DATABASE [db] REMOVE FILEGROUP [FileGroup]

 添加文件组

alter database [db] add filegroup Group1704

 给文件组中添加文件

alter database [db] add file   (    NAME=db_170103,    FILENAME=‘E:\DB\db_170113.ndf‘,    SIZE=3MB,    FILEGROWTH=20MB),(    NAME=db_170104,    FILENAME=‘E:\DBdb_170114.ndf‘,    SIZE=3MB,    FILEGROWTH=20MB)TO filegroup Group1701

 重命名文件名（逻辑），物理名不会重命名

alter database [db] modify file(name=‘db_170103‘,newname=‘db_01‘)

 创建分区函数（时间）

CREATE PARTITION FUNCTION ak_split_time_fun(datetime) AS RANGE LEFT FOR VALUES 
(N‘2016-08-01T00:00:00.000‘, N‘2016-09-01T00:00:00.000‘, N‘2016-10-01T00:00:00.000‘, N‘2016-11-01T00:00:00.000‘, 
N‘2016-12-01T00:00:00.000‘, N‘2017-01-01T00:00:00.000‘, N‘2017-02-01T00:00:00.000‘, N‘2017-03-01T00:00:00.000‘, 
N‘2017-04-01T00:00:00.000‘, N‘2017-05-01T00:00:00.000‘, N‘2017-06-01T00:00:00.000‘, N‘2017-07-01T00:00:00.000‘, 
N‘2017-08-01T00:00:00.000‘)

 创建分区方案（时间）

CREATE PARTITION SCHEME [ak_split_time_fung] AS PARTITION ak_split_time_fun TO 
([Group1608], [Group1609], [Group1610], [Group1611], [Group1612], [Group1701], [Group1702], [Group1703], 
[Group1704], [Group1705], [Group1706], [Group1707], [Group1708], [PRIMARY])

 创建分区函数 (日期)

CREATE PARTITION FUNCTION ak_split_date_fun(date) AS RANGE LEFT FOR VALUES 
(N‘2016-08-01T00:00:00.000‘, N‘2016-09-01T00:00:00.000‘, N‘2016-10-01T00:00:00.000‘, N‘2016-11-01T00:00:00.000‘,
 N‘2016-12-01T00:00:00.000‘, N‘2017-01-01T00:00:00.000‘, N‘2017-02-01T00:00:00.000‘, N‘2017-03-01T00:00:00.000‘, 
N‘2017-04-01T00:00:00.000‘, N‘2017-05-01T00:00:00.000‘, N‘2017-06-01T00:00:00.000‘, N‘2017-07-01T00:00:00.000‘,
 N‘2017-08-01T00:00:00.000‘)

 创建分区方案 (日期)

CREATE PARTITION SCHEME [ak_split_date_fung] AS PARTITION ak_split_date_fun TO 
([Group1608], [Group1609], [Group1610], [Group1611], [Group1612], [Group1701], [Group1702], [Group1703], 
[Group1704], [Group1705], [Group1706], [Group1707], [Group1708], [PRIMARY])

 创建分区函数（guid | uniqueidentifier）

CREATE PARTITION FUNCTION [ak_split_guid_fun]([uniqueidentifier]) AS RANGE LEFT FOR VALUES( ‘00000000-0000-0000-0000-174600000000‘, ‘00000000-0000-0000-0000-2E8B00000000‘, ‘00000000-0000-0000-0000-45D100000000‘, 
‘00000000-0000-0000-0000-5D1700000000‘, ‘00000000-0000-0000-0000-745D00000000‘, ‘00000000-0000-0000-0000-8BA200000000‘, 
‘00000000-0000-0000-0000-A2E800000000‘, ‘00000000-0000-0000-0000-BA2E00000000‘, ‘00000000-0000-0000-0000-D17400000000‘, 
‘00000000-0000-0000-0000-E8B900000000‘)

 创建分区方案 (guid | uniqueidentifier)

CREATE PARTITION SCHEME [ak_split_guid_fung]AS PARTITION [ak_split_guid_fun] TO ([Group1608], [Group1609], [Group1610], [Group1611], [Group1612], [Group1701], 
[Group1702], [Group1703], [Group1704], [Group1705], [PRIMARY])

 对现有分区函数和分区方案添加分区

alter partition scheme ak_dutysource_old_fung next used Group1702goalter partition function ak_dutysource_old_partition_fun() split range(N‘2017-02-01T00:00:00.000‘)

 合并分区函数

alter partition function ak_dutysource_partition_fun() merge range(N‘2016-08-01T00:00:00‘)

 删除表分区（将分区表转换为普通表），主要是将分区应用的索引删除

CREATE CLUSTERED INDEX IX_ak_dutySource_2 ON ak_dutysource(chkTime desc)  WITH ( DROP_EXISTING = ON) ON [PRIMARY]

 删除分区方案

drop partition scheme [ak_dutysource_partition_fang]

 删除分区函数

drop partition function [ak_dutySource_partition_fun]

 应用表分区（删除先前的聚焦索引，重新创建聚焦索引）

BEGIN TRANSACTIONALTER TABLE [dbo].[ak_account_pointrecords] DROP CONSTRAINT [PK_AK_ACCOUNT_POINTRECORDS]ALTER TABLE [dbo].[ak_account_pointrecords] ADD  CONSTRAINT [PK_AK_ACCOUNT_POINTRECORDS] PRIMARY KEY NONCLUSTERED (	[guid] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]CREATE CLUSTERED INDEX [ClusteredIndex_on_ak_split_guid_fung_636172205823253839] ON [dbo].[ak_account_pointrecords](	[userGuid])WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ak_split_guid_fung]([userGuid])DROP INDEX [ClusteredIndex_on_ak_split_guid_fung_636172205823253839] ON [dbo].[ak_account_pointrecords]

 

MS SQL Server 2008 R2 常规操作


联系
我们