ACID Normalisation
1NF Every row-by-column position in a table must have only one value, no repeating columns.
Atomicity 2NF Store only data that relates to one entity ("thing"), described by the PK.
A transaction must be an atomic unit of work; either all of its data modifications are 3NF All non-key columns must be mutually independent.
performed, or none of them is performed. Obvious example of a dependency is a calculated column, which is a no-no.
Consistency
When completed, a transaction must leave all data in a consistent state. In a
relational database, all rules must be applied to the transaction's modifications to Denormalisation
maintain all data integrity. All internal data structures, such as B-tree indexes or
doubly linked lists, must be correct at the end of the transaction. Process of defining a table that is not normalized to enhance performance and simplify queries.
Isolation Recommended when normalization causes many queries to join in excess of four (4) ways.
Modifications made by concurrent transactions must be isolated from the
modifications made by any other concurrent transactions. A transaction either sees
data in the state it was in before another concurrent transaction modified it, or it Transact-SQL Batch Denormalised
sees the data after the second transaction has completed, but it does not see an DECLARE @dbname varchar(25)
intermediate state. This is referred to as serializability because it results in the DECLARE @tblname varchar(25)
ability to reload the starting data and replay a series of transactions to end up with SET @dbname = 'Sales'
the data in the same state it was in after the original transactions were performed. SET @tblname = 'Customer'
EXEC ( ' USE ' + @dbname +
Durability
'SELECT * FROM ' + @tblname )
After a transaction has completed, its effects are permanently in place in the
system. The modifications persist even in the event of a system failure.
1NF
CREATE PROC[EDURE] procedure_name [;number]
[ PAD_INDEX
{@parameter data_type} [VARYING] [= default] [OUTPUT] INSERT [INTO] ... Free space on
] {
non-leaf pages.
[,...n] table_name WITH ( <table_hint_limited> [...n])
Requires
[WITH | view_name
FILLFACTOR.
{ | rowset_function_limited
RECOMPILE }
2NF
| ENCRYPTION FILLFACTOR ...
| RECOMPILE, ENCRYPTION { [(column_list)] % definition of how
} { VALUES ( { DEFAULT much to fill leaf level
] | NULL pages. Low = few page
[FOR REPLICATION] | expression splits, but large index.
AS }[,...n]
sql_statement [...n] )
| derived_table Summary Data
UPDATE | execute_statement
DDL: Data Definition Language }
3NF
{
} GROUP BY ... organise rows into
table_name WITH ( <table_hint_limited> [...n]) DML: Data Maniupulation Language | DEFAULT VALUES
| view_name groups and summarise groups.
| rowset_function_limited HAVING ... restrict GROUPing
} CUBE ... GROUP BY summary is
SET
{column_name = {expression | DEFAULT |
Error Handling retrned for every possible
combination of group and
NULL}
| @variable = expression RAISERROR subgroup, displayed as NULL.
INDEXES COMPOSITE Data Integrity
| @variable = column = expression } [,...n] BEGIN ROLLUP ... Summary rowes are ... 1-16 columns Domain (column) integrity NULL, DEFAULT, CHECK Datatypes, Rules, Defaults, Triggers,
RAISERROR ('The job_id:%d must be between %d and %d.', added into the result set. SPs
{{[FROM {<table_source>} [,...n] ] 16, 1, @@JOB_ID, @@MIN_LVL, @@MAX_LVL)
Entity (table) integrity PK, NULL, UNIQUE UNIQUE indexes, New Identity datatype
ROLLBACK TRANSACTION
Referential integrity FK, REFERENCES Triggers, SPs
[WHERE END
User-defined integrity FK, CHECK, REFERENCES Rules, Triggers, SPs
|
<search_condition>] } severity CLUSTERED ... Key values same as physical order of cor. rows.
Is the user-defined severity level associated with this message.
[WHERE CURRENT OF Severity levels from 0 through 18 can be used by any user. NON-CLUSTERED ... Physical store does not match physical order. Stored procedure: EXEC update_prices -or- Sales.dbo.update_prices
{ { [GLOBAL] cursor_name } | - level 17: insufficient resources .. WITH INDEX(0) >> clustered index, table scan if no CIndex
cursor_variable_name} - level 18: non fatal internal error .. WITH INDEX(1) >> clustered index, fail if no CIndex Triggers
]} Severity levels 19 through 25 are used only by members of the sysadmin
[OPTION (<query_hint> [,...n] )] fixed server role. For severity levels 19 through 25, the WITH LOG option
Isolation Levels INSERT … rows added to the table and an in memory table called "inserted".
is required. 20-25 are fatal. UPDATE … original rows moved to an in-memory table "deleted" and new rows added to the "inserted" table.
DELETE DELETE … rows deleted are moved to the "deleted" table.
[FROM ] READ COMMITTED ... Specifies that shared locks are held while the data is being read to avoid dirty Nested Triggers ... Triggers can be nested up to 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and
{
tab_name WITH ( <table_hint_limited> [...n])
View reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or
can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. To disable
phantom data. This option is the SQL Server default.
| view_name nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows nested triggers. If nested triggers is off, recursive
READ UNCOMMITTED ... Implements dirty read, or isolation level 0 locking, which means that no
| rowset_function_limited 1. SQL allows 32 nesting levels triggers is also disabled, regardless of the recursive triggers setting of sp_dboption.
shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read
} 2. Cannot include: uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the
ORDER BY, COMPUTE, data set before the end of the transaction. This option has the same effect as setting NOLOCK on all
[ FROM {<table_source>} [,...n] ] OMPUTE BY or INTO tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels. Data Types
3. Cannot associate triggers or DEFAULT. REPEATABLE READ ... Locks are placed on all data that is used in a query, preventing other users
[WHERE
{ <search_condition> 4. WITH CHECK OPTION allows changes to view data from updating the data, but new phantom rows can be inserted into the data set by another user and are bit Integer data with either a 1 or 0 value.
| { [ CURRENT OF only if it is within the bounds of the view criteria included in later reads in the current transaction. Because concurrency is lower than the default isolation bigint 64-bit Integer (whole number) data from -2^63 (-9,223,372,036,854,775,807) through 2^63 (9,223,372,036,854,775,807).
{ level, use this option only when necessary. int 32-bit Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - (2,147,483,647).
{ [ GLOBAL ] cursor_name } SERIALIZABLE ... Places a range lock on the data set, preventing other users from updating or smallint 16-bit Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).
| cursor_variable_name inserting rows into the data set until the transaction is complete. This is the most restrictive of the four
tinyint Integer data from 0 through 255.
} Joins isolation levels. Because concurrency is lower, use this option only when necessary. This option has the
decimal Fixed precision and scale numeric data from -10^38 -1 through 10^38 -1.
] same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
} INNER ... Specifies all matching pairs of rows SCHEMA ... Used when an operation dependent on the schema of a table is executing. The types of numeric A synonym for decimal.
] are returned. Discards unmatched rows from schema locks are: schema modification (Sch-M) and schema stability (Sch-S). money Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a
[OPTION (<query_hint> [,...n])]
SQL both tables. This is the default if no join type is
specified.
FULL [OUTER] ... Specifies that a row from SQL Cursors
smallmoney
float
ten-thousandth of a monetary unit.
Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
Floating precision number data from -1.79E + 308 through 1.79E + 308.
real Floating precision number data from -3.40E + 38 through 3.40E + 38.
R&D either the left or right table that does not meet
the join condition is included in the result set,
and output columns that correspond to the
Static
Fixes the result set when the cursor is opened. READ-ONLY! Static cursors detect few or
datetime
smalldatetime
cursor
Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of three-hundredths of a sec, or 3.33 millisec.
Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
A reference to a cursor.
other table are set to NULL. This is in addition no changes but consume relatively few resources while scrolling, although they store the
to all rows usually returned by the INNER JOIN. timestamp A database-wide unique number.
entire cursor in tempdb. uniqueidentifier A globally unique identifier (128-bit GUID).
LEFT [OUTER] ... Specifies that all rows from Dynamic
the left table not meeting the join condition are char Fixed-length non-Unicode character data with a maximum length of 8,000 chars.
Opposite of static. Changes are reflected! Dynamic cursors detect all changes but nchar Fixed-length Unicode data with a maximum length of 4,000 characters.
included in the result set, and output columns consume more resources while scrolling, although they make the lightest use of tempdb.
from the other table are set to NULL in addition varchar Variable-length non-Unicode data with a maximum of 8,000 characters.
READ_ONLY keyword makes the dynamic cursor read-only. nvarchar Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type
to all rows returned by the inner join. Key-Set Driven
RIGHT [OUTER] ... Specifies all rows from the that is a synonym for nvarchar(128) and is used to reference database object names.
Controlled by a unique set of identifiers. Keyset-driven cursors lie in between, detecting text Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.
right table not meeting the join condition are most changes but at less expense than dynamic cursors. Changes made by owner can be
included in the result set, and output columns ntext Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.
seen, others not. binary Fixed-length binary data with a maximum length of 8,000 bytes.
that correspond to the other table are set to Forward-only … fetch rows from start to end. FORWARD_ONLY keyword
NULL, in addition to all rows returned by the varbinary Variable-length binary data with a maximum length of 8,000 bytes.
Scrolling … up and down image Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.
BARONE BUDGE & DOMINICK (PTY) Ltd inner join.
