June 4, 2012 at 9:59 am
Hello all, I would like your opinions on the following standards I would like to enforce if possible with SQL server policies with the ones that have a * next to them.
•Use SQL Source Control for all SQL Schema Changes (tables, Stored Procedures, Views, etc..)
*No longer hardcoding Server names into SQL Code, an alias should be used. If there is no alias, contact DBA team to create one.
*No longer hardcoding Link Server names, an alias should be used. If there is no alias, contact DBA team to create one.
*Every table should be documented with its purpose and a brief explanation of the data it contains. Some tables are self-documenting and don't really need much of an explanation.
*Columns should be self-documenting as well with a short yet descriptive column name. There are some scenarios where you know the column name is confusing and there just isn't another good option. In this case you should document the column with a description.
*All scripts should provide the USE (Database Name) statement to begin script in proper database.
*All Tables should contain a {Schema} under which the tables reside.
*Procedures and Functions are named in the following patterns: {Schema}.usp_{PrimaryItem}_{Action}.sql (Procedure) & {Schema}.fn_{PrimaryItem}_{Action}.sql (Function). This is more flexible than the actions associated with tables as it must support a variety of behaviors
Does anyone have examples or website I can view about Recent T-SQL development standards as well? Thanks
June 4, 2012 at 10:09 am
Some of these you may be able to enforce, but in some cases, you've provided things that aren't really patterns, but formatting standards.
For example, a "use" statement for scripts. SQL Server has no idea what a "script" is. It only takes the code sent to it from the front end tool. A script is a file construct, and "scripts" are not run in SQL Server. Only batches.
The idea of having a schema with every table is fine, but every table has a schema. What you are asking for is a schema explicitly included in a create or alter table statement. You may be able to write a check for this, but it might not be easy. Are you looking for only existing schemas?
Ultimately the best way to do all this is to set stricter security in all server environments. Then if a Dev does something that doesn't match, you catch it with another pair of eyes that scan all scripts being run. You also prevent them from creating new objects or changing things that don't match your standards.
Most of these are pretty easy habits to get into, but you need some buy in from management, and then a second pair of eyes that scan code being run.
June 4, 2012 at 10:13 am
What do you mean by "no longer hardcoding... an alias should be used." ? Not sure that hardcoding is really really related to an alias... An alias still can be hardcoded or parameterized.
Jared
CE - Microsoft
June 5, 2012 at 8:17 am
Hi Steve, to answer your question about schema, I want to make sure developers don't use the default dbo schema but a more relevant one for example customer.name sales.leads instead of a table just called name or leads. Am I making sense? I don't want the policy to look at bad existing tables without schema but going forward when someone create a new table it will apply the policy.
Eventually I will have a SQL developer who will be reviewing all t-sql code before it is deployed.
Steve Jones - SSC Editor (6/4/2012)
Some of these you may be able to enforce, but in some cases, you've provided things that aren't really patterns, but formatting standards.For example, a "use" statement for scripts. SQL Server has no idea what a "script" is. It only takes the code sent to it from the front end tool. A script is a file construct, and "scripts" are not run in SQL Server. Only batches.
The idea of having a schema with every table is fine, but every table has a schema. What you are asking for is a schema explicitly included in a create or alter table statement. You may be able to write a check for this, but it might not be easy. Are you looking for only existing schemas?
Ultimately the best way to do all this is to set stricter security in all server environments. Then if a Dev does something that doesn't match, you catch it with another pair of eyes that scan all scripts being run. You also prevent them from creating new objects or changing things that don't match your standards.
Most of these are pretty easy habits to get into, but you need some buy in from management, and then a second pair of eyes that scan code being run.
June 5, 2012 at 8:21 am
Hi SQLKnowItAll, when I say hardcoding, our developers were putting the actual server name for example MyServerName.Database instead of using a DNS alias for a connection string or when writing T-SQL code when using Linked Servers. I have broken the habit for most of the developers, I just really want to enforce it now with some kind of policy.
SQLKnowItAll (6/4/2012)
What do you mean by "no longer hardcoding... an alias should be used." ? Not sure that hardcoding is really really related to an alias... An alias still can be hardcoded or parameterized.
June 5, 2012 at 8:22 am
dbdmora (6/5/2012)
Hi Steve, to answer your question about schema, I want to make sure developers don't use the default dbo schema but a more relevant one for example customer.name sales.leads instead of a table just called name or leads. Am I making sense? I don't want the policy to look at bad existing tables without schema but going forward when someone create a new table it will apply the policy.Eventually I will have a SQL developer who will be reviewing all t-sql code before it is deployed.
Steve Jones - SSC Editor (6/4/2012)
Some of these you may be able to enforce, but in some cases, you've provided things that aren't really patterns, but formatting standards.For example, a "use" statement for scripts. SQL Server has no idea what a "script" is. It only takes the code sent to it from the front end tool. A script is a file construct, and "scripts" are not run in SQL Server. Only batches.
The idea of having a schema with every table is fine, but every table has a schema. What you are asking for is a schema explicitly included in a create or alter table statement. You may be able to write a check for this, but it might not be easy. Are you looking for only existing schemas?
Ultimately the best way to do all this is to set stricter security in all server environments. Then if a Dev does something that doesn't match, you catch it with another pair of eyes that scan all scripts being run. You also prevent them from creating new objects or changing things that don't match your standards.
Most of these are pretty easy habits to get into, but you need some buy in from management, and then a second pair of eyes that scan code being run.
Just curious... Why would you force that? There are many cases where separating the schemas does not make sense. If you have 20 customer related tables, maybe. However, if you only have 1 or 2 it does not make sense.
Jared
CE - Microsoft
June 5, 2012 at 8:28 am
dbdmora (6/5/2012)
Hi SQLKnowItAll, when I say hardcoding, our developers were putting the actual server name for example MyServerName.Database instead of using a DNS alias for a connection string or when writing T-SQL code when using Linked Servers. I have broken the habit for most of the developers, I just really want to enforce it now with some kind of policy.SQLKnowItAll (6/4/2012)
What do you mean by "no longer hardcoding... an alias should be used." ? Not sure that hardcoding is really really related to an alias... An alias still can be hardcoded or parameterized.
I think that the problem here is that SQL Server (if it even could enforce coding patterns) would not even know that an alias exists. Again, why are you forcing this? Do your databases jump from server to server on a regular basis where the DNS alias gets updated for that server? Are all of your instances default instances? What if something happens to your DNS server? Does everything crash then?
Jared
CE - Microsoft
June 5, 2012 at 10:49 am
I think these are too vague and hard to enforce with hard rules.
Here's what I'd do instead.
- Make a solid argument for why these rules exist. Provide ROI, admin cost, performance, something as reasons and get a manager to sign off.
- Write a series of queries that look for changes on a daily or hourly basis. Set these up as jobs that send emails or alerts to the DBA group AND log the issue in a table.
- Publish these rules to all developers. Get a sign off from them (email receipt, something).
- When you get alerts, go check and handle the issues appropriately. Some will always slip through, but as you go to developers and make them change code, they'll build better habits.
- I might make sure you use templates for new objects in SSMS, or if you use something like SQL Prompt (from Red Gate), edit the snippets to require a schema, to ask for an alias, etc.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply