SQL Server DataTypes: Varchar, Numeric, Date Time

What is Datatype?

A datatype is defined as the type of data which any column or variable can store in MS SQL Server.

While creating any table or variable, in addition to specifying the name, you also set the Type of Data it will store. sql server dba training from industrial experts.

How to use MS SQL datatype

• You need to define in advance, the type of data a column or variable can store. Determining data type also restricts the user from entering any unexpected or invalid data.
• You can make efficient use of memory by assigning an appropriate data type to variable or column which will allocate only the required amount of system memory for the respective column’s data.
• MS SQL offers a broad category of the datatype as per user’s needs. Like Date, binary images, etc.

Why use DataTypes?

Let’s, take a sample of simple Sign up page of website application.Three input fields are First Name, Last Name & Contact number.

Here we should note that in real time:

• “First/Last Name” will always be alphabetic.
• “Contact” will always be numeric.
• From the above picture it worth defining “First/Last Name” as a character and “Contact” as an integer.

It is evident that in any application, all fields have one or the other type of data. E.g., numeric, alphabetic, date, and many more.

Also, note that different datatype has different memory requirement. Therefore, it makes more sense to define the column or variable with the data type it will hold for efficient use of memory. sql dba course along with certification and real time projects.

Data type available in MS SQL

MS SQL server support following categories of Data type:

• Exact numeric
• Approximate numeric
• Date and time
• Character strings
• Unicode character strings
• Binary strings
• Other data types

Exact Numeric

Exact numeric has nine types of sub-data types.

Exact Numeric Data TypesData TypeDescriptionLower limitUpper limitMemorybigintIt stores whole numbers in the range given−2⁶³ (−9,223,372, 036,854,775,808)2⁶³−1 (−9,223,372, 036,854,775,807)8 bytesintIt stores whole numbers in the range given−2³¹ (−2,147, 483,648)2³¹−1 (−2,147, 483,647)4 bytessmallintIt stores whole numbers in the range given−2¹⁵ (−32,767)2¹⁵ (−32,768)2 bytestinyintIt stores whole numbers in the range given02551 bytebitIt can take 0, 1, or NULL values.011 byte/8bit columndecimalUsed for scale and fixed precision numbers−10³⁸+110³⁸¹−15 to 17 bytesnumericUsed for scale and fixed precision numbers−10³⁸+110³⁸¹−15 to 17 bytesmoneyUsed monetary data−922,337, 203, 685,477.5808+922,337, 203, 685,477.58078 bytessmallmoneyUsed monetary data−214,478.3648+214,478.36474 bytes

Examples:

Query:

DECLARE @Datatype_Int INT = 2
PRINT @Datatype_Int

Output: 2

Syntax: Decimal (P,S)

Here,

• P is precision
• S is scale

Query:

DECLARE @Datatype_Decimal DECIMAL (3,2) = 2.31
PRINT @Datatype_Decimal

Output: 2.31

Approximate Numeric

Approximate Numeric category includes floating point and real values. They are mostly used in scientific calculations.

Interesting Facts!

• CHAR data type is faster than VARCHAR while retrieving data.

Summary:

• Every column in tables defines with its datatype during table creation.
• There are six main categories and one other miscellaneous category. Other miscellaneous have nine subcategories of data types available. sql dba training help you to learn more skills and techniques.