SQL server-focused computed column persistence (21)

Objective

In the previous section we ended the description of hash Match aggregate and stream aggregate, this series we talk about computed column issues in SQL Server, short content, in-depth understanding, always to review the basics.

Preliminary discussion of computed column persistence (compued column Persisted)

With the introduction of computed columns in SQL Server 2005, let's first look at MSDN on the definition of computed columns: computed columns are evaluated by expressions that can use other columns in the same table. An expression can be a column name, constant, function, or any combination of the above elements that are concatenated with one or more operators, in a non-computed column. An expression cannot be a subquery. In fact, it is to define a column for other columns to do the calculation can be columns name, function, etc., then what is its usage scenario? Let us first give an example. When you need to export some values, these values need to be calculated to be exported, while some columns also depend on another column or more columns, if a column is updated, its dependent columns must be updated synchronously, the above scenario by the calculation of one column or more columns, At this point we need to define a column that calculates the value of one or more columns, which is the computed column. Let's look at a typical example where all employees working in a company will have information about all their employees in the company's internal system, such as the employee number, date of birth, etc., if we need to export the employee's retirement date at this point, assuming that in China now the male retirement time is 60 years, At this point we need to calculate the date 60 years after the date of birth, and also say that a retirement date column needs to be defined in the table. Let's create a table to look at the computed column.

Use Tsql2012gocreate TABLE employee (    --employee number    Employeebirth DATETIME not NULL --date    of birth  (Employeebirth)-(1--retirement date )

At this point we see the design of the retirement date in the table to show that it is already persistent

Next we insert the test data to see

Use the tsql2012goinsert into dbo. Employee (EmployeeNumber, Employeebirth) SELECT305423,'1985-12-13'UNION Allselect587650,'1989-11-18'UNION Allselect221836,'1990-01-19'UNION Allselect746104,'1993-06-13'UNION Allselect139024,'1995-07-23' 

Then we'll check the table.

* FROMdbo. Employee

At this point we get each employee's retirement date by querying the employee table, there is no problem here, since we set it to be persistent, also said that when the other column changes when the computed column will also change, suddenly one day numbered 305423 of employees and input information of the colleague to communicate, He is actually born in 1986, the above 1985 is the ID card, id wrong, at this time we need to update their birth date to 1986, as follows

' 1986-12-13 ' ' 305423 '

Then we'll look at the data.

At this point we find that when the date of birth changes, the corresponding computed column is also synchronized from the original 2045 update to 2046, the above we added in the computed column added the persisted keyword, is not because the addition of this keyword causes persistence so that when a column is updated, Computed columns are also updated synchronously, is this the role of persisted persistence, is not the case, when you remove the persisted keyword at this time will also be synchronized update (do not believe you can try), then the role of persisted keyword is what? The fact is that when we create a computed column on a column, the computed data does not have a column (and I don't know where it exists), and the computed data is computed at run time, and when the computed column is identified with the persisted keyword, the computed column will be present in the table. Continue to look at data storage space usage to be verified.

Further exploration of data storage space through computed column persistence

Let's take a look at the table data storage when no computed column is added, a computed column is added, and the computed column is persisted. Let's create a test table.

Use tsql2012gocreate TABLE [dbo]. [Computecolumn] (ID int,firstname varchar (+), LastName varchar) GO

Inserting 100,000 of data into a table

100000 row_number () over (ORDER by A.name) RowID, ' Bob ' , Case if Row_number () over (ORDER by A.name)%21'Smith'  'Brown'  endfrom sys.all_objects aCROSS JOIN sys.all_objects BGO 

Now let's take a look at the table storage space usage

' [Computecolumn] ' GO

The above we learned that the storage data is 2680KB, let's create a computed column below to see.

ALTER TABLE dbo. [Computecolumn] Addfullname as (FirstName+"+LastName) GO

From here we can conclude that when we create a computed column, its data doesn't exist at all, so let's see what happens when we add persistent keywords.

ALTER TABLE dbo. [Computecolumn] Addfullname_p as (FirstName+"+LastName) Persistedgo

When the persisted keyword is added, the table storage data space becomes 4784KB, which verifies that when the persisted keyword is not added, the data on the computed column is evaluated at run time but not on the column, and when the computed column is identified with the persisted keyword, the data exists on the column.

Drill down into data storage space with computed column persistence

We know that if you create an index on a column, you certainly need some space to store the index, and we persisted the column, which increases the table storage space, and if we create an index, does it increase the size of the table data storage? Before creating a computed column, we create an index to look at the various data space storage sizes in its table, that is, to create an index on the column fullname created.

Use tsql2012gocreate nonclustered INDEX idx_comcol_fullnameon dbo.computecolumn (FullName)

Because the index is created, it just causes the index space to become larger, and then we create a computed column persistence and see its tablespace usage

Use tsql2012goalter TABLE dbo.computecolumn addfullname_p as (FirstName+"+lastname) PERSISTED

From the above we can see that increasing the index does not result in an increase in the size of the table data, while creating a computed column persistence requires additional space. Analysis so far, let's give a basic conclusion:

Computed column Analysis Conclusion: The purpose of the computed column is mainly used for multiple computations and more complex calculations, and if the computed column is persisted it can significantly reduce the computational overhead but it will increase disk space.

Summarize

In this section we learned the basics of calculating columns and persisting them, and in the next section we talk about the performance issues of computed columns and the persistence of computed columns, short content, in-depth understanding, and we'll see you next week.

SQL server-focused computed column persistence (21)