分区并不影响Linq,sql查询
在MSSQL中,选中目标表,右键-存储-创建分区
根据提示完成分区,存储成sql
这里展示如何根据Id的数据范围分区
在执行前,可能需要设置日志文件大小为“无限制”,否则执行过程中可能出现事务日志已满,原因为“LOG_BACKUP”的报错
--其实就是写个生成字符串列表(数据范围)的函数 --这里将数据以500000为一组分区,一直分到50000000条,50000000之后的数据则在一个区内 DECLARE @fenqu nvarchar(max) = N'CREATE PARTITION FUNCTION [fenqu](bigint) AS RANGE RIGHT FOR VALUES ('; DECLARE @i int=0; WHILE @i < 50000000 BEGIN SET @fenqu += '''' + convert(varchar,@i) + '''' + N', '; SET @i = @i+500000; END SET @fenqu += '''' + convert(varchar,@i)+ '''' + N'); '; EXEC sp_executesql @fenqu; --数量和上面分区的数量对应 CREATE PARTITION SCHEME [fenqufangan] AS PARTITION [fenqu] TO ( [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],[PRIMARY])
分区完成后,日志文件、数据文件可能会很大,可以收缩一下
CREATE PARTITION FUNCTION (Transact-SQL)
官方资料展示了根据数量、根据时间段分区的示例