sample: select YEAR(datetime), COUNT(1)over(partition by YEAR(datetime))
楼上的是代码是嘛意思?和分区函数(CREATE PARTITION FUNCTION)有关系么?
example ~~ /* 1.创建分区函数 */CREATE PARTITION FUNCTION pf_test(int) as range left for values(500,1000)/* 2. 创建不同的文件组 */ Alter DATABASE AdventureWorks add filegroup fg01 Alter DATABASE AdventureWorks add filegroup fg02 Alter DATABASE AdventureWorks add filegroup fg03ALTER DATABASE AdventureWorks add file ( name=file01, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\file01.mdf', size=5mb, filegrowth=1mb ) to filegroup fg01 ALTER DATABASE AdventureWorks add file ( name=file02, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\file02.mdf', size=5mb, filegrowth=1mb ) to filegroup fg02 ALTER DATABASE AdventureWorks add file ( name=file03, filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\file03.mdf', size=5mb, filegrowth=1mb ) to filegroup fg03/* 3.创建scheme */CREATE PARTITION SCHEME ps_test AS PARTITION pf_test TO([FG01],[FG02],[FG03])/* 4.创建table */ CREATE TABLE [dbo].[TestDatabaseLog]( [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL, [PostTime] [datetime] NOT NULL, [DatabaseUser] [sysname] NOT NULL, [Event] [sysname] NOT NULL, [Schema] [sysname] NULL, [Object] [sysname] NULL, [TSQL] [nvarchar](max) NOT NULL, [XmlEvent] [xml] NOT NULL, CONSTRAINT [PK_TestDatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED ( [DatabaseLogID] ASC ) ) ON ps_test(DatabaseLogId) GO/* 5.插入数据 */ SET IDENTITY_INSERT dbo.TestDatabaseLog On INSERT INTO TestDatabaseLog SELECT * FROM DatabaseLogSET IDENTITY_INSERT TestDatabaseLog Off/* 6.查看分区结果 */ select $partition.pf_test(DatabaseLogID) as 'partition number', COUNT(*) 'Rows in partition' from DATABASELOGBAK group by $partition.pf_test(DatabaseLogID); /*********************************************************************************************************************//* 1.创建分区函数 */ create partition function OrderDateRangePFN(datetime) AS RANGE RIGHT FOR VALUES(N'2001-01-01 00:00:00',N'2002-01-01 00:00:00',N'2003-01-01 00:00:00',N'2004-01-01 00:00:00');/* 2。创建分区scheme */ create partition scheme OrderDatePScheme as partition OrderDateRangePFN TO([Primary],[Primary],[Primary],[Primary],[Primary])/* 3.创建表 */CREATE TABLE DBO.[SalesOrderHeader]( [SalesOrderID] [int] NULL, [RevisionNumber] [tinyint] NOT NULL, [OrderDate] [datetime] NOT NULL, [DueDate] [datetime] NOT NULL, [ShipDate] [datetime] NULL, [Status] [tinyint] NOT NULL ) ON OrderDatePScheme (OrderDate)/* 旧表*/ CREATE TABLE DBO.[SalesOrderHeaderOLD]( [SalesOrderID] [int] NULL, [RevisionNumber] [tinyint] NOT NULL, [OrderDate] [datetime] NOT NULL, [DueDate] [datetime] NOT NULL, [ShipDate] [datetime] NULL, [Status] [tinyint] NOT NULL ) /* 4。插入数据 */ INSERT INTO dbo.SalesOrderHeader SELECT SalesOrderID,RevisionNumber,OrderDate,DueDate,ShipDate,Status FROM Sales.SalesOrderHeaderCREATE CLUSTERED INDEX SalesOrderHeaderCLInd ON SalesOrderHeader(OrderDate) ON OrderDatePScheme(OrderDate)CREATE CLUSTERED INDEX SalesOrderHeaderOLDCLInd ON SalesOrderHeaderOLD(OrderDate)ALTER TABLE SalesOrderHeaderOLD WITH CHECK ADD CONSTRAINT ck_SalesOrderHeaderOLD_Orderdate check ( orderdate >=('2003-01-01 00:00:00') and orderdate <=('2003-12-31 00:00:00')) /* 5.查看分区结果 */ select $partition.OrderDateRangePFN(OrderDate) as 'partition number', MIN(OrderDate) as 'Min Order Date', MAX(OrderDate) as 'Max Order Date', COUNT(*) as 'Rows In Partition' From SalesOrderHeader group by $partition.OrderDateRangePFN(OrderDate);Select * From SalesOrderHeaderOLD delete from SalesOrderHeaderOLD /* 6。切换分区内容到未分区的表 */ Alter table SalesOrderHeader switch partition 4 to salesOrderheaderOLDSelect * From SalesOrderHeaderOLD/*切换未分区的表内容到某个分区上*/ alter table salesorderheaderold switch to salesorderheader partition 4 --Select * From SalesOrderHeaderOLD/* 7. */ alter partition scheme orderdatepscheme next used [primary]; alter partition function orderdaterangePFN() split range ('2003-01-01 00:00:00');
编号 入单日期 想按入单日期(datetime)来按年分区,一个个输界定值的话,从02年到10年,要9个分区,
太繁琐了,况且如果用到20年,那不是还得再修改这个分区函数?能不能用直接函数来界定呢?
select YEAR(datetime), COUNT(1)over(partition by YEAR(datetime))
/*
1.创建分区函数
*/CREATE PARTITION FUNCTION
pf_test(int)
as range left
for values(500,1000)/*
2. 创建不同的文件组
*/
Alter DATABASE AdventureWorks
add filegroup fg01
Alter DATABASE AdventureWorks
add filegroup fg02
Alter DATABASE AdventureWorks
add filegroup fg03ALTER DATABASE AdventureWorks
add file
(
name=file01,
filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\file01.mdf',
size=5mb,
filegrowth=1mb
)
to filegroup fg01
ALTER DATABASE AdventureWorks
add file
(
name=file02,
filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\file02.mdf',
size=5mb,
filegrowth=1mb
)
to filegroup fg02
ALTER DATABASE AdventureWorks
add file
(
name=file03,
filename='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\file03.mdf',
size=5mb,
filegrowth=1mb
)
to filegroup fg03/*
3.创建scheme
*/CREATE PARTITION SCHEME ps_test
AS PARTITION pf_test
TO([FG01],[FG02],[FG03])/*
4.创建table
*/
CREATE TABLE [dbo].[TestDatabaseLog](
[DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
[PostTime] [datetime] NOT NULL,
[DatabaseUser] [sysname] NOT NULL,
[Event] [sysname] NOT NULL,
[Schema] [sysname] NULL,
[Object] [sysname] NULL,
[TSQL] [nvarchar](max) NOT NULL,
[XmlEvent] [xml] NOT NULL,
CONSTRAINT [PK_TestDatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED
(
[DatabaseLogID] ASC
)
)
ON ps_test(DatabaseLogId)
GO/*
5.插入数据
*/
SET IDENTITY_INSERT dbo.TestDatabaseLog On
INSERT INTO TestDatabaseLog
SELECT * FROM DatabaseLogSET IDENTITY_INSERT TestDatabaseLog Off/*
6.查看分区结果
*/
select $partition.pf_test(DatabaseLogID) as 'partition number',
COUNT(*) 'Rows in partition'
from DATABASELOGBAK
group by $partition.pf_test(DatabaseLogID);
/*********************************************************************************************************************//*
1.创建分区函数
*/
create partition function OrderDateRangePFN(datetime)
AS RANGE RIGHT
FOR VALUES(N'2001-01-01 00:00:00',N'2002-01-01 00:00:00',N'2003-01-01 00:00:00',N'2004-01-01 00:00:00');/*
2。创建分区scheme
*/
create partition scheme OrderDatePScheme
as partition OrderDateRangePFN
TO([Primary],[Primary],[Primary],[Primary],[Primary])/*
3.创建表
*/CREATE TABLE DBO.[SalesOrderHeader](
[SalesOrderID] [int] NULL,
[RevisionNumber] [tinyint] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[DueDate] [datetime] NOT NULL,
[ShipDate] [datetime] NULL,
[Status] [tinyint] NOT NULL
) ON OrderDatePScheme (OrderDate)/* 旧表*/
CREATE TABLE DBO.[SalesOrderHeaderOLD](
[SalesOrderID] [int] NULL,
[RevisionNumber] [tinyint] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[DueDate] [datetime] NOT NULL,
[ShipDate] [datetime] NULL,
[Status] [tinyint] NOT NULL
) /*
4。插入数据
*/
INSERT INTO dbo.SalesOrderHeader
SELECT SalesOrderID,RevisionNumber,OrderDate,DueDate,ShipDate,Status FROM Sales.SalesOrderHeaderCREATE CLUSTERED INDEX SalesOrderHeaderCLInd
ON SalesOrderHeader(OrderDate) ON OrderDatePScheme(OrderDate)CREATE CLUSTERED INDEX SalesOrderHeaderOLDCLInd
ON SalesOrderHeaderOLD(OrderDate)ALTER TABLE SalesOrderHeaderOLD WITH CHECK ADD CONSTRAINT
ck_SalesOrderHeaderOLD_Orderdate check (
orderdate >=('2003-01-01 00:00:00') and orderdate <=('2003-12-31 00:00:00'))
/*
5.查看分区结果
*/
select $partition.OrderDateRangePFN(OrderDate) as 'partition number',
MIN(OrderDate) as 'Min Order Date',
MAX(OrderDate) as 'Max Order Date',
COUNT(*) as 'Rows In Partition'
From SalesOrderHeader
group by $partition.OrderDateRangePFN(OrderDate);Select * From SalesOrderHeaderOLD
delete from SalesOrderHeaderOLD
/*
6。切换分区内容到未分区的表
*/
Alter table SalesOrderHeader
switch partition 4 to salesOrderheaderOLDSelect * From SalesOrderHeaderOLD/*切换未分区的表内容到某个分区上*/
alter table salesorderheaderold
switch to salesorderheader partition 4
--Select * From SalesOrderHeaderOLD/*
7.
*/
alter partition scheme orderdatepscheme next used [primary];
alter partition function orderdaterangePFN() split range ('2003-01-01 00:00:00');