After deciding on the name of a column, the database needs to know the kind of information the column will hold. Since there are various kinds of information a database can deal with, SQL Server provides a set of categories called data types. Therefore, you must specify the data type that is necessary for a particular column. The data types used on a table are the same as those we reviewed for the variables: bit: The bit is the smallest data type of the SQL Server categories of columns. It is used for a field that would validate a piece of information as being true or false, On or Off, Yes or No, 1 or 0. int: An integer, also called a natural number, or a whole number, is a number that can start with a + or a - sign and is made of digits. Between the digits, no character other than a digit is allowed. When the number starts with +, such as +44 or +8025, such a number is referred to as positive and you should omit the starting + sign; this means that the number should be written as 44 or 8025. Any number that starts with + or simply a digit is considered as greater than 0 or positive. A positive integer is also referred to (in the programming world) as unsigned. On the other hand, a number that starts with a - symbol is referred to as negative. If a column would hold numbers in the range of -2,147,483,648 to 2,147,483,647, set its data type as int. tinyint: If you except to use very small numbers such as student's ages, or the number of pages of a brochure or newspaper, apply the tinyint data type to such a field. A column with the tinyint data number can hold positive numbers that range from 0 to 255. smallint: The smallint data type follows the same rules and principles as the int data type except that it is used to store smaller numbers that would range between -32,768 and 32,767. bigint: The bigint data type follows the same rules and principles as the int data type except that its field can hold numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 binary: The binary data type is used for a column that would hold hexadecimal numbers. Examples of hexadecimal numbers are 0x7238, 0xFA36, or 0xAA48D. Use the binary data type if all entries under the column would have the exact same length (or quantity). If you anticipate that some entries would be different than others, then use the alternativevarbinary data type. The varbinary also is used for hexadecimal numbers but allows dissimilar entries, as long as all entries are hexadecimals. numeric and decimal: In some columns, users will be asked to enter particular numbers referred to as decimals. A decimal number is a number that can have a period (or the character used as the decimal separator as set in the Control Panel) between the digits. An example would be 12.125 or 44.80. Like an integer, a decimal number can start with a + or just a digit, which would make it a positive number. A decimal number can also start with a - symbol, which would make it a negative number. If the number represents a fraction, a period between the digits specifies what portion of 1 was cut. If you anticipate such a number for a field, specify its data type as numeric or decimal (either decimal or numericwould produce the same effect in SQL Server). float and real: A floating-point number is a fractional number, like the decimal type. Floating-point numbers can be used if you would allow the database engine to apply an approximation to the actual number that a field is supposed to carry. money: As its name announces it, the money data type can be used on a column whose data would consist of currency values. A field with a money data type can hold positive or negative values from -922,337,203,685,477.5808 to +922,337,203,685,477.5807 smallmoney: While the money data type can be used for a field that would hold large quantities of currency values, the smallmoney data type can be applied for a column whose value cannot be lower than -214,748.3648 nor higher than 214,748.3647 char: A field of characters can consist of any kinds of alphabetical symbols in any combination, readable or not. If you want a column to hold a fixed number of characters, such as the book shelf numbers of a library, use the char data type for such a column. varchar: In the computer world, a string is a character or a combination of characters that are considered "as is" with regards to the scenario in which they are used. In a table, some columns are meant to hold such strings. One of the most significant characteristics of such a field is that data entered into it can change from one record to another. An example would be the first names of employees: people have different first names of different lengths. If a column will hold strings of different lengths, apply a varchar data type to it. The maximum length of text that a field of varchar type can hold is equivalent to 8 kilobytes. text: The text data type can be applied to a field whose data would consist of ASCII characters. As opposed to a varchar type of field, a text type of field can hold text that is longer than 8 kilobytes. nchar, nvarchar, and ntext: These three types follow the same rules as the char,varchar, and text respectively, except that they can be applied to columns that would hold international characters, that is, characters of languages other than US English. This is done following the rules of Unicode formats. datetime: As its name suggests, a datetime data type is used for a column whose data would consist of date and/or time values. The entries must be valid date or time values but SQL Server allows a lot of flexibility, even to display a date in a non-traditional format. The date value of a datetime field can be comprised between January 1st, 1753 and December 31, 9999. smalldatetime: The smalldatetime is an alternative to the datetime data type. It follows the same rules and principles as the datetime data type except that a date value must be comprised between January 1st, 1900 and June 6, 2079. A database deals with various types of data, appropriate or not for certain fields. This means that you should take care of jobs behind the scenes as much as you can. One way you can do this is by controlling the amount of information that can be entered in a particular field. As various columns can hold different types of data, so can the same data type control its own mechanism of internal data entry. The length of data means different things to different fields. Columns that carry the same data type can have different lengths. Bit Fields: We saw already that a bit column type is meant for one of two answers. The user is supposed to simply let the database know that the answer is yes or no, true or false, on or off, 1 or 0. Therefore, the only length of this field is 1. Integers: The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes. Decimal and Floating-Point Numbers: The Length specifies how many bytes the field can store. Strings: The Length of a character or string column specifies the maximum number of characters that the field can hold. In some circumstances, you will need to change or specify the length as it applies to a particular field. For example, since you should use the varchar data type for a string field whose content will change from one record to another, not all varchar columns need to have the same length. Although a First Name and a Book Title columns should use thevarchar type, both columns would not have the same length of entries. As it happens, people hardly have a first name that is beyond 20 characters and many book titles go beyond 32 characters. In this case, both fields would use the same data type but different lengths. On the other hand, for columns of datetime and money data types, you should accept the default length suggested by the database. |
|
It is located at: