SQL Server Data Types Part 1

Choosing the sql data type tinyint instead of int for a "ProductType" column with values ranging from 1 to 10 will save three bytes per record. With 100,000 records you will save 300,000 bytes. That's not much in terms of disc space ("storage is cheap, etc") but you'll probably have indexes containing that column and if that index takes less memory the database engine will process that index much more efficient in every "join" and "where" etc.

So, queries will perform faster, release locks earlier (if any) and use less system resources (memory and CPU). This will make the whole server perform better as there will be more resources available for other things.

Once learned the sql data types available and spending a few extra minutes when designing your schema will result in faster query execution and an overall better performing database.

The columns named 8, 9, 10 and 11 indicates SQL Server version data type support where

8 = SQL Server 2000

9 = SQL Server 2005

10 = SQL Server 2008

11 = SQL Server 2012

A note on precision

Space taken by value entries of the types specifying precision (Float, Decimal, DateTime2 etc) is always the same. It's the column definition that defines how much space each entry takes, not the size of the value itself. So a Decimal(25,5) value of 999.999 takes 13 bytes, not 5 bytes. Even a NULL value will take 13 bytes. The column is fixed-length. Even though this might seem bad there's a performance gain CPU-wise when working with fixed-length data (also remember that index trees contains these values and fixed-length storage requirements).

Another consideration is when summing a precision based value the resulting summed values datatype will be the same (if not casted) as the column definition and an arithmetic overflow might occur. If you know your values will, for instance, range from 0 to 999,99 there's no point from a space perspective to not define it as Decimal(9,2) anyways (the highest 5 byte definition). That way your sum result have more space available and you can perhaps avoid some casting. From a constraining perspective a Decimal(5,2) might be more appropriate, but maybe constraints requirements shouldn't be mixed up with data type decisions (well this is another discussion outside the scope of this article).

Datatype	Min	Max	Storage	8	9	10	11	Type	Notes
Bigint	-2^63	2^63-1	8 bytes					Exact
Int	-2,147,483,648	2,147,483,647	4 bytes					Exact
Smallint	-32,768	32,767	2 bytes					Exact
Tinyint	0	255	1 bytes					Exact
Bit	0	1	1 to 8 bit columns in the same table requires a total of 1 byte, 9 to 16 bits = 2 bytes, etc...					Exact
Decimal	-10^38+1	10^38–1	Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes					Exact	The Decimal and the Numeric data type is exactly the same. Precision is the total number of digits. Scale is the number of decimals. For both the minimum is 1 and the maximum is 38.
Numeric	same as Decimal	same as Decimal	same as Decimal					Exact
Money	-2^63 / 10000	2^63-1 / 10000	8 bytes					Exact
Smallmoney	-214,748.36	214,748.36	4 bytes					Exact
Float	-1.79E + 308	1.79E + 308	4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53					Approx	Precision is specified from 1 to 53.
Real	-3.40E + 38	3.40E + 38	4 bytes					Approx	Precision is fixed to 7.
Datetime	1753-01-01 00:00:00.000	9999-12-31 23:59:59.997	8 bytes					Datetime	If you are running SQL Server 2008 or later and need milliseconds precision, use datetime2(3) instead to save 1 byte.
Smalldatetime	1900-01-01 00:00	2079-06-06 23:59						Datetime
Date	0001-01-01	9999-12-31	3 bytes	no	no			Datetime
Time	00:00:00.0000000	23:59:59.9999999		no	no			Datetime	Specifying the precision is possible. TIME(3) will have milliseconds precision. TIME(7) is the highest and the default precision. Casting values to a lower precision will round the value.
Datetime2	0001-01-01 00:00:00.0000000	9999-12-31 23:59:59.9999999	Presicion 1-2 = 6 bytes precision 3-4 = 7 bytes precision 5-7 = 8 bytes	no	no			Datetime	Combines the date datatype and the time datatype into one. The precision logic is the same as for the time datatype.
Datetimeoffset	0001-01-01 00:00:00.0000000 -14:00	9999-12-31 23:59:59.9999999 +14:00	Presicion 1-2 = 8 bytes precision 3-4 = 9 bytes precision 5-7 = 10 bytes	no	no			Datetime	Is a datetime2 datatype with the UTC offset appended.
Char	0 chars	8000 chars	Defined width					String	Fixed width
Varchar	0 chars	8000 chars	2 bytes + number of chars					String	Variable width
Varchar(max)	0 chars	2^31 chars	2 bytes + number of chars	no				String	Variable width
Text	0 chars	2,147,483,647 chars	4 bytes + number of chars					String	Variable width
Nchar	0 chars	4000 chars	Defined width x 2					Unicode	Fixed width
Nvarchar	0 chars	4000 chars						Unicode	Variable width
Nvarchar(max)	0 chars	2^30 chars		no				Unicode	Variable width
Ntext	0 chars	1,073,741,823 chars						Unicode	Variable width
Binary	0 bytes	8000 bytes						Binary	Fixed width
Varbinary	0 bytes	8000 bytes						Binary	Variable width
Varbinary(max)	0 bytes	2^31 bytes		no				Binary	Variable width
Image	0 bytes	2,147,483,647 bytes						Binary	Variable width. Prefer to use the varbinary(max) type as the image type will be removed in future versions.
Sql_variant								Other	Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
Timestamp								Other	Stores a database-wide unique number that gets updated every time a row gets updated.
Uniqueidentifier								Other	Stores a globally unique identifier (GUID).
Xml				no				Other	Stores XML data. You can store xml instances in a column or a variable.
Cursor								Other	A reference to a cursor.
Table								Other	Stores a result set for later processing.

