Every SQL Server database developer is aware of SQL Server Management Studio (SSMS) but there is one more important tool SQL Server Data Tools (SSDT) that can bring a lot of benefits to them.
SSDT is based on Visual Studio
There are many ancillary jobs that a database developer performs apart from writing T-SQL queries, creating tables, stored procedures, functions, triggers, views etc. To put it simple,
- Schema comparison
- Data comparison
- Schema versioning / Source Code Management
- Code analysis / Code review
- Code refactoring
Unfortunately, these features are not available in SSMS.
So, the question is how to handle these?
There are few third party tools which offer these solutions. Red-Gate is pioneer in providing the useful database tools for SQL Server to easily manage, maintain and administer the SQL Server database.
Is there a need to go to third party vendors to solve these? No, as Microsoft itself provides support to all the above features through SSDT. Any developer who is familiar with Visual Studio will find it very easy to use. And it’s free. All versions of SSDT are free to use as far as I know. Also, it is also possible to work with SQL Databases in offline mode using SSDT.
Please read https://msdn.microsoft.com/en-us/library/mt204009.aspx
Quoted: ” SQL Server Data Tools (SSDT) is a modern development tool that you can download for free to build SQL Server relational databases, Azure SQL databases, Integration Services packages, Analysis Services data models, and Reporting Services reports. With SSDT, you can design and deploy any SQL Server content type with the same ease as you would develop an application in Visual Studio.
This release supports SQL Server 2016 through SQL Server 2005, and provides the design environment for adding features that are new in SQL Server 2016.
SSDT is based on Visual Studio and co-released with SQL Server as a free web download. “
Getting started with SSDT
- Start the “Visual Studio” or “SQL Server Data Tools” from start menu
- Open the “SQL Server Object Explorer”
- Add SQL Server from “SQL Server Object Explorer”
- Verify the database
Schema comparison
- Select the desired database from “SQL Server Object Explorer”
- Right click the database you want to compare and click on “Schema Compare”
- Provide “Source Database” and “Target Database”
- Click on “Compare” button
- Review the difference
- Generate Script (if required)
- Update Target (if required)
Warning: If working in connected mode then “Update Target” will directly apply the differences to the target database. Pay due attention!
This option is also available under “Tools -> SQL Server -> New Schema Comparison” menu
Data comparison
- Select the desired database from “SQL Server Object Explorer”
- Right click the database you want to compare and click on “Data Comparison”
- Provide “Source Database” and “Target Database”
- Choose the “Data Comparison Options”
- Click on “Next” button
- Select the desired Tables / Views
- Click on “Finish” button
- Review the comparison result
This option is also available under “Tools -> SQL Server -> New Data Comparison” menu
Working in disconnected/offline mode
- Select the desired database from “SQL Server Object Explorer”
- Right click on the database
- Select “Create New Project”
- Provide the “Target project name” and “Target project location”
- Click on “Create new solution” (if you want new solution) else the project will be added to the existing solution
- Click on “Create directory for solution” (if you want different directory for your solution)
- Click on “Add to source control” (if you want to add your project to TFS or Git for Versioning / Source Code Management)
- Select the desired “Folder structure”
- Click on “Start” button
- Wait for for the process to complete
- Click on “Finish” button
- Verify your database project in “Solution Explorer” and “SQL Server Object Explorer”
Schema versioning / Source Code Management
Only two option are available for Source Control – Team Foundation Version Control (TFS) and Git. Choose the desired one.
Solution can be added to “Source Control” from “Solution Explorer” by right click on solution -> Add Solution to Source Control.
Individual project can be added to “Source Control” from “Solution Explorer” by right click on project -> Source Control -> Add Solution to Source Control.
Code analysis
Code analysis is one of the important feature that allows to take preventive measure than defensive actions. Code analysis is enforced upon the build event. It can also be run manually. There are certain predefined set of rules that can be enforced during build event. Project will not build successfully, if the enforced rules are not successful.
To enable the code analysis on build, follow the following steps,
- Open the Project Properties (Right click the Project and click on Properties)
- Click on “Code Analysis” tab
- Click on “Enable Code Analysis on Build”
- Click the desired rules from the following categories
- Microsoft.Rules.Data.Design
- Microsoft.Rules.Data.Naming
- Microsoft.Rules.Data.Performance
- If you want SSDT to throw error instead of warnings then click on “Treat Warning as Error” against the Rule
Note:- To manually run the code analysis, right click the project from solution explorer -> Run Code Analysis.
In SSDT 2015 / Visual Studio 2015 there are three categories of Rules that can be enforced on Build for Code Analysis,
- Microsoft.Rules.Data.Design – It has got 6 different rules
- Avoid SELECT * in stored procedures, views, and table-valued functions.
- Consider using SCOPE_IDENTITY instead of @@IDENTITY.
- Avoid using types of variable length that are size 1 or 2.
- Avoid using deprecated syntax when you join tables or views.
- Specify values for output parameters in all code paths.
- Maintain compatibility between data types.
- Microsoft.Rules.Data.Naming – It has got 3 different rules
- Avoid using special characters in object names.
- Avoid using reserved words for type names.
- Avioud using sp_ as a prefix for stored procedures.
- Microsoft.Rules.Data.Performance – It has got 5 different rules
- Avoid using columns that do not have an index as test expression in IN predicates.
- Avoid using patterns that starts with “%” in LIKE predicates.
- In the comparison, simplify the expression that includes indexed columns.
- Use ISNULL(column, default value) on nullable column in expression.
- Extract deterministic function calls from WHERE predicates.
Code refactoring
Renaming an object or moving specific set of objects from one schema to another, which have dependencies is not an easy job. If you have missed any of the dependent objects then be ready to pay a huge penalty!
Renaming or moving an object(s) is made easy by SSDT through Refactoring . You simply right click the object -> Refactor -> Choose the desired option. It will generate a preview of the changes for the selected option to review, and if review is good to go then you can apply the changes.
Conclusion
SSDT brings various features like schema comparison, data comparison, code refactoring, code analysis, working with SQL Server database in disconnected/offline mode and allowing database to add to source control for versioning, in a single tool embedded in popular Visual Studio environment which is familiar to almost all the Microsoft professionals.
Database Code versioning is least focused and most critical area which has been simplified by Microsoft with the introduction of SSDT.
Disclaimer
This is a personal weblog. The opinions expressed here represent my own and not those of my employer.
All data and information provided on this site by Author is for informational purposes only. Author makes no representations as to accuracy, completeness, correctness, suitability, or validity of any information on this site and will not be liable for any errors, omissions, or delays in this information or any losses, injuries, or damages arising from its display or use.
Please don’t use any of the examples discussed in this article in Production without evaluating it based on your need.
SQL Server Carpenter 
That’s quite helpful. Thank you.
LikeLike