Home > SQL Server > A Column Storage Calculator

A Column Storage Calculator


Proper table design requires a clear understanding of business requirement. There are two factor that affects the performance of the database system and storage. one, poor data modeling and two, column data storage.

I am not going to talk about the data modeling now. but the storage, while we are converting the business requirements into table design. we should consider the best storage for each attributes in the relation data model.

excess or less storage allocation leads to table or application modification more frequently. Excess storage leads to poor performing SQL queries.

There are different ways to determine the storage requirement for an attribute. But, when we are not sure about the data storage size. we can use the build in function to make efficient decision.

DATALENGTH()

This function tells you how many bytes are used to represent a given value or expression.

Example 1:

SELECT DATALENGTH(99999999.999999999999999999) – Length is 13 bytes

SELECT DATALENGTH(CAST(‘99999999.999999999999999999’ as NUMERIC(28,18))) – Length is 13 bytes

Example 2:

SELECT DATALENGTH(CAST(‘SQLServerRider’ AS VARCHAR(50))) – Actual storage size is 14 bytes and the declared storage size is 50.

SELECT DATALENGTH(‘SQLServerRider             ‘) – Returns 27 bytes as a fixed char length.

Thanks for reading.

Advertisements
  1. Manikanan
    August 30, 2012 at 12:40 pm

    nice illustration….

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: