Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I hope this question is easy for someone to answer :)

I have a table that looks like this (unimportant columns hidden)

CREATE TABLE [dbo].[Log](
    [LogID] [int] IDENTITY(1,1) NOT NULL,
    --several other columns
    [Name] [nvarchar](512) NOT NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
    [LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Now I did some indexing on that table and that also involved shortening the Name-column to 256 width. But when I change this column

ALTER TABLE Log ALTER COLUMN Name NVARCHAR(256) NOT NULL
GO

The database grows by a non trivial amount. (And yes I double checked - this is the only change I make here) In total that table has 90746 entries and before altering the table SSMS said the size was 247.56MB.

But after this update the database grew to 336.13MB.

Don't know if that matters here but SELECT @@VERSION gets Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

How can this be? Can someone explain?

UPDATE: I tried DBCC SHRINKDATABASE (myyDB, 0); that led to a smaller database of 268.81MB, but that is still bigger than the original database size with a wider column, I still do not understand :)

share|improve this question
    
do you know if this change has truncated actual value for one or more row in that table? if yes that indicates that data page allocation, hence index as well modified. hence there is fragmentation and unused spaces in each page is still there which is not reclaimed yet. –  Anup Shah Oct 17 '13 at 12:58
    
No I am absolutely sure there was no value truncated, the new column width was chosen to be safely bigger than the widest value contained in that colunm –  DrCopyPaste Oct 17 '13 at 13:01
    
SELECT MAX(LEN(Name)) FROM Log returned 79 before altering the table, just to add to that –  DrCopyPaste Oct 17 '13 at 13:09
    
Well, what happened was that it created a new column and copied the data. You can reclaim the space using ALTER TABLE ... REBUILD –  Aaron Bertrand Oct 17 '13 at 13:35
1  
You can't just consider row count - you probably had to add a lot of pages, because copies of all of that data wouldn't fit on existing pages. If you want to account for every last byte, you can feel free to examine every single page using DBCC PAGE, but just multipying column size by number of rows and calculating that should be the size change is a grossly over-simplified view. –  Aaron Bertrand Oct 17 '13 at 13:46
show 2 more comments

2 Answers 2

up vote 4 down vote accepted

SQL Server will create a new column, copy the data over, and drop the old column. The table will increase in space by some factor larger than just the size of the new or old column and/or the average or max length of the data. The reason is that temporarily the copy of the data for all rows on a page can't possibly fit on the page, so many new pages will have to be generated.

It doesn't reclaim this space after dropping the column. In order to reclaim the space, you'll need to rebuild the table / clustered index.

share|improve this answer
add comment

I was curious and I tried on small test table. the Table size it self before and after change remains same so the newly occupied space has to be with some SQL server's internal/system tables.

in the below image, first result set is spaced used by TABLE and bottom TWO result set is Space used by DATABASE.

enter image description here

share|improve this answer
    
Now put a clustered index on your table. –  Aaron Bertrand Oct 17 '13 at 17:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.