stored procedure prefixes (1 Viewer)

[wazz]

i've scripted a db and have been updating field names, etc.
first thing is, at the top of the file, two schemas and two users were created. each schema has a corresponding user (or vice-versa) with the same name. i'll come back to this.

secondly, i'm looking at the stored procedures and they all seem to start with 'sp_'. i say "seem to" because they actually look like one or the other of these:

CREATE PROCEDURE [SchemaOrUser].[sp_blah_blah]
or
CREATE PROCEDURE [dbo].[sp_blah_blah]

'SchemaOrUser' matches the schema name and user created at the top of the file.

so, a few questions:
- schema and user are the same. which one does the [SchemaOrUser] refer to?
- what does that 'prefix' do? is it required? (same thing with [dbo]).
- where does the stored procedure's name really start? with sp_ or [SchemaOrUser] ? i've read several times that sps really shouldn't start with sp_ (reads the system tables first or something) so i'd like to change them, if i should.

 

[Banana]

All users have their default schema, so if a user was defined to have a different default schema than dbo, then the call to "sp_blah_blah" would resolve to whatever schema they are in. So, if a user was assigned to SchemaOrUser schema, they'll be running that sp, and not dbo.sp_blah_blah.

Scheam and Users aren't actually same; I believe they may even share same name but they're not same thing. Think of schema as of namespace.

The prefix isn't required. I've written SPs without it. Schema may be significant only if you have more schema defined. For those applications where there is only one schema (dbo), I don't bother with it. Otherwise, watch out.

 

[wazz]

so, if those prefixes are schema, and they are required (seems they are here), where does the name of the sp begin? i think you're saying that it does start with the 'sp_' -- sp_foo -- is the sp. can you confirm that?

which means i'll be changing those sp names. i just read again in yet another place that they should not start with sp because the 'master' table (sql server) will be searched first, unnecessarily, which could cause a slight performance hit; sp_ is kind of a 'reserved prefix'.

 

[Banana]

Yes, and yes.

 

[wazz]

thanks a lot.

 

[boblarson]

kalpana's spam (in signature) reported to mods/admins