Stored Procedure name prefix - still an issue with 2005?

Question

Stored Procedure name prefix - still an issue with 2005?

Jesse Taylor

Newbie

Points: 5
More actions
June 22, 2007 at 6:41 am

#173112

Simple question;
I know that a good practice with SS 7/2000 was to name your procs with a prefix other than "sp" to avoid SS scanning the system procs first. Is that still the case with 2005?
thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Still your system stored procedures uses the same naming convention change is that they belong to different schema than earlier versions. So better to keep your database server safe and perform better use the advice as given for earlier versions.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Jesse Taylor Newbie Points: 5 More actions · Answer 2

Thanks Sugesh,

So, to clarify, you are saying that the System procedures are in their own schema, therefore the time-penalty of "sp" prefixes has been eliminated in 2005?

I realize that it may be a good practice to continue to avoid "sp", but I need to know exactly how it behaves in 2005; because I might need to consider re-working a considerable amount of existing procedures that I inherited, etc etc. But obviously I don't want to interfere with existing code if I don't have to.

thanks again

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

no I don't believe the sp_ issue is resolved in sql 2005, haven't tested this for a while but seem to remember you still get a cache miss - no test system to hand to check this just now.

just don't use sp_ unless the proc is going to be a system proc.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

Thanks Sugesh,

So, to clarify, you are saying that the System procedures are in their own schema, therefore the time-penalty of "sp" prefixes has been eliminated in 2005?

No. Agree with colin. Still it seems that this is an issue with SQL 2005 too. When i meant that they are in thier own schema now i meant that they are now in sys schema(most sp's) instead of the default dbo schema that used to be in SQL 2000.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com