function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?
THANKSTo substitute some other value instead of a space you could use CASE:
CASE WHEN x<>' ' THEN x ELSE 'something' END
Or you could use NULLIF() in conjunction with ISNULL() or COALESCE():
ISNULL(NULLIF(x,' '),'something')
COALESCE(NULLIF(x,' '),'something')
All of these examples return 'something' if X is either a NULL or a space.
--
David Portas
SQL Server MVP
--|||In addition to David's response, no there is not a pre-defined function
ISSPACE, but you could write a scalar UDF in a couple of minutes...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Killer" <roninkaiser@.tiscali.it> wrote in message
news:nEBxc.46489$Wc.1502104@.twister2.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS
>

Fun of INSTEAD OF UPDATE trigger

Hi All,
Here I have a view like this:
create view vw_Lcustkeycode as
select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
union all
select *, convert(bit,1) as IsArchived from
DataEntryArchive.dbo.aLcustkeycode
I created an INSTEAD OF UPDATE trigger like:
CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
AS
BEGIN
if update(UserName) and update(DateModified) begin
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from dbo.Lcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=0
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from DataEntryArchive.dbo.aLcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=1
end
END
When I run following update in Query Analyzer (notice the lcustkeycode_id is
primary key so only 1 row should be affected):
update vw_LCustKeycode
set username='jamma', datemodified=getdate()
where lcustkeycode_id=111060167
It said:
(2 row(s) affected)
(1 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
I check the data and they are correct and really only 1 row was updated, but
why it said so many rows were affected? I find there are no other trigers
sitting there except my instead of trigger.
Can anyone here explain this strange behaviour?
Thanks,
JamesJames Ma wrote:
> Hi All,
> Here I have a view like this:
> create view vw_Lcustkeycode as
> select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
> union all
> select *, convert(bit,1) as IsArchived from
> DataEntryArchive.dbo.aLcustkeycode
> I created an INSTEAD OF UPDATE trigger like:
> CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
> AS
> BEGIN
> if update(UserName) and update(DateModified) begin
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from dbo.Lcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=0
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from DataEntryArchive.dbo.aLcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=1
> end
> END
> When I run following update in Query Analyzer (notice the
> lcustkeycode_id is primary key so only 1 row should be affected):
> update vw_LCustKeycode
> set username='jamma', datemodified=getdate()
> where lcustkeycode_id=111060167
> It said:
> (2 row(s) affected)
> (1 row(s) affected)
> (8 row(s) affected)
> (0 row(s) affected)
> (8 row(s) affected)
> I check the data and they are correct and really only 1 row was
> updated, but why it said so many rows were affected? I find there are
> no other trigers sitting there except my instead of trigger.
> Can anyone here explain this strange behaviour?
> Thanks,
> James
Look in Profiler and see what it's doing (look at SP:StmtCompleted
events in addition to SQL:StmtCompleted).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for your quick reply. Just now I closed Query Analyzer and entered it
again, then the results become:
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
Seems fine now. I can't explain what happened just now. Even when I set
nocount off, it retuned.
(2 row(s) affected)
(8 row(s) affected)
But now everything is fine.
"David Gugick" wrote:

> James Ma wrote:
> Look in Profiler and see what it's doing (look at SP:StmtCompleted
> events in addition to SQL:StmtCompleted).
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Hello, James
The extra "n row(s) affected" are probably a side effect of the "Show
execution plan" in Query Analyzer. Retry your query with this option on
and off to see if that's the problem.
Razvan

Fun (trouble) with outer join

Okay, I'm having an issue with an outer join that is just confounding
me! Can somebody help explain. I started out with a large query that
was not returning all of the data that I wanted, so i paired it down to
this simple outer join:
SELECT E.EMP_RNG_CDE
FROM DATA_TABLE B LEFT OUTER JOIN
CODE_TABLE E ON E.EMP_RNG_CDE =B.EMP_RNG_CDE
WHERE B.POST_CDE = '60503'
AND B.SEG_CDE = '5'
There are 6 rows in the in "CODE_TABLE" .. while only 2 rows in
"DATA_TABLE" will match.. so I want all 6 out of the "CODE_TABLE" every
time (hence my OUTER JOIN...)
my results look like this however:
EMP_RNG_CDE
1
2
-- I only received the rows that matched from B.. not the outer rows
found in E!
HOWEVER now this drives me really crazy.. if re-work the query to join
my tables in the WHERE Clause and use the "*=" syntax for my left outer
join.. it works just right!
SELECT E.EMP_RNG_CDE
FROM DATA_TABLE B, LEFT OUTER JOIN
CODE_TABLE E
WHERE E.EMP_RNG_CDE *= B.EMP_RNG_CDE AND
B.POST_CDE = '60503'
AND B.SEG_CDE = '5'
Returns:
EMP_RNG_CDE
1
2
3
4
5
6
? Aren't those 2 queries the same? Just with different syntax for the
LEFT OUTER JOIN '
Can somebody help me with why those two are different? And how I can
get the results from the 2nd query with a "OUTER JOIN" syntax in my
FROM clause?
thansks!
jeffprizJeff,
Try switching the order of the tables in the join clause or try a RIGHT
JOIN.
HTH
Jerry
<jeffpriz@.yahoo.com> wrote in message
news:1128372693.433884.78940@.g14g2000cwa.googlegroups.com...
> Okay, I'm having an issue with an outer join that is just confounding
> me! Can somebody help explain. I started out with a large query that
> was not returning all of the data that I wanted, so i paired it down to
> this simple outer join:
> SELECT E.EMP_RNG_CDE
> FROM DATA_TABLE B LEFT OUTER JOIN
> CODE_TABLE E ON E.EMP_RNG_CDE =B.EMP_RNG_CDE
> WHERE B.POST_CDE = '60503'
> AND B.SEG_CDE = '5'
> There are 6 rows in the in "CODE_TABLE" .. while only 2 rows in
> "DATA_TABLE" will match.. so I want all 6 out of the "CODE_TABLE" every
> time (hence my OUTER JOIN...)
> my results look like this however:
> EMP_RNG_CDE
> 1
> 2
> -- I only received the rows that matched from B.. not the outer rows
> found in E!
> HOWEVER now this drives me really crazy.. if re-work the query to join
> my tables in the WHERE Clause and use the "*=" syntax for my left outer
> join.. it works just right!
> SELECT E.EMP_RNG_CDE
> FROM DATA_TABLE B, LEFT OUTER JOIN
> CODE_TABLE E
> WHERE E.EMP_RNG_CDE *= B.EMP_RNG_CDE AND
> B.POST_CDE = '60503'
> AND B.SEG_CDE = '5'
> Returns:
> EMP_RNG_CDE
> 1
> 2
> 3
> 4
> 5
> 6
> ? Aren't those 2 queries the same? Just with different syntax for the
> LEFT OUTER JOIN '
> Can somebody help me with why those two are different? And how I can
> get the results from the 2nd query with a "OUTER JOIN" syntax in my
> FROM clause?
> thansks!
> jeffpriz
>|||The preserved table is on the LEFT or the RIGHT side of the infixed
operator.
Here is how OUTER JOINs work in SQL-92. Assume you are given:
Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z
and the outer join expression:
Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition
We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query. What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.
1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.
2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN
3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.
So let us execute this by hand:
Let @. = passed the first predicate
Let * = passed the second predicate
Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @.
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @.
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @.* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
--
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
--
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
--
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL
the final results:
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL
The basic rule is that every row in the preserved table is represented
in the results in at least one result row.
There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables
Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250
and let's do an extended equality outer join like this:
SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;
If I do the outer first, I get:
Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL
Then I apply the (qty < 200) predicate and get
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
Doing it in the opposite order
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL
Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;
.. or do it in the joining:
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;
Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-92.
For example to find the students who have taken Math 101 and might
have taken Math 102:
SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;|||The WHERE clause causes your outer join to "become" an inner join. Not
explicitly, but in effect. (Put as simply as possible.)
Change your where clause like this or - as Jerry suggests - use RIGHT outer
join:
WHERE (B.POST_CDE = '60503' or B.POST_CDE is null)
AND (B.SEG_CDE = '5' or B.SEG_CDE is null)
ML|||Thanks! That actually helped a good bit! (it took me 5 reads, but I got
there!) LOL
I see where it's going. And i got my simple query to work, and I was
able to go back to my full-blown query to get it to work too
correctly..
thanks

Fumo?

Fumo 10 euro 0,8 grammi
Maria 10 euro 0,8 grammi
Cocaine 100 euro 1 g> Fumo 10 euro 0,8 grammi
> Maria 10 euro 0,8 grammi
> Cocaine 100 euro 1 g
This must be for those 30-hour debugging sessions. :)
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||LOL!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:%23OCbdED8FHA.1140@.tk2msftngp13.p
hx.gbl...
> This must be for those 30-hour debugging sessions. :)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||Mike,
After your accident, didn't you get a prescription for medical "Maria"?
::-)
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23OCbdED8FHA.1140@.tk2msftngp13.phx.gbl...
> This must be for those 30-hour debugging sessions. :)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> After your accident, didn't you get a prescription for medical "Maria"?
No way.
In fact, the "smoking nazi's" were so hardcore, that when I was in the
hospital for a WHOLE WEEK they wouldn't let me go outside to smoke. So as I
was getting more and more fidgety and irritable because all I wanted was a
@.#$% smoke -- their solution was to send a PSYCHIATRIST to my room to try to
figure out why I was so upset.
He came into the room with all that pedantic emotional psycho babble and I
was like, "DUDE, this is really quite simple: I just want a friggen
cigarette!"
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Sure, you told them that all you wanted was a smoke, but the psychiatrists
knew that, on a deeper sub-conscious level, you really just want to
'possess' your mother. I'm sure they were a big help in resolving your
frustration. ;-)
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%2351s%23cD8FHA.1020@.TK2MSFTNGP15.phx.gbl...
> No way.
> In fact, the "smoking nazi's" were so hardcore, that when I was in the
> hospital for a WHOLE WEEK they wouldn't let me go outside to smoke. So as
> I was getting more and more fidgety and irritable because all I wanted was
> a @.#$% smoke -- their solution was to send a PSYCHIATRIST to my room to
> try to figure out why I was so upset.
> He came into the room with all that pedantic emotional psycho babble and I
> was like, "DUDE, this is really quite simple: I just want a friggen
> cigarette!"
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>

Fully Qualified Query Accross Databases

Are there any perfmonace or query optimization limitations or issues that arise when issueing a fully qualified query across multiple databases on the same Instance of SQL Server. In other words are all features of query optimization fully supported in queries that span databases on the same Instance.

The only limitations that I'm aware of are the same limitations if the queries only run against one database, i.e. the databases have to be properly optimized for performance. If one database is properly indexed and one is not the bottleneck will be the database that isn't properly indexed.
|||

That is a fact, but I am really trying to determine if there are any optimizer limitations or nuances like there are if you are using partitioning.

|||The fact that there may be partitioning really isn't relevant since partitioning is only undertaken to optimize query performance anyway by distributing data across multiple files. As I mentioned, if the databases are optimized, whether through the use of partitioning or any other strategy, then cross database queries aren't an issue. There aren't any optimizer limitations or nuances that need to be considered.

If you were performing distributed queries across multiple linked servers or across multiple resource engines, i.e. SQL Server/Oracle then there might be some things to consider but even then, the issue would probably still come down to whether the databases are properly optimized.

Fully qualified object names help reuse of execution plans.

Fully qualified object names help reuse of execution plans.
Is this true? Discuss.
Also, what constitutes fully qualified?
Is it
server.database.owner.object
or can you get away with
database.owner.object
and still resuse your execution plans?
ThanksYou should read this KB. Even though it is for 2005 most of the same
principles still apply.
http://www.microsoft.com/technet/pr...005/recomp.mspx
As for your question of what does fully mean well that depends. Mostly it
means you should always specify the object owner along with the object.
dbo.yoursp or dbo.yourtable etc.
If you are accessing an object from within the same db then just specify the
owner and the object. Do not specify the database as it is no necessary and
actually invokes a few more lines of code than necessary. If you need to
specify an object in another db on the same server than you must specify
that as well.
OtherDB.dbo.Object
Andrew J. Kelly SQL MVP
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:2BBC6EA6-2E85-4643-8E07-16E031D8E8A9@.microsoft.com...
> Fully qualified object names help reuse of execution plans.
> Is this true? Discuss.
> Also, what constitutes fully qualified?
> Is it
> server.database.owner.object
> or can you get away with
> database.owner.object
> and still resuse your execution plans?
> Thanks
>|||Hi
http://msdn.microsoft.com/library/d...br />
4azp.asp
If you want more information, get yourself "Inside SQL Server 2000" by Kalen
Delaney.
owner.object is good enough for re-use.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:2BBC6EA6-2E85-4643-8E07-16E031D8E8A9@.microsoft.com...
> Fully qualified object names help reuse of execution plans.
> Is this true? Discuss.
> Also, what constitutes fully qualified?
> Is it
> server.database..object
> or can you get away with
> database.owner.object
> and still resuse your execution plans?
> Thanks
>

Fully Qualified names?

