Wednesday, October 10, 2007

Data Types

Data types defined for SQL Server http://msdn2.microsoft.com/en-us/library/aa258271(SQL.80).aspx

Difference between varable and fixed -

When you design your tables, it helps to understand the tradeoffs of using variable length columns versus fixed length columns. Variable length columns reduce database size because they take only what is required to store the actual value. Fixed length columns always take maximum space defined by the schema, even when the actual value is empty. The downside for variable length columns is that some operations are not as efficient as those on fixed length columns. For example, if a variable length column starts small and an UPDATE causes it to grow significantly, the record might have to be relocated. Additionally, frequent updates cause data pages to become more fragmented over time. Therefore, we recommend that you use fixed length columns when data lengths do not vary too much and when frequent updates are performed (Microsoft - http://technet.microsoft.com/en-us/library/ms172432.aspx.

Unicode vs. Non-Unicode - The primary difference between unicode and non-unicode data types is the ability of unicode to easily handle the storage of foreign language characters which also requires more storage space.