hi Mike,
"Michael Mieney" <mmieney@buffalo.edu> ha scritto nel messaggio
news:%23zSFYy%23cEHA.2384@TK2MSFTNGP09.phx.gbl...
[quoted text, click to view] > Am using SQL 2000.
>
> Am storing data in image columns, created with NULLS allowed. Requirement
> has come across to revert (in some user-selected cases) the column back to
> no data. Have been using the presence of the NULL to check for the
absence
> of the data and this has been successful. Have determined that I can use
a
> simple UPDATE statement to set the value of the column back to NULL. Was
> worried however about the status of pages formerly allocated to the data
> prior to this subsequent UPDATE (revert to NULL).
>
> From KB article, 65963, I have:
>
> If the columns were created with "nulls allowed," no additional space is
> allocated for Text and Image columns until data is actually inserted into
> those columns. However, once a Text or Image column has had data inserted
> into it, the page space remains allocated even if the column is changed to
> NULL. The only way to free this allocated page space is to delete the row.
>
> This is said to apply to versions 6.5 or earlier.
>
> 1. Does anyone know if this has changed in any way after 6.5?
>
> 2. Can anyone suggest an alternate method of deallocating the page space
> aside from deleting the row? Is there any other technique available?
just made few tries, with 8000 chars for sake of simplicity, like
CREATE TABLE dbo.xTest (
Id INT NOT NULL ,
data TEXT NULL
)
GO
INSERT INTO dbo.xTest VALUES ( 1 , REPLICATE ('-', 8000))
INSERT INTO dbo.xTest VALUES ( 2 , REPLICATE ('X', 8000))
INSERT INTO dbo.xTest VALUES ( 3 , REPLICATE ('Y', 8000))
....
DBCC TRACEON(3604)
GO
DBCC PAGE (dbname, 1, page_no, 1)
shows the page is actually filled with '-' as expected...
executing
UPDATE dbo.xTest SET data = NULL WHERE Id =1
and
DBCC PAGE (dbname, 1, page_no, 1)
shows the page is actually unmodified, but not linked to ID=1 row, while
UPDATE dbo.xTest SET data = REPLICATE('?',8000) WHERE Id =1
reassign the page with '?' data, again as expected...
Nulling out the column and performing a new insert will overwrite that page,
assigning it to the new row.. sot it will be recycled, as SQL Server
maintains an internal B-Tree about Text data, tha will be freed but not
deallocated when NULLed
SQL Server 2000 sp3a Dev Edition
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply
