Home .NET Database and DBMS research using T-SQL

Database and DBMS research using T-SQL

by admin

Preface

Greetings again, dear Habra reader!
When you put your realized ideas, experience, as well as all that information, which does not give rest, into a publication, sooner or later comes the logical point to all the previously written flow of information. This article will be different from all my previously published ones in its laxity and more free style of writing, and it will complete the presentation of all my accumulated experience with MS SQL Server.
This article is a supplement to the article Exploring Databases with T-SQL , and also talks briefly about the SRV administration database created and the project utilities that are designed to help MS SQL Server DBAs.

Some useful insights for researching databases and DBMSs in general

To determine the size of embedded tables, you can create the following [inf].[vInnerTableSize] view:
Implementation of the [inf].[vInnerTableSize] view

USE [DB_NAME]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [inf].[vInnerTableSize] as--dimensions of built-in tables select object_name(p.[object_id]) as [Name] , SUM(a.[total_pages]) as TotalPages , SUM(p.[rows]) as CountRows , cast(SUM(a.[total_pages]) * 8192/1024. as decimal(18, 2)) as TotalSizeKBfrom sys.partitions as pinner join sys.allocation_units as a on p.[partition_id]=a.[container_id]left outer join sys.internal_tables as it on p.[object_id]=it.[object_id]where OBJECTPROPERTY(p.[object_id], N'IsUserTable')=0group by object_name(p.[object_id])--order by p.[rows] desc;GO

With this view, you can control the growth of system tables to avoid excessive growth.
With system views [sys].[sql_logins] and [sys].[syslogins] you can get logins for boring and wind logins.
Also of interest are the following system views for MS SQL Server instance Agent tasks:
1) [msdb].[dbo].[sysjobactivity] – active tasks
2) [msdb].[dbo].[sysjobhistory] – job history
3) [msdb].[dbo].[sysjobs_view] and [msdb].[dbo].[sysjobservers] – jobs
4) [msdb].[dbo].[sysjobschedules] – task schedules
5) [msdb].[dbo].[sysjobsteps] – job steps
6) [msdb].[dbo].[sysjobstepslogs] – job steps logging
Also, in order to know which schedules have more than one task assigned to them, it is sufficient to create the following view [inf].[vScheduleMultiJobs]:
Implementation of the [inf].[vScheduleMultiJobs] view

USE [DB for administration]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE view [inf].[vScheduleMultiJobs] aswith sh as(SELECT schedule_idFROM [inf].[vJobSchedules]group by schedule_idhaving count(*)> 1)select *from msdb.dbo.sysschedules as swhere exists(select top(1)1 from sh where sh.schedule_id=s.schedule_id)GO

This view will avoid making a rash change in the schedule for one task so as not to cause a change for another.
To get information about the description of the database objects, you can use the advanced properties (system view [sys].[extended_properties] ).The following views can be created for convenience :
1) [inf].[vObjectDescription]:
Implementation of the [inf].[vObjectDescription] view

USE [DB_NAME] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE view [inf].[vObjectDescription] asselectSCHEMA_NAME(obj.[schema_id]) as SchemaName, QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(obj.[name]) as ObjectName, obj.[type] as [Type], obj.[type_desc] as [TypeDesc], ep.[value] as ObjectDescriptionfrom sys.objects as objleft outer join sys.extended_properties as ep on obj.[object_id]=ep.[major_id]and ep.[minor_id]=0and ep.[name]='MS_Description'where obj.[is_ms_shipped]=0and obj.[parent_object_id]=0GO

2)Descriptions for objects that have parents – using the [inf].[vObjectInParentDescription] view:
Implementation of the [inf].[vObjectInParentDescription] view

USE [DB_NAME] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE view [inf].[vObjectInParentDescription] asselectSCHEMA_NAME(obj.[schema_id]) as SchemaName, QUOTENAME(object_schema_name(obj.[parent_object_id]))+'.'+quotename(object_name(obj.[parent_object_id])) as ParentObjectName, QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(obj.[name]) as ObjectName, obj.[type] as [Type], obj.[type_desc] as [TypeDesc], ep.[value] as ObjectDescriptionfrom sys.all_objects as objleft outer join sys.extended_properties as ep on obj.[parent_object_id]=ep.[major_id]and ep.[minor_id]=obj.[object_id]and ep.[name]='MS_Description'where obj.[is_ms_shipped]=0and obj.[parent_object_id]<> 0GO

3) Descriptions for parameters – using the [inf].[vParameterDescription] view:
Implementation of the [inf].[vParameterDescription] view

USE [DB_NAME] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE view [inf].[vParameterDescription] asselectSCHEMA_NAME(obj.[schema_id]) as SchemaName, QUOTENAME(object_schema_name(obj.[object_id]))+'.'+quotename(object_name(obj.[object_id])) as ParentObjectName, p.[name] as ParameterName, obj.[type] as [Type], obj.[type_desc] as [TypeDesc], ep.[value] as ParameterDescriptionfrom sys.parameters as pinner join sys.objects as obj on p.[object_id]=obj.[object_id]left outer join sys.extended_properties as ep on obj.[object_id]=ep.[major_id]and ep.[minor_id]=p.[parameter_id]and ep.[name]='MS_Description'where obj.[is_ms_shipped]=0GO

4) Table column descriptions – using the [inf].[vColumnTableDescription] view:
Implementation of the [inf].[vColumnTableDescription] view

USE [DB_NAME] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [inf].[vColumnTableDescription] asselectSCHEMA_NAME(t.schema_id) as SchemaName, QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as TableName, c.[name] as ColumnName, ep.[value] as ColumnDescriptionfrom sys.tables as tinner join sys.columns as c on c.[object_id]=t.[object_id]left outer join sys.extended_properties as ep on t.[object_id]=ep.[major_id]and ep.[minor_id]=c.[column_id]and ep.[name]='MS_Description'where t.[is_ms_shipped]=0;GO

5) View column descriptions – using the [inf].[vColumnViewDescription] view:
Implementation of the [inf].[vColumnViewDescription] view

USE [DB_NAME] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [inf].[vColumnViewDescription] asselectSCHEMA_NAME(t.schema_id) as SchemaName, QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as TableName, c.[name] as ColumnName, ep.[value] as ColumnDescriptionfrom sys.views as tinner join sys.columns as c on c.[object_id]=t.[object_id]left outer join sys.extended_properties as ep on t.[object_id]=ep.[major_id]and ep.[minor_id]=c.[column_id]and ep.[name]='MS_Description'where t.[is_ms_shipped]=0;GO

6) Descriptions of database schemas – using the [inf].[vSchemaDescription] view:
Implementation of the [inf].[vSchemaDescription]

USE [DB_NAME] GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [inf].[vSchemaDescription] asselectSCHEMA_NAME(t.schema_id) as SchemaName--, QUOTENAME(object_schema_name(t.[object_id]))+'.'+quotename(t.[name]) as TableName, ep.[value] as SchemaDescriptionfrom sys.schemas as tleft outer join sys.extended_properties as ep on t.[schema_id]=ep.[major_id]and ep.[minor_id]=0and ep.[name]='MS_Description'GO

To add or edit advanced properties to document database objects, it is best to use third-party utilities (for example, I use dbForge ).
However, it can also be done by the following queries :
Examples of creating descriptions for database objects

--here we create description for @ObjectID parameter of dbo.GetPlansObject function--also for stored procedure parametersEXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Object ID', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'FUNCTION', @level1name = N'GetPlansObject', @level2type = N'PARAMETER', @level2name = N'@ObjectID';--here we create a description for dbo.GetPlansObject--also for stored procedures, triggersEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Returns all plans of a given object', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'GetPlansObject';--here we create a description for the inf.vColumnTableDescription view--This is done in the same way for the tablesEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Description of table columns', @level0type=N'SCHEMA', @level0name=N'inf', @level1type=N'VIEW', @level1name=N'vColumnTableDescription';--here we create a description for column TEST_GUID of table dbo.TABLE--also for the view columnEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Record ID (global)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TEST', @level2type=N'COLUMN', @level2name=N'TEST_GUID';--here we create a description for the rep schemaEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'rep schema objects contain information for reports'. , @level0type=N'SCHEMA', @level0name=N'rep';--this is where the description for the database is createdEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database for administrationVersion for MS SQL Server 2016-2017 (MS SQL Server 2012-2014 is also fully or partially supported).Support for all versions prior to MS SQL Server 2012 may not be at a sufficient level for use in a production environment.See HP inf.InfoAgentJobs.' for the required typical jobs;

In order to change or delete a description, it is sufficient to use stored procedures sp_updateextendedproperty and sp_dropextendedproperty respectively.
The following system views will also be useful as part of a study of the entire SDS :
1) [sys].[dm_os_performance_counters] – performance_counters
2) [sys].[dm_os_schedulers] – task schedulers
3) [sys].[configurations] – configuration information
4) To map session IDs to Windows thread IDs, you can create the following [inf].[vSessionThreadOS] view:
Implementation of the [inf].[vSessionThreadOS] view

USE [DB for administration]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate view [inf].[vSessionThreadOS] as/*The view returns information linking the session identifier to the Windows thread identifier.The performance of the thread can be monitored in the Windows system monitor.The query does not return session IDs that are currently idle.*/SELECT .STasks.session_id, SThreads.os_thread_idFROM sys.dm_os_tasks AS STasksINNER JOIN sys.dm_os_threads AS SThreadsON STasks.worker_address = SThreads.worker_addressWHERE STasks.session_id IS NOT NULL;GO

5) to find out about problems with the number of tempdb database files, you can create the following view [inf].[vServerProblemInCountFilesTempDB]:
Implementation of the [inf].[vServerProblemInCountFilesTempDB] view

USE [DB for administration]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate view [inf].[vServerProblemInCountFilesTempDB]as/*http://sqlcom.ru/dba-tools/tempdb-in-sql-server-2016/You can find out if there is a problem with the number of tempdb files.With this query try to find latch to system pages PFS, GAM, SGAM in tempdb database.If query returns nothing or only returns lines with "Is Not PFS, GAM, or SGAM page", then most likely current load does not require increasing tempdb files*/Select session_id, wait_type, wait_duration_ms, blocking_session_id, resource_description, ResourceType = CaseWhen Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'.When Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'Else 'Is Not PFS, GAM, or SGAM page'EndFrom sys.dm_os_waiting_tasksWhere wait_type Like 'PAGE%LATCH_%'And resource_description Like '2:%'GO

6) to find out about problems with tempdb database write time, you can create the following view [srv].[vStatisticsIOInTempDB]:
Implementation of the [srv].[vStatisticsIOInTempDB] view

USE [DB for administration]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate view [srv].[vStatisticsIOInTempDB] as/*If the data write time (avg_write_stall_ms) is less than 5 ms, it means a good level of performance.Between 5 and 10 ms is an acceptable level.Over 10 ms is low performance, you should do a detailed analysis, there are I/O problems for the temporary databasehttps://minyurov.com/2016/07/24/mssql-tempdb-opt/*/SELECT files.physical_name, files.name, stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms, stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_msFROM sys.dm_io_virtual_file_stats(2, NULL) as statsINNER JOIN master.sys.master_files AS filesON stats.database_id = files.database_idAND stats.file_id = files.file_idWHERE files.type_desc = 'ROWS'GO

7) To easily display information about the last backups taken of all databases, you can create the following view [inf].[vServerLastBackupDB]:
Implementation of the [inf].[vServerLastBackupDB] view

USE [DB for administration]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [inf].[vServerLastBackupDB] aswith backup_cte as(selectbs.[database_name], backup_type =case bs.[type]when 'D' then 'database'when 'L' then 'log'when 'I' then 'differential'else 'other'end, bs.[first_lsn], bs.[last_lsn], bs.[backup_start_date], bs.[backup_finish_date], cast(bs.[backup_size] as decimal(18, 3))/1024/1024 as BackupSizeMb, rownum =row_number() over(partition by bs.[database_name], typeorder by bs.[backup_finish_date] desc), LogicalDeviceName = bmf.[logical_device_name], PhysicalDeviceName = bmf.[physical_device_name], bs.[server_name], bs.[user_name]FROM msdb.dbo.backupset bsINNER JOIN msdb.dbo.backupmediafamily bmfON [bs].[media_set_id] = [bmf].[media_set_id])select[server_name] as [ServerName], [database_name] as [DBName], [user_name] as [USerName], [backup_type] as [BackupType], [backup_start_date] as [BackupStartDate], [backup_finish_date] as [BackupFinishDate], [BackupSizeMb], --size without compression[LogicalDeviceName], [PhysicalDeviceName], [first_lsn] as [FirstLSN], [last_lsn] as [LastLSN]from backup_ctewhere rownum = 1;GO

8) A similar view [inf].[vServerBackupDB] can be created to get information about all backups :
Implementation of the [inf].[vServerBackupDB] view

USE [DB for administration]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [inf].[vServerBackupDB] aswith backup_cte as(selectbs.[database_name], backup_type =case bs.[type]when 'D' then 'database'when 'L' then 'log'when 'I' then 'differential'else 'other'end, bs.[first_lsn], bs.[last_lsn], bs.[backup_start_date], bs.[backup_finish_date], cast(bs.[backup_size] as decimal(18, 3))/1024/1024 as BackupSizeMb, LogicalDeviceName = bmf.[logical_device_name], PhysicalDeviceName = bmf.[physical_device_name], bs.[server_name], bs.[user_name]FROM msdb.dbo.backupset bsINNER JOIN msdb.dbo.backupmediafamily bmfON [bs].[media_set_id] = [bmf].[media_set_id])select[server_name] as [ServerName], [database_name] as [DBName], [user_name] as [USerName], [backup_type] as [BackupType], [backup_start_date] as [BackupStartDate], [backup_finish_date] as [BackupFinishDate], [BackupSizeMb], --size without compression[LogicalDeviceName], [PhysicalDeviceName], [first_lsn] as [FirstLSN], [last_lsn] as [LastLSN]from backup_cte;GO

9) you can also improve the presentation by expectation statistics (from the article SQL Server expectation statistics or please tell me where it hurts ) to remove the output duplicate lines as a [inf].[vWaits] view:
Implementation of the [inf][vWaits] view

