Data Types in SQL Server 2012

Sysname

An alias data type that is currently mapped to nvarchar(128)

Binary Type

Stores binary objects

Sql Variant Data Type

Stores data of an unknown data type

Int

Stores four bytes of data and its range is -2,147,483,648 to 2,147,483,647

XML Data Type

Stores semi-structured textual data

Datetimeoffset

A combination of datetime2 and timezoneoffset data types; capable of storing and retrieving date and timezone values

Datetime2

A combination of the date data type and the time data type

LEN Function

Allows you to find the number of characters

Null

Allows you to have NO VALUE assigned to a field; used while inserting the record; used as a placeholder for unknown or inapplicable values; not equal to zero

PARSE Function

Allows you to specify culture while making the data type conversion

CONVERT

Allows you to specify the target data type and the source element

Varchar

Allows you to store variable length strings

Decimal

An ANSI compatible exact fractional number

Timestamp Variables

Automatically populated by SQL Server with the time that a row is created or modified

System Data Types

Built-in data types in SQL Server

Alias Data Types

Provide a mechanism for applying a name to a data type to make it more descriptive of the types of values being held in the object

Approximate Numeric Data Types

Provide less precision and are less commonly used

Unicode

Simplifies software localization; it allows you to have local languages or multi-language processing

Set Language

Specifies the language environment for the session

CAST Function

Fully converts data from one type to another

Table Data Type

Holds an entire rowset returned from a query

Cursor Data Type

Holds the reference to the cursor while constructing cursor-based code

Money and Smallmoney Data Types

Used to store currency values

Char Type

Used to store strings that have more or less the same length

Numeric

The same as decimal type

SQL Server 2012

A platform for developing business applications that are data centric; designed as a set of components

Collations

A set of rules that determine how you sort and compare the data; meant for non-Unicode characters

Global Unique Identifiers (GUID)

A unique binary number; generated in such a way that no other computer in The world will generate a duplicate of it

Datetime

A very old data type; has a date range of 1753-01-01 to 9999-12-31 and a time accuracy of 3.33 milliseconds

Time Data Type

Aligned to the SQL standard form of hh:mm:ss with optional decimal places up to hh:mm:ss.nnnnnnn

Date Data Type

Complies with the ANSI standard definition for the Gregorian calendar

User Defined Data Types

Data types you create

Uniqueidentifier Data Type

Does not automatically generate new IDs for inserted rows that have the identity property

Timezoneoffset

Extends beyond the date; it has a range of -14 hours to +14 hours

Bit Data Type

Known as the Boolean data type; the value is stored in a bit

Hierarchical Data Type

Manages code and represents the node of a tree

Aliases

Relieve you from hard-coding the connection details; can point to physical attributes of protocols and ports

TRUNCATE TABLE

Removes all rows from a table without logging the individual row deletions

Dv

Represents a data entity

DATALENGTH

Returns number of bytes

GETDATE Function

Returns the current database system timestamp as a datetime value without the database time zone offset

Smalldatetime Variables

Store 4-byte time and date values ranging from January 1, 1900, to June 6, 2079, with an accuracy of one minute

Float variables

Store four-byte or eight-byte floating-point numbers, the scale value for this is one to 53

Geometry and Geography Data Types

Store spatial data elements

Characters

Stored as strings

Bigint

Stored in eight bytes and stores very large integer values

Smallint

Stored in two bytes and ranges from -32768 to 32767

Tinyint

Stores a single byte of data from zero to 255; it cannot store negative values

Rowversion

Synonym for timestamp data type; changes automatically to another large number whenever the row containing this column is updated

Data Type

Tells you the type of data a field can take

Table

The basic unit to store user or system data

Column Row Intersection

The field in a record

Exact types

Used every day; used in business related applications

Numeric Data Types

Used for anything to do with numbers, whether they are exact or approximate

TRY CONVERT

Works like CONVERT function

TRY PARSE

Works like the PARSE function