Table Naming Convention in SQL Server

Question

Table Naming Convention in SQL Server

Learning1

SSC Eights!

Points: 898
More actions
May 22, 2014 at 4:44 pm

#307639

Hello Experts,
We are extracting data from multiple sources and staging the data in SQL Server tables. Once, the data is staged, we are doing some manipulations/calculations and create another set of tables which feed the SSRS reports. We would like to differentiate these staging tables and report tables using naming convention.
What is the best practice to name the tables in this scenario? Using Prefix or Suffix?
Example 1 (using Prefix):
RPT_TableName
STG_TableName
(OR)
Example 2 (using Suffix):
TableName_RPT
TableName_STG
Thanks inadvance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Learning1 (5/22/2014)
Hello Experts,
We are extracting data from multiple sources and staging the data in SQL Server tables. Once, the data is staged, we are doing some manipulations/calculations and create another set of tables which feed the SSRS reports. We would like to differentiate these staging tables and report tables using naming convention.
What is the best practice to name the tables in this scenario? Using Prefix or Suffix?
Example 1 (using Prefix):
RPT_TableName
STG_TableName
(OR)
Example 2 (using Suffix):
TableName_RPT
TableName_STG
Thanks inadvance.

I lean towards the second method just so that it's apparent that both tables exist when looking at the Object Explorer but, "It Depends". If it's necessary to quickly find things by operational area, the first method may be better.

Whichever method you and the folks you work with settle on, remember that consistency will become a major key.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I'll throw another alternative out there for you.

You could employ schemas to help group/separate these tables via functional areas.

So you could see something like

Reporting.SomeTable

Staging.SomeTable

If you are concerned about finding an object or ensuring it exists in the different schemas, a filter in SSMS is easy enough to use. Or you could query sys.objects to find it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

I'd also suggest going with the schema.

Or, if you have to name the tables, I'd go with naming them MyTableStaging or something like that instead of using old school Hungarian notation. We have plenty of room for naming objects, so we may as well name them using clear language.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Grant Fritchey (5/23/2014)
I'd also suggest going with the schema.
Or, if you have to name the tables, I'd go with naming them MyTableStaging or something like that instead of using old school Hungarian notation. We have plenty of room for naming objects, so we may as well name them using clear language.

Totally agree. Nothing like a shortened name that could represent something different than what you'd think. Prevent the confusion by clear naming.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Learning1 SSC Eights! Points: 898 More actions · Answer 5

Thank you all for your suggestions. We are using Schemas for another different reason. We will go with the "TableNameStaging" convention.