<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
T-SQL (SS2K8)
»
UNION vs OR --> NP-Complete Problem
13 posts, Page 1 of 2
1
2
»»
UNION vs OR --> NP-Complete Problem
Rate Topic
Display Mode
Topic Options
Author
Message
karthik M
karthik M
Posted Tuesday, February 05, 2013 2:53 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 8:34 AM
Points: 2,008,
Visits: 2,472
I have recently read about this in the below link.
http://www.sql-server-performance.com/2013/tsql-incorrect-union-operator/
I just heard about "NP-Complete Problem" first time :)
From Joe Celko...
This UNION vs OR problem has been in SQL for along time, not just T-SQL. The one-table VIEW with the OR is updatable; the UNION version counts as two tables, so the VIEW is not updateable, even tho it is logically equivalent.
When we wrote the Standards, we knew that VIEW updatign is an NP-Complete problem, so we went with the easiest definition for the Standards.
The conclusion is to use 'OR' instead UNION. Right?
karthik
Post #1415670
Eugene Elutin
Eugene Elutin
Posted Tuesday, February 05, 2013 3:06 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 2:08 AM
Points: 2,596,
Visits: 4,506
karthik M (2/5/2013)
I have recently read about this in the below link.
http://www.sql-server-performance.com/2013/tsql-incorrect-union-operator/
I just heard about "NP-Complete Problem" first time :)
From Joe Celko...
This UNION vs OR problem has been in SQL for along time, not just T-SQL. The one-table VIEW with the OR is updatable; the UNION version counts as two tables, so the VIEW is not updateable, even tho it is logically equivalent.
When we wrote the Standards, we knew that VIEW updatign is an NP-Complete problem, so we went with the easiest definition for the Standards.
The conclusion is to use 'OR' instead UNION. Right?
I wouldn't say so. As not-updateable VIEW can be made updateable with INSTEAD OF triggers help...
I also think that use 'OR' over 'UNION' should be decided case-by-case based on best performance.
_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)
How to post your question to get the best and quick help
Post #1415676
GilaMonster
GilaMonster
Posted Tuesday, February 05, 2013 3:07 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 4:58 AM
Points: 38,074,
Visits: 30,370
Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1415677
karthik M
karthik M
Posted Wednesday, February 06, 2013 5:56 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 8:34 AM
Points: 2,008,
Visits: 2,472
few more thoughts ?
karthik
Post #1416447
mister.magoo
mister.magoo
Posted Wednesday, February 06, 2013 6:06 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 1,333,
Visits: 4,016
karthik M (2/6/2013)
few more thoughts ?
It depends.
MM
Post #1416458
ScottPletcher
ScottPletcher
Posted Wednesday, February 06, 2013 2:31 PM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 10:11 AM
Points: 1,333,
Visits: 1,803
No, I wouldn't use OR just because that makes a view updateable -- UPDATEs aren't typically done using views like that anyway.
SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1416741
Eugene Elutin
Eugene Elutin
Posted Thursday, February 07, 2013 1:24 AM
SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 2:08 AM
Points: 2,596,
Visits: 4,506
ScottPletcher (2/6/2013)
No, I wouldn't use OR just because that makes a view updateable -- UPDATEs aren't typically done using views like that anyway.
What is the problem with updateable VIEW? I agree, usually views are not subjects of modification queries, but I don't think that "updateability" of a VIEW is evil enough to stop creating such views.
What about VIEW which doesn't even need OR or UNION, it can be updateable, will you not have such views at all?
_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)
How to post your question to get the best and quick help
Post #1416857
ChrisM@Work
ChrisM@Work
Posted Thursday, February 07, 2013 2:25 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 2:26 AM
Points: 5,705,
Visits: 11,136
GilaMonster (2/5/2013)
Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.”
- Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read
this article
.
Understanding and using APPLY, (I)
and
(II)
Paul White
Hidden RBAR: Triangular Joins
/
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Jeff Moden
Exploring Recursive CTEs by Example
Dwain Camps
Post #1416901
ScottPletcher
ScottPletcher
Posted Thursday, February 07, 2013 9:10 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 10:11 AM
Points: 1,333,
Visits: 1,803
Eugene Elutin (2/7/2013)
ScottPletcher (2/6/2013)
No, I wouldn't use OR just because that makes a view updateable -- UPDATEs aren't typically done using views like that anyway.
What is the problem with updateable VIEW? I agree, usually views are not subjects of modification queries, but I don't think that "updateability" of a VIEW is evil enough to stop creating such views.
What about VIEW which doesn't even need OR or UNION, it can be updateable, will you not have such views at all?
I think you misread what I said. I don't object to updateable views
per se
. I meant I wouldn't change the way I coded a view just to make the view updateable (unless I already intended to UPDATE via that view).
SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1417159
Paul White
Paul White
Posted Saturday, February 09, 2013 11:14 PM
SSChampion
Group: General Forum Members
Last Login: Yesterday @ 2:43 PM
Points: 10,990,
Visits: 10,576
karthik M (2/5/2013)
The conclusion is to use 'OR' instead UNION. Right?
No.
Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.
SQL is a declarative language: you specify the results you want, not how to get them; that's the optimizer's job.
Regarding the original article, it's interesting that the author chose not to make the clustered index on the table UNIQUE, even though the clustering key is an IDENTITY column. Had he done so, the example query would have produced this execution plan:
The example was also carefully crafted to use a very small number of rows and unhelpful nonclustered indexes. Joe Celko's comment about updatable views is mildly interesting if you want to understand why the SQL standard is the mess it is, but it has little practical consequence.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post Attachments
ssc.png
(
85 views,
14.45 KB
)
Post #1418080
« Prev Topic
|
Next Topic »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
