One database can have 32,767 files. If a table is not partitioned all data will go in PRIMARY Files, when a table is partitioned data goes in its defined partition, if it falls outside the range then it ends up in PRIMARY file.
So, let's see how we can create a range partition. In this scenario, consider a Database named "Partition" having table named "RangePart" which stores data month by month . We want to partition Rangepart in 12 parts where each month's data ends up in different partition. So first thing is we create different partition on database as shown below.
- Right Click in Database and select Properties
- From Left panel, select Files
3. Click on Add, a new row will be created and I am naming it as m1 and under Filegroup column click new filegroup
4. Small window will open and you can give a name, to keep things simple I am naming it as m1 as well and click OK
5. In the same way, create 12 filegroups with further names as m2, m3,m4,m5 and so on till m12 as shown below
6. Click Ok. Now we need to create partition function as below:
CREATE PARTITION FUNCTION [partfunction](int) AS RANGE LEFT FOR VALUES (1, 2, 3, 4,5,6,7,8,9,10,11,12)
7. We will be creating a partition scheme to map the file names that we created above
CREATE PARTITION SCHEME [partfunctionScheme] AS PARTITION [partfunction]
TO ([m1], [m2], [m3], [m4],[m5],[m6],[m7],[m8],[m9],[m10],[m11],[m12], [PRIMARY])
8. Now we create our table and make sure that it reference partition scheme as below.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Rangepart](
[EmployeeID] [nvarchar](50) NULL,
[Name] [nvarchar](300) NULL,
[Department] [nvarchar](50) NULL,
[UploadDate] [datetime] NOT NULL,
[Month] as datepart(month,uploaddate) persisted
) ON [partfunctionScheme]([Month])
Then we can see if our data went in correct partition by executing below query
select $partition.partfunction([Month]) partition, *
Note that above e.g. is just for demonstration in practical use partitioning is done when we have huge volume of data.