Chapter 02 slides

Page 1: Title Slide

• Murach’s SQL Server 2022

• Chapter 2: How to use Management Studio

• © 2023, Mike Murach & Associates, Inc.

Page 2: Objectives Applied

• Management tasks using Management Studio:

  • Start or stop an instance of SQL Server Express

  • Enable remote connections to a database

  • Navigate through database objects

  • Create database diagrams

  • View column definitions for tables

  • View/modify data in tables

  • Modify column definitions

• Running SQL queries:

  • Use Management Studio to run, open, and save SQL queries.

• Utilizing SQL Server documentation for information lookup.

Page 3: Objectives (cont.)

• Client Tool Functions:

  • Management Studio

  • SQL Server Configuration Manager

  • Azure Data Studio

• Authentication Methods:

  • Distinction between Windows authentication and SQL Server authentication for database connection.

Page 4: SQL Server 2022 Tools

• Key Tools:

  • SQL Server Configuration Manager

  • SQL Server Management Studio

  • Azure Data Studio

Page 5: SQL Server Configuration Manager (Services)

• Overview of services in SQL Server Configuration Manager

• Services Displayed:

  • SQL Server (SQLEXPRESS): Stopped/Runnings

  • SQL Server Agent (SQLEXPRESS): Stopped

  • SQL Server Browser: Stopped

• Configuration Statuses:

  • State: Running

  • Start Mode: Automatic

Page 6: SQL Server Configuration Manager (Network)

• Protocols for SQLEXPRESS:

  • Shared Memory: Enabled

  • Named Pipes: Disabled

  • TCP/IP: Selected for enabling

Page 7: Connect Using Windows Authentication

• Connection Parameters:

  • Server type: Database Engine

  • Server name: localhost\SQLEXPRESS

  • Authentication: Windows Authentication

  • User name & Password fields

Page 8: Connect Using SQL Server Authentication

• Connection Parameters:

  • Server type: Database Engine

  • Server name: localhost\SQLEXPRESS

  • Authentication: SQL Server Authentication

  • Login & Password fields

Page 9: Management Studio and Object Explorer

• Features of SQL Server Management Studio and Object Explorer:

  • Quick Launch (Ctrl+Q)

  • Navigate through Databases, Tables, Views, External Resources, etc.

  • Execute new queries

  • Overview of objects in Object Explorer

Page 10: Relationships in the AP Database

• Visual management of AP database and its entities via Management Studio

• Display of items like Vendors, Invoices, and their relations in graphical form.

Page 11: Columns in the Vendors Table

• Vendors Table Structure:

  • Column Names, Data Types, Allow Nulls

  • Examples:

    • VendorID: int (PK)

    • VendorName: varchar(50)

    • DefaultTermsID: int (FK)

  • Displaying key attributes of Vendor details.

Page 12: Row Modification in Terms Table

• Row Modifications:

  • Active management of rows displayed in grid format

  • Example entries of terms managing payment due days.

Page 13: View or Modify Data in a Table

• Viewing Data:

  • Right-click on the table and choose Select Top 1000 Rows

• Modifying Data:

  • Right-click and select Edit Top 200 Rows for grid-based editing

  • Commit changes by moving to another row; Press Esc to roll back changes.

Page 14: SELECT Statement with Completion List

• Query Editor Window:

  • SQL statement composition process

  • Example SQL statement structure for data retrieval using SELECT

Page 15: Entering and Executing a Query

• Using the Query Editor:

  • Create queries in a new Query Editor window, inputting SQL statements

  • Utilize IntelliSense for completion, execute using F5 or toolbar button.

Page 16: Management Studio with an Error Message

• Common Error Indicators:

  • Invalid column name examples and debugging through error messages.

Page 17: Common Causes of Errors

• Frequent Errors When Executing Queries:

  • Forgetting to select the correct database

  • Misspelling table or column names

  • Omitting necessary syntax elements like quotation marks.

Page 18: Open File Dialog Box

• Query Management:

  • Open and save SQL queries through the Open File dialog

  • Display of files based on structure and organization for convenience.

Page 19: Open and Save Queries

• Saving and Opening Queries:

  • Save using toolbar or shortcut Ctrl+S

  • Open using toolbar or Ctrl+O.

Page 20: Documentation for the SELECT Statement

• Documentation Overview:

  • Link to syntax and features of the SELECT statement in SQL Server.

  • Description of SQL components and logical processing order.