Category: Microsoft SQL Server

sql server disk space used by database.
You need to run following query on each database to get information by tables and its size on disk with number of rows.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    SCHEMANAME,TotalSpaceMB DESC, t.Name

select GEOGRAPHY::Point(lat1, long1, 4326).STDistance(GEOGRAPHY::Point(lat2, long2 , 4326))

multiple ways of doing this.

select distinct inputvalue,
, round(CAST( inputvalueAS DOUBLE PRECISION ) / 1000.0, 3) — doesn’t secure 3 decimals
, round(inputvalue/1000.0,3) —  by default 6 decimals
, cast(inputvalue/1000.0 as float)
, convert(numeric (15,3), inputvalue/1000.0)

run below t-sql to get this information.

select @@VERSION