Using NOLOCK and READPAST table hints in SQL Server

NOLOCK

This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

The following example shows how NOLOCK works and how dirty reads can occur. In the script below, I begin a transaction and insert a record in the SalesHistory table.

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
 
The number of records returned is 301. Since the transaction that entered the  record into the SalesHistory table has not been committed, I can undo it. I'll  roll back the transaction by issuing the following statement:
ROLLBACK TRANSACTION
 
This statement removes the record from the SalesHistory table that I  previously inserted. Now I run the same SELECT statement that I ran earlier:
SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
 
This time the record count returned is 300. My first query read a record that  was not yet committed -- this is a dirty read.

READPAST
 
This is a much less commonly used table hint than NOLOCK. This hint specifies  that the database engine not consider any locked rows or data pages when  returning results
The READPAST table hint example is very similar to the NOLOCK table hint  example. I'll begin a transaction and update one record in the SalesHistory  table.
BEGIN TRANSACTION
      UPDATE TOP(1) SalesHistory
      SET SalePrice = SalePrice + 1
 
Because I do not commit or roll back the transaction, the locks that were  placed on the record that I updated are still in effect. In a new query editor  window, run the following script, which uses READPAST on the SalesHistory table  to count the number of records in the table.
SELECT COUNT(*)
FROM SalesHistory WITH(READPAST)
 
My SalesHistory table originally had 300 records in it. The UPDATE statement  is currently locking one record in the table. The script above that uses  READPAST returns 299 records, which means that because the record I am updating  is locked, it is ignored by the READPAST hint.

SQL 2005 - Save a password not as clear text

In SQL Server 2005, you can use HashBytes to hash the password and EncryptByKey to encrypt it.

Examples

A. Simple symmetric encryption

The following code shows how to encrypt a column by using a symmetric key.

USE AdventureWorks;

GO

--If there is no master key, create one now.

IF NOT EXISTS

(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'

GO

CREATE CERTIFICATE HumanResources037

WITH SUBJECT = 'Employee Social Security Numbers';

GO

CREATE SYMMETRIC KEY SSN_Key_01

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE HumanResources037;

GO

USE [AdventureWorks];

GO

-- Create a column in which to store the encrypted data.

ALTER TABLE HumanResources.Employee

ADD EncryptedNationalIDNumber varbinary(128);

GO

-- Open the symmetric key with which to encrypt the data.

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric

-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.

UPDATE HumanResources.Employee

SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);

GO

-- Verify the encryption.

-- First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

GO

-- Now list the original ID, the encrypted ID, and the

-- decrypted ciphertext. If the decryption worked, the original

-- and the decrypted ID will match.

SELECT NationalIDNumber, EncryptedNationalIDNumber

AS 'Encrypted ID Number',

CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

AS 'Decrypted ID Number'

FROM HumanResources.Employee;

GO

Output option in SQL 2005

"Output" provides you access to inserted and deleted logical tables. The data that has been inserted into the table OR the data after update statement has been executed, is available in inserted logical table.

declare @temptable table
(
empid int ,
name varchar(60)
)
UPDATE AllEmployees set NAME = 'upd'
OUTPUT inserted.employee_id , inserted.name into @temptable
WHERE Employee_id = 9

select * from @temptable

SQL 2005 use level for hierarchy select

To get all the people in the hierarchy starting from Top management we could use the the following query:

Create table AllEmployees ( Employee_id int primary key identity , name varchar(100), ManagerId int)
select * from AllEmployees
insert into AllEmployees(name,managerid) values ( E1, null)
insert into AllEmployees(name,managerid) values ( E2, null)
insert into AllEmployees(name,managerid) values ( 'E3' , 1)
insert into AllEmployees(name,managerid) values ( 'E4' , 2)
insert into AllEmployees(name,managerid) values ( 'E5' , 1)
insert into AllEmployees(name,managerid) values ( 'E6' , 2)
insert into AllEmployees(name,managerid) values ( 'E7' , 4)
insert into AllEmployees(name,managerid) values ( 'E8' , 4)
With LvlAllemployees as
(
select Allemployees.employee_id, Allemployees.name , Allemployees.managerid, 0 as level from AllEmployees where ManagerId is null
union ALL
select Allemployees.employee_id, Allemployees.name , Allemployees.managerid, level+1 as level from LvlAllEmployees
inner join Allemployees on LvlAllemployees.employee_id = allemployees.managerid
)
select * from Lvlallemployees order by level