Unit testing in SQL Server, installing tSQLt and doing a quick test

In today's post we will install tSQLt and run a simple test. To start download tSQLt, unzip the zipfile into its own folder. Here is what is inside that zip file, we are looking at executing the tSQLt.class.sql file later on

Open up SSMS, connect to the database server where you want to create the tSQLt database. We need to enable the CLR on the server where you are running your tests because tSQLt is using some CLR functionality.

Execute the following command to enable the CLR on your box

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Now we need to create the database and make it trustworthy, execute the following

CREATE DATABASE tSQLt
GO

USE tSQLt
GO

ALTER DATABASE tSQLt SET TRUSTWORTHY ON;
GO

Go to your unzipped folder, open up the tSQLt.class.sql file, make sure that you are connected to you tSQLt database, execute the code

You should see something like this in the output once all the code has run

Installed at 2015-10-05 17:58:53.360

(1 row(s) affected)

+-----------------------------------------+
|                                         |
| Thank you for using tSQLt.              |
|                                         |
| tSQLt Version: 1.0.5686.18945           |
|                                         |
+-----------------------------------------+

After the code has completed, run the following

EXEC tSQLt.Fail 'congratulation, we are ready to go to the next step'

You should see the following output

Msg 50000, Level 16, State 10, Procedure Fail, Line 51
tSQLt.Failure


Let's create a sample database so that we can do some testing

Execute the following code

CREATE DATABASE UnittestExamples
GO

USE UnittestExamples
GO

CREATE TABLE dbo.test1(id int)
CREATE TABLE dbo.test2(id int)

INSERT dbo.test1  VALUES(1)
INSERT dbo.test2  VALUES(2)

INSERT dbo.test1  VALUES(3)
INSERT dbo.test2  VALUES(3)

INSERT dbo.test1  VALUES(4)
INSERT dbo.test2  VALUES(5)
GO

USE tSQLt
GO

Now let's run a couple of tests

tSQLt ships with an assertion to test if 2 tables are the same, the assertion is named AssertEqualsTable

So let's see if these two tables are the same, execute the following

EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'UnittestExamples.dbo.test2'

Here is the output
Msg 50000, Level 16, State 10, Procedure Fail, Line 73
tSQLt.Failure

As you just saw, will get an error, now what will happen if you compare the table against itself, run this to find out.

EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'UnittestExamples.dbo.test1'

There was no error now, this is expected

What happens if we test a table against a non existing table?

EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'UnittestExamples.dbo.test112'

Msg 50000, Level 16, State 10, Procedure Fail, Line 51
tSQLt.Failure
Msg 208, Level 16, State 1, Line 2
Invalid object name 'UnittestExamples.dbo.test112'.

I want to point something important out. Comparing two tables is usually doing for small lookup tables, you create a table inside your unit test with values that you expect to match the table in the database, if these tables don't match, then you have a problem. You wouldn't want to compare two million row tables in a test.


Let's do another assertion, let's see if an table exists.

EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'

Msg 50000, Level 16, State 10, Procedure Fail, Line 51
tSQLt.Failure

That fails as expected, now let's try it with a table that exists

EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.test1'

That ran fine as expected.

Let's clean up by dropping the two tables we created earlier.

DROP TABLE dbo.test1 , dbo.test12

Creating a test class
What you want to do with tSQLt is group all your tests together, for example if you are testing a stored procedure, you can execute just those set of tests. When creating a test class in tSQLt, a new schema will be created, you can easily verify this. Execute the following

USE tSQLt
GO

SELECT * FROM sys.schemas
WHERE schema_id BETWEEN 5 AND 1000

name schema_id principal_id
tSQLt         5 1

As you can see, the only schema the query returns is the tSQLt schema. Now let's create a new test class, you do this by executing the NewTestClass  stored procedure, if we wanted to create a test class named WebFlags, we would do it like this

tSQLt.NewTestClass 'WebFlags'


Running the following code again, you will now see an additional schema in the output

USE tSQLt
GO

SELECT * FROM sys.schemas
WHERE schema_id BETWEEN 5 AND 1000

name schema_id principal_id
tSQLt         5 1
WebFlags 6 1  


Let's create a couple of test stored procedures with the examples we used before

First we need to recreate the test1 table again

USE UnittestExamples
GO

CREATE TABLE dbo.test1(id int)


INSERT dbo.test1  VALUES(1)
INSERT dbo.test1  VALUES(3)
INSERT dbo.test1  VALUES(4)
GO

Now we will create 3 stored procedures. It is very important that you name each stored procedure beginning with 'test ', if you don't do this then tSQLt will skip the proc. Here is the code to create our 3 stored procedures

USE tSQLt
GO

CREATE PROCEDURE WebFlags.[test test1 table has all correct values]
AS

CREATE TABLE dbo.test2(id int)

INSERT dbo.test2  VALUES(2)
INSERT dbo.test2  VALUES(3)
INSERT dbo.test2  VALUES(5)

EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'tSQLt.dbo.test2'

DROP TABLE dbo.test2
GO



CREATE PROCEDURE WebFlags.[test SomeTable table exists]
AS
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'
GO

CREATE PROCEDURE WebFlags.[test test1 table exists]
AS
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.test1'
GO

To execute these procs, all we need to do is run the following

tSQLt.tSQLt.Run 'WebFlags'

Here is the output

[WebFlags].[test SomeTable table exists] failed: (Failure) 'UnittestExamples.dbo.SomeTable' does not exist
[WebFlags].[test test1 table has all correct values] failed: (Failure) Unexpected/missing resultset rows!
|_m_|id|
+---+--+
|<  |1 |
|>  |4 |
|=  |3 |
|>  |5 |
|>  |2 |

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name                                      |Dur(ms)|Result |
+--+----------------------------------------------------+-------+-------+
|1 |[WebFlags].[test test1 table exists]                |      3|Success|
|2 |[WebFlags].[test SomeTable table exists]            |      6|Failure|
|3 |[WebFlags].[test test1 table has all correct values]|     33|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 3 test case(s) executed, 1 succeeded, 2 failed, 0 errored.
-----------------------------------------------------------------------------



As you can see, on top you will get the name of the tests that failed, also included is the error message

[WebFlags].[test SomeTable table exists] failed: (Failure) 'UnittestExamples.dbo.SomeTable' does not exist
[WebFlags].[test test1 table has all correct values] failed: (Failure) Unexpected/missing resultset rows!
|_m_|id|
+---+--+
|<  |1 |
|>  |4 |
|=  |3 |
|>  |5 |
|>  |2 |

Below that you will get a list of each that that ran with an indication if the test was successful or not

|No|Test Case Name                                      |Dur(ms)|Result |
+--+----------------------------------------------------+-------+-------+
|1 |[WebFlags].[test test1 table exists]                |      3|Success|
|2 |[WebFlags].[test SomeTable table exists]            |      6|Failure|
|3 |[WebFlags].[test test1 table has all correct values]|     33|Failure|

Finally you get a summary

Test Case Summary: 3 test case(s) executed, 1 succeeded, 2 failed, 0 errored.


You can also run all these tests by running the following

tSQLt.tSQLt.RunAll

Right now since you only have 1 test class, the result is the same if you run all tests or the tests for the WebFlags class

That's all for today, in the next post we will take a look at tSQLt available assertions and how they are used

The list of all the unit test posts can be found here: Unit testing in SQL Server, a list of all the posts

Dropping all objects in a schema

I had the need to drop all objects that I created in a specific schema as part of a unit test. Then I decided to test my script on the Adventure Works 2014 sample database and I got a couple of errors. Some of these errors were that there were foreign key constraints from a different schema pointing to a table in this schema. I also got an error complaining about an XML SCHEMA collection living in my schema.

If you want to follow along by running the scripts, first download the Adventure Works 2014 sample database

After you are done downloading the database, create a folder named training on your C drive.
Open up SSMS, open a query window, run the following script

USE [master]

--DROP DATABASE AdventureWorks2014
GO

RESTORE DATABASE AdventureWorks2014
FROM disk= 'C:\Training\AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_data' TO 'C:\Training\AdventureWorks2014.mdf',
MOVE 'AdventureWorks2014_Log' TO 'C:\Training\AdventureWorks2014.ldf'
,REPLACE, stats =20

Now that the DB is available, we can look at what the script looks like. Connect to the database you just created and open up a new query window. Before running the script make sure that you are in text mode, just hit CTRL + T in your  SSMS query window

Here is what the script looks like

   SET NOCOUNT ON
   DECLARE  @SchemaName NVARCHAR(100) = 'HumanResources'


   SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id))
   +'.'+ QUOTENAME(OBJECT_NAME(s.parent_object_id))
   + ' DROP CONSTRAINT ' +  QUOTENAME(s.name)   
   FROM sys.foreign_keys s
   JOIN sys.tables t on s.parent_object_id = t.object_id
   JOIN sys.tables t2 on s.referenced_object_id = t2.object_id
    WHERE t2.schema_id = SCHEMA_ID(@SchemaName)

    SELECT 'DROP ' +
                    CASE WHEN type IN ('P','PC') THEN 'PROCEDURE'
                         WHEN type =  'U' THEN 'TABLE'
                         WHEN type IN ('IF','TF','FN') THEN 'FUNCTION'
                         WHEN type = 'V' THEN 'VIEW'
                     END +
                   ' ' +  QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name)  
                   FROM sys.objects
             WHERE schema_id = SCHEMA_ID(@SchemaName)
    AND type IN('P','PC','U','IF','TF','FN','V')
    ORDER BY  CASE WHEN type IN ('P','PC') THEN 4
                         WHEN type =  'U' THEN 3
                         WHEN type IN ('IF','TF','FN') THEN 1
                         WHEN type = 'V' THEN 2
                       END


  SELECT 'DROP XML SCHEMA COLLECTION '
  + QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name)
  FROM sys.xml_schema_collections
  WHERE schema_id = SCHEMA_ID(@SchemaName)

  SELECT 'DROP SCHEMA ' + QUOTENAME(@SchemaName)

As you can see, there are 4 things going on here.

1. Remove the constraints
2. Drop Views, Function, Stored Procedures and Tables, drop functions and views first, procs last
3. Drop XML Schema Collections
4. Drop the Schema

