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
·         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 uniqueidentifierrowguidcol 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
References
·         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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s