SQL Server: Developer and DBA Collaboration

Get an introduction to creating relational databases with SQL Server and the graphical interface, SQL Server Management Studio.

Introduction

• Create databases with SQL Server 2022
• What you should know

1. Getting Started

• Core concepts
• Choose a SQL Server 2022 edition
• Install SQL Server on your PC
• Install Management Studio
• Enable features with Configuration Manager
• Deploy SQL Server 2022 in the Azure cloud
• Work with SQL Server in Docker

2. Logging in to the Server

• User permissions and authentication
• Log in with Management Studio
• Enable the System Administrator account
• Connect to Azure and Docker servers

3. Creating Databases

• Create the first database
• Database configuration options
• Table structures
• Create a simple table
• Edit a table's structure
• Enter data into a table using the GUI
• Import data from CSV files
• Create tables from flat files
• Install a sample database

4. Table Design for Healthy Databases

• Choose appropriate data types
• SQL Server data type options
• Automatically assign record identities
• Primary key fields
• Establish a default value
• Check constraints
• Unique constraints
• Foreign keys
• Create a foreign key column
• Create relationships between tables

5. Structured Query Language

• Understand the role of T-SQL
• Use the T-SQL editor
• Create a table with T-SQL
• Add data to a table
• Retrieve records with SELECT
• Filter returned results with WHERE
• Sort records
• Delete records from a table
• Update records in a table
• Joining related tables
• Remove a table from the database

6. Writing Efficient Queries

• Create a view of the data
• Data table indexes
• Create additional indexes on a table
• Aggregate functions
• Use built-in functions
• Leverage user-defined scalar functions
• Introduction to stored procedures
• Create stored procedures
• Parameterized stored procedures

7. Backup and Restore

• Create a full backup of the database
• Create a differential backup
• Restore a backup
• Business continuity solutions
• Continuously replicate data to the cloud

8. Security

• Create a new user account
• User roles and permissions
• Database schemas
• Dynamic data masking
• Always On data encryption

9. Monitoring and Management

• Explore the role of system databases
• Prevent excessive TempDB autogrowth
• Review the SQL Server error log
• Dynamic management views
• Database Console Commands (DBCC)

Conclusion

• Next steps