
In the Last Episode…
Welcome! Or is it "Welcome back!"? This is the second instalment of a series of tutorials on SQL and SQL Server Studio. The goal? To make you familiar and comfortable with the tool, and the language. "Why does it even matter?" I see you asking. Well, it turns out, curiosity, and side projects are often key in getting picked on new projects, or even in getting hired for a new job. The mere fact you’ve already used an important tool such as SQL Server Studio, and wrote some SQL queries, can and will give you a clear headstart.
If you missed Episode 1, on how to set up our environment and local server, head towards this article, then don’t forget to come back 😉 .
Getting Started With SQL Server Management Studio – Part 1 Step by Step Setup
What to Expect?
Today we’ll look at databases, tables and briefly mention schemas. There will also be an important commentary on naming conventions. We’ll see two ways to create tables, and write queries to drop them. You’ll find examples of how SQL Studio supports you in writing bug-free queries, and be able to review query performance and results returned. By the end of this article, you’ll feel confident and be equipped with strong basics to start wandering around SQL Studio. Let’s jump right in!
Creating Your First Database
A database, or a DB for short, is a collection of tables. That’s the simplest way we could put it. Let’s go step by step to create our first one.
- We assume we’ve launched SQL Server Studio, and are connected to our instance of our local server.
- On the left-hand side is the Object Explorer. Right-click on Databases, and then ‘New database’.

- A new window appears. The million-dollar question is how to name our database. Why does it matter? A bit of food for thought:
- It will likely outlive many applications, dashboards, processes which we’ll put in place.
- We work collaboratively, so this chosen name will be used, both verbally and under a written form. Imagine for a second it was named _XRZPWOJLM. Not bad, right? Good luck naming this in a verbal conversation. And you’re likely to use this term a lot over time. Hundreds of times a year. Well, at least under a written form you could tell ‘it’s that server starting with XR…’. OK, you got a point. That’s of course unless it’s the only one named like that. Having a database named XRZPWO_JLM gives me a hint there’s going to be more named that way on the rest of your server. You’d have to log in, check, copy-paste (you’ll fail to properly communicate by memory). Your recipient might spend some time to find it. Sounds like hell, right?
- Names work as references. If you were to start with a DB called _XRZPWOJLM and in a few months, after being bored by the hassle to communicate it, you were to decide to change it, it could break dependent applications. A double pain.
So how to name it? The name should be self-explanatory for what to expect within the database. It should also be distinct. An interesting approach is to use databases to segregate our data by project.
- HR_data could host your data related to your personnel, like first & last name, email, hire date, rank.
- API_Finance could host data pulled from different financial websites’ APIs.
- Product_ABC could host the data collected and generated from your product named ABC.
Long story short, naming matters. The subject would deserve an article of its own. There are conventions, different approaches. Common sense and practicality should prevail. Let’s go for HR_data.
There’s a range of settings we could adjust, but we’ll keep the default settings. We hit enter, and the new database is created.
- Click on the + to expand hierarchies of ‘Databases’ and then ‘HR_data’, you should see this:

We’ve got our first database, we’re all set to create our first tables.
Creating Your First Tables
Depending on the field, the elements of a table can bear different names. The most common is the row-column appellation.
- A row can also be referred to as a record, or a line, depending on who you ask.
- As per the columns, they can be referred to as ‘features’ or ‘variables’, this is jargon often used within Data Science. But as you might know:
The rows by any other names would smell as sweet
Now, tables naming is as important as Database naming. Because again, we’re likely to use and discuss those terms over and over with colleagues. It also makes it easier when writing scripts or running queries, to have clear names and have a sense of what’s within the tables.
Let’s see a complex (spoiler: not in a good way) example below:

Those tables above form a "normalized schema" – as opposed to a schema of a flat table, more on this in the next article – but the naming of tables and columns is far from being optimal. There’s no consistency, and some are not self-explanatory. Let’s go ahead and create our first tables.
- Right-click on tables, then new table

- The table designer appears. A table can be created in multiple ways, either by using the table designer or by writing a query. We’ll start with the former.
Create a Table Using the Table Designer
We’ll start small and simple, with three columns:
- Department_ID – this will be the unique identifier for the department. As this will be a number, we specify int (integer) as the data type.
- Department_Name – this is its generic name. We pick nvarchar(50), because the length of the string, here department name, might vary, hence the ‘var’ in the data type. The (50) indicates the maximum length we’ll allow. Anything beyond won’t be recorded. We could set it to nvarchar(MAX) if we want to use the maximum accepted length. "Why not use nvarchar(MAX) all the time?", I see you asking. Well, because of performance, it’s slower than nvarchar(n).
- Department_Email – the department’s email, think of a shared mailbox for example. Nvarchar(50) here as well.

The nvarchar(n) can easily be adjusted later on. Using (MAX) makes sense mostly if you’re expecting text as entry, or if you’re lazy and performance isn’t your primary concern (might always come back and haunt you, but you’d then be able to adjust to a number ‘n’).
- Let’s hit the ‘Save’ floppy-disk, in the top left corner, and give our table a compelling name, ‘Departments’.

- The table is now visible within the hierarchy, under ‘Tables’. Hit the refresh button – the circling arrow close by ‘Connect’ – if it doesn’t show. When working with network servers, it might happen the table won’t show, and you’ll need to click ‘Connect’ and reconnect to the server.

- Now that the table is saved and created, we can close the design window

- If in the future you were to require to adjust the table, you can do so by right-clicking on it, then selecting Design. The design window reappears and lets you alter your table. Don’t forget to save changes once you’re done.

Create a Table by Writing a Query
It’s an alternative to the design window. It might be handy when you’re writing a script, which requires the creation of a table. The result is the same, you’ve got a new table. This method will most likely be used if you were to update, add or delete records from a table. Let’s see how to write a query to create that same table.
- Right-click on your DB HR_data, then New Query

- A query window appears. Let’s write the following
CREATE TABLE HR_data.dbo.Departments
(
Department_ID int,
Department_Name nvarchar(50),
Department_Email nvarchar(50)
)
The structure is as follows:
- ‘CREATE TABLE’ which is a COMMAND name within the Sql language.
- Then via a dot-chain or hierarchy, we precise where this table should be created. Here it’s within _HRdata.dbo.. ‘Dbo’ is a default setting.
- We then open a bracket ‘(‘ and start typing our column name, followed by its data type. Every line is ended by a comma.
- We close the list of columns with the other bracket ‘)’.
- Within the window, you’ll see part of the query being underlined in red. Something’s off. There’s already a table called like that in our DB. So instead, for this exercise, we’ll adjust our query above, and call the table _Departmentstemp. A useful feature of modern scripting and coding environments is the scanning for potential bugs or errors. This saves an incredible amount of time even before we start executing our code.

Let’s type this in instead:
CREATE TABLE HR_data.dbo.Departments_temp
(
Department_ID int,
Department_Name nvarchar(50),
Department_Email nvarchar(50)
)
The red underline is gone.
- We can now execute our query. Either hit F5 or the ‘Execute’ green arrow.

- We receive immediate feedback on our query

- Let’s refresh our server, and the table appears. Great stuff.

- To keep things tidy, we’ll get rid of this _temp table, by dropping it. Be careful with this, as once it’s dropped… It’s dropped. Gone.
- We’ll recycle our Query Window, first delete the code that led to the table creation. Then write the below, and hit Execute or F5:
DROP TABLE HR_data.dbo.Departments_temp
- After refreshing our hierarchy, we see the table is gone. Note that, ‘refreshing’ happens only from a user interface perspective. Because in the background, the table has already been dropped the moment you executed the ‘drop table …’ query.

Querying our Table
Tables can be queried or ‘read’. Let’s look at our very own ‘Hello World’ of SQL.
- Clean up your query window. Or close it and reopen one.
- Type the below, then execute/F5
SELECT *
FROM HR_data.dbo.Departments
- SELECT means we’ll select what follows,
- The * means ‘everything’, that is, every column,
- FROM indicates the source from which we’ll SELECT ‘everything’

Additional Tips & Comments on The Query Window
Colours
Throughout this series of tutorials, we’ll encounter different colours. Here’s a sneak peek at what to expect.
- Table names, column names, are black,
- SQL Commands (SELECT, FROM, …) will turn blue,
- Strings, depicted by simple quotes ‘ and ‘ will be red,
- System functions will be pink (purists would say magenta),
- Operators such as AND, OR, will be grey,
- Comments will be green
See for yourself:

And there’s more!
The Result Pane
The results pane shows us the results of our query (if we’re doing a ‘SELECT’):

It also provides information on the number of records impacted under the ‘Messages’. This is what you get if there were no errors:

And in case something wasn’t right, you’ll be notified, with some support on how to debug, e.g. ‘Invalid column name ‘Departm’.

The Result pane also provides us with general information on:
- the state of the query:


- its performance i.e. how long it ran and the number of records returned, here it was executed in less than 1 second, and returned 0 rows because our table or our query had 0 matches.

Final Words and What’s Coming Next
That’s it for now! I aimed at keeping it short and sweet, and entertaining, although there weren’t any memes in this one.
- I hope that you’re now feeling confident to create databases and tables, both using the design tool or by writing a query.
- You’re also empowered to drop tables, but be careful, as unless your database is backed up, you’ll lose the underlying data. You would also bring to a halt any applications, procedures or tables depending on it.
- You know about the result pane, and the metrics on query performance and the number of returned rows, the first approach to debugging.
- I couldn’t stress more on the importance of proper tables and database naming, as they will follow you over time, and might come to haunt you if you chose poor names.
Happy Coding!
Thanks for reading! Enjoyed this story? Join Medium for complete access to all my stories.
Continue the Journey with Episode 3 on CRUD Operations, Primary & Foreign Keys!
Learn SQL Server Management Studio – Part 3 CRUD Operations, Primary & Foreign Keys
Or select another episode of your choice
In Episode 4, we discuss schemas & the main normalization steps – Part 4 Schemas & Normalization
In Episode 5, we cover Stored Procedures & Scheduling, this is a real beast. I can’t stress enough how this will facilitate and automate your daily (data-)life. Part 5 Stored Procedures & Scheduling
In Episode 6, we introduce SSIS Packages for ETL, and reviewed out to import and export data between two databases, and between a database and Excel. Part 6 SSIS Packages Introduction
In Episode 7, we connect SQL Studio to PowerBI and build our first visuals. [Part 7 Connect to PowerBI & First Visuals](http://Part 7 Connect to PowerBI & First Visuals)