As a SQL Server developer, I am always looking for ways to improve my productivity and efficiency when writing SQL queries. Recently, I came across an update to the SQL Complete add-in, which I use in SQL Server Management Studio (SSMS) for query development. While exploring the new features, I discovered a significant improvement in the performance of metadata queries, which has greatly accelerated my work with database objects. In this blog post, I will delve into the details of this optimization and explain how it works.

When I first opened an SQL document in SSMS after the update, I noticed a series of queries being executed by SQL Complete. These queries retrieve various metadata information about the SQL Server instance and the current database. Let’s take a closer look at some of these queries:

-- Query 1: Retrieve instance version
SELECT SERVERPROPERTY('EngineEdition'), PATINDEX('%64%', CONVERT(VARCHAR, SERVERPROPERTY('Edition')))

-- Query 2: Retrieve default schema and original login
SELECT ISNULL(NULLIF(SCHEMA_NAME(), 'guest'), 'dbo')
SELECT ORIGINAL_LOGIN()

-- Query 3: Retrieve default backup path
DECLARE @dir NVARCHAR(4000)
IF 1 = ISNULL(CAST(SERVERPROPERTY('IsLocalDB') AS BIT), 0)
    SELECT @dir=CAST(SERVERPROPERTY('instancedefaultdatapath') AS NVARCHAR(512))
ELSE
    EXEC [master].dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 'BackupDirectory', @dir OUTPUT, 'no_output'
SELECT @dir

-- Query 4: Retrieve available databases
SELECT [dbid], name, CASE WHEN [dbid] >= 1 AND [dbid] <= 4 THEN 1 ELSE 0 END AS is_fixed_database, CASE WHEN [status] & 32768 != 0 THEN 0 ELSE CONVERT(BIT, [status] & (32 | 64 | 128 | 256 | 512)) END AS is_unavailable FROM [master].dbo.sysdatabases WITH(NOLOCK) ORDER BY name;

-- Query 5: Retrieve system languages
SELECT alias FROM [master].sys.syslanguages WITH(NOLOCK) ORDER BY alias;

-- Query 6: Retrieve system collations
SELECT name FROM ::fn_helpcollations() ORDER BY name;

-- Query 7: Retrieve linked servers
SELECT srvname AS name FROM [master].dbo.sysservers WHERE srvid != 0 ORDER BY srvid;

-- Query 8: Retrieve server triggers
SELECT t.name, t.[object_id], CASE t.[type] WHEN 'TR' THEN 1 ELSE 0 END AS is_sql_trigger, CASE WHEN ssm.[definition] IS NULL THEN 1 ELSE 0 END AS is_encrypted FROM [master].sys.server_triggers t WITH(NOLOCK) LEFT JOIN [master].sys.server_sql_modules ssm WITH(NOLOCK) ON ssm.[object_id] = t.[object_id] WHERE is_ms_shipped = 0;

-- Query 9: Retrieve system objects
SELECT o.[object_id] AS id, o.name, o.[type], o.[schema_id] FROM sys.system_objects o WITH(NOLOCK) WHERE o.[type] in ('U', 'V', 'IF', 'TF', 'FT', 'FN', 'AF', 'FS', 'P', 'RF', 'PC', 'X') ORDER BY o.[object_id];

-- Query 10: Retrieve extended properties for current database
SELECT value FROM sys.extended_properties WITH(NOLOCK) WHERE class = 0 AND name = 'MS_Description';

-- Query 11: Retrieve available schemas
SELECT s.[schema_id], s.name, CASE WHEN s.[schema_id] BETWEEN 16384 AND 16399 OR s.name IN ('guest', 'INFORMATION_SCHEMA', 'sys') THEN 1 ELSE 0 END AS is_fixed_role, CASE WHEN ISNULL(NULLIF(SCHEMA_NAME(), 'guest'), 'dbo') = s.name THEN 1 ELSE 0 END AS is_default_schema, ep.value AS [description] FROM sys.schemas s WITH(NOLOCK) LEFT JOIN (SELECT value, major_id FROM sys.extended_properties WITH(NOLOCK) WHERE class = 3 AND name = 'MS_Description') ep ON s.[schema_id] = ep.major_id;

-- Query 12: Retrieve user objects
SELECT o.[object_id] AS id, o.name, o.[type], o.[schema_id], o.is_ms_shipped AS is_system, COALESCE(OBJECTPROPERTY(o.[object_id], 'IsEncrypted'), 0) AS is_encrypted, ep.value AS [description] FROM sys.objects o WITH(NOLOCK) LEFT JOIN (SELECT value, major_id, minor_id FROM sys.extended_properties WITH(NOLOCK) WHERE class = 1 AND name = 'MS_Description') ep ON o.[object_id] = ep.major_id AND ep.minor_id = 0 WHERE o.[type] in ('U', 'V', 'IF', 'TF', 'FT', 'FN', 'AF', 'FS', 'P', 'RF', 'PC', 'X') ORDER BY o.[object_id];

