【SQLServer BUG?無圖無真相!】SQLServer分區表的應用

　　今天晚上兄弟遇到個奇怪的問題，查詢一個表的ID可以顯示出來，查詢其他的列卻查不出來了，如:

 

　　我的第一反應是BillCode是char類型的，後面應該帶空格了，所以讓他用BillCode like '%CK201012718200850%' 去查下。

結果被告知沒查到，所以問他要了資料庫地址和帳號密碼，自己去嘗試了，果真如此。

　　然後我查詢了所有的資料，這個表一共只有到iID = 12372的記錄。那12372以後的記錄12386怎麼出來的？第一反應是BUG（無知的汗顏。。。）

　　然後我用12372實驗了，如下語句，查到了12372是表的臨界點。

　　

 

　　當時看到這個記錄的時候，我承認我開始蛋疼了。。。我認為這個世界上還是有鬼的！（又為自己的無知汗顏一下。。。）

　　說實話，我當時先是想辦法怎麼去解決他，所以很容易就找到了。用 iID<=12372 和 iID>=12373進行了查詢。如下。

　　

　　兄弟說，是一個方法，但是需要修改很多。所以我們為了發現這一奇特的BUG，而繼續探尋著。。這時兄弟在群裡發了一張圖。他咋發現的，我也不曉得。據說是他們外包的公司提供的。

　　

 

　　FUNCTION我知道，但這個 Partiton是何意思就不得而知了，上網搜了下，在百度文檔裡看到了“分區表”三個字，在這就恍然大悟了。原來一切都是這個“函數”在作怪。

　　在文檔上有這麼一句話。

　　

　　在MSDN查詢Partition的資料 用了一個T-SQL語句查到了根本。

　　

SELECT * FROM sys.partition_functions

　　看到了這個造孽的分區，直接刪除搞定。

DROP PARTITION FUNCTION xBillIndexCust

 

 

　　下面是別人整理的一些分區表的建立,合并,刪除,查詢的一些操作，在這做個備份吧。看部落格園沒有分區表的相關資料。

 

　　

