Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

FORCE_LEGACY_CARDINALITY_ESTIMATION option causes formatting and in some cases loss of intellisense

   I recently upgraded from 9.x to version of sql prompt and I noticed that adding 
 OPTION ( USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION' )) causes sql prompt to not format it correctly.

 In some large sql procs, it completely stops giving intellisense and starts adding random text when trying to auto complete etc.


  • created a sample

    ALTER PROCEDURE dbo.testProc
        @endDate DATE = NULL

            IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL
                DROP TABLE #temp;
            CREATE TABLE #temp ( ReleaseDate DATE NOT NULL );

            INSERT INTO #temp ( ReleaseDate )
                        SELECT t.TradeDate FROM ( VALUES ( '1/1/2019' )) AS t ( TradeDate )

            --intellisense/autocomplete works here
            --SELECT * FROM #temp AS t;

            UPDATE ite SET ite.ReleaseDate = '1/1/2010' FROM #temp AS ite

            --intellisense/autocomplete is lost if hint in the update is used. if we comment out the hint, then it starts working here as well.
            --if you take all these statements out of a proc, then intellisense/autocomplete works.
            SELECT * FROM #temp;


  • after opening a ticket and no resolution at this time... I would like the formatting module to be open sourced so that if we can fix it, then we can compile that dll and drop it into our local machine to remove such small annoyances.

  • arjunkrishnaarjunkrishna Posts: 4 New member
    Thanks for resolving this issue in v9.4.17.9196 release.
Sign In or Register to comment.