(formerly) Three doubts about the functionality provided by the SQL Server system

List of directories in this document: 1. An overview of some of the doubts provided by the SQL Server System
2, the system function call when the default substitution optional parameter use is not uniform
3. The queue Field column message_enqueue_time the UTC datetime  4, @ @Pack_Received system function tip information Error 5. Summary language
6. List of reference listsBody: 1. An overview of some of the doubts provided by the SQL Server System  Recently after working in a systematic way to learn and study the SQL Server database engine this whole block, found something that was not too much attention, I feel SQL Server optical database engine This piece has a lot to learn and research. I personally feel that SQL Server features, in addition to bugs (which can be patched by Service pack), should be trustworthy. The following is a list of three doubts about the SQL Server system-provided functionality: When the system function is called, the default replaces the optional parameter with inconsistencies, the Queue Field column message_enqueue_time the UTC time and the @ @Pack_ Received system function tip information error, the following for each of the doubts specific demonstration and explanation.
2, the system function call when the default substitution optional parameter use is not uniform  The default keyword can be used in a DDL statement to define the defaults constraint, or it can be used in place of optional parameters. Using the default instead of optional parameters, the function and null have the same effect. In the dynamic management objects provided by the SQL Server system, the optional parameters are replaced by null or the default effect, especially when the call to the dynamic management function is called. The following T-SQL code:

SELECT * from [SYS].[Dm_db_index_physical_stats](NULL,NULL,NULL,NULL,NULL);GO SELECT * from [SYS].[Dm_db_index_physical_stats](DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT);GO

The test results for the above T-SQL are as follows: other types of system functions, some of which replace null or default effects in optional parameters, have fn_trace_getinfo, fn_trace_gettable, and so on. such as the following T-SQL code:

DECLARE @nvcDefaultTraceFilePath  as NVARCHAR(4000);SET @nvcDefaultTraceFilePath =N"'; SELECT @nvcDefaultTraceFilePath = [Path] from [SYS].[traces]WHERE [ID] = 1; SELECT * from:: Fn_trace_gettable (@nvcDefaultTraceFilePath,NULL); SELECT * from:: Fn_trace_gettable (@nvcDefaultTraceFilePath,DEFAULT);GO

The test results for the above T-SQL are as follows: In other types of system functions, more often in the optional parameters instead of NULL or default in effect is not the same, there are fn_virtualfilestats and so on. The following T-SQL code:

SELECT *  from: £ fn_virtualfilestats (1NULLSELECT* from:: fn_ Virtualfilestats (1DEFAULT);

The results for these T-SQL tests are as follows: 3. The Queue Field column message_enqueue_time the UTC datetimeWhen learning and studying Service Broker, the Message_enqueue_time value from the field column in the service queue was found to be missing UTC datetime. The effect of getting event notifications for creating tables is demonstrated in the system database tempdb. The following T-SQL code:

 Use [tempdb];GO --Create a queueIF object_id(N'[dbo]. [Notifyqueue]','SQ') is NULLBEGIN    CREATEQUEUE[dbo].[Notifyqueue]     withSTATUS=  on, RETENTION= OFFENDGO --Create a serviceIF  not EXISTS(SELECT 1  from [SYS].[Services] WHERE [name] =N'Eventnotifyservice') BEGIN    CREATESERVICE[Eventnotifyservice]     onQUEUE[dbo].[Notifyqueue]([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);ENDGO --Create a routeIF  not EXISTS(SELECT 1  from [SYS].[Routes] WHERE [name] =N'Notifyroute')BEGIN    CREATEROUTE[Notifyroute]     withservice_name=N'Eventnotifyservice', ADDRESS=N'LOCAL';ENDGO --Creating event NotificationsIF  not EXISTS(SELECT 1  from [SYS].[event_notifications] WHERE [name] =N'createtablenotification')BEGIN    CREATEEVENT NOTIFICATION[createtablenotification]     on DATABASE     forcreate_table toSERVICE'Eventnotifyservice','Current Database';ENDGO

The T-SQL script that creates the table is as follows:

IF object_id(N'[dbo]. [Tablecreate]','U') is NULLBEGIN    CREATE TABLE [dbo]. Tablecreate (Col1INT, Col2VARCHAR( -), Col3 XML) on [PRIMARY]ENDGO

The T-SQL code that gets the list from the service queue that you just created is as follows:

SELECT     CAST ([message_body]  as XML)    , Message_enqueue_time    , *  from [dbo] . NotifyqueueGO

For the above-mentioned T-SQL Test, such as: The two red rectangles circled in the place, it is obvious that the date time difference is very large, because the field column in the queue Message_enqueue_time is recorded in UTC datetime, which may be confusing to display. Note: Most of the T-SQL code shown above is excerpted from the program manifest 12-3 createqueue.sql in the 12th 12.4.4 Event Notification section of the SQL Server 2012 Management Advanced Tutorial (2nd edition). 4, @ @Pack_Received system function tips have errors  When you comb through the system functions provided by the SQL Server system, you find that the @ @Pack_Received and @ @Packet_Errors have the same prompt information. You can be sure that there must be one that is wrong. A demonstration of a message such as:

  5. Summary language  Recently, the overall overview of the SQL Server database engine this piece of knowledge, it feels like to continue to study and study further. The 3 doubts about the functionality provided by the SQL Server system above were found in the in-depth process. Use NULL as the default instead of the optional parameter for system function calls, with less default. For the field column in the service queue Message_enqueue_time why save UTC DateTime is still not clear, study many SQL Server system database, sample database rarely use UTC datetime value, please also know the colleague informed, extremely grateful. Long time no update blog, the main reason or too lazy, read, write and say are essential skills, write and say that these two basic skills continue to enhance the enhancement. Tonight is the Lantern festival, but also wish you a Happy Lantern festival, family fun. The new year begins today with a journey: roll up your sleeves and refuel.
6. List of reference lists1, SQL Server 2012 Management Advanced Tutorial (2nd edition) in the 12th Chapter 12.4.4 event Notification section of the program listing 12-3 Createqueue.sql.

(formerly) Three doubts about the functionality provided by the SQL Server system