Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 TSQL/ SP Best Practices

Author  Topic 

jesus4u
Posting Yak Master

204 Posts

Posted - 2002-09-09 : 10:56:56
Can we start a list of best practices here?
Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-09 : 11:09:05
OK, here's my contributions:

1. Always use stored procedures for database access (SELECT) and for data manipulation (INSERT/UPDATE/DELETE) when dealing with an application. Avoid using constructed SQL strings called through ADO or the like (from a web page, for example) and avoid directly accessing tables or views as much as possible.

2. Always comment your procedure code, and use a code template to document things like creation date, author, revision, and so on. You should also use some kind of source control like Visual SourceSafe to further control the code and prevent unknown users from modifying it; but the procedure itself should still be commented whether source control is used or not.

3. Keep procedures clearly defined and as short as possible. Break up larger operations into smaller, discrete procedures. Don't throw everything and the kitchen sink into one stream of code.

4. Be diligent with dynamic SQL, use it only when absolutely no other method is available.

5. Establish and maintain user permissions on procedures and their dependent objects (tables, views etc.) If you don't have guidelines for establishing permissions, stop right now and do so. DO NOT allow every user unfettered access to everything.

I'm sure I'll think of some more, and I know others will too.

And here's another important one:

6. ABSOLUTELY NEVER USE SA AS A LOGIN FROM YOUR APPLICATIONS! ALWAYS SET A PASSWORD FOR SA, DO NOT LEAVE THE PASSWORD BLANK, AND DO NOT GIVE THAT PASSWORD TO ANYONE OTHER THAN THE SENIOR DBA! If your application needs to connect to your server, create a specific login for it, or use NT authentication when connecting. If other users need system administrator access, add them to the sysadmin role under their own login. Every system admin should have their own login.

Edited by - robvolk on 09/09/2002 11:24:35
Go to Top of Page

jesus4u
Posting Yak Master

204 Posts

Posted - 2002-09-09 : 11:26:31
Very insightful.
Thank you :)

Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2002-09-09 : 11:32:02
Great contributions robvolk!!!

Along the same security practices as line item 6 above. Our organization never provides an anonymous user (eg Web User) with any table privileges. Only Execute SP privileges.




Edited by - ehorn on 09/09/2002 11:32:45
Go to Top of Page

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-09 : 11:34:00
Ok here I go ...

1) Make use of groups in SQL Server. I find that most of my *public* procedures that I call from my applications I just place in the "public" group rather than creating a user and assigning permissions to that user I place that user in the "public" group which makes PERMISSIONS administration a lot easier.

2) If you are an admin to the server make sure you disable access to the XP_CMDSHELL command to developers (if they REALLY REALLY need it I would ask them to provide you with the code they need to use it and create a procedure that calls it) I find that it's simple enough to take over the box with a few "net" commands "net user|net group".

3) NEVER set your startup account for SQL Server to an Administrator of the computer or Domain (specially the domain) Create a Domain/Computer user that has the proper access for running the service but can't do much more (this is where you can get in trouble with the XP_CMDSHELL; the user could add themself to the domain or the computer to take it over)

4) Make use of set-based operations and try to avoid cursors at all cost.

5) Use descriptive names and get rid of HUNGARIAN notation on all your tables/columns/procedures/views.

6) Document a standard for naming and make EVERYONE use that standard. If they don't conform make sure their manager knows about it. (Oh, in a multi-database servers envrionment don't try to enforce INFORMIX standards on a SQL Server database; there is a big difference between them)

7) When sending email, its best to have an EmailQueue table and insert all the emails you wish to send there. Make sure the table has a UNIQUE ID (IDENTITY PRIMARY KEY if you want). Next create an outside application (maybe a console application) that you can pass the EmailQueue ID column value to and it will send an email for it. THen schedule a job that runs a stored procedure that looks at the queue and processes any items in it. I tend to have a bit column called Sent that is 0 if the item needs sent and 1 if its been sent (I keep email records for 90 days ... just in case [people say i didn't get emailed and i'm like ya right])

* one more thing on this by sending the ID of the row to send the email you allow for data retrieval of TEXT or NTEXT fields to sending.. you could also place a query column on the table and the program could then run it and attach it as needed. this one of the most flexible ways to send email that I have come across... if you don't want to use a console application then create a COM object that has a method you can invoke that does it..

8) If you are using ASP.NET I would recommend using a single PROCEDURE for all data retrieval ... say you normally would call XX number of procedures to acquire drop down lists and data to display, well use those procedures in a wrapped procedure that returns all the data (reduces number of trips to the server if you read all the data at once)

9) Never use NOLOCK unless you are super sure you want to ignore all locks on a table (this can cause you so much pain) I would recommend reading on proper table locking and hints you can give the query optimizer

... I'll think of some more later...

... ok i thought of another one ...

10) When you have a table that you need to join to other tables (i would say more than 3) and it can produce a large number of rows I would suggest moving that join into a VIEW ... this will allow the query optimizer to do a much faster job than it would if you have the joins in a procedure...


Edited by - onamuji on 09/09/2002 11:37:17

Edited by - onamuji on 09/09/2002 11:39:11
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-09 : 11:54:23
Man, what was I thinking? Here's the #1 absolute BEST practice regarding T-SQL/stored procedures:

Pick up a copy of

The Guru's Guide to SQL Server Stored Procedures, XML and HTML by Ken Henderson.

Seriously, it's an invaluable resource and Ken covers a number of best practices above and beyond the ones listed here so far.

Here are some more I thought of:

7. All objects should be created by dbo, and dbo membership should be restricted on production machines. The database owner and DBA should review all modifications and additions before they are implemented on production servers. Developers should never be allowed to modify production databases without some kind of review process.

8. Avoid placing procedures and functions into the master database, and avoid using the sp_ prefix for system procedures, unless they are truly universal functions that do not rely on any specific database and/or object. It's too easy to put something in master and forget about it, and then have it break after a RESTORE or ALTER PROCEDURE. If the procedure is used by several different databases then either copy it into each one, or...

9. Get into the habit of using the 3-part naming convention for objects, like SELECT * FROM myDatabase.dbo.myTable instead of SELECT * FROM myTable. It will speed up execution of procedures and avoid name resolution errors (fits in very nicely with #7 too)

10. ALWAYS keep a copy of procedure code in a separate file(s). This is more-or-less guaranteed when using source control, but as the old saying goes, you can never have too many backups.

11. AVOID using encryption on stored procedures. AVOID thinking that encryption makes you cool or professional. You can do more to secure your code by using source control and proper permission guidelines. Encryption should only be used on final, off-site production installations where the end users have system administrator access, and you want to protect your proprietary code, AND you are absolutely using source control to maintain it. Using encryption without source control is akin to Russian Roulette.

Go to Top of Page

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-09 : 12:09:51
Ah yes, SOURCE CONTROL/VERSIONING ... wonderful things ... use them if ya got em ... if ya don't, get em ... save so many headaches ... like:

WHY DOESN'T THIS PROCEDURE WORK NOW! ***K I JUST SAVED OVER THE CODE THAT WORKED!

this can all be avoided with a basic SOURCE CONTROL/VERSIONING system. it can also save you headaches like:

I SWEAR I JUST CHANGED THAT PROCEDURE WHAT THE ***K IS GOING ON?!

little to the developers knowledge another member of his team was trying to modify that procedure too! Again SOURCE CONTROL could save you this head ache...

Don't let yourself give yourself excuses why not to use it and why not to get it if you don't have it ... it is just as neccassary as plenty of ram is for running SQL Server ... imagine running SQL server on 64mb of ram while trying to accomodate 200+ transactions per minute ... you are just ASKING for a head ache ... what if your hard drive goes?!


oh just thought of another good best practice

11) ALWAYS have a DEVELOPER, CERTIFICATION, and PRODUCTION machines (well this is a best practice ... ) DEVELOPER boxes are used by developers ... the DBA should then move the code into the CERTIFICATION box that end users can then verify the changes are working properly (also gives the DBA some time to review the code) once the go ahead comes through move the code to PRODUCTION)

12) ALWAYS keep records of changes made to a production box ... put them in a folder and date it ... do what ever you have to to have a trail of work done to a production database

13) If you can (i know most production boxes have to be up 24/7 however there are organizations that only use them from 9-5 5 days a week) schedule down time for these updates and restrict them to either a middle work day and never on the end of a work week (friday) you are just asking to come in to fix something that didn't go quite how they expected on friday... also before you do any updates make sure you have an active backup (very recent) of the database incase the changes don't go so good

... i've put off getting that new T-SQL Guru book .. i have the other one and it was a very good read ... will have to spend a night at the book store taking a read of it ....

Go to Top of Page

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-09-09 : 14:29:52
People, try my article :-)

Database programming guidelines and best practices
http://www.sqlteam.com/redir.asp?ItemID=4446

Wrote this more than an year ago!


--
HTH,
Vyas
http://vyaskn.tripod.com
Go to Top of Page

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-09-09 : 14:36:32
Rob, there's a problem with this :-)

quote:

9. Get into the habit of using the 3-part naming convention for objects, like SELECT * FROM myDatabase.dbo.myTable instead of SELECT * FROM myTable. It will speed up execution of procedures and avoid name resolution errors (fits in very nicely with #7 too)



To help execution plan reuse, it is enough to qualify database objects with owner name. Hardcoding database names is not required and is not a good idea. This can pose problems and 'hard to trace' bugs :-) For example, if you rename your database for whatever reason, your stored procedures will break. If you made a copy of your database, the procedures in your new (copied) database will refer to the old database, and will work without producing errors, there by introducing bugs :-)



