CODEWITHSUNDEEP
sql-serverdatabase
miguelbgouveia
miguelbgouveia

Reputation: 2973

SQL Server redesign to reduce disk space

We have an SQL Data Base that will be redesign. We are going to change some column types from INT to TINYINT. With this transformation we can reduce the length in disk of the data base or this transformation will not reduce disk space considerably? The data base version is SQL Server 2008 R2 32bits.

Upvotes: 0

Views: 388

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

We are going to change some column types from INT to TINYINT.

Note 1 (my point of view): The real problem isn't about storage on disk but about buffer pool.

Note 2: Choosing the right data type for a column (ex. MyColumn) influences the size of following database structures:

  1. First the heap/clustered index size and then
  2. {Non-clustered index|xml index|spatial index} size if MyColumn if part if clustered index key because clustered index key is duplicated in every non-clustered index row, is duplicated in every primary xml index (I'm not 100% sure: and in every secondary xml 'FOR PROPERTY' index) and is duplicated in every spatial index.
  3. Non-clustered index size if MyColumn is part of index (as a key or as a covered column (SQL2005+, see CREATE ... INDEX ... ON ... INCLUDE()).
  4. If MyColumn has {PRIMARY KEY constraint|UNIQUE constraint+NOT NULL|UNIQUE index+NOT NULL} and if there are foreign keys that reference MyColumn then those FKs must have the same data type, max length, precision, scale and collation like MyColumn (PK/UQ). Of course, these FKs can be, also, indexed (clustered/non-clustered index).
  5. If MyColumn is included in a indexed view. Indexed views can have unique clustered and non-clustered indices so, MyColumn can be duplicated again.
  6. If MyColumn has {a PRIMARY KEY constraint|a UNIQUE constraint+NOT NULL|a UNIQUE index+NOT NULL} them is duplicated in every full-text index that references this column (see CREATE FULLTEXT INDEX ... KEY unique_index_name).
  7. Topic non-covered: column-store indices (SQL2012+).

So, yes, changing the data type for some columns might have a big impact. You can use this script:

SELECT  i.object_id, i.index_id, i.name, i.type_desc, ips.page_count, ips.*
FROM    sys.indexes i
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ips ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE   i.name IN (
    N'PK_PurchaseOrderHeader_PurchaseOrderID', ...
)
ORDER BY i.object_id, i.index_id;

to find out how many pages have every {heap structure|index} before and after data type change.

Note 3: If you have Enterprise Edition then you could compress data (link 1, link 2).

Upvotes: 3

Meff
Meff

Reputation: 5999

It will reduce the row size by 3 bytes for each column you change from INT to TINYINT. You may need to reclaim the space manually afterward though. Rebuilding the clustered index will do this.

Upvotes: 1

Related Questions