How to reclaim space in columns changed from nvarchar to varchar

[wriggs]

Hi,

This is probably an easy question for someone so any help would be
appreciated.

I have changed the columns in a table that where nvarchar to the same
size of type varchar so halve the space needed for them.

I have done this a) becuase this is never going to be an international
application, b) we are running out of space and c) there are 100
million rows.

I have done this with the alter table statement which seems to work but
the space used in the database hasn't altered.

I'm presuming that the way the records are structured within the table
there is just now more space free inbetween each page???

Is there a way or re-shrinking just an individual table and free up
some of the space in there or am i missing the point somewhere?

Thanks in advance,

Ian

[Dan Guzman]

You can reorg the table by rebuilding the table's clustered index.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"wriggs" <ia***@btinternet.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Hi,

This is probably an easy question for someone so any help would be
appreciated.

I have changed the columns in a table that where nvarchar to the same
size of type varchar so halve the space needed for them.

I have done this a) becuase this is never going to be an international
application, b) we are running out of space and c) there are 100
million rows.

I have done this with the alter table statement which seems to work but
the space used in the database hasn't altered.

I'm presuming that the way the records are structured within the table
there is just now more space free inbetween each page???

Is there a way or re-shrinking just an individual table and free up
some of the space in there or am i missing the point somewhere?

Thanks in advance,

Ian

[wriggs]

The table doesn't have any clustered index. Any other ideas?
Dan Guzman wrote:

You can reorg the table by rebuilding the table's clustered index.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"wriggs" <ia***@btinternet.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Hi,

This is probably an easy question for someone so any help would be
appreciated.

I have changed the columns in a table that where nvarchar to the same size of type varchar so halve the space needed for them.

I have done this a) becuase this is never going to be an international application, b) we are running out of space and c) there are 100
million rows.

I have done this with the alter table statement which seems to work but the space used in the database hasn't altered.

I'm presuming that the way the records are structured within the table there is just now more space free inbetween each page???

Is there a way or re-shrinking just an individual table and free up
some of the space in there or am i missing the point somewhere?

Thanks in advance,

Ian

[Erland Sommarskog]

wriggs (ia***@btinternet.com) writes:

The table doesn't have any clustered index. Any other ideas?

Is that a conscious decision?

Having a clustered index on a table is usually good practice. Not the
least, because you then can run DBCC DBREINDEX to handle fragmentation.

If nothing else, you could create a clustered index on the table, and
then drop it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[wriggs]

Thanks for the advice Eland,

Not my decision, I've only just taken over the day to day running of
the box on a contract basis.

I agree totally with what you said, so I may have to try and at least
add a clustered index to the table and try what you said.

Ian,