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".

Once you have downloaded and installed SQL Server, you will also need SQL Server Management Studio and yes SQL Server management studio is a free to use software.

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.

sql-server-database-creation-min.png

For easier configuration, we will be using Windows Authentication for now, as shown in the below Gif Image.

choose-sql-server-authentication-to-login-min.gif

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"

select-new-database-min.png

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.

create-new-database-giving-name-min.png

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.

Expand "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.

create-new-table-sql-server-min.gif

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

expand-to-see-table-names-database-sql-server-min.png

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.

adding-sample-values-in-database-sql-server-min.gif

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', 'tarun@gmail.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.

Syntax

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM NamesEmail 
WHERE Name='Varun';

You might also like to read:

Connect to SQL Server using C# in Console Application