Quiz 2 - SQL Server - Datatypes Flashcards
exact numerics
- When data store is known precisely and no approximation is needed/allowed
- For example all integer values. Even values with decimals such as 10.23 or 1.23456
- These data types are useful for all integer values as well as values with decimals where exactness is useful.
- For example: data tracking money – the number of decimal places need tracking is small, but require exactness.
approximate numerics
- The numbers stored are made up of two parts: the mantissa and the exponent.
- The algorithm used to produce these two parts from a number is not precise.
- As a result, what is inserted would not be exactly what is stored and retrieved.
- These data types are useful for scientific, statistical and engineering purposes.
- These applications require much higher level of precision (number of digits stored) but are fine with the minor approximations in the storage.
date and time
For storing both data as well as time
character strings
- Series of character data made up of letters, digits and symbols.
- Each character is stored using 1-byte of space (ANSI character set which is 256 characters).
- Maximum length allowed is 8000 characters (8000 bytes), because more than that cannot be stored in single page.
unicode character strings
• Similar to character strings, but using a broader character set (Unicode Character Set).
• Each character is stored using 2-byte of space.
• Note: Unicode character set can potentially represent a maximum of 65,536 characters.
Maximum length allowed is 4000 characters (8000 bytes).
binary strings
• for storing binary data (1s and 0s)
SQL: BIGINT
- storage: 8 bytes
* values allowed: -2^63 to 2^63-1 (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)
SQL: INT
- storage: 4 bytes
* values allowed: -2^31 to 2^31-1 (-2,147,483,648 to 2,147,483,647)
SQL: SMALLINT
- storage: 2 bytes
* values allowed: -2^15 to 2^15-1 (-32,768 to 32,767)
SQL: TINYINT
- storage 1 byte
- values allowed: 0 to 2^8-1 (0 to 255)
- note: does not allow storing negative values
SQL: DECIMAL(p,s)
- fixed precision and scale numeric datatype
- When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1.
- p (precision) is the maximum total number of decimal digits that can be stored, including both to the left and to the right of the decimal point
- precision must be a value from 1-38
- default precision is 18
- s (scale) is the maximum number of decimal digits that can be stored to the right of the decimal point
- Scale must be a value from 0-p.
- Scale can be specified only if precision is specified.
- default scale is 0; therefore, 0 <= p.
SQL: NUMERIC
same as DECIMAL
SQL: MONEY
- storage: 8 bytes
- values allowed: -922,337,203,685,477.5808 to 922,337,203,685,477.5807
- note: stored values are accurate to a ten-thousandth of the monetary unit
SQL: SMALLMONEY
- storage: 4 bytes
- values allowed: - 214,748.3648 to 214,748.3647
- note: stored values are accurate to a ten-thousandth of the monetary unit
SQL: BIT
• integer data type that can take a value of 1, 0, or NULL
SQL: FLOAT
- approximate numeric
* values allowed: - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
SQL: REAL
- approximate numeric
* values allowed: - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38
SQL: DATETIME
- storage: 4 bytes
- values allowed: January 1, 1753, through December 31, 9999
- accuracy of 3.33 milliseconds
SQL: SMALLDATETIME
- storage: 2 bytes
- values allowed: January 1, 1900, through June 6, 2079
- accuracy of 1 minute
SQL: CHAR(n)
- fixed-length, non-Unicode character data with a length of n bytes
- n must be a value from 1 through 8,000
- storage size is n bytes (1 byte/character).
- default n=1
- padding stored at the end
- fixed-length is much faster to retrieve
SQL: VARCHAR(n|max)
- variable-length, non-Unicode character data
- n can be a value from 1 through 8,000
- for greater than 8000, the size is denoted using max which indicates that the maximum storage size is 2^31-1 bytes
- note: TEXT is recommended instead of VARCHAR(MAX)
- data entered can be 0 characters in length
SQL: TEXT
- variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31-1 (2,147,483,647) characters
- when the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes.
SQL: NCHAR(n)
- Unicode version of CHAR
- padding stored at the end
- fixed-length is much faster to retrieve
SQL: NVARCHAR(n|MAX)
- Unicode version of VARCHAR
* n can range from 1 through 4000.
SQL: NTEXT
- Unicode version of TEXT
- variable-length with a maximum length of 2^30 - 1 (1,073,741,823) characters
- storage size, in bytes, is 2X the number of characters entered
SQL: BINARY(n)
- fixed-length binary data with a length of n bytes
- n is a value from 1 through 8,000
- storage size is n bytes
SQL: VARBINARY(n|MAX)
- variable-length binary data
- n can be a value from 1 through 8,000
- max indicates that the maximum storage size is 2^31-1 bytes
- storage size is the actual length of the data entered + 2 bytes
- data entered can be 0 bytes in length
SQL: IMAGE
- variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
- equivalent to VARBINARY(max)
SQL: TIMESTAMP
- a data type that exposes automatically generated, unique binary numbers within a database
- generally used as a mechanism for version-stamping table rows
- storage size is 8 bytes
- an incrementing number and does not preserve a date or a time
SQL: UNIQUEIDENTIFIER
- 16-byte code to uniquely identify a row
- generated either randomly, or by supplying a string in which each character is a hexadecimal digit (0 to 9, or a through f)
SQL: TABLE
- special data type that can be used to store a result set for processing at a later time
- primarily used for temporary storage of a set of rows returned as the result set of a table-valued function
- note: TABLE is not used to define data type of a column, but for purpose described above.
SQL: XML
• other datatype
SQL: CURSOR
• other datatype
SQL: SQL_VARIANT
• other datatype
if there is no math, use a character datatype
GROUP BY works much more efficiently with a character string datatype
