Question about Dynamic WHERE clause without Dynamic SQL statem

Well... It looks like the consensus on this is that its bad for performance
to use COALESCE in the manner I hoped to.
I was on the comment about not using uniqueidentifiers. Not sure
what was meant by "you cannot validate or verify such non-relational
proprietary extensions". For some background, we use uniqueidentifiers
because some applications span physically disconnected networks that are
routinely synchonized using DataSets serialized as an XML files. I won't
bore ya'll with the details.
Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
tho. There is no reason why we can't have respectful conversations with eac
h
other on these newsgroups.
I do take offense to "tidy code does not mean efficient code". This is a
matter of analyzing your performance requirements against maintainability.
Tidy code is much more maintainable. Now, in this instance, the consensus i
s
that it is severely detrimental to performance. In this case, I would agree
on using a non-tidy efficient stored procedure. However, if the impact to
performance was only a couple clock ticks extra, I would go with tidy code
since the improvement in maintainability would far outweigh the minor
decrease to performance.
I thank you all for your inputs!
-Nate
"Tony Rogerson" wrote:

> This will do a TABLE SCAN or CLUSTERED INDEX SCAN which is effectively a
> table scan.
> Can I have a list of clients you have recommended this too and a contact
> name there so I can go and fix your lousy solution, this won't scale, it
> will cause all sorts of blocking on a real system that has
> update/insert/delete activity.
> Tidy code does not mean efficient code.
> You've been told several times this won't work well on SQL Server so why
> continue to post this horrible, inefficient solution?
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1147826363.210359.308410@.y43g2000cwc.googlegroups.com...
>
>> I was on the comment about not using uniqueidentifiers. Not sure
> what was meant by "you cannot validate or verify such non-relational
> proprietary extensions".
What Celko meant is that if you add me to your database as a customer, and
assign me some system-generated GUID, then you add Tony, and assign him some
other GUID. If the database became corrupt, and you saw rows associated
with both of us and both of our GUIDs, how would you validate which GUID
truly belonged to who? Other than your own data store, there is nothing
that ties that GUID to me in a meaningful way.
Same argument goes for IDENTITY columns. If you don't remember that I was
assigned ID #455, would you be able to rebuild the database in the event of
a disaster, and still give me the same customer ID #?|||My tactlessness is directed 100% at --CELKO-- because of his condesending,
irgnorant and bad attitude towards other people on these forums, especially
newbies - because of that I treat him the same way he treats other people,
childish I know - but he's got to learn... i see it caused you offense so I
apologise, it was not directed at you.
Check out my blog entry for today on the COALESCE comparison based on this
and other posts on real test data :
http://sqlblogcasts.com/blogs/tonyr.../05/17/444.aspx
On my test its the difference between reading 40KBytes (dynamic SQL version)
and 104,248KBytes (coalesce version).
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
> Well... It looks like the consensus on this is that its bad for
> performance
> to use COALESCE in the manner I hoped to.
> I was on the comment about not using uniqueidentifiers. Not sure
> what was meant by "you cannot validate or verify such non-relational
> proprietary extensions". For some background, we use uniqueidentifiers
> because some applications span physically disconnected networks that are
> routinely synchonized using DataSets serialized as an XML files. I won't
> bore ya'll with the details.
> Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
> tho. There is no reason why we can't have respectful conversations with
> each
> other on these newsgroups.
> I do take offense to "tidy code does not mean efficient code". This is a
> matter of analyzing your performance requirements against maintainability.
> Tidy code is much more maintainable. Now, in this instance, the consensus
> is
> that it is severely detrimental to performance. In this case, I would
> agree
> on using a non-tidy efficient stored procedure. However, if the impact to
> performance was only a couple clock ticks extra, I would go with tidy code
> since the improvement in maintainability would far outweigh the minor
> decrease to performance.
> I thank you all for your inputs!
> -Nate
> "Tony Rogerson" wrote:
>|||I still use this sort of code (coalesce) with simple lookup queries, where
the volume of data is small and the performance impact is minimal. Since
our databases are not hammered constantly the only real impact is that the
end user waits an extra few milliseconds. If the database had more
activity, then I would be more likely to use dynamic SQL. I do, however,
consider it more useful for the complex queries or reports, where
performance is more noticeable.
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
> Well... It looks like the consensus on this is that its bad for
performance
> to use COALESCE in the manner I hoped to.
> I was on the comment about not using uniqueidentifiers. Not sure
> what was meant by "you cannot validate or verify such non-relational
> proprietary extensions". For some background, we use uniqueidentifiers
> because some applications span physically disconnected networks that are
> routinely synchonized using DataSets serialized as an XML files. I won't
> bore ya'll with the details.
> Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
> tho. There is no reason why we can't have respectful conversations with
each
> other on these newsgroups.
> I do take offense to "tidy code does not mean efficient code". This is a
> matter of analyzing your performance requirements against maintainability.
> Tidy code is much more maintainable. Now, in this instance, the consensus
is
> that it is severely detrimental to performance. In this case, I would
agree
> on using a non-tidy efficient stored procedure. However, if the impact to
> performance was only a couple clock ticks extra, I would go with tidy code
> since the improvement in maintainability would far outweigh the minor
> decrease to performance.
> I thank you all for your inputs!
> -Nate
> "Tony Rogerson" wrote:
>
SQL|||Interesting stand point Aaron, that would assume the GUID or IDENTITY was
corrupted, what would prevent your other identifying keys from going the
same way?
Once assigned it should never be changed, IDENTITY does protect you that way
but using UNIQUEIDENTIFIER doesn't...
Personally, I prefer IDENTITY as the surrogate key because its more readable
than a 36 character hex number, IDENTITY is also fragmentation friendl(ier)
than the random nature of the GUID.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eYihBZbeGHA.3888@.TK2MSFTNGP02.phx.gbl...
> What Celko meant is that if you add me to your database as a customer, and
> assign me some system-generated GUID, then you add Tony, and assign him
> some other GUID. If the database became corrupt, and you saw rows
> associated with both of us and both of our GUIDs, how would you validate
> which GUID truly belonged to who? Other than your own data store, there
> is nothing that ties that GUID to me in a meaningful way.
> Same argument goes for IDENTITY columns. If you don't remember that I was
> assigned ID #455, would you be able to rebuild the database in the event
> of a disaster, and still give me the same customer ID #?
>|||> Interesting stand point Aaron, that would assume the GUID or IDENTITY was
> corrupted, what would prevent your other identifying keys from going the
> same way?
I'm not standing behind Celko's point, just clarifying it. But I do agree
that if your database got hosed, even if you still had all of my personal
information elsewhere, if you generated my primary key artificially (GUID,
sequence, identity, what have you) you'd be hard pressed to rebuild your
system and assign me the same primary key again. This is one reason we
strive to make sure the surrogate key remains internal -- a customer
shouldn't know his/her customer number if it's not something you can
validate and easily re-create if necessary. So to clarify further, I am not
against using surrogate keys, but I do understand why Celko and others find
them dangerous.

> Once assigned it should never be changed, IDENTITY does protect you that
> way but using UNIQUEIDENTIFIER doesn't...
> Personally, I prefer IDENTITY as the surrogate key because its more
> readable than a 36 character hex number, IDENTITY is also fragmentation
> friendl(ier) than the random nature of the GUID.
I agree wholeheartedly. We use GUIDs for other reasons, but we never make
them the key.|||> Well, Mr. Rogerson definitely has good knowledge,... not so much on tact
> tho. There is no reason why we can't have respectful conversations with
> each
> other on these newsgroups.
Wow, you think Tony lacks tact? First, his disrespect is aimed at exactly
one person, Celko. And if you spend more than 10 minutes here and witness
some of Celko's barraging against newbies, it's like comparing Lenin and
Barney.

> I do take offense to "tidy code does not mean efficient code". This is a
> matter of analyzing your performance requirements against maintainability.
> Tidy code is much more maintainable. Now, in this instance, the consensus
> is
> that it is severely detrimental to performance. In this case, I would
> agree
> on using a non-tidy efficient stored procedure. However, if the impact to
> performance was only a couple clock ticks extra, I would go with tidy code
> since the improvement in maintainability would far outweigh the minor
> decrease to performance.
As with many other things, it really depends. I think what Tony objects to
is using tidy code because it is tidy, without regard to any of the other
criteria for sound development practices.
A|||The funny thing is, of the 6 or so posts of his that I have read today,
every one of them was very polite and informative, without any of the usual
bashing. It appears he is in a good mood, or perhaps is just trying a new
approach (maybe it is just me, but I think he has been more diplomatic
lately). Personally, I appreciate the discussions that his posts generate,
if not always the tact. I like to be reminded of the tradeoff between
portability, scalability, and concise code, and we do get some thoughtful
discussions out of it. I do feel sorry for new visitors to the group who
catch him on a bad day, however.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:OPQF1ebeGHA.3808@.TK2MSFTNGP02.phx.gbl...
> My tactlessness is directed 100% at --CELKO-- because of his condesending,
> irgnorant and bad attitude towards other people on these forums,
especially
> newbies - because of that I treat him the same way he treats other people,
> childish I know - but he's got to learn... i see it caused you offense so
I
> apologise, it was not directed at you.
> Check out my blog entry for today on the COALESCE comparison based on this
> and other posts on real test data :
> http://sqlblogcasts.com/blogs/tonyr.../05/17/444.aspx
> On my test its the difference between reading 40KBytes (dynamic SQL
version)
> and 104,248KBytes (coalesce version).
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Nate" <Nate@.discussions.microsoft.com> wrote in message
> news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
sure
won't
a
maintainability.
consensus
to
code
a
contact
it
why
a
>|||I see now :) ... I just got done looking at some other threads and saw the
same pattern. One of my coworkers just informed me that he's seen CELKO
being less than respectful on other discussions. Sorry for my ignorance.
I probably should have said "I take exception" vice "offense" in my earlier
phrasing where I compared maintainability and performance. I wasn't
offended... just disagreed on the fact that the statement shouldn't be used
as a blanket statement, but should be expanded upon.
I'm just glad I can go somewhere and have SQL geniuses explain SQL to an SQL
dummy (me).
"Tony Rogerson" wrote:

