SQL Server


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

Advertisements

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.

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.

The master database holds information for all databases located on the SQL Server instance and is theglue that holds the engine together. Because SQL Server cannot start without a functioning masterdatabase, you must administer this database with care.

The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

The tempdb holds temporary objects such as global and local temporary tables and stored procedures.

The model is essentially a template database used in the creation of any new user database created in the instance.

The resoure Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

Next Page »