Share via

Walkthrough: Create and Deploy a New Version-Controlled Database

  • Article

In this walkthrough, you create a simple database that contains two tables and a stored procedure. This process requires you to create a database project, create database objects in that project, and then build and deploy your changes to a database server. By creating a database project, you can put your database schema under version control by putting the database project under version control.

The major steps for this walkthrough are as follows:

  • Create a database project.

  • Create the database tables.

  • Create the indexes, keys, and constraints for those tables.

  • Create a stored procedure.

  • Configure database project properties.

  • Build the database project.

  • Deploy the database project.

  • Put the database project under version control.

Prerequisites

To perform this walkthrough, you must log on with an account that has permissions to create a database on a database server that is running SQL Server 2005.

To create the database project

  1. On the File menu, point to New, and click Project.

    The New Project dialog box appears.

  2. In Project Types, expand Database Projects, and click SQL Server 2005.

  3. In Templates, click SQL Server 2005 Database Project.

  4. In Name, type ProductsDB.

  5. Select the Create directory for solution check box.

  6. Accept the default values for Location, Solution Name, and Add to Source Control, and click OK.

    Note

    You could, at this point, add your solution to version control. In this walkthrough, you add the solution to version control in the final procedure.

    The new database project, ProductsDB, appears in Solution Explorer.

  7. On the View menu, click Database Schema View.

    Schema View appears if it was not already visible.

    Next you add the tables to the database project.

To add the Categories table to your database project

  1. In Schema View, expand the ProductsDB node, expand the Schemas node, expand the dbo node, and click the Tables node.

  2. On the Project menu, click Add New Item.

    The Add New Item dialog box appears.

    Note

    You can also right-click the ProductsDB project in Schema View, point to Add, and click Table.

  3. In Templates, click Table.

    Note

    In the Categories list, you can click Tables and Views to more easily find the template for a table.

  4. In Name, type Categories as the name that you want to give the new table.

  5. Click Add to add the table to your database project.

    Solution Explorer shows the new file for the table in your database project. Schema View shows the new table object. The Transact-SQL (T-SQL) editor appears, displaying the definition for your new table.

  6. In the T-SQL editor, modify the table definition to match the following example:

    -- =============================================
-- Create Categories table
-- =============================================
CREATE TABLE [dbo].[Categories]
(
[CategoryID] [int] NOT NULL IDENTITY(1, 1),
[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Picture] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  7. On the File menu, click Save dbo.Categories.table.sql.

  8. Expand the dbo.Categories node in Schema View.

  9. Expand the Columns node in the dbo.Categories table.

    The four columns that you defined in the T-SQL editor appear.

    Next you add an index to the Categories table.

To add an index to the Categories table

  1. In Schema View, right-click dbo.Categories, point to Add, and click Index.

    The Add New Item dialog box appears.

  2. In Templates, click Index.

  3. In Name, type CategoriesCategoryID as the name that you want to give the new index.

  4. Click Add to add the index to the Categories table.

    Solution Explorer shows the new file for the index in your database project. Schema View shows the new index object. The T-SQL editor appears, displaying the definition for your new index.

    Note

    In Schema View, the icon for the index appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "column_1" column, which does not exist.

  5. In the T-SQL editor, modify the index definition to match the following example:

    -- =============================================
-- Create CategoriesCategoryID index 
-- =============================================
CREATE NONCLUSTERED INDEX [CategoriesCategoryID] 
ON [dbo].[Categories] ([CategoryID]) 
ON [PRIMARY]

  6. On the File menu, click Save dbo.Categories.CategoriesCategoryID.index.sql.

    The error indicator disappears from the icon, which indicates that the index definition is now valid.

    Next you add a primary key to the Categories table.

To add a primary key to the Categories table

  1. In Schema View, right-click dbo.Categories, point to Add, and click Primary Key.

    The Add New Item dialog box appears.

  2. In Templates, click Primary Key.

  3. In Name, type PK_Categories as the name that you want to give the new primary key.

  4. Click Add to add the primary key to the Categories table.

    Solution Explorer shows the new file for the primary key in your database project. Schema View shows the new primary key object. The T-SQL editor appears, displaying the definition for your new key.

    Note

    The icon for the primary key appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "column_1" column, which does not exist.

  5. In the T-SQL editor, modify the primary key definition to match the following example:

    -- =============================================
-- Create PK_Categories primary key 
-- =============================================
ALTER TABLE [dbo].[Categories] 
ADD CONSTRAINT [PK_Categories] 
PRIMARY KEY CLUSTERED  ([CategoryID]) 
ON [PRIMARY]

  6. On the File menu, click Save dbo.Categories.PK_Categories.pkey.sql.

    The error indicator disappears from the icon, which indicates that the primary key definition is now valid.

    Next you add the Products table.

To add the Products table

  1. In Schema View, click ProductsDB.

  2. On the Project menu, click Add New Item.

    The Add New Item dialog box appears.

    Note

    You can also right-click the ProductsDB project in Schema View, point to Add, and click Table.

  3. In Templates, click Table.

  4. In Name, type Products as the name that you want to give the new table.

  5. Click Add to add the table to your database project.

    Solution Explorer shows the new file for the table in your database project. Schema View shows the new table object. The T-SQL editor appears, displaying the definition for your new table.

  6. In the T-SQL editor, modify the table definition to match the following example:

    -- =============================================
-- Create Products table
-- =============================================
CREATE TABLE [dbo].[Products]
(
[ProductID] [int] NOT NULL IDENTITY(1, 1),
[ProductName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CategoryID] [int] NULL,
[QuantityPerUnit] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UnitPrice] [money] NULL CONSTRAINT [DF_Products_UnitPrice] DEFAULT (0),
[UnitsInStock] [smallint] NULL CONSTRAINT [DF_Products_UnitsInStock] DEFAULT (0),
[UnitsOnOrder] [smallint] NULL CONSTRAINT [DF_Products_UnitsOnOrder] DEFAULT (0),
[ReorderLevel] [smallint] NULL CONSTRAINT [DF_Products_ReorderLevel] DEFAULT (0),
[Discontinued] [bit] NOT NULL CONSTRAINT [DF_Products_Discontinued] DEFAULT (0)
) ON [PRIMARY]

  7. On the File menu, click Save dbo.Products.table.sql.

  8. Expand the dbo.Products node in Schema View.

  9. Expand the Columns node in the dbo.Products table.

    The nine columns that you defined in the T-SQL editor appear.

    Next you add an index to the Products table.