Of course if you have a view in  another database or schema referencing your objects inside this schema you will have problems...I will get to this later


Running that script will give you the following output

ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID]
ALTER TABLE [Production].[Document] 
DROP CONSTRAINT [FK_Document_Employee_Owner]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_BusinessEntityID]
ALTER TABLE [HumanResources].[EmployeePayHistory] 
DROP CONSTRAINT [FK_EmployeePayHistory_Employee_BusinessEntityID]
ALTER TABLE [HumanResources].[JobCandidate] 
DROP CONSTRAINT [FK_JobCandidate_Employee_BusinessEntityID]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] 
DROP CONSTRAINT [FK_PurchaseOrderHeader_Employee_EmployeeID]
ALTER TABLE [Sales].[SalesPerson] 
DROP CONSTRAINT [FK_SalesPerson_Employee_BusinessEntityID]


DROP VIEW [HumanResources].[vEmployee]
DROP VIEW [HumanResources].[vEmployeeDepartment]
DROP VIEW [HumanResources].[vEmployeeDepartmentHistory]
DROP VIEW [HumanResources].[vJobCandidate]
DROP VIEW [HumanResources].[vJobCandidateEmployment]
DROP VIEW [HumanResources].[vJobCandidateEducation]
DROP TABLE [HumanResources].[Shift]
DROP TABLE [HumanResources].[Department]
DROP TABLE [HumanResources].[Employee]
DROP TABLE [HumanResources].[EmployeeDepartmentHistory]
DROP TABLE [HumanResources].[EmployeePayHistory]
DROP TABLE [HumanResources].[JobCandidate]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]


DROP XML SCHEMA COLLECTION [HumanResources].[HRResumeSchemaCollection]


DROP SCHEMA [HumanResources]

Run that and it should all run fine, the schema should be gone

Now change @SchemaName NVARCHAR(100) = 'HumanResources' to @SchemaName NVARCHAR(100) = 'Sales'

Run the script again, run the output from the script

Finally change the variable to Person @SchemaName NVARCHAR(100) = 'Person'

Run the script, run the output.  All 3 schemas we specified should be gone now



Now restore the database again, This time run the code to remove the Sales Schema, Then run the code to remove the Person Schema.

Here is the error you will receive


Msg 6328, Level 16, State 1, Line 39
Specified collection 'AdditionalContactInfoSchemaCollection' cannot be dropped because it is used by object 'HumanResources.vEmployee'.

However, it is really used by the Person.Person table which we just dropped. In this case you will need to drop the view. If you have stuff like this in the database, you probably don't want to just start dropping objects in another schema that point to objects in your schema

I am not having these kind of issues so the code is good enough for me to wipe out objects that have been created as part of my unit testing

Unit testing in SQL Server, taking a first look a tSQLt

This is the third post in my Unit Testing series, today we are looking at what tSQLt, in the next post we will take a look at how to install tSQLt

What is tSQLt?  Here is how it is described on the tSQLt website


tSQLt is a database unit testing framework for Microsoft SQL Server. tSQLt is compatible with SQL Server 2005 (service pack 2 required) and above on all editions.

Main features

tSQLt allows you to implement unit tests in T-SQL. This is important as you do not have to switch between various tools to create your code and your unit tests. tSQLt also provides the following features to make it easier to create and manage unit tests:

• Tests are automatically run within transactions – this keeps tests independent and reduces any cleanup work you need
• Tests can be grouped together within a schema – allowing you to organize your tests and use common setup methods
• Output can be generated in plain text or XML – making it easier to integrate with a continuous integration tool
• Provides the ability to fake tables and views, and to create stored procedure spies – allowing you to isolate the code which you are testing

So basically after you install tSQLt on your SQL Server instance, it is just a database, you now have a unit test framework that you can use. They way you use tSQLt is by creating stored procedures that will do the unit testing for you. It all runs within SQL Server.

You can run 1 test, all tests in a test class or all tests for all test classes by calling a single stored procedure.  Here are the Assertions and Expectations that ship with tSQLt

Assertions

AssertEmptyTable

AssertEquals

AssertEqualsString

AssertEqualsTable

AssertLike

AssertNotEquals

AssertObjectDoesNotExist

AssertObjectExists

AssertResultSetsHaveSameMetaData

Fail

Expectations

ExpectException

ExpectNoException

In addition to that you can of course use IF EXISTS..... and then a Fail Assertions to test anything that is not testable with the built in Assertions 

In addition to be able to run this all from a query window in SSMS, if you have Red Gates SQL Test then you can use that to run the tests, you will get a graphical indicator at that point. If the test passes it will be green, if it fails it will be red.

In the next post, we will take a look at how to install tSQLt on your database instance and write our  first test.

Unit testing in SQL Server..what are unit tests

This is a continuation from yesterday's post Unit testing in SQl Server... why? Today we will look at what unit tests actually are.

Test on an individual unit of code.
A unit test should ideally only test one discrete unit of code, there should not be a test for example how this unit of code reacts with other units of code. Tests should not be influenced by other units.

Repeatable
Executing a unit test should give you the same result every single time. Since a unit test is repeatable it is also easy to be automated.

Test one thing only
A test should only be testing one thing, it should test one question only. For example if I put money in the account did the balance increase?

Test the requirement for the unit.
If the requirement is that zip codes are 5 digits for example then you should have tests that verify that 5 is correct and for example 4 and 6 will fail the test.



When do I write unit tests?

Before development?
This enables you to focus on requirements, it is part of Agile, it also allows you to develop code structures first

During development?
If requirements change you need to create new unit tests or modify existing unit tests

After development?
You will need to add tests that validate existing code


The answer is of course during all 3 phases!
You will be writing unit tests when you change something in an existing application, start a new application or if you are in the middle of development for a soon to be launched application. The only difference is that with a new application you will write the test before the code and with an existing application that you will write the unit test after the code already exists.




Why are you not unit testing yet?
I asked this question and here are some of the answers I got...I hope the last one is a joke  :-)


Don’t know where to start
No time
No benefit
Our customer do our unit testing for us

Hopefully once you are done reading all these posts, these answers should not prevent you from starting to do unit testing

Unit testing in SQl Server... why?

I have been using tSQLt to do unit testing within SQL Server for the last 2 years. I already did a presentation twice at work and decided to document some of this so that it might help other people looking at using tSQLt to do unit testing with SQL Server.

This will be a multi part series, some of the stuff I will cover:

What is unit testing and why you should be unit testing
What tSQLt is and how to get started
Sample of all the different functionality that tSQLt provides
How to integrate tSQLt within your builds


Before starting, let me first tell you what happens when something is checked in into out source code repository.
1) The applications are build
2) Integration tests are run
3) Unit tests are run
4 If there were any errors an email is sent to the developers with the output of the failed tests or compilation errors

If there are errors, tSQLt will give you the name and the error for each test as well have a summary which looks something like the following

---------------------------------------------------------------------------------
Test Case Summary: 128 test case(s) executed, 127 succeeded, 1 failed, 0 errored.
---------------------------------------------------------------------------------


Before explaining what unit tests are, let take a look at what the cost is of fixing a bug

Take a look at this image below from a presentation by Stefan Priebsch, it shows you what the cost is of fixing a bug within the software lifecycle

As you can see, the relative cost fixing a bug is 150 times compared to if it was fixed during the requirements stage.

When doing unit testing, you hopefully will catch the errors before it gets to the point where the cost to fix the bug is 20x or higher.


In the next post I will explain what unit tests are and what they will provide to you and your team

Whenever you use the designer in SSMS, click on the Generate Change Script icon to verify what it will do

SQL Server Management Studio is pretty good tool to manage and develop against SQL Server instances. You have to be aware of some things that can be dangerous. Just the other day I had to help someone on a development environment who had a problem when he made a column bigger


The table looked like this

CREATE TABLE [TestTable] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[itemdate] [datetime] NOT NULL ,
[title] [varchar] (30) NOT NULL ,
) ON [PRIMARY]

So what the person wanted to do was make the title column 50 characters instead of 30. He right clicked on the table, clicked on Design, made the change and hit the save button. He then tried to run a query against the table and it was stuck, nothing was being returned. After a couple of minutes all seemed fine.
So what happened? Behind the scenes SSMS actually created a new table, dumped all that data into that table, dropped the original table and then renamed this new table to match the name of the original table



Here is what the script looks like that SSMS generates

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO

CREATE TABLE dbo.Tmp_TestTable
(
id int NOT NULL IDENTITY (1, 1),
itemdate datetime NOT NULL,
title varchar(50) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE dbo.Tmp_TestTable SET (LOCK_ESCALATION = TABLE)
GO

SET IDENTITY_INSERT dbo.Tmp_TestTable ON
GO

IF EXISTS(SELECT * FROM dbo.TestTable)
EXEC('INSERT INTO dbo.Tmp_TestTable (id, itemdate, title)

SELECT id, itemdate, title FROM dbo.TestTable WITH (HOLDLOCK TABLOCKX)')
GO

SET IDENTITY_INSERT dbo.Tmp_TestTable OFF
GO

DROP TABLE dbo.TestTable
GO

EXECUTE sp_rename N'dbo.Tmp_TestTable', N'TestTable', 'OBJECT' 
GO
COMMIT

All that can be replaced with this simple one line command, it runs much faster and it doesn't recreate anything, no data is being moved around either.

ALTER TABLE [TestTable] ALTER COLUMN [title] [varchar] (50)

The only time you should be using the designer is if the table is empty or if you want to see what kind of T-SQL the designer generates. You can see the T-SQL that SSMS generates by clicking on the Generate Change Script icon, you can then copy the T-SQL into a SSMS query window to look at it. You can see the Generate Change Script icon in the image below

 

By the way, SSMS will by default disable the recreation of tables when clicking on the save button, this can be disabled or enabled by unchecking or checking the highlighted option in the image below. You can get to this by clicking Tools-->Options-->Table and Database Designers

Review of SQL Performance Explained by Markus Winand

SQL Performance Explained is a database book written by Markus Winand, the book is a little less than 200 pages. The book covers Oracle, MySQL, PostgreSQL and SQL Server. The terminology used in the book is that of the Oracle RDBMS but there are site notes for the other RDBMS products. The primary focus of this book is indexing, if you are interested in indexing then this book is for you, the one index this book covers in the B-tree Index, other indexes are not covered in this book at all.


There are 8 chapters in this book and an appendix, here is what is in the book.









CHAPTER 1 - Anatomy of an Index
The first chapter is the only one that doesn't cover SQL specifically; it is about the fundamental structure of an index. An understanding of the index structure is essential to following the later chapters — don’t skip this!
Although the chapter is rather short — only about eight pages — after working through the chapter you will already understand the phenomenon of slow indexes.

CHAPTER 2 - The Where Clause
This is where we pull out all the stops. This chapter explains all aspects of the where clause, from very simple single column lookups to complex clauses for ranges and special cases such as LIKE. This chapter makes up the main body of the book. Once you learn to
use these techniques, you will write much faster SQL.

CHAPTER 3 - Performance and Scalability
This chapter is a little digression about performance measurements and database scalability. See why adding hardware is not the best solution to slow queries.

CHAPTER 4 - The Join Operation
Back to SQL: here you will find an explanation of how to use indexes to perform a fast table join.

CHAPTER 5 - Clustering Data
Have you ever wondered if there is any difference between selecting a single column or all columns? Here is the answer — along with a trick to get even better performance.

CHAPTER 6 - Sorting and Grouping
Even order by and group by can use indexes.

CHAPTER 7 - Partial Results
This chapter explains how to benefit from a “pipelined” execution if you don’t need the full result set.

CHAPTER 8 - Insert, Delete and Update
How do indexes affect write performance? Indexes don’t come for free — use them wisely!

APPENDIX A - Execution Plans
Asking the database how it executes a statement.

So who is this book for? This book is primarily for developers, if you are an admin and you want to know about how indexing work, then this book will help you understand that. Don't expect to find how to reorganize or rebuild indexes, that is not the focus of this book.

I enjoyed this book a lot, there is a ton of useful information for developers and as a developer you should understand how data is stored and how you can get to the data more efficiently. Once you are done with this book, you will understand databases and retrieval of data much better and you might even rethink how you are storing some of the data. I also like the fact that this book is a little less than 200 pages and focused one one thing instead of those monster 1000+ page books that cover dozens of topics but don't delve deep into any of those topics.

I give this book two thumbs up because it does what the author's intentions, is focused on indexing and explains it well. I do have one little bone to pick with the book and that is that the book does not mention the XML Execution plan that was introduced with SQL Server 2005.




You can check out the book at the book's website here: http://sql-performance-explained.com/

You can also check Amazon here: SQL Performance Explained

Dealing with temporary tables and named constraints

The other day one of our recently changed stored procedures in the development environment started to fail with a message like the following

There is already an object named 'PK_#SomeName' in the database.

I looked at the proc code and noticed something like the following

ALTER TABLE #test ADD CONSTRAINT PK_#test PRIMARY KEY CLUSTERED (id)

Books On Line specifies the following about constraint names

constraint_name

Is the name of a constraint. Constraint names must be unique within the schema to which the table belongs.

Before I give you an example of how you can get around this, let's first see how it breaks

Copy and paste the following code in a window in SQL Server Management Studio, execute the code below

CREATE TABLE #test (id int not null)


ALTER TABLE #test ADD CONSTRAINT PK_#test PRIMARY KEY CLUSTERED (id)

Now take the same exact code, paste it in a new window (use the same database) and execute it, you should see the following error

Msg 2714, Level 16, State 5, Line 3
There is already an object named 'PK_#test' in the database.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

As you can see the message clearly tells you that there is already an object with that name in the database. So how can you get around this? There are two ways, the first is to use an unnamed constraint

Open a new window and execute the following

CREATE TABLE #test (id int NOT NULL, PRIMARY KEY (id))

)

You can now do this in a couple of new windows and it won't fail.

Just to prove that the constraint works as expected, run the following code in some of those windows

INSERT #test VALUES(1)
INSERT #test VALUES(1)

You will get a message similar to the one below

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__#test_____3213E83F8E75389B'. Cannot insert duplicate key in object 'dbo.#test'. The duplicate key value is (1).
The statement has been terminated.

Instead of a primary key, you could also use a unique index. Contrary to constraints names, index names do not have to be unique within the schema to which the table belongs

You can run the following code in a couple of windows and it won't fail

CREATE TABLE #test (id int not null)

CREATE UNIQUE CLUSTERED INDEX PK_#test on #test(id)

If you run the code below you will see that it will fail on the second insert

INSERT #test VALUES(1)

INSERT #test VALUES(1)

Here is the expected error message

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.#test' with unique index 'PK_#test'. The duplicate key value is (1).
The statement has been terminated.

So to sum it up, do NOT use named constraint with temporary tables, especially not inside stored procedures, if two sessions run the same proc you will get a failure


Use unnamed constraints or use a unique index on a non nullable column instead

Defaults with parameters in SQL Server stored procedures gotcha

I was talking with someone today and this person told me that the stored procedure doesn't work as expected



Here is what the simplified code looked like

CREATE PROCEDURE prTest
@SomeParam int,
@SomeOtherParam int = 1,
@SomeThirdParam int
AS
SELECT @SomeOtherParam
GO

The person told me that when calling the proc and passing in NULL, the value would be NULL, he expected the value to be 1 since that is the default. Running the code like this will return the value NULL

EXEC prTest 1,null,3

In order to omit this parameter you can't be passing it in. If your parameter is the last one then you can just omit the value and pass in the first 2 parameters. If your parameter is not last,  then use named parameters instead of positional parameters. if you run the query like this, you will get back the value 1

EXEC prTest @SomeParam = 1,@SomeThirdParam = 3

Here is an example where the last parameter has a default

ALTER PROCEDURE prTest
@SomeParam int,
@SomeOtherParam int ,
@SomeThirdParam int =3
AS
SELECT @SomeThirdParam
GO

Running that proc with just the first 2 values will return the default

EXEC prTest 1,2

So to recap..... a default will only be applied when the parameter is not passed in, if you pass in a NULL, which is technically the absence of a value, NULL will be used

It is ten years ago that I wrote my first blogpost

This month ten years ago I wrote my first blogpost, that post is Split a comma delimited string fast!
Looking at that post, it makes me cringe, it is just a bunch of code and some comments, there is no other text anywhere. What was I thinking?

You can see all my posts for September 2005 here http://sqlservercode.blogspot.com/2005_09_01_archive.html

You will laugh at the one about pinning a table into memory by using DBCC PINTABLE

Go and take a look at the first couple of blogpost you ever wrote, are you satisfied with it, would you change anything?

Also leave me a comment with a link to your first blogpost and I will check it out......

Links to a handful of posts I created today

I got rid of my squarespace site and decided to repost the handful of SQL Server related posts here.  Some of this content might be useful so here it is as a repost.

Of course if I was really smart I would have done this before getting rid of the site. So some of this content I had to pull from Google cache and some images I had to regenerate.

Here are links to the posts:

Questions for SQL Server MVPs

My last day as a SQL Server MVP

Awesome SSMS shortcuts that will save you tons of time

PASS Summit 2014 pre-conference day 2

My 3 favorite sessions at PASS Summit 2014

What is a valid variable name in T-SQL

Questions for SQL Server MVPs

Since I decided I did not want to be awarded again as a SQL Server MVP,  I wanted to ask some fellow MVPs a couple of questions about the MVP program. I asked the following questions

1. How long have you been a SQL Server MVP?
2. What is the best part of the MVP program?
3. What are the two main reasons that you go to the MVP summit?
4. If you were not a MVP anymore, what would you miss the most?

The following people answered: Adam Machanic, Rob Farley, Paul Randal, Alejandro Mesa, Hugo Kornelis, Hilary Cotter, Itzik Ben-Gan, Brent Ozar, Mladen Prajdic, Arnie Rowland, Jamie Thomson, Thomas LaRock, Kevin Kline, Rod Colledge, George Mastros, Jes Borland, Louis Davidson, Denny Cherry

My own answers can be found here: My last day as a SQL Server MVP

Originally I had each MVP's answer separately but then I noticed that the  4 questions were repeated for every MVP, I decided to just list the question once and have all the answer listed under the question. Now all you have to guess is who gave what answer  :-)

A big thank you to the MVPs that took time answering all these questions

Here are the answers...enjoy. 

1: How long have you been a SQL Server MVP?

Since October, 2004
Since October 2006.
7 years
I have been a SQL Server MVP since Jan, 2007. I believe it is 8 years by now.
Since January 2006. So I am now in my ninth year. (Wow!!)
I have been an MVP for about 13 years now.
15 years
5 years - I got the MVP award back in 2009, and I've been tickled pink every time I get renewed.
since 2010
8 years
8 years I think
I earned my first MVP in April of 2009, so this year is my sixth consecutive award.
I was first awarded the MVP status for the 2003 cycle.
I've been an MVP since January 2010 - This is my 5th consecutive year
It will be 4 years in October.
I was awarded for the third time in April 2014
10 years so far...
6 years

2: What is the best part of the MVP program?

Interacting with other MVPs! I've learned a huge amount over the years from numerous MVPs -- famous ones like Itzik Ben-Gan, retired ones like Steve Kass and Anith Sen, and I can't forget some guy called Denis Gobo...

This is more answered by the fourth question. The best part of the program is not really something provided by Microsoft but the community that has formed with the SQL MVPs. But on the assumption that you want an answer that relates to something Microsoft provides, I'll go with the mailing list. Everything discussed there is under NDA, and it's a great place to learn and see what's going on. It's through the mailing list that Microsoft talks to MVPs when they want our opinion on some new feature, reflecting the fact that there are lots of people with Microsoft who do see us as a valuable group of people (not that non-MVPs are not - but the NDA needs to be there).

The free MSDN subscription

For me the best part is to have the opportunity to meet other MVPs, like you, that I have followed for long time either reading their blogs, forum answers, books, etc.

I also like the free MSDN subscription, the interaction with the SQL Server development group and the private list.

I hope you're not forcing me to pick just a single thing, because I can't - there are three things that I all consider to be one of the three best parts. These are, in no specific order: the MVP mailing list (I think the existence of this list is public, so you can include it in your post); the MVP summit; and all the contacts and opportunities that have opened to me through being in the MVP program and networking with other MVPs. (see below for a longer explanation of this)

The best part is the distribution list and being able to pick people brains with questions that I am too lazy to research or can't get good answers about. A close second is the recognition it gives me in getting work. Being an MVP means that I can screw up far more than regular folks and get a pass:)

Industry recognition and MSDN

More chances to talk to Microsoft employees, who are some of the smartest people I know. Forget talking to other MVPs - if you want to do that, go talk to them. You don't need to be an MVP to talk to another MVP - just walk right up to them at any SQLSaturday. They're friendly, open people who love helping others. After all, that's how they became MVPs!

the people you get to meet

Access to many on the product teams, and most especially, the global SQL MVP community.

Free MSDN sub

Best part? - The MVP distribution list. There I get to see many interesting questions and answers as well as interact with the product team at Microsoft.

While there are some monetarily significant benefits, such as the MSDN Universal License, and there are some prestige aspects of the award, the best part of the program for me is the ability to interface with the worldwide MVP community. I especially enjoy the private MVP discussion forum, where MVPs share many of their most challenging and interesting technology questions. MVPs are typically very knowledgeable and, in a sense, a cut above the average IT person. Consequently, I'm always learning from my colleagues in the program and advance my own skills through their efforts. 

The best part is the engagement with the product teams at Microsoft - both at the conference itself, and through the email forums

The MVP emails are, by far, the best part of the program.  It allows me unprecedented access to some of the fine folks that have actually written parts of the database engine.

I appreciate the direct access to Microsoft. Being able to get answers quickly and understand the future direction of the product is a huge benefit. 

The best part is the kind of instant family that you gain when you become an MVP. The communication between the MVPs and Microsoft employees we get is excellent, and not something you can easily get.  To be fair, it isn't that much more than you might get at any conference you attend, but it is far more regular and constant.

It is also kind of the worst part too. The fact is, while it is sort of like family, it is different in one very important way. You are family for life. The members of the MVP family have changed over the years quite a bit, and the Microsoft employees we interact with change constantly. The reasons vary, but I am always sad to see people like yourself fade off into the sunset, though at the same time I think it is awesome that people like you have the intelligence to know when things in your real family are more important.

Being able to work directly with the product group on new features and fixing problems with the product

3: What are the two main reasons that you go to the MVP summit?

I haven't attended an MVP Summit in several years, so I guess I have no reasons to go. When I did go I enjoyed interacting with other MVPs in person (usually over way too many beers). I get the same experience at the PASS Summit.

Actually, I haven't been to the last six MVP Summits. I went the first two years I was an MVP, in early 2007 and early 2008, but haven't been back since. It's ever such a long way to go from Australia, and while I love the MVP community, I actually prefer to be at events that include non-MVPs. I've been keen to go, because the interaction with the product group is really good. And of course, hanging out with the other MVPs is terrific. And the sessions on the internal workings of the product.

Socializing. We're usually not around for the Summit (we live in Redmond) and there's usually nothing new presented at the Summit that we haven't already been told some other way.

First to get together with other MVPs, second to interact with other MVPs. :)

The information exchange with the SQL Server dev team, and the many friends I meet there - many of them just once per year, at that summit.

I haven't gone to the summits. A couple of times I went to some MVP events for SQL Server after PASS and was kind of shocked by most of my peers. They were ego maniacs. I might go to the one this fall. I always toy with the idea of going but normally don't. We will see.

Hearing what the developers have to say and meeting my MVP friends (i.e., having beer together)

I don't, actually. I have a commitment with my family that I'll only travel one week per month, and for the last few years, the private MVP Summit has been the same month as public conferences that I'd rather attend. I'm really happy that this year Microsoft chose to host the MVP Summit in Seattle during the same week as the PASS Summit, and I'll be able to attend both. (Without coming home to find my belongings helpfully arranged on the doorstep.)

to see what'll be next. to see friends and (re)connect with others

Same as #2. Networking, personal contacts, and advanced product information. Toss in a smidgen of actually being asked about future directions –though, at times that seems more like a palliative effort than a ‘real’ engagement…

Learn about new stuff coming out, opportunity to grill the folks that build the produc

2 reasons? - To connect with fellow MVPs and to learn more about where MS is heading (and taking us with them, should we choose to go!)

While the MVP Summit is a great yearly event, it's not on my "must-attend" list. I think that I  manage to attend about 60% of the time. The two biggest reasons I DO like to attend the Summit are: 

1) ...to make introductions with the actual people leading the various efforts for each specific Microsoft SQL Server feature. Because people move around a lot within Microsoft, I like to make sure I know and get to meet the various program managers who are responsible for key areas of SQL Server product development, planning, and strategy. That way, if I ever have a question about the product, I know who to ask and, conversely, they know me. It's not a channel I exercise frequently - perhaps twice per year. But it's has great utility and value for a person who builds tools based on what Microsoft themselves are building.

2) ...to be a part of the feedback loop. Microsoft provides MVPs a couple avenues for feedback at the Summit. For example, there are often opportunities to see Microsoft's roadmap and future plans and to comment on them. That's cool, especially those times where you actually see Microsoft make a course correction in their product based on your feedback. Another example is when Microsoft provides the MVPs an opportunity to comment on an existing set of features and to influence when and how those features grow. 

The Microsoft SQL Server team does appear at other conferences over the course of the year, such as Microsoft TechEd and at the big annual PASS Summit. But I find the MVP Summit is the best place to get some one-on-one time and, in some cases, even make friends with the various team leads for SQL Server. That, for me, is priceless.

To be honest, I've only been once (2010). The travel involved from Australia makes it difficult, particularly when I also go the PASS summit, which is normally around the same time. I will almost definitely be going this year though, so I'm really looking forward to that

I have never been to the MVP Summit, and I regret it each year.

It's a rare conference I go to where I am just an attendee, and I get to sit in on all the sessions and learn tons of cool stuff. I also really like running the hills in Bellevue, WA in the mornings with fellow MVPs.

First, to see everyone in person that I see in email feeds, online, etc. It is always a great week of fun, talking about everything from technical stuff, to entertainment, to a wee bit of politics here and there. The MVPs are a very diverse bunch of folks that have a bit of every personality style so it is a great time.

Second (and the one that makes it a worthwhile expense), to gather material for future writing and future work. Getting information about stuff that is coming allows me to start to plan material I want to work on for the upcoming years. 

To see what the product team has coming out in the next release, and to swap war stories with the other SQL Server MVPs
  

  

4: If you were not a MVP anymore, what would you miss the most?

Interacting with other MVPs, naturally. The private mailing list (which used to be a newsgroup) has always been, by far, the number one benefit for me.

Good question... ;) 

Being an MVP is something I've got used to, and something which has become part of my identity within the SQL Server world. But then again, there are plenty of other things which also contribute to my profile - the style of conference session I give, the fact that I did that song in 2011, and so on. 

If I were not an MVP, it wouldn't change what I do, or who I am. I'd definitely miss the mailing list, and the community that has grown up through that, but I think the community would continue somehow, as it does through things like Twitter already. I'm already a long way away from most of my favourite people in the SQL community, and so I wouldn't stop being part of the community.

At the end of the day, I love the fact that Microsoft has the MVP program. I thoroughly enjoy the fact that I am an MVP, and wish more people could be recognised for their own contributions to the community. It saddens me that there are people who feel they have missed out every time they don't get awarded. But the SQL community is stronger than ever today, and MVPs only make up a small portion of that community.

Being part of the MVP group

The MVP summit for sure.

See Q2.

(*) Being an MVP has opened up lots of opportunities to me. Some directly, others indirectly. I started blogging, authoring, and tech editing, all because MVPs asked me to. I started speaking after being invited to attend a conference for free (MVP perk) and seeing how much fun that can be. And many of the places I have traveled to as a speaker are also the result of speaking with other MVPs. (The best example of this was in a bus at the MVP Summit. Niko Neugebauer, who I barely knew at that time but consider a close friend now, sat next to me, and his exact words were "what would it take to get you to speak in Lisbon".
And it's not just community activities leading to more community activities; it has also resulted in extra income. I don't think I would ever have been asked to be reviewer for a website that offers exam training without my tech editing experience. And without my years of speaking experience, I would not have become a Pluralsight author.

The distribution list. It would hurt my ego for a while but I would get over it.

Industry recognition and MSDN

The humor in Conor Cunningham's answers on the MVP email list.

probably the email distribution list

See #2 and 3 above.

For me, the greatest aspect of being a SQL Server MVP is the Global SQL Server MVP Community. An incredible group of very smart and wise folks that are enthused about the technology we work with on a daily basis, and more than willing to freely share their expertise and knowledge

MSDN sub

Miss? - Besides the MVP distribution list, I'd miss the MSDN subscription. I've been getting enterprise software for free for so long I'm not sure I'm ready for the sticker shock should I ever need to pay for something on my own.

Well, I'm a people person. I keep up with a lot of my friends' comings and goings based upon their presence in the MVP discussion forum. In some cases, there are MVPs who are quite active in the forum who do not post regularly on social media sites and so they'd literally fall of my radar without the forums. So I'd have to say that I'd miss the friendships and interesting people most were I no longer an MVP.

I'd definitely miss the email forums, where I can effectively drop in on conversations between some of the great minds in our industry, and the opportunity to ask questions of them

I would miss the status.  I have never been one to be particularly impressed by status, nor do I gloat about it.  However, being an MVP gives you a certain amount of respect.  All of my customers have a sysadmin and a network admin.  A few have DBA's, but most do not.  They look to me for advice because they know I will take care of them.  When I tell a customer that I will take care of them, they can trust that I will, and that I will do it the right way. I am not boastful, but I am confident in what I know (and not shy to say what I don't know).  All of this leads my customer in having greater confidence in me and my application.

I'd miss the advance knowledge. It's been nice to have access to early versions of software and provide feedback. 

Having something to worry about on October 1, which is my renewal date. I look forward every year to sweating it out...

But seriously.  The communication channels being an MVP opens are really valuable. I already mentioned them a bit, but even the information I gather by osmosis at times comes in handy. I don't know how many times I am in a discussion about trying to use or write about some feature in SQL Server, and I realize I know more than I thought I did.

The MVP mailing list as that gives the MVPs direct access to the product group