If you’ve been learning SQL and came across the symbols @ and @@, you might have wondered:
1. What is @ and @@ in SQL?
2. Are they the same?
3. Which is better: @ or @@ in SQL?
Let’s clear up all the confusion in a simple, teacher-style explanation — with easy examples you can try right now.
Introduction: Why @ and @@ Matter in SQL
In SQL Server (Microsoft T-SQL), both @ and @@ are used to refer to variables or system-level values.
However, they serve very different purposes:
@→ Used for user-defined variables.@@→ Used for built-in system functions or global variables.
Understanding the difference between @ and @@ in SQL is essential for writing clean, bug-free queries and passing SQL interviews with confidence.
What is @ in SQL?
The @ symbol is used to declare and use local variables within your SQL scripts, stored procedures, or functions.
Example:
DECLARE @StudentName NVARCHAR(50);
SET @StudentName = 'Alex';
SELECT @StudentName AS Student;
✅ Explanation:
DECLAREcreates a variable named@StudentName.SETassigns a value to it.SELECTdisplays its current value.
Use @ when you need to store temporary data like counters, IDs, or calculations inside your SQL script.
What is @@ in SQL?
The @@ symbol is used for system functions (global variables) that return information about the SQL Server environment, configuration, or current session.
These variables are read-only — you can’t change their values.
Example:
SELECT @@VERSION AS SQLServerVersion;
✅ Explanation:
@@VERSIONreturns the SQL Server build and version details.
Other popular @@ variables include:
| System Variable | Description |
|---|---|
@@ERROR | Returns the error number for the last executed statement. |
@@ROWCOUNT | Returns the number of rows affected by the last command. |
@@IDENTITY | Returns the last generated identity value. |
@@SERVERNAME | Returns the name of the current server. |
Difference Between @ and @@ in SQL
| Feature | @ | @@ |
|---|---|---|
| Type | User-defined variable | System-defined global variable |
| Can declare? | Yes (DECLARE @VarName) | No (predefined by SQL Server) |
| Can modify? | Yes | No |
| Scope | Local (procedure, batch, or function) | Global (SQL Server session or system) |
| Example | @Name, @Age, @Total | @@ROWCOUNT, @@ERROR, @@VERSION |
Quick Tip:
Use @ for your own variables and @@ to access SQL Server’s internal values.
Which is Better: @ or @@ in SQL?
Neither is “better” — they serve different purposes.
- Use @ when you want to store and manipulate your own data temporarily.
- Use @@ when you want SQL Server to give you information (like system stats or environment values).
Example combining both:
DECLARE @RowsInserted INT;
INSERT INTO Students (Name) VALUES ('Liam');
SET @RowsInserted = @@ROWCOUNT;
SELECT @RowsInserted AS 'Number of Rows Inserted';
✅ Here:
@@ROWCOUNTgives the number of rows affected.- The result is stored in
@RowsInserted, your user variable.
@ and @@ in SQL Examples (Quick Recap)
| Task | Example | Explanation |
|---|---|---|
| Declare a variable | DECLARE @Count INT | Creates a local variable |
| Set a variable | SET @Count = 5 | Assigns value to variable |
| Get SQL Server version | SELECT @@VERSION | Returns SQL Server version |
| Get affected rows | SELECT @@ROWCOUNT | Number of rows changed by last query |
Summary
Here’s what you should remember:
@→ For your own variables (local).@@→ For SQL Server’s built-in variables (system/global).- You declare and set
@variables. - You read
@@variables; you can’t change them. - Both are essential for writing professional SQL scripts and debugging queries efficiently.
SQL Interview Questions on @ and @@
- Question 1:
What is the difference between@and@@in SQL Server? Give one example of each. - Question 2:
Write a SQL query that inserts a record into a table, captures the number of affected rows using@@ROWCOUNT, and displays it using a variable.
Final Note
If you’re preparing for SQL interviews or writing stored procedures, understanding the @ vs @@ difference in SQL is a small detail that makes a big difference.
It shows you understand both the user logic and the system internals — a skill every good SQL developer needs!
