Creating a Database in SQL Server Express via SSMS

It is all fine and good to be able to manipulate a database, but that will get you nowhere in the Real World unless you can also create a new database and add the information that you want in it. I am not all that unusual in that I have a lot of photographs (of course, being married to a photographer aids in this endeavor), and I like to organize them by subject. So, I need a place to store large files, I want to have some information about them (such as subject, date taken, etc.), and I want to have them organized – a perfect job for database!

The hardest part about this is getting organized beforehand. Knowing what fields you will want will make this whole process much easier, so it is good to find out exactly what it is that you want, then create the database and the tables. In this case, I only really want one table, there is no need to do any more than that, as this is an extremely simple database. I will want a field (which becomes a column if you are thinking about it that way) for the actual photo, and one each for subject, date taken, a Primary Key, and a Description field where I can make free-text comments about who is in it, where it was taken, etc.

To create the new Database, log into SQL Server Management Studio (SSMS), and when you see the list of databases on the left hand side, right click Databases and you will see the option for “New Database”. Click this, and a Wizard-type dialog will pop up and ask you for the name of the Database, and the owner. You can also set growth options here, and in the left navigation pane, you can click on Options and Filegroups to set more options here as well. For our purposes, we will keep the defaults, at some point I may go through all the options here but for now, we should be ready to proceed to the next step.

To create the table we need next, expand the new database you just created (I’ve surprisingly called mine “Pictures”), and you will see an entry called Tables – right click this and select “New Table”. This will now start a Wizard where you can type in the Column Name, data type for the column, and there is also an “Allow Nulls” field. I will allow nulls for every field except, of course, the Primary Key. To set the Primary Key, while you are looking at the table in Design Mode in SSMS, right-click the field name you want to set as the PK, and click the “Set Primary Key” item.

This is also possible through scripts, you can see an example of this in my previous post “Scripts in SQL Server”. Both methods are entirely valid, it just depends on your situation and your preferences as to which method you choose to create a database.

I will be on vacation for the next two weeks, but when I get back, I will attempt to get data into the database, so stay tuned!