20 system variables that SQL Server should know

Source: Internet
Author: User
Tags rowcount

1. @ connections
Returns the number of connections or attempts since Microsoft SQL Server was last started.
Example: The following example shows the number of logon attempts by the current date and time.
Select getdate () as 'date and time', @ connections as 'login attempts'

2. @ cpu_busy
Returns the CPU usage time since Microsoft SQL Server was last started, in milliseconds (based on the system timer resolution ).
Example: The following example shows the SQL Server CPU activity till the current date and time.
Select @ cpu_busy as 'cpu Ms', getdate () as 'as'

3. @ datefirst
Returns the current value of the Set datefirst parameter. The set datefirst parameter specifies the specified first day of each week. 1 corresponds to Monday, 2 corresponds to Tuesday, and so on. 7 corresponds to Sunday.
Example: The following example sets the first day of a week to 5 (Friday) and assumes that the day is Saturday. The SELECT statement returns the datefirst value and the day of the week.
Set datefirst 5
Select @ datefirst as '1st Day', datepart (DW, getdate () as 'today'

4. @ io_busy
Returns the time (in milliseconds) that Microsoft SQL Server has used to perform input and output operations since its last start (based on the system timer resolution ).
Example: The following example shows the number of milliseconds that SQL Server has used to perform input/output operations since it was started.
Select @ io_busy as 'IO Ms', getdate () as 'as'

5. @ langid
Returns the local language identifier (ID) of the current language ).
Example: The following example sets the language of the current session to Italian, and then returns the Italian ID with @ langid.
Set language 'Italian'
Select @ langid as 'language id'

6. @ Language
Returns the current language name.
Example: The following example returns the language of the current session.
Select @ language as 'language name'

7. @ max_connections
Returns the maximum number of simultaneous user connections allowed on Microsoft SQL Server. The number returned does not need to be the currently configured value.
Example: The following example assumes that SQL Server has not been reconfigured for fewer user connections.
Select @ max_connections

8. @ pack_received
Returns the number of input data packets that Microsoft SQL server has read from the Network since its last startup.
Example
Select @ pack_received

9. @ pack_sent
Returns the number of output data packets that Microsoft SQL Server has written to the Network since its last startup.
Example
Select @ pack_sent

10. @ packet_errors
Returns the number of network packet errors that have occurred on the Microsoft SQL Server connection since SQL Server was last started.
Example
Select @ packet_errors

11. @ rowcount
Returns the number of rows affected by the previous statement.
Example: Execute the update statement in the following example and use @ rowcount to check whether there are any changed rows.
Update authors set au_lname = 'Jones'
Where au_id = '2017-888-7777'
If @ rowcount = 0
Print 'Warning: No rows were updated'

12. @ servername
Returns the name of the local server that runs Microsoft SQL Server.
Example
Select @ servername

13. @ servicename
Returns the registry key name that is running under Microsoft SQL Server. If the current instance is the default instance, @ servicename returns MSSQLServer; if the current instance is a named instance, this function returns the Instance name.
Example
Select @ servicename

14. @ spid
Returns the ID of the server process of the current user process ).
Example: The following example returns the process ID, logon name, and user name of the current user process.
Select @ spid as 'id', system_user as 'login name', user as 'user name'

15. @ timeticks
Returns the number of microseconds of a scale.
Example
Select @ timeticks

16. @ total_errors
Returns the number of disk read/write errors that Microsoft SQL Server has encountered since its last startup.
Example: The following example shows the number of errors that SQL Server has encountered since the current date and time.
Select @ total_errors as 'errors ', getdate () as 'as'

17. @ total_read
Returns the number of times that Microsoft SQL Server reads a disk (not a cache read) since it was last started.
Example: The following example shows the total number of disk reads and writes to the current date and time.
Select @ total_read as 'reads', @ total_write as 'writes ', getdate () as 'as'

18. @ total_write
Returns the number of times that Microsoft SQL Server has written to a disk since its last startup.
Example: The following example shows the total number of disk reads and writes to the current date and time.
Select @ total_read as 'reads', @ total_write as 'writes ', getdate () as 'as'

19. @ version
Returns the date, version, and processor type currently installed on Microsoft SQL Server.
Example: The following example returns the date, version, and processor type of the current installation.
Select @ version

20. @ identity
Returns the last inserted id value. This variable is very useful. When you insert a row of data, you can use @ identity to obtain the ID (ID column) of the row at the same time.
Example: In the following example, insert a row to a table with an ID column and use @ identity to display the id value used in the new row.
Insert into jobs (job_desc, min_lvl, max_lvl)
Values ('accountant', 12,125)
Select @ identity as 'identified'

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.