Row and Page Compression in SQL Server 2008

Row compression is used to minimize storage at the row level by compressing fixed-length data types.


Page compression is used to minimize storage for redundant data stored in pages.

Find max value in Excel

Cell B1=MAX(A:A)

Clone table in SQL Server

SELECT *
INTO MyNewTable
FROM MyTable

XML Query in SQL Server

DECLARE @prodList xml =''



'';

WITH XMLNAMESPACES(DEFAULT 'urn:Wide_World_Importers/schemas/Products')
SELECT prod.value('./@Name','varchar(100)'), prod.value('./@Category','varchar(20)'), prod.value('./ @Price','money')
FROM @prodList.nodes('/ProductList/Product') ProdList(prod);

Transactions statements in SQL Server

SET IMPLICIT_TRANSACTIONS

SET REMOTE_PROC_TRANSACTIONS

SET TRANSACTION ISOLATION LEVEL

SET XACT_ABORT

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server.

Isolation Level of SSIS

ReadUncommited: Does not lock the records being read. This means that an uncommitted change can be read and then rolled back by another client, resulting in a local copy of a record that is not consistent with what is stored in the database. This is called a dirty read because the data is inconsistent.

Chaos: Behaves the same way as ReadUncommitted, but checks the isolation level of other pending transactions during a write operation so that transactions with more restrictive isolation levels are not overwritten.

ReadCommitted: Locks the records being read and immediately frees the lock as soon as the records have been read. This prevents any changes from being read before they are committed, but it does not prevent records from being added, deleted, or changed by other clients during the transaction.

RepeatableRead: Locks the records being read and keeps the lock until the transaction completes. This ensures that the data being read does not change during the transaction.

Serializable: Locks the entire data set being read and keeps the lock until the transaction completes. This ensures that the data and its order within the database do not change during the transaction.

Snapshot: The data read within a transaction will never reflect changes made by other simultaneous transactions. The transaction uses the data row versions that exist when the transaction begins. No locks are placed on the data when it is read.

CUBE, ROLLUP, GROUPING SETS

Grouping Sets in SQL Server 2008

http://blogs.msdn.com/b/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx

Aggregation WITH ROLLUP

http://blogs.msdn.com/b/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx

Aggregation WITH CUBE

http://blogs.msdn.com/b/craigfr/archive/2007/09/27/aggregation-with-cube.aspx