Should I name tables based on date & time of creation, and use EXEC() and a variable to dynamically refer to these tables?

user_4976192 0
Viewed 44

TL;DR: Current company creates new table for every time period, such as sales_yyyymmdd, and use EXEC() to dynamically refer to table names, making the entire query red and hard to read. What kind of changes can I suggest to them to both improve readability and performance?

Some background: I'm a Data analyst (and not a DBA), so my SQL knowledge can be limited. I recently moved to a new company which use MS SQL Server as their database management system.

The issues: The DAs here share a similar style of writing SQL scripts, which includes:

  • Naming tables based on their time of creation, e.g. data for sales record everyday will be saved into a new table of that day, such as sales_yyyymmdd. This means there are a huge amount of tables like this. Note that the DAs has their own database to tinker with, so they are allowed to created any amount of tables there.

  • Writing queries enclosed in EXEC() and dynamically refer to table names based on some variable @date. As such, their entire scripts become a color red which is difficult for me to read.

  • They also claim that enclosing queries in EXEC(), per their own words, makes the scripts running entirely when stored as scheduled jobs, because when they write them the "normal way", sometimes these jobs stop mid-way.

My questions:

  • Regarding naming and creating new tables for every new time period: I suppose this is obviously a bad practice, at least in terms of management due to the sheer amount of tables. I suggested merging them and add a created_date column, but the DAs here argued that both ways take up the same amount of disk space, so why bother with such radical change. How do I explain this to them?

  • Regarding the EXEC() command: My issue with this way of writing queries is that it's hard to maintain and share to other people. My quick fix for now (if issue 1 remains), is to use one single EXEC() command to copy the tables needed to temp tables, then select these temp tables instead. If new data need to be merged, I first insert them into temp tables, manipulate them here, and finally merge into the final, official table. Would this method affect performance at all (as there is an extra step involving temp tables)? And is there any better way that both helps with readability and performance?

  • I don't have experience scheduling jobs myself on my own computer, as my previous company has a dedicated data engineering team that take my SQL scripts and automate the job on a server. My googling also has not yielded any result yet. Is it true that using EXEC() keeps jobs from being interrupted? If not, what is the actual issue here?

I know that the post is long, and I'm also not a native speaker. I hope that I explain my questions clearly enough, and I appreciate any helps/answers.

Thanks everyone, and stay safe!

sql-server sql database
3 Answers

While I understand the reasons for creating a table for each day, I do not think this is the correct solution.

Modern databases do very good job partitioning data, SQL Server also has this feature. In fact, such use-cases are exactly the rason why partitioning was created in the first place. For me that would be the way to go, as:

  • it's not a WTF solution (your description easily understandable, but it's a WTF still)
  • partitioning allows for optimizing partition-restricted queries, particularly time-restricted queries
  • it is still possible to execute a non-partition based query, while for the solution you showed it would require an union, or multiple unions
user_19616340

As everybody mentioned in the comments, You can have single table Sales and have extra column in the table to hold the date, the data got inserted.

  1. Create table Sales to hold all sales data
CREATE TABLE Sales
(
col1 datatype
col2 datatype
.
.
InsertedDate date  --This contains the date for which sales data correspond to 
)
  1. Insert all the existing tables data into the above table
INSERT INTO sales
SELECT *,'20200301' AS InsertedDate FROM Sales_20200301
UNION ALL
SELECT *,'20200302' AS InsertedDate FROM Sales_20200302
.
.
UNION ALL
SELECT *,'20200331' AS InsertedDate FROM Sales_20200331
  1. Now, you can modify EXEC query with variable @date to direct query. You can easily read the script without them being in the red color.
DECLARE @date DATE = '20200301'
SELECT col1,col2...
FROM Sales
WHERE InsertedDate = @date

Note: If data is huge, you can think of partitioning the data based on the Inserteddate.

user_6349350

The purpose of database is not to create tables. It is to use tables. To be honest, this is a nuance that is sometimes hard to explain to DBAs.

First, understand where they are coming from. They want to protect data integrity. They want to be sure that the database is available and that people can use the data they need. They may have been around when the database was designed, and the only envisioned usage was per day. This also makes the data safe when the schema changes (i.e. new columns are added).

Obviously, things have changed. If you were to design the database from scratch, you would probably have a single partitioned table; the partitioning would be by day.

What can you do? There are several options.

You do have some options, depending on what you are able to do and what the DBAs need. The most important thing is to communicate the importance of this issue. You are trying to do analysis. You know SQL. Before you can get started on a problem, you have to deal with the data model, thinking about execs, date ranges, and a whole host of issues that have nothing to do with the problems you need to solve.

This affects your productivity. And affects the utility of the database. Both of these are issues that someone should care about.

There are some potential solutions:

  1. You can copy all the data into a single table each day, perhaps as a separate job. This is reasonable if the tables are small.
  2. You can copy the latest data into a single table.
  3. You can create a view that combines the data into a single view.
  4. The DBAs could do any of the above.

I obviously don't know the structure of the existing code or how busy the DBAs are. However, (4) does not seem particularly cumbersome, regardless of which solution is chosen.

If you have no available space for a view or copy of the data, I would write SQL generation code that would construct a query like this:

select * from sales_20200101 union all
select * from sales_20200102 union all
. . .

This will be a long string. I would then just start my queries with:

with sales as (
      <long string here>
     )
<whatever code here>;

Of course, it would be better to have a view (at least) that has all the sales you want.

user_11440350
Related