SQL interview questions

INTERVIEW(1):
SQL:
1).Find top second salary?
2).How many types joins in sql server?
3).Difference between full and cross join?
4).Explain constraints.(Check constraints)
5).Difference between where clause and having clause?
6).Difference between subquires and correlated queries,
describes it with one example.
7).How can we use "top" keyword in 2005?
T-SQL:
8).Why introducing T-SQL in sql server?
9).Difference between procedures and functions.
10).Explain triggers.
11).Explain indexes.
12).How to avoid duplicate records?

INTERVIEW(2):
1).Identity property.
2).How can we manually insert data for identity property?
3).How to store data in sql server,take one example and describe?
4).Least top two salaries?
5).How many clustered indexes on a particular table?
6).How to connect the sql server to front end application?
7).Heirarity?

INTERVIEW(3):

1).HINT keyword?
2).coalese keyword?
3).How many types of CTEs are in sql server?

INTERVIEW(3):
1).How many types of system databases?
2).What is the purpose of model and msdb?
3).Constraints?
4).What is the purpose of check constraint?
5).Difference between constraints and triggers?
6).Difference between instead and after triggers?
7).What is magic table?
8).What is error handling?
9).User error identity starts with which number?
10).Transactions?
11).try,catch syntaxes?
12).Difference between procedures and functions?
13).Global variables?
14).Types of temporary tables in sql server.Explain all,where they are stored?
15).Difference between tempdb and msdb?
16).Difference between local variables and global variables?
17).What is a job?
18).Difference between jobs and maintenance plans?
19).What is the purpose of @@spid?
20).Difference between varchar and nvarchar?
21).Explain money datatype.
22).In detail about datetime functions.
23).What is the purpose of identity?
24).Difference between save point and commit
25).Give me some system procedures.
26).Cursors in detail.

INTERVIEW(4):

1).Constraints.
2).Procedures
3).Parent child relation.
4).Indexes.
5).How to split records in sql server?
6).Why we use triggers in sql server applications?
7).What the role of clustered index in sql server?
8).Fill factor?(INDEXES)

INTERVIEW(5):

1).Quoted identifier.

2).Set ANSI nulls.

3).Disable triggers.

4).How to restore the db size?

5).Example for cursor.

6).Example for golbal variables.

7).Difference between procedures and function?

8).Sytax for triggers.

9).Add a constraint(pk) to column.

10).Three types of joins.

11).How to create users?

12).Nested procedures?

14).DB size for your project?

15).Backup script,if i give remote path what does?

16).Indexes ?Which index is faster?

INTERVIEW(6):

1.)Update  salray from another table.

2.)delete duplicate records.

3.)difference between stored procedures and functions.

4.)Select 2nd maximum salary and end record.

5.)Tell me which transformations you used?

6.)In destination,what is the difference between fast load and normal load?

7.)Which reports you have developed?

8.)Have you write expressions?

9.)Which type of expressions you written?Can you explain?

10.)In drill down report,setting visible mode what happen?

INTERVIEW(7):

1.Table is associating with view,Can we deleting the table what happen?

2.How many columns can we create in a table?

3.How many indexes can we create for a table?

4.One table having one clustered index,i want to create one more clustered index,its happening or not?

5. How merge statement works?

6.New features in 2005 to 2008?

7.I have created one job,i forgot that job name,sql server agent does not work?How can you see the history of the job without sql server agent?

Some important information:

-->max tables per select statement-256

-->max columns per index-16

-->max indexes per table-250(249+1)/in 2008 (999+1)

-->max columns per table-1024

-->max size of row-8060bytes

-->max size per index-900bytes

INTERVIEW(8):

1).What is truncate?

2).Can you rollback on truncate?

3).What are instead of triggers?

4).What is CTE?

5).Can i create view on ordinary clause in select?

6).What is schema binding?

7).What is the order of dropping indexes?

8).One table having 100 records,how to drop my table?

9).What is ndf?

10).When we creating db,what files are created?

11).Types of backups?

12).Can i recover my data?What is recovery models?

13).What is fill factor?Is '0' and '100' are equal or not?

14).What is bulk insert?

15).What is execution plan?

16).What is statistical plan?

17).How to do performance tuning?

18).What is better in table scan and index scan?

19).What is temporary tables?Scope of temporary tables?

20).What is isolation levels?what is highest isolation level?

21).What is dirty read?

22).What is with nolock?when with nolock is used what is isolation level?

23).What is tuning advisor?

24).How to get last identity value?

25).Tell me some tasks in SSIS?What they do?

26).How to execute package in different ways?

27).What is cross apply?

28).What is hash join and merge join?

29).Difference between  CTE and temp tables?

30).Difference between left outerjoin and outer apply?

