ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,471 through 1,485 (of 10,143 total)

RE: function for highest value across columns

Simpler:
SELECT *
FROM (SELECT col1 = 1, col2 = 2, col3 = 3, col4 = 4) s
CROSS APPLY (
SELECT MAXmyval = MAX(d.myval)
FROM (VALUES (col1), (col2), (col3), (col4)) d (myval)
) x
^{“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

June 24, 2016 at 5:26 am

#1886699
RE: Are the posted questions getting worse?

BWFC (6/24/2016)
tindog (6/24/2016)
Beatrix Kiddo (6/24/2016)
Well, I'm embarrassed to be British now.
I'm just shocked.
I can't believe it either.
Farrago has already admitted that the £350 million figure, upon which many people based...
^{“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

June 24, 2016 at 4:29 am

#1886687
RE: Are the posted questions getting worse?

GilaMonster (6/24/2016)
Ed Wagner (6/23/2016)
Phil Parkin (6/23/2016)
Jeff Moden (6/23/2016)
On the "Brexit" thing, the folks in GB really need to consider the ramifications to their automotive industries. The EU has a...
^{“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

June 24, 2016 at 1:58 am

#1886662
RE: converting UDF to be memory optimized

Or this:
DECLARE @pString VARCHAR(8000) = '-[ab]+[cd]-[ef]';
WITH E1(N) AS (
SELECT 1 UNION ALL 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

June 23, 2016 at 7:06 am

#1886517
RE: converting UDF to be memory optimized

Can it be any of these?
'-[ab]+[cd]-[ef]'
'+[ab]+[cd]-[ef]'
'[ab]+[cd]-[ef]' (first element defaults 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

June 23, 2016 at 6:41 am

#1886508
RE: converting UDF to be memory optimized

kyagi.jo (6/23/2016)
Thanks! This doesn't seem to work quite right when the first value is negative as I get a positive empty entry, but it gets me nearly there, so I'll...
^{“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

June 23, 2016 at 6:29 am

#1886503
RE: converting UDF to be memory optimized

Ed Wagner (6/23/2016)
Nice work, Chris. I like the way you handled the first element.
Haha! The OP doesn't 😀
^{“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

June 23, 2016 at 6:27 am

#1886501
RE: converting UDF to be memory optimized

Oh I see - I got completely the wrong end of the stick, I assumed you were looking for a means of speeding up the function. To that end, here's...
^{“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

June 23, 2016 at 5:31 am

#1886484
RE: converting UDF to be memory optimized

kyagi.jo (6/23/2016)
Something like "[ab]+[cd]-[ef]" would create a mini-table that would have a row for "ab", "cd", "ef", and tell you that "ab" and "cd" were positive, and "ef" is negative....
^{“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

June 23, 2016 at 5:15 am

#1886479
RE: converting UDF to be memory optimized

What might be typical inputs and outputs of this code?
^{“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

June 23, 2016 at 5:02 am

#1886475
RE: Gaps and Islands with previous possible values

squvi.87 (6/23/2016)
Luis Cazares (6/22/2016)
You could use the Quirky Update. Be sure to read and understand the following article as you might get this wrong if you don't know what you're...
^{“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

June 23, 2016 at 1:40 am

#1886439
RE: Gaps and Islands with previous possible values

squvi.87 (6/23/2016)
"severe damage"?
How many rows are you expecting the query to return, and what consumes those rows?
250 MILLION
and what consumes those rows? What are you going to do with...
^{“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

June 23, 2016 at 1:36 am

#1886438
RE: Gaps and Islands with previous possible values

squvi.87 (6/22/2016)
ChrisM@Work (6/22/2016)
SELECT a.ID, a.TestDate, a.Score, b.dt AS dates FROM Calendartable b OUTER APPLY ( SELECT TOP 1 a.ID, a.TestDate, a.Score FROM testdata a WHERE a.TestDate <= b.dt ORDER BY a.TestDate DESC ) a
Test data is...
^{“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

June 22, 2016 at 9:56 am

#1886327
RE: Gaps and Islands with previous possible values

SELECT
a.ID, a.TestDate, a.Score,
b.dt AS dates
FROM Calendartable b
OUTER APPLY (
SELECT TOP 1 a.ID, a.TestDate, a.Score
FROM testdata a
WHERE a.TestDate <= b.dt
ORDER BY a.TestDate DESC
) a
^{“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

June 22, 2016 at 9:38 am

#1886321
RE: Are the posted questions getting worse?

Brandie Tarvin (6/22/2016)
Beatrix Kiddo (6/22/2016)
I'm f@*king terrified.
Me too, and ashamed of the xenophobes.
Back to SQL Server, I work for a company that has a lot of trading systems. We are...
^{“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

June 22, 2016 at 9:24 am

#1886314

Viewing 15 posts - 1,471 through 1,485 (of 10,143 total)