It isn't easy to remember all the details regarding object/schema qualifying object names. There are many variables involved, such as:
- Version of SQL Server
- Qualifying the owner when you call the proc (EXEC dbo.procname)
- Qualifying object references inside the proc code
- Qualifying object names if you aren't using procedures
- Whether the user is the same as the owner of the proc/table
- Default schema for the user
So, I decided to give it a spin for different combinations and investigate both profiler events and also number of plans in cache. I won't post all details here, that would be too much to write down. You can use the scripts at the end to do your own findings. I did not investigate the differences regarding compile locks. Anyhow, here are my conclusions:
- I could not produce any recompile (SP:Recompile or SQLStmtRecompile) event for any combbination.
- I did find SP:CacheMiss events on 2000 when you execute a proc and don't qualify the proc name (for 2005 I always got those events). Then a subsequent SP_CacheHit will follow.
- For straight SQL (no procedures) I noticed that each user get its separate plan when you don't owner-qualify the table name. This makes sense. An interesting aspect on 2005 was that if you specify a default schema for the user (and two users has the same default schema), then the users will share the plan (basically the default schema becomes the "owner" of the plan).
Below are the scripts I used:
--Login as sysadmin:
IF DB_ID('myTestDb') IS NOT NULL DROP DATABASE myTestDb
IF EXISTS(SELECT * FROM syslogins WHERE name = 'Kalle') EXEC sp_droplogin 'Kalle'
IF EXISTS(SELECT * FROM syslogins WHERE name = 'Olle') EXEC sp_droplogin 'Olle'
EXEC sp_addlogin 'Kalle', '*hjk&6f' EXEC sp_addlogin 'Olle', '*hjk&6f'
CREATE DATABASE myTestDb
EXEC sp_grantdbaccess 'Kalle' EXEC sp_grantdbaccess 'Olle'
CREATE TABLE dbo.t(c1 int identity PRIMARY KEY, c2 char(30))
INSERT INTO dbo.t SELECT TOP 1000 'hello' FROM sysobjects a CROSS JOIN sysobjects b
CREATE INDEX x ON t(c1)
CREATE PROC dbo.p AS SELECT c1 FROM t WHERE c1 = 34 AND c2 = 'Hello'
CREATE PROC dbo.p_q AS SELECT c1 FROM dbo.t WHERE c1 = 34 AND c2 = 'Hello'
GRANT EXECUTE ON dbo.p TO Kalle, Olle
GRANT EXECUTE ON dbo.p_q TO Kalle, Olle
GRANT SELECT ON t TO Kalle, Olle
--Number of plans in cache, run after executions of proc
SELECT OBJECT_NAME(objid), sql, uid, *
WHERE dbid = DB_ID()
AND cacheobjtype = 'Compiled Plan'
AND sql NOT LIKE '%PSTATMAN%'
--Run this three times, logged in as sysadmin (dbo), Kalle and Olle
SELECT c1 FROM t WHERE c1 = 34 AND c2 = 'Hello'
SELECT c1 FROM dbo.t WHERE c1 = 34 AND c2 = 'Hello'