Hi all
A collegeue recently found an item on
http://www.sql-server-performance.c..._procedures.asp (do a search on
the page to "fully qualified name")
In theory this does make sense, and I can understand what the item is
getting at. I work with a team of 20 developers, and we had not implemented
that item.
I decided that before telling everyone to change the thousands of stored
procedures to use this convention, I wanted to be sure that it was worth it.
Anyway, I did a test using SSMS and it didn't seem to matter if I used just
the procedure name, a two part name or a three part name.
The article was written a while ago, so it must be directed at SQL2000. I'd
like to perform a better test to prove this item one way or another.
Can anyone suggest a fair, method of testing this claim and being able to
properly and fairly mesure the difference?
Regards
Colin Dawson
www.cjdawson.comHi, Colin

> Anyway, I did a test using SSMS and it didn't seem to matter if I used
> just the procedure name, a two part name or a three part name.
Well, I think if you specify fully qualified name it will help to SQL
Server to "find" the object more quickly , I mean not to run thro all
objects that have DBO. or all default schema , so it is just my point
of view
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:Udh8g.67139$wl.47904@.text.news.blueyonder.co.uk...
> Hi all
> A collegeue recently found an item on
> http://www.sql-server-performance.c..._procedures.asp (do a search
> on the page to "fully qualified name")
> In theory this does make sense, and I can understand what the item is
> getting at. I work with a team of 20 developers, and we had not
> implemented that item.
> I decided that before telling everyone to change the thousands of stored
> procedures to use this convention, I wanted to be sure that it was worth
> it. Anyway, I did a test using SSMS and it didn't seem to matter if I used
> just the procedure name, a two part name or a three part name.
> The article was written a while ago, so it must be directed at SQL2000.
> I'd like to perform a better test to prove this item one way or another.
> Can anyone suggest a fair, method of testing this claim and being able to
> properly and fairly mesure the difference?
> Regards
> Colin Dawson
> www.cjdawson.com
>|||On Wed, 10 May 2006 08:10:28 GMT, Colin Dawson wrote:

>Hi all
>A collegeue recently found an item on
>http://www.sql-server-performance.c..._procedures.asp (do a search o
n
>the page to "fully qualified name")
>In theory this does make sense, and I can understand what the item is
>getting at. I work with a team of 20 developers, and we had not implemente
d
>that item.
>I decided that before telling everyone to change the thousands of stored
>procedures to use this convention, I wanted to be sure that it was worth it
.
>Anyway, I did a test using SSMS and it didn't seem to matter if I used just
>the procedure name, a two part name or a three part name.
>The article was written a while ago, so it must be directed at SQL2000. I'
d
>like to perform a better test to prove this item one way or another.
>Can anyone suggest a fair, method of testing this claim and being able to
>properly and fairly mesure the difference?
Hi Colin,
Contrary to what this article says, there's no need to database-qualify
any objects (unless they are in a different database of course).
Owner qualifying is important, though, for the following two reasons:
1. Reduce chance of unexpected bugs. (Example: EXEC MyProc suddenly
throws errors when executed by Alice, becuase she created a procedure
Allice.MyProc yesterday)
2. Performance gain. (EXEC MyProc for Alice takes two lookups: first for
Alice.MyProc, then [if the former doesn't exist] for dbo.MyProc).
The performance gain is real, but in most cases not big enough to
warrant the cost of changing thousands of existing stored procedures.
Just make it your policy to demand that all table, view, and procedure
names shoould be owner-qualified in new code, and that the owner should
be added when changing any existing code (exempting bugfixes and small
changes under heavy time pressurre).
Hugo Kornelis, SQL Server MVP|||Colin Dawson (newsgroups@.cjdawson.com) writes:
> A collegeue recently found an item on
> http://www.sql-server-performance.c..._procedures.asp (do a
> search on the page to "fully qualified name") In theory this does make
> sense, and I can understand what the item is getting at. I work with a
> team of 20 developers, and we had not implemented that item.
> I decided that before telling everyone to change the thousands of stored
> procedures to use this convention, I wanted to be sure that it was worth
> it. Anyway, I did a test using SSMS and it didn't seem to matter if I
> used just the procedure name, a two part name or a three part name.
Including the database name is meaningless, and only increases the
complexity of the programming. (Since you need to get to get the database
from somewhere, and hard-coding it seems like a bad idea.)
Including schema/owner on the other hand matters.
Let's first look at this on SQL 2000. On SQL 2000, each user has a default
schema with the same name as the user name. Thus, if fred says
EXEC some_sp
(or make this call through RPC), SQL Server will first lookup "some_sp" in
the cache, including the uid. If there is a fred.some_sp, there will be a
cache hit, else a cache miss. Later SQL Server will look up some_sp in
metadata, and when fred.some_sp is missing, it will try dbo.some_sp, which
it will find in the cache.
On the other hand, if fred says:
EXEC dbo.some_sp
he will get a cache hit directly.
On SQL 2005, this is somewhat different, as a user can have dbo as the
default schema. In this case, at least in theory, it should not matter
whether you specify dho or not.
Now, how to set the default schema of a user? Well, if you use the new
command, CREATE USER, the user's default schema will be dbo, and there will
be no schema with the user's name. But if you create users with sp_adduser
out of habit, you will get a schema with the user's name, and that will
be the default schema.
Since you cannot be sure that future users get not added in a proper way,
it appears to be a good recommendation to include schema on SQL 2005 as
well.
As for the question of what the impact is of not including dbo, well I don't
have any numbers, but on a busy system with few cycles to spare, I believe
all those cache misses can cause quite some aggrevation.
I should add that including owner/schema is even more important when you
work with dynamic SQL, be that sp_executesql or parameterised command from
the client (which is sp_executesql anyway). If there is a single unqualified
table in the query, the plan will be private to the user, and cannot be
used by other users. (Again, not if the user's default schema is dbo, but
do not rely on that.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the info. It's helping me to understand this particular nuance.
In this case, I think that I'm not going to insist on the change. The
reason of this is that the db is accessed from a middle tier. The middle
tier uses connection pooling so we need to standardise on a specific user.
This user we need to setup, to it is safe (in this instance) to rely on the
the schema being set to dbo.
I'll certainly remember this for when I'm working on and dynamic sql (of
which there is currently none in the entire app!)
If we find that the app comes even close to maxing out the processor (very
unlikley at this stage) then I'll take a look at forcing dbo. infront of all
objects.
Thanks for the enlightenment.
Regards
Colin Dawson
www.cjdawson.com
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BF808BB6063Yazorman@.127.0.0.1...
> Colin Dawson (newsgroups@.cjdawson.com) writes:
> Including the database name is meaningless, and only increases the
> complexity of the programming. (Since you need to get to get the database
> from somewhere, and hard-coding it seems like a bad idea.)
> Including schema/owner on the other hand matters.
> Let's first look at this on SQL 2000. On SQL 2000, each user has a default
> schema with the same name as the user name. Thus, if fred says
> EXEC some_sp
> (or make this call through RPC), SQL Server will first lookup "some_sp" in
> the cache, including the uid. If there is a fred.some_sp, there will be a
> cache hit, else a cache miss. Later SQL Server will look up some_sp in
> metadata, and when fred.some_sp is missing, it will try dbo.some_sp, which
> it will find in the cache.
> On the other hand, if fred says:
> EXEC dbo.some_sp
> he will get a cache hit directly.
> On SQL 2005, this is somewhat different, as a user can have dbo as the
> default schema. In this case, at least in theory, it should not matter
> whether you specify dho or not.
> Now, how to set the default schema of a user? Well, if you use the new
> command, CREATE USER, the user's default schema will be dbo, and there
> will
> be no schema with the user's name. But if you create users with sp_adduser
> out of habit, you will get a schema with the user's name, and that will
> be the default schema.
> Since you cannot be sure that future users get not added in a proper way,
> it appears to be a good recommendation to include schema on SQL 2005 as
> well.
> As for the question of what the impact is of not including dbo, well I
> don't
> have any numbers, but on a busy system with few cycles to spare, I believe
> all those cache misses can cause quite some aggrevation.
> I should add that including owner/schema is even more important when you
> work with dynamic SQL, be that sp_executesql or parameterised command from
> the client (which is sp_executesql anyway). If there is a single
> unqualified
> table in the query, the plan will be private to the user, and cannot be
> used by other users. (Again, not if the user's default schema is dbo, but
> do not rely on that.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Colin Dawson (newsgroups@.cjdawson.com) writes:
> In this case, I think that I'm not going to insist on the change. The
> reason of this is that the db is accessed from a middle tier. The middle
> tier uses connection pooling so we need to standardise on a specific
> user. This user we need to setup, to it is safe (in this instance) to
> rely on the the schema being set to dbo.
And you are on SQL 2005?
Else you need to make that standardised user dbo.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97C04703D6F1Yazorman@.127.0.0.1...
> Colin Dawson (newsgroups@.cjdawson.com) writes:
> And you are on SQL 2005?
> Else you need to make that standardised user dbo.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Yes, I've been on SQL2005 for the last 12 months of so.
Regards
Colin Dawson
www.cjdawson.com