ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,281 through 2,295 (of 10,143 total)

RE: issue with or statement

WHERE StateCodeName = 'Open'
AND SalesStageCode IN (
1,/* DISCOVERY */
121370000, /* PROPOSAL */
121370001, /* SIGN AGREEMENT */
121370002/* INSTALLATION */
)
^{“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 12, 2015 at 8:44 am

#1819341
RE: Searching for allowed characters from table using function

azdeji (8/11/2015)
Hi ChrisM,
Your solution is perfect but it changes the case from lower / title to upper case can this be corrected to keep the original case?
Thanks
Sure. Sub in either...
^{“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 11, 2015 at 10:08 am

#1819046
RE: String Comparison function

Some links here.
^{“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 11, 2015 at 9:52 am

#1819040
RE: String Comparison function

nadersam (8/11/2015)
It will return success also, there are 3 different characters they don't have to follow each other.
I was able to write the attached function but it doesn't work if...
^{“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 11, 2015 at 9:48 am

#1819032
RE: Are the posted questions getting worse?

Luis Cazares (8/11/2015)
Am I late to the party? I was on a meeting.
Should I open the bottles of champagne? Who's in charge of the BBQ? :hehe:
Merry Christmas! Have I missed...
^{“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 11, 2015 at 7:15 am

#1818932
RE: Are the posted questions getting worse?

Koen Verbeeck (8/11/2015)
Only 10 posts to go...
and you're reached the corner of the field.
^{“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 11, 2015 at 6:22 am

#1818880
RE: Are the posted questions getting worse?

djj (8/11/2015)
ChrisM@Work (8/11/2015)
rodjkidd (8/11/2015)
Sean Lange (8/10/2015)
SQLRNNR (8/10/2015)
Sean Lange (8/10/2015)
kwere
The above sounds like with "air" or "bear"? I have heard this pronunciation but that has not happened frequently.
kwire
The above sounds like...
^{“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 11, 2015 at 6:14 am

#1818875
RE: Are the posted questions getting worse?

Koen Verbeeck (8/11/2015)
ChrisM@Work (8/11/2015)
rodjkidd (8/11/2015)
Sean Lange (8/10/2015)
SQLRNNR (8/10/2015)
Sean Lange (8/10/2015)
kwere
The above sounds like with "air" or "bear"? I have heard this pronunciation but that has not happened frequently.
kwire
The above sounds...
^{“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 11, 2015 at 4:01 am

#1818841
RE: Are the posted questions getting worse?

rodjkidd (8/11/2015)
Sean Lange (8/10/2015)
SQLRNNR (8/10/2015)
Sean Lange (8/10/2015)
kwere
The above sounds like with "air" or "bear"? I have heard this pronunciation but that has not happened frequently.
kwire
The above sounds like "clear" or...
^{“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 11, 2015 at 3:28 am

#1818826
RE: Query performance

tfeuz (8/10/2015)
We have 2 databases on different servers with identical structures and indexes
Database 1 has a stored procedure that runs in approximately 40 seconds on 15,000 rows of data
Database...
^{“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 11, 2015 at 1:39 am

#1818808
RE: please help me: select is verry slow from table 5 milion record

ChrisM@Work (8/10/2015)
-- Rewrite your inner query to use SARGable predicates.
Until and unless you change at least one of your predicates to be SARGable, your query will use a full table...
^{“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 11, 2015 at 1:31 am

#1818806
RE: OFFSET is not accepting start index

thbaig (8/10/2015)
Thank you Chris. You are correct and initially I had same understanding, but I de-tracked 🙁
Thanks again
No worries, I have days like that too - more as the grey...
^{“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 10, 2015 at 10:08 am

#1818625
RE: Searching for allowed characters from table using function

This is completely different, using a msTVF vs an iTVF but doesn't have the overhead of the XML black box:
CREATE FUNCTION fn_RemoveUnwantedCharacters
(@MyString VARCHAR(50)) -- arbitrary max, matches iTally table...
^{“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 10, 2015 at 9:11 am

#1818607
RE: OFFSET is not accepting start index

This might help:
DECLARE @pageSize INT, @PageNum INT
SELECT @pageSize = 60, @PageNum = 1
SELECT * FROM #inv ORDER BY #inv.InvoiceDateTime ASC
OFFSET 0 ROWS FETCH FIRST 60 ROWS ONLY
SELECT @pageSize =...
^{“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 10, 2015 at 8:03 am

#1818567
RE: OFFSET is not accepting start index

thbaig (8/10/2015)
Yes, I have confirmed. Overall data set size in temp table is ~100k rows.
When I set offset it terminate on EndRows , but always start from 0. e.g. if...
^{“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 10, 2015 at 7:51 am

#1818561

Viewing 15 posts - 2,281 through 2,295 (of 10,143 total)