Partager via

SQL Server Data Tools (SSDT)

  • Article

SQL Server Data Tools (SSDT) is a Visual Studio Suite that allows database users to build, debug, maintain and refactor database objects. It supports code navigation, intellisense and provides for automatic source control. With SSDT, databases can be published just like websites. The previous two sentences may make you feel that if you are experienced with Visual Studio, you are at home in SSDT. If you are a hardcore Server Management Studio (SSMS) user, an SSMS like feature is available within SSDT.

In order to download use link - https://msdn.microsoft.com/en-US/data/tools

SSDT supports all versions of SQL Server including SQL Server 2005 and above. It also supports all editions of SQL Server including the express edition.

Let us look at a few example of how SSDT helps with “connected development”.

To get started  

1. Open SQL Server Data Tools.

 

2. Connect to an instance of SQL Server by providing appropriate connections. Expand to see databases as you would within SQL Server Management Studio.

 

 

 

 

1. Connected Development – Altering Views

  • In this example, I am connected to DinnerNow database. 
  • Double click on the view.
  • The create script of the view definition is opened up.
  • Remove a few columns
  • Hit the update Button

 

You get two options, to either generate script for the same or to update the database directly

 

 

Let us take a look at the script generated.

  • Script is generated in SQLCMD mode
  • Scripts are parameterized so that it can be passed around
  • Scripts are executed in a transaction i.e. all the changes are updated or none
  • The statement automatically changes to “Alter View”

 

 3. Connected Development – Altering Tables

 

  • Double click on a table
  • Open Window gives us Table designer as in SSMS, T-SQL Script and a dependency Window which tells us the dependencies of columns selected in the designer.

  •  In the table designer, a number of properties such as description, datatype, length, allow nulls, default values, identity, Sequence can also be set

  • Changes can be made either to Designer and /or T-SQL
  • New Indexes can also be added with a right click using the dependency window

  •   Selecting a column will highlight what the dependencies are on the column. This will be of great value to see the impact of the changes

 

Connected Development – Executing Scripts

Any script that can be executed in SSMS, can also be executed within SSDT. Click on new query and execute the same. While creating a new SQL object, new Object window can also be used. This will perform additional checks for conflicts when executed.

 

 

 

When a script is executed, Data Tools Operation window provides Preview, View and Post View of the script

 

This provides warnings, displays exact script and results of the execution in Post view

Lastly, SSDT is a free tool. Start using the same and provide feedback to me in this blog.

 

In the next part of SSDT blogs, I will provide examples of how a project based development will work

Please find Part2 of the blog at

blogs.msdn.com/.../sqlserverdatatools-part2.aspx

blogs.msdn.com/.../sqlserverdatatools-part2.aspx

Comments

  • Anonymous
    April 03, 2013
    Here is part2 of SSDT usage where Databases can be treated as Projects i.e. Project based development blogs.msdn.com/.../sqlserverdatatools-part2.aspx

  • Anonymous
    July 16, 2015
    Your blogs are quiet useful, I am using SSDT and upgrading my SSRS reports from 2005 to 2012. SSRS 2005 uses IIS, When we enable the compression at IIS level, the data returned by reporting service is compressed. However in SSRS 2012, it uses its own Http.sys runtime and doesn't returns the data in compressed/gzip format. How do I encode the http response to compress the data before return for mimetype: text/plain. I have asked this question below link, but no pointers yet. social.msdn.microsoft.com/.../how-do-i-compress-the-reporting-service-response-in-ssrs-2012