SQL Server Data Tools (SSDT)
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