How to calculate whether the column occupies space in SQL Server
SQL Server how to calculate whether the column occupies space, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.
Today, there is a question on the Internet: whether the SQL Server computing column takes up space or not.
In fact, if you check MSDN or BOL for this question, you can know the result. When you create a computed column, you have a parameter that specifies PERSISTED. Use this parameter to specify that the database engine will physically store calculated values in the table and update them when any other columns on which the calculated columns depend are updated. And marking computed columns as PERSISTED can improve performance by indexing computed columns that are deterministic but imprecise.
If the PERSISTED parameter is not used, the computed column will not take up disk space, but the value will be calculated when querying the computed column, which will affect performance (space for performance).
Here's a 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)
SELECT TOP 100000 ROW_NUMBER () OVER (ORDER BY a.name) RowID
'Bob'
CASE WHEN ROW_NUMBER () OVER (ORDER BY a.name)% 2 = 1 THEN 'Smith'
ELSE 'Brown' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check thespace used by table
Sp_spaceused 'UDFEffect'
GO
-- AddComputed Column
ALTER TABLE dbo.UDFEffect ADD
FullName AS (FirstName+''+ LastName)
GO
-- Check thespace used by table
Sp_spaceused 'UDFEffect'
GO
You 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 AS (FirstName+''+ LastName) PERSISTED
GO
-- Check thespace used by table
Sp_spaceused 'UDFEffect'
GO
Using the PERSISTED parameter, you can see the growth of the data.
-- Clean upDatabase
DROP TABLE UDFEffect
GO
After reading the above, have you mastered the method of calculating whether the column takes up space in SQL Server? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!