Oracle Data Types Info

Datatypes	Limit	Comments
BFILE	maximum size: 4 GB maximum size of file name: 255 characters maximum size of directory name: 30 characters maximum number of open BFILEs: see comments	The maximum number of BFILEs is limited by the value of SESSION_MAX_OPEN_FILES, which is itself limited by the maximum number of open files the operating system will allow.
BLOB	4 GB maximum	The number of LOB columns per table is limited only by the maximum number of columns per table (i.e., 1000)
CHAR	2000 bytes maximum
CHAR VARYING	4000 bytes
CLOB	4 GB maximum	The number of LOB columns per table is limited only by the maximum number of columns per table (i.e., 1000)
Literals (characters or  numbers in SQL or PL/SQL)	4000 characters maximum
LONG	231-1 bytes (2 GB) maximum	Only one LONG column allowed per table
NCHAR	2000 bytes
NCHAR VARYING	4000 bytes
NCLOB	4 GB maximum	The number of LOB columns per table is limited only by the maximum number of columns per table (i.e., 1000)
NUMBER	999...(38 9's) x10125maximum value	Can be represented to full 38-digit precision (the mantissa).
	-999...(38 9's) x10125minimum value	Can be represented to full 38-digit precision (the mantissa).
Precision	38 significant digits
RAW	2000 bytes maximum
VARCHAR	4000 bytes maximum
VARCHAR2	4000 bytes maximum
Item	Type of Limit	Limit Value
Database Block Size	minimum	2048 bytes; must be a multiple of O/S physical block size
	maximum	O/S-dependent; never more than 32 KB
Database Blocks	minimum in initial extent of a segment	2 blocks
	maximum per datafile	platform dependent; typically 222 blocks
Controlfiles	number of controlfiles	1 minimum: 2 or more (on separate devices) strongly recommended
	size of a controlfile	dependent on O/S and database creation options; maximum of 20,000 x (database block size)
Database files	maximum per tablespace	O/S dependent, usually 1022
	maximum per database	65533; may be less on some operating systems; limited also by size of database blocks, and by the DB_FILES init parameter for a particular instance
Database file size	maximum	O/S dependent, limited by maximum O/S file size;typically 222 or 4M blocks
MAXEXTENTS	default value	derived from tablespace default storage or DB_BLOCK_SIZE
	maximum	unlimited
Redo Log Files	maximum number of logfiles	LOG_FILES initialization parameter, or MAXLOGFILES in CREATE DATABASE; controlfile can be resized to allow more entries; ultimately an O/S limit
	maximum number of logfiles per group	unlimited
Redo Log File Size	minimum size	50K bytes
	maximum size	O/S limit, typically 2GB
Tablespaces	maximum number per database	64K Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file.
Item	Type	Limit
GROUP BY clause	maximum length	The group-by expression and all of the non-distinct aggregates (e.g., sum, avg) need to fit within a single database block.
Indexes	maximum per table	unlimited
	total size of indexed column	40% of the database block size minus some overhead.
Columns	table	1000 columns maximum
	indexed (or clustered index)	32 columns maximum
	bitmapped index	30 columns maximum
Constraints	maximum per column	unlimited
Nested Queries	maximum number	255
Partitions	maximum length of linear partitioning key	4KB - overhead
	maximum number of columns in partition key	16 columns
	maximum number of partitions allowed per table or index	64K-1 partitions
Rollback Segments	maximum number per database	no limit; limited within a session by MAX_ROLLBACK_SEGMENTS init parameter
Rows	maximum number per table	no limit
SQL Statement Length	maximum length of statements	64K maximum; particular tools may impose lower limits
Stored Packages	maximum size	PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. Consult your PL/SQL or Developer/2000 documentation for details. The limits typically range from 2000-3000 lines of code.
Trigger Cascade Limit	maximum value	O/S dependent, typically 32
Users and Roles	maximum	2,147,483,638
Tables	maximum per clustered table	32 tables
	maximum per database	unlimited
Item	Type	Limit
Instances per database	maximum number of OPS instances per database	O/S dependent
Locks	row-level	unlimited
	Distributed Lock Manager	O/S dependent
SGA size	maximum value	O/S dependent, typically 2-4 GB for 32-bit O/S, > 4 GB for 64 bit O/S
Job Queue Processes	maximum per instance	36
I/O Slave Processes	maximum per background process (DBWR, LGWR, etc.)	15
	maximum per Backup session	15
Sessions	maximum per instance	32K, limited by PROCESSES and SESSIONS init parameters
LCK Processes	maximum per instance	10
MTS Servers	maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.
Dispatchers	maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.
Parallel Query Slaves	maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.
Backup Sessions	maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS init parameters, for instance.