If you are a database developer or Full-stack developer working with SQL Server database, then you might need to create database in SQL Server, which we can do it easily using SQL Server managment Studio, so in this article, I have provided step by step details on sql server database creation using SSMS (SQL Server Management Studio). Before we begin, I hope you have downloaded and installed SQL Server on your local machine, if not check here to "Download and Install SQL Server step by step".
SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure. Use SSMS to access, configure, manage, administer, and develop all components of SQL Server, Azure SQL Database, and SQL Data Warehouse.
Using SQL Server Management Studio to create database
Let's begin creating our new database easily using SSMS.
Step 1: Open SQL Server Management Studio and then connect it to local database engine using Windows Authentication or SQL Server Authentication,as shown in the below image.
For easier configuration, we will be using Windows Authentication for now, as shown in the below Gif Image.
Step 2: As you can see in the above Gif image, once we are connected to Local database Engine, we have got options like "Database".
So to create new database, right-click on "Database" and then select "New Database"
Once you have selected "New Database", a new Window Pop-up will appear, where you can name your database (SampleDatabase) and click "OK" to generate new database, as shown below.
That's it, we have created a new database (Expand "Databases" by clicking "+" icon on the left of it and you will see new database name there.), let's add some tables and sample data in it.
Step 3: You can now create new table in the database in SSMS, in similar way.
SampleDatabase" by clicking "+" icon on the left of it and then right-click on "Tables" and then select "Table/New-Table", as shown in the below Gif image.
In the above Gif image, we are creating table, named "NamesEmail" and we have also created three columns in it:
- "Id" of "int" type (int = intger type, values 1,2,3, etc can be stord)
- "Name" of "varchar(500)" type (varchar(500) = string type column, we store words like "Ramesh", "Suresh" etc with limit of 500 characters )
- "Email" of "varchar(500)" type to save email ids
Now, to check if new table exists, you can expan "Tables" to see the new table name, as shown below
Step 4: Adding new data in table "NamesEmail", now, we have table but we don't have any data in it.
So, we can use the GUI of SSMS to add sample data in our "SampleDatabase" table named "NamesEmail".
Simply, right-click on "NamesEmail" table and select "Edit Top 200 Rows".
You will see empty rows, you can fill them up for adding sample data.
Check the below gif image, to understand complete procedure.
In the above sample image,initially you can see there is no data in table "NamesEmail", then we are adding new rows using above procedure and when using command "
SELECT * FROM [SampleDatabase].[dbo].[NamesEmail]" we can see newly added rows are populated.
We can also edit or update values of SQL Server table rows using same procedure, but only first 200 rows values can only be edited or added using this method.
Add Value in SQL Server Table
So, if there are lots of values, you can insert new values using SQL Query, using below SQL Query format
INSERT INTO table_name VALUES (value1, value2, value3,...);
So, for above sample table, to add new row query looks like this
INSERT INTO NamesEmail VALUES (3, 'Tarun', 'email@example.com');
Update a record in Table
Similarly, to update a row, query look format
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
So, if you want to update row with "Id=3", "Name" column from "tarun" to "varun", you can use SQL query as below
UPDATE NamesEmail SET Name = 'Varun' WHERE Id=3;
as "Id" columns is primary key in our table and is unique for every row, so we used it in "Where" condition.
To delete a record from table
The DELETE statement is used to delete existing records in a table.
DELETE FROM table_name WHERE condition;
DELETE FROM NamesEmail WHERE Name='Varun';
You might also like to read: