Creating Ghost Table, A blank table with phantom records

 

SQL server pretty much depends upon the statistics it collects 

for the data objects, Statistics of a data object has major say 

in the way sql designs plans and handles execution

This gives us the loop hole in tricking the relational 

engine/Query processor into designing query plan depending 

upon the statistics which we alter.

Sql server allows updating statistics manually, in this blog we 

are going to trick relational engine into thinking a newly 

created table and its effect on execution plans.

UPDATE STATISTICS <table_name> WITH ROWCOUNT=50000, PAGECOUNT=24000;

the above query of UPDATE STATISTICS using option of 

ROWCOUNT and PAGECOUNT enables us to manually set the 

row count of table without having actual rows inserted, the 

optimizer will not just look for the rows but also the amount 

of pages that it needs to read to get the data hence the PAGECOUNT 

lets implement it to a basic table :

The statistics is as seen above is altered manually to 1000 rows but in fact the table is empty,

when you design a query , you may want to consider how the query will react to 

the increased volume of data, the method we discussed doesn't really need you to 

pump data in gbs to observe this.

Lets take a simple query on a table with almost 300 records (Product) having join to a table with 10 records (Product_category), 

Now updating the statistics of table product with 50000 records and pagecount with 24000. we can definitely see a change in execution plan for the same query

To reset the index statistics to the actual rowcount of the table, use a DBCC command:

DBCC UPDATEUSAGE
    (database_name, '<table_name>', index_name)
    WITH COUNT_ROWS;