Suyati Technologies
  • Services
    • Salesforce Services
      • Sales Cloud
      • Service Cloud
      • Marketing Cloud
      • Einstein
      • Experience Cloud
      • Mulesoft
      • Commerce cloud
      • Finance cloud
      • CPQ
      • Consultation
      • Implementation
      • Integration
      • Custom Development
      • Salesforce DevOps
      • Support & Maintenance
      • App Development
      • Managed Services
    • IT Services
      • Content Management Services
      • Analytics
      • RPA
      • Front end Technologies
      • Microsoft Applications
      • Cloud
      • DevOps
      • Snowflake
  • Approach
    • Development Methodology
    • Engagement Model
    • Consulting
  • Intel
    • Blog
    • eBooks
    • Webinars
    • Case Studies
  • About Us
    • Management Team
    • Advisory Board
    • Our Story
    • Testimonials
  • Careers
  • Contact Us
Suyati Technologies
  • Services
    • Salesforce Services
      • Sales Cloud
      • Service Cloud
      • Marketing Cloud
      • Einstein
      • Experience Cloud
      • Mulesoft
      • Commerce cloud
      • Finance cloud
      • CPQ
      • Consultation
      • Implementation
      • Integration
      • Custom Development
      • Salesforce DevOps
      • Support & Maintenance
      • App Development
      • Managed Services
    • IT Services
      • Content Management Services
      • Analytics
      • RPA
      • Front end Technologies
      • Microsoft Applications
      • Cloud
      • DevOps
      • Snowflake
  • Approach
    • Development Methodology
    • Engagement Model
    • Consulting
  • Intel
    • Blog
    • eBooks
    • Webinars
    • Case Studies
  • About Us
    • Management Team
    • Advisory Board
    • Our Story
    • Testimonials
  • Careers
  • Contact Us
Suyati Technologies > Blog > A look at Hypothetical Indexes in SQL Server

A look at Hypothetical Indexes in SQL Server

by Ramesh Velayudhan March 6, 2013
by Ramesh Velayudhan March 6, 2013 2 comments

It is a common scenario in SQL Server that you want to create indexes on columns in tables with voluminous data to cut down the query processing times. But there is no guarantee that the index we are planning to create would be used by the Query optimizer when the query is run against the table. Also, we must keep in mind that any index needs additional resources for its maintenance like disk space, additional overheads incurred with read, updates and delete to the base table data. Additionally creating an index on a table with millions of rows could take much longer time than we think and it might not be a good idea to do it on a production server when the server is under heavy usage. So is there any way we can create an index without encountering all the issues listed above and check if it will be used the Query optimizer when a query is run? Yes, this is where Hypothetical indexes come into the picture.
When Hypothetical Indexes are created, it contains the metadata of the index and creates a statistic associated with the index, but does not create the actual index itself.
Suppose I have a table dbo.CustomerSales with the following schema:
dbo.CustomerSales
You could see that a query against this table filtering upon the OrderDate column leads to a clustered index scan on the primary key as we do not have any indexes on the OrderDate column.
image
 
Let’s create a hypothetical index on this column in the table.
CREATE INDEX IDX_OrderDate_CustomerSales ON dbo.CustomerSales(OrderDate) WITH STATISTICS_ONLY = -1;
As we can see there is an optional clause added to the routine index definition statement WITH STATISTICS_ONLY = -1. This is an undocumented feature which creates statistics for the index but the index is not considered created/used by the query optimizer unless query is run in AUTOPILOT mode.
To force a query to be run in AUTOPILOT mode, we need to make use of command DBCC AUTOPILOT along with SET AUTOPILOT ON command. Before that, let’s retrieve some index specific information that will be needed for our scenario.
image 2
 
Let’s again run the SELECT query filtering on the OrderDate column in AUTOPILOT mode. Please note that I’m setting database id, object id and index id values in the DBCC AUTOPILOT command to enable the usage of index in AUTO PILOT mode.
image 3
 
