Reuse of space after truncating

Hi,

Will space used by an table be unallocated if the table is truncated or
will it only be marked as unused?

TIA,

Can you explain the difference between unallocated and unused?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"Gurba" <gurbao@hotmail.com> wrote in message
 news:Xns97976A2B37D0
 Fgurbaohotmailcom@12
9.250.171.66...
> Hi,
>
> Will space used by an table be unallocated if the table is truncated or
> will it only be marked as unused?
>
> TIA,
>
>

Hi,

well,
as I have understood space allocation in SQL Server it will allocate 1
extent when needing more space for data (not talking about mixed extents
here). If the new data is only occupying 1 page in the extent the whole
extent is counted as allocated and 7 pages as unused.

Am I far off in my understanding this issue? If so, please explain...

TIA,

"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote
in news:OuSKkGKVGHA.196@TK2MSFTNGP10.phx.gbl:

> Can you explain the difference between unallocated and unused?
>

Your understanding about pages and extent allocation seems to be correct. Wh
en you do TRUNCATE
TABLE, all the extents are deallocated for the table and its indexes. If you
 do it in a transaction,
the deallocated pages are not available for re-use until COMMIT. But essenti
ally, you end up with
the same situation as if you just had created the table (and indexes) and be
fore you insert the
first row into the table.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"Gurba" <gurbao@hotmail.com> wrote in message
 news:Xns9797852E1C29
 6gurbaohotmailcom@12
9.250.171.68...
> Hi,
>
> well,
> as I have understood space allocation in SQL Server it will allocate 1
> extent when needing more space for data (not talking about mixed extents
> here). If the new data is only occupying 1 page in the extent the whole
> extent is counted as allocated and 7 pages as unused.
>
> Am I far off in my understanding this issue? If so, please explain...
>
> TIA,
>
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote
> in news:OuSKkGKVGHA.196@TK2MSFTNGP10.phx.gbl:
> 
>