To write effective queries in T-SQL, you should understand how SQL server stores different types of data. This is especially important if your queries not only retrieve data from tables, but also perform comparisons, manipulate data, and implement other operations. In this blog I want to show you built-in systems data types specifications, similarities and differences within categories of data types. SQL server defines a set of system data types for storing data in columns, holding values temporarily in variables, operating on data in expressions, and passing parameters to stored procedures.

Data types specify the type, length, precision and scale of data.

There are seven categories data types as below;

[table id=17/]

In SQL server, based on their storage characteristics, some data types are designated as belonging to the following groups;

  • Large value data types: varchar(max) and nvarchar(max)
  • Large object data types: text, ntext, image, varbinary(max) and xml

Exact Numeric

These data types store data with precision, either as:

Integers: whole numbers with varying degrees of capacity.

Decimal: decimal numbers with control over both the total number of digits stored and the       number of digits to the right of the decimal place.

[table id=18/]

[info]Decimal is the ISO standards compliant name for the data type, numeric is used for backward compatibility with earlier versions of SQL server. Numeric is functionally equivalent to decimal.[/info]

(P) is precision, the maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

(s) is scale, the number of decimal digits that will be stored to the right of the decimal point. Scale can be specified only if precision is specified. Default scale is 0; therefore, 0

Approximate Numeric

These data types allow inexact values to be stored, typically for use in scientific calculations.

[table id=19/]

(n) is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size.

[info](n) in float data type is optional and default value for (n) is 53.[/info]

The real data type is a synonym for a float data type with a mantissa value of 24(that is, float(24)).

Date and Time

These data types allow date and time data to be stored. To use date and time data in your queries, it is recommended that you use a neutral format as below for consistency and to avoid issues with language or nationality interpretation.

[table id=20/]

SQL server will implicitly convert the string literals to date and time values.

If you don’t provide all elements of date and time data types, they will show MID NIGHT (00:00:00.000) for missed time information and will show first date of their range as missed date.

[codesyntax lang=”tsql”]

Select Case('20171221' as datetime) as [Date and Time]
Select Case('12:22:35.123' as datetime) as [Date and Time]
Select Case('12:22' as datetime) as [Date and Time]
Select Case('20171221 12:00' as datetime) as [Date and Time]


If you want to retrieve data from date and time column in specific date, you should use WHERE clause with BETWEEN or use WHERE clause with CAST or CONVERT in your select statement.

[codesyntax lang=”tsql”]

Select index_advantage as [Index Advantage], Cast(last_user_seek as date) as [Last User Seek]From ['Missing indexes$'] Where Cast(last_user_seek as date) = '2017-11-07' 
Order by last_user_seek


[codesyntax lang=”tsql”]

Select index_advantage as [Index Advantage], last_user_seek as [Last User Seek]
From ['Missing indexes$'] Where '2017-11-07'


[codesyntax lang=”tsql”]

Select index_advantage as [Index Advantage], Cast(last_user_seek as date) as [Last User Seek]From ['Missing indexes$'] Where Cast(last_user_seek as date) Between '2017-11-07'and'2017-11-09'
Order by last_user_seek


Date and Time Functions

There are many functions to:

  • Get system date and time values: GETDATE, GETUTCDATE, SYSDATETIME
  • Get date and time parts: DATENAME, DATEPART
  • Get date and time values from their parts: DATETIME2FROMPARTS , DATEFROMPARTS
  • Get date and time difference: DATEDIFF, DATEDIFF_BIG
  • Modify date and time values: DATEADD, EOMONTH
  • Validate date and time values: ISDATE

[codesyntax lang=”tsql”]

Select GETDATE() as [Getdate]
Select DATENAME(MONTH,'2017-12-22 11:55:10.1234567 +08:00') as [Datename]
Select DATETIME2FROMPARTS(2017,12,22,12,35,45,0,0) as [Datetime2fromparts]
Select DATEDIFF(DAY,'20171212','20171222') as [Datediff]
Select DATEADD(DAY,5,'20171222') as [Dateadd]
Select ISDATE('20171222') as [Isdate]


To find more functions please visit

Character String

Working with character data in SQL server can be more complicated. This is because you need to consider multiple language, character sets, accented characters, sort rules and case sensitivity, and capacity and storage. Each of these factors might have an impact on which character data types you encounter when writing queries.

Character data types in SQL server are categorized by two characteristics, support for either fixed-width or variable-width data:-

  • Fixed-width data is always stored at a consistent size, regardless of the number of characters in the character data. Any unused space is filled with padding.
  • Variable-width data is stored at the size of the character data, plus a small overhead.
  • Support for either a single-byte character set or a multi-byte character set:
  • A single-byte character set supports up to 256 different characters, stored as one byte per character. By default, SQL Server uses the ASCII character set to interpret this data.
  • A multi-byte character set supports more than 65,000 different characters by storing each character as multiple bytes—typically two bytes per character, but sometimes more. SQL Server uses the UNICODE UCS-2 character set to interpret this data.

The four available character data types support all possible combinations of these characteristics:

