| Author |
Topic |
|
johnstern
Yak Posting Veteran
67 Posts |
 Posted - 2007-06-14 : 12:43:54
|
| it is a good practice then to put SET NOCOUNT ON all my stored procedures ? |
|
|
johnstern
Yak Posting Veteran
67 Posts |
Posted - 2007-06-14 : 12:44:56
|
| I see a good deal of SPs in some of my clients that do not have this, but I am certainly willing to do it, if this is the right approach |
 |
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-06-14 : 12:57:54
|
| Yes. it suppresses sql server messages (like 1 row affected) that you generally won't need. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-06-14 : 13:02:44
|
| Yes it is a good practice. Depending on how complex the procs are..basically almost every T-SQL generates some kind of message which is passed on to the caller. So bigger procs have lot more info that will be sent back to the application unnecessarily choking the bandwidth.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 14:15:06
|
We put this as the first statement in every SProcSET NOCOUNT ONSET XACT_ABORT ONSET ARITHABORT ON Kristen |
|
|
|
johnstern
Yak Posting Veteran
67 Posts |
Posted - 2007-06-14 : 14:18:30
|
| what those other two do ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 14:23:54
|
| Have a look in the SQL Documentation! |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-06-14 : 14:23:56
|
quote:
Originally posted by Kristen
We put this as the first statement in every SProcSET NOCOUNT ONSET XACT_ABORT ONSET ARITHABORT ON Kristen
You would think by now that the Sql Server team would make this property you could toggle in Management Studio..... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 14:24:56
|
Is "Management Studio" some new-fangled gizmo?  |
|
|
|
johnstern
Yak Posting Veteran
67 Posts |
Posted - 2007-06-14 : 14:51:50
|
| I checked the documentation, very good statements indeed to have |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-14 : 16:10:16
|
| Excellent! Just watch out for XACT_ABORT, if you do manage to have very VERY tight error handling in your SProcs you may not want this as it will cause SQL Server to just abort your Sproc.Kristen |
|
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-06-14 : 17:11:01
|
| I really don't think this is necessary. Bandwidth? Nowdays, both the SQL and APP servers are located in the same colocation and therefore network congestion is USUALLY not an issue. Yes, it might send more info back to the app server but with quad-core cpus out these days and faster SCSI disks, do we really need to "micro" optimize with things like that????However, it varies with each situation but for most situtations (especially where the db and app server are close to each other), you don't need to microoptimize like this. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-15 : 04:22:28
|
| I reckon your answer was intended to be against a different thread which I read only a short while ago ... but I can't find it now!Or did I get that wrong?Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
it is a good practice then to put SET NOCOUNT ON