Analysis Datatypes Mapping to Microsoft SQL Server Datatypes

Here's a mapping from the analysis datatypes to Microsoft SQL Server datatypes.

Thanks to Andrew Novick of Novick Software, the Microsoft SQL Server expert who provided this mapping.

Analysis Datatypes Mapping to Microsoft SQL Server Datatypes

Analysis Datatype Name	Corresponding SQL Server Datatype Name	Note
indicator	bit	values are 0 or 1, 1 meaning true
code	nvarchar or varchar	to enforce domain constraints, either place a CHECK constraint on the column (if the number of valid codes is small), or place all the codes in a separate table and use a Foreign Key constraint
datetime	datetime or datetime2(n) or datetimeoffset(n)	datetime serves most purposes but is restricted to a precision of 3 milliseconds; datetime2 was introduced in SQL Server 2008 and adds a variable amount of precision, (n), up to 7 digits in the fraction of a second, giving a resolution of 100 nanoseconds; datetimeoffset(n) has both variable precision and a timezone offset from Greenwich Mean Time (GMT)
date	date or smalldatetime	date was introduced in SQL Server 2008; in earlier versions use smalldatetime with a CHECK constraint.
time	time	time was introduced in SQL Server 2008; in earlier versions use datetime with a CHECK constraint limiting the datetime to the start and end of 1/1/1900
money	money, smallmoney, or vardecimal for the numeric portion; varchar for the units portion, if needed	money and smallmoney carry 4 digits of precision to allow use with all world currencies; smallmoney is restricted to plus or minus 214,748; vardecimal may be substituted, in Enterprise Edition of 2005 SP2 and above, to allow compression when storage space is a concern; the currency code portion, if needed, can be represented by the ISO 4217 currency code list, which includes both three character alphabetic and three digit numeric currency codes; if automatic currency conversion functions are desired, a SQLCLR type may be created
amount	decimal, vardecimal, float or real; varchar for the units, if needed.	decimal allows specification of the number of digits on both sides of the decimal point with up to 38 total digits; vardecimal may be substituted, in Enterprise Editon of 2005 SP2 and above, to allow compression when storage space is a concern;in Enterprise Editon of 2005 SP2 and above, see ratio below for the range of values for float and real;in Enterprise Editon of 2005 SP2 and above, units may be limited to an appropriate list using a CHECK constraint on the column, or if the number of possible units is large or might change, place the codes in a separate table and use a Foreign Key constraint; the second column for storing the units may be unnecessary if all values can be converted to identical units; a useful naming convention for this situation is to append the units to the name of the column; for example, _km for kilometers.
quantity	tinyint or smallint or int or bigint	the choice depends on the range of values needed to represent the quantity; tinyint can represent a range between 0 and 255 and utilizes 8 bits; smallint can represent a range between -32,768 and 32,767 and utilizes 16 bits; int can represent a range between -2,147,483,648 and 2,147,483,647 and utilizes 32 bits; bigint can represent a range between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 and utilizes 64 bits
sequence number	int or bigint (or tinyint or smallint)	the most typical choices are int or bigint, however decimal is also an option; consider adding the identity property to the column, if appropriate
address	nvarchar for address lines; varchar(9) for the US zip codes or postal code	typically requires multiple columns, one for each portion of the address; state/province and country may be checked against the appropriate validation tables
phone number	varchar(15) for the phone number; varchar(6) for the extension	ISO E.164 specifies that phone numbers are a maximum of 15 characters; the length specifications assume the application removes all spaces and punctuation; extensions may be as many as 6 but are typically fewer digits
email address	varchar(310)	e-mail addresses are defined in IETF RFC 5322 as ASCII strings that may have as many as 64 characters before the @ sign and a 255 character domain name after the @; a CHECK constraint can be used to validate e-mail addresses; these can be written in either T-SQL or SQLCLR, SQLCLR being more efficient; see SQL Server CLR function to improve performance of validating email addresses for a comparison.
internet address	nvarchar(2083)	URLs are defined by RFC 1738; there is no length restriction on URLs in the specification or a specific restriction to ASCII; however, 2083 is the practical limit of a URL accepted by the Internet Explorer browser.
identifier	varchar or nvarchar or varbinary	varchar (ASCII) or nvarchar (UNICODE) strings are usable for most identifiers; when encryption is required, such as with a credit card number, column level encryption may be used in SQL Server 2005 and above, and varbinary would be the appropriate datatype
name-person	nvarchar	typically utilizes one column for each portion of a person's name (e.g. prefix, first, middle, last, suffix)
name-organization	nvarchar
name-other	nvarchar
specification text	nvarchar	CHECK constraints may be used to enforce the format.
text	varchar or nvarchar (or text or ntext)	varchar(max) and nvarchar(max) are recommended; while the text and ntext datatypes are in all current versions of SQL Server, they have been deprecated and may not be included in future versions
encoded data	binary or varbinary or varbinary(max) (or image)	while the image type is in all current versions of SQL Server, it has been deprecated and may not be included in future versions; as of SQL Server 2005 the image datatype has been replaced by varbinary(max); as of SQL Server 2008, large binary objects can be moved from database files into the operating system by adding the FILESTREAM attribute.
ratio	real or float for the numeric portion; a pair of varchar codes for the two units, if needed	the amount of precision required will determine the choice: - real can include up to 7 decimal digits, utilizes 32 bits, and allows for representing numbers from negative 3.40E + 38 to -1.18E - 38 to 1.18E - 38 to 3.40E + 38 - float can include up to 15 decimal digits, utilizes 64 bits, and allows for representing numbers from negative 3.40E + 38 to 1.18E - 38 to 3.40E + 38; numerator units and denominator units can be varchar and should have CHECK constraints to limit the possible values, or if the number of possible units is large or might change, put the units into a table and use a Foreign Key constraint; the columns for storing the numerator and denominator units may be unnecessary if all values can be converted to identical units; a useful naming convention for this situation is to append the ratio of the units to the name of the column; for example, _kmph for kilometers per hour.