Find largest table in a SQL Server database Monitoring table size over time is very important to the SQL Server Engine. In performance point of view to run queries. At the most optimal and avoid performance issues due to larger table size as it important factor in SQL Server performance There are often many times people ask, how to find the largest tables/objects in a database, so below scripts, will determine which objects in a database are largest. SELECT t.name AS TableName, i.name as indexName, sum(p.rows) as RowCounts, sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) as DataPages, (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, (sum(a.data_pages) * 8) / 1024 as DataSpaceMB FROM sys.tables t INNER JOIN sys.indexes i ON t.object_id = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.name NOT LIKE 'dt%' AND i.object_id > 255 AND i.index_id <= 1 GROUP BY t.name, i.object_id, i.index_id, i.name ORDER BY TotalSpaceMB DESC

SQL ordering both negative and positive records in order issue

Vikash

I have a soccer stats table with goal difference that are both positive and negative values. I need to order the goal difference column in order from highest to lowest like e.g

+5 +1 0 -1 -5

I have tried a couple of sql queries but not getting exactly what I need. I can't seem to get the positive and negative values to order properly.

(SELECT stsID, tmName, tmGroup, stsP, stsW, stsD, stsL, stsGF, stsGA, stsGD, stsPoints FROM `stats` WHERE tmGroup='A' AND stsGD='0') 
UNION 
(SELECT stsID, tmName, tmGroup, stsP, stsW, stsD, stsL, stsGF, stsGA, stsGD, stsPoints FROM `stats` WHERE tmGroup='A' AND stsGD<'0' ORDER BY stsGD DESC, stsPoints DESC, stsGF DESC)
UNION 
(SELECT stsID, tmName, tmGroup, stsP, stsW, stsD, stsL, stsGF, stsGA, stsGD, stsPoints FROM `stats` WHERE tmGroup='A' AND stsGD>'0' ORDER BY stsGD ASC, stsPoints DESC, stsGF DESC)

The above gave me:

+5, -5, -1, +1

I also modified it as

SELECT stsID, tmName, tmGroup, stsP, stsW, stsD, stsL, stsGF, stsGA, stsGD, stsPoints FROM stats WHERE tmGroup='A' ORDER BY stsPoints DESC, stsGF DESC, stsGA ASC

this gave me:

+5, +1, -5, -1

I have tried using SIGN() but still not the result I wanted.

SELECT stsID, tmName, tmGroup, stsP, stsW, stsD, stsL, stsGF, stsGA, stsGD, stsPoints FROM stats WHERE tmGroup='A' ORDER BY stsPoints DESC, SIGN(stsGD) DESC, stsGF DESC

output:

+5, +1, -5, -1

I just can't seem to get it right.