Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
As you know, partition is the division of large tables into small tables. For more information about Partition, please read the article “How To Create Partition On SQL Server“.
In versions prior to SQL Server 2016, Table Partitioning was not supported in Standard Edition. Therefore, organizations that use Standard Edition could not do table partitioning. Instead, Partitioned View can solve this problem partially. If you are using a version of SQL Server 2016 and later, I recommend that you use table partition instead of partitioned view if your infrastructure is appropriate.
Example
We create 12 tables as follows and we add a record to every table as follows. To be able to do Partitioned View, there must be CHECK constraint on the column that will be partitioned. You may want to read my article “How To Create CHECK CONSTRAINT“.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 | CREATE TABLE TableJanuary (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 1), CONSTRAINT [PK_TableJanuary] PRIMARY KEY CLUSTERED ([ID] ASC) ) CREATE TABLE TableFebruary (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 2), CONSTRAINT [PK_TableFebruary] PRIMARY KEY CLUSTERED ([ID] ASC) ) CREATE TABLE TableMarch (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 3), CONSTRAINT [PK_TableMarch] PRIMARY KEY CLUSTERED ([ID] ASC) ) CREATE TABLE TableApril (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 4), CONSTRAINT [PK_TableApril] PRIMARY KEY CLUSTERED ([ID] ASC) ) CREATE TABLE TableMay (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 5), CONSTRAINT [PK_TableMay] PRIMARY KEY CLUSTERED ([ID] ASC) ) CREATE TABLE TableJune (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 6), CONSTRAINT [PK_TableJune] PRIMARY KEY CLUSTERED ([ID] ASC) ) CREATE TABLE TableJuly (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 7), CONSTRAINT [PK_TableJuly] PRIMARY KEY CLUSTERED ([ID] ASC) ) CREATE TABLE TableAugust (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 8), CONSTRAINT [PK_TableAugust] PRIMARY KEY CLUSTERED ([ID] ASC) ) CREATE TABLE TableSeptember (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 9), CONSTRAINT [PK_TableSeptember] PRIMARY KEY CLUSTERED ([ID] ASC) ) CREATE TABLE TableOctober (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 10), CONSTRAINT [PK_TableOctober] PRIMARY KEY CLUSTERED ([ID] ASC) ) CREATE TABLE TableNovember (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 11), CONSTRAINT [PK_TableNovember] PRIMARY KEY CLUSTERED ([ID] ASC) ) CREATE TABLE TableDecember (ID INT, CustomerID INT NOT NULL, OrderMonth INT NULL CHECK (OrderMonth = 12), CONSTRAINT [PK_TableDecember] PRIMARY KEY CLUSTERED ([ID] ASC) ) GO INSERT INTO [dbo].[TableJanuary]([ID],[CustomerID],[OrderMonth]) VALUES (1,324,1) INSERT INTO [dbo].[TableFebruary]([ID],[CustomerID],[OrderMonth]) VALUES (1,314,2) INSERT INTO [dbo].[TableMarch]([ID],[CustomerID],[OrderMonth]) VALUES (1,314,3) INSERT INTO [dbo].[TableApril]([ID],[CustomerID],[OrderMonth]) VALUES (1,324,4) INSERT INTO [dbo].[TableMay]([ID],[CustomerID],[OrderMonth]) VALUES (1,324,5) INSERT INTO [dbo].[TableJune]([ID],[CustomerID],[OrderMonth]) VALUES (1,314,6) INSERT INTO [dbo].[TableJuly]([ID],[CustomerID],[OrderMonth]) VALUES (1,324,7) INSERT INTO [dbo].[TableAugust]([ID],[CustomerID],[OrderMonth]) VALUES (1,324,8) INSERT INTO [dbo].[TableSeptember]([ID],[CustomerID],[OrderMonth]) VALUES (1,314,9) INSERT INTO [dbo].[TableOctober]([ID],[CustomerID],[OrderMonth]) VALUES (1,324,10) INSERT INTO [dbo].[TableNovember]([ID],[CustomerID],[OrderMonth]) VALUES (1,314,11) INSERT INTO [dbo].[TableDecember]([ID],[CustomerID],[OrderMonth]) VALUES (1,324,12) |
We create partitioned view with the help of the following script.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | CREATE VIEW PartitionedViewExample AS SELECT * FROM TableJanuary UNION ALL SELECT * FROM TableFebruary UNION ALL SELECT * FROM TableMarch UNION ALL SELECT * FROM TableApril UNION ALL SELECT * FROM TableMay UNION ALL SELECT * FROM TableJune UNION ALL SELECT * FROM TableJuly UNION ALL SELECT * FROM TableAugust UNION ALL SELECT * FROM TableSeptember UNION ALL SELECT * FROM TableOctober UNION ALL SELECT * FROM TableNovember UNION ALL SELECT * FROM TableDecember |
We can use partitioned view with the help of the following query.
1 2 | SELECT * FROM PartitionedViewExample WHERE OrderMonth IN (5,6) |
As you can see in the screenshot below, when we use the OrderMonth column as a filter, it goes to only two tables.
