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;
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
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.
[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
These data types allow inexact values to be stored, typically for use in scientific calculations.
(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.
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.
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.
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
Select index_advantage as [Index Advantage], last_user_seek as [Last User Seek] From ['Missing indexes$'] Where '2017-11-07'
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
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 https://docs.microsoft.com
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:
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.
[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)
SELECT empid, lastname
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)
SELECT empid, lastname
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
SELECT empid, lastname
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.
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
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.
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:
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]
Select LOWER(‘SAEED JABARPOURFARD’) as [Lower]
These data types allow binary data to be stored, such as byte streams or hashes, to support custom applications.
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.