Hi Paul

Can you set up your sample data as ddl and dml please? There are instructions for this in the first article in my sig. Cheers.

Pankaj067 (4/16/2013)

---

Hello cooljagadeesh,

You can implement RANK in your query like this

select *,

RANK() over (partition by name order by row)...

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...

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....

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...

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...

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)

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...

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...

;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,...

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...

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...

Last of all, can you post the actual batch please? Change the table name if you need to.

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 )

If it's not in this list[/url], it's not here.