Beginner's Guide to SQL Server Database Development with VSTS Database Edition
When you poke around the project, you'll notice that the "Build" operations are enabled in Visual Studio. In fact, as you change your project, removing or changing tables, your project will do dependency validation. Broken dependencies will show in the Error List.
Visual Studio, working in conjunction with a database, creates and manages locally on your local SQL Server and handles all the dependency checking and compilation for you. Figure 6 displays the local SQL Database from inside of Management Studio.
Figure 6: Project SQL Database
With some basic project operations understanding, you're ready to add code to the project.
Adding to the Project
Hosting TSQL development in Visual Studio is only part of the database development story.
To illustrate how the other development tools work, I'm going to make three changes to the project. One change will be made from inside the project, and two changes will be made from the AdventureWorks database on the server.
The following is a stored procedure I've added directly to the project.
CREATE PROCEDURE [dbo].[Procedure1] @param1 int = 0, @param2 int AS SELECT @param1, @param2 RETURN 0;
Below is a comment I've added to the uspGetBillOfMaterials stored procedure in the AdventureWorks database on the server.
AS SET NOCOUNT ON /*New Comment*/
I also added a new table to the AdventureWorks database and a foreign key. The DDL code is below.
CREATE TABLE [dbo].[Table_1]( [Field1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Field2] [int] NULL, [KeyField] [int] NOT NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [KeyField] ASC )WITH(PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Table_1] WITH CHECK ADD CONSTRAINT [FK_Table_1_ShipMethod] FOREIGN KEY([KeyField]) REFERENCES [Purchasing].[ShipMethod] ([ShipMethodID])
As I mentioned earlier, TSQL code is compact, specialized, and lacks built-in versioning. The Schema Compare leverages TSQL attributes to overcome its versioning issues. Schema compare allows a developer to compare all of the DDL of databases and project, and then to synchronize the changes.
Schema Compare is accessed from the "Data" menu. Accessing the Data Menu appears in Figure 7.
Figure 7: Data menu options
The Schema Compare dialog is in Figure 8.
Figure 8: Schema comparison dialog options
Page 3 of 4