How to Create an Empty Table and Fool Optimizer to Believe It Contains Data? – Interview Question of the Week #204

Question: How to Create an Empty Table and Fool Optimizer to Believe It Contains Data?

Answer: Before I answer this question, I must acknowledge that I have never heard this question in my 20 years career before and I must ask you back, where do you come up with these kinds of questions.

In any case, I really loved your question and let us see the answer to your question. Honestly, I must accept that today, I am going to show you one of the tricks, which I use all the time during Comprehensive Database Performance Health Check.

Let us first create a table.

CREATE TABLE #T1 (col1 INT)
GO

Next, retrieve data from the table but keeping the execution plan on. Please note that you can enable your execution plan by typing shortcut key CTRL + M.

SELECT *
FROM #T1
GO

In the execution plan, pay attention to the parameters estimated number of the rows. You will see that the estimated number of the rows are just 1. Following this, update the statistics of the table with keyword ROWCOUNT.

UPDATE STATISTICS #T1
WITH ROWCOUNT = 5000
GO

Now once again, run the select statement with the following command:

SELECT *
FROM #T1
GO

Again, check the execution plan. This time you will see that the estimated number of rows are 5000 whereas we have not populated any further data in the table.

This is quite a neat trick to use when you are working with SQL Server Performance Tuning projects where you do not want to populate more data in your system but just want to create an execution plan to check the scenario pretending that your tables have a lot of data.

After the testing is completed you can reset your database statistics by running update statistics with a full scan.

UPDATE STATISTICS #T1 
WITH FULLSCAN
GO

After you have run the demonstration you can drop the test table to clean up the database.

DROP TABLE #T1
GO

Did you know this cool trick? If yes, I would love to know when and where you use it. Please leave a comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)