SQL’s Basic Objects
Transact-SQL, has the same basic features as other common programming languages:
- Literal values (also called constants)
- Reserved keywords
A literal value is an alphanumerical, hexadecimal, or numeric constant. A string constant contains one or more characters of the character set enclosed in two single straight quotation marks
–valid string constants and hexadecimal constants
‘Apostrophe is displayed like this: can”t’
0x53514C0D — hexadecimal constant
— not valid
‘AB’C‘ (odd number of single quotation marks)
Double quotation marks can also be used as delimiters for so-called delimited identifiers. Delimited identifiers allow the use of reserved keywords as identifiers and also to allow spaces in the names of database objects
— delimited identifiers
SELECT “sometext” AS Value — FAIL because “sometext” is not a literal
There are two different ways to specify a comment in a Transact-SQL statement. Using the pair of characters /* and */ marks the enclosed text as a comment. The characters — (two hyphens) indicate that the remainder of the current line is a comment.
— comment on 1 line
is still a comment*/
Identifiers are used to identify database objects such as databases, tables, and indices.
- Character strings that may include up to 128 characters and can contain letters, numerals, or the following characters: _, @, #, and $.
- Each name must begin with a letter or one of the following characters: _, @, or #.
All the data values of a column must be of the same data type, apart from SQL_VAARIANT data type; they are
- Numeric data types
- Character data types
- Temporal (date and/or time) data types
- Miscellaneous data types
- DECIMAL with VARDECIMAL storage type
Numeric Data Types
Character Data Types
2 general forms of character data types
- single-byte characters
- Strings of Unicode characters.
- have fixed length
- variable length
Temporal Data Types
Transact-SQL supports the following temporal data types:
The DATE data type is stored in 3 bytes and has the range 01/01/0001 to 12/31/9999. The TIME data type is stored in 3–5 bytes. The DATETIME2 data type is also a new data type that stores high-precision
— date and time
— The following date descriptions can be used:
— (with SET DATEFORMAT dmy)
‘May 28, 1959’
‘1959 MAY 28’
Miscellaneous Data Types
Transact-SQL supports several data types that do not belong to any of the previous data type groups :
- Binary data types
- Large object data types
- User-defined data types
Binary data types
BINARY and VARBINARY are the two binary data types to represent data objects in the internal format of the system
The values of the BIT data type are stored in a single bit
Large Object Data Types
Large objects (LOBs) are data objects with the maximum length of 2GB
TO define LOBs:
- Use the data types VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) // preferred
- Use the so-called text/image data type
Use the FILESTREAM attribute to a VARBINARY(MAX) column to store large binary data directly in an NTFS file system.
UNIQUEIDENTIFIER Data Type
As its name implies, a value of the UNIQUEIDENTIFIER data type is a unique identification number stored as a 16-byte binary string; it used to guarantee uniqueness worldwide (through GUID)
SQL_VARIANT Data Type
The SQL_VARIANT data type can be used to store values of various data types at the same time, such as numeric values, strings, and date values.
HIERARCHYID Data Type
The HIERARCHYID data type is used to store an entire hierarchy.
Use GetAncestor(), GetDescendant(), Read(), and Write().
Transact-SQL functions can be either aggregate functions or scalar functions
Aggregate functions are applied to a group of data values from a column. Aggregate functions always return a single value.
Scalar functions that are used in the construction of scalar expressions. (It operates on a single value or list of values)
They can be categorized as follows:
- Numeric functions
- Date functions
— Date Functions
— > 2011-06-06 18:07:52.700
— > 6, part item of a date date as an integer
— > Monday, part item of the date date as a character string
- String functions
— String Functions
— > A
— > Ire, Returns the first length characters from the string z.
- System functions
— System functions
— > 3000000000, Converts an expression a into the specified data type type (if possible)
— > 1, the first expression that is not NULL.
- Metadata functions
— metadata functions
Functions return information concerning the specified database and database objects.
Global variables are special system variables that can be used as if they were scalar constants. They have to be preceded by the prefix @@
— global vars
A NULL value is a special value that may be assigned to a column. This value is normally used when information in a column is unknown or n/a
Any NULL value in the argument of aggregate functions AVG, SUM, MAX, MIN, and COUNT is eliminated before the respective function is calculated
A column of a table allows NULL values if its definition explicitly contains NULL.
— > NULL