Types of Data Literals in SQL Server Transact-SQL

Q

What are different types of data literals supported in SQL Server Transact-SQL?

✍: FYIcenter.com

A

There are 8 types of data literals supported in SQL Server Transact-SQL:

1. Integer Number Literals - Sequences of numbers prefixed with +/- sign if needed. Integer number literals are used to represent integer values. For example, -2255, 1, -1, and 999 integer number literals.

2. Decimal Number Literals - Sequences of numbers with a single decimal point and an optional prefix sign +/-. Decimal number literals are used to represent numeric values with fractional parts. For example: 1894.1204, 1.0 and -0.0001 are decimal number literals.

Integer number literals with decimal number literals together are also called exact number literals, because they are stored in memory with predefined exact precisions and scales.

3. Approximate Number Literals - Numeric values expressed in the scientific notation. Approximate number literals are also used to represent numeric values with fractional parts. But they are stored in memory using IEEE floating point formats. For example, 9.22337e+3, and 9.22337e-3 approximate number literals.

Approximate number literals are also called floating point number literals.

4. Single-byte String Literals - Sequences of characters enclosed in single quotes. Single-byte string literals are used to represent character string values in a specific character code page. For example, 'Hello World!', 'FYIcenter.com' and 'Murphy''s law' are single-byte string literals.

5. Unicode String Literals - Sequences of characters enclosed in single quotes and prefixed with N. Single-byte string literals are used to represent Unicode character string values. For example, N'Murphy''s law' and N'友爱' are Unicode character string literals.

6. Binary String Literals - Sequences of byte values expressed hexadecimal digits and prefixed with 0x. Binary string literals are used to represent byte string values. For example, 0x69048AEFDD010E and 0x41424344 are binary string literals.

7. Currency Value Literals - Sequences of numbers with a currency sign prefix, an optional sign +/-, and an optional decimal point. Currency value literals are used to represent currency values. For example, $542023.14 and €542023.14 are currency literals.

8. NULL Value Literal - Used to provide the NULL value For example, NULL and null are NULL literals.

By the way, there are no date and time literals. Single-byte string literals are used to provide to date and time values.

 

Data Literals in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2017-05-20, 319👍, 0💬