[table id=21/]

Definitions for columns or variables take an optional value which defines the maximum length of the character data to be stored. You will almost always need to specify a value for the string length; if the maximum length value is not supplied, the default value is one character.

The varchar and nvarchar data types support the storage of very long strings of character data by using max for this value. Use of varchar(max) and nvarchar(max) replaces the use of the deprecated text and ntext types.

[table id=22/]

[info]All character data is delimited with single quotation marks.[/info]

Single-byte character data is indicated with single quotation marks alone—for example ‘SQL Server’. Multi-byte character data is indicated by single quotation marks with the prefix N (for National)— for example N’SQL Server’. The N prefix is always required, even when inserting the data into a column or variable with a multi-byte type.


In addition to character byte count and length, SQL Server character data types are assigned a collation.

A collation is a collection of properties which determine several aspects of character data, including:  Language or locale, from which is derived:

  • Character set
  • Sort order
  • Case sensitivity
  • Accent sensitivity

[info]A default collation is configured during the installation of SQL Server, but can be overridden on a per-database or per-column basis. As you will see, you might also override the current collation for some character data by explicitly setting a different collation in your query.[/info]

When querying, it is important to be aware of the collation settings for your character data—for example, whether it is case-sensitive.

The following query will return different results, depending on whether the column being tested in the WHERE clause is case-sensitive or not. If the column is case-sensitive, this query will return results. Note that the case of the search term matches the case of the data as stored in the database.

Case-Sensitivity Example (1)

[codesyntax lang=”tsql”]

SELECT empid, lastname

FROM HR.employees

WHERE lastname = N’Funk’;


Amending the search term, so that the case no longer matches the data as stored in the database, would result in no rows being returned:

Case-Sensitivity Example (2)

[codesyntax lang=”tsql”]

SELECT empid, lastname

FROM HR.employees

WHERE lastname = N’funk’;


The COLLATE clause can be used to override the collation of a column and force a different collation to be applied when the query is run. This example forces a case-sensitive and accent-sensitive comparison using the Latin1_General sort rules and character table by adding a COLLATE clause to the WHERE clause:

Using COLLATE in the WHERE Clause

[codesyntax lang=”tsql”]

SELECT empid, lastname

FROM HR.employees

WHERE lastname COLLATE Latin1_General_CS_AS = N’Funk’;


[info]Note that database-level collation settings apply to database object names (such as tables and views) as well as to character data.  For example, in a database with a case-sensitive default collation, the table names “HR.Employees” and “HR.employees” would refer to two different objects. In a database with a case-insensitive collation, the table names “HR.Employees” and “HR.employees” would refer to the same object. [/info]

Character String Functions


This function takes at least two (or more) data values as arguments and returns a string value with the input values concatenated together. If any of the input data values is not of a character data type, it will be implicitly converted to a character data type. Any NULL values will be converted to an empty string.

[codesyntax lang=”tsql”]

Select Concat ( ‘Fard’ , ‘-‘ , null , ‘Solutions’ ) as [Concated String]

Select Concat ( ‘Fard’ , ‘-‘ , null + ‘Solutions’ ) as [Concated String]


It was same as below example before SQL server 2012

[codesyntax lang=”tsql”]

Select ‘Fard’ + ‘-‘ + ‘Solutions’  as [Concated String]

Select  ‘Fard’ + ‘-‘ + null + ‘Solutions’  as [Concated String]



Allows you to format an input value to a character string based on a .NET format string, with an optional culture parameter.

[codesyntax lang=”tsql”]

Declare @m money = 120.595

Select @m as [Unformatted Values],

Format ( @m , ‘c’ , ‘zh-cn’ ) as [zh_cn Currency],

Format ( @m , ‘c’ , ‘en-us’ ) as [en_us Currency],

Format ( @m , ‘c’ , ‘de-de’ ) as [de_de Currency]


These are some other functions for characters string:

[codesyntax lang=”tsql”]

Select SUBSTRING(‘Saeed Jabarpourfard’,7,9) as [Substring]
Select LEFT(‘Saeed Jabarpourfard’,5) as [Left]
Select RIGHT(‘Saeed Jabarpourfard’,13) as [Right]
Select LEN(‘Saeed Jabarpourfard’) as [Len]
Select DATALENGTH(‘   Saeed Jabarpourfard     ‘) as [Datalength]
Select CHARINDEX(‘a’,’Saeed Jabarpourfard’,1) as [Charindex]
Select REPLACE(‘Saeed Jabarpourfard’,’Jabarpour’,’J.’) as [Replace]
Select UPPER(‘saeed jabarpourfard’) as [Upper]


Binary String

These data types allow binary data to be stored, such as byte streams or hashes, to support custom applications.

[table id=23/]

[codesyntax lang=”tsql”]

Select Cast(‘Saeed’ as binary) as [Binary String]


The image data type is also a binary string type but is marked for removal in a future version of SQL server. Varbinary(max) should be used instead.

I will discuss about other data types category in other blog. Hope this blog post was informative for you.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles


Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan