Viewing 15 posts - 5,071 through 5,085 (of 7,631 total)
Garadin (10/24/2008)
Performing an operation that the optimizer HAS to take first, such as selecting all the data into a temp table or table variable would have avoided...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 4:49 pm
Garadin (10/24/2008)
rbarryyoung (10/24/2008)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 4:18 pm
adams.squared (10/24/2008)
declare @wh varchar(2)
set @wh='10';
select obitno, oaorsc
from
(select obitno, oaorsc
from hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100) as DerivedHistTable
where convert(int,oaorsc)=@wh
I still get a conversion...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 4:11 pm
Using a Tally table/CTE probably. How high do you want you Division numbers?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 3:51 pm
Another note: Although AND branches are not order sensitive in SQL (thus leading to this problem), CTE clauses are order sensitive (because later clauses can only refer to earlier...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 3:42 pm
select obitno, oaorsc
from hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100
and convert(int,oaorsc)=@wh
The reason that this does not work is because the order that clauses appear in the WHERE section does NOT...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 3:38 pm
Seggerman (10/24/2008)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 3:21 pm
Really?!? That is extremely surprising to me, I cannot imagine why it wouldn't use the new index?
Can you try the following version of Query1 and post the Actual Execution...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 3:12 pm
Well, the syntax was fine, it's the semantics that were wrong... 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 2:56 pm
There were a lot of security changes in SQL 2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 2:54 pm
Right, sorry:
SELECT COALESCE(A.Value, B.Value)
FROM TableA A
FULL OUTER JOIN TableB B ON Source.ID = B.ID
I tested the syntax this time 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 12:45 pm
Tim Benninghoff (10/24/2008)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 12:43 pm
Tim Benninghoff (10/24/2008)[hr1) If I grant connect to a user in database B mapped to the login, I don't seem to need to have the originating database marked as...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 12:35 pm
The easy way to do this is through Schemas: Put the tables in one schema (DAT) and everything else in another schema (EXE) that has access rights to DAT.
Then...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 12:30 pm
OK, Garadin just made a small mistake, which is easily fixed:
SELECT COALESCE(A.Value, B.Value)
FROM TableA A ON Source.ID = A.ID
FULL OUTER JOIN TableB B ON Source.ID = B.ID
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 24, 2008 at 12:23 pm
Viewing 15 posts - 5,071 through 5,085 (of 7,631 total)