Setting Up Transparent Data Encryption in SQL Server

Introduction

This is geared more toward DBA's than developers, but developers need to know how this works. Transparent Data Encryption or TDE is a feature of Microsoft SQL Server in version 2008 and up that allows you to encrypt the data and log files of your SQL server with no special operations in your transactions at all. If you have used column level encryption with symmetric or asymmetric keys then you know what a blessing this is. Everything happens behind the scenes and your files and backups are safe from data theft with very little work at all on your part.

The What

TDE protects your data at rest at the database level. What this means is your backups and data and log files for the encrypted database as well as tempDB are encrypted and protected in most cases with a symmetric key called a DEK (Database Encryption Key) that is stored in the database and protected with a certificate in the Master database secured with the service master key of the server. Sometimes an asymmetric key stored in an EKM module is used instead of a certificate but we're going to look at symmetric key TDE in this BLOG.

The multi-layer encryption scheme SQL Server uses for protecting data is a complex one to say the least.  Here we're just going to look at how to get it rolling quickly. Below is the diagram of the TDE hierarchy.

TDE is set on a per database basis. There are a few gotcha's that need to be considered when enabling TDE on a database.

• Encrypted data is NOT compressible. Read that carefully! Row and Page level data compression is still possible since this happens before TDE does its thing. However, your nice little compressed backups that have been saving your space on the NAS...you can kiss those goodbye. Make sure you take this into consideration when capacity planning so you don't run out of space.
• Once encrypted, the whole database is inaccessible without the certificate and key. BACK THIS UP IMMEDIATELY and put it somewhere safe! The same protection that keeps data thieves from stealing your data and log files and attaching them or restoring your backups will also prevent you from accessing the data. You have been warned.
• You can't encrypt a read-only database or a database with any read-only file groups.
• Encryption happens in the background once you enable TDE on the database. Until it has finished, most maintenance operations on the database are not allowed. Review the link to the MSDN at the bottom of the article for a complete listing of restrictions on this.
• Filestream data is not encrypted.
• Replication data is unencrypted before publishing. You will have to enable TDE on each member of the replication team individually.
• TDE is available on Enterprise and higher level editions of SQL server, Development and Evaluation editions starting with version 2008.  They have recently added TDE to Azure but on your local servers you're going to pay the price of taking the easy road. 
• There is a bit of a CPU increase when using TDE obviously so if you're flirting with the 85% usage mark on your server it's time to add some cores before stepping into this.

The How

Okay! Let's take a look at what it takes to enable TDE on a database. Just a hint here, the scripts I give you are fill in the blank kind of things that you can save for a quick script to get it done in most cases. You can save these to file or refer back to the blog for a quick select and copy. I know I am always stealing code from some of Peter's and Daniel's blogs to save typing time.

-- Step 1 -- Create a Master Key
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyR3a11yL0ng&c0mpl3xP@$$20rd!'

-- Step 2 -- Create the server certificate
CREATE CERTIFICATE [CERTIFICATENAMEHERE] 
WITH SUBJECT = 'DATABASENAMEHERE TDE'

-- Step 3 -- Create the database encryption key
USE [DATABASEIMENCRYPTING]
GO 
CREATE DATABASE ENCRYPTION KEY 
 WITH ALGORITHM = AES_256 --<--Your choice here, but anything less is uncivilized!
ENCRYPTION BY SERVER CERTIFICATE [CERTIFICATENAMEHERE]
GO 

-- Step 4 -- Backup the key and certificate NOW!!!!
USE [master] 
GO 
-- NOTE! You must use seperate locations for the keys and certs.
BACKUP CERTIFICATE [CERTIFICATENAMEHERE]  
TO FILE = '\\UNC PATH TO MY BACKUP REPOSITORY\certs\[CERTIFICATENAMEHERE].cer' 
WITH PRIVATE KEY (FILE = '\\UNC PATH TO MY BACKUP REPOSITORY\Keys\[CERTIFICATENAMEHERE].pvk' , 
ENCRYPTION BY PASSWORD = 'An0th3rR3a11yL0ng&c0mpl3xP@$$20rd!' ) 
GO 

-- Step 5 -- Enable TDE on the database
ALTER DATABASE [DATABASEIMENCRYPTING]
SET ENCRYPTION ON

-- Step 6 -- Check the progress
USE [master] 
GO
--This will show you the status of the encryption operation going on in the background.
--The database is fully online while this is happening. Neat huh!?
SELECT 
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
 ON db.database_id = dm.database_id;
GO

That was easy enough huh!? But what if I want to move the database to another server? There is a procedure for this as well, I'll throw them all into a handy script block as well but here is the procedure.

Moving an encrypted database

• Create a database master key on the new server. This doesn't have to use the same password.
• Recreate the certificate using the backup you did when encrypting the database. You did back it up? Right??? The password is the same one that you used when backing up the cert originally! 
• Attach the database files from their location or restore the backup, whichever method you are using.
• Test access to the data.
• Here is the handy script for doing these operations and a few more you might need at some point.

-- Drop the database encryption key. REMOVE ENCRYPTION FIRST!!!!
USE [MYENCRYPTEDDATABASE]
GO
DROP DATABASE ENCRYPTION KEY

-- Drop certificate
USE [MASTER]
GO
DROP CERTIFICATE [CERTIFICATENAMEHERE]

-- Drop Master Key
USE [MASTER]
GO
DROP MASTER KEY 

Conclusion

That's it for our short lesson on TDE. I hope this has at the very least given you some clarity on the subject. Maybe it's motivated you to give TDE a shot! I know I love it. Encryption and security is an art form in itself and you never can learn too much about it or do to much to protect your server and your data.

References

TDE MSDN Documentation: https://msdn.microsoft.com/en-us/library/bb934049.aspx

Using the MERGE Statement

Introduction

We are born naked, wet, hungry and dying. Then things get worse....

Did you ever need to take a list or table of rows and insert or update a target table depending on the prior existence of the row? We have all run into this at one time or another in our careers. If you have not had the pleasure of doing this you haven't lived yet! We call it an "upsert" for short and it stands for UPDATE or INSERT. Ever write a huge block of code like IF EXISTS(...)...ELSE...? There is an easier way. I now introduce to you the MERGE statement.


The What

So what is this magic that you speak of wizard? The MERGE statement was introduced into SQL in Server 2008. It will perform INSERT, UPDATE and even DELETE from a source to a destination. The definition of the structure of the merge statement can be scary to look at but you will find after using it a few times it's really quite easy to get the hang of using.

[ WITH <common_table_expression> [,...n] ]
MERGE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> 
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]    
;

<target_table> ::=
{ 
    [ database_name . schema_name . | schema_name . ]
  target_table
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }
}

<table_source> ::= 
{
    table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
        [ WITH ( table_hint [ [ , ]...n ] ) ] 
  | rowset_function [ [ AS ] table_alias ] 
        [ ( bulk_column_alias [ ,...n ] ) ] 
  | user_defined_function [ [ AS ] table_alias ]
  | OPENXML <openxml_clause> 
  | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
  | <joined_table> 
  | <pivoted_table> 
  | <unpivoted_table> 
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<set_clause>::=
SET
  { column_name = { expression | DEFAULT | NULL }
  | { udt_column_name.{ { property_name = expression
                        | field_name = expression }
                        | method_name ( argument [ ,...n ] ) }
    }
  | column_name { .WRITE ( expression , @Offset , @Length ) }
  | @variable = expression
  | @variable = column = expression
  | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
  } [ ,...n ] 

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ] 
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

<search condition> ::=
    { [ NOT ] <predicate> | ( <search_condition> ) } 
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] 
[ ,...n ] 

<predicate> ::= 
    { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression 
    | string_expression [ NOT ] LIKE string_expression 
  [ ESCAPE 'escape_character' ] 
    | expression [ NOT ] BETWEEN expression AND expression 
    | expression IS [ NOT ] NULL 
    | CONTAINS 
  ( { column | * } , '< contains_search_condition >' ) 
    | FREETEXT ( { column | * } , 'freetext_string' ) 
    | expression [ NOT ] IN ( subquery | expression [ ,...n ] ) 
    | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } 
  { ALL | SOME | ANY} ( subquery ) 
    | EXISTS ( subquery ) } 

<output_clause>::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
        [ (column_list) ] ]
    [ OUTPUT <dml_select_list> ]
}

<dml_select_list>::=
    { <column_name> | scalar_expression } 
        [ [AS] column_alias_identifier ] [ ,...n ]

