ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,946 through 6,960 (of 10,143 total)

RE: Using CASE in a WHERE clause

GSquared (12/3/2010)
select * from dbo.MyTable where @Parameter = 'A' or (ParentTradeID is not null and @Parameter = 'O')
or (ParentTradeID is null and @Parameter = 'R');
Wouldn't that do it?
Hey Gus, reckon you're still...
^{“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

December 3, 2010 at 6:35 am

#1258357
RE: Burned Out?

WayneS (12/3/2010)
Chris Morris-439714 (12/3/2010)
CirquedeSQLeil (12/2/2010)
Jack Corbett (12/2/2010)
For those not on twitter and who don't have my blog in their feed reader (shame on you!), here's the blog post I mentioned,...
^{“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

December 3, 2010 at 6:32 am

#1258353
RE: Update Table If Record Exists Else Insert ?

The two-stage method mentioned by GSquared is outlined 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

December 3, 2010 at 6:27 am

#1258348
RE: Today's Random Word!

Craig Farrell (12/2/2010)
Logging - That orange tree really needs to come down....
Lemonwood makes an excellent bow, wonder if orange is any good?
^{“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

December 3, 2010 at 2:56 am

#1258265
RE: Burned Out?

CirquedeSQLeil (12/2/2010)
Jack Corbett (12/2/2010)
For those not on twitter and who don't have my blog in their feed reader (shame on you!), here's the blog post I mentioned, http://goo.gl/fb/FviPR
I am going...
^{“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

December 3, 2010 at 2:54 am

#1258264
RE: add data separate with a coma in temp table !?

WayneS (12/2/2010)
Chris,
Interesting code. Do you have any performance testing on your method compared to the DelimitedSplit8k function? (Click here for the latest Delimited Split Function.)
Hi Wayne, no I've not done...
^{“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

December 3, 2010 at 2:51 am

#1258262
RE: Find Similar rows and add totals together.

@leroy - thanks.
@scott - your method will almost certainly perform better than mine, with two analytical functions.
^{“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

December 2, 2010 at 9:17 am

#1257962
RE: Find Similar rows and add totals together.

scott.pletcher (12/2/2010)
I thought E was an id value and thus unique. Maybe I'm wrong there.
Maybe not - there's not enough info.
^{“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

December 2, 2010 at 9:01 am

#1257953
RE: Find Similar rows and add totals together.

scott.pletcher (12/2/2010)
Barring some type of "fuzzy" update, the fastest way is probably to save your results as a temporary table, then use that table to UPDATE the original data and...
^{“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

December 2, 2010 at 8:54 am

#1257945
RE: Split string of concatenated words

Try this, Tim:
DROP TABLE #Sentences
CREATE TABLE #Sentences (Sentence VARCHAR(100))
INSERT INTO #Sentences (Sentence)
SELECT 'QualificationsLength' UNION ALL
SELECT 'DepthMustBeNumeric' UNION ALL
SELECT 'HeightMustBeNumeric' UNION ALL
SELECT 'WidthMustBeNumeric' UNION ALL
SELECT 'DepthNotValid' UNION ALL
SELECT 'HeightNotValid' 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

December 2, 2010 at 8:07 am

#1257906
RE: Split string of concatenated words

Tim-24860 (12/2/2010)
I wonder if anyone could help me with a problem I'm having.
I have some data in a table that contains many joined words e.g. ThisIsASample
What I would like 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

December 2, 2010 at 7:23 am

#1257874
RE: avoding recursion in CTE

Abhijit More (12/2/2010)
Is there is any way to break / avoid the recursion in the CTE QUERY??
Break? What do you mean?
Avoid? Yes of course - don't write your CTE as...
^{“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

December 2, 2010 at 7:09 am

#1257861
RE: Extracting duplicates within a specific range

You might find it easier to use a temp table to hold your ranges. Finding the dupes is straightforward:
DROP TABLE #Person
CREATE TABLE #Person (Number CHAR(5), Firstname VARCHAR(20), Lastname VARCHAR(40), Email...
^{“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

December 2, 2010 at 7:01 am

#1257850
RE: add data separate with a coma in temp table !?

-- make some sample data, about 100,000 rows
DECLARE @variable TABLE (OneRowOfData VARCHAR(200))
INSERT INTO @variable (OneRowOfData)
SELECT '12345|name|domaine|category|2010/11/15|2011/10/30|2|' UNION ALL
SELECT '12346|name|domaine|category|2010/11/15|2011/10/30|2|' UNION ALL
SELECT '12347|name|domaine|category|2010/11/15|2011/10/30|2|'
INSERT INTO @variable (OneRowOfData) SELECT * FROM @variable
INSERT INTO...
^{“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

December 2, 2010 at 4:58 am

#1257791
RE: creating view from different tables

Use the following code as a template to prepare a CREATE TABLE and INSERT script, which when run will create the tables and populate them with some sample data.
CREATE 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

December 2, 2010 at 3:31 am

#1257773

Viewing 15 posts - 6,946 through 6,960 (of 10,143 total)