When developing a SQL Server Database it is easy to run the SQL statements directly via SSMS (SQL Server Management Studio) on the database. Although this practice can be okay for your particular setup, it may be useful to save database objects as a code project.
This will allow you to
(1) deploy the same database objects to multiple database environments,
(2) add source control to your database project and
(3) create CICD pipelines based on the project.
Here’s how to create a database project in Visual Studio (version 2019) using SSDT (SQL Server Data Tools) and deploy it to an Azure SQL database. Visual Studio is able to deploy the same project to other SQL Server versions as well. As long as the functionality that is used in the project is available in the SQL server version.
Table of Contents
Installing Visual Studio
Get Visual Studio from this website Visual Studio IDE, Code Editor, Azure DevOps, & App Center – Visual Studio (microsoft.com).
Installing SQL Server Data Tools
To create database projects with Visual Studio, you’ll need SSDT. How to install this functionality in Visual Studio, please follow the steps on Download SQL Server Data Tools (SSDT) – SQL Server Data Tools (SSDT) | Microsoft Docs.
Create a new database project
When you start Visual Studio you will see the options to either ‘open recent’, where a list is shown of recent projects, or ‘Get started’. To start a new project you’ll need to select ‘Create a new project’. This will open a wizard to create all kinds of new projects.
Note: I use dark mode in Visual Studio (Tools > Options > Environment > General > Color Theme ‘Dark’).
From the list with different types of projects you’ll need to select ‘SQL Server Datatbase Project’. The easiest way to find this is by typing ‘database’ in the search box at the top, select the ‘SQL Server Datatbase Project’ and click ‘Next’.
Give a name to your new project, and maybe change the location where the project will be saved. The default is ‘\source\repos\’ in your user directory. In most cases this will be fine. The solution name can be the same as the project name.
If you are planning to create more projects in one solution, you may want to change the solution name into something more generic. You may have multiple databases belonging to one solution. It may be feasible to contain al those database projects in one solution.
When you are happy with the choices, click ‘Create’.
Visual Studio will now create a folder on the disk with the solution and project files. You can view this folder by going to the path of your project (found under ‘location’) from the last step in windows explorer. This folder will contain all the files of the solution you just created. Projects are stored in different folders inside the solution folder.
On the right side of Visual Studio you’ll see the Solution Explorer.
All the projects, and the files belonging to the projects are shown here. You can switch between ‘project view’ and ‘folder view’. In folder view you are able to see all files in the folder as though you opened the folder with windows explorer. This can be useful, because not all files that you may want to see or edit are part of your project.
Visual Studio knows which files are part of the project by their entries in <projectname>.sqlproj’.
In the folder view, more folders and files are visible. This view can be useful when searching for missing projectfiles, files that you have created but somehow are not showing in the project view. Also, sometimes you may have files in the project folder that are not part of the database project, but need to be in version control.
Note: I like to have my explorer windows on the left hand side of the screen. You can easily modify the appearance of Visual Studio by dragging and/or nesting the different parts of the GUI.
All the print screens from now on will have this different view.
It is good practice to order database objects by schema, and object type. When importing a database structure from an existing database, Visual Studio creates these folders for you. But only for the database objects that are present in the imported database. When starting a database project from scratch, you have to create these folders yourself.
To start adding structure to your project,
(1) right click on de project folder and
(2) select ‘Add’ and then
(3) ‘New Folder’
Add a table to the project
Adding database objects is done by adding SQL files. Each object gets one file. These files contain create statements, like when you are creating database objects in the database itself. Though the build and publish actions in Visual Studio the different SQL files for the objects are combined in one large publish script.
Note: When publishing the database project to a real database, the location of the files is not important and has no meaning. So it is up to you to maintain order in the project by creating the files in the right folders.
To add a table to your project you need to create a file with the create table syntax:
(1) right click on the projectfolder in which you would like to add the file,
(2) select ‘Add’ from the menu,
(3) select ‘New Item…’,
type ‘table’ in the search box in the top right corner and select ‘Table’
As you can see in the image below, a new file ‘Table1.sql’ is added to the project. Also the file is opened immediately to show the Design window. Now you may add columns to the table with different datatypes through the designer. At the bottom you’ll see the T-SQL code that belongs to this table. You can also add columns by directly altering the T-SQL code.
After altering the table definition, save the file using CTRL+S, or by clicking the disk icon.
As you can see at (1), the file is still called ‘Table1.sql”. This does not matter at all for deploying your project to a real database, because I altered the name of the table in the T-SQL code (2), the table will be named ‘ExampleTable’.
However, to keep your project neat and tidy, and above all, manageable, you want to change the name of the file to match the name of the actual table:
(1) Select the file and right click
(2) Select ‘Rename’
Publishing the project to a database
When you want to see the project and its objects in a real live database, you can publish it. In a publish Visual Studio will check which objects exist in the target database and create them if they don’t exist yet. If you have altered objects, Visual Studio will generate alter statements.
For complicated alterations, like renaming tables or columns, Visual Studio can use ‘Refactoring’. The use of refactoring is not part of this blog, but it is a very powerful tool that can be frustrating at the same time. It depends on a refactor log and changes being made only once.
SQL server version setting
Before you publish the project, make sure the version of the database is correct. To do this, select the project in the solution explorer, right click and select ‘properties’. In the next window, select the version of SQL server you will be publishing to.
I will be publishing to Azure SQL database.
Publishing
To publish a project to a database select the build menu and then ‘Publish <projectname>…’
A new pop-up appears and select ‘Edit’ to enter the database connection string.
For this example I am using an Azure database. But you can use any SQL server database as long as you have enough permissions to perform the actions needed in de publish script.
Click ‘OK’, and then ‘Publish’….
If all went well, you will see the following message…
View the database from SQL Server Object Explorer
Visual Studio also lets you write T-SQL code and query it directly against the database. This is done in the SQL Server Object Explorer. If the SQL Server Object Explorer is not shown in your GUI, make it visible by selecting the menu item ‘View’ and then ‘SQL Server Object Explorer’.
Add a database connection by clicking on the ‘Add SQL Server’ button at the top of the Explorer window.
If you have just published the database, the connection details will be saved and visible under ‘History’. Otherwise you may need to resupply them, like when publishing a database.
Click ‘Connect’ to connect to the database.
When the connection is made, you can open a new file through the file menu, select sql file and click ‘OK’. The thing is that, other than in SQL Server Management Studio, in Visual Studio you’ll need to connect every new query window to a / the database. This is done at the top of the query window.
Import code from an existing database
You can create your own database project from scratch, but it is also easy to import existing databases into a Visual Studio Database project. I will show you how to import an existing database, based on the Wide World Importers sample database from Microsoft.
To learn where to get this database please visit:
- Install & configure DW WideWorldImporters sample database – SQL Server | Microsoft Docs
- sql-server-samples/samples/databases/wide-world-importers at master · microsoft/sql-server-samples · GitHub
Start by creating a new SQL database project in Visual Studio…
Then right click on the project and select ‘Import’, ‘Database’. You can also choose to import your database schema from a Dacpac or sql script.
In the new window, click on ‘Select connection’ and enter the connection parameters for the database that you want to import.
When the connection paramaters are correct, click ‘Start’. Visual Studio will start importing all the database objects into the project. When done, click ‘Finish’.
The Solution Explorer will show you alle the imported scripted database objects. Save the solution and you can start editing the imported database through Visual Studio SSDT.
