ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,736 through 9,750 (of 10,143 total)

RE: UK Cultures

James A. Lawrence (7/30/2008)
What is a chav?
She has you for breakfast.
^{“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 30, 2008 at 1:15 pm

#850817
RE: Substring gives error

Did you find a useful resolution for this problem?
^{“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 30, 2008 at 10:13 am

#850660
RE: UK Cultures

Pint of strong black coffee and three smokes.
^{“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 30, 2008 at 9:42 am

#850629
RE: SQL Brain

Shaun McGuile (7/30/2008)
I don't keep contact details in my head,
but I do have a 1 through 12 instant lookup multiplcation matrix.
.i.e. give me any two integers 1 through 12 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

July 30, 2008 at 9:41 am

#850625
RE: problem creating query.

DROP TABLE #OrderRenewals
CREATE TABLE #OrderRenewals (OrderRenewalID INT, OrderRenewalRef INT, OrderID INT, RenewalStatusCode CHAR(3), RenewalDate DATETIME)
INSERT INTO #OrderRenewals (OrderRenewalID, OrderRenewalRef, OrderID, RenewalStatusCode, RenewalDate)
SELECT 1, 10, 444, 'ACT', '01-06-2006' UNION ALL
SELECT 2,...
^{“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 30, 2008 at 8:46 am

#850571
RE: Cannot check vales returned from a function in a CASE statement

ScoreA and ScoreB don't exist for the WHERE to use yet.
INSERT INTO Tablex (a,b,c,d,ScoreA,ScoreB)
SELECT a,b,c,d,ScoreA,ScoreB
FROM (
SELECT DISTINCT a,b,c,d,
ScoreA = (CASE WHEN...
^{“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 30, 2008 at 8:18 am

#850537
RE: Problems adding foreign key

Yep, spot on Mr Baseball.
/* table definitions for testing =====================
-- Payment batch table ==========================
CREATE TABLE [dbo].[ptPaymentBatches] (
[PaymentBatchID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED...
^{“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 30, 2008 at 7:36 am

#850492
RE: Problem with alias name

Chandru (7/30/2008)
Yes Chris i too tried in that way but it will create problem
Let me try in a different scenario
Thanks
Chandru.
What problem, Chandru? I suspect your answer...
^{“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 30, 2008 at 6:51 am

#850436
RE: Problem with alias name

Chandru (7/30/2008)
If new fk_id is passed (i.e fk_id which is not present in custom_data table) then i have insert into custom_data table otherwise i have to update the...
^{“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 30, 2008 at 6:08 am

#850401
RE: Problem with alias name

Hi Chandru
Couple of questions:
1. How many rows are returned by the source FROM OpenXML(@xml_hnd, '/NewDataSet/Table1', 2)?
Always one, or possibly more than one? "Always one" would make things easy.
2. Are 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

July 30, 2008 at 5:37 am

#850384
RE: Substring gives error

Antonio's method is probably cleanest but if you want to pursue the method which you started with, then this will help.
DECLARE @LongStr VARCHAR(200), @ShortStr VARCHAR(200)
SET @LongStr = 'http://website.net/trimmed-Jacket.aspx?yyy=137668&DeptId=16465&TypeId=1&Type=0&viewall=1'
SET @ShortStr =...
^{“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 30, 2008 at 5:19 am

#850370
RE: Usage of "Tally Table"

That sounds about right, Chris.
I've also increased i and j to 1000 to generate a 1 mill row table and that took 46 seconds - but first I changed...
^{“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 30, 2008 at 4:45 am

#850358
RE: Problem with alias name

Hi Chandru,
There's a problem with the INSERT INTO ... SELECT ... block.
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 30, 2008 at 4:25 am

#850345
RE: Usage of "Tally Table"

karthikeyan (7/30/2008)
Coming back to my research, I have executed the below script.
.
.
.
The above code took 2 hour 10 minutes for inserting 1,000,000 records.
It takes less than a millisecond on...
^{“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 30, 2008 at 3:59 am

#850336
RE: How to identify the position of the nth occurance of character in a string

Hello
If you're prepared to experiment a little, then a numbers or tally table can make this kind of operation really sing. Here's something which might start you off.
DECLARE @cSearchExpression nvarchar(4000),...
^{“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 30, 2008 at 3:45 am

#850330

Viewing 15 posts - 9,736 through 9,750 (of 10,143 total)