ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,801 through 4,815 (of 10,143 total)

RE: Ranking Query

Pankaj067 (4/16/2013)
Hello cooljagadeesh,
You can implement RANK in your query like this
select *,
RANK() over (partition by name order by row)...
^{“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

April 16, 2013 at 2:02 am

#1606726
RE: Alternative UPDATE Syntax

Have a look at the query shown in the first post of this thread. There are no aliases on the output columns so we can't tell which columns come 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

April 16, 2013 at 1:44 am

#1606722
RE: Comparing column data in 2 tables with the same schema containing 1.5 billion records

JayK (4/14/2013)
I have had the task of recreating a table in our data warehouse which contains 1.5 billions rows to make use of partioning on the clustered index by date....
^{“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

April 16, 2013 at 1:36 am

#1606719
RE: Arithmetic overflow error - double join?

Rob-350472 (4/15/2013)
Guys,
I've created a temp table which has a bunch of IDs and dates, something dead simple like this:
SELECT
Pg.ID,
pg.Name,
COUNT(pa.Log_ID) as A,
FROM tbla pg
JOIN #log pa on pa.ID = pg.ID
GROUP BY...
^{“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

April 16, 2013 at 1:27 am

#1606715
RE: Comparing Two Different Tables, Showing Values In Table 1 Not in Table 2

SELECT
TableSource = CASE WHEN T1.[Name] IS NULL THEN 'Table2' ELSE 'Table1' END,
T1.[Name],
T1.[Address],
T2.[Name],
T2.[Address]
FROM [dbo].[My Table 1] T1
FULL OUTER JOIN [dbo].[My Table 2] T2
ON T1.[Name] = T2.[Name]
WHERE...
^{“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

April 16, 2013 at 1:18 am

#1606713
RE: comparing list to master list

SELECT d.*
FROM (VALUES ('ooo'),('bbb'),('ccc'),('ddd'),('eee'),('fff'),('ggg'),('hhh'),('iii'),('jjj'),('kkk'),('lll')) d (name)
WHERE NOT EXISTS (SELECT 1 FROM List l WHERE l.name = d.name)
^{“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

April 16, 2013 at 1:10 am

#1606711
RE: Alternative UPDATE Syntax

Many folks stick to this syntax:
UPDATE pv -- alias of first table listed in FROM clause
SET
PV_ChangeDate = GETDATE(),
PV_ChangeUser = 'Peter',
PV_IntValue = 1000
FROM ParamValues pv -- put the UPDATE target...
^{“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

April 15, 2013 at 8:32 am

#1606482
RE: Begginer Sub Query problem

vgargav (4/15/2013)
Fantastic, that link is perfect! Thanks ever so much, seems to me the more I learn about SQL I realise the less I actually know! I got so lost...
^{“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

April 15, 2013 at 8:25 am

#1606477
RE: Combine data from 2 tables and insert in another table

;WITH Credits AS (
SELECT crparty, cramount = SUM(crAmount)
FROM voucherCr
GROUP BY crparty
), Debits AS (
SELECT drparty, drAmount = SUM(drAmount)
FROM voucherDr
GROUP BY drparty
)
INSERT INTO ledgerBalance (party, closingBalance)
SELECT
Party = COALESCE(c.crparty,...
^{“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

April 15, 2013 at 6:49 am

#1606442
RE: Slows system performance

That looks ok to me, other than the joins to the temp table not being optimized. You don't really need the temp table anyway:
SET @RowsDeleted = 1000;
WHILE @RowsDeleted > 0
BEGIN
BEGIN...
^{“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

April 15, 2013 at 6:26 am

#1606439
RE: Begginer Sub Query problem

Yep, this is a recursive CTE, and this is one of the few problems where it's use would be recommended. I'll fish around for a reference or two...
Exploring Recursive CTEs...
^{“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

April 15, 2013 at 5:58 am

#1606433
RE: Slows system performance

Last of all, can you post the actual batch please? Change the table name if you need 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

April 15, 2013 at 5:22 am

#1606423
RE: Slows system performance

Can you run this please?
EXEC sp_spaceused 'mytable'
- change the table name to the name of the table you are deleting from.
Also, run this:
DBCC SQLPERF ( LOGSPACE )
^{“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

April 15, 2013 at 4:55 am

#1606418
RE: Need Link

sagar0838 (4/15/2013)
Can anyone provide link for :Visual Studio 2005 as i am unable to find it
If it's not in this list[/url], it's not 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

April 15, 2013 at 4:33 am

#1606412
RE: Slows system performance

How big is the data file (and any indexes)?
^{“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

April 15, 2013 at 4:16 am

#1606408

Viewing 15 posts - 4,801 through 4,815 (of 10,143 total)