As it happens, originally I did use DENSE_RANK, but changed it on a whim as I posted.

DENSE_RANK...

SELECT Name = A.value('name[1]', 'varchar(50)'),

Value = A.value('value[1]', 'varchar(50)'),

LON = A.value('parent::document/parent::orderrequest/loannumber[1]', 'int')

FROM...

You could hack at it with CASE, but fundamentally the approach is flawed.

If you store key-value pairs in rows instead of columns, you will find the task trivial.

Paul

Thank you Matt :w00t:

:laugh: better :laugh:

Even that won't show all the databases on a server.

You need to run:

SELECT * FROM master.sys.sysdbreg;

from the DAC to see the hidden mssqlsystemresource database. 😉

Paul

Not guaranteed to split equally, but groups nicely:

select FireCentreName, ProjectID, DisplayName, RANK() OVER(ORDER BY FireCentreName) % 2 + 1 AS 'ColumnNumber'

from FireList

order by ColumnNumber, FireCentreName -- Not required, just...

That will just fit in the first (mixed) extent. My previous comments apply to indexes as well as...

Bob Hovious (8/5/2009)

---

But changing from a char(1) to a varchar, i'm sure pointers would have to be established on the page to support the varchar. It wouldn't necessarily...

Hey David,

On a large-ish table like this, even for a relatively modest change such as you describe, I would be very tempted to bulk copy the data out (ordered by...

I'm tempted to say: "it depends"; however I am going to say that it is pretty much essential to have tempdb on its own data path and with dedicated drives...

Good point!

Though actually the first...

I would just add that a nonclustered index that INCLUDEs every non-key column in the object is pretty close to being another clustered index. This can be an optimization...

With AWE in use on x86:

Performance Monitor Counters:

SQL Server: Buffer Manager: Total pages (multiply by 8KB) = data cache size

Process: sqlsrvr: PrivateBytes = other memory allocations, including mutli-page allocations ("MemToLeave")

So...

It would also help if you would post the output from:

SELECT * FROM sys.configurations;

...and...

The current SQL Server error log

...and...

DBCC MEMORYUSAGE;

The output from the second and last ones will be large,...