THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

What is a project? (10 days of SSDT – Day 3)

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server databases nearly every day since then. In that time I’ve learnt a lot about how to make best use of the tool and I want to share that experience in a new series of blog posts called “10 days of SSDT”. I shall be publishing a different blog post every day for 10 days, each day revealing something that you may not know about SSDT. I hope you enjoy the series and contribute via the comments!

In Day 1 – Online and offline I explained how the offline (aka disconnected) development experience in SSDT makes use of the Solution Explorer pane inside Visual Studio. In the screenshot below I show one Visual Studio 2012 solution containing two projects, both are SSDT projects:

image

An SSDT project pertains to a SQL Server database that either already exists or you may want to create in the future. It is a container for lots of DDL statements that define all of the objects that do/will exist in that database. That’s an important point so I’ll state it again, the project contains DDL statements. It does not contain procedural code that defines how to create objects, for example you are not going to write anything like this in an SSDT project:

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Customer')
BEGIN
   CREATE TABLE
Customer(
      
CustomerId INT
  
,   CustomerName VARCHAR(50)
   )
  
PRINT 'Created table Customer';
END

In an SSDT project you only define DDL, so for the table defined above you only need the CREATE TABLE statement:

   CREATE TABLE Customer(
      
CustomerId INT
  
,   CustomerName VARCHAR(50)
   )

This is a very important point regarding the declarative nature of SSDT.

You are free to organise your DDL scripts within an SSDT project however you like. For a very simple database you may just want to place all your scripts under the root of the project however for something more complex you may want to come up with a folder structure that reflects how you like to organise your code, a folder per schema is a common convention. In the following screenshot you can see that I have shown both approaches:

image

Each SSDT project contains a series of properties that govern the behaviour of the SQL Server database within it. These properties are accessed by selecting a project within Solution Explorer, right-clicking and selecting properties (or press ALT+Enter), then from the resulting Properties page selecting “Database Settings”.

SNAGHTML1499a99e

As you can see here is where you should set pertinent properties of your database such as collation, various SET options, and many other things.

So in short, an SSDT project pertains to a SQL Server database and all the objects within it. Later we’ll see how SSDT turns a project into an actual database via a process called publishing. As always, if you have any questions please put them in the comments section below.

@Jamiet

If you want to learn more about SSDT then come along to my training course Introduction to SQL Server Data Tools in association with Technitrain on 23rd/24th February 2015 in London. There is a discount if you register before the end of 2014.

Earlier posts in this series:

Published Friday, September 19, 2014 9:39 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Cara said:

Thanks for this Jamie!  I just started working with SSDT's so this is really well timed for me.  Looking forward to the upcoming posts!

September 19, 2014 9:05 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 22, 2014 2:55 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 23, 2014 2:44 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 24, 2014 3:06 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 25, 2014 3:36 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

September 26, 2014 2:40 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

October 1, 2014 2:37 AM
 

Jamie Thomson said:

SQL Server Data Tools (SSDT) was released in Spring 2012 and I have been using it to build SQL Server

October 13, 2014 2:57 AM
 

Koos said:

Hi Jamie,

You state that all you store inside a database project are DDL statements. But what about post-deployment scripts? Or initial fills like configuration tables?

Would you exclude scripts for initial filling from your project? Or would you store these inside (for example) 'initial filling stored procedures' or something alike?

Would love to hear your opinion about this. Thanks in advance!

May 6, 2015 6:32 AM
 

jamiet said:

Hi Koos,

Thanks for the comment.

"You state that all you store inside a database project are DDL statements."

Hmm, well, that's not exactly what I said. I said:

"the project contains DDL statements. It does not contain procedural code that defines how to create objects"

which, I would argue, is subtly different.

Nevertheless it could be considered slightly ambiguous so for the avoidance of doubt let me say this. A database project stores DDL scripts to define the database objects plus pre/post-deployment scripts that can be used to affect the database in whichever manner you choose (typically this means populating the database with data).

So to answer your questions:

Would you exclude scripts for initial filling from your project? No

Or would you store these inside (for example) 'initial filling stored procedures' or something alike? Writing stored procs that populate required data into the tables is a valid approach and I think it makes sense to call such stored procedures from post-deployment scripts.

Hope that helps.

JT

May 6, 2015 6:46 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement