【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 datetime
SET @dt = '20020101'
CREATE PARTITION FUNCTION PF_HistoryArchive(datetime)
AS RANGE RIGHT
FOR VALUES(
    @dt,
    DATEADD(Year, 1, @dt))
 
--    b. 适用于存储历史记录的分区表的分区函数
--DECLARE @dt datetime
SET @dt = '20000101'
CREATE PARTITION FUNCTION PF_History(datetime)
AS RANGE RIGHT
FOR 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_HistoryArchive
AS PARTITION PF_HistoryArchive
TO([PRIMARY], [PRIMARY], [PRIMARY])
 
--    b. 适用于存储历史记录的分区表的分区架构
CREATE PARTITION SCHEME PS_History
AS PARTITION PF_History
TO([PRIMARY], [PRIMARY],
    [PRIMARY], [PRIMARY], [PRIMARY],
    [PRIMARY], [PRIMARY], [PRIMARY],
    [PRIMARY], [PRIMARY], [PRIMARY],
    [PRIMARY], [PRIMARY], [PRIMARY])
GO
 
-- 3. 删除索引
--    a. 删除存储历史存档记录的表中的索引
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ProductID
DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
 
--    b. 删除存储历史记录的表中的索引
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ProductID
DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
GO
 
-- 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_id
FROM 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_id
GO
 
--=========================================
-- 移动分区表数据
--=========================================
-- 1. 为存储历史存档记录的分区表增加分区, 并接受从历史记录分区表移动过来的数据
--    a. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]
 
--    b. 修改分区函数, 增加分区用以接受从历史记录分区表移动过来的数据
DECLARE @dt datetime
SET @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 datetime
SET @dt = '20030901'
ALTER PARTITION FUNCTION PF_History()
MERGE RANGE(@dt)
 
--    b.  修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_History
NEXT 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 datetime
SET @dt = '20020101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
MERGE RANGE(@dt)
 
-- 4. 修改分区架构, 增加用以接受新分区的文件组
ALTER PARTITION SCHEME PS_HistoryArchive
NEXT USED [PRIMARY]
 
-- 5. 修改分区函数, 增加分区用以接受新数据
SET @dt = '20040101'
ALTER PARTITION FUNCTION PF_HistoryArchive()
SPLIT RANGE(@dt)