--=========================================-- 轉換為分區表--=========================================-- 1. 建立分區函數--    a. 適用於儲存曆史存檔記錄的分區表的分區函數DECLARE @dt datetimeSET @dt = '20020101'CREATE PARTITION FUNCTION PF_HistoryArchive(datetime)AS RANGE RIGHTFOR VALUES(    @dt,    DATEADD(Year, 1, @dt)) --    b. 適用於儲存記錄的分區表的分區函數--DECLARE @dt datetimeSET @dt = '20000101'CREATE PARTITION FUNCTION PF_History(datetime)AS RANGE RIGHTFOR VALUES(    @dt,DATEADD(Month, 1, @dt),DATEADD(Month, 2, @dt),DATEADD(Month, 3, @dt),DATEADD(Month, 4, @dt),DATEADD(Month, 5, @dt),DATEADD(Month, 6, @dt),DATEADD(Month, 7, @dt),DATEADD(Month, 8, @dt),DATEADD(Month, 9, @dt),DATEADD(Month, 10, @dt),DATEADD(Month, 11, @dt),DATEADD(Month, 12, @dt))GO -- 2. 建立分區架構--    a. 適用於儲存曆史存檔記錄的分區表的分區架構CREATE PARTITION SCHEME PS_HistoryArchiveAS PARTITION PF_HistoryArchiveTO([PRIMARY], [PRIMARY], [PRIMARY]) --    b. 適用於儲存記錄的分區表的分區架構CREATE PARTITION SCHEME PS_HistoryAS PARTITION PF_HistoryTO([PRIMARY], [PRIMARY],    [PRIMARY], [PRIMARY], [PRIMARY],    [PRIMARY], [PRIMARY], [PRIMARY],    [PRIMARY], [PRIMARY], [PRIMARY],    [PRIMARY], [PRIMARY], [PRIMARY])GO -- 3. 刪除索引--    a. 刪除儲存曆史存檔記錄的表中的索引DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ProductIDDROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID --    b. 刪除儲存記錄的表中的索引DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ProductIDDROP INDEX Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineIDGO -- 4. 轉換為分區表--    a. 將儲存曆史存檔記錄的錶轉換為分區表ALTER TABLE Production.TransactionHistoryArchive    DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID    WITH(        MOVE TO PS_HistoryArchive(TransactionDate)) --    b.將儲存記錄的錶轉換為分區表ALTER TABLE Production.TransactionHistory    DROP CONSTRAINT PK_TransactionHistory_TransactionID    WITH(        MOVE TO PS_History(TransactionDate))GO -- 5. 恢複主鍵--    a. 恢複儲存曆史存檔記錄的分區表的主鍵ALTER TABLE Production.TransactionHistoryArchive    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID        PRIMARY KEY CLUSTERED(            TransactionID,            TransactionDate) --    b. 恢複儲存記錄的分區表的主鍵ALTER TABLE Production.TransactionHistory    ADD CONSTRAINT PK_TransactionHistory_TransactionID        PRIMARY KEY CLUSTERED(            TransactionID,            TransactionDate)GO-- 6. 恢複索引--    a. 恢複儲存曆史存檔記錄的分區表的索引CREATE INDEX IX_TransactionHistoryArchive_ProductID    ON Production.TransactionHistoryArchive(        ProductID) CREATE INDEX IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID    ON Production.TransactionHistoryArchive(        ReferenceOrderID,        ReferenceOrderLineID) --    b. 恢複儲存記錄的分區表的索引CREATE INDEX IX_TransactionHistory_ProductID    ON Production.TransactionHistory(        ProductID) CREATE INDEX IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID    ON Production.TransactionHistory(        ReferenceOrderID,        ReferenceOrderLineID)GO-- 7. 查看分區表的相關資訊SELECT    SchemaName = S.name,    TableName = TB.name,    PartitionScheme = PS.name,    PartitionFunction = PF.name,    PartitionFunctionRangeType = CASE            WHEN boundary_value_on_right = 0 THEN 'LEFT'            ELSE 'RIGHT' END,    PartitionFunctionFanout = PF.fanout,    SchemaID = S.schema_id,    ObjectID = TB.object_id,    PartitionSchemeID = PS.data_space_id,    PartitionFunctionID = PS.function_idFROM sys.schemas S    INNER JOIN sys.tables TB        ON S.schema_id = TB.schema_id    INNER JOIN sys.indexes IDX        on TB.object_id = IDX.object_id            AND IDX.index_id < 2    INNER JOIN sys.partition_schemes PS        ON PS.data_space_id = IDX.data_space_id    INNER JOIN sys.partition_functions PF        ON PS.function_id = PF.function_idGO --=========================================-- 移動分區表資料--=========================================-- 1. 為儲存曆史存檔記錄的分區表增加分區, 並接受從記錄分區表移動過來的資料--    a. 修改分區架構, 增加用以接受新分區的檔案組ALTER PARTITION SCHEME PS_HistoryArchiveNEXT USED [PRIMARY] --    b. 修改分區函數, 增加分區用以接受從記錄分區表移動過來的資料DECLARE @dt datetimeSET @dt = '20030901'ALTER PARTITION FUNCTION PF_HistoryArchive()SPLIT RANGE(@dt) --    c. 將記錄表中的到期資料移動到曆史存檔記錄表中ALTER TABLE Production.TransactionHistory    SWITCH PARTITION 2        TO Production.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(@dt) --    d. 將接受到的資料與原來的分區合并ALTER PARTITION FUNCTION PF_HistoryArchive()MERGE RANGE(@dt)GO -- 2. 將儲存記錄的分區表中不包含資料的分區刪除, 並增加新的分區以接受新資料--    a. 合并不包含資料的分區DECLARE @dt datetimeSET @dt = '20030901'ALTER PARTITION FUNCTION PF_History()MERGE RANGE(@dt) --    b.  修改分區架構, 增加用以接受新分區的檔案組ALTER PARTITION SCHEME PS_HistoryNEXT USED [PRIMARY] --    c. 修改分區函數, 增加分區用以接受新資料SET @dt = '20041001'ALTER PARTITION FUNCTION PF_History()SPLIT RANGE(@dt)GO  --=========================================-- 清除曆史存檔記錄中的到期資料--=========================================-- 1. 建立用於儲存到期的曆史存檔資料的表CREATE TABLE Production.TransactionHistoryArchive_2001_temp(    TransactionID int NOT NULL,    ProductID int NOT NULL,    ReferenceOrderID int NOT NULL,    ReferenceOrderLineID int NOT NULL        DEFAULT ((0)),    TransactionDate datetime NOT NULL        DEFAULT (GETDATE()),    TransactionType nchar(1) NOT NULL,    Quantity int NOT NULL,    ActualCost money NOT NULL,    ModifiedDate datetime NOT NULL        DEFAULT (GETDATE()),    CONSTRAINT PK_TransactionHistoryArchive_2001_temp_TransactionID        PRIMARY KEY CLUSTERED(            TransactionID,            TransactionDate)) -- 2. 將資料從曆史存檔記錄分區表移動到第步建立的表中ALTER TABLE Production.TransactionHistoryArchive    SWITCH PARTITION 1        TO Production.TransactionHistoryArchive_2001_temp -- 3. 刪除不再包含資料的分區DECLARE @dt datetimeSET @dt = '20020101'ALTER PARTITION FUNCTION PF_HistoryArchive()MERGE RANGE(@dt) -- 4. 修改分區架構, 增加用以接受新分區的檔案組ALTER PARTITION SCHEME PS_HistoryArchiveNEXT USED [PRIMARY] -- 5. 修改分區函數, 增加分區用以接受新資料SET @dt = '20040101'ALTER PARTITION FUNCTION PF_HistoryArchive()SPLIT RANGE(@dt)