<column_name> ::=
    { DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

I told you it was scary! There are all kinds of possible ways to use this. If you ask me though, the very definition is what intimidates people and scares them out of learning to use it. Let's look at some simple examples of how to use this next so it's not quite so scary.


The How

We're going to need two tables to test this out with and some data inside them. Start up SSMS and your handy TestDB and open a query window. Cut and paste this into it, run it and you will have a source and a target table. The source is the table of products that came in on the shipping truck and need to be added to inventory. The target is the table that holds the current inventory of your store.

USE [TestDB]
GO

-- Create some test tables
CREATE TABLE [dbo].[tblProduct] 
( [ID] int IDENTITY
 ,[ProductName] varchar(50)
 ,[Quantity] int
)  

CREATE TABLE [dbo].[NewShipment]
(
 [ID] int IDENTITY
 ,[ProductName] varchar(50)
 ,[Quantity] int
)

-- Add some inventory
INSERT INTO [dbo].[tblProduct]
( [ProductName]
 ,[Quantity]
)
VALUES
('Left Handed Monkey Wrench',1),
('Right Handed Hammer',2),
('Invisible Paint',5),
('Drop Cloth',2),
('Wratchet Wrench',1),
('1/2 Inch Drive Socket Set',4)

-- Add some product to the shipment that just came in.
INSERT INTO [dbo].[NewShipment]
( [ProductName]
 ,[Quantity]
)
VALUES
('Left Handed Monkey Wrench',3),
('Wratchet Wrench',2),
('1/2 Inch Drive Socket Set',1),
('Coffee Cup',10)

Now that we have a products table and a shipment table, let's update our product inventory with the latest shipment that came in. We have some additions to the existing inventory and we added a line of Coffee Cups with a quantity of 10. Here is the statement heavily commented to show what is going on.

USE [TestDB]
GO

--Show initial table content
SELECT * FROM [dbo].[tblProduct];

--Merge the shipment
MERGE [dbo].[tblProduct] AS [Products] --Target
USING [dbo].[NewShipment] AS [Shipment] --Source.
 ON [Products].[ProductName] = [Shipment].[ProductName] --Join condition
--Match Condition. Add to existing inventory.
WHEN MATCHED THEN  
  UPDATE SET [Products].[Quantity] = [Products].[Quantity] + [Shipment].[Quantity] -- 
--No Match Condition. Create new inventory record.
WHEN NOT MATCHED THEN  
  INSERT ([ProductName],[Quantity]) VALUES ([Shipment].[ProductName], [Shipment].[Quantity]);

--Show outcome
SELECT * FROM [dbo].[tblProduct];

You'll notice when the statement finishes there are two result sets. The first was the content of the inventory table before the merge and the second is the content of the table after the merge completed. You'll notice our counts increased as they should have and we have added the new coffee Cup inventory to our table as well.

What about DELETE? It will also delete items in the merge. I left it out for clarity. To remove items during a merge we use the condition WHEN NOT MATCHED BY SOURCE THEN DELETE. This causes any values not in the source table to be deleted from the target table. The statement WHEN NOT MATCHED BY TARGET THEN DELETE is also valid. If we have the statement below and we execute it against the tables, what do you think the outcome will be?

MERGE [dbo].[tblProduct] AS [Products] --Table to hold final output 
USING [dbo].[NewShipment] AS [Shipment] --Table values to merge into above.
 ON [Products].[ProductName] = [Shipment].[ProductName] --Join condition
--Match Condition. Add to existing inventory.
WHEN MATCHED THEN  
  UPDATE SET [Products].[Quantity] = [Products].[Quantity] + [Shipment].[Quantity] -- 
--No Match Condition. Create new inventory record.
WHEN NOT MATCHED THEN  
  INSERT ([ProductName],[Quantity]) VALUES ([Shipment].[ProductName], [Shipment].[Quantity])
--If not matched in SOURCE, delete it.
WHEN NOT MATCHED BY SOURCE THEN DELETE;

Conclusion

I hope this has helped to demonstrate the basic use of the MERGE statement for you and given you some ideas to improve future code you have to write or refine existing code from long ago. Just remember these few little gotcha's and you will be fine:

• Duplicate rows in the target table will give you a duplicate row error. Use the GROUP BY clause or eliminate duplicates rows in the target before attempting a merge.If you need the duplicate rows in the target and use a GROUP BY clause it will apply the action to ALL duplicate rows in the target.
• Avoid filtering rows with the ON clause of the join. Use only the necessary values to provide the needed join or you could end up with some strange outcomes. 
• You can use additional conditional clauses on the WHEN operations of the merge. An example would be like: WHEN MATCHED AND ([Products].[Quantity] > 5) THEN... This would only operate on matches where the Quantity > 5.

Happy coding!

Removing Duplicate Data

Introduction

A common problem almost everyone runs into at one time or another is duplicated data in a table. If you haven't encountered this yet you are one, very lucky and two, your day is coming. Good news! We have a solution in SQL that makes sorting through this a much easier task! The ROW_NUMBER() function can make very short work of eliminating those pesky extra rows. First you'll want to identify how those rows made it into your table and close the hole so it doesn't happen again. Now let's take a look at how we can use this function to make our problems go away.

Creating the Problem

Lets create a table in our TestDB. We'll call it Employees. This table is for tracking the progression of our employees careers over time.

USE [TestDB]
GO
--Create a test table
CREATE TABLE [dbo].[Employees](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](20) NOT NULL,
[LastName] [varchar](20) NOT NULL,
[EmployeeNumber] [varchar](10) NOT NULL,
[Title] [varchar](20) NOT NULL,
 CONSTRAINT [PK__Employee__ID] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

Now lets fill it with some bogus data and make up a few career progressions along the way so we have some rows on all our employees. Any similarity to persons either living or dead is just coincidence.

--Fill it with some data
INSERT INTO [dbo].[Employees] ([FirstName],[LastName],[EmployeeNumber],[Title])
VALUES
('Daniel','Slingblade','1001101111','Troll')
,('Peter','PumpkinEater','0110010000','Troll')
,('Bob','UpandDown','4242424242','Troll')
,('Dan','TheMan','ffffffffff','Troll')
,('Peter','PumpkinEater','0110010000','Lesser-Deity')
,('Daniel','Slingblade','1001101111','Gate Keeper')
,('Bob','UpandDown','4242424242','Key Master')
,('Dan','TheMan','ffffffffff','Stream Crosser')
,('Bob','UpandDown','4242424242','Lesser-Deity')
,('Dan','TheMan','ffffffffff','Lesser-Deity')
,('Peter','PumpkinEater','0110010000','Demigod')
,('Daniel','Slingblade','1001101111','Wizard')
,('Guy','Redshirt','0000000000','Expendable')
,('Buffy','Bendy','4572475047','Slayer')

Now that we have some data in our table, we have to create a duplication situation in order to resolve it. Take this query and run it a few times. Really mess things up.

INSERT INTO [dbo].[Employees] ([FirstName],[LastName],[EmployeeNumber],[Title])
SELECT [FirstName],[LastName],[EmployeeNumber],[Title]
FROM [dbo].[Employees] [Emp] WITH (NOLOCK)
WHERE Firstname LIKE 'Dan%'

INSERT INTO [dbo].[Employees] ([FirstName],[LastName],[EmployeeNumber],[Title])
SELECT [FirstName],[LastName],[EmployeeNumber],[Title]
FROM [dbo].[Employees] [Emp] WITH (NOLOCK)
WHERE Firstname LIKE 'P%'

Solving the Problem

I now have 41 rows of duplicated data in my test table. We're going to attempt to clean the table so that we only have a single row of data for any given condition.

The SQL function ROW_NUMBER() adds an integer column to a selection of data. This allows you to easily number a set of rows for sorting, paging or in this case, identifying duplicate rows.

Step one, we'll decide what identifies a row. In this case it will be [FirstName], [LastName], [EmployeeNumber] and [Title]. You can change your criteria to meet the needs of the situation you're in but the end game is to get a unique key. So let's do a SELECT DISTINCT on these fields and see what we come up with. With the query below we have our original 14 rows so this plan will work.

How do we split them off? In this case a SELECT INTO... would solve your problem with a quick truncate table, but we're going to pretend there are millions of rows here. There are several different ways but one of the easiest is to just delete the duplicate entries.

Using this query we can easily number our rows 1...X by our key we identified earlier.

SELECT ROW_NUMBER() OVER ( PARTITION BY [FirstName],[LastName],[Title] ORDER BY [LastName] ) [ROW]
      ,[FirstName]
      ,[LastName]
      ,[EmployeeNumber]
      ,[Title]
FROM [TestDB].[dbo].[Employees] 

You should see something similar to the table listing below. Based on this, we can now delete anything with [ROW] > 1 and remove all the duplicates.

Using this statement inside a transaction we can test the operation before we actually commit it. What we do is select the rows numbered as [Rows] deleting any of them with [Row] > 1.

BEGIN TRANSACTION
DELETE [ROWS] FROM
(
SELECT ROW_NUMBER() OVER ( PARTITION BY [FirstName],[LastName],[Title] ORDER BY [LastName] ) [ROW]
 ,[FirstName]
 ,[LastName]
 ,[EmployeeNumber]
 ,[Title]
FROM [TestDB].[dbo].[Employees]
) [ROWS]
WHERE [ROW] > 1
ROLLBACK

Now just change the ROLLBACK to a COMMIT and run the statement again and the duplicate rows will be gone. .

Conclusion

I hope you can use this at some point in time to save yourself a little bit of work. This is just one of the possible uses of ROW_NUMBER(). What other possible uses can you think of?

The COUNT Function & NULLs

I'm still a bit under the weather from this cold so I'm going with a short and simple refresher this week.

We all at one time or another have used the SQL COUNT function to look at how many rows of something exists. Some of us have learned the hard way that you have to be careful when using this function. At the very least I hope to give you something to think about.

Let's create a table in our TestDB with this script.

CREATE TABLE [dbo].[tblTestCount]
(
   [id] [int] IDENTITY(1,1) NOT NULL,
   [AllowNulls] [int] NULL,
   [NoNulls] [int] NOT NULL
) ON [PRIMARY]
GO

Now lets put some data into the table.

INSERT INTO tblTestCount ([AllowNulls], [NoNulls])
VALUES (1,1)
,(1,0)
,(null,5)
,(5,4)
,(null,4)
,(3,3)
,(8,10)

Here are the queries we're going to run against the table. Before you run it, write down what you think the results for each query will be. 

SELECT COUNT(1) AS [Query1] FROM [dbo].[tblTestCount]
SELECT COUNT(*) AS [Query2] FROM [dbo].[tblTestCount]
SELECT COUNT([id]) AS [Query3] FROM [dbo].[tblTestCount]
SELECT COUNT([AllowNulls]) AS [Query4] FROM [dbo].[tblTestCount]
SELECT COUNT([NoNulls]) AS [Query5] FROM [dbo].[tblTestCount]
SELECT COUNT(DISTINCT [AllowNulls]) AS [Query6] FROM [dbo].[tblTestCount]

SELECT COUNT(DISTINCT [NoNulls]) AS [Query7] FROM [dbo].[tblTestCount]

SELECT COUNT(DISTINCT *) AS [Query8] FROM [dbo].[tblTestCount]

Did you get them correct? What if anything can you take away from this? Next week, removing duplicate data.

SSMS Third Party Tools

Introduction

My last BLOG covered tips and tricks to make using SSMS more efficient and friendly. This week we'll cover some third party tools that will save you some serious time and trouble while working with SQL server. Most of the major players in the third party market have free products they offer in a free tools download area for the cost of registering with them. We are only going to scratch the surface again in regards to what is available. I invite you to look around and see what you can find. Check out the links in the resource section at the bottom.

  

Tools

SQL Search
The first tool I'm going to cover is from Red Gate Software and is called SQL Search. This application is a free plugin for SSMS that assists with tracking things down in SQL server. Click the link in the resources section and install it into your SSMS.

Click the toolbar button it adds on the left of your GUI. A tabbed page will appear allowing you to select the server, database, object types to search and an entry for a search value. This is an invaluable tool when making changes to a database you are not familiar with, or need to quickly find all references to an object.

Let's say you need to add a column to a table in a database and someone coded a stored procedure that truncates and inserts the values from this table into a table in another database using something like:

DELETE FROM AnotherDB.dbo.AnotherTable
INSERT INTO AnotherDB.dbo.AnotherTable
SELECT *
FROM dbo.TableImModifying

Believe me, it does happen! This is a great example of why we want to use a column list when we're selecting information. When we add the column to TableImModifying we're going to break the stored procedure that updates AnotherTable in AnotherDB. By doing a quick search server wide with SQL Search for TableImModifying we'll find the reference and either modify the table AnotherTable in AnotherDB or better yet, change the stored procedure to use a column list, the latter of which assumes the column you are needing is not needed in AnotherDb.dbo.AnotherTable. You get the picture!

There are other add-ins available from Red Gate you can look at by clicking the Add-Ins button on the tool bar next to search. Some cost money, some are free. Look around and see what else they have that you could find useful. SQL Compare is another great tool in my tool belt and is well worth the licensing fee.

SSMS Tools Pack
The next tool I'm going to talk about is SSMS Tools Pack. This is an add-in package developed by Mladen Prajdic and is made available to the SQL community through his web site linked in the resources below. Versions supporting SQL server 2K5 to 2K8R2 are free to anyone who wishes to use them. Starting with SQL server 2012 version he has started charging a minimal licensing fee for its use. Download and install the package for the version of SSMS you have installed.

The default installation adds the tools as a seperate menu item but you have the option to install it under your Tools menu item. I use the default. When the installation completes restart SSMS and you will have a menu item named SSMS Tools. Click the menu and a sub menu will drop down similar to the one here.

There are many facets to this tool represented in each sub-menu item that are useful and you need to explorer it's documentation fully. I'll hit a few of the high points here.

SQL History: Search Local SQL History will pop open a form that allows you to search through queries that have been run on your machine. There is a date range you can adjust to limit the results returned. You have the option of copying it to the clipboard or opening it in a new window. This is great when you need to run a query you ran some time back and don't want to write it again.

SQL Snippets: These are shortcuts you can type into your query window and when you hit enter they will be replaced with the code associated with them. There are quite a few built into the application when you first install it and you can add your own. If you have a query you use often, you can enter it into snippets and assign it a shortcut and you won't ever have to type it out or load it from file again, Read up on them in the documentation. Below is the snippet INS. If you type this into your  query window and hit enter it will be replaced with the query you see in the right window.

Execution Plan Analyzer: This tool is very good at catching some of the easier problems with queries and offering advice on how to improve them. It is a good starting point though when you need to quickly diagnose and correct an issue in a database you are not acquainted with. You will need to execute the query once with Show Actual Execution Plan. Once the query completes, switch to the execution plan tab and right click the top bar for the sub-menu to enable the plan analyzer. It will show areas of your query that could improve and even offer tips on how to do it.

Click the Execution Plan Analysis button (1) and then the bar (2) and it will show you things that could be improved.

Brent Ozar First Aid

Brent is a Microsoft SQL Server MVP and has built quite a business consulting on SQL issues that plague many corporations. He is a dedicated blogger and extremely active in the forums. I could talk all day about his greatness. One thing I find admirable about him is that he started from the bottom like most of us with little to no help and he goes out of his way to make sure everyone has the tools available that he wishes he had when he began. I have linked to the first aid kit page on his web site that holds all the free tools and scripts he offers. You may also click the individual links to each of the three tools I'm going to talk about here for their individual pages. I strongly advise subscribing to his blog to continue your progress toward enlightenment.

These are all stored procedures that are geared more toward DBAs than developers using SSMS. If you're a DBA by accident or choice you'll love these. I create them in the [Master] database of the servers I manage.

sp_Blitz; When executed it will examine your server for common performance and health issues.The default execution gives results in a prioritized list with links to information on what it is and how to resolve the problem. There are many parameters that can be used to look at various things and tweak output. Refer to the documentation he provides and watch the video.

sp_BlitzIndex; When this is executed against your database it examines missing and unused indexes as well as existing indexing strategy and produces output with information about your indexes, problems it has found and links to more information for each row.

sp_AskBrent: This one is for when you get the call saying "Your server is running slow". You execute it while the problem exists and it takes in everything going on with the server and returns prioritized listing of issues it has identified with links for more information.

Conclusion

I hope you have found some of these tools to be of interest to you. Dig into them and discover what else they contain that could be of use to you in your daily work. Some of the authors of these tools, scripts, etc. allow donations from their websites. They provide these free of charge for your use but if you find them to have value, think about kicking back a buck or two to help support their further development. See you next week!

Resources

RedGate SQL Search - SQL search plugin for SSMS.

SSMS Tools Pack - Tools, scripts and analyzers.

SQL Sentry Plan Explorer Free - A very good add-in for query plan analysis.

Brent Ozar First Aid - Brent's free scripts. This guy is amazing! Explore his entire site!

SSMS - Tips, Tricks and Tweaks

Introduction

SQL Server Management Studio commonly referred to as SSMS is the GUI management application Microsoft provides for querying, configuring, managing and monitoring Microsoft SQL Server.

Since most of us have experience using SSMS for basic object creation and modification, I'm going to start with covering the "built-in" and less commonly known features and progress in future blogs to add-on tools that utilize the extensible feature of SSMS. This is by no means a complete list and I invite you to share below in the comments tools and tips you have found that are useful and feel would be of value for others to know.

Drag and drop scripting

Did you know that you can save yourself some keystrokes when writing a query by dragging an object from the explorer over to the query window? That's right! Check this out!

Write a simple script like you see below and then right click the data base you want in the USE statement, hold it down and drag it over between the brackets.

Release the mouse button and walha! With code completion this example is a little ridiculous, but think about having to type a bunch of column names in a SELECT statement. This can be pretty handy at times and works with anything you see in the Object Explorer!

Scripting Anything

You don't have to know everything about T SQL in order to write a script for creating an object, altering an object, setting or changing a permission or any other SQL server task. SSMS can do it all for you! SSMS works disconnected since server 2005. That means anything you do is not applied until you select save or apply which results in the creation of a script and execution of that script. This feature allows you to save the script for future use instead of immediately applying it. Lets look at an example.

I am going to add to columns to my existing table in my TestDB. Column_1 and Column_2 as you can see have been added in the designer. Instead of clicking the save button and doing it now, I want to wait until tonight so I need a script. Right click in the designer window and select "Generate Change Script...". 

A window will pop up with the script in it for performing the modifications you have made. If the table requires a tear down, there will even be script to move your data to a temporary table. Click the Yes button and it will prompt you for a location and file name to save the script to.

This works for almost anything in SSMS. Modify an index and then script it out so you can apply it later when the server is slow. Make server configuration changes through the GUI and then script them. The list goes on and on.

Protecting Table Modification

I get this question a lot so thought I would throw it in. Did you ever attempt to save a change to a table and have SSMS deny the change? That's a default means of protecting you from doing something that could cause the table to become unavailable or cause data loss. To turn it off and have your way with the server, click the TOOLS menu and then OPTIONS.

Templates

Did you ever have to write a script more than once and wish you had saved it? There are several add-on tools out there we will cover in future articles but SSMS has one that is built in that you can get started with and you can even add your own custom templates as well!

Open SSMS and connect to your test database server. Press CTRL+ALT+T to open your template explorer. You can park it on your side bar so it will open when you mouse over it. I usually have it on the right side.

Open a new query and drag Database.CreateDatabase to the query window.

Press CTRL+SHFT+M and you will be prompted for values to insert into placeholders in the template. Type a database name and hit enter. Your query window will now have a script to create your database.

You can create your own templates and define placeholders for things you do regularly. I'll leave that up to you to research.

Conclusion

Hopefully you have learned some of the interesting and not often discovered features of SSMS. See what else you can discover for yourself by looking around the web or just exploring inside SSMS. If you find something, share! Next BLOG we'll cover add-in tools that will make your life working with SQL server much easier.

 

LINQ - LINQ to SQL

Intro w/Recap

In part 1, part 2, and part 3 we discussed LINQ basics, some more advanced techniques for dealing with objects in LINQ, and LINQ to XML. Here in our final post of the series we'll discuss LINQ to SQL, and briefly discuss Entity Framework in order to give some context to LINQ to SQL. What is LINQ to SQL? As you can guess, it is the usage of LINQ with data stored in a sql-compatible database such as MS SQL Server. For the purposes of our discussion we will be using SQL Express 2008 R2, though these samples will work fine with newer versions as well.

Entity Framework Basics

At this point hopefully you're wondering why I plan on discussing Entity Framework (EF). The reason is that LINQ to SQL works directly with Entity Framework objects. Entity Framework is MS's Object Relational Mapper (ORM) that helps save you time by linking your Plain-Old-CLR-Objects (POCO's) to your database. If you've ever had to link objects with database tables/fields you know it can be very tedious and time-consuming work, and ORM's help automate much of this tedium.

As part of today's LINQ lesson I'll walk you through how to get a database all setup and ready to map with EF, then I'll show you how to have EF automatically create your tables/fields based on object(s) that you've created in code.

Let's start with setting up the database. If you don't already have it, go download sql server express edition. You can find it here. Install that little sucker (get the one called "SQL Server Express With Tools") and then return. If you need help with installation, please post a reply here in the blog so that everyone can benefit from the shared knowledge. After you have completed installation, you're done! Yeah EF is so cool that it creates your database, tables, and columns for you. Can't get much easier than that. But hey we haven't done that yet so keep reading.

Code-First DB Setup w/EF

That's it for the database for now. We're going to use something called code-first setup of our EF, meaning we'll write our storage classes first and we'll use some spiffery to automatically create the tables for us.

I've created a couple classes myself for storage; Animal and Show. Here is the code for both:

using System.ComponentModel.DataAnnotations;
namespace BlogLinq
{
    public class Animal
    {
        [Key]
        public string name { get; set; }
        public string animalType { get; set; }
    }
}

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace BlogLinq
{
    public class Show
    {
        [Key]
        public string name { get; set; }
        public virtual List<Animal> animals { get; set; }
    }
}

As you can see, we have an Animal with 2 properties (name and animalType), and a Show with 2 properties (name and animals, which is a list of type Animal). We use the Key attribute to denote which field is the primary key for our class/table, so that EF can avoid creating a heap table. Trust me (or ask Bobby, my resident DBA, they're bad). The only other oddity here is that I made the animals property of the Show class virtual. This is necessary for EF to load such a list at runtime from a SQL data source.

Next you need to install the EntityFramework package from NuGet. If you don't, your code won't compile as that namespace and the Key attribute I used above are from EF. I'm assuming you know how to work with NuGet package manager, but if that's not the case feel free to ask in the comments below. Now you need to create a "Context" class, which is just a fancy way of saying that you need something that ties LINQ to your database table(s) and classes. Create a class called ZooContext. Here's the code of my ZooContext:

using System.Data.Entity;

namespace BlogLinq
{
    public class ZooContext : DbContext
    {
        public DbSet<Show> Shows { get; set; }
        public DbSet<Animal> Animals { get; set; }
    }
}

Because this blog post isn't about EntityFramework (let me know if you want to see such a beast!) I won't go into any further detail on this ZooContext class, so we'll just say for now that this class as defined above will let us use LINQ to query the database rather than having to roll our own SQL queries.

Inserting Data With EF

What fun is querying data when there's no data? none at all! We're going to write a quick method here that will insert data into our database and tables for us. "But Pete", you say, "We don't have a database, or tables, or columns!". Yeah I know that, and so does EF. Trust me, it will create this crap for you. Write a function like this:

        protected void btnEFInsert_Click(object sender, EventArgs e)
        {
            var zooContext = new ZooContext();
            var show = new Show() { name = "Early", animals = new List<Animal>() };
            show.animals.Add(new Animal() { animalType = "Bird", name = "George" });
            var show2 = new Show() { name = "Late", animals = new List<Animal>() };
            show2.animals.Add(new Animal() { animalType = "Ferret", name = "Fred" });
            show2.animals.Add(new Animal() { animalType = "Bear", name = "Bear" });
            zooContext.Shows.Add(show);
            zooContext.Shows.Add(show2);
            zooContext.SaveChanges();
        }

In the first line of our function we create an instance of our ZooContext class that links EF to our POCO's. We then proceed to create some shows, add animals to them, and add our shows (which contain the animals) to the zooContext. Then we call zooContext.SaveChanges(), and voila! EF created a database for us, a few tables, setup our columns, and even created primary and foreign keys for us. That's so darn cool I could pinch myself. Here's a screenshot full of awesomesauce:

Querying Data With LINQ to SQL

Back to LINQ...now that we have a little bit of data in our database our foray into pure EF is over, so let's query that data using LINQ. This should look pretty familiar by now:

        protected void btnLinqSqlSelect_Click(object sender, EventArgs e)
        {
            var zooContext = new ZooContext();
            var query = from show in zooContext.Shows
                        where show.name.Equals("Late", StringComparison.OrdinalIgnoreCase)
                        select show;
            foreach (var show in query)
            {
                Response.Write("show: " + show.name);
                foreach (var animal in show.animals)
                    Response.Write(String.Format("<br / >    {0} the {1} is in the {2} show!", animal.name, animal.animalType, show.name));
            }
        }

We start by creating a ZooContext object in order to pull junk from the database, then it's all just standard-looking LINQ from there. In the above LINQ query we retrieve all the shows (there's just 1) named "Late", and EF/LINQ goes and pulls all the shows and their child animals from the database for us. Simple and powerful stuff here!

Here's the output in case you don't trust me about the code working:
show: Late
    Bear the Bear is in the Late show!
    Fred the Ferret is in the Late show!

Drawbacks

With great power comes great headaches. The cool time-saving and readability afforded by LINQ comes with a price. Ask your local DBA what they think about ORM's and you'll get a good first-person ear-blasting about the evils of auto-generated queries. Fine-tuning a SQL database really is a profession unto itself, which is part of why the DBA was invented. EF and LINQ generate some decent SQL, but in many cases it's not optimized. This isn't much of a concern with small apps, but if you expect to scale, dig further into LINQ/EF and see what all options you can find for optimization.

What's Next?

• You could spend some time cozying up to Entity Framework. Learn things like changing the name of the database your data gets plopped in, view the sql queries it generates (it's still using sql behind the scenes), how to add new fields to your classes and have them added to the table(s), etc.

Resources

My Code (Created Using VS Express 2013)!
Entity Framework
SQL Express