I altered the type of a column in to an actual smaller type:
-- change type from nvarchar(100) to varchar(50)
Alter Table [MyTable] Alter column [MyColumn] varchar(50)
The table contains 4 Mio. records. Before it used around 1.1 GB of space, after the altering of the column, it uses 2.2 GB space. Is there any explanation for that?
I find the space it uses either by SMO or by looking at the table properties in the sql server management studio: "MyTable -> Properties -> Storage -> Data space". Now I doubt that this is the actual space used by the data.
Run ALTER INDEX ALL On MyTable REBUILD and then check space again with the "true" flag
Basically, the change in type has fragmented storage somehow: for example it is now NULLable and it wasn't before which affects the on-disk structures. I chose this example because you have not specified NULL/NOT NULL in your ALTER TABLE