SQL Server 2012 Data Types
In SQL Server 2012, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that stipulates the type of data that the object mayembrace: integer data, characters data, money data, date and time data, binary strings, and so on. SQL Server 2012 offers a set of data types that express all the types of data that may be used inSQL Server 2012. You may also express your own datatypes in TransactSQL or the .NET-Framework. User-defined types obtain their structures from the methods and operators of a class that you create by using one of the programming languages support by the .NET-Framework. When there are two expressions that have different kind of data types, collations, precision, scale, the appearances of result are determined by the:
-
The data type of the outcome is determined by relating the rules of data type precedence to the data types of the input expressions.
-
The collation of the outcome is determined by the rules of collation precedence when the result data type is char, varchar, text, nchar, nvarchar, or ntext.
-
The precision, scale, and length of the outcome depend on precision and length of the input expressions.
- Uppercase and lowercase strings such as a, b, and C.
- Special characters like as the "at" sign (@),(&), and (!).
- Numbers such as 1, 2, and 3.
Integer Data Type
Integers are wholesome numbers which do not contain decimals or fractions. MS SQL Server 2012 has below integer data types:
Bigint
Has a length of 8 bytes and stores numbers from –2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).
integer or int
Has a length of 4 bytes, and stores numbers from -2,147,483,648 through 2,147,483,647.
Smallint
Has a length of 2 bytes, and stores numbers from -32,768 through 32,767.
Tinyint
Has a length of 1 byte, and stores numbers from 0 through 255.
Integer objects and languages can be used with any calculated operations. Any fractions produced by these processes are truncated, not rounded. For example, SELECT 7/3 returns a value of 2, not the value 3, which would return if the fractional result were rounded.The integer data types are the single ones that can be used with the IDENTITY property, which is anroutinely incrementing number. The IDENTITY property is typically used to automatically generate UID numbers or primary keys. With Integer data, no need toto be enclosed in single quotes like character data type.
Binary Data Type
The binary data types store strings of bits and although character data is understood based on the SQL Server 2012 code, binary data is simply a stream of bits. This typeof data can store a max of 8000 bytes. Varbinary on the other hand can store a max of 2^31 bytes. Binary factors have a leading 0x which is followed by the hex illustration of the bit pattern. As an example, 0x2A stipulates the hexvalue of 2A, which is equal to a decimal value of 42 or a one-byte bit pattern of 00101010.Use binary data type when storing hex values such SSN, social security numbers, SIDs and GUIDs.
Below example stores a SID and hexadecimal verbatim:
USE AdventureWorks2012;
GO
CREATE TABLE MyCcustomerTable
(
user_loginvarbinary(85) DEFAULT SUSER_SID()
,data_valuevarbinary(1)
);
GOINSERT MyCustomerTable (data_value)
VALUES (0x4F);
GOAtt
Char and Varchar Data Type
These-- char and varchar data type store data poised of the following:
The char or varchar data type can be a single character, or it can be a string with a max of 8000 characters for char data. It also allow up to 2^31 characters for varchar data. varchar data can be of a stated maximum length of characters, as an example, varchar(6) indicates that this data type can store a maximum of six characters; or it can be of the form varchar(max), this increases the max number of chars that can be kept by this data type to 2^31.
Each char and varchar data value has a collection. This Collationsdescribe attributes such as the bit patterns used to characterize each character, comparison rules, and sensitivity to case or accenting. All database possess a default collation which is set during the installation . When a column is defined, or a constant stated, they are allocated the default collation of the database if not you assign a specific collation byrunning the COLLATE clause. When two char or varchar values having dissimilar collations are joint or compared, collation precedence rules define which collation is used for the operation.
These Character constants must be in single quotes (') or double quotes ("). Hem in a character constant in single quotation marks is highly recommended. Enclosing a character constant within in double quotes is occasionally not allowed when the QUOTED IDENTIFIER option is set to ON.
Below example sets a character variable to a value:
DECLARE @MyCharVarCHAR(25)
SET @MyCharVar = 'Ricardo Adocicados'
When using single quotation marks to delimit a character constant that contains an embedded single quotation mark, use two single quotation marks to represent the embedded single quotation mark. For example:
SET @MyCharVar = 'O''Leary'
If the data to be stored is longer than the number of characters allowed, the data is truncated. For example, if a column is defined as char(10) and the value "This is a really long character string" is stored into the column, SQL Server 2012 truncates the character string to "This is a ".
Data and Time Data Type
String literal formats affect the performance of data in applications to users but not the fundamental integer storage format in SQL Server 2012. SQL Server 2012 might take a date value in a string literal format, input by an application or user for storage or to a date function, as different dates. The interpretation be influenced by on the combination of string literal format, data type, and runtime SET DATEFORMAT, and default language option and SET LANGUAGE settings.Some of the string literal formats are not all affected by these settings. Consider using a format that doesn’t always depend on these settings, unless you are sure that the settings are correct for the format. Transact-SQL that uses string literal formats, dependent on system settings.The ydm date format is not allowed for the date, datetime2 and datetimeoffset types. A run time error will be raised since this is not supported.
The following lists different date and time string formats. MS recommend that you use date-time formats that are not DATEFORMAT reliant on and are multilanguage. The ISO 8601 formats, '1998-02-23T14:23:05' and '1998-02-23T14:23:05-08:00' are the only formats that are an international standard. They are not DATEFORMAT or default login language dependent and are multilanguage.
Date-time part |
Format type |
Format example |
Can combine with other formats |
DATEFORMAT dependent |
Multilanguage |
Date |
Un-separated ISO 8601 |
'19980223' |
Yes |
No |
Yes |
Date |
Numeric |
'02/23/1998' |
Yes |
Yes |
No (DATEFORMAT) |
Date |
ISO 8601 Numeric |
'1998-02-23' |
Yes |
No |
No |
Date |
Alphabetical |
'23 February 1998' |
Yes |
No |
No (month or short month) |
Date |
ODBC date |
{d '1998-02-23'} |
No |
No |
Yes |
Time |
ISO 8601 Time |
'14:23:05' '10:00:00.123456' |
Yes |
No |
Yes |
Time |
ODBC time |
{t '14:23:05'} |
No |
No |
Yes |
Date-time |
ODBC date-time |
{ts '1998-02-23 14:23:05'} |
No |
No |
Yes |
Date-time |
ISO 8601 |
'1998-02-23T14:23:05' '1998-02-23T14:23:05 -08:00' |
No |
No |
Yes date, datetime2, datetimeoffset. |
Date-time |
ANSI SQL Standard |
'1998-02-23 14:23:05' '1998-02-23 14:23:05 -08:00' |
No |
No (datetime2, datetimeoffset) Yes (datetime) |
Yes date, datetime2, datetimeoffset. |
Date-time |
Combination of date and time |
'19980223 14:23:05' '02/23/1998 2:23:05 PM' '1998-02-23 10:00:00.123' '23 Feb 1998 14:23:05' |
No |
Yes (date part) |
No |
TimeZone |
TimeZone Format |
'+12:00' '01:00' '-08:00' 'Z' |
Yes |
No |
Yes |
Example of SET LANGUAGE and SET DATEFORMAT settings.
DECLARE @Today date = '1/1/2012';
DECLARE @MyTime time = '1:30:00';
DECLARE @MyDatetimeoffsetdatetimeoffset = '1/1/20121:30:00 -5:00';
SET LANGUAGE Italian
-- Returns: Changed language setting to Italiano.
SELECT DATENAME(month, @Today) AS 'Month Name';
-- Returns: dicembre
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
-- Returns: dic 1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
-- Returns: 12
SET LANGUAGE us_english;
-- Returns: Changed language setting to us_english.
SELECT DATENAME(month, @Today) AS 'Month Name';
-- Returns: December
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
-- Returns: Dec 1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
-- Returns: 12
GO
-- Set date format to month, day, year.
SET DATEFORMAT mdy;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
-- Returns: 1998-12-31
GO
-- Set date format to year, day, month.
SET DATEFORMAT ydm;
GO
DECLARE @datevardatetimeoffset = '1998/31/12 1:30:00 -05:00';
SELECT @datevar AS DateVar;
-- Returns: Msg 241, Conversion failed when converting
-- date and/or time from character string.
GO
-- Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
-- Returns: 1998-12-31
GO
Currency Data Type
Currency or monetary data type does not need to be enclosed in single quotes. It is vital to remember that while you may specify currency values lead by a currency symbol, SQL Server 2012 does not store any currency info associated with the symbol, it only stores the numeric value. As an example, to assign a value of 900 Dollars to a variable, you can do the following:
DECLARE @dollars AS money
SET @dollars = $900
SELECT @dollars
The value returned is 100.0000, without a currency symbol.
If an object is defined as currency, it can contain a maximum of 19 digits, 4 of which can be to the right of the decimal. The object uses 8 bytes to store the data. The money data type therefore has a precision of 19, a scale of 4, and a length of 8. money and smallmoney are limited to four decimal points. Use the decimal data type if more decimal points are required.
Use a period to distinct partial monetary units, like cents, from whole monetary units. Commafilters are not allowed in money or smallmoney constants, though the display format of these data types embraces comma separators. You can stipulate the comma separators only in character strings explicitly cast to money or smallmoney. For example:
USE sqltestdb;
GO
CREATE TABLE TestMoney (cola INT PRIMARY KEY, colb MONEY);
GO
SET NOCOUNT ON;
GO
-- The following three INSERT statements work.
INSERT INTO TestMoney VALUES (1, $123.45);
GO
INSERT INTO TestMoney VALUES (2, $123123.45);
GO
INSERT INTO TestMoney VALUES (3, CAST('$344,323.45' AS MONEY) );
GO
-- This INSERT statement gets an error because of the comma
-- separator in the money string.
INSERT INTO TestMoney VALUES (3, $455,123.45);
GO
SET NOCOUNT OFF;
GO
SELECT * FROM TestMoney;
GO
Data Conversions