31).How to frop indexes in sql server 2005?

32).What is fill factor ?Difference between '0' and '100'?

33).How to create @tables and #tables?

34).What is actual and estimated execution plans?

35).What is DTA(database tuning advisor)

INTERVIEW(9):

1).What is unique key?

2).Difference between primary key and unique key.

3).What is referential integrity?

4).Difference between truncate and delete?

5).What is transaction?ACID properties?LOCKS?

6).What is execlusive lock?On exclusive lock deadlock can occur/not?

7).Default isolation level?What is it does?

8).Temporary tables?Can we create temporary tables in functions?

9).What is cube and rollup?

10).Exception handling?

11).What is B-Tree structure does?

12).Sql server 2008 features?

13).WHat type of reports you have done?

14).Matrix reports?

15).Subscriptions

16).On grouping columns i can toggled and visibility mode?What happen?

17).In report parameters,can parameter take null values or not ?

18).Page breaks

SQL Server 2005 Encryption types

Encryption is the key for data security. Sensitive data such as Social Security numbers, credit card numbers, passwords, etc. should be protected from hacking.

In SQL Server 2000, you have to create your own user-defined functions to encrypt the data or use external DLLs to encrypt the data. In SQL Server 2005, these functions and methods are available by default.

SQL Server 2005 provides the following mechanism of encryption in order to encrypt the data.

• ENCRYPTION by passphrase
• ENCRYPTION by symmetric keys
• ENCRYPTION by Asymmetric keys
• ENCRYPTION by certificates

In part one of this series, I demonstrate how to use ENCRYPTION by password mechanism to encrypt the data and how to decrypt it.

SQL Server 2005 provides two functions regarding encryption: one for Encrypting and another for decrypting.

“ENCRYPTION by passphrase” is basically encrypting the data using a password. The data can be decrypted using the same password.

Let us try to encrypt the data and decrypt it using the ENCRYPTION by passphrase mechanism.

select EncryptedData = EncryptByPassPhrase('MAK', '123456789' )

Result

EncryptedData
0x0100000000214F5A73054F3AB954DD23571154019F3EFC031ABFCCD258FD22ED69A48002

Now let us execute the above Encryptbypassphrase function three times as shown below.

declare @count int
declare @SocialSecurityNumber varchar(500)
declare @password varchar(12)
set @count =1
while @count<=3
begin
set @SocialSecurityNumber = '123456789'
set @Password = 'MAK'
select EncryptedData = EncryptByPassPhrase
        (@password, @SocialSecurityNumber )
set @count=@count+1
end

Result

EncryptedData
0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7
        CD598DC56364D106B05D47B930093

(1 row(s) affected)

EncryptedData
0x010000005E884D30C8FF7E4723D4E70A03B0B07F877
        667BAF1DA9BE1E116434842D11B99

(1 row(s) affected)

EncryptedData
0x01000000C508FB0C4FC7734B47B414D2602A71A33841
        7DD685229173684D319334A084CD

Note:Here “123456789” is the simulated data of a social security number and “MAK” is the password.

The result of the Encryptbypassphrase is different every time you execute the Encryptbypassphrase function. However, when you decrypt the data it would decrypt perfectly.

Now let us try to decrypt the above-encrypted data using the DecryptByPassPhrase function.

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

123456789

(1 row(s) affected)


123456789

(1 row(s) affected)


123456789

(1 row(s) affected)

Now let us try to decrypt the encrypted data using a different password. Execute the following command.

