Recently SQL Complete add-in was updated, which I use when writing SQL queries in SSMS. In the product history I saw that a several features were added, such as code refactoring or automatic semicolon insertion. However, in this post I do not want to talk about them. I have noticed a new logic for metadata queries, which significantly accelerated my work with database objects. So I decided to look inside SQL Complete… to understand how it works and what changed…. Let us learn about Advantages of Intellisense Features.
We can use the free dbForge Event Profiler for SQL Server tool to see what queries SQL Complete sends. We can apply filter to eliminate custom queries from the tracing.
When we first open an SQL document in SSMS, we get the following trace.
I wrote in comments what each of these queries return.
-- 1. instance version
SELECT SERVERPROPERTY('EngineEdition'), PATINDEX('%64%', CONVERT(VARCHAR, SERVERPROPERTY('Edition')))
GO
-- 2. default schema (dbo by default)
SELECT ISNULL(NULLIF(SCHEMA_NAME(), 'guest'), 'dbo')
GO
SELECT ORIGINAL_LOGIN()
GO
-- 3. don’t know why SQL Complete needs a 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
GO
-- 4. 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;
GO
-- 5. system languages
SELECT alias
FROM [master].sys.syslanguages WITH(NOLOCK)
ORDER BY alias;
GO
-- 6. system collations (SQL_Latin1_General_CP1_CI_AI, ....)
SELECT name
FROM ::fn_helpcollations()
ORDER BY name;
GO
-- 7. linked servers
SELECT srvname AS name
FROM [master].dbo.sysservers
WHERE srvid != 0
ORDER BY srvid;
GO
-- 8. 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;
GO
-- 9. system objects (like sys.views, sys. ...)
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];
GO
-- 10. extended properties for current database
SELECT value
FROM sys.extended_properties WITH(NOLOCK)
WHERE class = 0 AND name = 'MS_Description';
GO
-- 11. 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;
GO
-- 12. user objects (tables, views, procedures, triggers, etc)
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];
GO
-- 13. 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];
GO
-- 14. 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;
GO
-- 15. 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;
GO
-- 16. 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;
GO
-- 17. 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];
GO
-- 18. 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;
GO
-- 19. 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;
GO
Then I switched to another database and noticed that only a part of queries (10-19) has been executed. This is a way of optimization, when we retrieve names of system objects only once. You need to understand that names of system objects do not change between databases. However, the data returned by these objects is subject to change.
Another interesting optimization is hidden in the settings. If such option «Detect changes on a server…»
If you turn it on, then while updating the custom objects prompt, the following query is executed:
SELECT
[type]
, MAX(modify_date) AS max_date
, COUNT(1) AS num
FROM sys.objects WITH(NOLOCK)
WHERE [type] IN ('U', 'V', 'IF', 'TF', 'FT', 'FN', 'AF', 'FS', 'P', 'RF', 'PC', 'X', 'SN', 'TR', 'TA')
GROUP BY [type];
The optimization goal is to define what types of objects have changed since the last update of metadata and get changes only for changed types.
When profiling, I noticed another interesting optimization. When writing code, additional information about the objects is loaded when they are selected. For example, we choose a table.
And we have information about this table immediately:
-- table/view columns
SELECT
c.name
, c.[object_id] AS parent_id
, c.column_id
, c.is_rowguidcol
, c.is_identity
, c.is_nullable
, c.is_computed
, CASE WHEN c.default_object_id <> 0 THEN 1 ELSE 0 END AS default_exist
, c.[precision]
, c.scale
, CAST(
CASE WHEN t.base_type_name IN ('nchar', 'nvarchar') AND c.max_length <> -1
THEN c.max_length / 2
ELSE c.max_length
END AS INT) AS [length]
, t.name AS [type_name]
, t.base_type_name
, t.is_user_defined
, t.type_schema_name
, ep.value AS [description]
FROM sys.all_columns c WITH(NOLOCK)
LEFT JOIN (
SELECT
*
, SCHEMA_NAME([schema_id]) AS type_schema_name
, COALESCE(TYPE_NAME(system_type_id), name) as base_type_name
FROM sys.types WITH(NOLOCK)
) t ON c.user_type_id = t.user_type_id
LEFT JOIN (
SELECT value, major_id, minor_id
FROM sys.extended_properties WITH(NOLOCK)
WHERE class = 1 AND name = 'MS_Description'
) ep ON ep.major_id = c.[object_id] AND ep.minor_id = c.column_id
WHERE c.[object_id] = {0}
ORDER BY c.column_id;
GO
-- index for selected table/view
SELECT
i.[object_id]
, i.index_id
, i.name
, i.is_unique_constraint
, i.is_primary_key
, i.[type]
FROM sys.indexes i WITH(NOLOCK)
WHERE i.[object_id] = {0}
AND i.index_id > 0
AND i.is_hypothetical = 0
ORDER BY i.index_id;
GO
-- index columns for selected table/view
SELECT
ic.[object_id]
, ic.index_id
, c.name
FROM sys.index_columns ic WITH(NOLOCK)
JOIN sys.columns c WITH(NOLOCK) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE ic.[object_id] = {0}
AND ic.index_id > 0
ORDER BY
ic.index_id
, ic.index_column_id;
GO
This same approach works while prompting another database objects (e.g. stored routines, functions and synonyms).
The described optimization improves usability when working. You do not need to wait a second to write a query.
In the future, I plan to continue to look under the hood of other SSMS add-ins. It is more interesting to work, when you know how it works.
Reference : Pinal Dave (https://blog.sqlauthority.com)
