Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.