 |
|
|
|
|
|
I've noticed that the thread hasn't been moving for a while.
I'm on a DB2 -> MSSQL migration project and I would like to provide my team an appropriate coding standard before all developers start working on it.
Any update on this or link to another coding standard document will be much appreciated.
Thanks,
Joseph
|
|
|
|
|
|
|
I hope I will find time soon to rework this manual to represent more experience and the Sql Server 2008 as a target platform.
How did the migration go?
Was it a succes and did you make your own standard for coding on the Sql Server.
If so please share your experiences with the community.
Best regards,
Casper Leon Nielsen
MCP/MCAD
|
|
|
|
|
|
|
Hi,
First of all , thanks for your work.
Tables represent instances of an intity. So why 'd I name a table User ? Does it hold one user or what? I think it makes more sense to name it Users.
and what do you think about this ?
SELECT
FirstName,
LastName,
WHERE
UserID = 40
AND Age > 2
ORDER BY
X,
Y
Thanks.
|
|
|
|
|
|
|
Hi
Thank you for the input.
[table naming]
The reason we name tables in the singular form is complex but valid, as I am sure you will agree:
We need to make it simple. simple > complex
We want the naming to be consistent all over.
The simple form of the word user is... "user". The plural form of user is "users".
We now have an option of either naming all tables in the singular (simple) or in the plural form.
We choose the simple form.
Hope this makes as much sense to you as it does to me.
[formatting]
The formatting option you give is as valid as the one I propose.
My lazy eyes have gotten used to the one I propose, but the one you present here is as good in the general sense.
Best regards,
Casper Leon Nielsen
MCP/MCAD
|
|
|
|
|
|
|
I seem to be the only person that uses (NOLOCK) statements in my SPs. I only use these when I need to improve performance and I can tell you it really does improve SPs for reports etc.,
I also find that using derived tables is a massive advantage in joins i.e.,
INNER JOIN
(
SELECT
ID ,
THINGO
FROM
dbo.Table1 (NOLOCK)
WHERE
THINGO = 2
) DER ON DER.ID = OTHER.TABLEID
especially when "Table1" above has a large amount of fields
Also, use the execution plan!!!!!!!!
|
|
|
|
|
|
|
"It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back." qouted from http://www.sql-server-performance.com/rd_table_hints.asp[^].
This means we always have to be on our toes for special error conditions when using this hint. Furthermore its quite an advanced procedure to clearly see the implications of dirty reads - not something that can I can sum up in a few lines of "does and donts".
Using NOLOCK is risky business and should not be used in a coding standard as such IMO. Maybe I will write something in the optimization chapter, if I can find something precise to say about it.
Casper Leon Nielsen
MCP/MCAD
|
|
|
|
|
|
|
The summary of the link pasted above is:
"As you can see, table-level hints are available for use by Transact-SQL developers or SQL Server database administrators, but should only be used to fine-tune your code, not as a standard technique for writing queries. You should perform a strict review of the query plans procedure by the query optimizer before you decide that a table-level hint may be want you need to solve your problem. In addition, you should perform this strict review after the hint is in place. "
In short: It have no room in a standard.
Casper Leon Nielsen
MCP/MCAD
|
|
|
|
|
|
|
I can completely understand your point of view. However, we service a number of proprietary databases where we can not just create constraints and indexes when we need to, even if we want to.
The primary function of our department is to create our own extensions for these systems and reports, and this is why we use (NOLOCK) hints. We have found that NOLOCK improves performance on ten-fold when you dont need real-time data and still be considered for reports and so forth...in my own opinion...
|
|
|
|
|
|
|
I am currently working on a "queries best practices" - and this will surely be part of that (without the nolock).
Best regards,
Casper Leon Nielsen
MCP/MCAD
|
|
|
|
|
|
|
|
|
|
Your coding standard says to prefix all stored procedures with "sp_". There is numerous article recommding this is bad practice as SQL Server will scan for system stored procedures prefixed with sp and then look for your stored procedure (this is an optimization issue). It would be best if you prefixed the stored procedure with something such as "usp_".
|
|
|
|
|
|
|
Hi
The standard says to prefix stored procedures with "sp" not "sp_". It clearly states that UDO naming should match expression [a-zA-Z0-9]+ so in reality it states that using the underscore in naming should be avoided. But thanks for your input anyways.
Regards,
Casper
|
|
|
|
|
|
|
I was pointing out the standard is incorrect and should be changed. You shouldn't prefix stored procedurs with sp_ due to performance issues and you shouldn't prefix them with sp (stands for system procedure) as this can cause confusion between user stored procdures and system stored procedures. Why not be more descriptive and prefix them with usp (user stored procedure)?
Or another option is why not go with just p for procedure, since technically a function is also a stored function (or stored procedure with a return value).
Example:
uspMyProcedure
fMyFunction
or
pMyProcedure
fMyFunction
|
|
|
|
|
|
|
Hi Stephen
Thanks for the input!
Well...It all boils down to being a question of stylistic taste:
Do we abbreviate stored procedures as "sp", "usp" or "p". In this standard, as is the case with standards generally, I had to make some choices on what to name things and "sp" seemed, and still seems, a natural choice for stored procedures.
In your first message you incorrectly stated that it was a technical question and I argumented that it wasnt - then you progressed onto stating "the standard is incorrect", not by any technical argument, but rather a feeling that using the abbreviation "sp" to indicate a stored procedure would clog the other use of that abbreviation, "sp_", which is used for system stored procedures - this is not my view.
Regarding your observations on procedures and functions: It is a very common misunderstanding, even amongst some of the more experienced people out there, that functions are technically equivalent to stored procedures - this is not the case however:
Syntactically the programmer uses these constructs very differently:
Stored procedures must be EXECUTE'd while UDF's can be used in ex. joins directly. This gives UDF's a massive usability advantage over stored procedures in set operations: We dont have to store the result set in an intermediate table to use the result, rather we can use it directly.
Furthermore the Sql Server engine is aware that that UDF's does not create side effects and have a constrained result type - and can therefore execute them quite differently and more effectively from stored procedures.
So in the end it boils down to a choice of taste: is "usp" or "sp" better to cover the term "Stored Procedure" - I say no as I like the stylistic beauty of only having two-letter prefixes. Furthermore the standard states that the underscore character is not to be used in naming UDO's, making it very easy for the programmer to visually differentiate between "sp_" and "sp".
Hope this makes sense,
Postscript...
Too many people feels uncomfortable with prefixing stored procedures with "sp" so I decided to change it to "usp" for the sake of democracy.
Casper Leon Nielsen
MCP/MCAD
|
|
|
|
|
|
|
Yes it is a matter of preference in styles... I was only trying to point out that clarity should be be considered more than it normally is. I get the feeling here that it isn't and sp was chosen simple because your used to it.
In addition... when I meant functions and stored procedures are the same, I should have clarified that and what I meant to say is reguardless how how they behave in SQL Server, they are still prodecures and functions 'stored' on SQL Server.
|
|
|
|
|
|
|
Rework your word document into a suitable format that Code Project will accept.
|
|
|
|
|
|
|
Heh...
I anticipate some changes in the near future to the document.
When I am satisfied that the document is mature, hopefully via some discussions here, I will reformat it and update this article.
Until then I leave it in the Pdf.
Regards,
Casper Nielsen
|
|
|
|
|
|
|
"Avoid using the blob type for storing files. Files are stored on a file-system, not in the database. Use releative paths to files instead. Remember to backup the filestore simultaneously with the database to counter synchronization issues."
- Not according to Microsoft or any other SQL based file system.
|
|
|
|
|
|
|
Hi
Thanks for your input. I dont understand your message unfortunately, could you elaborate?
Regards,
Casper
|
|
|
|
|
|
|
The notion of non using of blobs in db is outdated.
Check how Sharepoint or MS CMS stores files, inside SQL thats how 
|
|
|
|
|
|
|
Well there are pros and cons to both strategies. It is correct that using the database as a filestore can solve some issues and have some tantalising aspects:
Uniform access: Access to files can be encapsulated in the same type of datalayer components as the rest of the application.
Backup: Files will be backed up with the rest of the datastore.
But unfortunately it also tends to clog the visibility alot:
Users cannot interact with files through their normal tools, but have to extract them from the database to view and manipulate them.
Most components expects files to be excatly that: files in a directory structure and cannot interact directly with files in a datastore. This creates the need to unpack the files to a cache before using them.
So you are partly right: If you are the architect of a massive product, such as the before mentioned Sharepoint and MS CMS, you are well of trading visibility for structure. If however you are designing are solution on a smaller scale the tradeof is not as good.
To state however that "The notion of non using of blobs in db is outdated" is as valid as stating that "Designing an application using a multitiered approach is outdated, as Microsoft have now introduced DataAdapters that exist in the presentation layer"  ...
I think I will rewrite the standard to simply state the tradeoffs instead of warning against the usage of blobs as file-storage.
Regards,
Casper
|
|
|
|
|
|
|
Changed the blob statement to:
"Be cautious when using the blob type for storing files. Weigh the structure benefit with the clarity cost before deciding which approach to use. Whichever approach you decide to use, be consistent and avoid using both in the same project. If you choose the filesystem as the datastore for files, remember to backup the filestore simultaneously with the database to counter synchronization issues."
|
|
|
|
|
|
|
God job, I meant to say, any job sucks 
Now, go watch TV
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin