Notes on SQL 4

Previous related posts:

• Notes on SQL 1


• Notes on SQL 2


• Notes on SQL 3

Data Types

SQL Server System data types VS User-defined types (UDTs) or SQL Common Language Runtime (SQLCLR) types

Collations for character types are provided by the table-valued fn_helpcollations function. Names of collations (e.g. Latin1_General_CI_AI) specify case sensitivity (CI VS CS) and accent sensitivity (AI VS AS). However, note that what counts as an "accent" differs in different languages (and thus collations).

When doing operations on a column, a specific collation (different from the one originally defined on the column) can be used like this: WHERE Name = 'Öqvist' COLLATE Finnish_Swedish_CI_AS. This prevents indexes on that column from being used for that particular expression, though.

int and decimal types (in all of their versions) are exact and work the same no matter the underlying processor architecture. Decimal (without parameters) defaults to decimal(18,0).

float and real are approximate (floating point) numeric types. For float(n), n specifies the amount of bits that represent the mantissa of the number. But in reality, this can only be 24 or 53, and n is "rounded up" to one of these values. real is equivalent to float(24).

• datetime: 1753-01-01 => 9999-12-31, accuracy to every third millisecond (so the last digit can only be 0, 4, 7). 23:59:59.998 gets rounded down to 23:59:59.997, but 23:59:59.999 gets rounded up to the next day.


• smalldatetime: 1900-01-01 => 2079-06-06, accuracy to the minute


• datetime2(fractional seconds precision): 0001-01-01 => 9999-12-31, accuracy to specified fractional seconds


• datetimeoffset(fractional seconds precision): as datetime2, plus a +/- 14:00 offset.


• date: 0001-01-01 => 9999-12-31


• time(fractional seconds precision): 00:00:00 => 23:59:59

rowversion columns are automatically set to a database-scoped binary counter whenever they are inserted or updated. It is like a "sequence number" that can be used to determine the order in which things happened (up to some point, since it can be overwritten if another modification to the row happens).

varchar(max), nvarchar(max), varbinary(max) and xml prevents online index rebuilding, and cannot be used in the key of an index.

Tables

"A schema is similar to a namespace in other programming languages, but only 1 level of schemas exist." The question that I never gave myself time to answer =P.

IDENTITY allows for a seed and an increment step (which can be negative): IDENTITY(seed, increment). It can only be applied on integer data types, and decimal(0). If an insertion fails, the generated number is NOT given to the next insert, it is lost.

Compression can be enabled on a page or row level.

Computed columns are not physically stored, unless PERSISTED is specified. "Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns."

Data integrity

Declarative data integrity (constraints on table columns) VS procedural data integrity (SPs and triggers).

Notes on constraint types:

• Primary Key/Unique: a unique index is automatically created (which cannot contain more than 16 columns or 900 bytes of data). PK defaults to creating a clustered index, Unique defaults to a non-clustered one (both can be overridden). They can be created on computed columns.


• Foreign Key: the column being referenced MUST have a unique index (either PK or Unique). Since they are used a lot (in joins and to verify referential data integrity), indexes are highly recommended on FK columns. The default behavior (called NO ACTION) when a referenced value is deleted (raise an error and rollback the deletion) can be changed to SET NULL, SET DEFAULT, or CASCADE. Different actions can be specified for (ON) DELETE and (ON) UPDATE. For SET NULL and SET DEFAULT, the columns must be nullable and have a default respectively (in the second case, NULL is the implicit default).


• Check: it's not that it accepts values that evaluate to true, but that it rejects values that evaluate to false (different because of NULL, which is accepted). Scalar UDFs can be used.


• Default: nothing of interest to say.

FK and Check constraints can be turned off, but doing some flags them as "not trusted", because someone could have messed with the data during that time (this can cause significant differences in execution plans). To make them "trusted" again, they must be turned back on specifying WITH CHECK, e.g.:

ALTER TABLE tableName
WITH CHECK
CHECK CONSTRAINT FKForeignKey

Click here for the next part in this series of posts.