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.
nice illustration….