Refreshing the cache to optimize SQL code auto-completion

ApexSQL Complete is a SQL Server Management Studio and Visual Studio add-in designed to fill in SQL fragments, speed up coding with snippets, generate outlines for SQL script structure, and complete SQL statements automatically.

Let’s take a quick look at one of its features – Quick cache refreshing – and explain how it can speed up SQL coding.

The Auto-complete feature increases coding speed by presenting lists of keywords, objects and data types that can be selected and added to a script. ApexSQL Complete caches the database catalog data in the memory, increasing the performance thereby (the application will not query the database when presenting hint list).

Therefore, the cache won’t be affected by database changes, since ApexSQL Complete does not update it automatically (it would be performance demanding and, in most cases, unneeded).

In a dynamic development environment, where database changes occur, awareness of the newly-created and/or deleted SQL objects is desirable. If the SQL Server database structure has been modified (i.e. a table has been added), ApexSQL Complete will not be immediately aware of the change. For example, if a new dbo.Org_T2 table has been created, it will not be shown in the auto-complete hint list:

CREATE TABLE [dbo].[Org_T2](
 [EmployeeId] [hierarchyid] NOT NULL,
 [LastChild] [hierarchyid] NULL,
 [EmployeeName] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
 [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Therefore, in order to use the dbo.OrgT2 table via the auto-complete hint list, the cache needs to be reloaded (using the Refresh cache command). Once the ApexSQL Complete cache has been reloaded, all newly-created objects will be available via the add-in’s hint list (all deleted objects will be removed):

Using the Refresh cache command after every change made to the SQL database will keep the in-memory cache synchronized with the SQL database catalog data.

In order to refresh the cache for the SQL database, just click the Refresh cache command from the ApexSQL Complete sub-menu: