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

Table designer (10 days of SSDT – Day 4)

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!

Every database contains tables therefore not surprisingly one of the most fundamental features within SSDT is the table designer which I think of as being split into three areas, Script, Columns & Child objects:

image

Each table definition is stored as DDL in a text file with a .sql suffix however SSDT understands when a file contains a table definition and hence by opening up such a file the table designer will (by default) get displayed (you do have the option to right-click on a file and just show the raw script). The DDL for the table is shown in the script section of the table designer while the Columns and Child objects section are a graphical representation of that DDL. The significance of having a graphical UI over the script is that the two stay in sync with each other, any change you make in one will get reflected in the other. For example, if I were to check the ‘Allow Nulls’ checkbox for [Id] field the NOT NULL in the script would immediately change to NULL. If I define an index in my script then that will appear under Indexes in the Child objects section.

Its intuitive to think that the Table Designer displays DDL from only one file but actually that’s not the case, its a lot smarter than that. Take the following example where I have a table defined in one file (Product.table.sql) and the primary key for that table defined in another file (Product.PrimaryKey.sql):

image

Notice how the primary key [pkdboProduct] is still displayed in the Child objects section even though it is defined in another file. Notice also how I can choose which file the script section displays:

image

That’s the basic functionality of the Table Designer.

@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 Monday, September 22, 2014 8:52 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

 

Jason Kohlhoff said:

Jamie,

Love the blog series so far.

I'm interested to hear about you experience with SSDT and source control, branching/merging, build, deploy, pre and post scripts, static data, etc.

Any chance you're going to cover SSDT and TFS source control?

Thanks,

Jason

September 22, 2014 3:05 PM
 

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
 

jamiet said:

Hi Jason,

Its not in the posts I've written so far but I'll put it on the list.

cheers

Jamie

September 23, 2014 2:54 AM
 

jamiet said:

Jason,

You might find this useful:

Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

http://sqlblog.com/blogs/jamie_thomson/archive/2013/01/27/continuous-deployment-of-ssdt-database-projects-to-windows-azure-using-team-foundation-service.aspx

JT

September 23, 2014 2:55 AM
 

Jason Kohlhoff said:

Thanks for the info, Jamie.  You rock dude.

September 23, 2014 10:16 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
 

Jag said:

Thanks Jamie!

How did you get the PK to appear in a separate file? Adding a PK or index in Designer just adds the DDL to the main table script.

October 1, 2014 11:39 AM
 

Peter Schott said:

@Jag - that could be a holdover from the old VS DB Project format where everything was broken out into separate scripts. If you want to do something similar, you can right-click in the project to add a new constraint as a separate file. To me, it's easier to manage as part of the table definition, but I can see some might prefer splitting each object into its own file.

October 1, 2014 2:12 PM
 

jamiet said:

Jag,

Cut and paste dude, cut and paste 😊

October 2, 2014 1:59 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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement