This is my personal blog. The views expressed on these pages are mine alone and not those of my employer.
The other day while studying I had the need to create a table and populate it with a small set of random data. I just so happened to have “Show Actual Execution Plan” enabled and when I executed the insert statement to populate the table (using a UNION SELECT) I was quite shocked at the plan generated.
This got me thinking that there must be a better, more efficient way to populate a table with data.
The set-up:
The set-up here is very basic. I wanted to test an insert into a table that had a clustered index (so we can see the effect of the insert which needed to update the index). I didn’t care what the values were or what the index was for the purpose of this test.
Create a test table:
USE tempdb
GO
CREATE TABLE tempvalues (
Value1 NUMERIC(5,5)
,Value2 NUMERIC(5,5)
,Value3 NUMERIC(5,5)
,CONSTRAINT [PK_tempvalues] PRIMARY KEY CLUSTERED (Value1, Value2, Value3));
GO
Insert some data using UNION SELECT
INSERT INTO tempdb..tempvalues (Value1, Value2, Value3)
SELECT 0.13601, 0.11622, 0.40041
UNION SELECT 0.28688, 0.33935, 0.58548
UNION SELECT 0.66694, 0.78595, 0.458
UNION SELECT 0.18889, 0.34067, 0.75255;
Here is the execution plan using a UNION SELECT:
As you can see there is a “Constant scan” operator for each row you are inserting. Now suppose you are doing this for 500 rows. Here is a partial extract of what the execution plan would look like:
As you can see there are now 500 “Constant Scan” operators which has flooded the graphical execution plan!
As we are working with such a small dataset, a more preferable way to do this kind of insert would be to use a Table Value Constructor (TVC).
What is a TVC?
Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified in the VALUES clause of the INSERT statement, in the USING <source table> clause of the MERGE statement, and in the definition of a derived table in the FROM clause.
Source: http://msdn.microsoft.com/en-us/library/dd776382(v=sql.105).aspx
Where would you use a TVC?
When constructing a static data set for insertion or selection. E.G. Reference data scripts.
Notable Limitations?
You can only specify a maximum of 1000 rows in a TVC.
TVC Syntax:
INSERT INTO tempdb..tempvalues (Value1, Value2, Value3)
SELECT Value1
,Value2
,Value3
FROM (
VALUES (0.13601,0.11622,0.40041)
,(0.28688,0.33935,0.58548)
,(0.66694,0.78595,0.458)
,(0.18889,0.34067,0.75255)
) AS x (Value1, Value2, Value3)
Here is what the execution plan looks like:
And even with 500 rows, the execution plan is the same:
Here are some stats to show the efficiency different between the 2 methods:
UNION SELECT (Inserting 500 rows)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 468 ms, elapsed time = 483 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table ‘tempvalues’. Scan count 0, logical reads 1005, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(500 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 140 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table Value Constructor (inserting 500 rows)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 116 ms, elapsed time = 116 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table ‘tempvalues’. Scan count 0, logical reads 1005, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(500 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 18 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Summary:
It seems conclusive that using the TVC to insert small datasets (<1000 rows) is more efficient than using the UNION SELECT. Aside from having less processor and execution time it’s worth noting that the subtree cost and cached plan size would be smaller too.
At the end of the day any performance gain is worth it right?…
FINALLY!! After much anticipation Microsoft have finally introduced code snippets into the latest version of SQL Server.
How much time does one spend writing out the same code over and over to create objects or how much time is spent fixing errors made by copy and paste!
Code snippets eliminates these problems and creating them is simple.
Where are they located?
Code Snippets are in 2 locations.
- Default Snippets - ..\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033
- Custom Snippets - ..\Documents\SQL Server Management Studio\Code Snippets
If you create your own the default save location will be in the Customer Snippets directory but it is very possible to edit and/or add to the Default Snippets.
Snippets have a file extension of .SNIPPET but are simple XML files.
General XML layout.
locDefinitions / locTag - Title, Description, Author, Tool-tip. These will be displayed in the code snippets manager and in the snippets selection list
Next are the Literals - these are pretty much variables which can be used in the CDATA part of the file. The format of a Literal is:
- ID - The name of the Literal (variable)
- Tooltip - A pop up when you mouse over the word giving a description
- Default - The default value of the variable
These literals are then used to build the SQL statement:
Note that the Literals start and end with the $ symbol.
This will produce the following output:
Notice the pop-up when hovering the cursor over the data type.
It seems that one of the main purposes with using Literals are the ability to tab through them. As you can see from the above image, each Literal is highlighted yellow showing that they are tabbable. Unfortunately if you use the same Literal multiple times throughout your script you can only tab the first one. Tabbing order is defined by first literal in the script to the last. Pressing the “Enter” key will accept the changes to the Literals and the ability to tab will end.
Creating your own Snippets
The above is the basic layout of the .SNIPPET file, creating your own is easy, either create a new text file in a new folder (see below for creating a new folder) or an existing folder, fill out the Title, description etc, define any literals and fill out the CDATA. Rremember to save the file as .SNIPPET. If you are not creating the new file under an existing directory under the 1033 folder then you will need to open the Snippets Manager and “Import” your new snippet file. When importing your snippet file you will get the option to select an existing folder to save it in.
It’s worth noting that if you are creating your own files under an existing folder then once the file is added to the folder (and it’s valid) then the file will immediately be available to use.
Taking code snippets one step further
Code snippets are great as they are but its possible to make them even better by using parameters. Parameters are not new, they are used in SQL templates. Pressing Shift+CTRL+M will bring up the “Specify Values for Template Parameters” box where you can quickly fill out the templates parameters which can save huge amounts of time!
The general syntax for a parameter is:
<Parameter Name, Type, Value>
e.g.
<Table Name, sysname, Enter the name of the table>
This would appear as so:
Its possible to use this parameter format in the CDATA when building your SQL statement. Although it’s not exactly eye friendly, it makes filling out your snippets easier then ever !
Here’s one example of one of my Stored Procedure:
By using these parameters I can easily fill out the most boring parts of my stored procedure in under a minute leaving just the nice meaty stuff left to do.
Adding folders to the Snippet Menu:
To add a new folder to the snippet menu simply reference it in the SnippetsIndex.xml file.
Add a new SnippetDir to the language element.
<SnippetDir>
<OnOff>On</OnOff>
<Installed>true</Installed>
<Locale>LOCALE_ID</Locale>
<DirPath>%InstallRoot%\VSTSDB\Snippets\%LCID%\Name of the folder</DirPath>
<LocalizedName>Name of the folder</LocalizedName>
<SnippetDir>
Once added, create a folder of the same name in the root of the 1033 folder.
Next open the Code Snippets Manager and add the newly created folder. This will also import any files under the folder.
And there you have it! Creating your own SQL 11 Code Snippets.
PART 2
Deploying a SQL Server Database
One of the great things about having your database projects in source control and editing in Visual Studio is the ability to create static data in the post deployment script. Imagine having several databases which all have several tables with hundreds of rows of static data! However, its not just static data that can be put in the pre / post deployment files, it’s any valid SQL statement. The pre and post deployment files will be created as part of the deployment process.
There are a few options out there for deploying a SQL Server database, some of the options i’m personally aware of are by using:
- Schema Compare tool in Visual Studio
- Visual Studio deploy to deploy direct to a target server
- VSDBCMD.exe
- SQL Packager
- TSQL scripts
- Powershell
I’ve used all except SQL Packager and Powershell, but I’m going to talk a little about VSDBCMD only.
What is VSDBCMD?
VSDBCMD is a command line tool that can take in files as input and creates or updates database schemas in a given target database
VSDBCMD.exe is called from the command line, the following line is an example of how to execute it (this should be all on one line, but i’ve split it into new lines for readability)
vsdbcmd.exe
/a:Deploy /Manifest:”.\ExampleDbProjectImport.Deploymanifest”
/p:DeploymentConfigurationFile=”.\ExampleDbProjectImport_Database.sqldeployment”
/p:sqlcommandvariablesfile=”.\ExampleDbProjectImport_Database.sqlcmdvars”
/cs:”Data source=localhost; integrated security=true”
/dd
For a list of VSDBCMD commands see this MSDN link http://msdn.microsoft.com/en-us/library/dd193283.aspx
There’s also some dependencies which should be pointed out in order for this tool to execute successfully. These are (as described in Dureks MSDN bloghttp://blogs.msdn.com/b/dukek/archive/2009/10/19/implementing-a-wix-installer-that-calls-the-gdr-version-of-vsdbcmd-exe.aspx)
- Microsoft .NET Framework 3.5 SP1
- SQL Compact 3.5
- SQL 2008 Server Management Objects
Deploying
The deployment settings for a database project are set when you create the project, however it is still possible to change these settings. Right click the database project, choose properties and click the “Deploy” tab.
The 2 main options here are in the “Deploy Action”. You can either create a deployment script or create a deployment script and deploy to a database. If you intend to use the VSDBCMD tool then you will want to create the deployment script only.
To deploy the database, right click the project and click deploy. If doing this for multiple databases right click the solution and click deploy.
There will be several files created as part of the deploy process.
- *.dbschema - This contains the complete description of the database in the form of XML
- *.deploymanifest - This is an MSBuild file describing how the database schema should be deployed and also contains links to other files
- *._Database.sqlcmdvars - This defines custom variables for the deployment script
- *.Database.sqldeployment - This configures the deployment options (i.e, which database features to set, which to ignore etc)
- *._Database.sqlsettings - This contains database level settings like ARITHABORT, QUOTED_IDENTIFIER etc
- *.PreDeployment - This will contain SQL statements to be executed on the server before the database is created/altered
- *.PostDeployment - This will contain SQL statements to be executed on the database after it has been created/altered
As you’ve seen from an above example on how to execute VSDBCMD.EXE 3 of these files are used in the command line but all are used - the *.deploymanifest will call the files which are not in the command line.
Run the VSDBCMD.EXE tool against these files and the database will be deployed to your target server in a minute or so.
To Summarise…
Having the databases in source control has enabled me and the rest of the team to work on a common database without having different schema changes all over the place. Its simple to manage all database objects from database level logins and permissions to tables and stored procedures and then simply click deploy to create your deployment scripts.
For our project we have 7 databases which we need to deploy, before this tool it used to be a pain getting database backups, restoring them and then finding out which deltas we need to apply to get us up to the latest version. Now with this deployment strategy a team member can simply deploy the solution, go to the output location, run the .BAT file (which is held in source control with the databases), the .BAT file will prompt the user for the target db server and voilà the databases are deployed!
Sooner or later during a company project comes the time to start thinking about databases… and lets face it… databases usually get thought of last!
When I first joined my team in my current company we had no clear process for managing databases and deltas. SQL scripts were being emailed around teams with all sorts of schema changes. Needless to say that eventually this got out of hand!
To start with we needed to make sure all our databases were stored in source control. Having it stored centrally in somewhere like TFS would enable teams working on these databases to work in an isolated environment for database development and allow team members to get the latest of any changes made. A great way to ensure everyone is working on the same code right?
In this 2 part post I will go through some of the ways to Manage and Deploy SQL server databases using Visual Studio 2010.
PART 1
Managing a SQL Server Database
Creating your database project is very simple. Heres a quick example:
Create a new project and choose “Database sql server” from the template list. In the project type list choose the project type which best fits your needs. In this example i’m using “SQL Server 2008 Wizard” (I’m not adding this project to source control at this time as the machine I’m working on doesn’t have it installed)
Click next to enter the “Project Properties” page. On this page you specify the type of project you want to create and how to organize the files in the project.
Click next again to enter the “Set Database Options” page. Here you configure the database level options such as enabling full-text search, database collation, default schema for new object etc.
Next again takes you to “Import Database Schema” page. Here you can choose to import an existing database or click next to skip this stage.
And finally next again to set the “Configure Build and Deploy” options. Here you can set whether to create ONLY deployment scripts when the solution is deployed or create scripts AND deploy to a target server when the solution is deployed amongst other deployment options.
Click next then finish to complete the process. Voilà - you have successfully created a database project!
Once the databases are in source control it becomes very easy to manage the database objects. All your database objects will appear under the “Solution Explorer” or “Schema View” windows like so:
Directly under the database project is “Properties”. This is where you can set your database level options. These options are what you will need to care about for when you deploy your database. If you have multiple databases under your database solutions remember each database will have its own set of property files.
- “Database.sqlcmdvars” properties are set when you deploy the database solution. This contains the database name, the data file path and the log file path
- “Database.sqldployment” contains a whole host of options about what you want to do when you deploy, for example, deploy database properties, always recreate the database, execute deployment scripts in single-user mode, back up database before deployment. There’s also a tonne of advanced options such as AbortOnFirstError, ScriptDatabaseCollation and VerifyDeployment.
- “Database.sqlpermissions” is used to set database and object level permissions
- “Database.Settings” is where you configure your database level settings. Here you can specify your database collation, set the default filegroup, set compatibility level and enable change tracking (depending on version of SQL) etc.
Further down the list in solution explorer you can create Data Generation Plans, Schema Comparisons (very useful tool - but I wont go into that now), schema objects and scripts.
Schema objects is probably where all the action will take place. Here you can create and edit database level objects like security, triggers, service broker and storage and schema level objects like tables, functions, stored procedures, views, Synonyms etc.
The last section in the project is for scripts. Here you can create Pre and Post deployment TSQL / SQLCMD statements. As the names suggest, these are the scripts that will run before and after the database is deployed. You can add your own sql scripts to these pre and post folders but remember that they will also be run against your target server with the database deployment. If you do want to store your own scripts but not have them run when you deploy, either create a separate folder to store them or set the “Build Action” to “Not in Build” (it’s probably best to check the build action anyway just incase).
Visual Studio 2010 has a whole host of features for database projects including a nice feature called “Data Generator”.
Data Generator basically does what it says on the tin… It generates sample data, as per this link http://msdn.microsoft.com/en-us/library/aa833267.aspx
What I want to achieve:
I want to be able to place some sample data that has some sort of valid postcode (valid in the sense of either 2 letters 1 number 1 number 2 letters OR 2 letters 2 numbers 2 numbers 2 letters). I don’t particularly care if the postcodes are valid UK ones or even exist for the purpose of this.
As part of the above MSDN link there is a SQL script which is used to create a sample database “DinnerNowDataGenerator”. The table “Restaurant” is what I used to test my Regular Expression.
If you have been following the MSDN post you will eventually get to the Data Generation Designer Screen:
Highlight the PostCode column and change the Generator properties to “Regular Expression”. Then in the properties of the PostCode column enter ([A-Z]{2}[0-9]{1,2})([0-9]{1}[A-Z]{2}) as the Expression. This will choose 2 random letters between A-Z, 1 or 2 random numbers between 0-9, 1 random number between 0-9 and 2 letters between A-Z.
