Problem
Some decisions in life are easy, like whether to drink that second cup of coffee. But when it comes to databases, things get complicated fast. Developers often seek my input on adding tables and columns. A common question arises: Should they create a new table or expand an existing one by adding columns? This decision can be tricky because it depends on several factors, including query performance, future growth, and the complexity of implementing either solution. While adding one or two columns to an existing table may seem the easiest option, is it the best long-term solution? In this article, we look at whether it is better to add new columns versus a new table in SQL Server.
Solution
This article presents three scenarios where adding a new table might be better than adding a column to an existing table. We’ll explore an example where our database needs to grow, and we need to decide whether to add a new column or a new table. Spoiler alert: I lean towards a new table in each situation. By the end, you’ll be ready to make more informed decisions about your growing database.
Supporting a Growing Database
I can’t recall working on a database for an active application where developers weren’t constantly adding columns and tables. Business stakeholders come forward and ask for new functionality, which often requires storing more data. A growing database is generally a good sign of active use—if your database isn’t growing, it could mean people aren’t using it unless you perfected the design from the start.
Sometimes, decisions about adding a new column or creating a new table are clear-cut. For instance, if you have an existing Employee table, the business might ask you to track related attributes, like office locations or termination dates. In most cases, adding those columns to the existing table makes sense. However, even these cases require further thought. For example, you may want to add an Office table if one doesn’t exist and then create a foreign key reference in the Employee table. Employees usually share an office name, and creating a lookup table with the name often makes sense. As this example shows, designing tables can get complicated quickly.
Demo Dataset
In the following sections, I’ll present three situations where you might need to add a table rather than a column. To illustrate these concepts, let’s start by building a demo dataset. We’ll create two tables to track our video game collection and, most importantly, how long it takes to beat a game.
- Video Games – This dimension table stores information about video games like title, publisher, etc.
- VideoGameTracker – Functions as a fact table, capturing measurable data about playtime.
-- mssqltips.com
USE [master];
GO
IF DB_ID('TimeTrackerDemo') IS NOT NULL
BEGIN
ALTER DATABASE TimeTrackerDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TimeTrackerDemo;
END;
GO
CREATE DATABASE TimeTrackerDemo;
GO
ALTER DATABASE TimeTrackerDemo SET RECOVERY SIMPLE;
GO
USE TimeTrackerDemo;
GO
CREATE TABLE dbo.VideoGames
(
VideoGameId INT NOT NULL,
Title NVARCHAR(256) NOT NULL,
Developer NVARCHAR(256) NOT NULL,
Console NVARCHAR(256) NOT NULL,
Multiplayer BIT NOT NULL,
ReleaseDate DATE NULL,
CONSTRAINT PK_VideoGames_VideoGameId
PRIMARY KEY CLUSTERED (VideoGameId)
);
INSERT INTO dbo.VideoGames
(
VideoGameId,
Title,
Developer,
Console,
Multiplayer,
ReleaseDate
)
VALUES
(1, 'Super Street Fighter II Turbo', 'Capcom', 'SNES', 1, '1993-08-01'),
(2, 'Mortal Kombat II', 'Midway Games', 'SNES', 1, '1993-09-10'),
(3, 'Super Mario Bros. 3', 'Nintendo', 'NES', 1, '1990-02-10');
CREATE TABLE dbo.VideoGameTracker
(
TrackerId INT NOT NULL,
VideoGameId INT NOT NULL,
DateStart DATE NOT NULL,
DateEnd DATE NULL,
CONSTRAINT PK_VideoGamesTracker_TrackerId
PRIMARY KEY CLUSTERED (TrackerId),
CONSTRAINT FK_VideoGameTracker_VideoGames
FOREIGN KEY (VideoGameId)
REFERENCES dbo.VideoGames (VideoGameId)
);
INSERT INTO dbo.VideoGameTracker
(
TrackerId,
VideoGameId,
DateStart,
DateEnd
)
VALUES
(1, 1, '01-01-2025', '01-15-2025');
GONew Requirements
Creating a new data structure always reminds me of painting a room and being in awe of the gorgeous, stain-free walls. If you have kids, you know those walls don’t stay stain-free for long. Similarly, new business requirements to add features come down the pipeline and stain your once-spotless database.
In our example, we need to start tracking our book collection and the time it takes to read a book. This new data contains attributes only associated with books. Can we agree that creating a Books table like the VideoGames table is common sense?
-- mssqltips.com
CREATE TABLE dbo.Books
(
BookId INT NOT NULL,
Title NVARCHAR(256) NOT NULL,
Author NVARCHAR(256) NOT NULL,
ReleaseDate DATE NULL,
CONSTRAINT PK_Movies_MovieId
PRIMARY KEY CLUSTERED (BookId)
);
GO
INSERT INTO dbo.Books
(
BookId,
Title,
Author,
ReleaseDate
)
VALUES
(1, 'Atomic Habits', 'James Clear', '10-16-2018'),
(2, 'Deep Work', 'Cal Newport', '01-05-2016'),
(3, 'Tiny Habits', 'BJ Fogg', '12-31-2019');
GOImagine that lots of front-end code is involved here. The Dev team wants to add the time for finishing a book to our current VideoGameTracker table to reduce code refactoring, and they present the code below.
-- mssqltips.com
ALTER TABLE dbo.VideoGameTracker
ADD BookId INT NULL, CONSTRAINT FK_VideoGameTracker_Books
FOREIGN KEY (BookId)
REFERENCES dbo.Books (BookId);
GO
ALTER TABLE dbo.VideoGameTracker ALTER COLUMN VideoGameId INT NULL;
GO
Adding a column to track books in the current table isn’t terrible at first glance. Ideally, the Dev team would come to me and ask my opinion early on. In the worst case, they would ask me to approve a pull request with the code. Looking at the code snippet above, here is what I would point out to them.
Specific Table Names
Working on any new project with a name is easier for me. I’m the first to make up a name—even if it’s silly—and stick with it until someone else sets a different one in stone. So, a name is essential.
From a SQL Server perspective, we can easily change a table name using tools like sp_rename, but this can significantly impact other database objects like stored procedures and views. However, if you name a table to hold specific data, the data should match the table name. An orange doesn’t become an apple just because you put it into an apple basket. It frustrates me when I see tables whose data has nothing to do with the name.
There could be circumstances where you can’t change a table name, like dependencies on an external application, existing reports, or development overhead. As a rule, if you should change the table name, that might indicate you need to add a new table.
If you can change the name and are dead set on not creating a new table, then at the very least, change the table’s name. Unless you break something with the name change, everyone creating reports will thank you.
Changing Foreign Key References to NULL
I’m a fanatic about adding foreign key references to help ensure database integrity. Some people don’t like them because of the added overhead from constraints, but I think the overhead is worth the peace of mind. A foreign key establishes a relationship between two tables where one acts as the parent and the other as the child. Yes, you can create a foreign key that references the same table as the primary key.
With our VideoGameTracker table, if we try to add a book reference column, we need to change the VideoGameId column to allow NULLs. It’s not the end of the world because we at least have a foreign key reference.
A NULL represents an unknown value; there’s nothing inherently bad about columns allowing NULL. However, when we start allowing NULLs for a foreign key, it’s tempting to continue allowing NULLs as a workaround for other business requirements. The bottom line is that when someone suggests changing an existing foreign key reference to allow NULLs, it likely indicates that you should consider adding a new table.
Growth Opportunity
Growth is usually good—except for debt, waistlines, and stress. If your table grows, it’s a sign that business is going well. However, we should add a new table if the VideoGameTracker table grows horizontally—especially when you add columns specific to books. Don’t fall for promises like, “This is a one-time thing, and we’ll never add another column; we promise.” The person might mean well, but they won’t always be around. How can you be sure that you will never add another unrelated column? Easy, create a new table to store information about books.
Let’s say we add attributes like the current page number or whether the book is physical or digital. These have nothing to do with a video game—at least none I’ve played. This approach would result in several columns where the value is always NULL whenever we add a book record. In those cases, a NULL no longer represents an unknown value. It represents something that will never exist.
Similarly, if someone tells me, “Here’s the new design, but we’ll go back and clean it up later,” that’s a red flag. It’s like moving into a house expecting to make improvements later and discounting your future self. Remember, be kind to your future self and other developers.
Giving Advice
I’m guilty of giving advice without fully understanding the problem someone is trying to solve—just ask my wife. If we don’t fully understand the constraints someone is under, it’s easy to become judgmental about their decisions. Sure, I can say you should add a column or table all day, but my biases shape that advice. A developer might face time pressure or numerous other work challenges.
We can’t control other people’s actions even after giving advice. What if you’re on vacation when someone modifies the design, and you’re stuck with it? All you can do is voice your concern and hope people listen. As Samuel Taylor Coleridge said, “Advice is like snow; the softer it falls, the longer it dwells upon, and the deeper it sinks into the mind.” If someone listens even 80% of the time, that’s a win in my book.
Clean Up
If you’re done with testing, remember to run the code below to delete the demo database.
-- mssqltips.com
USE [master];
GO
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'TimeTrackerDemo')
BEGIN
ALTER DATABASE TimeTrackerDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TimeTrackerDemo;
END;
GOSummary
Finally, an example with cats (I’m a cat lover, but dogs are okay too) comes to mind. If we have a table named Cats where you track all the fabulous felines you adopt, let’s imagine you only track Maine Coons because that’s all you’ve adopted so far. Then, one day, you decide to adopt a Persian. Well, you wouldn’t create a new table to add the Persian cats, would you? They share many of the same attributes: four legs, a tail, and a purr. Instead, I would add a column that represents the breed of the cat and call it a day. Alternatively, if your audience isn’t SQL-savvy, you could illustrate the point using an Excel workbook.
Now let’s wrap things up. In this article, we reviewed three key signs that suggest when to create a new table instead of adding a column to an existing one. However, there are scenarios where we may have no choice but to add columns to an existing table. For example, you might work with an application without complete backend control or can’t modify the front end to reference the new table. In those situations, the priority is to deliver business value despite these limitations.
Do you have any further advice for spotting red flags that suggest you should add a new table versus a column? If so, please share your ideas in the comments below.
Next Steps
- Are you curious about the performance implications of constraints? Check out my article, “Performance Impact of SQL Server Check Constraints,” for an overview.
- Need to rename a table in one of your databases and are unsure about all the effects? Greg Robidoux wrote the informative article “Renaming SQL Server Database Objects and Changing Object Owners” to guide you.
- Itzik Ben-Gan wrote several articles related to the complexities of NULL placeholders. If this topic grabs your attention, start with “NULL complexities – Part 1.”
