Creating a new SQL Server Database Project

In my previous post Migration-base vs State-based database development approach, I've shown two main approaches for database development. Today we will concentrate on state-based approach and learn how to start working with Microsoft Database Project.

Creating new project

Open Visial Studio → click 'File' → 'New' → 'Project'

If you are running Visual Studio 2017 navigate to 'Other languages' → 'SQL server' and select SQL Server Database Project.

After this operation Visual Studio will create an empty database project in the specified folder.

Creating table

To give good understanding for developers about business domains included into the project, the project should be well-structured. It is better to allocate specific schema for each of business domains and put all associated items (stored procedures, tables, triggers, etc.) with the schema in a separate folder. So let's create first business domain with the name 'Application'. At first we have to create folder with name 'Application' in solution. To do that, right-click on solution file → Add → New folder

After you create domain folder, let's create the file with schema. Right-click on 'Application' folder → Add → Script.

Specify an appropriate name for the schema file and make it part of build process. That will allow us to identify issues with SQL code during project compilation.

Application.Schema.sql code

CREATE SCHEMA [Application]
    AUTHORIZATION [dbo];

GO
EXECUTE sp_addextendedproperty 
    @name = N'Description', 
    @value = N'Tables common across the application. Used for categorization and lookup lists, system parameters and people (users and contacts)', 
    @level0type = N'SCHEMA', 
    @level0name = N'Application';

Now we are ready to create our first table. Let's create 'Tables' folder in 'Application' folder and create People table there.

People.sql code

CREATE TABLE [Application].[People] (
    [PersonID]     INT             NOT NULL,
    [FullName]     NVARCHAR (50)   NOT NULL,
    CONSTRAINT [PK_Application_People] PRIMARY KEY CLUSTERED ([PersonID] ASC)
)

GO
EXECUTE sp_addextendedproperty
    @name = N'Description',
    @value = 'Full name for this person',
    @level0type = N'SCHEMA',
    @level0name = N'Application',
    @level1type = N'TABLE',
    @level1name = N'People',
    @level2type = N'COLUMN',
    @level2name = N'FullName';

GO
EXECUTE sp_addextendedproperty
    @name = N'Description',
    @value = 'Numeric ID used for reference to a person within the database',
    @level0type = N'SCHEMA',
    @level0name = N'Application',
    @level1type = N'TABLE',
    @level1name = N'People',
    @level2type = N'COLUMN',
    @level2name = N'PersonID';

GO
EXECUTE sp_addextendedproperty
    @name = N'Description',
    @value = N'People known to the application (staff, customer contacts, supplier contacts)',
    @level0type = N'SCHEMA',
    @level0name = N'Application',
    @level1type = N'TABLE',
    @level1name = N'People';

Finally, we created our first SQL Server Database project. Congrats! Source code related to this tutorial could be found using following link https://github.com/aliakseimaniuk/blog-examples/tree/master/WideWorldImporters/01-Create-Database-Project.