SQL Server Partitioned view implementation sub-table

We all know that when the amount of database data is large, data can be horizontally extended, such as sub-Library, partition, sub-table (also called partition) and so on. One scenario for a sub-table is to use the partitioned view implementation.

Partitioned views allow data in large tables to be split into smaller member tables. partitions data between individual member tables based on the range of data values in one of the columns. The data range for each member table is defined in the CHECK constraint specified for the partitioning column. Then define a view to use UNION all to combine all the selected member tables into a single result set. After the SELECT statement referencing the view specifies the search criteria for the partitioning column, the query optimizer uses the CHECK constraint definition to determine which member table contains the corresponding row.

Check constraints provide better optimization features in query, see a warrior's experiment using CHECK constraints to improve performance in SQL Server is currently not very good in other operations, the following tests.

The current test is a local partitioned view:

Use [demodb]go--create a table with the same structure, [ID] do not set the self-increment (IDENTITY), because you need to know the ID value before inserting the table--drop table [DEMOTAB01],[DEMOTAB02],[DEMOTAB03] CREATE TABLE [dbo]. [DEMOTAB01] ([ID] [int] not null,[insdate] [datetime] NULL) on [Primary]gocreate TABLE [dbo]. [DEMOTAB02] ([ID] [int] not null,[insdate] [datetime] NULL) on [Primary]gocreate TABLE [dbo]. [DEMOTAB03] ([ID] [int] not null,[insdate] [datetime] NULL) on [primary]go--constrains the scope of each table ALTER TABLE [dbo]. [DEMOTAB01] With check ADD CONSTRAINT [ck_demotab01_id] Check ([id] between 0 and 99999) goalter TABLE [dbo]. [DEMOTAB02] With check ADD CONSTRAINT [ck_demotab02_id] Check ([id] between 100000 and 199999) Goalter TABLE [dbo]. [DEMOTAB03] With check ADD CONSTRAINT [ck_demotab03_id] Check ([id] between 200000 and 299999) go--since it is divided by ID, it is easier to locate the ID as a clustered index to find alter TABL E [dbo]. [DEMOTAB01] ADD CONSTRAINT [pk_demotab01_id] PRIMARY KEY CLUSTERED ([id] ASC) goalter TABLE [dbo]. [DEMOTAB02] ADD CONSTRAINT [pk_demotab02_id] PRIMARY KEY CLUSTERED ([id] ASC) goalter TABLE [dbo]. [DEMOTAB03] ADD CONSTRaint [pk_demotab03_id] PRIMARY KEY CLUSTERED ([id] ASC) go--each field name is listed, avoid using asterisks, otherwise the upgrade and delete fields will be different when there is an error--drop VIEW [dbo]. [V_demotab] CREATE VIEW [dbo]. [V_demotab] Asselect [id],[insdate] from [dbo]. [DEMOTAB01] UNION Allselect [id],[insdate] from [dbo]. [DEMOTAB02] UNION Allselect [id],[insdate] from [dbo]. [DEMOTAB03] go--121317 row data insert INTO [V_demotab] ([id],[insdate]) SELECT salesorderdetailid,modifieddate from AdventureWorks2012.Sales.SalesOrderDetailGOSELECT COUNT (*) from [dbo]. [V_demotab] SELECT COUNT (*) from [dbo]. [DEMOTAB01] SELECT COUNT (*) from [dbo]. [DEMOTAB02] SELECT COUNT (*) from [dbo]. [DEMOTAB03]

--now query the view for select * FROM [dbo]. [V_demotab] WHERE id = 0--There is no select * FROM [dbo]. [V_demotab] WHERE ID = 3000--Only the ID has a record of select * FROM [dbo]. [V_demotab] WHERE id = 300000--Out of check range

As you can see, lookups are made as long as the query is within the check constraints. The third query is not in scope and does not scan the table row count, only constant scan, which improves query performance.

Now perform the view update:

BEGIN tranupdate [dbo]. [V_demotab] SET insdate = ' 2005-11-01 ' WHERE id = 3000select case resource_type if ' OBJECT ' then object_name (resource_associated_ent ity_id) ELSE ' END as [Object],resource_type,resource_description,request_mode,request_status,request_type    From sys.dm_tran_locks where resource_database_id=db_id () and [email protected] @SPID  COMMIT Tran

id = 3000 in table [DEMOTAB01], the query of the view is converted to a query on the table, but the other tables are added with intent exclusive lock (IX), which will inevitably affect the access of other users in a lot of concurrency.

In the execution plan, a constant scan of non-conforming conditions does not actually read the data, but it also increases the overhead.

Reference:

Implementing a federated database server

Resolving distributed Partitioned views

Working with partitioned views

Blog: Improve performance with CHECK constraints in SQL Server

Blog: SQL Server Scale-out: design, implementation, and Maintenance (3)-implementation of distributed partitioned views

SQL Server Partitioned view implementation sub-table