SQL Server Data Type

Determining the right data type is a good database implementation and administration technique. By implementing effective and efficient data types, you can save hard drive space and data processing time from your SQL Server.

Here are some of the built-in data types found in SQL Server along with the space required on the hard drive when implementing it:



  • Bit
The bit data type can only accept input numbers 1 and 0 as values (or it could be null, meaning no value). This data type is helpful if you want to generate the output yes / no, true / false, etc.

  • Int
This data type may already be familiar to you. This data type can receive values ranging from -231 (-2,147,483,648) to 231-1 (2,147,483,647). This data type consumes 4 bytes to store data on the hard drive.

  • Bigint
This data type is similar to int, only the received value is greater than int. This data type can receive values ranging from -263 (-9,223,372,036,854,775,808) to 263-1 (-9,223,372,036,854,775,807). This data type consume 8 bytes to store data on the hard drive.

  • Smallint
This data type is also similar to int, only the received value is smaller than int. This data type can receive values ranging from -215 (-32,768) to 215-1 (32767). This data type requires only 2 bytes to store data on the hard drive.

  • Tinyint
This data type receives a value smaller than smallint. Acceptable values range from 0 to 255, and require only 1 bytes to store data on the hard drive.

  • Decimal
This data type receives a more precise value than the integer data types discussed earlier. This data type uses 2 parameters to determine the level of precision of the value received; precision and scale. Precision is the number of digits that can be received by the field, while the scale is the number of numbers behind the comma that can be accepted by the field. So, if we make the precision parameter as much as 5 and scale 2 then field we can accept value like this: 123,45. This data type can receive values ranging from -1038 to 1038-1. This data type consumes 5-17 bytes to store data on the hard drive, depending on the precision of the inserted value.

  • Numeric
This data type is basically the same as the decimal data type. So this data type can be called a synonym of decimal.

  • Money
This data type can receive values ranging from -263 (-9,223,372,036,854,775,808) to 263-1 (-9,223,372,036,854,775,807). This data type consumes 8 bytes to store data on the hard disk.

  • Smallmoney
This data type is basically the same as the data type of money, only the received value is smaller, ranging from -214,748,3648 to 214,748,3647. This data type consumes 4 bytes to store data on the hard drive.

  • Float
This data type is similar to the decimal data type, it's just that the paramater scale on this data type can accept infinite value, as in the value of pi. This data type can receive values ranging from -1.79E + 308 to 1.79E +308. If you are describing a field with a data type like this: float (2), then the output value of pi (for example) is 3.14. The number 2 inside the brackets explains how many numbers should be displayed behind the comma. This data type consumes 4-8 bytes to store data on the hard drive.

  • Real
This data type is similar to the float data type, it just receives a smaller value than the float, which starts from -3.40E +38 to 3.40E +38. This data type consumes 4 bytes to store data on the hard drive.

  • Datetime
This data type can receive date and time values from 1 January 1753 to 31 December 9999. This data type consumes 8 bytes to store data on the hard disk..

  • Smalldatetime
This data type can receive dates and times from January 1, 1900 to June 6, 2079, with the accuracy of the time used is minutes. This data type consumes 4 bytes to store data on the hard disk..

  • Timestamp

This data type is used to record records when new data is inserted and updated. This data type is very useful to find out the changes that occur in your database.

  • Uniqueidentifier
This data type works to create a unique value that might look like this 6F9619FF-8B86-D011-B42D-00C04FC964FF. This data type is useful if you want to create a unique serial number or id.

  • Char
This data type can be used to enter non-Unicode character data with a fixed number of characters. This data type can accept up to 8000 characters, and the number of bytes required depends on the number of characters entered. 1 character requires 1 bytes, so if you define it like this: char (5) then the field can only accept characters as many as 5 characters with space needed to store data on hard drive as much as 5 bytes.

  • Varchar
This data type is similar to the char data type, but this data type is useful for you who do not know exactly the number of characters that will be entered by the user. This data type can also receive values up to 8000 characters. So if in the char data type, you define char (5), then you will always need 5 bytes to store data on the hard disk, although the number of characters entered is only 1 to 4 characters; then in this type of data, the number of bytes required will be more flexible. For example if you define varchar (30) for a field, then the field can receive data up to 30 characters (30 bytes), but if you only enter 1 character, then the number of bytes required is only 1 bytes.

  • Varchar(max)
This data type is also similar to varchar, it's just, the acceptable value reaches 2^31-1 (2,147,438.67) bytes of data.

  • Nchar
This type of data is similar to the char data type, but this data type can accept Unicode values or data (in contrast to char data types that can only accept non-Unicode character values). This data type can receive values up to 4000 characters. This data type consumes 2-8000 bytes to store data on the hard disk. Why it takes 2-8000 bytes? Because this data type multiplies 2 bytes for each character. So if the user only enter 1 character, it takes 2 bytes to store data on the hard drive.

  • Nvarchar
This data type is similar to the varchar data type, but this data type can accept Unicode values or data. This data type can also receive values up to 4000 characters.

  • Nvarchar(max)

This data type is similar to varchar data type (max), but this data type can accept Unicode value or data. This data type can accept characters up to 2 31 - 1 (2,147,483,67) bytes data.

  • Binary
This data type can receive binary data with a maximum of 8000 bytes of data. This data type is interpreted as a string of bits eg (110011001011).

  • Varbinary
This data type is similar to varchar, it's just acceptable value only binary data. This data type is useful for storing unknown binary data with exact number of bytes of data.

  • Xml
This data type is useful for storing data in XML Document format. This data type can store data up to 2Gb. This data type is a new data type contained in SQL Server.


That's it, a brief explanation of some data types contained in SQL Server. Although there are actually some other types of data built-in, but the data type above is the type of data that we often use.

Share this

Related Posts

First

2 comments

comments
November 14, 2010 at 10:07 AM delete

thanks brooo...
tugas kuliah jadi lancar...
penjelasannya lengkap banget..

Reply
avatar