SQL Server Data Tools for Visual Studio

Learn how to use the SQL Server Data Tools and perform your database actions without leaving Visual Studio.

Introduction

• Work with your SQL Server databases from Visual Studio
• What you should know
• Further lessons and examples
• The programming setup and configuration
• How to access the sample code on GitHub

1. Overview

• Why developers need SSDT
• DACPAC and BACPAC files
• SQL Server and Azure Database versions
• Why LocalDB is essential for developers
• Connected and disconnected workflows with SSDT
• Install the SQL Server Data Tools (SSDT)

2. Connect to a Database

• The SQL Server Express course databases
• The Azure SQL course databases
• SQL Server Object Explorer (SSOE)
• The LocalDB named instances
• Connect to network or cloud SQL database
• Create Visual Studio data project and use disconnected workflow
• Connect to SQL Server Express in Server Explorer
• Use logical name for readability

3. SQL Server Object Explorer

• Copy database files
• Reconnect the databases
• Tour the Table Designer
• Add a column to a table
• Update the connected database with table modifications
• Handling errors in table updates
• Understand the Power Buffer
• Update multiple edits to the connected database

4. Work with Table Data

• View data in the table data grid
• Sort data in the table data grid
• Filter data in the table data grid
• Edit data in the table data grid
• Add and delete rows in the table data grid

5. More Table Operations

• Add and remove tables
• PRIMARY KEY and other column settings
• FOREIGN keys and tables
• Add CHECK constraints

6. Queries, Views, and Stored Procedures

• Use the query window
• Write a SELECT query
• Write a CREATE TABLE query
• Work with existing views
• Create a custom view
• Work with existing stored procedures
• Call a stored procedure with parameters
• Debug a stored procedure
• Create a custom stored procedure

7. Compare Databases

• Prepare to compare databases
• Create a Data Compare session
• Analyze the Data Compare information
• Update the data in the target database
• Create a Schema Compare session
• Analyze Schema Compare for object definitions changes
• Update the schema in the target database
• Preventing data loss in a schema update

8. SQL Server Data Project

• Databases and the project lifecycle
• Understand the data project template
• Create a data project
• Examine the SQL Server object types
• Add items to project
• Import schema from a .dacpac file
• Import schema from a connected database
• Change a table or other data item
• Build the project
• Auto-mount the database on project open
• Auto-populate the data items on project debug
• Refactor a database object
• Compare the data project with other databases
• Create snapshot DACPAC files

Conclusion

• Next steps