6

I have about 300 tables with 5.5kk rows each. One of the rows is using nvarchar(128) as a datatype (SQL Server 2012).
We decided to change this to int and add FK to dictionary table with all nvarchars.

After doing all of it I removed the nvarchar column, but the size of the tables remained the same. I used DBCC CLEANTABLE and rebuilt the indexes to reclaim free space, but size of the table is still not changing.

So far, the only way I found is to copy all data to the new table.

Question: What am I missing here ? Why the space is still marked as used and I can't free is by shrinking or CREANTABLE command?

Thank you!

Answ: Looks like the answer was pretty simple and I was not able to find it due to my lack of knowledge. The main problem here was heap fragmentation. This query worked for me:

ALTER TABLE [TABLE_NAME] REBUILD

I am not sure that it's the best way, but at least it's a working one.

Edited1: Sorry, I think I forgot to mention - I had clustered index on the Text field, so I had to remove the index to be able to actually remove the field. Now I have no indexes.

Edited2:

old table:

CREATE TABLE [dbo].[Data_](
    [ID] [bigint] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [Text] [nvarchar](128) NOT NULL,
    [Category] [tinyint] NOT NULL,
    [Country] [nvarchar](2) NOT NULL,
    [ImportTimestamp] [date] NOT NULL
)

new table:

CREATE TABLE [dbo].[Data_New](
    [ID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [Category] [tinyint] NOT NULL,
    [Country] [nvarchar](2) NOT NULL,
    [TextID] [int] NOT NULL)

ALTER TABLE [dbo].[Data_New]  WITH CHECK ADD FOREIGN KEY([TextID])
REFERENCES [dbo].[Dictionary] ([Id])

Copy script:

INSERT INTO Data_New
      ([Category]
      ,[Country]
      ,[TextID])
SELECT 
      [Category]
      ,[Country]
      ,[TextID]
  FROM Data_
Dmytro Aleksin
  • 95
  • 1
  • 6

2 Answers2

3

Are you sure the table should get smaller because of this column change? nvarchar(128) doesn't mean SQL Server will allocate 128 bytes (+2) for saving data, for example the string 'test'. The string 'test' will only take 6 bytes. Maybe you need to check free space in table first:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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 = 'TABLE_NAME'
    AND t.is_ms_shipped = 0'
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name
Jeffrey
  • 412
  • 2
  • 10
  • If he copies the data to a new table the space is reclaimed. So this cannot be the issue. – usr Apr 05 '16 at 17:34
  • The issue is the wasted space. It's existence is proven by his statement (hopefully true) that copying the data to a new table shrinks it. – usr Apr 05 '16 at 17:42
  • Thats clear. But still he needs to check if there is freespace within the table first – Jeffrey Apr 05 '16 at 17:53
  • 1
    I see your point now which is that the old table might have had partially empty pages. This query will not determine the space wasted on partially filled pages, though. Still, a valid point. +1 – usr Apr 05 '16 at 17:55
  • All strings that are saved are about 100-120 ch. length. So I am sure that there is wasted space there. with your query I got a result of 12 mb free out of 900. that couldn't be true. After recreating a table I am reducing the size from 900 to 200 mb. – Dmytro Aleksin Apr 06 '16 at 08:33
  • Can you give me create table statement of old tabla and the sql of recreating the table>? Please add it to you're question – Jeffrey Apr 06 '16 at 08:38
1

If you don't care about understanding the issue and just want to get rid of it then rebuilding the index is a sure way to remove any waste. This is because a index rebuild constructs the physical data structures freshly. Whatever the old index contained will not matter anymore.

There are trade-offs involved when comparing this with CLEANTABLE. If a rebuild does the job for you I'd always do that because it is such a complete solution.

When I say "index" in this answer I mean all physical structures that contain one of the columns you care about. That can be b-tree indexes or the heap the table is based on.

usr
  • 168,620
  • 35
  • 240
  • 369
  • That doesn't cover the data of the whole table? Or do you mean rebuilding the clustered index, if available? – Jeffrey Apr 05 '16 at 18:00
  • He can rebuild whatever he wants, for example each index individually. Most tables have a CI so he needs to rebuild all indexes that contain one of the columns he cares about. If it's a heap he needs to rebuild the heap which causes everything else to also be rebuilt. With this simple answer I hope that simply rebuilding everything is OK for him. If not then the answer does not apply to the OP but maybe to future visitors. – usr Apr 05 '16 at 18:03
  • Well, I am trying to understand the problem, but I have index only on one field used for search and no I am actually deleting this field and I removed current index because it's just no valid anymore. And I can't believe that I have table of 900 mb and one index is occupying 700 mb out of it . – Dmytro Aleksin Apr 06 '16 at 08:38
  • @DmytroAleksin I'#m not sure about the situation. Did you rebuild everything including the table (the clustered index or the heap)? If the space is not freed by doing that something entirely different is going on. – usr Apr 06 '16 at 08:47
  • Thank you! Actually I was not aware about the fact of heap fragmentation. ALTER TABLE ...REBUILD made this work for me, but I am not sure that it's the best solution in this case. – Dmytro Aleksin Apr 06 '16 at 09:05