If a loaf can full your appetite then why to order for two loafs and waste the other one. But to this people call them stereotype of miser that is wealthy and greedy, a man who lives intelligently in order to save and increase his treasure. Same thing I have seen while creating a table and columns and assigning the data-types and their sizes, if we can full fill the requirement of the data by providing the accurate or approximate size then it will not only increase the treasure(or in technical terms we used to call performance) but also we can reduce the risk which prevails in any application. If we can use tiny int and get our work done then why to use small int or simple int and burden the database and decrease the performance. Use the best data-type matching your requirement, if it is varchar don’t just define varchar, but if you know the size define it like varchar(20) or something else. Below are few data-types and the values they accommodate. I know this is the basic of but sometimes these basic things make a major difference. So when ever creating the tables BE MISER… and don’t BE SPENDTHRIFT
|
bigint |
Integer data from -2^63 through 2^63-1 |
|
int |
Integer data from -2^31 through 2^31 - 1 |
|
smallint |
Integer data from -2^15 through 2^15 - 1 |
|
tinyint |
Integer data from 0 through 255 |
|
bit |
Integer data with either a 1 or 0 value |
|
decimal |
Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 |
|
numeric |
Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 |
|
money |
Monetary data values from -2^63 through 2^63 - 1 |
|
smallmoney |
Monetary data values from -214,748.3648 through +214,748.3647 |
|
float |
Floating precision number data from -1.79E + 308 through 1.79E + 308 |
|
real |
Floating precision number data from -3.40E + 38 through 3.40E + 38 |
|
datetime |
Date and time data from January 1, 1753, through December 31, 9999,with an accuracy of 3.33 milliseconds |
|
smalldatetime |
Date and time data from January 1, 1900, through June 6, 2079,with an accuracy of one minute |
|
char |
Fixed-length character data with a maximum length of 8,000 characters |
|
varchar |
Variable-length data with a maximum of 8,000 characters |
|
text |
Variable-length data with a maximum length of 2^31 - 1 characters |
|
nchar |
Fixed-length Unicode data with a maximum length of 4,000 characters |
|
nvarchar |
Variable-length Unicode data with a maximum length of 4,000 characters |
|
ntext |
Variable-length Unicode data with a maximum length of 2^30 - 1 characters |
|
binary |
Fixed-length binary data with a maximum length of 8,000 bytes |
|
varbinary |
Variable-length binary data with a maximum length of 8,000 bytes |
|
image |
Variable-length binary data with a maximum length of 2^31 - 1 bytes |
|
timestamp |
A database-wide unique number that gets updated every time a row gets updated |
|
uniqueidentifier |
A globally unique identifier |
|
sql_variant |
A data type that stores values of various data types,except text, ntext, timestamp, and sql_variant |
|
varchar(max) |
Maximum 1,073,741,824 characters |
|
text |
Maximum 2GB of text data |
|
nvarchar(max) |
Maximum 536,870,912 characters |
|
ntext |
Unicode data. Maximum 2GB of text data |