对用户表分区 如果要对按用户名边界对用户名的索引进行分区 分区边界该怎么设置呢
对于每个分区 分区是应该存放在单独的文件组中还是全放在一个?如果一个数据库文件组分的比较多 会不会有什么影响
对于每个分区 分区是应该存放在单独的文件组中还是全放在一个?如果一个数据库文件组分的比较多 会不会有什么影响
解决方案 »
- 请教大神下图这样的用什么软件画出来的?
- 急 急 无法还原
- 批量修改字段值
- 请问一下我这个关系的级联删除和更新为什么不能设置成功?谢谢!
- 触发器与事务的问题!
- 求存储过程,用来删除数据库里所有数据表里的数据,我需要完整的代码??谢谢
- 请问为何我的MASTER数据库多了 INFORMATION_SCHEMA 和 system_function_schema 两个用户?
- 用VC打开SQL数据库问题!
- 我用odbc连接局域网内服务器上的sql server 2000,总是说超时,用的是tcp/ip,为什么?
- 怎样在客户端调出服务器上的存储过程的源代码?
- SQL SERVER 的用户存在那个表中?
- 问个drop column的问题
多文件组设置只针对多个独立的磁盘有提高性能的帮助
表中ID字段为int类型,主键,并分区了
此表有普通索引,只对name字段进行了索引,对这个索引进行分区时,可以直接在“存储”中选择ID字段即可。分区索引的代码如下:
CREATE NONCLUSTERED INDEX [IX_TB_Name] ON [dbo].[TB]
(
[Name] ASC
) ON [分区方案名]([ID])
-- 脚本内容:数据库分区实现
-- 示例数据:SQL Server2005自带AdventureWorks
-- 示 例 表: TransactionHistory and TransactionHistoryArchive
-- 创建时间:2008-2-27
-- 创 建 人:高建刚
--**************************************************************************************SET DATEFORMAT mdy;
GOUSE [AdventureWorks];
GO-- 删除原表所有的主外键
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'FK_TransactionHistory_Product_ProductID') AND parent_object_id = OBJECT_ID(N'[Production].[TransactionHistory]'))
ALTER TABLE [Production].[TransactionHistory] DROP CONSTRAINT [FK_TransactionHistory_Product_ProductID];
GOIF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Production].[TransactionHistory]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [Production].[TransactionHistory]
GOIF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Production].[TransactionHistoryArchive]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [Production].[TransactionHistoryArchive];
GO
IF EXISTS (SELECT * FROM [sys].[partition_schemes] WHERE name = 'TransactionsPS1')
DROP PARTITION SCHEME [TransactionsPS1];
GOIF EXISTS (SELECT * FROM [sys].[partition_functions] WHERE name = 'TransactionRangePF1')
DROP PARTITION FUNCTION [TransactionRangePF1]
GOIF EXISTS (SELECT * FROM [sys].[partition_schemes] WHERE name = 'TransactionArchivePS2')
DROP PARTITION SCHEME [TransactionArchivePS2];
GOIF EXISTS (SELECT * FROM [sys].[partition_functions] WHERE name = 'TransactionArchivePF2')
DROP PARTITION FUNCTION [TransactionArchivePF2]
GO-- 创建分区函数
CREATE PARTITION FUNCTION [TransactionRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('10/01/2003', '11/01/2003', '12/01/2003',
'1/01/2004', '2/01/2004', '3/01/2004', '4/01/2004',
'5/01/2004', '6/01/2004', '7/01/2004', '8/01/2004');
GO-- 创建分区方案
CREATE PARTITION SCHEME [TransactionsPS1]
AS PARTITION [TransactionRangePF1]
TO ([PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY]);
GO
-- 将表创建到分区方案中
CREATE TABLE [Production].[TransactionHistory](
[TransactionID] [int] IDENTITY (1, 1) NOT NULL,
[ProductID] [int] NOT NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL CONSTRAINT [DF_TransactionHistory_ReferenceOrderLineID] DEFAULT (0),
[TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_TransactionDate] DEFAULT (GETDATE()),
[TransactionType] [nchar](1) NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_ModifiedDate] DEFAULT (GETDATE()),
CONSTRAINT [CK_TransactionHistory_TransactionType] CHECK (UPPER([TransactionType]) IN ('W', 'S', 'P'))
) ON [TransactionsPS1] (TransactionDate);
GO
--创建分区函数
CREATE PARTITION FUNCTION [TransactionArchivePF2] (datetime)
AS RANGE RIGHT FOR VALUES ('9/01/2003');
GO
--创建分区方案
CREATE PARTITION SCHEME [TransactionArchivePS2]
AS PARTITION [TransactionArchivePF2]
TO ([PRIMARY], [PRIMARY]);
GO--创建表到分区方案中
CREATE TABLE [Production].[TransactionHistoryArchive](
[TransactionID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_ReferenceOrderLineID] DEFAULT (0),
[TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_TransactionDate] DEFAULT (GETDATE()),
[TransactionType] [nchar](1) NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistoryArchive_ModifiedDate] DEFAULT (GETDATE()),
CONSTRAINT [CK_TransactionHistoryArchive_TransactionType] CHECK (UPPER([TransactionType]) IN ('W', 'S', 'P'))
) ON [TransactionArchivePS2] (TransactionDate);
GO --以下为插入数据测试
DECLARE
@retcode INT
,@data_path NVARCHAR(256)SELECT @data_path = SUBSTRING([physical_name], 1, CHARINDEX(N'master.mdf', LOWER([physical_name])) - 1)
FROM [master].[sys].[master_files]
WHERE [database_id] = 1
AND [file_id] = 1;SET @data_path = LEFT(@data_path, PATINDEX('%\MSSQL%', @data_path)) + '90\Tools\Samples\AdventureWorks OLTP\'EXECUTE (N'BULK INSERT [Production].[TransactionHistory] FROM ''' + @data_path + N'TransactionHistory.csv''
WITH (
CODEPAGE=''ACP'',
DATAFILETYPE = ''char'',
FIELDTERMINATOR= ''\t'',
ROWTERMINATOR = ''\n'' ,
KEEPIDENTITY,
TABLOCK
)');EXECUTE (N'BULK INSERT [Production].[TransactionHistoryArchive] FROM ''' + @data_path + N'TransactionHistoryArchive.csv''
WITH (
CODEPAGE=''ACP'',
DATAFILETYPE = ''char'',
FIELDTERMINATOR= ''\t'',
ROWTERMINATOR = ''\n'' ,
KEEPIDENTITY,
TABLOCK
)');
GO
--添加主键
ALTER TABLE [Production].[TransactionHistory] WITH CHECK ADD
CONSTRAINT [PK_TransactionHistory_TransactionID] PRIMARY KEY CLUSTERED
(
[TransactionDate],
[TransactionID]
) ON [TransactionsPS1] (TransactionDate);
GOALTER TABLE [Production].[TransactionHistoryArchive] WITH CHECK ADD
CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY CLUSTERED
(
[TransactionDate],
[TransactionID]
) ON [TransactionArchivePS2] (TransactionDate);
GO
--创建索引
CREATE INDEX [IX_TransactionHistory_ProductID] ON [Production].[TransactionHistory]([ProductID]) ON [TransactionsPS1] (TransactionDate);
CREATE INDEX [IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID] ON [Production].[TransactionHistory]([ReferenceOrderID], [ReferenceOrderLineID]) ON [TransactionsPS1] (TransactionDate);
GOCREATE INDEX [IX_TransactionHistoryArchive_ProductID] ON [Production].[TransactionHistoryArchive]([ProductID]) ON [TransactionArchivePS2] (TransactionDate);
CREATE INDEX [IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID] ON [Production].[TransactionHistoryArchive]([ReferenceOrderID], [ReferenceOrderLineID]) ON [TransactionArchivePS2] (TransactionDate);
GO
--添加外键
ALTER TABLE [Production].[TransactionHistory] ADD
CONSTRAINT [FK_TransactionHistory_Product_ProductID] FOREIGN KEY
(
[ProductID]
) REFERENCES [Production].[Product](
[ProductID]
);
GO
--查看分区情况
SELECT OBJECT_NAME([object_id]), * FROM [sys].[partitions]
WHERE [object_id] = OBJECT_ID('[Production].[TransactionHistory]')
ORDER BY [partition_number], [index_id];
GO
SELECT OBJECT_NAME([object_id]), * FROM [sys].[partitions]
WHERE [object_id] = OBJECT_ID('[Production].[TransactionHistoryArchive]')
ORDER BY [partition_number], [index_id];
GO