20 system variables that SQL Server should know

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'