SQL
| Encryption | SQL Performance |
|---|---|
| Column Level | Loop vs Set-Based |
This page will appeal to politically conservative database professionals.
Available on Amazon:
SQL Server Database Tips
Column Level Encryption
The following script demonstrates how to implement column level encryption:
create table Customer (
CustomerID int IDENTITY(1,1) NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
SSNEncrypt varbinary(256),
SalaryEncrypt varbinary(256)
)
go
alter table Customer add constraint PK_Customer PRIMARY KEY (CustomerID)
go
create master key encryption by password = 'somePassword'
go
create certificate theCert with subject = 'Social Security Number'
go
create symmetric key theKey with algorithm = AES_256 encryption by certificate theCert
go
open symmetric key theKey decryption by certificate theCert
go
insert Customer (FirstName, LastName, SSNEncrypt, SalaryEncrypt)
values ('John', 'Smith', ENCRYPTBYKEY(key_GUID('theKey'), N'123-45-6789'), ENCRYPTBYKEY(key_GUID('theKey'), N'100000.00'))
go
close symmetric key theKey
go
open symmetric key theKey decryption by certificate theCert
go
select *, SSN = convert(nvarchar, DECRYPTBYKEY(SSNEncrypt)), Salary = convert(nvarchar, DECRYPTBYKEY(SalaryEncrypt)) from Customer
go
close symmetric key theKey
go
/*Run the following scripts to view the certificate and symmetric key.*/
select * from sys.certificates
select * from sys.symmetric_keys
/*Clean up
DROP SYMMETRIC KEY theKey
DROP CERTIFICATE theCert
DROP MASTER Key
DROP TABLE Customer
*/
/*
Grant Permissions to the Encrypted Data:
Permissions can be granted to a set of users to decrypt and read data using the commands below.
*/
GRANT VIEW DEFINITION ON SYMMETRIC KEY::theKey TO someRole;
GO
GRANT VIEW DEFINITION ON Certificate::theCert TO someRole;
GO
Prime Numbers
Method 1: Prime numbers are generated with a WHILE Loop.
declare @i int = 1,
@j int,
@Counter int
declare @Primes TABLE (xPrime int)
WHILE @i < 10000
BEGIN
select @j = 1, @Counter = 0
WHILE @j <= @i
BEGIN
IF @i % @j = 0
BEGIN
select @Counter = @Counter + 1
END
IF @Counter > 2
BREAK
ELSE
select @j = @j + 1
END
If @Counter = 2
BEGIN
insert @Primes (xPrime) values (@i)
END
select @i = @i + 1
END
select * from @Primes
Method 2: Prime Numbers are generated with a numbers table. This script performs better than the initial one since the WHILE loop is removed.
--Create the Numbers table
create table dbo.Numbers (xNumber int)
go
--Populate the Numbers table
;WITH cteN(Number) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
FROM sys.all_columns with (nolock)
)
insert dbo.Numbers (xNumber)
select Number
FROM cteN WHERE [Number] <= 10000
go
CREATE UNIQUE CLUSTERED INDEX I_Numbers ON dbo.Numbers([xNumber])
go
--Generate Primes
;with cte (aNumber)
as (select xNumber from dbo.Numbers with (nolock) where xNumber > 0
)
select aNumber, COUNT(*)
from cte
cross join dbo.Numbers n with (nolock)
where n.xNumber > 1
and aNumber % n.xNumber = 0
group by aNumber
having COUNT(*) = 1
order by aNumber
go