Is there a High water Mark in sql server

[Vincento Harris]

If you delete rows in a table and do a full table scan...
Is that supposed to read up to the highest block/extent that the
table ever attended.(like in some databases I use)
If so what is the best way to take care of such tables in sql server.

I appreciate your responses

Vince

[Erland Sommarskog]

Re: Is there a High water Mark in sql server

Vincento Harris (wumutek@yahoo. com) writes:[color=blue]
> If you delete rows in a table and do a full table scan...
> Is that supposed to read up to the highest block/extent that the
> table ever attended.(like in some databases I use)
> If so what is the best way to take care of such tables in sql server.[/color]

I'm afraid that I don't understand the question. Could you exemplify what
you are looking for?


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Vincento Harris]

Re: Is there a High water Mark in sql server

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns940185 65C8FYazorman@1 27.0.0.1>...[color=blue]
> Vincento Harris (wumutek@yahoo. com) writes:[color=green]
> > If you delete rows in a table and do a full table scan...
> > Is that supposed to read up to the highest block/extent that the
> > table ever attended.(like in some databases I use)
> > If so what is the best way to take care of such tables in sql server.[/color]
>
> I'm afraid that I don't understand the question. Could you exemplify what
> you are looking for?[/color]

Sorry
I hope this is in some clarifies the earlier post.


Example

My table contains 10000 rows

If a query is run that requires a full table scan all the blocks with
data are visited.

Now

9000 rows are deleted

Next Step
Run a query that requires a full table scan and all the blocks that
ever contained data are visited (Not only the one thousand now
present)


Remedy

If the table is exported ,truncated,impo rted
the highest point that ever contained data is readjusted at least in
Oracle

Does this work the same in sql server?....

Will appreciate your responses


Vince

[Erland Sommarskog]

Re: Is there a High water Mark in sql server

Vincento Harris (wumutek@yahoo. com) writes:[color=blue]
> Example
>
> My table contains 10000 rows
>
> If a query is run that requires a full table scan all the blocks with
> data are visited.
>
> Now
>
> 9000 rows are deleted
>
> Next Step
> Run a query that requires a full table scan and all the blocks that
> ever contained data are visited (Not only the one thousand now
> present)
>
>
> Remedy
>
> If the table is exported ,truncated,impo rted
> the highest point that ever contained data is readjusted at least in
> Oracle
>
> Does this work the same in sql server?....[/color]

It sounds like what you are looking for is DBCC DBREINDEX. This commands
rebuilds all or the selected indexes for a table. Note that if you
have a clustered index on a table, the data pages are the leaf level
of that index, so DBREINDEX also caters for these. For a table that
does not have a clustered index, there is no command as far as I know
that takes care of the data pages. But it is recommendable to always
have a clustered index on a table.

An alternative is DBCC INDEXDEFRAG which can be run without locking out
other users.

Both commands are described in Books Online.

The reason that your question confused me, is that you used Oracle
terminology. In SQL Server you never talk about high-water marks,
for instance.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Mr. DB]

Re: Is there a High water Mark in sql server

> The reason that your question confused me, is that you used Oracle[color=blue]
> terminology. In SQL Server you never talk about high-water marks,
> for instance.[/color]

Right. SQL Server has a totally different internal structure than
Oracle has. In SQL Server, a table is basically a big linked-list and
the data blocks are essentially the leaf-blocks of the cluster index.
Those blocks are then doubly-linked back and forth so you can traverse
the table in a full table scan or in an index range scan. In fact, an
index range scan of the whole table is essentially (physically) the
same as a full table scan. I'm not sure exactly why, but SQL Server
has always had trouble with corruption of these link-list pointers.
Run DBCC to check (and fix) problems with these pointers.