ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,751 through 6,765 (of 10,143 total)

RE: Using Identity Field as my main relation in joins

sammuts (8/16/2011)
Hi,
In all my tables my first field is an Identity field which is set as my primary key. Is it a good practice to use this fied...
^{“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 16, 2011 at 6:08 am

#1369028
RE: Inserting summarized data

troe_atl (8/15/2011)
Hi everyone.
I have a store procedure that has a summary query that return 4 rows but only inserts 2 rows into my table. Please take a look at 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

August 16, 2011 at 5:43 am

#1369014
RE: how to find the difference between current column and previous column

Here's another method which is 3x faster than OVER()
SELECT a.PlayerId, a.PlayerName, a.overs, a.PlayerScore - ISNULL(b.PlayerScore, 0) AS Score
FROM #score a
CROSS APPLY(SELECT [LastRow] = MAX(overs) FROM #score WHERE PlayerName = a.PlayerName...
^{“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 16, 2011 at 5:00 am

#1369005
RE: how to find the difference between current column and previous column

Prasanthi Reddy (8/16/2011)
This gives complete score in 1st over also in 2nd over. I want the score only in 2nd over.
i.e., difference between 2nd over and 1st over's score.
Also i...
^{“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 16, 2011 at 2:36 am

#1368975
RE: how to find the difference between current column and previous column

SELECT a.*, b.*
FROM score a
INNER JOIN score b ON b.PlayerName = a.PlayerName AND b.overs = 2
WHERE a.overs = 1
^{“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 16, 2011 at 1:41 am

#1368962
RE: problem on @@ROWCOUNT

@@ROWCOUNT will be the number of rows updated. Bear in mind that a row may be updated only once. Your findings indicate that for some rows of your target 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 15, 2011 at 8:56 am

#1368662
RE: problem on @@ROWCOUNT

peleg (8/15/2011)
first one
Is your rowcount equal to the result of this query?
SELECT COUNT(*)
FROM TargetTable_Staging tts
WHERE EXISTS (SELECT 1
FROM TargetTable t1
INNER JOIN Dyn_Company t2
ON t1.Company=t2.Name COLLATE SQL_Latin1_General_CP1_CI_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

August 15, 2011 at 8:12 am

#1368625
RE: Today's Random Word!

crookj (8/12/2011)
SQLRNNR (8/12/2011)
Revenant (8/12/2011)
SQLRNNR (8/12/2011)
Strengthed
Conan
Barbarian
Red Sonja
Coincidence (Red Sonja was on UK terrestrial tv last night - I'd forgotton how cr@p it was - even more so after 300)
^{“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 15, 2011 at 7:19 am

#1368588
RE: problem on @@ROWCOUNT

-- run this
SELECT COUNT(*)
from TargetTable_Staging tts
inner join (
select t1.id as PKid,t2.id as IndexId
FROM TargetTable t1 inner join Dyn_Company t2 (NOLOCK)
ON t1.Company=t2.Name COLLATE SQL_Latin1_General_CP1_CI_AS )t3 on tts.id=t3.PKid
-- and 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

August 15, 2011 at 7:11 am

#1368581
RE: Count across multiple tables with left outer join

Kiara (8/15/2011)
...Now I'm curious, though, so once I get back to the actual problem this is related to, I'll post a version of what actually works best in the environment...
^{“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 15, 2011 at 6:09 am

#1368544
RE: nothing returning for date

SELECT
[DAY number 1] = 01/01/2004, -- result is integer 0
[DAY number 2] = 31/12/2004, -- result is integer 0
[Date 1] = CAST(01/01/2004 AS DATETIME), --...
^{“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 15, 2011 at 5:58 am

#1368537
RE: Count across multiple tables with left outer join

Kiara (8/15/2011)
...Caveat: I haven't played with this approach against my actual data, so I don't know what the performance will look like yet...
Have a look at the actual execution plans...
^{“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 15, 2011 at 5:50 am

#1368533
RE: To improve the performance of the query.

LutzM (8/11/2011)
Wouldn't the WHERE clause be equal to
WHERE
fbt.cust_no != '0306A'
OR channel_cd in ('Correspond','Correspondent','Retail','Wholesale','IGNORE CHANNEL') ? :unsure:
Yes, and if there's an index on channel_cd, then your version would of...
^{“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 15, 2011 at 5:16 am

#1368517
RE: SQL Agent - TSQL Step Text Size

Put the TSQL into a stored procedure and call the sproc from the job.
^{“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 15, 2011 at 5:04 am

#1368511
RE: multiply aggregate

Or like this, depending on your data:
SELECT r1.ProductID,
[total number of units to a pallet] = r1.[qty in units] * r2.[units in a case] * r3.[cases to a pallet]
FROM...
^{“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 15, 2011 at 4:36 am

#1368499

Viewing 15 posts - 6,751 through 6,765 (of 10,143 total)