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)