[Sqlserver bug? No picture, no truth !] SQL Server Partition Table Application

This evening, my brother encountered a strange problem. The ID of a table can be displayed, but other columns cannot be queried, for example:

 

My first response is that billcode is of the char type and should be followed by a space. So let him check it with billcode like '% ck201020.18200850 %.

The result was told that the database address and account password were not found, so he asked him for the database address and password and tried it by himself.

Then I query all the data. This table only contains records with IID = 12372. How did 12372 of records after 12386 come out? The first reaction is Bug (ignorance of shame ...)

Then I tried it with 12372. The following statement found that 12372 is the critical point of the table.

 

When I saw this record, I admitted that I started to get hurt... I think there are ghosts in this world! (Shame on your ignorance ...)

To be honest, I first tried to solve it, so it was easy to find it. IID <= 12372 and IID> = 12373 are used for query. As follows.

It is a method, but it needs to be modified a lot. So we continue to explore this strange bug .. At this time, the brother sent a picture in the group. I don't know what he found. It is said that they are provided by outsourcing companies.

 

I know about function, but I don't know what this partiton means. I searched the internet and found the word "partition table" in the Baidu document. Then I suddenly realized it. It turns out that this "function" is playing a strange role.

There is such a sentence in the document.

In msdn to query partition information with a T-SQL statement to find the root.

 
Select * From SYS. partition_functions
 

You can delete the failed partition directly.

 
Drop Partition Function xbillindexcust
 

 

 

The following are some operations for creating, merging, deleting, and querying partition tables organized by others. Back up these operations. Check that there is no partition table in the blog Park.

 

-- ===================================================== ==-- Convert to a partitioned table -- ==================================== =========-- 1. create a partition function --. the partition function declare @ DT datetimeset @ dt = '000000' create partition function pf_historyarchive (datetime) as range rightfor values (@ DT, dateadd (year, 1, @ DT) -- B. the partition function -- declare @ DT datetimeset @ dt = '000000' 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. create a partition Architecture --. 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]) Go -- 3. delete An index --. delete the index drop index production in the table that stores historical archive records. transactionhistoryarchive. ix_transactionhistoryarchive_productiddrop index production. transactionhistoryarchive. ix_transactionhistoryarchive_referenceorderid_referenceorderlineid -- B. delete the index drop index production in the table that stores historical records. transactionhistory. ix_transactionhistory_productiddrop index production. transactionhistory. ix_transactionhistory_referenceorderid_referenceorderlineidgo -- 4. convert to partitioned table --. converts a table storing historical archive records to a partitioned table alter table production. transactionhistoryarchive drop constraint pk_transactionhistoryarchive_transactionid with (move to ps_historyarchive (transactiondate) -- B. convert a table storing historical records to a partitioned table alter table production. transactionhistory drop constraint pk_transactionhistory_transactionid with (move to ps_history (transactiondate) Go -- 5. restore the primary key --. restore the primary key alter table production of the partition table that stores historical archive records. transactionhistoryarchive add constraint pk_transactionhistoryarchive_transactionid primary key clustered (transactionid, transactiondate) -- B. restore the primary key alter table production of the partition table that stores historical records. transactionhistory add constraint pk_transactionhistory_transactionid primary key clustered (transactionid, transactiondate) Go -- 6. restore index --. restore the index create index ix_transactionhistoryarchive_productid on production of the partitioned table that stores historical archive records. transactionhistoryarchive (productid) Create index ix_transactionhistoryarchive_referenceorderid_referenceorderlineid on production. transactionhistoryarchive (referenceorderid, referenceorderlineid) -- B. restore the index create index ix_transactionhistory_productid on production of the partitioned table that stores historical records. transactionhistory (productid) Create index ix_transactionhistory_referenceorderid_referenceorderlineid on production. transactionhistory (referenceorderid, referenceorderlineid) Go -- 7. view Partition Table Information 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 -- ======================================== ====-- move partition table data -- ==================================== ========== -- 1. add partitions for the Partition Table storing historical archive records, and accept the data moved from the historical Partition Table --. modify the partition architecture and add the file group alter Partition Scheme ps_historyarchivenext used [primary] -- B. modify the Partition Function and add partitions to accept the data that is moved from the Partition Table of the history record. Declare @ DT datetimeset @ dt = '000000' alter Partition Function pf_historyarchive () split range (@ DT) -- C. move the expired data in the history record table to the alter table production in the history record table. transactionhistory switch partition 2 to production. transactionhistoryarchive partition $ partition. pf_historyarchive (@ DT) -- d. merge the received data with the original partition alter Partition Function pf_historyarchive () Merge range (@ DT) Go -- 2. delete partitions that do not contain data in the Partition Table that stores historical records, and add new partitions to accept new data --. declare @ DT datetimeset @ dt = '000000' alter Partition Function pf_history () Merge range (@ DT) -- B. modify the partition architecture and add the file group alter Partition Scheme ps_historynext used [primary] -- C. modify the Partition Function and add a partition to accept the new data set @ dt = '000000' alter Partition Function pf_history () split range (@ DT) go -- ============================================== ====-- clear expired data in the Historical Archive record -- ====================== ============== -- 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 begin primary key clustered (transactionid, transactiondate )) -- 2. move the data from the historical archive Record Partition Table to the alter table production table created in step. transactionhistoryarchive switch partition 1 to production. transactionhistoryarchive_2001_temp -- 3. delete the partition declare @ DT datetimeset @ dt = '000000' alter Partition Function pf_historyarchive () Merge range (@ DT) -- 4. modify the partition architecture and add the file group alter Partition Scheme ps_historyarchivenext used [primary] -- 5. modify the Partition Function and add a partition to accept the new data set @ dt = '000000' alter Partition Function pf_historyarchive () split range (@ DT)