SQL Server惯用的全局变量

1.@@CONNECTIONS  --返回SQL server自上次以来尝试的连接数ExamplesThis example shows the number of login attempts as of the current date and time.SELECT GETDATE() AS 'Today's Date and Time',    @@CONNECTIONS AS 'Login Attempts'Here is the result set:Today's Date and Time              Login Attempts---------------------------        ---------------1998-04-09 14:28:46.940            182.@@CPU_BUSY  --返回SQL server自上次启动后的工作时间ExamplesThis example shows SQL Server CPU activity as of the current date and time.SELECT @@CPU_BUSY AS 'CPU ms', GETDATE() AS 'As of'Here is the result set:CPU ms               As of-----------------    ---------------------------20                   1998-04-18  14:43:08.180 3.@@CURSOR_ROWS--返回连接上打开的上一个游标中的当前限定行的数目，确定当它被调用时检索了游标符合条件的行数Return value   Description -m            The cursor is populated asynchronously. The value returned (-m) is the number of rows currently in the keyset. -1            The cursor is dynamic. Because dynamic cursors reflect all changes, the number of rows that qualify for the                   cursor         is constantly changing. It can never be definitely stated that all qualified rows have been                    retrieved. 0             No cursors have been opened, no rows qualified for the last opened cursor, or the last-opened cursor is closed               or deallocated. n             The cursor is fully populated. The value returned (n) is the total number of rows in the cursor. ExamplesThis example declares a cursor and uses SELECT to display the value of @@CURSOR_ROWS. The setting has a value of 0 before the cursor is opened, and a value of -1 to indicate that the cursor keyset is populated asynchronously.SELECT @@CURSOR_ROWSDECLARE authors_cursor CURSOR FORSELECT au_lname FROM authorsOPEN authors_cursorFETCH NEXT FROM authors_cursorSELECT @@CURSOR_ROWSCLOSE authors_cursorDEALLOCATE authors_cursor----------- 0           (1 row(s) affected)au_lname                                 ---------------------------------------- White                                    (1 row(s) affected)            ----------- -1          (1 row(s) affected)4.@@DATEFIRST--针对对话返回SET DATEFIRST的当前值，SET DATEFIRST表示制定每周的第一天ExamplesThis example sets the first day of the week to 5 (Friday), and assumes the current day to be Saturday. The SELECT statement returns the DATEFIRST value and the number of the current day of the week.SET DATEFIRST 5SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today'Here is the result set. Counting from Friday, today (Saturday) is day 2.1st Day           Today----------------  --------------5                 25.@@DBTS--返回当前数据库的当前timestamp数据类型的值，这一时间戳值在数据库中必须是唯一的ExamplesThis example returns the current timestamp from the pubs database.USE pubsSELECT @@DBTS6.@@ERROR--返回执行上一个transact-SQL语句的错误号，如果前一个transact-SQL语句执行没错误，返回0ExamplesA. Use @@ERROR to detect a specific errorThis example uses @@ERROR to check for a check constraint violation (error #547) in an UPDATE statement.USE pubsGOUPDATE authors SET au_id = '172 32 1176'WHERE au_id = "172-32-1176"IF @@ERROR = 547   print "A check constraint violation occurred"B. Use @@ERROR to conditionally exit a procedure The IF...ELSE statements in this example test @@ERROR after an INSERT statement in a stored procedure. The value of the @@ERROR variable determines the return code sent to the calling program, indicating success or failure of the procedure.USE pubsGO-- Create the procedure.CREATE PROCEDURE add_author @au_id varchar(11),@au_lname varchar(40),@au_fname varchar(20),@phone char(12),@address varchar(40) = NULL,@city varchar(20) = NULL,@state char(2) = NULL,@zip char(5) = NULL,@contract bit = NULLAS-- Execute the INSERT statement.INSERT INTO authors(au_id,  au_lname, au_fname, phone, address,  city, state, zip, contract) values(@au_id,@au_lname,@au_fname,@phone,@address, @city,@state,@zip,@contract)-- Test the error value.IF @@ERROR <> 0 BEGIN   -- Return 99 to the calling program to indicate failure.   PRINT "An error occurred loading the new author information"   RETURN(99)ENDELSEBEGIN   -- Return 0 to the calling program to indicate success.   PRINT "The new author information has been loaded"   RETURN(0)ENDGOC. Use @@ERROR to check the success of several statementsThis example depends on the successful operation of the INSERT and DELETE statements. Local variables are set to the value of @@ERROR after both statements and are used in a shared error-handling routine for the operation.USE pubsGODECLARE @del_error int, @ins_error int-- Start a transaction.BEGIN TRAN-- Execute the DELETE statement.DELETE authorsWHERE au_id = '409-56-7088'-- Set a variable to the error value for -- the DELETE statement.SELECT @del_error = @@ERROR-- Execute the INSERT statement.INSERT authors   VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',   '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)-- Set a variable to the error value for -- the INSERT statement.SELECT @ins_error = @@ERROR-- Test the error values.IF @del_error = 0 AND @ins_error = 0BEGIN   -- Success. Commit the transaction.   PRINT "The author information has been replaced"       COMMIT TRANENDELSEBEGIN   -- An error occurred. Indicate which operation(s) failed   -- and roll back the transaction.   IF @del_error <> 0       PRINT "An error occurred during execution of the DELETE       statement."    IF @ins_error <> 0      PRINT "An error occurred during execution of the INSERT       statement."    ROLLBACK TRANENDGOD. Use @@ERROR with @@ROWCOUNTThis example uses @@ERROR with @@ROWCOUNT to validate the operation of an UPDATE statement. The value of @@ERROR is checked for any indication of an error, and @@ROWCOUNT is used to ensure that the update was successfully applied to a row in the table.USE pubsGOCREATE PROCEDURE change_publisher@title_id tid, @new_pub_id char(4) AS-- Declare variables used in error checking.DECLARE @error_var int, @rowcount_var int-- Execute the UPDATE statement.UPDATE titles SET pub_id = @new_pub_id WHERE title_id = @title_id -- Save the @@ERROR and @@ROWCOUNT values in local -- variables before they are cleared.SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT-- Check for errors. If an invalid @new_pub_id was specified-- the UPDATE statement returns a foreign-key violation error #547.IF @error_var <> 0BEGIN   IF @error_var = 547   BEGIN      PRINT "ERROR: Invalid ID specified for new publisher"      RETURN(1)   END   ELSE   BEGIN      PRINT "ERROR: Unhandled error occurred"      RETURN(2)   ENDEND-- Check the rowcount. @rowcount_var is set to 0 -- if an invalid @title_id was specified.IF @rowcount_var = 0 BEGIN   PRINT "Warning: The title_id specified is not valid"   RETURN(1)ENDELSEBEGIN   PRINT "The book has been updated with the new publisher"   RETURN(0)ENDGO7.@@FETCH_STATUS--返回针对当前打开的任何游标发出的上一条游标FETCH语句的状态Return value       Description 0                  FETCH statement was successful. -1                 FETCH statement failed or the row was beyond the result set. -2                 Row fetched is missing. ExamplesThis example uses @@FETCH_STATUS to control cursor activities in a WHILE loop.DECLARE Employee_Cursor CURSOR FORSELECT LastName, FirstName FROM Northwind.dbo.EmployeesOPEN Employee_CursorFETCH NEXT FROM Employee_CursorWHILE @@FETCH_STATUS = 0BEGIN   FETCH NEXT FROM Employee_CursorENDCLOSE Employee_CursorDEALLOCATE Employee_Cursor8.@@IDENTITY --返回上次插入的标记值ExamplesThis example inserts a row into a table with an identity column and uses @@IDENTITY to display the identity value used in the new row.INSERT INTO jobs (job_desc,min_lvl,max_lvl)VALUES ('Accountant',12,125)SELECT @@IDENTITY AS 'Identity'9.@@IDLE  --返回SQL Server自上次启动后的空闲时间，结果以CPU时间增量或“时钟周期”表示，并且是所有CPU的累积ExamplesThis example shows the number of milliseconds SQL Server was idle between the start time and the current time.SELECT @@IDLE AS 'Idle ms', GETDATE() AS 'As of'Here is the result set:Idle Ms              As of-----------------    ---------------------------277593               1998-04-18  16:41:07.16010.@@IO_BUSY  --返回自从SQL Server最近一次启动以来，Microsoft SQL Server 已经用于执行输入和输出操作的时间。其结果是CPU时间增量（时钟周期），并且是所有CPU的积累值。ExamplesThis example shows the number of milliseconds SQL Server has spent performing input/output operations between start time and the current time.SELECT @@IO_BUSY AS 'IO ms', GETDATE() AS 'As of'Here is the result set:IO ms                As of------------------   -----------------------------31                   1998-04-18  16:49:49.65011.@@LANGID  --返回当前使用的语言的本地语言标识符（ID）ExamplesThis example sets the language for the current session to Italian, and then uses @@LANGID to return the ID for Italian.SET LANGUAGE 'Italian'SELECT @@LANGID AS 'Language ID'Here is the result set:Language ID--------------------612.@@LANGUAGE --返回当前所有语言的名称ExamplesThis example returns the language for the current session.SELECT @@LANGUAGE AS 'Language Name'Here is the result set:Language Name-----------------------------us_english13.@@LOCK_TIMEOUT  --返回当前会话的当前锁定超时设置（毫秒）ExamplesThis example shows the result set when a LOCK_TIMEOUT value is not set.SELECT @@LOCK_TIMEOUTHere is the result set:-----------------1This example sets LOCK_TIMEOUT to 1800 milliseconds, and then calls @@LOCK_TIMEOUT.SET LOCK_TIMEOUT 1800SELECT @@LOCK_TIMEOUTHere is the result set:------------------------------180014.@@MAX_CONNECTIONS --返回SQL Server实例允许同时进行的最大用户连接数，返回的数值不一定是当前配置的数值ExamplesThis example assumes that SQL Server has not been reconfigured for fewer user connections.SELECT @@MAX_CONNECTIONSHere is the result set:------------------3276715.@@MAX_PRECISION  --按照服务器当前设置，返回decimal和numeric数据类型所用的精度级别ExamplesSELECT @@MAX_PRECISION16.@@NESTLEVEL --返回本地服务器上执行的当前存储过程的嵌套级别（初始值为0）ExamplesThis example creates two procedures: one that calls the other, and one that displays the @@NESTLEVEL setting of each.CREATE PROCEDURE innerproc as select @@NESTLEVEL AS 'Inner Level'GOCREATE PROCEDURE outerproc as select @@NESTLEVEL AS 'Outer Level'EXEC innerprocGOEXECUTE outerprocGOHere is the result set:Outer Level ----------------- 1                 Inner Level ----------------- 2               17.@@OPTIONS --返回有关当前SET选项的信息ExamplesThis example sets NOCOUNT ON and then tests the value of @@OPTIONS. The NOCOUNT ON option prevents the message about the number of rows affected from being sent back to the requesting client for every statement in a session. The value of @@OPTIONS is set to 512 (0x0200), which represents the NOCOUNT option. This example tests whether the NOCOUNT option is enabled on the client. For example, it can help track performance differences on a client.SET NOCOUNT ONIF @@OPTIONS & 512 > 0    RAISERROR ('Current user has SET NOCOUNT turned on.',1,1)18.@@PACK_RECEIVED --返回SQL Server自上次启动后从网络读取的输入数据包数ExamplesSELECT @@PACK_RECEIVED19.@@PACK_SENT --返回SQL Server自上次启动后写入网络读取的输入数据包数ExamplesSELECT @@PACK_SENT20.@@PACKET_ERRORS  --返回自上次启动SQL Server后，在SQL Server连接上的网络数据报错误数ExamplesSELECT @@PACKET_ERRORS21.@@PROCID --返回Transact-SQL当前模块的对象标识符（ID），Transact-SQL模块可以是存储过程，用户定义函数或触发器ExamplesThis example creates a procedure that uses SELECT to display the @@PROCID setting from inside the procedure.CREATE PROCEDURE testprocedure ASSELECT @@PROCID AS 'ProcID'GOEXEC testprocedureGO22.@@REMSERVER  --返回远程SQL Server数据库服务器在登录记录中显示的名称ExamplesThis example creates a procedure, check_server, that returns the name of the remote server.CREATE PROCEDURE check_serverASSELECT @@REMSERVERThe stored procedure is created on SEATTLE1, the local server. The user logs on to a remote server, LONDON2, and runs check_server.exec SEATTLE1...check_serverHere is the result set:---------------LONDON223.@@ROWCOUNT  --返回受上一行影响的行数ExamplesThis example executes UPDATE and uses @@ROWCOUNT to detect if any rows were changed.UPDATE authors SET au_lname = 'Jones'WHERE au_id = '999-888-7777'IF @@ROWCOUNT = 0   print 'Warning: No rows were updated'24.@@SERVERNAME --返回运行SQL Server的本地服务器的名称Instance                                 Server information Default instance                         'servername' Named instance                           'servername\instancename' Virtual server - default instance         'virtualservername' Virtual server - named instance          'virtualservername\instancename' ExamplesSELECT @@SERVERNAME25.@@SERVICENAME  --返回SQL Server正在其下运行的注册表项的名称，若当前实例为默认实例，则@@SERVICENAME返回MSSQLSERVERExamplesSELECT @@SERVICENAMEHere is the result set:------------------------------ MSSQLServer26.@@SPID  --返回当前用户进程的会话IDExamplesThis example returns the process ID, login name, and user name for the current user process.SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'Here is the result set:ID     Login Name       User Name  -----  -------------    -----------11     sa               dbo        27.@@TEXTSIZE  --返回SET语句中的TEXTSIZE选项的当前值ExamplesThis example uses SELECT to display the @@TEXTSIZE value before and after it is changed with the SET TEXTSIZE statement.SELECT @@TEXTSIZESET TEXTSIZE 2048SELECT @@TEXTSIZEHere is the result set:------------------------64512------------------------204828.@@TIMETICKS --返回每个时钟周期的微秒数ExamplesSELECT @@TIMETICKS29.@@TOTAL_ERRORS --返回SQL Server子上启动后所遇到的磁盘写入错误数ExamplesThis example shows the number of errors encountered by SQL Server as of the current date and time.SELECT @@TOTAL_ERRORS AS 'Errors', GETDATE() AS 'As of'Here is the result set:Errors         As of                          -------        -------------------------------0              1998-04-21  22:07:30.013       30.@@TOTAL_READ  --返回SQL Server子上启动后读取磁盘（不是读取高速缓存）的次数ExamplesThis example shows the total number of disk read and writes as of the current date and time.SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As of'Here is the result set:Reads       Writes        As of---------   -----------   ------------------------------978         124           1998-04-21 22:14:22.3731.@@TOTAL_WRITE --返回SQL Server子上启动以来磁盘所执行的写入次数ExamplesThis example shows the total number of disk reads and writes as of the current date and time.SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As of'Here is the result set:Reads       Writes        As of---------   -----------   ------------------------------978         124           1998-04-21 22:14:22.37        32.@@TRANCOUNT  --返回的钱连接的活动事务数ExamplesThis example uses @@TRANCOUNT to test for open transactions that should be committed.BEGIN TRANSACTIONUPDATE authors SET au_lname = upper(au_lname)WHERE au_lname = 'White'IF @@ROWCOUNT = 2   COMMIT TRANIF @@TRANCOUNT > 0BEGIN   PRINT 'A transaction needs to be rolled back'   ROLLBACK TRANEND33.@@VERSION  --返回当前SQL Server安装的版本，处理体系结构，生成日期和操作系统ExamplesThis example returns the date, version, and processor type for the current installation.SELECT @@VERSION

1楼zh634455283昨天 23:17

[code=cpp]ncout<<"顶"<endl;n[/code]