October 25, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Beginner's Guide to SQL Server Database Development with VSTS Database Edition

  • June 10, 2008
  • By Jeffrey Juday
  • Send Email »
  • More Articles »

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])

Schema Compare

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.



Click here for a larger image.

Figure 7: Data menu options

The Schema Compare dialog is in Figure 8.



Click here for a larger image.

Figure 8: Schema comparison dialog options





Page 3 of 4



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel