October 8, 2021 at 7:40 pm
I am trying to establish the standards for my database naming convention. Can you please provide me the best standards which worked for you. Thanks in Advance!
October 8, 2021 at 9:05 pm
"best" standards really depend on your environment and how often objects will change and how your team works.
For example, I like naming my tables with the name of the entity it will contain in singular form (ie it defines a row). So I like having a "person" table vs a "people" table, or employee vs employees.
Others prefer having the table name describe the contents of the table and thus prefer it in plural form.
With stored procedures, I like a 2-3 letter acronym at the start to define the process (adm for admin, fin for finance, etc) followed by _ and then what type of procedure it is (read only (RO), write only (WO), read/write (RW)) followed by another _ then the name of the procedure where it will describe the end result of the process upon success. Such as "adm_RW_ResetPassword".
Views, we prefix with "vw_" followed by the purpose such as "vw_EmployeeSalary" which would map the Employee table and Salary table together in a view.
Triggers we prefix with trg followed by the type of trigger (I for insert, U for update, D for delete, and can combine them like IU for insert or update) followed by _ and then what the trigger does. So "trgIUD_AuditLog" for logging an audit trail of changes on a table for example.
Our overall document for standards is lengthy but contains 2 main sections - requirements and recommendations. Requirements has naming conventions that are hard requirements and not following it will result in code verification failure. Recommendations is things that we recommend to do, but won't cause code verification failure. Our SQL Coding standards expand past naming conventions and include things like DROP and CREATE vs ALTER and Operations and keywords to avoid (such as CURSORs and NOLOCK).
We like having the hard requirements (don't use deprecated datatype for example, or don't start a stored procedure with sp_), and we like having recommendations (such as avoid using OPTION(RECOMPILE)) as it gives the developers flexibility. Nothing more annoying than spending a month working on the SQL back end for a new application only to have it fail verification because you used tabs instead of spaces in your code.
The best standards that work for my team (tiny little team of 3 developers who also do DBA work) is to make it flexible and have open communication during code review. If I see something that I think would be hard to support, I bring it up with the developer and we talk through it. Sometimes it is the way to go, sometimes we come up with more efficient ways to code it. I worked with a developer to remove a cursor from their code and it cut the execution time down by about 80%. Now, if it was an urgent release and needed to go live immediately, the cursor could have remained as we have it as a "recommendation" not a "requirement". It allows for flexibility in the code and for faster release cycles with the intention to go back and improve the code at a future date if needed. Sometimes, the code runs "good enough", so we don't bother going back to fix things. A report (for example) that takes 20 seconds to load, but is a scheduled report that fires in the middle of the night and nobody looks at live data doesn't need to be optimized.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 8, 2021 at 9:23 pm
In general:
Use clear expressive names and don't abbreviate unless necessary, abbreviation/acronym is almost universally understood in your industry and company, and/or full descriptive name that distinguishes it from similar entities would be insanely long, or even exceed RDBMS limits In general, and within limits, comprehension is better with full expressive names. We're decades beyond need to keep things to 8 characters.
Tables:
Columns:
Stored procedures and functions:
October 8, 2021 at 9:33 pm
For the database itself, our naming convention is to either name it after the application that uses it, or if multiple applications are using it, then the processes that are contained inside. For example, "Operations" for all production operation related data, or CONFIGURATION for application configuration related data, or ServiceDesk for the database that will hold all of the Service Desk data, or MessageManager for our service broker centralized logging database.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 8, 2021 at 9:33 pm
I agree w/ Mr. Gale that using prefix "vw" for views and "trg" for triggers is a good, common, reasonable exception to the "don't prefix w/ object type" rule. And these are generally less common objects. Please don't use "tbl" prefix for tables, nor "prc" or "proc" prefix for stored procedures -- usually two of your most common objects!
(though I personally still prefer using a suffix -- something as simple but useful as clicking on the first letter of an object in the UI to navigate to an object rather than having to searching is broken if every object starts w/ "v" or "t").
October 9, 2021 at 12:38 am
Give your databases names that reflect their business purpose. Don't use prefixes or suffixes in the name to indicate "database". MS got this half right: MS has dbs "master", "model", "msdb" (why db??) and "tempdb" (why db??).
In fact, be consistency is the most important thing. So, consistently don't include a prefix/suffix for type in base object names.
Thus, don't use "vw_"/"_view"; "proc"/"sp"; "fn"/"func", etc. For a view, what happens later if that view then needs to become an actual table ... are you really going to rename it everywhere it's used? No, of course not, the name will become inaccurate and misleading. Instead, give a view a name that matches its business function, just like with all other base objects.
As to triggers, and other elements related to tables, I don't object to TRG and other suffixes in the name. They aren't base objects.
Also, I don't start any name with a type prefix (PK_, TRG_, etc.). People seem to have blindly copied this from MS. Instead, for tables, I start all names with the table name then add a suffix to match the type. For example, "table1__TRG_INS" (or INSERT) for an insert trigger; "table1__CL" for the clus index on a table; "table1__IX_column_b" for a nonclus index keyed on column_b; etc.. This approach insures that each table's objects sort together and that each index has its own unique name.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 9, 2021 at 12:42 am
With stored procedures, I like a 2-3 letter acronym at the start to define the process (adm for admin, fin for finance, etc) followed by _ and then what type of procedure it is (read only (RO), write only (WO), read/write (RW)) followed by another _ then the name of the procedure where it will describe the end result of the process upon success. Such as "adm_RW_ResetPassword".
Using a prefix to group functionality for procs makes perfect sense.
I very strongly object, however, to the RO/RW/WO, etc., approach. One big problem is: what do you when a proc that was previously read only then needs to do an update? Do you rename the proc everywhere? No, you don't, because it's too much hassle. So now you have an inaccurate name.
Instead, leave those types of details out of the name. Just name the proc after the business task it does. If you want to maintain write info, use a table to store that. It can be made more accurate and you can use it for searches and analysis.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 11, 2021 at 4:53 am
Whatever you choose, please, please, OH PLEASE, focus first on clarity and communication. Any other kind of rigid enforcement of some particular little persnickety rule must come second. The purpose of a naming standard must be to assist communication, not to hide stuff from the uninitiated.
"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
October 11, 2021 at 1:20 pm
Keep in mind, too, that the point of naming is to give accurate business names to objects, not to make things easier for developers. That is, just because a naming style is convenient for developers to use does not automatically make it a good style, no matter how much developers insist otherwise.
And, yes, the "vw_" thing would fall under that convenient-for-developer-but-bad-approach-for-business/overall functionality naming.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply