Database Fundamentals #10: Modifying Tables

Invariably, either while building a new database, or while maintaining old ones, you will find that the business needs change or that you’ve made a mistake. Either way, you will need to modify the tables that you’ve created. Change is inevitable and luckily SQL Server takes that into account, providing you with mechanisms to modify structures after you’ve created them.

Important Warning

One of the fundamental concepts of databases is that the data is stored. You can’t simply throw data away when you need to make a change to a table. There are two levels of changes you can make to a table. The first, is a change that won’t affect data in the table. These are simple changes and we’ll cover them in this blog post. The second level of change is a change that can affect data in the table. This is a much more complicated change requiring multiple steps to protect the data in the table. This will be covered in later blog posts after you’ve learned more about how to work with data and structures.

Using the GUI to Modify a Table

To modify a table through the GUI you’ll need to navigate to the table and open the Table Designer window by right clicking on the table and choosing the appropriate menu choice. When you open this window you should note that the cursor is in a new, blank column, waiting for you to start typing. See the focus here:

To add the new column just supply a column name and pick the data type. After you have typed the name, the Allow Nulls box will be automatically selected, making this column allow NULL values. Leave that as is. Requiring data in a new column also requires you to create default data. That will be covered in another blog post. Saving the table will add the new column you just defined to it.

T-SQL Can Modify Tables Too

You can also use T-SQL to modify a table. Suppose the business requirements changed and you now need to capture the government identifier for a person, such as their Social Security Number in the US. You’ll want to add a column for storing this information. This T-SQL statement will add a new column to the table:

ALTER TABLE Personnel.Person
ADD GovernmentID CHAR(11) NULL;

The statement should make a lot of sense. You’re telling SQL Server to ALTER a TABLE. Then you’re specifying exactly which table, including the schema, Personnel.Person. You’re added a column, GovernmentID, with a specified data type, CHAR(11). T-SQL is frequently quite straightforward in how the language modifies objects.

Generating Scripts

While you can type all the scripts directly into T-SQL, believe it or not, the GUI can also generate scripts for you. Navigate back to the Table Designer window. Define one more column. Before you hit save, look up at the Table Designer toolbar. The button all the way on the left is used to script out whatever changes you have made within the Designer window:

Pushing the button will open the Save Change Script window. This window shows you the script that SQL Server would generate to make the changes you’ve defined. SQL Server generates a much more complicated script than is strictly needed, but it’s doing that to try to protect you from accidents that you could introduce if you just took the scripts strictly as defined. It does this by setting connection information and a transaction. You don’t need to do all this. But, if you look in the Save Change Script window, you’ll see an ALTER TABLE statement, just like what you defined all on your own.

If you click Yes here you can save the script and use it to update the table at a later time. If you watch, most of the GUI windows offer you the capability of saving a script that does whatever it is you’ve tasked the GUI with completing. This is a great way to learn more about how SQL Server works. It’s also a handy way to generate a script so that you can run it on more than one database in the event you need to .

Conclusion

The basic changes to a table are very easy and straightforward. It’s just a good idea to always remember that frequently your changes won’t be basic. Then you have to employ quite a bit more work than we did here in this blog post.