To add an index to the Products table

  1. In Schema View, right-click dbo.Products, point to Add, and click Index.

    The Add New Item dialog box appears.

  2. In Templates, click Index.

  3. In Name, type ProductsCategoryID as the name that you want to give the new index.

  4. Click Add to add the index to the Products table.

    Solution Explorer shows the file for the new index in your database project. Schema View shows the new index object. The T-SQL editor appears, displaying the definition for your new index.

    Note

    In Schema View, the icon for the index appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "column_1" column, which does not exist.

  5. In the T-SQL editor, modify the index definition to match the following example:

    -- =============================================
-- Create ProductsCategoryID index
-- =============================================
CREATE NONCLUSTERED INDEX [ProductsCategoryID] 
ON [dbo].[Products] ([CategoryID]) 
ON [PRIMARY]

  6. On the File menu, click Save dbo.Products.ProductsCategoryID.index.sql.

    The error indicator disappears from the icon, which indicates that the index definition is now valid.

    Next you add a primary key to the Products table.

To add a primary key to the Products table

  1. In Schema View, right-click dbo.Products, point to Add, and click Primary Key.

    The Add New Item dialog box appears.

  2. In Templates, click Primary Key.

  3. In Name, type PK_Products as the name that you want to give the new primary key.

  4. Click Add to add the primary key to the Products table.

    Solution Explorer shows the file for the new primary key in your database project. Schema View shows the new primary key object. The T-SQL editor appears, displaying the definition for your new primary key.

    Note

    The icon for the primary key appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "column_1" column, which does not exist.

  5. In the T-SQL editor, modify the primary key definition to match the following example:

    -- =============================================
-- Create PK_Products primary key 
-- =============================================
ALTER TABLE [dbo].[Products] 
ADD CONSTRAINT [PK_Products] 
PRIMARY KEY CLUSTERED  ([ProductID]) 
ON [PRIMARY]

  6. On the File menu, click Save dbo.Products.PK_Products.pkey.sql.

    The error indicator disappears from the icon, which indicates that the primary key definition is now valid.

    Next you add a foreign key to the Products table.

To add a foreign key between the Products table and the Categories table

  1. In Schema View, right-click dbo.Products, point to Add, and click Foreign Key.

    The Add New Item dialog box appears.

  2. In Templates, click Foreign Key.

  3. In Name, type FK_ProductsCategories as the name that you want to give the new foreign key.

  4. Click Add to add the foreign key to the Products table.

    Solution Explorer shows the file for the new foreign key in your database project. Schema View shows the new foreign key object. The T-SQL editor appears, displaying the definition for your new foreign key.

    Note

    The icon for the foreign key appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "column_1" column, which does not exist.

  5. In the T-SQL editor, modify the foreign key definition to match the following example:

    -- =============================================
