Partition view from spring book)

The partition view of SQL Server provides a method for managing large data volumes, similar to the partition view of other database management systems. This not only enables centralized table management, but also enables rapid data locating. Below I will take a log record table as an example to describe the role of the following Partition view:

The log record table can record the log information for a long time. Over time, the log table will become very large. In this way, the query and maintenance operations on tables are very slow, but if we divide tables into several small tables by date, and logically use a unified view for query management, it is very convenient. in addition, SQL Server can automatically locate a table based on the query conditions, which increases the query speed.

First, we need to implement a log recording stored procedure. This stored procedure does not simply insert logs, but also determines whether the table exists based on the current date, and then dynamically creates a table, create check constraints According to the partition table rules. in this example, we use the week date. The table name is specified as follows: DBO. [20061218-20061224]

SQL code
Use Testdb
If   Exists ( Select Name From Sysobjects Where Name =   ' P_logwriter '   And Type =   ' P ' )
Drop   Proc DBO. p_logwriter
Go
-- -- Log file

Create   Proc DBO. p_logwriter
  @ DESC   Varchar ( 1000 )
As
Begin   Tran
  Set Nocount On

Declare   @ Wk_start   Datetime , @ Wk_end   Datetime
  Declare   @ Str_start   Varchar ( 50 ), @ Str_end   Varchar ( 50 )
  Declare   @ Tb_name   Varchar ( 100 ), @ SQL   Varchar ( 4000 )

-- Start date of week
  Set   @ Wk_start = Dateadd (D, 2 - Datepart (DW, Getdate ()), Convert ( Char ( 10 ), Getdate (), 120 ))
  -- End Date of week
  Set   @ Wk_end = Dateadd (S, - 1 , Dateadd (Wk, 1 , @ Wk_start ))

Set   @ Str_start = Convert ( Varchar ( 50 ), @ Wk_start , 120 )
  Set   @ Str_end = Convert ( Varchar ( 50 ), @ Wk_end , 120 )

-- Table Name
  Set   @ Tb_name = Convert ( Varchar ( 10 ), @ Wk_start , 112 ) + ' - ' + Convert ( Varchar ( 10 ), @ Wk_end , 112 )

-- If the table does not exist, create a table.
  If   Not   Exists ( Select   1   From Sysobjects Where Name = @ Tb_name   And Xtype = ' U ' )
  Begin
Set   @ SQL = ' Create Table DBO .[ ' + @ Tb_name + ' ] (ID int identity (1, 1), updatetime datetime primary key check (updatetime ''' + @ Str_start + ''' And ''' + @ Str_end + ''' ), [DESC] varchar (1000 )) '
-- Print @ SQL
Exec ( @ SQL )
  End

-- Insert Log Data
  Set   @ SQL = ' Insert [ ' + @ Tb_name + ' ] (Updatetime, [DESC]) Select getdate (), ''' + @ DESC + ''''
  Exec ( @ SQL )

 SetNocountOff
Commit Tran
Go

-- Create partition View

Create   View V_log
As
Select   *   From DBO. [ 20061218-20061224 ]
Union   All
Select   *   From DBO. [ 20061225-20061231 ]
Go

-- Query partition View

Select   *   From V_log
Where Updatetime < ' 2006-12-24 11:21:27. 653 '

 

-- Through the analysis of the query execution plan, we can see that SQL server can automatically locate the corresponding table to obtain data, thus improving the query speed.