ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,181 through 3,195 (of 10,143 total)

RE: How to combine two codes

hoseam (7/7/2014)
Then it's serial I'm looking for.
Thanks.
Then you should use UNION (ALL). The results of the two queries have widely different column counts. You will have to decide which columns...
^{“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

July 7, 2014 at 2:14 am

#1727287
RE: Problem with group by and order by

DROP TABLE #Turnover
CREATE TABLE #Turnover
(location varchar(50),Total int)
INSERT INTO #Turnover (location,Total) VALUES
('A', 500),
('AB', 200),
('ABC', 100),
('BA', 100),
('BAC', 500),
('BAM', 100)
SELECT NewGroup = LEFT(location,1), location, Total...
^{“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

July 7, 2014 at 2:07 am

#1727285
RE: How to combine two codes

hoseam (7/7/2014)
What I want it's for the results of the second query to be joined to the results of the first query. I don't know if that's parallel or not.
Joined...
^{“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

July 7, 2014 at 1:47 am

#1727282
RE: Nested user-defined functions in tsql. Parameters passing trough INSERT-SELECT structures

What happens if you run this
SELECT
...
^{“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

July 4, 2014 at 8:35 am

#1727098
RE: Nested user-defined functions in tsql. Parameters passing trough INSERT-SELECT structures

Please post the definition of function dbo.fn_Nested.
^{“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

July 4, 2014 at 8:34 am

#1727097
RE: Execution plan of a CTE

Can you post the whole view definition?
^{“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

July 4, 2014 at 8:31 am

#1727096
RE: Execution plan of a CTE

What you've shown of your view definition equates to this:
SELECT
urpx.RoleID
FROM [DBA].dbo.URPX
INNER JOIN [DBA].dbo.URX
ON urpx.RoleID = urx.RoleID
LEFT OUTER JOIN (
SELECT
urx.RoleID,
urx.UserID
FROM [DBA].dbo.URX
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

July 4, 2014 at 7:20 am

#1727081
RE: How to combine two codes

Parallel combination or serial? If serial, you will have to match the number and type of output columns between the queries. If parallel, you will require a join strategy.
You're using...
^{“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

July 4, 2014 at 6:18 am

#1727073
RE: Best way to write a Search Query

GilaMonster (7/4/2014)
Byzza (7/4/2014)
Any Comments ?
You have security vulnerabilities in that. Specifically SQL Injection. DO NOT concatenate user input into a string which will be executed. It is perfectly possible...
^{“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

July 4, 2014 at 5:29 am

#1727067
RE: Best way to write a Search Query

Oh boy. Where to start with this one.
First off, the use of multiple right joins in the queries in the stored procedure suggest that the queries were generated using...
^{“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

July 4, 2014 at 2:32 am

#1727025
RE: Is there a clever way to do an UPDATE/MERGE and insert rows when there are duplicate matches?

hominamad (7/3/2014)
Thanks for the quick reply. So basically this is a two-step update, first an update then inserting the duplicates. I will experiment with that and see if it 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

July 3, 2014 at 7:34 am

#1726815
RE: ISNUMERIC() bug?

grantsaunders75 (7/2/2014)
ISNUMERIC('Value'+'e01') -- always worked perfectly for me.
Sneaky 😀
SELECT
NumberString,
[ISNUMERIC] = ISNUMERIC(NumberString),
[ISNUMERIC extra] = ISNUMERIC(NumberString+'e00'),
[Number] = CASE WHEN ISNUMERIC(NumberString+'e00') = 1 THEN CAST(NumberString AS FLOAT) ELSE NULL END
FROM (VALUES...
^{“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

July 3, 2014 at 5:20 am

#1726745
RE: columns compare

There are quite a few ways of doing this
SELECT
t.id,
t.idvalue,
[Diff] = ABS(t.idvalue - x.idvalue),
Flag = CASE WHEN ABS(t.idvalue - x.idvalue) > 18 THEN 'failure' ELSE 'success' END...
^{“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

July 3, 2014 at 3:43 am

#1726717
RE: Merge with duplicate rows

You should start a new thread for this, and perhaps include the action you would like to take depending upon the distribution of ID's between the two tables. For instance,...
^{“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

July 3, 2014 at 1:48 am

#1726696
RE: Are the posted questions getting worse?

Grant Fritchey (7/2/2014)
Did you hear, for a little while Tim Howard was listed on Wikipedia as the US Secretary of Defense. That's awesome.
That's brilliant!
^{“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

July 2, 2014 at 8:51 am

#1726536

Viewing 15 posts - 3,181 through 3,195 (of 10,143 total)