USE [DB for administration]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE view [inf].[vWaits] asWITH [Waits] AS(SELECT[wait_type], -wait_type name[wait_time_ms] / 1000.0 AS [WaitS], -Total wait time of this type in milliseconds. This time includes signal_wait_time_ms([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], --Total wait time of this type in milliseconds without signal_wait_time_ms[signal_wait_time_ms] / 1000.0 AS [SignalS], -Difference between the waiting thread's signaling time and its execution start time[waiting_tasks_count] AS [WaitCount], --The number of waits of a given type. This counter is incremented each time waiting starts100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]FROM sys.dm_os_wait_statsWHERE [wait_type] NOT IN (N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')), ress as (SELECT[W1].[wait_type] AS [WaitType], CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S], --The total wait time of a given type in milliseconds. This time includes signal_wait_time_msCAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S], --Total wait time of this type in milliseconds without signal_wait_time_msCAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S], -The difference between the signaling time of the waiting thread and its execution start time[W1].[WaitCount] AS [WaitCount], -The number of waits of the given type. This counter is incremented each time the wait startsCAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage], CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S], CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S], CAST ((([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]FROM [Waits] AS [W1]INNER JOIN [Waits] AS [W2]ON [W2].[RowNum] <= [W1].[RowNum]GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS], [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]HAVING SUM ([W2].[Percentage] - [W1].[Percentage] < 95 -- percentage threshold)SELECT [WaitType], MAX([Wait_S]) as [Wait_S], MAX([Resource_S]) as [Resource_S], MAX([Signal_S]) as [Signal_S], MAX([WaitCount]) as [WaitCount], MAX([Percentage]) as [Percentage], MAX([AvgWait_S]) as [AvgWait_S], MAX([AvgRes_S]) as [AvgRes_S], MAX([AvgSig_S]) as [AvgSig_S]FROM ressgroup by [WaitType];GO

SRV database for database administration and DBMS in general

Before writing my articles, I created the database SRV, which was modified and supplemented with the experience and knowledge gained.
There were also developed other projects-tools to help the database administrator in C#.NET.
Access to projects here
At the root is the "Description" file, which briefly describes each project.
These solutions are open and distributed freely.
Also thanks to you, dear Habra readers, who left feedback in the form of comments and messages, we managed to improve the SRV database project. And thank you very much for that!
But it is important to note, that the existing approaches and solutions described in external sources, should be carefully analyzed, because for your problem these methods may not be suitable. It is necessary to pay special attention to the difference of parameters and conditions of your task from the task, which is solved in the example: the load, the amount of information processed, the frequency, the specificity of the business task, etc. For example, a procedure that runs for 40 minutes is suitable for a call once a day, but if the process needs to run with a higher frequency, this solution may not be suitable.
Once you’ve found your unique approach to a particular problem, don’t forget to share it! This way you will add to the "global knowledge base" that makes it easier to find solutions and ideas for new tasks.

Results

Some more useful MS SQL Server system views were discussed, including those for self-documenting in the form of advanced properties.

Reflections and ideas

As you have already noticed, MS SQL Server already supports NoSQL at a sufficient level in the form of graph tables (with MS SQL Server 2017 ) and document-oriented data (XML, and with MS SQL Server 2016 and JSON ).
However, as Edgar Frank Codd pointed out back in the 1970s of the 20th century (from the source [1]), it is not a simple relation that can be considered in a relational model. That is, you can both embed one table in another and inherit another from one table (recall that a table is a relation in the relational model). Table inheritance is implemented in some DBMSs, e.g. PostgreSQL But I haven’t seen an implementation of attachments. If you implement both nesting and inheritance and lay down a mechanism for handling these complex relationships, you get a DBMS that generalizes JSON and XML formats and completely covers the so-called NoSQL technology (analog of operator overloading in programming languages, but in DBMS-index, aggregation, statistics, maintenance, etc. for such relationships). Moreover, it will probably cover sufficiently all other data models, although it will be handled by a declarative SQL query language with some specific extensions and definitions for complex relationships.
Seeing how fast MS SQL Server is developing, we should hope that someday it will come to implement complex relationships and cover all their varieties. And then the wishes and foresight of the creator of relational algebra will be brought to life, and in the relational model, specialists will discover completely different aspects of creating various information databases and data warehouses.

Sources :

" "Highly loaded applications. Programming, Scaling, Support, " St. Petersburg: Peter, 2018 Kleppman M. [1]
" SQL Server expectation statistics or please tell me where it hurts
" Exploring Databases with T-SQL
" SQL documentation
" Improvements to tempdb in SQL Server 2016
" Optimizing a temporary database (tempdb)
" T-SQL design standard
" Utilities for MS SQL Server DBA
» dbForge
" PostgreSQL (inheritance)
" MS SQL Server 2017 (graphs)
» JSON in MS SQL Server 2016-2017

You may also like