select convert(varchar(100),DecryptByPassPhrase('test'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

NULL

(1 row(s) affected)

As you can see, SQL Server generates NULL as the result when the password is wrong.

Now let’s create a table with a few rows of credit card numbers and social security number and then encrypt the data permanently with a passphrase.

USE [master]
GO
/****** Object:  Database [admin]    Script Date: 11/25/2007 10:50:47 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Customer DB')
DROP DATABASE [Customer DB]
go

create database [Customer DB]
go
use [Customer DB]
go

create table [Customer data]
([customer id] int,
[Credit Card Number] bigint,
[Social Security Number] bigint)
go

insert into  [Customer data] values (1, 1234567812345678, 123451234)
insert into  [Customer data] values (2, 1234567812345378, 323451234)
insert into  [Customer data] values (3, 1234567812335678, 133451234)
insert into  [Customer data] values (4, 1234567813345678, 123351234)
insert into  [Customer data] values (5, 1234563812345678, 123431234)
go

Now let us create two columns to hold the encrypted data.

use [Customer DB]
go
alter table [Customer Data] add 
[Encrypted Credit Card Number] varbinary(MAX)
go
alter table [Customer Data] add 
[Encrypted Social Security Number] varbinary(MAX)
go

Let’s update the two columns with the encrypted data as shown below.

use [Customer DB]
go
update [Customer Data] set [Encrypted Credit Card Number] =
EncryptByPassPhrase('Credit Card', convert(varchar(100),[Credit Card Number]) )
go
update [Customer Data] set [Encrypted Social Security Number] =
EncryptByPassPhrase('Social Security', convert(varchar(100),[Social Security Number]) )
Go

Query the table as shown below. [Refer Fig 1.0]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.0

Let’s remove the columns that have clear text data.

use [Customer DB]
go
alter table [Customer Data] drop column [Credit Card Number]
go
alter table [Customer Data] drop column [Social Security Number] 
go

Query the table as shown below. [Refer Fig 1.2]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.2

Let’s decrypt the data on the table using the decryptbypassphrase function as shown below. [Refer Fig 1.3]

use [Customer DB]
go
select 
[customer id],
convert(bigint,convert(varchar(100),decryptbypassphrase('Credit Card',[Encrypted Credit Card Number]) )) as
[Credit Card Number],
convert(bigint,convert(varchar(100),decryptbypassphrase('Social Security',[Encrypted Social Security Number] ) )) as
[Social Security Number] from [customer data]
Go

Result

customer id,Credit Card Number,Social Security Number
1, 1234567812345678, 123451234
2, 1234567812345378, 323451234
3, 1234567812335678, 133451234
4, 1234567813345678, 123351234
5, 1234563812345678, 123431234

 

SQL Server 2005 Encryption types

Encryption is the key for data security. Sensitive data such as Social Security numbers, credit card numbers, passwords, etc. should be protected from hacking.

In SQL Server 2000, you have to create your own user-defined functions to encrypt the data or use external DLLs to encrypt the data. In SQL Server 2005, these functions and methods are available by default.

SQL Server 2005 provides the following mechanism of encryption in order to encrypt the data.

• ENCRYPTION by passphrase
• ENCRYPTION by symmetric keys
• ENCRYPTION by Asymmetric keys
• ENCRYPTION by certificates

In part one of this series, I demonstrate how to use ENCRYPTION by password mechanism to encrypt the data and how to decrypt it.

SQL Server 2005 provides two functions regarding encryption: one for Encrypting and another for decrypting.

“ENCRYPTION by passphrase” is basically encrypting the data using a password. The data can be decrypted using the same password.

Let us try to encrypt the data and decrypt it using the ENCRYPTION by passphrase mechanism.

select EncryptedData = EncryptByPassPhrase('MAK', '123456789' )

Result

EncryptedData
0x0100000000214F5A73054F3AB954DD23571154019F3EFC031ABFCCD258FD22ED69A48002

Now let us execute the above Encryptbypassphrase function three times as shown below.

declare @count int
declare @SocialSecurityNumber varchar(500)
declare @password varchar(12)
set @count =1
while @count<=3
begin
set @SocialSecurityNumber = '123456789'
set @Password = 'MAK'
select EncryptedData = EncryptByPassPhrase
        (@password, @SocialSecurityNumber )
set @count=@count+1
end

Result

EncryptedData
0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7
        CD598DC56364D106B05D47B930093

(1 row(s) affected)

EncryptedData
0x010000005E884D30C8FF7E4723D4E70A03B0B07F877
        667BAF1DA9BE1E116434842D11B99

(1 row(s) affected)

EncryptedData
0x01000000C508FB0C4FC7734B47B414D2602A71A33841
        7DD685229173684D319334A084CD

Note:Here “123456789” is the simulated data of a social security number and “MAK” is the password.

The result of the Encryptbypassphrase is different every time you execute the Encryptbypassphrase function. However, when you decrypt the data it would decrypt perfectly.

Now let us try to decrypt the above-encrypted data using the DecryptByPassPhrase function.

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

123456789

(1 row(s) affected)


123456789

(1 row(s) affected)


123456789

(1 row(s) affected)

Now let us try to decrypt the encrypted data using a different password. Execute the following command.

select convert(varchar(100),DecryptByPassPhrase('test'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

NULL

(1 row(s) affected)

As you can see, SQL Server generates NULL as the result when the password is wrong.

Now let’s create a table with a few rows of credit card numbers and social security number and then encrypt the data permanently with a passphrase.

USE [master]
GO
/****** Object:  Database [admin]    Script Date: 11/25/2007 10:50:47 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Customer DB')
DROP DATABASE [Customer DB]
go

create database [Customer DB]
go
use [Customer DB]
go

create table [Customer data]
([customer id] int,
[Credit Card Number] bigint,
[Social Security Number] bigint)
go

insert into  [Customer data] values (1, 1234567812345678, 123451234)
insert into  [Customer data] values (2, 1234567812345378, 323451234)
insert into  [Customer data] values (3, 1234567812335678, 133451234)
insert into  [Customer data] values (4, 1234567813345678, 123351234)
insert into  [Customer data] values (5, 1234563812345678, 123431234)
go

Now let us create two columns to hold the encrypted data.

use [Customer DB]
go
alter table [Customer Data] add 
[Encrypted Credit Card Number] varbinary(MAX)
go
alter table [Customer Data] add 
[Encrypted Social Security Number] varbinary(MAX)
go

Let’s update the two columns with the encrypted data as shown below.

use [Customer DB]
go
update [Customer Data] set [Encrypted Credit Card Number] =
EncryptByPassPhrase('Credit Card', convert(varchar(100),[Credit Card Number]) )
go
update [Customer Data] set [Encrypted Social Security Number] =
EncryptByPassPhrase('Social Security', convert(varchar(100),[Social Security Number]) )
Go

Query the table as shown below. [Refer Fig 1.0]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.0

Let’s remove the columns that have clear text data.

use [Customer DB]
go
alter table [Customer Data] drop column [Credit Card Number]
go
alter table [Customer Data] drop column [Social Security Number] 
go

Query the table as shown below. [Refer Fig 1.2]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.2

Let’s decrypt the data on the table using the decryptbypassphrase function as shown below. [Refer Fig 1.3]

use [Customer DB]
go
select 
[customer id],
convert(bigint,convert(varchar(100),decryptbypassphrase('Credit Card',[Encrypted Credit Card Number]) )) as
[Credit Card Number],
convert(bigint,convert(varchar(100),decryptbypassphrase('Social Security',[Encrypted Social Security Number] ) )) as
[Social Security Number] from [customer data]
Go

Result

customer id,Credit Card Number,Social Security Number
1, 1234567812345678, 123451234
2, 1234567812345378, 323451234
3, 1234567812335678, 133451234
4, 1234567813345678, 123351234
5, 1234563812345678, 123431234

 

SQL Server 2005 Encryption types

Encryption is the key for data security. Sensitive data such as Social Security numbers, credit card numbers, passwords, etc. should be protected from hacking.

In SQL Server 2000, you have to create your own user-defined functions to encrypt the data or use external DLLs to encrypt the data. In SQL Server 2005, these functions and methods are available by default.

SQL Server 2005 provides the following mechanism of encryption in order to encrypt the data.

• ENCRYPTION by passphrase
• ENCRYPTION by symmetric keys
• ENCRYPTION by Asymmetric keys
• ENCRYPTION by certificates

In part one of this series, I demonstrate how to use ENCRYPTION by password mechanism to encrypt the data and how to decrypt it.

SQL Server 2005 provides two functions regarding encryption: one for Encrypting and another for decrypting.

“ENCRYPTION by passphrase” is basically encrypting the data using a password. The data can be decrypted using the same password.

Let us try to encrypt the data and decrypt it using the ENCRYPTION by passphrase mechanism.

select EncryptedData = EncryptByPassPhrase('MAK', '123456789' )

Result

EncryptedData
0x0100000000214F5A73054F3AB954DD23571154019F3EFC031ABFCCD258FD22ED69A48002

Now let us execute the above Encryptbypassphrase function three times as shown below.

declare @count int
declare @SocialSecurityNumber varchar(500)
declare @password varchar(12)
set @count =1
while @count<=3
begin
set @SocialSecurityNumber = '123456789'
set @Password = 'MAK'
select EncryptedData = EncryptByPassPhrase
        (@password, @SocialSecurityNumber )
set @count=@count+1
end

Result

EncryptedData
0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7
        CD598DC56364D106B05D47B930093

(1 row(s) affected)

EncryptedData
0x010000005E884D30C8FF7E4723D4E70A03B0B07F877
        667BAF1DA9BE1E116434842D11B99

(1 row(s) affected)

EncryptedData
0x01000000C508FB0C4FC7734B47B414D2602A71A33841
        7DD685229173684D319334A084CD

Note:Here “123456789” is the simulated data of a social security number and “MAK” is the password.

The result of the Encryptbypassphrase is different every time you execute the Encryptbypassphrase function. However, when you decrypt the data it would decrypt perfectly.

Now let us try to decrypt the above-encrypted data using the DecryptByPassPhrase function.

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

123456789

(1 row(s) affected)


123456789

(1 row(s) affected)


123456789

(1 row(s) affected)

Now let us try to decrypt the encrypted data using a different password. Execute the following command.

select convert(varchar(100),DecryptByPassPhrase('test'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

NULL

(1 row(s) affected)

As you can see, SQL Server generates NULL as the result when the password is wrong.

Now let’s create a table with a few rows of credit card numbers and social security number and then encrypt the data permanently with a passphrase.

USE [master]
GO
/****** Object:  Database [admin]    Script Date: 11/25/2007 10:50:47 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Customer DB')
DROP DATABASE [Customer DB]
go

create database [Customer DB]
go
use [Customer DB]
go

create table [Customer data]
([customer id] int,
[Credit Card Number] bigint,
[Social Security Number] bigint)
go

insert into  [Customer data] values (1, 1234567812345678, 123451234)
insert into  [Customer data] values (2, 1234567812345378, 323451234)
insert into  [Customer data] values (3, 1234567812335678, 133451234)
insert into  [Customer data] values (4, 1234567813345678, 123351234)
insert into  [Customer data] values (5, 1234563812345678, 123431234)
go

Now let us create two columns to hold the encrypted data.

use [Customer DB]
go
alter table [Customer Data] add 
[Encrypted Credit Card Number] varbinary(MAX)
go
alter table [Customer Data] add 
[Encrypted Social Security Number] varbinary(MAX)
go

Let’s update the two columns with the encrypted data as shown below.

use [Customer DB]
go
update [Customer Data] set [Encrypted Credit Card Number] =
EncryptByPassPhrase('Credit Card', convert(varchar(100),[Credit Card Number]) )
go
update [Customer Data] set [Encrypted Social Security Number] =
EncryptByPassPhrase('Social Security', convert(varchar(100),[Social Security Number]) )
Go

Query the table as shown below. [Refer Fig 1.0]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.0

Let’s remove the columns that have clear text data.

use [Customer DB]
go
alter table [Customer Data] drop column [Credit Card Number]
go
alter table [Customer Data] drop column [Social Security Number] 
go

Query the table as shown below. [Refer Fig 1.2]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.2

Let’s decrypt the data on the table using the decryptbypassphrase function as shown below. [Refer Fig 1.3]

use [Customer DB]
go
select 
[customer id],
convert(bigint,convert(varchar(100),decryptbypassphrase('Credit Card',[Encrypted Credit Card Number]) )) as
[Credit Card Number],
convert(bigint,convert(varchar(100),decryptbypassphrase('Social Security',[Encrypted Social Security Number] ) )) as
[Social Security Number] from [customer data]
Go

Result

customer id,Credit Card Number,Social Security Number
1, 1234567812345678, 123451234
2, 1234567812345378, 323451234
3, 1234567812335678, 133451234
4, 1234567813345678, 123351234
5, 1234563812345678, 123431234

 

SQL Server 2005 Encryption types

Encryption is the key for data security. Sensitive data such as Social Security numbers, credit card numbers, passwords, etc. should be protected from hacking.

In SQL Server 2000, you have to create your own user-defined functions to encrypt the data or use external DLLs to encrypt the data. In SQL Server 2005, these functions and methods are available by default.

SQL Server 2005 provides the following mechanism of encryption in order to encrypt the data.

• ENCRYPTION by passphrase
• ENCRYPTION by symmetric keys
• ENCRYPTION by Asymmetric keys
• ENCRYPTION by certificates

In part one of this series, I demonstrate how to use ENCRYPTION by password mechanism to encrypt the data and how to decrypt it.

SQL Server 2005 provides two functions regarding encryption: one for Encrypting and another for decrypting.

“ENCRYPTION by passphrase” is basically encrypting the data using a password. The data can be decrypted using the same password.

Let us try to encrypt the data and decrypt it using the ENCRYPTION by passphrase mechanism.

select EncryptedData = EncryptByPassPhrase('MAK', '123456789' )

Result

EncryptedData
0x0100000000214F5A73054F3AB954DD23571154019F3EFC031ABFCCD258FD22ED69A48002

Now let us execute the above Encryptbypassphrase function three times as shown below.

declare @count int
declare @SocialSecurityNumber varchar(500)
declare @password varchar(12)
set @count =1
while @count<=3
begin
set @SocialSecurityNumber = '123456789'
set @Password = 'MAK'
select EncryptedData = EncryptByPassPhrase
        (@password, @SocialSecurityNumber )
set @count=@count+1
end

Result

EncryptedData
0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7
        CD598DC56364D106B05D47B930093

(1 row(s) affected)

EncryptedData
0x010000005E884D30C8FF7E4723D4E70A03B0B07F877
        667BAF1DA9BE1E116434842D11B99

(1 row(s) affected)

EncryptedData
0x01000000C508FB0C4FC7734B47B414D2602A71A33841
        7DD685229173684D319334A084CD

Note:Here “123456789” is the simulated data of a social security number and “MAK” is the password.

The result of the Encryptbypassphrase is different every time you execute the Encryptbypassphrase function. However, when you decrypt the data it would decrypt perfectly.

Now let us try to decrypt the above-encrypted data using the DecryptByPassPhrase function.

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

123456789

(1 row(s) affected)


123456789

(1 row(s) affected)


123456789

(1 row(s) affected)

Now let us try to decrypt the encrypted data using a different password. Execute the following command.

select convert(varchar(100),DecryptByPassPhrase('test'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

NULL

(1 row(s) affected)

As you can see, SQL Server generates NULL as the result when the password is wrong.

Now let’s create a table with a few rows of credit card numbers and social security number and then encrypt the data permanently with a passphrase.

USE [master]
GO
/****** Object:  Database [admin]    Script Date: 11/25/2007 10:50:47 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Customer DB')
DROP DATABASE [Customer DB]
go

create database [Customer DB]
go
use [Customer DB]
go

create table [Customer data]
([customer id] int,
[Credit Card Number] bigint,
[Social Security Number] bigint)
go

insert into  [Customer data] values (1, 1234567812345678, 123451234)
insert into  [Customer data] values (2, 1234567812345378, 323451234)
insert into  [Customer data] values (3, 1234567812335678, 133451234)
insert into  [Customer data] values (4, 1234567813345678, 123351234)
insert into  [Customer data] values (5, 1234563812345678, 123431234)
go

Now let us create two columns to hold the encrypted data.

use [Customer DB]
go
alter table [Customer Data] add 
[Encrypted Credit Card Number] varbinary(MAX)
go
alter table [Customer Data] add 
[Encrypted Social Security Number] varbinary(MAX)
go

Let’s update the two columns with the encrypted data as shown below.

use [Customer DB]
go
update [Customer Data] set [Encrypted Credit Card Number] =
EncryptByPassPhrase('Credit Card', convert(varchar(100),[Credit Card Number]) )
go
update [Customer Data] set [Encrypted Social Security Number] =
EncryptByPassPhrase('Social Security', convert(varchar(100),[Social Security Number]) )
Go

Query the table as shown below. [Refer Fig 1.0]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.0

Let’s remove the columns that have clear text data.

use [Customer DB]
go
alter table [Customer Data] drop column [Credit Card Number]
go
alter table [Customer Data] drop column [Social Security Number] 
go

Query the table as shown below. [Refer Fig 1.2]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.2

Let’s decrypt the data on the table using the decryptbypassphrase function as shown below. [Refer Fig 1.3]

use [Customer DB]
go
select 
[customer id],
convert(bigint,convert(varchar(100),decryptbypassphrase('Credit Card',[Encrypted Credit Card Number]) )) as
[Credit Card Number],
convert(bigint,convert(varchar(100),decryptbypassphrase('Social Security',[Encrypted Social Security Number] ) )) as
[Social Security Number] from [customer data]
Go

Result

customer id,Credit Card Number,Social Security Number
1, 1234567812345678, 123451234
2, 1234567812345378, 323451234
3, 1234567812335678, 133451234
4, 1234567813345678, 123351234
5, 1234563812345678, 123431234

 

SQL Server 2005 Encryption types

Encryption is the key for data security. Sensitive data such as Social Security numbers, credit card numbers, passwords, etc. should be protected from hacking.

In SQL Server 2000, you have to create your own user-defined functions to encrypt the data or use external DLLs to encrypt the data. In SQL Server 2005, these functions and methods are available by default.

SQL Server 2005 provides the following mechanism of encryption in order to encrypt the data.

• ENCRYPTION by passphrase
• ENCRYPTION by symmetric keys
• ENCRYPTION by Asymmetric keys
• ENCRYPTION by certificates

In part one of this series, I demonstrate how to use ENCRYPTION by password mechanism to encrypt the data and how to decrypt it.

SQL Server 2005 provides two functions regarding encryption: one for Encrypting and another for decrypting.

“ENCRYPTION by passphrase” is basically encrypting the data using a password. The data can be decrypted using the same password.

Let us try to encrypt the data and decrypt it using the ENCRYPTION by passphrase mechanism.

select EncryptedData = EncryptByPassPhrase('MAK', '123456789' )

Result

EncryptedData
0x0100000000214F5A73054F3AB954DD23571154019F3EFC031ABFCCD258FD22ED69A48002

Now let us execute the above Encryptbypassphrase function three times as shown below.

declare @count int
declare @SocialSecurityNumber varchar(500)
declare @password varchar(12)
set @count =1
while @count<=3
begin
set @SocialSecurityNumber = '123456789'
set @Password = 'MAK'
select EncryptedData = EncryptByPassPhrase
        (@password, @SocialSecurityNumber )
set @count=@count+1
end

Result

EncryptedData
0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7
        CD598DC56364D106B05D47B930093

(1 row(s) affected)

EncryptedData
0x010000005E884D30C8FF7E4723D4E70A03B0B07F877
        667BAF1DA9BE1E116434842D11B99

(1 row(s) affected)

EncryptedData
0x01000000C508FB0C4FC7734B47B414D2602A71A33841
        7DD685229173684D319334A084CD

Note:Here “123456789” is the simulated data of a social security number and “MAK” is the password.

The result of the Encryptbypassphrase is different every time you execute the Encryptbypassphrase function. However, when you decrypt the data it would decrypt perfectly.

Now let us try to decrypt the above-encrypted data using the DecryptByPassPhrase function.

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

123456789

(1 row(s) affected)


123456789

(1 row(s) affected)


123456789

(1 row(s) affected)

Now let us try to decrypt the encrypted data using a different password. Execute the following command.

select convert(varchar(100),DecryptByPassPhrase('test'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

NULL

(1 row(s) affected)

As you can see, SQL Server generates NULL as the result when the password is wrong.

Now let’s create a table with a few rows of credit card numbers and social security number and then encrypt the data permanently with a passphrase.

USE [master]
GO
/****** Object:  Database [admin]    Script Date: 11/25/2007 10:50:47 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Customer DB')
DROP DATABASE [Customer DB]
go

create database [Customer DB]
go
use [Customer DB]
go

create table [Customer data]
([customer id] int,
[Credit Card Number] bigint,
[Social Security Number] bigint)
go

insert into  [Customer data] values (1, 1234567812345678, 123451234)
insert into  [Customer data] values (2, 1234567812345378, 323451234)
insert into  [Customer data] values (3, 1234567812335678, 133451234)
insert into  [Customer data] values (4, 1234567813345678, 123351234)
insert into  [Customer data] values (5, 1234563812345678, 123431234)
go

Now let us create two columns to hold the encrypted data.

use [Customer DB]
go
alter table [Customer Data] add 
[Encrypted Credit Card Number] varbinary(MAX)
go
alter table [Customer Data] add 
[Encrypted Social Security Number] varbinary(MAX)
go

Let’s update the two columns with the encrypted data as shown below.

use [Customer DB]
go
update [Customer Data] set [Encrypted Credit Card Number] =
EncryptByPassPhrase('Credit Card', convert(varchar(100),[Credit Card Number]) )
go
update [Customer Data] set [Encrypted Social Security Number] =
EncryptByPassPhrase('Social Security', convert(varchar(100),[Social Security Number]) )
Go

Query the table as shown below. [Refer Fig 1.0]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.0

Let’s remove the columns that have clear text data.

use [Customer DB]
go
alter table [Customer Data] drop column [Credit Card Number]
go
alter table [Customer Data] drop column [Social Security Number] 
go

Query the table as shown below. [Refer Fig 1.2]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.2

Let’s decrypt the data on the table using the decryptbypassphrase function as shown below. [Refer Fig 1.3]

use [Customer DB]
go
select 
[customer id],
convert(bigint,convert(varchar(100),decryptbypassphrase('Credit Card',[Encrypted Credit Card Number]) )) as
[Credit Card Number],
convert(bigint,convert(varchar(100),decryptbypassphrase('Social Security',[Encrypted Social Security Number] ) )) as
[Social Security Number] from [customer data]
Go

Result

customer id,Credit Card Number,Social Security Number
1, 1234567812345678, 123451234
2, 1234567812345378, 323451234
3, 1234567812335678, 133451234
4, 1234567813345678, 123351234
5, 1234563812345678, 123431234

 

SQL Server 2005 Encryption types

Encryption is the key for data security. Sensitive data such as Social Security numbers, credit card numbers, passwords, etc. should be protected from hacking.

In SQL Server 2000, you have to create your own user-defined functions to encrypt the data or use external DLLs to encrypt the data. In SQL Server 2005, these functions and methods are available by default.

SQL Server 2005 provides the following mechanism of encryption in order to encrypt the data.

• ENCRYPTION by passphrase
• ENCRYPTION by symmetric keys
• ENCRYPTION by Asymmetric keys
• ENCRYPTION by certificates

In part one of this series, I demonstrate how to use ENCRYPTION by password mechanism to encrypt the data and how to decrypt it.

SQL Server 2005 provides two functions regarding encryption: one for Encrypting and another for decrypting.

“ENCRYPTION by passphrase” is basically encrypting the data using a password. The data can be decrypted using the same password.

Let us try to encrypt the data and decrypt it using the ENCRYPTION by passphrase mechanism.

select EncryptedData = EncryptByPassPhrase('MAK', '123456789' )

Result

EncryptedData
0x0100000000214F5A73054F3AB954DD23571154019F3EFC031ABFCCD258FD22ED69A48002

Now let us execute the above Encryptbypassphrase function three times as shown below.

declare @count int
declare @SocialSecurityNumber varchar(500)
declare @password varchar(12)
set @count =1
while @count<=3
begin
set @SocialSecurityNumber = '123456789'
set @Password = 'MAK'
select EncryptedData = EncryptByPassPhrase
        (@password, @SocialSecurityNumber )
set @count=@count+1
end

Result

EncryptedData
0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7
        CD598DC56364D106B05D47B930093

(1 row(s) affected)

EncryptedData
0x010000005E884D30C8FF7E4723D4E70A03B0B07F877
        667BAF1DA9BE1E116434842D11B99

(1 row(s) affected)

EncryptedData
0x01000000C508FB0C4FC7734B47B414D2602A71A33841
        7DD685229173684D319334A084CD

Note:Here “123456789” is the simulated data of a social security number and “MAK” is the password.

The result of the Encryptbypassphrase is different every time you execute the Encryptbypassphrase function. However, when you decrypt the data it would decrypt perfectly.

Now let us try to decrypt the above-encrypted data using the DecryptByPassPhrase function.

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000CBB7EE45B5C1460D6996B149CE16B76C7F7CD598DC56364D106B05D47B930093))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x010000005E884D30C8FF7E4723D4E70A03B0B07F877667BAF1DA9BE1E116434842D11B99))

select convert(varchar(100),DecryptByPassPhrase('MAK'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

123456789

(1 row(s) affected)


123456789

(1 row(s) affected)


123456789

(1 row(s) affected)

Now let us try to decrypt the encrypted data using a different password. Execute the following command.

select convert(varchar(100),DecryptByPassPhrase('test'
    , 0x01000000C508FB0C4FC7734B47B414D2602A71A338417DD685229173684D319334A084CD))

Result

NULL

(1 row(s) affected)

As you can see, SQL Server generates NULL as the result when the password is wrong.

Now let’s create a table with a few rows of credit card numbers and social security number and then encrypt the data permanently with a passphrase.

USE [master]
GO
/****** Object:  Database [admin]    Script Date: 11/25/2007 10:50:47 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Customer DB')
DROP DATABASE [Customer DB]
go

create database [Customer DB]
go
use [Customer DB]
go

create table [Customer data]
([customer id] int,
[Credit Card Number] bigint,
[Social Security Number] bigint)
go

insert into  [Customer data] values (1, 1234567812345678, 123451234)
insert into  [Customer data] values (2, 1234567812345378, 323451234)
insert into  [Customer data] values (3, 1234567812335678, 133451234)
insert into  [Customer data] values (4, 1234567813345678, 123351234)
insert into  [Customer data] values (5, 1234563812345678, 123431234)
go

Now let us create two columns to hold the encrypted data.

use [Customer DB]
go
alter table [Customer Data] add 
[Encrypted Credit Card Number] varbinary(MAX)
go
alter table [Customer Data] add 
[Encrypted Social Security Number] varbinary(MAX)
go

Let’s update the two columns with the encrypted data as shown below.

use [Customer DB]
go
update [Customer Data] set [Encrypted Credit Card Number] =
EncryptByPassPhrase('Credit Card', convert(varchar(100),[Credit Card Number]) )
go
update [Customer Data] set [Encrypted Social Security Number] =
EncryptByPassPhrase('Social Security', convert(varchar(100),[Social Security Number]) )
Go

Query the table as shown below. [Refer Fig 1.0]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.0

Let’s remove the columns that have clear text data.

use [Customer DB]
go
alter table [Customer Data] drop column [Credit Card Number]
go
alter table [Customer Data] drop column [Social Security Number] 
go

Query the table as shown below. [Refer Fig 1.2]

use [Customer DB]
go
select * from [customer data]
go

Result

 
Fig 1.2

Let’s decrypt the data on the table using the decryptbypassphrase function as shown below. [Refer Fig 1.3]

use [Customer DB]
go
select 
[customer id],
convert(bigint,convert(varchar(100),decryptbypassphrase('Credit Card',[Encrypted Credit Card Number]) )) as
[Credit Card Number],
convert(bigint,convert(varchar(100),decryptbypassphrase('Social Security',[Encrypted Social Security Number] ) )) as
[Social Security Number] from [customer data]
Go

Result

customer id,Credit Card Number,Social Security Number
1, 1234567812345678, 123451234
2, 1234567812345378, 323451234
3, 1234567812335678, 133451234
4, 1234567813345678, 123351234
5, 1234563812345678, 123431234