-- Query 13: Retrieve foreign keys between tables
SELECT fk.parent_object_id, fk.[object_id], fk.name, o.name AS referenced_table_name, SCHEMA_NAME(o.[schema_id]) AS referenced_table_schema FROM sys.foreign_keys fk WITH(NOLOCK) JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = fk.referenced_object_id WHERE o.[type] = 'U' ORDER BY fk.parent_object_id, fk.[object_id];

-- Query 14: Retrieve columns for foreign keys
SELECT fc.parent_object_id as owner_object_id, fc.constraint_object_id AS constraint_id, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS column_name, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name FROM sys.foreign_key_columns AS fc WITH (NOLOCK) ORDER BY fc.parent_object_id, fc.constraint_object_id, fc.constraint_column_id;

-- Query 15: Retrieve user types
SELECT t.name, t.[schema_id], t.is_assembly_type AS is_clr, st.name AS base_type FROM sys.types t WITH(NOLOCK) LEFT JOIN sys.types st WITH(NOLOCK) ON st.is_user_defined = 0 AND st.is_assembly_type = 0 AND st.is_table_type = 0 AND st.user_type_id = st.system_type_id AND st.system_type_id = t.system_type_id WHERE t.is_user_defined = 1 AND t.is_table_type = 0 ORDER BY t.user_type_id;

-- Query 16: Retrieve table types
SELECT tt.type_table_object_id AS id, t.name, t.[schema_id] FROM sys.types t WITH(NOLOCK) LEFT JOIN sys.table_types tt WITH(NOLOCK) ON t.user_type_id = tt.user_type_id WHERE t.is_user_defined = 1 AND t.is_table_type = 1 ORDER BY id;

-- Query 17: Retrieve database triggers
SELECT t.name, t.[object_id], o.[schema_id], CASE t.type WHEN 'TR' THEN 1 ELSE 0 END as is_sql_trigger, COALESCE(OBJECTPROPERTY(t.[object_id], 'IsEncrypted'), 0) AS is_encrypted, o.name AS parent_name FROM sys.triggers t WITH(NOLOCK) LEFT JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = t.parent_id WHERE t.is_ms_shipped = 0 AND t.parent_class = 1 ORDER BY o.[schema_id], t.[object_id];

-- Query 18: Retrieve synonyms
SELECT s.[object_id], s.name, s.[schema_id], s.base_object_name, OBJECTPROPERTYEX(s.[object_id], N'BaseType') AS base_object_type, ep.value AS [description] FROM sys.synonyms s WITH(NOLOCK) LEFT JOIN (SELECT value, major_id, minor_id FROM sys.extended_properties WITH(NOLOCK) WHERE class = 1 AND name = 'MS_Description') ep ON s.[object_id] = ep.major_id AND ep.minor_id = 0;

-- Query 19: Retrieve XML collections
SELECT c.name AS name, c.[schema_id] FROM sys.xml_schema_collections c WITH(NOLOCK) WHERE c.[schema_id] <> 4 ORDER BY c.xml_collection_id;

What I found interesting is that when I switched to another database, only a subset of these queries (Queries 10-19) were executed. This optimization is based on the fact that the names of system objects do not change between databases. However, the data returned by these objects may vary. By retrieving the names of system objects only once, SQL Complete reduces the overhead of executing these queries repeatedly for each database switch.

Another optimization I discovered is related to a setting called “Detect changes on a server…”. When this option is enabled, SQL Complete executes a query to determine the types of objects that have changed since the last metadata update. This allows SQL Complete to fetch changes only for the modified object types, improving the performance of updating custom objects.

Furthermore, I noticed that SQL Complete loads additional information about selected objects in real-time. For example, when I select a table, I immediately get information about its columns and indexes. This optimization eliminates the need to wait for the information to be fetched separately, resulting in a smoother and more efficient coding experience.

Overall, these optimizations in SQL Complete have significantly improved my productivity and made my SQL query development process much faster. I no longer have to wait for queries to execute or manually retrieve metadata information. With SQL Complete, I can focus on writing queries and let the add-in handle the heavy lifting.

In future blog posts, I plan to explore the inner workings of other SSMS add-ins to gain a deeper understanding of how they enhance my SQL Server development experience. Stay tuned for more insights!