Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) (2023)

  • Article

Applies to: Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) (1) SQL Server

Get started using SQL Server Management Studio (SSMS) to connect to your SQL Server instance and run some Transact-SQL (T-SQL) commands.

The article demonstrates how to follow the below steps:

  • Connect to a SQL Server instance
  • Create a database
  • Create a table in your new database
  • Insert rows into your new table
  • Query the new table and view the results
  • Use the query window table to verify your connection properties

This article covers connecting and querying an instance of SQL Server. For Azure SQL, see Connect and query Azure SQL Database & SQL Managed Instance.

To use Azure Data Studio, see connect and query SQL Server, Azure SQL Database, and Azure Synapse Analytics.

(Video) SQL Server Management Studio (SSMS) | Full Course

To learn more about SQL Server Management Studio, see Additional Tips and Tricks.

Prerequisites

To complete this quickstart, you need the following prerequisites:

Connect to a SQL Server instance

To connect to your SQL Server instance, follow these steps:

  1. Start SQL Server Management Studio. The first time you run SSMS, the Connect to Server window opens. If it doesn't open, you can open it manually by selecting Object Explorer > Connect > Database Engine.

    Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) (2)

  2. The Connect to Server dialog box appears. Enter the following information:

    SettingSuggested Value(s)Description
    Server typeDatabase engineFor Server type, select Database Engine (usually the default option).
    Server nameThe fully qualified server nameFor Server name, enter the name of your SQL Server (you can also use localhost as the server name if you're connecting locally). If you're NOT using the default instance - MSSQLSERVER - you must enter in the server name and the instance name.

    If you're unsure how to determine your SQL Server instance name, see Additional tips and tricks for using SSMS.

    AuthenticationWindows Authentication

    SQL Server Authentication

    Azure Active Directory Authentication

    Windows Authentication is set as default.

    You can also use SQL Server Authentication to connect. However, if you select SQL Server Authentication, a username and password are required.

    Azure AD Authentication is available for SQL Server 2022 (16.x) and later versions. For step-by-step instructions on how to configure it, see Set up Azure Active Directory authentication for SQL Server

    For more information about authentication types, see Connect to the server (database engine).

    LoginServer account user IDThe user ID from the server account used to log in to the server. A login is required when using SQL Server Authentication.
    PasswordServer account passwordThe password from the server account used to log in the server. A password is required when using SQL Server Authentication.

    Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) (3)

  3. After you've completed all the fields, select Connect.

    (Video) Connect to SQL Server with the Management Studio (SSMS)

    You can also modify additional connection options by selecting Options. Examples of connection options are the database you're connecting to, the connection timeout value, and the network protocol. This article uses the default values for all the fields.

  4. To verify that your SQL Server connection succeeded, expand and explore the objects within Object Explorer where the server name, the SQL Server version, and the username are displayed. These objects are different depending on the server type.

    Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) (4)

Create a database

Now let's create a database named TutorialDB by following the below steps:

  1. Right-click your server instance in Object Explorer, and then select New Query:

    Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) (5)

  2. Paste the following T-SQL code snippet into the query window:

    USE masterGOIF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = N'TutorialDB')CREATE DATABASE [TutorialDB]GO
  3. Execute the query by selecting Execute or selecting F5 on your keyboard.

    (Video) How to Connect Sql Server Using SSMS-Part1

    Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) (6)

    After the query is complete, the new TutorialDB database appears in the list of databases in Object Explorer. If it isn't displayed, right-click the Databases node, and then select Refresh.

Create a table

In this section, you create a table in the newly created TutorialDB database. Because the query editor is still in the context of the master database, switch the connection context to the TutorialDB database by doing the following steps:

  1. In the database drop-down list, select the database that you want, as shown here:

    Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) (7)

  2. Paste the following T-SQL code snippet into the query window:

    USE [TutorialDB]-- Create a new table called 'Customers' in schema 'dbo'-- Drop the table if it already existsIF OBJECT_ID('dbo.Customers', 'U') IS NOT NULLDROP TABLE dbo.CustomersGO-- Create the table in the specified schemaCREATE TABLE dbo.Customers( CustomerId INT NOT NULL PRIMARY KEY, -- primary key column Name [NVARCHAR](50) NOT NULL, Location [NVARCHAR](50) NOT NULL, Email [NVARCHAR](50) NOT NULL);GO
  3. Execute the query by selecting Execute or selecting F5 on your keyboard.

After the query is complete, the new Customers table is displayed in the list of tables in Object Explorer. If the table isn't displayed, right-click the TutorialDB > Tables node in Object Explorer, and then select Refresh.

(Video) How to install SQL Server 2022 Developer and SQL Server Management Studio (SSMS) - for FREE

Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) (8)

Insert rows

Now let's insert some rows into the Customers table that you created. Paste the following T-SQL code snippet into the query window, and then select Execute:

-- Insert rows into table 'Customers'INSERT INTO dbo.Customers ([CustomerId],[Name],[Location],[Email])VALUES ( 1, N'Orlando', N'Australia', N''), ( 2, N'Keith', N'India', N'keith0@adventure-works.com'), ( 3, N'Donna', N'Germany', N'donna0@adventure-works.com'), ( 4, N'Janet', N'United States', N'janet1@adventure-works.com')GO

Query the table and view the results

The results of a query are visible below the query text window. To query the Customers table and view the rows that were inserted, follow the steps below:

  1. Paste the following T-SQL code snippet into the query window, and then select Execute:

    -- Select rows from table 'Customers'SELECT * FROM dbo.Customers;

    The results of the query are displayed under the area where the text was entered.

    Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) (9)

    You can also modify the way results are presented by selecting one of the following options:

    Connect and query a SQL Server instance using SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) (10)

    (Video) SSMS Tutorial (SQL Server Management Studio) - Feature Demonstration

    • The first button displays the results in Text View, as shown in the image in the next section.
    • The middle button displays the results in Grid View, which is the default option.
      • This is set as default
    • The third button lets you save the results to a file whose extension is .rpt by default.

Troubleshoot connectivity issues

To review troubleshooting techniques to use when you can't connect to an instance of your SQL Server Database Engine on a single server, visit Troubleshoot connecting to the SQL Server Database Engine.

Next steps

The best way to get acquainted with SSMS is through hands-on practice. These articles help you with various features available within SSMS.

Videos

1. Microsoft SQL Server Management Studio Part 1 - Setup a LocalDB server instance using sqllocaldb
(Chris Dyck)
2. How to Install Microsoft SQL Server & SSMS on Windows 10/11 [ 2023 Update ] Complete guide
(Geeky Script)
3. How To Install SQL Server and SQL Server Management Studio 2019 (SSMS)
(ProgrammingKnowledge2)
4. How to Find SQL Server Instance Name
(studywithdemo)
5. SQL Server Part -1 Management Studio SSMS
(Databit)
6. How to Install SQL Server 2022 Express and SQL Server Management Studio SSMS - It's FREE to use.
(Sachin Samy)

References

Top Articles
Latest Posts
Article information

Author: Kelle Weber

Last Updated: 06/08/2023

Views: 5516

Rating: 4.2 / 5 (73 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Kelle Weber

Birthday: 2000-08-05

Address: 6796 Juan Square, Markfort, MN 58988

Phone: +8215934114615

Job: Hospitality Director

Hobby: tabletop games, Foreign language learning, Leather crafting, Horseback riding, Swimming, Knapping, Handball

Introduction: My name is Kelle Weber, I am a magnificent, enchanting, fair, joyous, light, determined, joyous person who loves writing and wants to share my knowledge and understanding with you.