-- Create FK_Products_Categories foreign key
-- =============================================
ALTER TABLE [dbo].[Products] WITH NOCHECK
ADD CONSTRAINT [FK_Products_Categories] FOREIGN KEY ([CategoryID])
REFERENCES [dbo].[Categories] ([CategoryID])

  6. On the File menu, click Save dbo.Products.FK_ProductsCategories.fkey.sql.

    The error indicator disappears from the icon, which indicates that the foreign key definition is now valid.

    Next you add a check constraint to the Products table.

To add a check constraint to the Products table

  1. In Schema View, click dbo.Products, point to Add, and click Check Constraint.

    The Add New Item dialog box appears.

  2. In Templates, click Check Constraint.

  3. In Name, type CK_ProductsUnitPrice as the name that you want to give the new check constraint.

  4. Click Add to add the constraint to the Products table.

    Solution Explorer shows the file for the new constraint in your database project. Schema View shows the new constraint object. The T-SQL editor appears, displaying the definition for your new constraint.

    Note

    The icon for the constraint appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "column_1" column, which does not exist.

  5. In the T-SQL editor, modify the constraint definition to match the following example:

    -- =============================================
-- Create CK_ProductsUnitPrice check constraint
-- =============================================
ALTER TABLE [dbo].[Products] WITH NOCHECK 
ADD CONSTRAINT [CK_Products_UnitPrice] 
CHECK (([UnitPrice] >= 0))

  6. On the File menu, click Save dbo.Products.CK_ProductsUnitPrice.chkconst.sql.

    The error indicator disappears from the icon, which indicates that the constraint definition is now valid.

    Next you add a stored procedure to the project.

To create a stored procedure

  1. In Schema View, click ProductsDB.

  2. On the Project menu, click Add New Item.

    The Add New Item dialog box appears.

    Note

    You can also right-click the ProductsDB project in Schema View, point to Add, and click Stored Procedure.

  3. In Categories, expand Database Project and click Programmability.

  4. In Templates, click Stored Procedure.

  5. In Name, type Ten Most Expensive Products as the name that you want to give the new stored procedure.

  6. Click Add to add the stored procedure to your database project.

    Solution Explorer shows the file for the new stored procedure in your database project. Schema View shows the new stored procedure object. The T-SQL editor appears, displaying the definition for your new stored procedure.

  7. In the T-SQL editor, modify the stored procedure definition to match the following example:

    -- =============================================
-- Create Ten Most Expensive Products
--   stored procedure
-- =============================================
CREATE PROCEDURE [dbo].[Ten Most Expensive Products]
AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
RETURN 0

  8. On the File menu, click Save dbo.Ten Most Expensive Products.proc.sql.

  9. In Schema View, expand the Programmability node, and then expand the Stored Procedures node.

    The procedure that you defined in the T-SQL editor appears.

    Next you configure your project settings before you build and deploy the project.

To configure settings for deploying the project

  1. In Solution Explorer, click ProductsDB (the project, not the solution).

  2. On the Project menu, click ProductsDB Properties.

    The project properties window appears.

    Note

    You can also right-click ProductsDB in Solution Explorer and click Properties.

  3. Click the Deploy tab.

  4. In the Deploy action list, click Create a deployment script (.sql) and deploy to the database.

  5. Click Edit to specify the target connection.

  6. Specify the information to connect to the database server to which you want to deploy the ProductsDB database.

  7. In Select or enter a database name, type ProductsDB, and click OK.

    Target Connection is populated with the connection string. Note that the Target Database Name is set to ProductsDB.

  8. Accept the default values for the other options.

  9. On the File menu, click Save Selected Items.

    Your project build settings are saved.

    Next you build your database project.

To build your database project

  • On the Build menu, click Build Solution.

    Your database project builds. If successful, Build Succeeded appears in the status bar and the build results are displayed in the Output window.

    Finally, you deploy your database project.

To deploy your database project to your database server

  1. In Solution Explorer, click ProductsDB (the project, not the solution).

  2. On the Build menu, point to Deploy ProjectName.

    Your database project is deployed by using the connection that you specified in the build configuration. The message "Deployment succeeded" appears in the Output window and in the status bar.

To check your database project into version control

  1. In Solution Explorer, click ProductsDB (the solution).

  2. On the File menu, point to Source Control, and click Add Solution to Source Control.

    Follow the directions for your version control software to add the solution, the database project, and its contents to version control and check in all files. The database project represents the master copy of your database schema and is under version control.

Next Steps

You can use additional walkthroughs to find out how to work with existing deployed databases.

See Also

Concepts

Configuring Database Projects and Performing a Test Deployment

Performing Iterative Database Development

Terminology Overview of Database Edition

Other Resources

Building and Deploying Databases to Staging and Production Environments