June 13, 2003 at 8:16 am
I am in process of making a short list of tips the development team should keep in mind while coding and using sql server. Would appriciate inputs from the members of the forum with all the tips they can provide. Example: To use NOLOCK option for queries that don't require exact data and can have dirty reads or Use WHERE clause in the SELECT statement. Any tip would be helpful for the documentation. Thanks in advance.
June 13, 2003 at 9:33 am
Don't use "select *", list out the columns you need. And only those columns.
Use ANSI syntax, makes things clearer, ex:
select a.colA, b.colB
from tableA a
inner join TableB b
on a.id = b.id
http://qa.sqlservercentral.com/columnists/sjones/codingstandardspart2formatting.asp
USE VCS (Sourcesafe, PVCS, SourceGear, etc.). Don't edit from the Object Browser in EM or QA. check out and check in. It will make your life easier.
http://qa.sqlservercentral.com/columnists/sjones/vcspart1.asp
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 13, 2003 at 10:51 am
If your developers are using any kind of front-end (VB Delphi ASP etc.) encourage them NOT to do database work on the client side and particularly keep SQL scripts on the client to the barest minimum.
Instead use calls to Stored Procedures in the database for as much as you can. SQLServer is much more efficient at it than ADO, JDBC etc. And much easier to maintain, you can make changes to the database portion centrally on the database, whereas if the code is the client this requires recompiles and redeployment.
DO enforce the use of Disconnected recordsets don't let them keep a db connection open any longer than is absolutely neccesary.
In fact from long experience we've found you really only ever need to use a connection object to call a Stored Procedure for data-writes and the recordset object sets. It's far simpler to Execute short SQL statements calling Stored procedures (including any parameters) than using the clumsy client-side objects like the ADO Command object
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
June 15, 2003 at 8:48 pm
I try to keep alias names consistent across all the SPs / Views / Triggers
Table Aliases
=====================
Area a
Country c
Organization o
Region r
SubRegion sr
I predefine these at the very beginning, then if I create any SPs/Trig... I try to make sure i have same aliases across.
paul
paul
June 16, 2003 at 12:28 am
If your developers will have the ablity to create (or ask for the creation of) tables, columns, views, procs, etc., tell them NOT to include any spaces or dashes (-) in object names. Only alphanumeric characters and underscores(_). This will avoid having to put brackets around object names when coding.
Also. Have them use a naming convention of all objects (tbl_Table_Name, vw_View_Name, usp_Stored_Proc_Name, etc.). This will help keep things clearer in the code, as well.
G. Milner
June 16, 2003 at 3:00 am
I would say "Comment the code". If you don't comment your code then you are asking to be nailed to your old projects for life or until you resign. The comments need to be good enough to allows a.n.other programmer take over from your.
Be careful of developers using server side cursors. It will hit performance.
If they want to do a row count of a table then have some naming scheme for the primary key (I tend to prefix table names with tbl and my primary keys with pk, so Tbl_Orders will have a primary key of pk_orders).
SELECT Name,Rows FROM SysIndexes WHERE Name='PK_Orders' gives a good rowcount provided you keep your stats up to date.
If you use compound indices (ones with more than one field) then always put the most unique field first and the least unique last.
You only get one clustered index and the primary key may not be the best place to put it.
June 16, 2003 at 4:14 am
Crosspatch, why do you consider the command object clumsy?
Andy
June 16, 2003 at 4:26 am
Never use reserved words or keywords in your table names (there is a list in BOL but it is so common to see Name and ID used).
Do comeup with a standard naming convention for your database objects and never let an object start with or be all numbers. Also, make sure you never use sp_ for Procs. http://qa.sqlservercentral.com/columnists/jtravis/sp_performance.asp
Be carefull of syntax downfalls such as using "= NULL" , always in that case us ISNULL(). http://qa.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp They can catch you at anythime and be the worst to troubleshoot.
Designate one person to applly the code to a particular test environment and have them perform a backup before applying. This will save you so many mistakes in the future.
Make sure if one person is doing a particular piece of background code that they let everyone else know they are working with it and what changes may take effect so front end or middle tier works can keep their code in check and so that anyone else who may want to work the same code doesn't cause a revision issue with the other person.
Do everything possible to limit the data you pull to only what you need and keep in mind multiple recordsets can be passed within one run. A savings can be seen if you plan your presentation process properly around the code.
Ex.
I need the LastName, FirstName, Address, and HomePhone of a customer. Along with their most current order and the list of the order.
You query should first pull the LastName, FirstName, Address, HomePhone and MostRecentOrder as one and in the same SP you can have the OrderDetails as a seperate query (producing a second recordset). Then on your presentation layer the app outputs the data from the first recordset and in the order details section produces each row for that.
Good planning can save on the overhead of data width providing perfomance gains in data retrieval and display. In the above situation you just have to keep in mind you cannot move back to a previous recordset.
Another commonly overlooked item is for teammates to get together to discuss what they are doing frequently to get feedback on effectiveness from others before it gets too invovled. The biggest downfall I have seen in the past and we have worked together to fix is that in group projects we were not communicating enough nor asking for feedback and thus building awkward or suboptimal processes costing one programmer more headaches dealing with it that maybe the one who created the root issue.
Communicating, documentating, developing standards, commenting, looking for better solutions, revisioning, care of control, and aiming towards reusability will bennifit you in the long run. However, when done do a lessons learned (let them know this beforehand) with everyone to make sure new knowledge is shared with everyone so they all grow as an individual as well as a team, all in all experience is the best teacher and sharing the lesson will help the others grow faster.
But do keep in mind you may not always see a solution and need outside help. Don't be afraid to ask questions in places like this about the project you are working on if you stumble over a particular issue. Sure we don't want to write your program for you but if you need assistance with nusances that crop up you have millions of years of experience on the web at your fingertips. With that said I will suggest getting a search utility like webferret to help you out in your search (even the freeware one is great). www.ferretsoft.com
June 16, 2003 at 7:46 am
All prior to mine are great tips. As a VP of Dev for 12 Years now, I recommend the following.
Implement peer review and mandate it. E.G. the initial developer will do much better, if he/she knows the code will be read, tested and objectively criticized by their peer.
Get your developers used to optimizing their code via incremental use of showplans etc.
Make them understand the users request better than the user, it will pay off.
Design for code re-use,up-front (often forgot about, on the back end), using udf's, Indexed views.
Stay away, if possible, from cursors and looping. Educate them on the use and power of SET based thinking. To solve problems, we typically think procedurally, but the optimal solution resides in sets of data, their relations. Understand derived tables vs use of aggregates, etc.
Document the intended purpose of the procedures, and other db objects prior to development (provided a template for them to fill in and have the documentation reviewed for clarity).
Provide them with as much 'live data' as possible.
Make them understand that the clients data is the clients intellectual property and of the utmost importance.
Do not give them dbo rights and take it away from yourself. By all means, have a person in charge of scripting, packaging and distribution.
Implement some type of scenario testing tool.
Backup, Backup, Backup! I can't stress this one enough.
All of this sounds costly, and it took me 10 years to finally implement it all, but some have been life savers.
Good luck!
June 16, 2003 at 11:42 am
Stored procedures, stored procedures, stored procedures. Simply don't let the apps access the server in any other way.
Use roles with execute permissions on the stored procs.
Always make dbo the owner of the tables and stored procedures.
If they have Visual Studio .NET and Visual Source Safe, encourage them to use database projects. This will help them use VSS for their stored procedures. It's easy for them to apply their scripts directly from VS.NET.
June 16, 2003 at 3:21 pm
Hmmm, surprised no one mentioned this yet:
Use:
SET NOCOUNT ON
where appropriate. It limits the amount of code passed between application tiers.
🙂 Even the little things add up...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply