Design Database Objects: Design tables

(This is part of the Study Guide series, 70-464)

Microsoft’s Measured Skill description: This objective may include but is not limited to: data design patterns; develop normalized and de-normalized SQL tables; understand the difference between physical tables, temp tables, temp table variables, and common table expressions; design transactions; design views; understand advantages and disadvantages of using a GUID as a clustered index; understand performance implications of # vs. @ temp tables and how to decide which to use, when, and why; use of set-based vs. row-based logic; encryption (other than TDE); table partitioning; filestream and filetable

What I see:

·         data normalization

·         temp tables vs. table variables

·         GUID as a clustered index

·         encryption

·         filestream

·         filetables

Data Normalization

                Wikipedia actually has a great definition on what Database Normalization is.  I’d recommend skimming that article, as well as the descriptions of the different normal forms.  The big takeaway is that normalization minimizes data redundancy.  There are many benefits to this:  Store data once in one spot, data consistency as the INSERT/UPDATE/DELETE statements need to hit the fewest amount of sources, and performance benefits.

Encryption

                Encryption is a great and widely-used form of security.  The core to encryption in SQL Server is the service master key (created upon installation) and the database master key.  There can only be one database master key per database, and prior to using some forms of encryption this needs to be created (note:  it is best practice to backup your database master key upon creation and store it in a secure place):

use AdventureWorks2012;

go

create master key

encryption by password = 'm@$t3rk3yp@$$w0rd';

go

There are a few ways to do column-level encryption.  The first one is by using a certification.  Below is an example of how to use a certificate to encrypt data, and likewise to decrypt the same data:

use AdventureWorks2012;

go

create master key

encryption by password = 'm@$t3rk3yp@$$w0rd';

go

create certificate CertSales

with subject = 'Sales Encryption Cert';

go

select

       CreditCardID,

       CardType,

       encryptbycert(cert_id('CertSales'), CardNumber) as CardNumber,

       ExpMonth,

       ExpYear,

       ModifiedDate

into Sales.CreditCardEncCert

from Sales.CreditCard;

select

       cast(decryptbycert(cert_id('CertSales'), CardNumber) as nvarchar(25)),

       *

from sales.CreditCardEncCert;

A symmetric key can also be used to encrypt data (and often the most used choice).  A symmetric key can be encrypted by using a certificate, symmetric key, asymmetric key, password, or PROVIDER.  The below example uses the above certificate to encrypt the symmetric key, and then encrypts the same data with the new symmetric key:

create symmetric key SymSales

with algorithm = aes_256

encryption by certificate CertSales;

go

if exists (select * from sys.tables where name = 'CreditCardEncSym')

       drop table Sales.CreditCardEncSym;

go

open symmetric key SymSales

decryption by certificate CertSales;

select

       CreditCardID,

       CardType,

       encryptbykey(key_guid('SymSales'), CardNumber) as CardNumber,

       ExpMonth,

       ExpYear,

       ModifiedDate

into Sales.CreditCardEncSym

from Sales.CreditCard;

select

       cast(decryptbykey(CardNumber) as nvarchar(25)) as CardNumber,

       *

from Sales.CreditCardEncSym;

close symmetric key SymSales;

go

The above are just a couple of examples of how to work with data encryption.  See BOL to explore more possibilities.

Filestream

                Filestream is a SQL Server method of storing BLOBs in the NTFS file system.  You can then use SQL to perform all DML statements against the filestream data.  Below shows an example of how use filestream.  It first needs to be enabled at the service level, as well as the instance level.  Then it needs to be set in the database through a filestream filegroup:

exec sp_configure 'filestream access level', 2;

go

reconfigure;

go

alter database TestDB

add filegroup FileStreamFG

contains filestream;

go

alter database TestDB

add file

(

       name = FileStreamFile,

       filename = 'C:\SqlServer\Testing\FileStream\TestDB'

)

to filegroup FileStreamFG;

go

alter database TestDB

remove file FileStreamFile;

go

use TestDB;

go

create table PhotoTable

(

       row_id uniqueidentifier rowguidcol not null unique,

       Photo varbinary(max) filestream not null

)-- on FileStreamFG;

go

declare @photo varbinary(max);

select @photo = cast(bulkcolumn as varbinary(max))

from openrowset

(

       bulk 'C:\SqlServer\SQLSalt\Blog\BlogDesign\BlogIcon.png',

       single_blob

) x

insert into PhotoTable

(

       row_id,

       Photo

)

values

(

       newid(),

       @photo

);

select *

from PhotoTable;

FileTables

                FileTables are one of the really cool new features of SQL Server 2012.  The technology sits on top of FILESTREAM, so having that base for the server/instance/database is necessary.  I recommend reading an experimenting with the related BOL articles on FileTables, but below I will show how to create one.  For the database’s FILESTEAM, NON_TRANSACTED_ACCESS does have to be enabled:

alter database MeasureMySkills

set filestream

(

       directory_name = 'MmsFs',

       non_transacted_access = full

);

go

use MeasureMySkills;

go

create table MyFirstFileTable

as filetable

with

(

       filetable_directory = 'MyFirstFileTableDir',

       filetable_collate_filename = database_default

);

go

-- get the filetable's directory

select filetablerootpath('dbo.MyFirstFileTable') as FtDir;

Code

·         Database_FileStream_EnableFileStream.sql

References

·         Wikipedia article on Database Normalization

·         SQL Salt blog post on temp tables vs table variables

·         SQL Server Central (Wayne Sheffield) post on Comparing Table Variables with Temporary Tables

·         SQL Salt blog post on NEWID() vs NEWSEQUENSTIALID()

·         BOL reference on CREATE MASTER KEY

·         BOL reference on CREATE SYMMETRIC KEY

·         BOL reference on ENCRYPTBYKEY

·         BOL reference on DECRYPTBYKEY

·         TechNet overview of FILESTREAM

·         BOL reference on FILESTREAM

·         BOL reference on FileTables

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.