> My tactlessness is directed 100% at --CELKO-- because of his condesending,
> irgnorant and bad attitude towards other people on these forums, especiall
y
> newbies - because of that I treat him the same way he treats other people,
> childish I know - but he's got to learn... i see it caused you offense so
I
> apologise, it was not directed at you.
> Check out my blog entry for today on the COALESCE comparison based on this
> and other posts on real test data :
> http://sqlblogcasts.com/blogs/tonyr.../05/17/444.aspx
> On my test its the difference between reading 40KBytes (dynamic SQL versio
n)
> and 104,248KBytes (coalesce version).
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a S
QL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Nate" <Nate@.discussions.microsoft.com> wrote in message
> news:0BD292E5-57F3-4D47-B60C-2264A4656AA2@.microsoft.com...
>
>|||Hi Nate,
I'm still laughing at Aaron comparing me and celko as Barney (me hopefully)
to Lenin - that is just so amusing.
There are a lot of fantastic people on these forums - I learn a lot here
myself, and believe me I'm the first one to admit I'm not a SQL genius -
things move too quickly and the product is just too big now.
Happy SQL'ing.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Nate" <Nate@.discussions.microsoft.com> wrote in message
news:D887EF0B-C397-4309-98CB-1134B1689B08@.microsoft.com...
>I see now :) ... I just got done looking at some other threads and saw the
> same pattern. One of my coworkers just informed me that he's seen CELKO
> being less than respectful on other discussions. Sorry for my ignorance.
> I probably should have said "I take exception" vice "offense" in my
> earlier
> phrasing where I compared maintainability and performance. I wasn't
> offended... just disagreed on the fact that the statement shouldn't be
> used
> as a blanket statement, but should be expanded upon.
> I'm just glad I can go somewhere and have SQL geniuses explain SQL to an
> SQL
> dummy (me).
> "Tony Rogerson" wrote:
>