SQL Server partitioned View implementation level 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:

[SQL]View PlainCopy

 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]  GO  CREATE TABLE [dbo].[DEMOTAB02](      [ID] [int]  not NULL,      [insdate] [datetime] NULL  )  on [PRIMARY]  GO  CREATE TABLE [dbo].[DemoTab03](      [ID] [int]  not NULL,      [insdate] [datetime] NULL  )  on [PRIMARY]  GO    --constrain the scope of each tableALTER TABLE [dbo].[DEMOTAB01]  with CHECK ADD CONSTRAINT [ck_demotab01_id] CHECK([ID] between 0  and 99999 )  GO  ALTER TABLE [dbo].[DEMOTAB02]  with CHECK ADD CONSTRAINT [ck_demotab02_id] CHECK([ID] between 100000  and 199999 )  GO  ALTER TABLE [dbo].[DemoTab03]  with CHECK ADD CONSTRAINT [ck_demotab03_id] CHECK([ID] between 200000  and 299999 )  GO    --since the ID is divided by ID, it is easier to locate it as a clustered indexALTER TABLE [dbo].[DEMOTAB01] ADD CONSTRAINT [pk_demotab01_id] PRIMARY KEY CLUSTERED([ID] ASC)  GO  ALTER TABLE [dbo].[DEMOTAB02] ADD CONSTRAINT [pk_demotab02_id] PRIMARY KEY CLUSTERED([ID] ASC)  GO  ALTER TABLE [dbo].[DemoTab03] ADD CONSTRAINT [pk_demotab03_id] PRIMARY KEY CLUSTERED([ID] ASC)  GO      --each field name is listed to avoid asterisks, otherwise an error occurs when the upgrade and delete fields are different--DROP VIEW [dbo]. [V_demotab]CREATE VIEW [dbo].[V_demotab]   as  SELECT [ID],[insdate]  from [dbo].[DEMOTAB01]  UNION  All  SELECT [ID],[insdate]  from [dbo].[DEMOTAB02]  UNION  All  SELECT [ID],[insdate]  from [dbo].[DemoTab03]  GO    --121317 rows of dataINSERT  into [V_demotab]([ID],[insdate])  SELECTSalesorderdetailid,modifieddate fromAdventureWorks2012.Sales.SalesOrderDetailGO      SELECT COUNT(*) from [dbo].[V_demotab]  SELECT COUNT(*) from [dbo].[DEMOTAB01]  SELECT COUNT(*) from [dbo].[DEMOTAB02]  SELECT COUNT(*) from [dbo].[DemoTab03]  

[SQL]View PlainCopy

1. --View Query now
2. SELECT * from [dbo].[ V_demotab] WHERE id = 0 --not present
3. SELECT * from [dbo].[ V_demotab] WHERE id = ---only that ID has a record
4. 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:

[SQL]View PlainCopy

2. BEGIN TRAN
4. UPDATE [dbo]. [V_demotab] SET insdate = ' 2005-11-01 ' WHERE id =
8. Select Case resource_type if ' OBJECT ' then object_name (resource_associated_entity_id) ELSE " END as [object]
10. , Resource_type,resource_description,request_mode,request_status,request_type
12. From sys.dm_tran_locks where resource_database_id=db_id () and [email protected] @SPID
14. 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.

SQL Server partitioned View implementation level sub-table