Data Types (Work with Data 25-30% of Exam)
Bit data type
An exact numeric data type whose value can be 1, 0, or NULL. Used to store Boolean values or flags. True and False can be converted to bit values (1 is True, 0 is False)
Converting Float/Real to other Data Types
Float values are truncated when they are converted to any integer type. Use STR (string) function to covert to character data. (Example)
How Variable Binary Data Types are Stored
For varbinary and varbinary(max), The storage size is the actual length of the data entered + 2 bytes.
What do Binary String Data Types Store
Allow a developer to store binary information, such as serialized files, images, bytestreams, and other specialized data. For varbinary and varbinary(max), The storage size is the actual length of the data entered + 2 bytes.
Uniqueidentifier Data Type
Also referred to as a GUID and is 16 bytes. Automatically generates value that is unique across multiple systems. Must be generated either by converting from a string (reducing the guarantee of uniqueness), using the NEWID() system function, or using the NEWSEQUENTIALID() function. (provide Examples)
Cast Function
Can be used in SELECT and WHERE clauses. ANSI standard. (Provide example). Used when you need to explicitly convert a data type.
Convert Function
Can be used in SELECT and WHERE clauses. Has same purpose as CAST but provides an optional parameter to specify the standard (such as ISO).
CONCAT Function
Can be used to combine character data from multiple columns into a single string in a new column. Converts Null values to empty strings. (+ operator does not convert Null values to empty strings) (Example)
vachar(max)
Can be used to store 2 gigabytes of character. (used as alternative to nvarchar). Stores 1 byte per character. Replaced "Text" data type.
Negative effects of using NEWSEQUENTIALID() and NewID() functions
Can cause index fragmentation on larger systems. This causes data retrieval to be less efficient.
Numeric (or decimal) Data Type
Can specify the total number of digits to be stored (precision) and the number of digits to the right of the decimal (scale). Stored 5 through 17 bytes
Basic List of data type Precedence
Character strings have lowest precedence, followed by Other data types, then Exact Numerics, Approximate Numerics, Datetime data types, XML, then user defined data types.
Can Collation Settings be controlled?
Collation settings can be controlled in WHERE clause of a query. (Example)
Data Type Categories
Exact Numerics (Unicode character strings), Approximate Numerics (binary strings), Date and Time (Other Data types), Character Strings.
Get Current date and time
Getdate() or Current_Timestamp
Money and Small Money Data Types
Holds monetary values with a maximum of four decimal places. Use of Numeric/Decimal data type can be used for same purposes. Money Storage: 8 bytes. Small Money Storage: 4 bytes.
Money and Smallmoney data types
Only stores numeric data, not the currency symbol. Stores 8 bytes for money, and 4 bytes for small money. Stores 4 spaces to the right of the decimal.
Float data type
Store values in scientific notation. (n) is an optional parameter representing the number of digits to be stored after the decimal (called the mantissa). It determines the storage size of the float. If the mantissa is in the range 1 to 24, the float requires 4 bytes. If the mantissa is between 25 and 53, it requires 8 bytes. (Example: convert float to int). Float data type is approximate.
Collation
The properties that define character data on the server, database, table, or column level. The properties are language, sort order, case sensitivity, and accent sensitivity.
Binary String Data Types
The types are Binary, varbinary, and varbinary(max). Binary and varbinary store 1-8000 bytes. Use binary for fixed length and use varbinary for variable length. (provide example of using them)
Using NEWID() function
Used to generate nonsequential GUIDs.
Exact numeric data types that use integer data
bigint, int, smallint, tinyint
Real Data Type
An ISO synonym for float. Stores 4 bytes.
DATEADD Function
DATEADD(Day/Week/Month/Year, value to add to date, colname). (Example)
Length
Number of bytes that are used to store the number
Precision
Number of digits in a number
Scale
Number of digits to the right of the decimal place
int Data Type
Range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647), Storage: 4 bytes (Example: convert int to date and time, binary strings, and character strings.)
TRY_CONVERT Function
Same purpose as Convert function. Returns a NULL value if the conversion is valid but fails for some reason.
Return the just the date of a column
Select Cast(colName as date)
Return just the current time of a column
Select Cast(colName as time)
Varbinary Data Type
Stores variable length binary data. Can store anything. Use when the sizes of the column data entries vary considerably. Stores up to 8,000 bytes. The storage size is the actual length of the data entered + 2 bytes.
Char and Varchar vs nchar and nvarchar
The first group stores ASCII character set and the other stores Unicode character set. Unicode stores 2 bytes and ASCII stores 1 byte. Unicode supports all languages but ASCII does not.
FORMAT function
Use the FORMAT function for locale-aware formatting of date/time and number values as strings. Returns a varchar value.
Issues that can occur with data type Conversions
Values can be truncated (Can only occur with Explicit Conversions). The Collation of the data types may be different.
Explicit Data Type Conversion
When a Higher data type is converted to a data type with a lower precedence (need to use CAST or CONVERT). Will also work for correct implicit conversions.
Implicit Data Type Conversion
When a lower data type is converted to a data type with a higher precedence (automatically happens)