We can now see that Query analyzer shows an estimated execution plan that considers all indexes including the hypothetical indexes turned on by the DBCC AUTOPILOT command.
Since we are dealing with an undocumented piece of code here, I would advise caution against using this code in any production environment unless it is backed by Microsoft in the near future.
Hope you have learnt few things about general index usage and Hypothetical indexes by reading this article.

Hypothetical Indexes in SQL Server
2 comments
0
FacebookTwitterLinkedinTumblr
previous post
“At Suyati, there is no hierarchy for ideas.”
next post
A run down of the recent Android Flaws

You may also like

What you need to know before kick-starting cloud...

January 13, 2022

An Eye-opener into the Future Trends in Salesforce...

January 13, 2022

Seven Key IT Outsourcing Trends to Expect in...

January 13, 2022

How to Select the Right Partner for a...

January 13, 2022

On Premises vs Cloud CRM: Which is Better?

September 28, 2021

Choosing between Cloud and On-Premise Servers for your...

September 28, 2021

Broken Customer Experience? What’s the Fix?

August 19, 2020

Are Remote Proctored Exams a New Reality?

August 18, 2020

10 Exciting Features in Salesforce’s new Summer ’20...

August 17, 2020

Importance of Data Analytics in Developing Smart Cities

August 11, 2020

2 comments

Avatar
Admiral Captain October 21, 2013 - 7:08 pm

I need help with a database related problem. I am creating a disciplinary database that people need to log into to access. I have the login page, but how do I then enable it along with the other pages?

Reply
Avatar
Ramesh Velayudhan October 22, 2013 - 3:03 pm

Hi Admiral Captain,
From the way you have described, it does not seem to be related to database. If it is something related to database, please share some more information on what you are trying to accomplish here.
Ramesh

Reply

Leave a Comment Cancel Reply

Save my name, email, and website in this browser for the next time I comment.

Keep in touch

Twitter Linkedin Facebook Pinterest

Recent Posts

  • What you need to know before kick-starting cloud implementation

    January 13, 2022
  • An Eye-opener into the Future Trends in Salesforce Commerce Cloud

    January 13, 2022
  • Seven Key IT Outsourcing Trends to Expect in 2022

    January 13, 2022

Categories

  • Twitter
  • Linkedin
  • Facebook
  • Instagram
  • Services
    • Salesforce Services
      • Sales Cloud
      • Service Cloud
      • Marketing Cloud
      • Einstein
      • Experience Cloud
      • Mulesoft
      • Commerce cloud
      • Finance cloud
      • CPQ
      • Consultation
      • Implementation
      • Integration
      • Custom Development
      • Salesforce DevOps
      • Support & Maintenance
      • App Development
      • Managed Services
    • IT Services
      • Content Management Services
      • Analytics
      • RPA
      • Front end Technologies
      • Microsoft Applications
      • Cloud
      • DevOps
      • Snowflake
  • Approach
    • Development Methodology
    • Engagement Model
    • Consulting
  • Intel
    • Blog
    • eBooks
    • Webinars
    • Case Studies
  • About Us
    • Management Team
    • Advisory Board
    • Our Story
    • Testimonials
  • Careers
  • Contact Us

© 2021 Suyati Technologies


Back To Top
Suyati Technologies

Popular Posts

  • 1

    What are the Top 3 risks for implementing a CX Program?

    August 30, 2019
  • 2

    Do you need a separate CX Team at your company?

    September 2, 2019
  • 3

    How to build Employee Advocacy for your Business?

    September 3, 2019
  • 4

    What is Salesforce CRM and What Does it Do?

    February 19, 2014
  • 5

    Tips to Reduce Salesforce Pricing

    February 17, 2015
© 2021 Suyati Technologies

Read alsox

How to migrate to Drupal 8- A four step guide

September 7, 2016

Women’s Day at Suyati

April 9, 2018

Optimizing IT Infrastructure in the Publishing Domain

September 28, 2016

By continuing to use this website you agree with our use of cookies. Read More Agree