Generate Random Number for each row in SQL Server

In this post, I am sharing how  to generate random values for each row in a result set in SQL Server

Idea here is to generate another column in SELECT statement that gives you random values for each row each time the query is executed.

Let's create a Table and populate some data in it.

CREATE TABLE TAB (
    ID VARCHAR(10), NAME VARCHAR(10)
)
GO
INSERT INTO TAB
SELECT 1, 'Anil' UNION ALL
SELECT 2, 'Sunil' UNION ALL
SELECT 3, 'Amit' UNION ALL
SELECT 4, 'Sumit' UNION ALL
SELECT 5, 'Rajesh'

In SQL Server, there is in-built function RAND() to generate random values, but when it is used in SELECT statement, it generates a static value for each row, so this is not going to help us in this scenario. However, NEWID() generates a random value for each row. Let's execute a script to see the difference

SELECT  RAND() AS RAND, NEWID() AS NEWID, ID, NAME
FROM    TAB

OUTPUT

Check the result, you will find RAND() function is giving static value for each row but NEWID() is giving random values, so NEWID() is going to help us in getting random row numbers. Now, Lets see how we can use NEWID() to generate the random values

SELECT  ABS(CHECKSUM(NEWID())%100) AS NEWID, ID, NAME
FROM    TAB

OUTPUT

 

Check the above result, here is a new column NEWID in which there is random values for each rows. Try running the script multiple times, you will find that this will generate different values each time for each row

SQL Server Column Level Encryption Example using Symmetric Keys

In this article, I have posted step by step procedure to apply encryption on columns in SQL Server using symmetric keys.

Step 1 - Create a sample SQL Server table
Let’s use an example where we create the dbo.Customer_data table which contains credit card details for customers. Our task is to protect this data by encrypting the column, which contains the credit card number. I will populate it will some sample data as shown below.

CREATE TABLE dbo.Customer_data(
      Customer_id            INT CONSTRAINT Pkey3 PRIMARY KEY NOT NULL
    , Customer_Name            VARCHAR(100) NOT NULL
    , Credit_card_number    VARCHAR(25) NOT NULL
)
INSERT INTO dbo.Customer_data
SELECT 1,'Cust1','1111-2222-3333' UNION ALL
SELECT 2,'Cust2','1452-2563-1526' UNION ALL
SELECT 3,'Cust3','2147-4526-4587'

SELECT * FROM dbo.Customer_data

OUTPUT

Step 2 - SQL Server Service Master Key
The Service Master Key is the root of the SQL Server encryption hierarchy. It is created during the instance creation. Confirm it's existence using the query below. If it does not exist we need to manually create it.

USE master
GO
SELECT  *
FROM    sys.symmetric_keys
WHERE   name = '##MS_ServiceMasterKey##'

Step 3 - SQL Server Database Master Key
The next step is to create a database master key. This is accomplished using the CREATE MASTER KEY method. The "encrypt by password" argument is required and defines the password used to encrypt the key. The DMK does not directly encrypt data, but provides the ability to create keys that are used for data  encryption. It is important that you keep the encryption password in a safe place and/or keep backups of your SQL Server Database Master Key.

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

Step 4 - Create a Self Signed SQL Server Certificate
The next step is to create a self-signed certificate that is protected by the database master key. A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server. An optional argument when creating a certificate is ENCRYPTION BY PASSWORD. This argument defines a password protection method of the certificate's private key. In our creation of the certificate we have chosen to not include this argument; by doing so we are specifying that the certificate is to be protected by the database master key.

CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Secure Data'

Step 5 - SQL Server Symmetric Key
A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database

CREATE SYMMETRIC KEY SymmetricKey1
WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Certificate1

Step 6 - Schema changes
An Encrypted column can only be of datatype varbinary and since the column we want to encrypt is of datatype varchar, we have to create a new column and populate it with encrypted values.

ALTER TABLE Customer_data
ADD Credit_card_number_encrypt VARBINARY(MAX) NULL

Step 7 - Encrypting the newly created column
To encrypt the data we will use the EncryptByKey Command. Below is a sample code which can be used to encrypt the data. Please note that symmetric key needs to opened before we can encrypt data and be sure you manually close the key else it will remain open for the current session.

OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
UPDATE  Customer_data
SET     Credit_card_number_encrypt = EncryptByKey (Key_GUID('SymmetricKey1'),Credit_card_number)
FROM    dbo.Customer_data;
GO
CLOSE SYMMETRIC KEY SymmetricKey1;

OUTPUT

Step 8 - Remove old column
To finalize this process, let's remove the old column so that the table is left only with the encrypted data.

ALTER TABLE Customer_data DROP COLUMN Credit_card_number;

Step 9 - Reading the SQL Server Encrypted Data
Let's take a look at an example of reading data using the decrypt bykey option. As we indicated before, make sure you open and closesymmetric key as shown earlier.

OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1;
GO
SELECT  Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number'
        , CONVERT(VARCHAR, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM    dbo.Customer_data;
CLOSE SYMMETRIC KEY SymmetricKey1;

OUTPUT

Step 10 - Adding Records to the Table
Below is the sample code to insert values into the newly created encrypted column.

OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1;
INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number_encrypt)
VALUES (4, 'Cust4', EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,'2539-2222-1245')))

OUTPUT

Dynamic Pivot in SQL Server

Pivot in SQL Server is used to transform row values of a column as the columns of the output.

What is the need of dynamic pivot?
When the row values of the column on which you are going to pivot are static i.e you already know what all set of values exist in the column, then static pivot would work for you. The need of dynamic pivot arises when the row values for the column are not static i.e you do not know what all values would be there in the column

Example

CREATE TABLE #tblitems(
  item    VARCHAR(10),
  parameter  VARCHAR(10),
  value    INT
)

INSERT INTO #tblitems
SELECT 'item1', 'param1', 10 UNION ALL
SELECT 'item1', 'param2', 20 UNION ALL
SELECT 'item1', 'param3', 30 UNION ALL
SELECT 'item2', 'param1', 15 UNION ALL
SELECT 'item2', 'param2', 20

-- Creating a list of all distinct row values that would be going to become columns
DECLARE @paramList VARCHAR(MAX)
SET @paramList = STUFF((
                     SELECT DISTINCT ',[' + parameter + ']'
                     FROM #tblitems FOR XML PATH('')
                    )
                 ,1,1,'')
PRINT @paramList
-- OUTPUT : [param1],[param2],[param3]

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT item, ' + @paramList + ' FROM( SELECT * FROM #tblitems )src
PIVOT(SUM(value) FOR parameter IN (' + @paramList + ')) pvt'

EXEC sp_executesql @query
DROP TABLE #tblitems

OUTPUT