Whether the SQL Server Computing column occupies space

Source: Internet
Author: User
There is a problem on the internet today: Check whether the SQLServer computing column occupies space. In fact, check the MSDN or BOL to see the result. When creating a calculated column, you can specify PERSISTED as a parameter. Use this

There is a problem on the internet today: Check whether the SQLServer computing column occupies space. In fact, check the MSDN or BOL to see the result. When creating a calculated column, you can specify PERSISTED as a parameter. Use this

I have a question on the internet today: Does the SQL Server Computing column occupy space?

In fact, you can find out the result by checking the MSDN or BOL. When creating a calculated column, you can specify PERSISTED as a parameter. You can use this parameter to specify that the database engine will physically store computing values in the table, which is an American server, and update these computing values when any other column on which the computing column depends is updated. Additionally, when you mark a computed column as PERSISTED, you can create an index for a deterministic but inaccurate computed column. This improves performance in the U.S. space and Hong Kong servers.


If the PERSISTED parameter is not used, the calculation column does not occupy disk space, but the value must be calculated when querying the calculation column. This will affect the performance (space for performance ).


Here we will test:

USE tempdb

GO

-- CreateTable

Create table UDFEffect (id int,

FirstName VARCHAR (100 ),

LastName VARCHAR (100 ))

GO

-- Insert OneHundred Thousand Records

Insert into UDFEffect (ID, FirstName, LastName)

BY a. name) RowID,

'Bob ',

BY a. name) % 2 = 1 THEN 'Smith'

ELSE 'browser' END

A

B

GO

-- Check thespace used by table

Sp_spaceused 'udfeffect'

GO

-- AddComputed Column

Alter table dbo. UDFEffect ADD

FullName LastName)

GO

-- Check thespace used by table

Sp_spaceused 'udfeffect'

GO


We can see that the table size has not changed, and the data page has not grown.

-- AddComputed Column PERSISTED

Alter table dbo. UDFEffect ADD

FullName_P LastName) PERSISTED

GO

-- Check thespace used by table

Sp_spaceused 'udfeffect'

GO


Using the PERSISTED parameter, we can see that the data is growing.

-- Clean upDatabase

Drop table UDFEffect

GO

This article is from the "follow SQL Server Technology" blog. Be sure to keep this source

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.