SQL Server Management Studio
Most of us know that in this advanced technological era, Relational Databases play a crucial role in any application development. An application that has a requirement to store data will require either a Relational Database or Non-Relational Database. In this article, we will focus on Relational databases and discuss What is MySQL, SQL Server and What is SSMS in-depth.
Relational database Management Systems are structured in a way that the data is organized and stored inside the tables. Dependencies and relationship exist between these types of data which resides inside the tables. The language which is used to communicate between these relational databases is Structured query language, which is widely known as SQL.
And a popular relational database is MySQL. It is open-source and free software under the GNU license. MySQL is very easy to use, offers high security and flexibility.
Now you might wonder why are we discussing MySQL when the article title says SSMS? This will be clear once we start discussing SQL Server and how it differs from MySQL. Gradually in this post, we will move on to SSMS which is a management tool for SQL Server.
Keep reading the article to know more about SSMS and how it is used.
Table of content:
- What is SQL Server?
- Difference between MySQL and SQL Server?
- What is SSMS?
- Installation of SSMS
- Connecting to the Database engine.
- Using SSMS
- Wrapping Up
What is SQL Server?
SQL Server is the relational database developed from the house of Microsoft. Similar to other Relational Database Management Systems, SQL Server is built on top of SQL programming language. SQL Server is T-SQL, Microsoft’s implementation of SQL which adds some more extensions to the language. It is easily compatible with other Microsoft based services like Azure.
Difference between MySQL and SQL Server?
MySQL and SQL Server (Microsoft) are both used on an extreme enterprise level. MySQL is GNU licensed and freely available, whereas Microsoft distributes SQL Server. Microsoft has many enterprise editions of SQL server from which businesses can choose from. SQL server is more secured than MySQL.
What is SSMS?
SQL Server Management Studio is a Graphical tool which enables you to configure and manage SQL server more efficiently and in a powerful way. So, SSMS is typically not a server like the SQL server. We can connect it with any SQL component like the SQL server and work with SQL elegantly. It is cost-free and has various add-in options and an easy installation process.
How to install SSMS
- You can install SSMS from this link SQL Server Management Studio. Remember you need to have an SQL server instance depending on your platform.
- For Windows, follow this link: Download SQL Server 2019 Developer Edition
- For Mac, follows this link: Download SQL Server 2019 on Docker.
Connecting to the database engine
After you finish installing, click on the SQL Management Studio from the Start menu or you can access it from the command prompt by navigating to the installed directory and running the file Ssms.exe.
After launching SSMS, a new window known as ‘Connect to the server’ will open.
There are four different server types available in the SQL Server Management Studio.
- Database Engine: Works with relational databases and uses transact SQL queries.
- Analysis Service: To work with SQL Server Analysis Service
- Reporting Service: To work with SQL Server Reporting Service
- Integration Instance: To work with SQL Server Integration Service
You need to enter the Server Name which is nothing but the SQL server instance name.
- If the SQL server was installed using the default instance, then you can use the localhost, system IP address.
- If the SQL server was installed with any other name other than the default name then you have to provide your System name/Instance name. For example, if the system name is Ruud and the instance which was created is called PartechSQL, then you have to provide Ruud/PartechSQL.
After this, you have to select the type of authentication you want to use. You can choose:
- Windows Authentication: If you’re a system admin and the server is installed locally, you can choose this.
- SQL Server Authentication: This will ask you to enter the username and password of the server you want to connect with.
Now after completing these fields click on ‘Connect’.
Using SSMS
Once you’ve successfully established a connection, right-click on the server instance in Object Explorer and select New Query.
The following SQL code can be used to create a database with the name ‘Test’-
This query will check if a database with the name ‘Test’ exists or not. If the database doesn’t exist it will create a new database named Test. Otherwise, it will display the name column from the database.
Execute this query by selecting ‘execute’ or F5 on the keyboard. As soon as this query completes, the new Test database appears in the list of databases.
Let’s add a ‘Users’ table in this Test database.
From the list of databases choose your specific database (In our case it is the Test database) and paste this SQL code in Query Window and execute it.
Execution of this query will create a new table named Users and it will be displayed in the object explorer tab.
Let’s add some rows to the table. Paste the following T-SQL code into the Users table.
!
Execution of this query will populate the table Users with the given records.
After this, let’s query the table and view the results.
The execution of this query will display all the records of the Users table.
Wrapping Up
In this post, we have seen what are Relational databases, MySQL, and SQL Server. We also had a glimpse into SQL Server Management Studio and saw how to install and use the application. With the help of SSMS, we can run SQL commands to create a database, create a table, insert values into the table, view the inserted values, and do a lot more