--
HTH,
Vyas
http://vyaskn.tripod.com
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-09 : 14:52:58
Good point. Although...
quote:
if you rename your database for whatever reason, your stored procedures will break
...will never be an issue for me.

Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-09 : 19:11:29
Always give user access only through stored procedures.
Alwyays install and update servers/databases/tables/SPs/views/triggers/jobs/replication/linked servers/logins/everything else - via scripts
Hold all scripts (i.e. everything you do) in SourceSafe
Never depend on an external application i.e. make external dependencies asynchronous.
Identify data owners and process controllers and their boundaries.
If things are getting complicated then review the design
Design for resource contention - don't leave it for retro-fitting
Apply constraints to tables.
If you feel it's wrong then it probably is - even if you're not sure why.

and most important

It's never too late for a rewrite.
Corollary
Design the system so that you can rewrite bits without affecting the rest. Don't let the bits get too big or too small.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 09/09/2002 19:12:57
Go to Top of Page

Tamsun
Starting Member

2 Posts

Posted - 2002-09-09 : 23:32:02
Can anyone tell me why "Be diligent with dynamic SQL, use it only when absolutely no other method is available. "
thx.





Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-10 : 04:27:15
It puts strain on the server as it has to recompile on every run. I
It means you have to give the user permissions on the tables.
It makes things more difficult to test as you don't have a fixed query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-09-10 : 04:45:24
There is a comprehensive article on "Dynamic SQL" written by SQL Server MVP, Erland Sommarskog:

The curse and blessings of dynamic SQL
http://www.algonet.se/~sommar/dynamic_sql.html

--
HTH,
Vyas
http://vyaskn.tripod.com
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-09-10 : 11:04:59
What about view names? Should they contain view or v as part of the name?

Voted best SQL forum nickname...."Tutorial-D"
Go to Top of Page

jesus4u
Posting Yak Master

204 Posts

Posted - 2002-09-10 : 11:06:21
IS it better to call another Sp from within a SP or from the client app?

thanks

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-10 : 11:09:40
quote:
What about view names? Should they contain view or v as part of the name?
Naming conventions are a personal choice, you can choose to use prefixes or not, there's no right or wrong on that. I don't use them because I don't think they help anything, and I make it a point to familiarize myself with a database so that I know which objects are tables and which are views. It's far better IMHO opinion to choose a name that's descriptive than use prefixes to do the job. I take it as a bad sign, when I'm designing a database, if I can't keep everything straight without relying on prefixes...usually that means the design is in trouble.

Edited by - robvolk on 09/10/2002 11:10:26
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-10 : 11:17:03
I would use a prefix for a view so that people know they are not accessing a physical table (I tend not to use views except for bcp). Also for SPs so that you can search for the prefix in client apps.

>> Is it better to call another Sp from within a SP or from the client app?

Depends on the situation. If you are fulfilling a single function or need everything withing the same transaction then probably from the SP. You shouldn't try to mix separate functions just to save a call from the client unless it will cause a lot of performance problems.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-10 : 11:55:50
I would prefer it if everyone would conform to my standards so that when I take over your job (and I will) it will be much easier of a transition for me

Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-09-10 : 14:25:38
quote:
Naming conventions are a personal choice, you can choose to use prefixes or not, there's no right or wrong on that. I don't use them because I don't think they help anything,


and

quote:
I would use a prefix for a view so that people know they are not accessing a physical table (I tend not to use views except for bcp).


Thanks, I stew over little goofy stuff like this all the time. This forum lightens that up that load of fickleness a lot. ie. "Man, what was I thinking doing that, better change it..."
Trying to follow the best practices here for sp security.
So for a Utility database that will contain sp's to mainly interact with a proprietory (other vendors) database. It is best to assign only the public role to the application login(s) and then grant permissions on the Utility database to the public group. Or should a new role be created. Seems no matter how you are connecting there is gonna be at least public permissions but then any other base logins are going to have at least expanded public from that point on.
(I think I just argued this the oppisite of what I've done it. F***)
Oh no .. wait...because those login which have by default the public role won't have sweeping access to the utility database unless specifically granted!! at the db access level for the login. That is it I think......



Voted best SQL forum nickname...."Tutorial-D"
Go to Top of Page

SamC
White Water Yakist

3467 Posts

Posted - 2002-09-10 : 23:06:48
quote:

8) If you are using ASP.NET I would recommend using a single PROCEDURE for all data retrieval ... say you normally would call XX number of procedures to acquire drop down lists and data to display, well use those procedures in a wrapped procedure that returns all the data (reduces number of trips to the server if you read all the data at once)

Edited by - onamuji on 09/09/2002 11:39:11



I've never heard this mentioned anywhere. Could you elaborate a little on why a single procedure is good (even possible) for an entire .NET web application?

SamC

Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -