SQL implementation is similar to the ability to automatically refresh data

Original: SQL implementation is similar to the ability to automatically refresh data

Sometimes it is necessary to refresh a table periodically in SQL, for example, to periodically query the number of rows in a table, usually by manually pressing F5 to refresh the data. But if this timed query lasted longer, 10 minutes, or half an hour, manual words would have to crash. It seems that SQL does not have the ability to automatically refresh the application, perhaps I do not know that there is such a feature, so I wrote a similar automatic refresh data script, the need to manually abort the program's operation, of course, may also write a condition, automatically end the execution of the statement.

Code:

1  Useadventureworks2008r22 GO3  4  while 1=15 BEGIN6     SELECT COUNT(*) fromHumanResources.Employee7     WAITFORDELAY'00:00:01'8 END

After execution it was found that after a long wait, SSMs did not return data and was executed. If the patience to wait, about 1-2 minutes, the data suddenly "bang" all of a sudden came out, is not the effect of their own want to achieve (write here, think of Jackie Chan Big Brother to sell x King Shampoo advertising deeply affected my-_-!! ）。 Isn't that the way it's written? Let's test it now.

Modify the code as follows:

1  while 1 = 1 2 BEGIN 3     SELECT *  from HumanResources.Employee 4     WAITFOR ' 00:00:01 ' 5 END

It worked! Why is that? In fact, SSMs is a very clever software, because the amount of data returned is very small, ssms will not be every query, on-the-fly display, but to have a certain amount of data to be displayed.
However, if you only need to query the number of rows, not all of the data, here only a slight modification, increase the return (display) of the amount of data can be achieved.

The code is as follows:

1  while 1=12 BEGIN3     SELECT COUNT(*) fromHumanResources.Employee4     PRINT REPLICATE(1,4000)5     WAITFORDELAY'00:00:01'6 END

So the question is, what is the size of the data, and every query will be displayed immediately? Change the PRINT REPLICATE (1,4000) 4000 to $ , execute the statement, and find that it shows two rows of results each time, 2000, sometimes one row, sometimes two lines , modified to 3000, a row of slightly more, modified to 4000, can be. It is concluded that when the return is greater than 4,000 characters, SSMS will be displayed instantly.

So, the new question is coming again! Each time the select query is separated by a row, is there a way to display only the latest results, or only in one table?

Then continue to modify the code as follows:

1 SETNOCOUNT on;2 3  while 1=14 BEGIN5     SELECT 'The Count at' + CONVERT(VARCHAR( -),GETDATE(), -)+ 'is ==>' + RTRIM(COUNT(*)) fromHumanResources.Employee6     PRINT REPLICATE(' ',4000)7     WAITFORDELAY'00:00:01'8 END9 Ten SETNOCOUNTOFF;

Before executing, remember to press CTRL + T, or click the SSMs menu command to display the results in text format.

The actual results are as follows:

So you can comfortably watch SSMs automatically refresh the data, if the table data changes, will also be seen immediately, may be able to meet some "special" needs (seemingly no practical effect-_-!! ）。

Well, for a detailed description of the WAITFOR statement, please refer to:https://msdn.microsoft.com/en-us/library/ms187331%28v=sql.105%29.aspx

SQL implementation is similar to the ability to automatically refresh data