Encrypt Decrypt Password in SQL 2005

 BEGIN TRAN

-- Create Master Key

CREATE MASTER KEY ENCRYPTION BY
    PASSWORD ='man@123'


-- Create Certificate

CREATE CERTIFICATE TEST_Certificate
    WITH SUBJECT='Maneesh'


-- Create Symmetric Key

CREATE SYMMETRIC KEY TEST_SYMKEY
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE TEST_Certificate


-- Inserting records into the table

    OPEN SYMMETRIC KEY TEST_SYMKEY
       DECRYPTION BY CERTIFICATE TEST_Certificate;


    INSERT INTO TestTable
    ([NAME], PASSWORD)
    VALUES
    ('amit', EncryptByKey(Key_GUID('TEST_SYMKEY'), 'pass@123'))


    CLOSE SYMMETRIC KEY TEST_SYMKEY


-- Drop or Close Master Key

    CLOSE MASTER KEY -- FOR CLOSE
    DROP MASTER KEY -- FOR DROP

-- Getting the output again

    OPEN SYMMETRIC KEY TEST_SYMKEY DECRYPTION
    BY CERTIFICATE TEST_Certificate

    SELECT CONVERT(VARCHAR(50),DecryptByKey(Password)) AS DecryptSecondCol
    FROM TestTable




Reference: Govind Badkur(http://sqlserver20.blogspot.com)

Recursive Queries using Common Table Expressions (CTE)

       Sometimes we have data in a Hierarchical format, and we are to perform some Insert/Update/Delete operation over there. In this situation if the DataBase Server is SQL Server 2000 or lower version, then the task becomes bit complex and need to apply the logic with the help of views, cursors, temp tables or any other object.

        But, if the Database Server is SQL Server 2005 or later version, then the task becomes easier and can implement the Common Table Expressions (CTE).

        Here is an example to elaborate the concept. Let's say we have table UserDetails as below :

UserID
CityName
ParentID
U001
Mumbai
U001
U002
Bhopal
U002
U003
Delhi
U003
U004
Bhopal
U002
U005
Mumbai
U001
U006
Delhi
U003
U007
Mumbai
U001
U008
Delhi
U003
U009
Bhopal
U002
U010
Mumbai
U001
U011
Delhi
U003
U012
Mumbai
U001

    Now from the above table we like to get all the users which come under the hierarchy of user having UserID as "U001".

  To get the above result, we will apply the CTE with below code :

;WITH CTE

AS

(

      SELECT * FROM UserDetails  

      WHERE UserID = 'U001' 



      UNION ALL



        


      SELECT  MS.*

      FROM    UserDetails MS



              ON MP.UserID = MS.ParentID 
              INNER JOIN CTE MP 

)

SELECT * FROM CTE
ORDER BY UserID

 Executing the above code, we will get the below ouput :

UserID
CityName
ParentID
U001
Mumbai
U001
U005
Mumbai
U001
U007
Mumbai
U005
U010
Mumbai
U007
U012
Mumbai
U005

 Reference: Govind Badkur(http://sqlserver20.blogspot.com)