Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Sun, 26-Oct-2014 Authors
All Time Authors
Sourav.Kayal
39750
Sheonarayan
38050
Niladri.Biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads
 Article

Global variables in Sql Server - Part1

Saratvaddilli
Posted by under Sql Server category on for Beginner level | Points: 250 | Views : 5524 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
Rating: 5 out of 5
1 vote(s)
In this article we are going to learn the global variables in sql server 2008

Introduction


In SQL Server they are two types of variables local variables and global variables .

Local variables :

local variable is defined with a declare statement and assigned an initial value within the statement batch where it is declared with a select statement

Declaring a Local Variable:

Use the following syntax to declare a local variable's name and datatype:

declare @variable_name datatype 
[, @variable_name datatype]...

The variable name must be preceded by the @ sign and conform to the rules for identifiers. The datatype can be any datatype except text, image, or sysname.

Global variables:

Global variables are SQL Server-supplied variables that have system-supplied values.The global variables are reserved for system-wide usage. You cannot explicitly create or modify global variables - SQL Server maintains them implicitly.Predefined global variables are distinguished from local variables by having two @ signs preceding their names, for example, @@error@@rowcount. Users cannot create global variables, and cannot update the value of global variables directly in a select statement.

 List of first 10 Global Variables and their Usage are included below:

 1) @@CONNECTIONS    The number of logins or attempted logins since SQL Server was last started.      
Example:
select getdate() as today , @@connections as login_attempts

2)@@CPU_BUSY The Number of milliseconds CPU has spent Working since SQL Server was last started

Example:

select @@CPU_BUSY as cputime_utilized

3)@@CURSOR_ROWS            : Number of rows currently in the last opened cursor (for the current connection)

Example:

select @@CURSOR_ROWS  AS  ROWS_CURSOR

4)@@DATEFIRST : First day of the week. NOTE: unlike what you'd expect @@DATEFIRST returns 1 for Monday, 2 for Tuesday, etc. Default is 7(Sunday).

Example:

select  @@DATEFIRST  AS  FIRSTDAY  

5)@@DBTS : The current value of TIMESTAMP for the current database.

Example:

select @@DBTS AS TIMESTAMP_db

6)@@ERROR : The error number for the last T-SQL statement executed. If this value is zero than there        were no error. 

Example:

select @@ERROR AS ERROR_NO
7)@@FETCH_STATUS : Fetch status of the last FETCH statement of the connection, executed against any cursor opened on the current connection.

Example:
select @@FETCH_STATUS 
8)@@IDENTITY Returns the last IDENTITY value inserted. If there haven't been any IDENTITY values inserted than this variable is NULL.

Example:
select @@IDENTITY AS LAST_IDENTITYVALUE

9)@@IDLE : Number of milliseconds SQL Server has been idle since it was last started


Example:
select @@IDLE AS IDLE_MILLISECONDS

10)@@IO_BUSY : The amount of time, in ticks, that SQL Server has spent doing input and output operations    since it was last started. i.e Number of milliseconds SQL Server has spent performing Input and Output (IO) operations since it was last started.


Example:

	select @@IO_BUSY AS IOBUSY_MILLISECONDS

Conclusion

These are the first 10 global variables in the sql server 2008 , in the next article we will learn the next 10 global variables in the sql server 2008

click below link to go to global variables in Sql Server 2008 Part-2

http://www.dotnetfunda.com/articles/article2017-global-variables-in-sql-server-part2.aspx

Reference

http://manuals.sybase.com/onlinebooks/group-asarc/srg1100e/sqlref/@Generic__BookTextView/72481;pt=73386


Page copy protected against web site content infringement by Copyscape
About the Author

Saratvaddilli

Full Name: vaddilli sarat
Member Level: Bronze
Member Status: Member
Member Since: 7/30/2012 3:54:23 AM
Country: India
Thanks and Regards V.SaratChand Show difficulties that how difficult you are
www.dotnetfunda.com

I am sarat vaddilli working on the cutting edge technologies like html5,css3, Jquery and MicroSoft technologies like Asp.Net,Asp.Net MVC, SQL Server.
Login to vote for this post.
Found interesting? Add this to:


Comments or Responses

Posted by: Sheonarayan on: 11/3/2012 | Points: 25

Great effort dear,

Keep it up!


Login to post response

Comment using Facebook(Author doesn't get notification)