Osama Assaf

Symmetric keys in SQl 2005

Use Symmetric Keys to encrypt the content of your data before inserting into the database.

Create Symmetric Key: 

CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = DESENCRYPTION BY PASSWORD = ‘MyPassword’

Open Symmetric Key for use:

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD = ‘MyPassword’ Encrypt data using Symmetric Key: 

SELECT ENCRYPTBYKEY(KEY_GUID (‘MyKey’),‘Hello World’)

Decrypt data using Symmetric Key: 

SELECT CONVERT(VARCHAR,DECRYPTBYKEY(ENCRYPTBYKEY(KEY_GUID (‘MyKey’),‘Hello World’)))

Paging Records using SQL 2005 ROW_NUMBER() Function

To retrieve data from an SQL 2005 database in pages, use ROW_NUMBER( ) function.

Records will numbered starting from 1. ORDER BY should be used to sort out data before numbering the records.

–This Example returns 20 Products as Page 1

SELECT ProductName,

WhenProduced

FROM

( SELECT ROW_NUMBER() OVER (ORDER BY WhenProduced DESC) AS RowNumber,

ProductName,

WhenProduced

FROM

ProductProductions

) AS ProductProductionWithRowNumbers

WHERE

RowNumber >= 1 AND RowNumber <= 20

–This Example returns 20 Products as Page 2

SELECT ProductName,

WhenProduced

FROM

(SELECT ROW_NUMBER() OVER (ORDER BY WhenProduced DESC) AS RowNumber,

ProductName,

WhenProduced

FROM

ProductProductions

) AS ProductProductionWithRowNumbers

WHERE

RowNumber >= 21 AND RowNumber <= 40