Why DataTypes are important to use?

This is an example of a signup page of a website.

There are three input fields, they are-

  • First Name
  • Last Name
  • Contact number

Here, First and Last Names will always be alphabetic.

Contact will always be numeric.

Contact

From the above image, you can determine that “First and Last Name” are used as the character and “Contact Number” as an integer.

As you know, a name cannot be a number and a contact number cannot be a character, it is important to define “First and Last Name” as a character and “Contact Number” as an integer. It needs to be noted that every field in every application contains at least one sort of data. such as date, alphabetic, numeric, and many others.

Also, take note that different datatypes demand different amounts of memory. For effective memory usage, it makes more appropriate to specify the column or variable with the data type it would contain.

Wish to get certified in SQL? Learn SQL from top SQL experts and excel in your career with intellipaat’s SQL Server certification.

SQL Server Data Types

The three basic kinds of data types supported by Microsoft SQL Server are-

  • String Data Types
  • Numeric Data Types
  • Date and Time Data Types

String Data Types

Names, addresses, descriptions, and any value containing both letters and numbers, including binary data, such as image or audio files, are typically stored in string data types.

Data type Description
char(n) A fixed-length character string is stored. The maximum  character count is 8,000.
varchar(n) Stores a character string of variable length. The maximum character count is 8,000.
varchar(max) Stores a character string of variable length. In this case, max denotes a maximum storage size of 2 GB.
text Stores a character string of variable length. 2 GB is the maximum storage size.
nchar A fixed-length Unicode string is stored. The maximum character count is 4,000.
nvarchar A variable-length Unicode string is stored. The maximum character count is 4,000.
nvarchar(max) A variable-length Unicode string is stored. In this case, max denotes a maximum storage size of 2 GB.
ntext A variable-length Unicode string is stored. 2 GB is the maximum storage size.
binary(n) Stores binary data of a fixed length. A maximum of 8,000 bytes may be stored.
varbinary(n) Stores binary data with variable length. A maximum of 8,000 bytes may be stored.
varbinary(max) Stores binary data with variable length. In this case, max denotes a maximum storage size of 2 GB.
image Stores binary data with variable length. The largest storage size is 8,000 bytes.

Learn more about SQL Server through SQL Server Tutorial.

Numeric Data Types

Consider a numeric data type when you need to execute mathematical operations on SQL data. You can store numbers that you know will be utilized in a mathematical statement by using numeric data types.

To store data like price, salary, etc numeric data types are used.

Data type Description
bit Enables the storage of a value of 1, 0, or NULL.
tinyint Contains integer data with a value range of 0 to 255.
smallint Contains integer values between -32,768 and 32,767.
int The storage of integer numbers between -2,147,483,648 and 2,147,483,647.
bigint Contains integer data between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
decimal(p,s) Numbers with set precision and scale are stored. The range of acceptable values is 1038+1 to 1038-1.
numeric(p,s) Decimal is functionally equivalent to the numeric datatype.
smallmoney Gives you the ability to precisely store monetary or currency amounts between -214,748.3648 and 214,748.3647.
money Enables you to accurately store money or currency values between -922,337,203,685,477.5808 and 922,337,203,685,477.5807.
float(n) Keeps floating-point numerical values. The acceptable ranges are 0 and 2.23E-308 to 1.79E+308, respectively.
real Stores floating-point numerical values. The range of acceptable values is 3.40E + 38 to -1.18E – 38, 0, and 1.18E – 38 to 3.40E + 38.

Date and Time Data Types

When storing dates and exact times, date or time-based data types are utilized. These data types were created with the purpose of storing a date or date and time.

Date

Select the Date data type if you need to record dates without a specific time associated with them. The format of the date type is YYYY-MM-DD, which holds the year, month, and day.

Datetime

Use the DateTime data type if you must hold both a date and an hour. In addition to storing dates in the same format as dates (YYYY-MM-DD), the Datetime data type also includes times in the format HH:MM:SS:MS, down to the millisecond (MS).

Data type Description
date Stores a date value between January 1 (0001-01-01) and December 31 (9999-12-31). (December 31, 9999).
time A day’s worth of time is kept with a precision of 100 nanoseconds. The range of possible values is 00:00:00.0000000 to 23:59:59.9999999.
datetime Stores a time and date value that are 3.33 milliseconds accurate. Datetime is only valid for dates between 1753-01-01 (January 1, 1743) and 9999-12-31. (December 31, 9999).
datetime2 The datetime2 data type is an expansion of the datetime data type with a wider date range. Datetime2’s valid date range is from 0001-01-01 (January 1, 1) to 9999-12-31. (December 31, 9999).
smalldatetime Stores a time and date value that is accurate to the minute. Smalldatetime accepts dates between 1900-01-01 (January 1, 1900) and 2079-06-06. (June 6, 2079).
datetimeoffset Similar to datetime2, except with the time zone offset added. The standard format is hh:mm:ss[.nnnnnn] [+|-hh:mm]. The time zone offset has a suitable range of -14:00 to +14:00.
timestamp The row version data type in SQL Server, which creates distinct binary integers automatically within a database, is referred to as a timestamp. Typically, table rows are version-stamped using the row version.

Drop any of your queries in our SQL Server Community and start a discussion.

Course Schedule

Name Date Details
SQL Training 11 Feb 2023(Sat-Sun) Weekend Batch
View Details
SQL Training 18 Feb 2023(Sat-Sun) Weekend Batch
View Details
SQL Training 25 Feb 2023(Sat-Sun) Weekend Batch
View Details

Leave a Reply

Your email address will not be published. Required fields are marked *