Ⅰ sql 超多數據怎麼分區好點
這樣的項目少於百萬級別，就別做了，這百萬還只是硬體的上的投入。這幾天經常看到你問類似的問題。說實話，這樣的性能要求，若純靠技術手段實現。俺只能呵呵呵了
Ⅱ 什麼是SQL Server自動化管理分區設計方案
（圖1：整體概念圖） 數據流經過分區方案，被分配到不同的分區中，從圖中可以看出，分區是可以重復利用的，後台有一個所謂的自動化切換分區的作業在跑，目的就是如果重復 利用這些分區。這里的PRIMARY目的就是說明它與其它文件組的一個平級關系，而且我們在做交換分區時候也會用到PRIMARY，需要事先分配足夠的空間； （圖2：自動化設計圖） 自動化切換分區作業的邏輯處理，其中分區管理表的設計是比較重要的，它的靈活度關繫到整個自動化的效果； 這個邏輯有以下幾個特點： 1、 分區的索引進行存儲位置對齊；其它索引在創建時就使用了分區方案，索引數據跟隨分區數據一起存儲在分區中； 2、分區管理表，包含了分區記錄數預警設計，在Id達到這個值後就會進行交換分區； 3、分區管理表，FileGroup_String欄位的數據可以通過SQL腳本自動化生成，條件就是分區文件組名稱需要有規律； 4、 臨時表是創建在PRIMARY主分區上，跟原表使用相同的分區方案；需要事先給PRIMARY分配大於或者等於一個分區文件大小的空間，這樣在交換分區的時候就不用增量為主分區分配數據空間； 5、 交換舊數據到臨時表，使用下面的語句可以把數據交換到相同的分區中編號，這樣可以應對臨時表就是一個歷史表，而好處就是歷史表也同樣使用了分區； ALTER TABLE ［tb］ SWITCH PARTITION @PARTITION_num TO ［Temp_tb］ 6、這里需要先修改分區方案，才能修改分區函數，這個跟創建分區函數與分區方案的順序是剛好相反的； 分區管理表（PartitionManage）是能應對比較多的情況： 1、 比如我們可以修改預警值（Change_Value），讓數據提早進入交換分區； 2、比如我們可以修改分區值（Part_Value），達到調整分區間隔的目的； 3、比如我們可以修改分區文件組名稱（FileGroup_String），達到跳級文件組的目的；通過修改分區管理表來設置分區值與分區文件組的對應關系； 4、再比如，我們一次性修改了分區方案和分區函數，已經去到很後面的分區值了，那麼我們只要設置這些分區值的狀態（IsDone）為1（True）就可以解決； 5、記錄了進行交換分區的時間（UpdateTime），方便查詢； 不知道朋友們看到這個標題是會陌生還是熟悉呢？今天帶大家一起看看一起介紹下SQL Server自動化管理分區設計方案； 一、設計說明 設計這個自動化的目的是想要交替、重復地使用固定的幾個分區（分區編號01~05）來保存數據，當最後一個分區就是快滿的時候，我們會把最舊數據的分區的數據清空出分區，新數據就可以使用老分區空間了； 應用這個自動化管理分區的環境是有些限制的； 其一：分區的數據是呈現遞增的，比如分區欄位是自增Id值，或者是以日期作為分區； 其二：可以接受歷史 數據被移除分區表帶來的問題； 其三：一天進庫的數量不應大於分區管理表PartitionManage中Part_Value與Change_Value 的差，因為我們作業執行的頻率是1天，不過你可以調整Change_Value或者作業的執行頻率；二、看圖說話
Ⅲ 如何創建SQL Server 2005表分區
創建一個分區表首先要定義你用來映射表內分區的分區函數。在下面定義的分區函數中，我將使用三個分區，每個分區對應於SalesHistoryArchive表中的每一種產品類型，這個表將在稍後定義。基本上，這些分區會把SalesHistoryArchive檔案表劃分成三個不同的表，它們由SQL Server自動維護。CREATE PARTITION FUNCTION [pf_Proct_Partition](VARCHAR(10)) AS RANGE LEFTFOR VALUES (N'BigScreen', N'Computer', N'PoolTable')定義函數的范圍決定分區值屬於哪一個邊界。RNAGE LEFT：指定分區值將小於或等於在分區函數中定義的值。在上面使用的分區函數中一共建立了四個分區。所有名稱小於或等於『BigScreen』的產品將映射到第一個分區。任何名稱大於『BigScreen』但小於或等於『Computer』的產品將映射到第二個分區；諸如此類。RANGE RIGHT：指定分區值將小於在分區函數中定義的值。在上面使用的分區函數中一共建立了四個分區。所有名稱小於『BigScreen』的產品將映射到第一個分區。任何名稱大於或等於『BigScreen』但小於或等於『Computer』的產品將映射到第二個分區；諸如此類。建好分區函數後，現在我需要建立分區方案。我將把例子中的所有分區都映射到主文件組中。如果我希望將分區映射到不同的文件組，我會按文件組列表的順序加入文件組名稱。下面是創建分區方案的代碼：CREATE PARTITION SCHEME [ps_Proct_Scheme] AS PARTITION[pf_Proct_Partition]ALL TO ([PRIMARY])現在我建立需要分區的表，我可以創建SalesHistoryArchive表並在其中載入數據。在CREATE TABLE語句末尾，分區方案使用表中的一個欄位名告訴SQL Server如何映射需要分區的表中的數據。查看列表A中的代碼樣本。我需要對數據進行一些查詢，保證分區正常運行。下面的查詢返回SalesHistoryArchive表的所有行，並使用$partition函數指出返回的行屬於哪個分區：SELECT $partition.[pf_Proct_Partition](Proct), *FROM SalesHistoryArchive這個查詢返回所有映射到SalesHistoryArchive表中的分區：SELECT * From sys.partitionsWHERE OBJECT_NAME(object_id) = 'SalesHistoryArchive'接下來…在後面的文章中，我將解釋如何修改表中的這些分區，從而在其中增加新數據，並非常高效地刪除舊數據。
Ⅳ 如何對一個已經存在的SQL Server表分區
有兩種方法可以實現對一個表分區.一是創建一個新的標識為分區表的表(你可參照此步驟),然後把數據復制到這張新表,再對這兩張表分別改名.或者,像我寫在下面的,通過重建或創建一個聚集索引來達到分區一個表.
一個SQL Server表和數據進行分區示例
--Table/Index creation
CREATE TABLE [dbo].[TABLE1]
([pkcol] [int] NOT NULL,
[datacol1] [int] NULL,
[datacol2] [int] NULL,
[datacol3] [varchar](50) NULL,
[partitioncol] datetime)
GO
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY CLUSTERED (pkcol)
GO
CREATE NONCLUSTERED INDEX IX_TABLE1_col2col3 ON dbo.TABLE1 (datacol1,datacol2)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
-- Populate table data
DECLARE @val INT
SELECT @val=1
WHILE @val < 1000
BEGIN
INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, partitioncol)
VALUES (@val,@val,@val,'TEST',getdate()-@val)
SELECT @val=@val+1
END
GO
通過查看sys.partitions系統視圖,上面的代碼創建了一個傳統的單分區表.
SELECT o.name objectname,i.name indexname, partition_id, partition_number, [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%TABLE1%'
objectname
indexname
partition_id
partition_number
rows
TABLE1 PK_TABLE1 72057594042712064 1 999
TABLE1 IX_TABLE1_col2col3 72057594042777600 1 999
創建已分區的SQL Server表
為了創建一個分區表,需要先創建一個分區函數和分區方案. 下面的示例中,將通過datatime欄位對一個表分區. 這里是創建這些對象與在系統視圖中查看這些元數據的代碼.
CREATE PARTITION FUNCTION myDateRangePF (datetime)
AS RANGE RIGHT FOR VALUES ('20110101', '20120101','20130101')
GO
CREATE PARTITION SCHEME myPartitionScheme
AS PARTITION myDateRangePF ALL TO ([PRIMARY])
GO
SELECT ps.name,pf.name,boundary_id,value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id
現在我們有一個分區方案,可對表進行分區操作. 既然我們想通過聚集索引來對一個創建了聚集索引的表進行分區,我們需要先刪除掉這個索引並通過一個非聚集索引重建這個約束. 若這張表沒有聚集索引,我們可忽略這一步,直接執行創建聚集索引語句. 類似地,若有一個聚集索引創建在分區欄位上,我們可執行帶DROP_EXISTING的創建聚集索引語句. 最後,若關注於執行此任務時資料庫停止服務時間並且你使用的是SQL Server企業版本,可通過創建索引的ONLINE=ON選項來使資料庫的停止服務時間最小. 記住,在使用ONLINE選項重建索引時,你可能會看到一些性能降低的現象. 這里是一些可能會乃至的腳本.
ALTER TABLE dbo.TABLE1 DROP CONSTRAINT PK_TABLE1
GO
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY NONCLUSTERED (pkcol)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX IX_TABLE1_partitioncol ON dbo.TABLE1 (partitioncol)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON myPartitionScheme(partitioncol)
GO
在這些語句結束後,我們可再次查看sys.partitions系統視圖(看上面的代碼)並確認我們的表有4個分區.
objectname
indexname
partition_id
partition_number
rows
TABLE1 IX_TABLE1_partitioncol 72057594043039744 1 233
TABLE1 IX_TABLE1_partitioncol 72057594043105280 2 365
TABLE1 IX_TABLE1_partitioncol 72057594043170816 3 366
TABLE1 IX_TABLE1_partitioncol 72057594043236352 4 35
TABLE1 IX_TABLE1_col2col3 72057594043301888 1 999
TABLE1 PK_TABLE1 72057594043367424 1 999
SQL Server表和數據示例清除
--cleanup
DROP TABLE TABLE1
DROP PARTITION SCHEME myPartitionScheme
DROP PARTITION FUNCTION myDateRangePF
Ⅳ sqlserver2008怎麼實現自動分區表
/*第一步：創建分區函數*/
Create partition function Part_func_Bag(varchar(20)) as range right
/*正式區間
for values(N'01100923909760', N'01100936207030', N'6311001806524',N'92451400060101');*/for values(N'91701311710807', N'07201280707101', N'8011011089884',N'80241001430714');goselect * from Bag where BagCode in(N'91701311710807', N'07201280707101', N'8011011089884',N'80241001430714')
/*第二步：創建文件組和文件*/
alter database ZXAutoCode add filegroup [Bag_1]; alter database ZXAutoCode add filegroup [Bag_2];alter database ZXAutoCode add filegroup [Bag_3];alter database ZXAutoCode add filegroup [Bag_4];goalter database ZXAutoCode add file (name = Bag1_data,filename = 'E:\MSSQL\TESTDATA\Bag1_data.ndf',size = 3MB) to filegroup [Bag_1];alter database ZXAutoCode add file (name = Bag2_data,filename = 'E:\MSSQL\TESTDATA\Bag2_data.ndf',size = 3MB) to filegroup [Bag_2];alter database ZXAutoCode add file (name = Bag3_data,filename = 'E:\MSSQL\TESTDATA\Bag3_data.ndf',size = 3MB) to filegroup [Bag_3]; alter database ZXAutoCode add file (name = Bag4_data,filename = 'E:\MSSQL\TESTDATA\Bag4_data.ndf',size = 3MB) to filegroup [Bag_4]; go
/*第三步：創建分區方案並關聯到分區函數*/
Create partition scheme Part_func_Bag_scheme as partition Part_func_Bag to ([Bag_1],[Bag_2],[Bag_3],[Bag_4],[Primary]); go
/*第四步 重建索引（刪除聚集索引以及需要分區欄位的索引後重建該類索引，表被按分區值將分配到各文件組。數據在這一步開始轉移。）*/
EXEC sp_helpindex N'Bag' --查看orders中使用的索引 drop index idx_cl_od on Bag;gocreate clustered index idx_cl_od on Bag(bagcode) on Part_func_Bag_scheme(bagcode); go
Ⅵ sql server2008怎麼建分區
打開MsSQL2008,找到作業該項,如果打不開或者SQL Server代理是未啟動狀態,請先在windows服務中啟動SQL Server代理(參考圖片),
2
右擊MsSQL2008對象資源管理器中的作業,選擇新建作業,輸入該作業你想用的名稱,類別不用管,說明裡面是輸入一些該作業完成的功能,可不寫,請務必勾選已啟用復選框.
3
點擊新建作業窗體左側的步驟項,點擊右側區域下方的新建按鈕,輸入步驟名稱,類型請選擇Transact-SQL腳本(T-SQL),運行身份默認,資料庫請選擇要進行分區的資料庫,請不要選擇master默認的,命令文本框中輸入如下代碼:
/*--------------------創建資料庫的文件組和物理文件------------------------*/
declare @tableName varchar(50), @fileGroupName varchar(50), @ndfName varchar(50), @newNameStr varchar(50), @fullPath
varchar(50), @newDay varchar(50), @oldDay datetime, @partFunName varchar(50), @schemeName varchar(50)
set @tableName='要分區的資料庫名稱'
set @newDay=CONVERT(varchar(100), GETDATE(), 23)--23:按天 114:按時間
set @oldDay=cast(CONVERT(varchar(10),dateadd(day,-1,getdate()), 120 ) as datetime)
set @newNameStr=Replace(Replace(@newDay,':','_'),'-','_')
set @fileGroupName=N'G'+@newNameStr
set @ndfName=N'F'+@newNameStr+''
set @fullPath=N'E:\\SQLDataBase\\UserData\\'+@ndfName+'.ndf'
set @partFunName=N'pf_Time'
set @schemeName=N'ps_Time'
--創建文件組
if exists(select * from sys.filegroups where name=@fileGroupName)
begin
print '文件組存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')
print '新增文件組'
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')
print '修改分區方案'
end
if exists(select * from sys.partition_range_values where function_id=(select function_id from
sys.partition_functions where name =@partFunName) and value=@oldDay)
begin
exec('alter partition function '+@partFunName+'() split range('''+@newDay+''')')
print '修改分區函數'
end
end
--創建NDF文件
if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))
begin
print 'ndf文件存在,不需添加'
end
else
begin
exec('ALTER DATABASE '+@tableName+'ADD FILE (NAME ='+@ndfName+',
FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')
print '新創建ndf文件'
end
/*--------------------以上創建資料庫的文件組和物理文件------------------------*/
--分區函數
if exists(select * from sys.partition_functions where name =@partFunName)
begin
print '此處修改需要在修改分區函數之前執行'
end
else
begin
exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHTFOR VALUES ('''+@newDay
+''')')
print '新創建分區函數'
end
--分區方案
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
print '此處修改需要在修改分區方案之前執行'
end
else
begin
exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO
(''PRIMARY'','''+@fileGroupName+''')')
print '新創建分區方案'
end
print '---------------以下是變數定義值顯示---------------------'
print '當前資料庫:'+@tableName
print '當前日期:'+@newDay+'(用作隨機生成的各種名稱和分區界限)'
print '合法命名方式:'+@newNameStr
print '文件組名稱:'+@fileGroupName
print 'ndf物理文件名稱:'+@ndfName
print '物理文件完整路徑:'+@fullPath
print '分區函數:'+@partFunName
print '分區方案:'+@schemeName
/*
--查看創建的分區函數
select * from sys.partition_functions
--查看分區函數的臨界值
select * from sys.partition_range_values
--查詢分區方案
select * from sys.partition_schemes
--查詢表數據在哪個分區中存儲,where條件查詢第一個分區中存在的數據
select *,$partition.pf_SaveTime(分區欄位) as Patition from 表名 where $partition.pf_SaveTime(分區欄位)=1
*/
GO
點擊確定按鈕
上述代碼中的變數名稱,路徑等均可自行修改,上述是按天為單位,以G開頭的日期作為文件組名稱,以F開頭的日期作為物理分區文件名即ndf文件名稱
4
選擇新建分區左側的計劃項,然後點擊右側區域下方的新建按鈕,設定新建分區的時間間隔,圖中設置的是每天創建一個新的分區,用戶也可以自行修改,按月,按周,按自定義時間等
其他的條目,通知,警報,目標可自行設置,也可不設置,至此自動創建分區的計劃任務已成功設置.
END
步驟二:對表應用分區方案和分區函數
右擊要分區的表,選擇存儲菜單下的創建分區,上述步驟一中創建的分區函數是按datetime類型進行的分區,所以創建分區的時候需要選擇相應類型的欄位作為分區依據,用戶也可以根據int型或其他類型的欄位進行分區,選擇下一步,使用現有分區函數下一步使用現有分區方案,下一步會自動按照分區方案執行的日期進行分區,繼續點擊下一步選擇立即執行,完成後即可完成的整體的表分區自動執行.
需注意:剛設置完第一步的計劃任務,可能不會執行第一步的分區方案的代碼,也就意味著沒有創建分區函數和分區方案,第二步設置的時候使用現有分區函數和使用現有分區方案也就不可用,可先把第一步的代碼執行一遍即可.