SQL COMPONENTS
In This Chapter
SQL’s Basic Objects
• Data Types
• Transact-SQL Functions
• Scalar Operators
• NULL Values
The Transact-SQL language's foundational objects and
operations are introduced in this chapter.
First, descriptions are given of the fundamental language
components, such as constants, identifiers, and delimiters. Then, data types
are thoroughly covered because each elementary object has a corresponding data
type. All currently used operators and functions are also described. NULL
values are introduced at the chapter's conclusion.
SQL’s Basic Objects
Transact-SQL, the language of the Database Engine, shares
the following characteristics with other popular programming languages:
• Literal values (also called constants)
• Identifiers
• Delimiters
• Comments
• Reserved keywords
These features are described in the sections that follow.
Literal values
An alphanumeric, hexadecimal, or numerical constant is an
illustration of a literal value. One or more characters from the character set
are contained in single or double straight quote marks (' ') within a string
constant (" "). (Single quotation marks are recommended because
double quotation marks are frequently used, as will be covered in a moment.)
Use two consecutive single quotation marks to separate a string from another
string if you wish to include a single quotation mark in the string. In order
to represent nonprintable characters and other binary data, hexadecimal
constants are utilised. Every hexadecimal constant starts with the letters
"0x" and then an even number of letters or digits. Hexadecimal and other
valid and invalid string constants are shown in examples 4.1 and 4.2.
Example 4.1 Some valid string constants and hexadecimal
constants follow:
'Philadelphia'
"Berkeley, CA 94710"
'9876'
'Apostrophe is
displayed like this: can' 't' (note the two consecutive single quotation marks)
0x53514C0D
Example 4.2 The following are not string constants:
'AB'C' (odd number of single quotation marks) 'New
York" (same type of quotation mark—single or double—must be used at each
end of the string) The numeric constants include all integer, fixed-point, and
floating-point values with and without signs.
Identifiers: -
Identifiers are used in Transact-SQL to identify database
objects including databases, tables, and indices. They are represented as
character strings that can contain up to 128 characters, letters, numbers, or
the _, @, #, and $ characters. Each name must be preceded by a letter or one of
the symbols _, @, or #. A temporary object is indicated by the character # at
the start of a table or stored procedure name, whereas a variable is indicated by
the character @ at the start of a name. These guidelines don't apply to
delimited identifiers (also known as quoted identifiers), which can contain or
start with any character, as detailed in the following section (other than the
delimiters themselves).
Delimiters: -
Double quotation marks in Transact-SQL have two distinct
meanings. Double quotation marks can be used as a delimiter for so-called
delimited identifiers in addition to enclosing strings. A specific type of
identifier called a delimited identifier is typically used to permit the usage
of reserved words as identifiers and to permit spaces in the names of database
objects.
In Transact-SQL, the QUOTED IDENTIFIER option of the SET
statement is used to define the use of double quotation marks. An identifier
enclosed in double quotation marks will be regarded as a delimited identifier
if this option is turned on, which is the default setting. Double quote marks
cannot be used to separate strings in this situation.
Comments:-
In a Transact-SQL statement, there are two alternative ways to specify a comment. When the characters /* and */ are used, the surrounded content is designated as a comment. The comment in this instance might span multiple lines. Also indicating that the rest of the line is a comment are the characters — (two hyphens). (The two — are Transact-SQL additions, whereas /* and */ conform to the ANSI SQL standard.)
Data Types: -
A column's data values must all be of the same data type. (The SQL VARIANT data type's values are the sole exception.) Transact-SQL employs a variety of data types that fall into the following categories:
• Numeric data types
• Character data types
• Temporal (date and/or time) data types
• Miscellaneous data types
All these categories are described in the sections that follow.
Numeric Data Types:-
Numbers are represented using numeric data types. The complete list of all numerical data types is shown in the table below:
Character Data Types:-
In SQL, character data types are used to store character or
string values. The most commonly used character data types are:
CHAR(n): This data type is used to store fixed-length
character strings. The "n" in the parentheses specifies the maximum
number of characters that can be stored. For example, CHAR(10) can store a
maximum of 10 characters.
VARCHAR(n): This data type is used to store variable-length
character strings. The "n" in the parentheses specifies the maximum
number of characters that can be stored. For example, VARCHAR(50) can store a
maximum of 50 characters.
TEXT: This data type is used to store large text values that
may exceed the maximum limit of the CHAR or VARCHAR data types.
BLOB: This data type is used to store binary large objects
such as images, audio, or video files.
It's important to note that the maximum limit and storage
size may vary based on the database management system you are using.
