Today, I am struggling with this problem for a day.
The following code is reproduced on the Internet.
Create Partition Function pf_orders_orderdaterange (datetime)
As
Range right for values (
'2017-01-01 ',
'2017-01-01 ',
'2017-01-01'
)
Go
-- Create a partition scheme
Create Partition Scheme ps_orders
As
Partition pf_orders_orderdaterange
To ([primary], [primary], [primary], [primary])
Go
-- Create a partition table
Sp_rename 'dbo. Orders ', 'Orders _ from_sql2000_northwind'
Create Table DBO. Orders
(
Orderid int not null
, Customerid varchar (10) Not null
, Employeeid int not null
, Orderdate datetime not null
)
On ps_orders (orderdate)
Go
-- Create a clustered partition Index
Create clustered index ixc_orders_orderdate on DBO. Orders (orderdate)
Go
-- Set the primary key for the partition table
Alter table dbo. Orders add constraint pk_orders
Primary Key (orderid, customerid, orderdate)
Go
-- Import data to a partition table
Insert into DBO. Orders
Select orderid, customerid, employeeid, orderdate
From DBO. orders_from_sql2000_northwind -- (Note: The data is from the SQL Server 2000 sample database)
Go
-- View the data distribution of each partition in a partition table
Select partition = $ partition. pf_orders_orderdaterange (orderdate)
, Rows = count (*)
, Minval = min (orderdate)
, Maxval = max (orderdate)
From DBO. Orders
Group by $ partition. pf_orders_orderdaterange (orderdate)
Order by partition
Go
Create Table DBO. orders_1998
(
Orderid int not null
, Customerid varchar (10) Not null
, Employeeid int not null
, Orderdate datetime not null
) On [primary]
Go
Create clustered index ixc_orders1998_orderdate on DBO. orders_1998 (orderdate)
Go
Alter table dbo. orders_1998 add constraint pk_orders_1998
Primary Key nonclustered (orderid, customerid, orderdate)
Go
Alter table dbo. Orders switch partition 3 to DBO. orders_1998
Go
Alter table dbo. orders_1998 add constraint ck_orders1998_orderdate
Check (orderdate> = '2014-01-01 'and orderdate <'2014-01-01 ')
Go
Alter table dbo. orders_1998 switch to DBO. Orders partition 3
This code is okay. Normal tables can be successfully switched to partitioned tables. Note that the partition field is not null. If you change to null, you cannot switch.
Only outgoing access is allowed.