SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Practices in an Adhoc Environment


Best Practices in an Adhoc Environment

Author
Message
sxnandwa
sxnandwa
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 2
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/snandwani/bestpracticesinanadhocenvironment.asp



rjaj
rjaj
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 1
Realy a nice article. It match my working environment and is of lot of help for me



Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16126 Visits: 3403
I would add that stored procedure names should not start with sp_ because SQL Server will search the MASTER database for the stored procedure first and then the user database. Although the performance hit is minor it is still a hit.

For similar reasons always prefix object names with their owner i.e. dbo.tblMyTable or dbo.vwMyView.

Always comment your code unless you want to be shackled to it for life.

Always carry out a QA stage on code.

If at all possible, make developers use stored procedures, not dynamic SQL.

Anyone who uses ADO should use the command object to call the stored procedure rather than connection.open "SELECT * FROM...etc"

========================
He was not wholly unware of the potential lack of insignificance.

LinkedIn Profile
www.simple-talk.com
*Prashant Thakwani
*Prashant Thakwani
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1293 Visits: 164
It is a good article for the startup DB Programmmers. Keep it up.

Prashant Thakwani
thakwani@rediffmail.com

Prashant Thakwani
thakwani_prashant@yahoo.co.in
Ferguson
Ferguson
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1031 Visits: 109
This is to some extent the beginning of a standards document and I think a good start. Some additional thoughts:

I think developers should be discouraged from creating a new database for each application, instead favoring having databases for related needs (related in particular by where they are needed, replication needs, backup needs). This reduces the number of databases to maintain.

On a related note, if developers can create databases there needs to be a thorough process to make sure all the admin details are worked out -- how will it be backed up, reorged, etc. Otherwise it is very easy to have little islands of applications and discover one day you can't recover it when something happens.

A whole 'nother big topic, but I think blankent recommendations against dynamic sql are a mistake. Depending on your definition of "ad hoc" especially. If the code has parameters driven by user input and those parameters can vary radically (especially being null), AND if the queries will have long run time (more than seconds), then dynamic SQL is often preferred because it allows for better query plans. Complex queries driven by parameters often have lots of OR statements in them (e.g. @Param is null or Field=@param) which can be omitted if dynamic. OR statements make for lousy execution plans usually.

I believe any environment that allows or encourages development of databases by programmers also needs a consistent documentation environment -- how and where will data dictionary type information be stored? Make sure it is consistent among all databases, and preferably easily searchable across databases to ensure that similar concepts are recorded similarily (or perhaps not recorded at all redundantly). Strong Naming conventions are a great benefit if the number of contributors to database schemas is large.



Adam Wiseman
Adam Wiseman
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1
I'm not entirely sure the statement about always use varchar and not char variables is true.

Varchar data types in a table are stored differently in the page file than char data types, varchar data types, while giving you the benefit of being only as long as the text you put in them, are not stored in order with the rest of the fields. Instead a pointer is stored in the varchar fields place. This points to the end of that record in the paging file, where the length is stored, and then it reads the varchar field from the end of the record. Using varchar fields for short text often takes more processor time and space than if a char was used. The pointer and length field combined take up (4 bytes?), I believe. Take consideration on every field under 10-15 characters to be a char field. Fields like state, gender, any field where the length is known, and the field is short.



jwiner
jwiner
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 18
Just curious, has anyone in your organization addressed moving database development from the production environment to a development one...?



sxnandwa
sxnandwa
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 2
Thanks every one for taking time to read the article and commenting on same.
Here are few of my thoughts.
David: Thanks for giving the inputs. The sp_ naming convention is sometimes commonly seen with developers and we have learnt a bit hard way in last couple of years and now we do have a naming conventions which takes care of this. What Ferguson we have gone through the grind of developing and implementing the naming conventions and implementing the same in our environment. I agree with Adam on his suggestion that Varchar should be judiciously used only when the Character field is fairly long. Probably I got biased by my learnings as I have only come accross cases on the other extreme in which large fields have been given Char.
JWINER: As far as moving our development to separate development environment is concerned, we have considered it for some of assignments in which the development cycle is long enough. Typically to give you an idea of our organisation, we conduct lot of HR Surveys which have huge amount of data and both data and Analysis varies from survey to survey. Our environment does not fit very well into either typical OLTP or OLAP environment. However we do have some certain set of techniques and VB Components, data structures available which we customise to suit our requirements. Some of the standard surveys also are in single system and single database system in which case we do follow a process of making changes on development and porting to production but for some application which are on the fly we really don't have that luxury.



Lara Rubbelke
Lara Rubbelke
SSC-Addicted
SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)SSC-Addicted (482 reputation)

Group: General Forum Members
Points: 482 Visits: 67
I would like to add that I discourage the use of truncate statements in lieu of delete statements. While the truncate statement is faster and consumes less log space, if the statement is called by someone who is not in the SA role or the db_ddladmin database role, or not an owner of the database the statement will fail. This provides less flexibility in allowing other users to use the procedure.



shendricks
shendricks
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 1
I found that there were several factual errors in the article and several differences of opinion. The following is a list, in no particular order:

1) "where the developer has free access to the production servers..."

DO NOT let the developers have free access to the production server(s). We can debate how much access to grant them in the development environment but Production is for real. Hands Off!!!

2) "The developer may choose to create a database..."

DO NOT let the developers create databases. If they must be allowed, do it through a script that you keep in some source code control mechanism (e.g., SourceSafe, et al.)

3) "The recovery model is by default full..."
The recovery model defaults through the MODEL database. MSDE deafults this to Simple; others versions to Full.

4) "The transaction log settings should be kept to simple..."

This is a recipe for disaster. While the log files do grow in the other two modes, this is precisely what allows you to recover all (most?) of your data in the event of a failure. Regular backups should be run in order to prevent excessive growth of the log files.

5) CHAR vs VARCHAR - There are conditions where VARCHAR consumes MORE space than CHAR and results in slower performance.

6) "The transaction isolation level should be set to low wherever possible."

The isolation setting is a trade-off between speed and data integrity. There is no simple rule that can be universally applied. You could just as easily argue here that the isolation level should be set as high as necessary.

Steve Hendricks
MCSD, MCDBA
AFS Consulting Group

shendricks@afsconsulting.com
(949) 588-9800 x15


Steve Hendricks
MCSD, MCDBA
Data Matrix

shendricks@afsconsulting.com
(949) 588-9800 x15
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search