Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Twenty tips to write a good stored procedure Expand / Collapse
Author
Message
Posted Monday, August 10, 2009 3:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:52 AM
Points: 839, Visits: 330
gfabbri (8/10/2009)
sorry, it is better to put SET NOCOUNT ON, isn't it?


Definitely - SET NOCOUNT ON suppresses the count messages. Kinda wrong way round - the code shown is the opposite what the description meant.
Post #767706
Posted Monday, August 10, 2009 3:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 18, 2013 5:56 AM
Points: 10, Visits: 92
Hi,
I tried with these two statements but the second one is not get executing and it says "Incorrect syntax near the keyword 'exists'." What wrong with this or is this wrong statement?

SELECT * FROM employee WHERE emp_no NOT IN (SELECT emp_no from emp_detail)
SELECT * FROM employee WHERE NOT EXISTS (SELECT emp_no FROM emp_detail)
Post #767708
Posted Monday, August 10, 2009 4:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 17, 2014 9:44 AM
Points: 127, Visits: 105
Good set of guidelines Arup...
but regarding set nocount
do you mean set nocount on?
Post #767721
Posted Monday, August 10, 2009 4:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 221, Visits: 47
arup_kc (8/9/2009)
Comments posted to this topic are about the item Twenty tips to write a good stored procedure

I understand that 'where lower(fieldname)' will not use the index on fieldname as the optimizer will look for an index based on the expression on the left of the =
Sam Trenchard MCT
Post #767727
Posted Monday, August 10, 2009 4:43 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
I think you missed a very important point I try to stress to all my developers (unless I missed it in the list), and thats to review your query plan for the stored proc. It's probably one of the most important things to do when creating a sproc.
Post #767737
Posted Monday, August 10, 2009 4:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:32 PM
Points: 11,194, Visits: 11,140
I deeply begrudge the minimum one star.

Gail's first post expresses it best, and in milder language terms than I might have used. I would encourage everyone who reads the article to read it too.

This article really needed some serious peer review before being submitted. It wouldn't be so bad if it didn't attempt to sound so authoritative! Please Arup, get some feedback from some of the hugely knowledgeable people on here before publishing - poor advice and dodgy 'tips' will do nothing to enhance your standing or that of SSC, sad to say.

/rant

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #767744
Posted Monday, August 10, 2009 5:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 31, 2012 4:17 AM
Points: 5, Visits: 39
Paul White (8/10/2009)
I deeply begrudge the minimum one star.

Gail's first post expresses it best, and in milder language terms than I might have used. I would encourage everyone who reads the article to read it too.

This article really needed some serious peer review before being submitted. It wouldn't be so bad if it didn't attempt to sound so authoritative! Please Arup, get some feedback from some of the hugely knowledgeable people on here before publishing - poor advice and dodgy 'tips' will do nothing to enhance your standing or that of SSC, sad to say.

/rant

Paul


Couldn't agree more. I thought SS Central had better editorial standards than this.

Post #767747
Posted Monday, August 10, 2009 5:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 21, 2010 4:06 AM
Points: 174, Visits: 46
Some points in this article no longer apply, some are mis-leading and some are just incorrect. Please read with caution.
Post #767748
Posted Monday, August 10, 2009 5:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 22, 2014 3:32 AM
Points: 22, Visits: 107
very nice article and must read article.

one question is every thing is true for sql 2008 or not ?
Post #767764
Posted Monday, August 10, 2009 6:00 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 854, Visits: 862
10. WHERE clauses - In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their performance.

=, >, <, >=, <=, <>, !=, !>, !<

for details, refer to the article: http://msdn.microsoft.com/en-us/library/ms190276.aspx


The article you've linked to does not support your point, as far as I can see. That article addresses operator precedence, not performance. Your point may be correct for all I know, but that article doesn't address it one way or the other.


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #767769
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse