ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,986 through 3,000 (of 10,143 total)

RE: Today's Random Word!

Ed Wagner (8/28/2014)
ChrisM@Work (8/28/2014)
TomThomson (8/28/2014)
Ed Wagner (8/28/2014)
BWFC (8/28/2014)
TomThomson (8/27/2014)
Ed Wagner (8/27/2014)
crookj (8/27/2014)
Ed Wagner (8/27/2014)
BWFC (8/27/2014)
Punched tape
Cards (remember the stack that got dropped?)
Chad
Election
Florida
Keys
Alligators
Everglades
Aviator shades
Fighter Pilot
Pitot tube
^{“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

August 28, 2014 at 6:13 am

#1741611
RE: Today's Random Word!

TomThomson (8/28/2014)
Ed Wagner (8/28/2014)
BWFC (8/28/2014)
TomThomson (8/27/2014)
Ed Wagner (8/27/2014)
crookj (8/27/2014)
Ed Wagner (8/27/2014)
BWFC (8/27/2014)
Punched tape
Cards (remember the stack that got dropped?)
Chad
Election
Florida
Keys
Alligators
Everglades
Aviator shades
^{“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

August 28, 2014 at 6:11 am

#1741608
RE: fragmented indexes cause TEMPDB growth?

sqlenforcer (8/27/2014)
If there is fragmentation in some of the indexes being used in a multi-table join query, would that cause TEMPDB to grow?
You're experiencing heavy tempdb usage during...
^{“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

August 28, 2014 at 4:35 am

#1741592
RE: right trim

ronan.healy (8/27/2014)
sorry I ment the text before the date.
that's the text I want to take
so its like this now BNY Mellon - 07/01/2014
and I want it like this BNY Mellon
DECLARE...
^{“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

August 27, 2014 at 6:39 am

#1741300
RE: Selecting latest record with info

There are a few alternatives, such as this:
SELECT MyStuff
FROM TableList t
OUTER APPLY (
SELECT TOP 1 item, customer, delnote, deldate
FROM #tmpdelnotes d
WHERE d.Item = t.Item
ORDER BY deldate DESC
) ou
Can you...
^{“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

August 27, 2014 at 5:53 am

#1741283
RE: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

twin.devil (8/27/2014)
i would recommend to check you Users table 1st. i feel there are duplicates and that might be give you the clue
Following is the example have been made to...
^{“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

August 27, 2014 at 2:04 am

#1741212
RE: JOIN BIG and SMALL TABLEs

tony28 (8/26/2014)
ChrisM@Work (8/26/2014)
tony28 (8/25/2014)
I added second sql plan with query with condition before update statistics..
is this so big different between before and after ?
here looks almost ok
Query 2 works...
^{“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

August 27, 2014 at 2:02 am

#1741211
RE: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Finally, here's one of several alternative ways to write essentially the same query:
SELECT
vs.PipeSystemID,
vs.PipelineID,
vs.OrionStationSeries,
vs.ValveSectionBegin,
Builder = ISNULL(u1.FirstName + ' ', '') + ISNULL(u1.LastName,''),
QC = ISNULL(u2.FirstName + '...
^{“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

August 27, 2014 at 1:50 am

#1741206
RE: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

In this version, I've made each table reference completely unambiguous and also included TOP to eliminate dupes in the subqueries. This is one of those very few cases where TOP...
^{“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

August 27, 2014 at 1:47 am

#1741205
RE: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here's one of those subqueries from the output list. Run it and see what it returns.
SELECT [Final Engineer] = (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))
FROM Users
INNER JOIN...
^{“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

August 27, 2014 at 1:40 am

#1741203
RE: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Here's your query reformatted to make it easier to understand what you are trying to do:
SELECT
ValveSections.PipeSystemID,
ValveSections.PipelineID,
ValveSections.OrionStationSeries,
ValveSections.ValveSectionBegin,
Builder = (SELECT (COALESCE(FirstName, '') + ' ' + COALESCE(LastName,''))
FROM...
^{“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

August 27, 2014 at 1:28 am

#1741200
RE: Display ALL rows even if no data exists

David Burrows (8/26/2014)
pwalter83 (8/26/2014)
Actually this worked superbly well for the OP ! I had been stuck on this for a long time now and had given up all hope
You're welcome...
^{“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

August 26, 2014 at 10:00 am

#1741067
RE: date still showing up as YYYY-MM-DD

winston Smith (8/26/2014)
Finally, if it doesn't return what you expect, can you provide an online source which shows that what you are expecting to see isn't just a wish.
if i...
^{“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

August 26, 2014 at 7:58 am

#1741026
RE: date still showing up as YYYY-MM-DD

winston Smith (8/26/2014)
ChrisM@Work (8/26/2014)
winston Smith (8/26/2014)
Im trying to change the date format to DD-MM-YYYY serverwide.
i have changed to british english in the server settings, and my user also has british...
^{“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

August 26, 2014 at 7:38 am

#1741018
RE: Parameter Sniffing

Louis Hillebrand (8/25/2014)
Chris,
Here is the execution plan..
The OUTER APPLY does 100k seeks on the Follow-Up table.
Louis.
The cost is as likely to be the residual predicate on the quotation table. Try...
^{“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

August 26, 2014 at 7:15 am

#1741009

Viewing 15 posts - 2,986 through 3,000 (of 10,143 total)