Database Fundamentals #12: Adding Data with SSMS GUI

In the previous Database Fundamentals, I argued that you should be learning T-SQL, yet the very next post I’m showing you how to use the GUI. What’s up?

Why the GUI?

It’s a very simple reason. I want to show you what it is so that I’m not hiding things. However, showing it to you will quickly expose the weaknesses inherent in using the SSMS GUI for direct data manipulation. It’s a poor choice. However, we’ll understand how it works at the end of this post. I’ll also cover it in other posts, showing how to UPDATE and DELETE data using the GUI. They will further illustrate the weaknesses. You will however know how it works.

Data Entry through the GUI

If you are not already connected to the server, do so now. Navigate  in  the Object Explorer window until you have the Tables folder open. Right click on a table. From the context menu select the menu choice “Edit Top 200 Rows.” This will open a window known as the Query Designer User Interface. Strictly speaking this is not a window for editing data. Instead, it’s a window that has been put together to assist you in building your own TSQL queries. Keep that in mind as you explore this window.

If no defaults have changed and you have not experimented with entering data, then you should see a blank grid something like an Excel spread sheet.

There is no data in the table. If there had been, you’d see up to 200 rows on display in this window. There are a number of other functions associated with this window, but we won’t be covering them all here. For now, we’ll just use this window to add data to the database. It’s already prepared a row for you. All you have to do is click inside the grid and start typing. If the first column you start trying to type in is the PersonID column, you might notice that you can’t type into it at all. This is because I identified the PersonID column as an identity column when the table was created. This will be filled automatically by SQL Server. Click on the next column using your mouse pointer or use the Tab key to change the focus to that next column.

This column I can type into. I can type in a value for the FirstName column then hit tab again. When I do this, a red exclamation point appears. This is because I’ve modified the data in that column. If you hover your mouse pointer over the exclamation point, an informational window will appear that looks a lot like this:

As I move through the grid adding data, the same thing will happen. In the CreateDate column, if I were to type in the value ‘mydate’ instead of a valid DATETIME value then hit the tab button again, I’ll immediately get an error because you can’t enter data into that column that SQL Server doesn’t recognize as a DATETIME value. The error message looks like this:

Clicking on the OK button and the focus will return to the CreateDate column. I can fill in the rest of the row until I hit the last column. If I tab from there, I’ll see the exclamation points disappear and where there was no value in the PersonID column, assuming we haven’t entered other information, there will be a value of ‘1’. This is the automatic identity value at work. This row has been added to the database. You didn’t have to save it or anything. It all occurred automatically.

If I check the value in the CreateDate column, because it’s a DATETIME column, SQL Server assumed I meant for the time to be Midnight for the date I entered. This part of the difficulties of working with the GUI. Also, any foreign key values I’m going to have to go and find.

Conclusion

The SSMS GUI does supply a mechanism for adding data, but it’s not adequate. A better way to do it is with T-SQL. That’s the next Database Fundamentals blog post.