Databases (General)


http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Advertisements

I was getting this error, because in the MySQL Manager, I had a select query open, that probably had the table locked. Ughhh…

There are 5 isolation levels in SQL 2005 (more)

  1. Read Uncommitted: Specifies that statements can read rows that have been modified by other transactions but not yet committed. This causes dirty reads.
  2. Read Committed (Default): Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.
  3. Repeatable Read: Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
  4. Snapshot: Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.
  5. Serializable: Specifies the following:
      – Statements cannot read data that has been modified but not yet committed by other transactions.
      – No other transactions can modify data that has been read by the current transaction until the current transaction completes.
      – Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

The UNION command is used to pull all rows from all tables fitting your query specifics, much like the JOIN command, and combines them into a table. However, when using the UNION command all selected columns need to be of the same data type.

1. UNION ALL will not eliminate duplicate rows; UNION will eliminate duplicate rows by effectively doing a SELECT DISTINCT on the results set

2. UNION ALL is faster if you know that all the records returned are unique; else use UNION

1. Row Compression

Row compression changes the format of physical storage of data. It minimize the metadata (column information, length, offsets etc) associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar.  (Read More Here)

2. Page Compression

Page compression allows common data to be shared between rows for a given page. Its uses the following techniques to compress data:

  • Row compression.
  • Prefix Compression. For every column in a page duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which resides after page header. A reference number is assigned to these prefixes and that reference number is replaced where ever those prefixes are being used.

3. Dictionary Compression

Dictionary compression searches for duplicate values throughout the page and stores them in CI. The main difference between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page.

Using the NOLOCK query optimizer hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read.
CON:
1. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data.
2. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay (blocking).
PRO:
1. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks.
2. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data.

Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

Transaction isolation levels control:

  • Whether locks are taken when data is read, and what type of locks are requested.
  • How long the read locks are held.
  • Whether a read operation referencing rows modified by another transaction:
  • Blocks until the exclusive lock on the row is freed.
  • Retrieves the committed version of the row that existed at the time the statement or transaction started.
  • Reads the uncommitted data modification.

Next Page »