SQL server, SQLServerPedia Syndication

Finding the size of a data type.

When doing capacity planning, it is important to know the space that will be required for a row of data. This will also help with performance because if you can change your schema to pull in more rows into a buffer page (without degrading other aspects), then you will have better performance.

One function you can use is the DATALENGTH function which will show you the number of bytes used to represent any expression. Consider the data/expression: ‘20130101‘, which is a date. To see the effects on size when using different data types, see below.

SELECT DATALENGTH(CAST(‘20130101’ as char))  — 30 bytes
SELECT DATALENGTH(CAST(‘20130101’ as binary))  — 30 bytes
SELECT DATALENGTH(CAST(‘20130101’ as nvarchar))  — 16 bytes

SELECT DATALENGTH(CAST(‘20130101’ as char(8)))  — 8 bytes
SELECT DATALENGTH(CAST(‘20130101’ as varbinary))  — 8 bytes
SELECT DATALENGTH(CAST(‘20130101’ as text))  — 8 bytes
SELECT DATALENGTH(CAST(‘20130101’ as varchar))  — 8 bytes
SELECT DATALENGTH(CAST(‘20130101’ as image))  — 8 bytes

SELECT DATALENGTH(CAST(‘20130101’ as int))  — 4 bytes
SELECT DATALENGTH(CAST(‘20130101’ as date))  — 3 bytes

You can see how 10 times the data could be stored if the column was a date type as compared with a char type.

Advertisements
Standard

Thinking about someting? 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