Changes Using a State-Based Methodology

In this lab we will make changes to a database using Stated-Based Methodology

DevOps MPP Course Source

Prerequisites:

Lab Tasks:

In this lab, we will begin making a variety of changes to our database and committing them to version control.

  • Alter an existing stored procedure
  • Add a new sequence
  • Check in changes

Estimated Lab Time:

  • approx. 30 minutes

Task 1: Alter an existing stored procedure

We will examine the process for altering a stored procedure through code.

  1. Open your SSDT project WideWorldImporters-SSDT in Visual Studio. The project should contain database objects from the previous lab. (The Recent Projects and Solutions option from the File menu might help with opening this project).

    Screenshot of Visual Studio with the WideWorldImporters-SSDT project folders displaying.

  2. In Solution Explorer, expand the Website folder, expand the Stored Procedures folder and note the available procedures.

    In Solution Explorer, the Website folder is expanded, and Stored Procedures is expanded. Under Stored Procedures, SearchForStockItems.sql is selected.

  3. Right-click the SearchForStockItems stored procedure and click Open.

    The SearchForStockItems.sql stored procedure displays.

  4. Add a line to display the brand as follows:

    In the SearchForStockItems.sql file, callout arrows point to si.StockItemName, and si.Brand.

  5. From the File menu, click Save SearchForStockItems.sql. In Solution Explorer, note that the lock icon has changed to a check mark to indicate that the file is now checked out.

    In Solution Explorer, SearchForStockItems.sql is selected, and a callout arrow points to the checkmark next to the name.

  6. Close the SearchForStockItems.sql tab.

Task 2: Add a new sequence

We will add a new sequence to the database.

Let add a new sequence to the database. We want to add a sequence that will be used as a default for a new table that is being developed.

  1. In Solution Explorer, expand the Sequences folder, then right-click the Sequences folder (second level folder), then click Add, then click New Item. In the Add New Item - WideWorldImporters-SSDT window, in the center pane select Sequence, in the Name enter CustomerSpecialDealID, then click Add.

    In the Add New Item - WideWorldImporters-SSDT window, in the left pane, SQL Server is selected. In the center pane, Sequence is selected. The Name field is set to CustomerSpecialDealID, and the Add button is selected.

  2. Then modify the code as shown.

    In Visual Studio, on the CustomerSpecialDealID tab, a callout points to (Sequences) on line one, which reads: CREATE SEQUENCE (Sequences).(CustomerSpecialDealID). On line 7, a callout points to “CACHE 100.”

  3. From the File menu, click Save All.

  4. Close the CustomeSpecialDealID.sql tab and in Solution Explorer, note the + icon for a newly added item.

    The newly added item, CustomerSpecialDealID.sql, has a plus icon next to it.

Task 3: Check in code

We will check our changes into the VCS.

  1. Before checking in our code, let’s ensure that it builds OK. From the Build menu, click Build WideWorldImporters-SSDT. When the build completes, in the Output pane, check that it was successful.

    In the Output code, a callout points to the Build: 1 Succeeded message. Successfuly build output

  2. In the Team Explorer pane, click Changes.

    In Tean Explorer - Home, under Project, Changes is selected.

  3. Add a commit message as shown.

    Commit message is as follows: Added branch to stock item search. Added CustomerSpecialDealID sequence.

  4. From the Drop-down menu beside Commit All, click Commit All and Push.

    Commit All is selected, and from its drop-down menu, Commit All and Push is selected.

  5. When all steps have finished executing, you should receive a message indicating success.

    In Team Explorer - synchronization, A callout points to the message, Successfully pushed to origin/master.

Summary

In this lab you completed the following tasks:

  • Added ReadyRoll to Visual Studio
  • Created a new ReadyRoll project
  • Made an initial commit to version control