| Author |
Topic |
|
pvccaz
Yak Posting Veteran
87 Posts |
 Posted - 2010-10-29 : 19:34:20
|
| Hi,I had been looking for a good web link that has T-SQL best practices. I searched msdn and it gives a whole bunch of white papers related to sql server best practices. I was wondering if there is something that the members of sqlteam.com uses.Thanks |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-10-30 : 07:18:27
|
| which best practices are you looking for?BOL provides you with great documentation and examples on T-SQL. |
 |
|
|
pvccaz
Yak Posting Veteran
87 Posts |
Posted - 2010-11-01 : 12:24:35
|
| I am looking for some sql coding standards. For example using WITH NOLOCK in the joins is better because no shared locks are issued against the table that would prohibit other transactions from modifying data in the table. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-11-01 : 13:21:34
|
| Using NOLOCK (or any query hint) is certainly NOT a best practice. They should only be used when all other tuning options have been exhausted, i.e. as a last resort. |
|
|
|
pvccaz
Yak Posting Veteran
87 Posts |
Posted - 2010-11-01 : 14:18:14
|
| I thought that NOLOCK was a good practice. Thank you robvolk for clarifying. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-01 : 14:57:46
|
"I thought that NOLOCK was a good practice."No. Just to be clear: it is terrible practice.You will get some rows twice, some rows not at all, some rows will show data for rows that do exist in the database, but which were not committed, and thus the data you see will be data that was never actually in the database.These "side effects" happen very INfrequently. They are hard / impossible to reproduce, so when users say things like "I saw XXX" it is very unlikely you will be able to reproduce it. If management use reports containing NOLOCK there is a serious business risk that they will take decisions based on data that was incorrect / inaccurate / inappropriate!!Don't use NOLOCK ever. If you think you need to use NOLOCK then ask me first  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-11-01 : 18:06:54
|
| Before you use or don't use a particular isolation level, you should understand the implications of that choice. I dislike blanket "Always" or "Never" do X, Y or Z statements. So, I'll leave it up to you to educate yourself.As to standards or best practices... That's harder to nail down because most of the best practices depend on your situation. Some are more universal, like it's not a good idea to name objects the same as SQL reserved words, etc.. Unfortunately, there are no universal coding standards; every shop has their own (if they even have any). So, if you come up with some, just be consistent in your use of that standard.Here is link to the 11179 ISO standards:http://metadata-stds.org/11179/And here is a link to the Wikia for more information on said standard:http://en.wikipedia.org/wiki/ISO/IEC_11179 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pvccaz
Yak Posting Veteran
87 Posts |
Posted - 2010-11-01 : 20:07:31
|
| Thanks everybody for clearing up my queries on NOLOCK. There are a lot of scripts that i had used NOLOCKS. Probably i need to change them. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-02 : 03:30:56
|
"Just say that it's bad..."Its not that it is bad, it is that it is really bad. It is made bad because the people using it don't know the consequences. It fixes the problem at hand (blocking, say) and seems the panacea solution. I do understand why it is widely used, I am sure that 99% of people using it don't realise the potential consequences, and why should they? So its only when they hear from someone else that there are issues that they will become aware. I like to speak up on this particular issue because I suspect in most/many cases there is a very real possibility that it is doing harm - but only very VERY infrequently, but quite possibly with disastrous consequences.Sorry O/P didn't mean my reply to sound harsh. But, yes, you do need to change your code and as Tara said: most likely READ_COMMITTED_SNAPSHOT is what you need - plus, you just turn that on once for the database and get the benefit (of what I think you are using NOLOCK for) on every query in your application - but you do have to go take out the NOLOCK's, and there may be side effects of doing that and, as Brett said!, you will get the blame for those! (Maybe some queries WILL now block - where Write is blocking Write, for example, but NOLOCK was not the solution for them and you'll expose a real problem that you do need to fix properly.)Hope it goes well  |
|
|
|
|
T-SQL standards