WIKI

On time multi or infinite rows of data need to be fetched or processed. (To process result set.)

Here is a CURSOR sample in MS Stored Procedure:

CREATE PROCEDURE sp_Test()
AS
 
DECLARE @colA nvarchar(10)
DECLARE @colB nvarchar(10)
DECLARE @MyCursor CURSOR
 
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT colA,colB
FROM tableA
 
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColA,@ColB
 
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ColA
PRINT @ColB
FETCH NEXT FROM @MyCursor
INTO @ColA,@ColB
END
 
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

• TRIM() RTRIM LTRIM to tailor spaces
• SUBSTR()to extract substring
• LEN() to get string length
• UPPER() to get uppercase chars
• CHARINDEX(exp1, exp2) to get starting point of exp2 on occurrence of exp1

see mssql_string_func

Datalength() returns number of bytes used to represent any expression, retain any spacing lengths. While LEN() returns the actual number of characters of string value with spaces trimed.

• DATALEN will return the number of bytes that are used to store the value:

http://msdn.microsoft.com/en-us/library/ms173486(SQL.90).aspx

• LEN will return the number of characters in a string. Since a string can use single or double-byte characters, this differs from DATALENGTH in that you will always get 1, no matter how long a single character is:

http://msdn.microsoft.com/en-us/library/ms190329.aspx

Remove the rows and then use DBCC CHECKIDENT(sometable, RESEED, 1) to reset identities

see http://msdn.microsoft.com/en-us/library/ms176057.aspx

• Any query with parameters is a dynamic SQL (With variable declaration)
• EXEC it (Build the statements on the fly)
• EXECUTE sp_executesql (Build the statements on the fly, an can use variables thus datatype-checking is provided)

SQL Server offers a few ways of running a dynamically built SQL statement. These ways are:

1. Writing a query with parameters
2. Using EXEC
3. Using sp_executesql

• This EXEC approach is pretty straight forward if you only need to pass parameters into your WHERE clause of your SQL statement. Let's say we need to find all records from the customers table where City = 'London'. This can be done easily such as the following example shows.

DECLARE @city varchar(75)
SET @city = 'London'
SELECT * FROM customers WHERE City = @city

• With 2nd approach you are building the SQL statement on the fly and can pretty much do whatever you need to in order to construct the statement.

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)

• With sp_exectesql approach you have the ability to still dynamically build the query, but you are also able to still use parameters. This saves the need to have to deal with the extra quotes to get the query to build correctly. In addition, with using this approach you can ensure that the data values being passed into the query are the correct datatypes.

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

There are 5 levels, the key point of which is to set what kind of read lock is aquired:

• read uncommitted

lowest lock level, gives full concurrency and may cause inaccurate data to be read by multiple sessions

• read commited

Default for MS SQL DB. A session cannot read data in time when it is updated by another session.

• repeatable read

Similar to above, retains locks on every row it touches, Even rows that do not qualify for the query result remain locked. Useful for counting and updating etc.

• snapshot

Reads snapshot data provided on expression starting.

• serializable

Most restrictive, data range of each statements are locked and serialized.

Syntax:

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
[ ; ]

Quote from MSDN

READ UNCOMMITTED

    Specifies that statements can read rows that have been modified by other transactions but not yet committed.

    Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

    In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

        * The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
        * The SNAPSHOT isolation level.

READ COMMITTED

    Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

    The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

        * If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine (Motor de base de datos) uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.
          ms173763.note(es-es,SQL.105).gifNota:
          In SQL Server 2008 R2, file system access to FILESTREAM data follows the READ_COMMITTED_SNAPSHOT isolation database setting.

          If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine (Motor de base de datos) uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

    When the READ_COMMITTED_SNAPSHOT database option is ON, you can use the READCOMMITTEDLOCK table hint to request shared locking instead of row versioning for individual statements in transactions running at the READ COMMITTED isolation level.
    ms173763.note(es-es,SQL.105).gifNota:
    When you set the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. There must be no other open connection in the database until ALTER DATABASE is complete. The database does not have to be in single-user mode.

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.

    Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary.

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. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

    Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.

    During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.

    The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

    A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts the first time it accesses data.

    A transaction running under SNAPSHOT isolation level can view changes made by that transaction. For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.
    ms173763.note(es-es,SQL.105).gifNota:
    Under snapshot isolation FILESTREAM data is presented at the beginning of the transaction not the Transact-SQL statement.

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.

    Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

A lock escalation is a conversion from lower scope locks, such as row locks, to wide ranged locks, such as table locks. In time locks reaches some threshhold defined on Server.

Quote from MSDN:

Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead. Microsoft® SQL Server™ 2000 automatically escalates row locks and page locks into table locks when a transaction exceeds its escalation threshold.

For example, when a transaction requests rows from a table, SQL Server automatically acquires locks on those rows affected and places higher-level intent locks on the pages and table, or index, which contain those rows. When the number of locks held by the transaction exceeds its threshold, SQL Server attempts to change the intent lock on the table to a stronger lock (for example, an intent exclusive (IX) would change to an exclusive (X) lock). After acquiring the stronger lock, all page and row level locks held by the transaction on the table are released, reducing lock overhead.

SQL Server may choose to do both row and page locking for the same query, for example, placing page locks on the index (if enough contiguous keys in a nonclustered index node are selected to satisfy the query) and row locks on the data. This reduces the likelihood that lock escalation will be necessary.

Lock escalation thresholds are determined dynamically by SQL Server and do not require configuration.

A join that applied between same table.

A table can be joined to itself in a self-join. Use a self-join when you want to create a result set that joins records in a table with other records in the same table. To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name. This table alias helps the query processor determine whether columns should present data from the right or left version of the table.

Example: The following example uses a self-join to find the products that are supplied by more than one vendor

USE AdventureWorks2008R2;
GO
SELECT DISTINCT pv1.ProductID, pv1.VendorID
FROM Purchasing.ProductVendor pv1
    INNER JOIN Purchasing.ProductVendor pv2
    ON pv1.ProductID = pv2.ProductID
        AND pv1.VendorID <> pv2.VendorID
ORDER BY pv1.ProductID

The following example performs a self-join of the Sales.SalesPerson table to produce a list of all the territories and the sales people working in them.

SELECT st.Name AS TerritoryName, sp.BusinessEntityID, 
    sp.SalesQuota, sp.SalesYTD
FROM Sales.SalesPerson AS sp
    JOIN Sales.SalesTerritory AS st
        ON sp.TerritoryID = st.TerritoryID
ORDER BY st.Name, sp.BusinessEntityID

A full outer join combines all the data on left and right values, all records from both table retains, NULL is filled on mismatch. While cross join, produce an “X” operation. It is an unconditioned(or always true condition) inner join thus requires matching,

The Full Outer Join logical operator returns each row satisfying the join predicate from the first (top) input joined with each row from the second (bottom) input. It also returns rows from:

• The first input that had no matches in the second input.
• The second input that had no matches in the first input.

The input that does not contain the matching values is returned as a null value.

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The following example shows a Transact-SQL cross join.

USE AdventureWorks2008R2;
GO
SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
ORDER BY p.BusinessEntityID;

The join is ordered. However, if a WHERE clause is added, the cross join behaves as an inner join. For example, the following Transact-SQL queries produce the same result set. Copy

USE AdventureWorks2008R2;
GO
SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
WHERE p.TerritoryID = t.TerritoryID
ORDER BY p.BusinessEntityID;
 
-- Or
 
USE AdventureWorks2008R2;
GO
SELECT p.BusinessEntityID, t.Name AS Territory
FROM Sales.SalesPerson p
INNER JOIN Sales.SalesTerritory t
ON p.TerritoryID = t.TerritoryID
ORDER BY p.BusinessEntityID;