The first thing I'd ask is are you sure the columns you dropped are decimals?
The reason why I ask is that with variable columns (which Decimal is not), then SQL Server won't automatically reduce space used when these are dropped. To resolve this you can run DBCC CLEANTABLE. You might want to try this anyway.
However, if the columns that you dropped are only decimals, then this probably won't have an effect.
Another possibility is that the decimal columns were just not taking up that much space - depending on the precision, a decimal column can be quite small. You say that there is no perceptible difference, is it just a case that the decimal columns were taking up relatively little space compared to other columns in the table (and rest of the database)?
Check the space used of the actual table before and after the change. I used this query to check table spaced used
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
OBJECT_NAME(i.object_id)
Reference: I got this originally from this SO post.
To note/warning:
I'm assuming you're doing this in some sort of development or test environment, doing this sort of thing in production without testing is really not advisable. This earlier post is similar to yours (but is specifically about variable column lengths).
SpaceUsed
Your query to find space used looks fine, but there is an inbuilt stored procedure sp_spaceused that you might find easier (but probably doesn't make much of a difference.
Create a new Azure SQL Server on a different resource group.
New-AzureSqlDatabaseServer -Location "East US" -AdministratorLogin "AdminLogin" -AdministratorLoginPassword "AdminPassword"
Copy the source database to the newly created Azure SQL Server.
Start-AzureSqlDatabaseCopy -ServerName "SourceServer" -DatabaseName "Orders" -PartnerServer "NewlyCreatedServer" -PartnerDatabase "OrdersCopy"
Move the resource group of the Newly created Azure SQL Server to another subscription.
Move-AzureRmResource
-DestinationResourceGroupName
[-DestinationSubscriptionId ]
-ResourceId
[-Force]
[-ApiVersion ]
[-Pre]
[-DefaultProfile ]
[-InformationAction ]
[-InformationVariable ]
[-WhatIf]
[-Confirm]
[]
Best Answer
I'd have you look at tables and indexes with the following code:
You will want to check the size of the objects at regular intervals to see if you can determine which ones are in flux.