What is Partitioned View in SQL Server

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“.

We create partitioned view with the help of the following script.

We can use partitioned view with the help of the following query.

As you can see in the screenshot below, when we use the OrderMonth column as a filter, it goes to only two tables.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *