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