Production Database Report: Why choosing proper data type for column is important?

UPDATED: 26 January 2015
Microsoft SQL Server Report

Database is one of the important part of any web application. Respect the fact that database is much more important than software. However many of you don't care about what data type you are choosing for new column in database table. I've prepared analysis report from production database in Microsoft SQL Server.

Initial Report of Table
Table has 48 columns in it, out of them 16 were with "VARCHAR" data types. Out of 16 column, there were 13 with VARCHAR(MAX). Column names and its initial data types given at the end of the article.

Total Rowcount 17931710
Total Space Occupied Including Index 9319 MB


Steps taken to improve
Step 1: Find the maximum length of value stored in column. SELECT MAX(LENGTH(COLUMN_NAME)) FROM TABLE_NAME.
- Consider a scenario for a column you got Maximum length of value 10,15...20, It means you are storing some predefined status in it and you don't need VARCHAR(MAX)/NVARCHAR(MAX) for it. Discuss with seniors or team member for length of column.
- Perform above step for all column of VARCHAR/NVARCHAR data type. And redesign your table with new length for VARCHAR/NVARCHAR data type column.

Step 2: Create new table with refined data types.

Step 3: Insert data from Old Table to New Table.(Use IDENTITY_INSERT to preserve old ID value of table. Read more https://msdn.microsoft.com/en-us/library/ms188059.aspx)

Step 4: Create existing indices on new table.

Step 5: Delete old table.

Step 6: Rename new table with old/original table name.

I followed the above steps and got the unexpected result. This reports shows the importance of choosing proper data types.

Total Rowcount 17931710
Total Space Occupied without Index 4317 MB
Total Space Occupied with Index 6493 MB
Space occupied by new table 4317(Data) + 2176(Index) = 6493

Previously size of table was 9319 MB with redefined data types its 6493 MB and we've just decreased 2826 MB from table.

Column Name Length New Lenght
****_value max >> max
hash max >> 500
****_status 255 >> 30
sign max >> 500
****_type 255 >> 30
****_message max >> 150
****_value max >> max
****_key max >> 500
****_public_key max >> 500
****_status max >> 20
****_key_before max >> 500
****_key_before_**** max >> 500
****opes max >> 500
****_values max >> 500
****_info max >> 500
currency_**** 1000 >> 500


Note
Currently I'm not sure about altering column size using ALTER. I think its just meta data change, It won't decrease the size of table. I'll test it and let you know the result of it.

0 comments :