Pseudo(Virtual) columns in MS SQL/My SQL/ Oracle and Mongo DB

 Pseudo columns are special types of virtual columns provided by databases to retrieve metadata or special information about a row without being part of the actual schema.

🔸 1. Oracle – Rich Pseudo Column Support

Oracle supports several pseudo columns, and they are very commonly used.

Pseudo Column	Description	Example
ROWNUM	Returns a number indicating the order in which a row is selected from a table.	SELECT * FROM EMP WHERE ROWNUM <= 5;
ROWID	Unique ID for each row in the database (physical location of the row).	SELECT ROWID, ENAME FROM EMP;
LEVEL	Used with hierarchical queries (CONNECT BY).	SELECT ENAME, LEVEL FROM EMP CONNECT BY PRIOR EMPNO = MGR;
SYSDATE	Current date and time.	SELECT SYSDATE FROM DUAL;
SYSTIMESTAMP	Returns current timestamp with time zone.	SELECT SYSTIMESTAMP FROM DUAL;
USER	Returns the name of the currently logged-in user.	SELECT USER FROM DUAL;
CURRVAL, NEXTVAL	Used with sequences to get the current or next value.	SELECT SEQ_EMP.NEXTVAL FROM DUAL;

🔸 2. MySQL – Limited or Different Behavior

MySQL doesn’t have pseudo columns in the traditional Oracle sense. But some functions or keywords act similarly:

Feature	Description	Example
LAST_INSERT_ID()	Returns the last AUTO_INCREMENT value inserted.	SELECT LAST_INSERT_ID();
ROW_NUMBER() (Window Function)	Not a pseudo column but provides similar use.	SELECT *, ROW_NUMBER() OVER (ORDER BY id) FROM employees;
UUID()	Generates a universal unique identifier (acts like a surrogate key).	SELECT UUID();
NOW()	Current date and time.	SELECT NOW();
USER()	Logged-in user info.	SELECT USER();

📝 MySQL has virtual/generated columns, but they’re explicitly defined—not implicit pseudo columns.

---

🔸 3. SQL Server (MS SQL)

SQL Server doesn't have pseudo columns like Oracle, but provides similar features via functions and system-defined metadata.

Feature	Description	Example
@@IDENTITY, SCOPE_IDENTITY()	Last inserted identity value.	SELECT SCOPE_IDENTITY();
ROW_NUMBER()	Assigns sequential number to rows (like ROWNUM).	SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNum FROM Employees;
GETDATE()	Current system date and time.	SELECT GETDATE();
SYSDATETIME()	Higher precision timestamp.	SELECT SYSDATETIME();
SYSTEM_USER	Returns the login name of the user.	SELECT SYSTEM_USER;
NEWID()	Generates a new GUID.	SELECT NEWID();

🔸 4. MongoDB – Document-Based, No True Pseudo Columns

MongoDB is a NoSQL document store, so it doesn’t have SQL-style pseudo columns. However, you can treat some default fields as pseudo columns:

Field	Description	Example
_id	Unique ObjectId for each document (like ROWID).	db.collection.find({}, { _id: 1 })
ObjectId.getTimestamp()	Timestamp from _id value.	ObjectId("5f4e8f45...").getTimestamp()
$currentDate, $toDate, $now	Aggregation operators for current date/time.	db.collection.update({}, { $currentDate: { lastModified: true } })
$$CURRENT	In aggregation, refers to current document.	Used inside $map, $filter, etc.

🔸 Note: MongoDB doesn’t support row numbers or positional references like ROWNUM, but can emulate via aggregation pipelines with $group, $sort, $limit, $skip.

---

✅ Summary Table

DB	Common Pseudo Columns / Equivalents
Oracle	ROWNUM, ROWID, LEVEL, SYSDATE, SYSTIMESTAMP, USER, NEXTVAL
MySQL	LAST_INSERT_ID(), UUID(), NOW(), USER(), ROW_NUMBER() (window)
SQL Server	@@IDENTITY, SCOPE_IDENTITY(), ROW_NUMBER(), GETDATE(), NEWID()
MongoDB	_id, ObjectId.getTimestamp(), $$CURRENT, $currentDate, $now

----------------------------------------

2. Stored precedure vs function in MS Sql

Stored Procedures and Functions are reusable blocks of SQL code — but they differ significantly in purpose, features, and usage.

Here’s a full comparison of Stored Procedures vs Functions in MS SQL:

✅ 1. Definition

Feature	Stored Procedure	Function
Purpose	Perform actions (INSERT, UPDATE, DELETE, etc.)	Return a value (scalar/table), mainly for calculations
Syntax	CREATE PROCEDURE	CREATE FUNCTION
Return Type	Optional, use OUTPUT parameters	Mandatory: must return a value

✅ 2. Syntax Example

🔹 Stored Procedure

CREATE PROCEDURE GetUserById
    @userId INT
AS
BEGIN
    SELECT * FROM users WHERE id = @userId;
END

🔹 Function

CREATE FUNCTION GetUserFullName(@userId INT)
RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @fullName VARCHAR(200);
    SELECT @fullName = first_name + ' ' + last_name FROM users WHERE id = @userId;
    RETURN @fullName;
END

---

✅ 3. Return Types

Feature	Stored Procedure	Function
Scalar Return	No (must use output parameter)	Yes (scalar or table)
Table Return	Yes (via SELECT)	Yes (via RETURNS TABLE)
Multiple Result Sets	Yes	No

✅ 4. Usage in SQL Queries

Feature	Stored Procedure	Function
Can be used in SELECT	❌ No	✅ Yes (must be deterministic)
Can be used in JOIN	❌ No	✅ Yes (table-valued function)
Called from other SQL code	EXEC GetUserById @id	SELECT dbo.GetUserFullName(@id)

✅ 5. Side Effects & DML

Feature	Stored Procedure	Function
Can modify data (DML)	✅ Yes (INSERT/UPDATE/DELETE)	❌ No (pure function)
Can use TRY...CATCH	✅ Yes	❌ No
Can use transactions	✅ Yes	❌ No

✅ 6. Parameters

Feature	Stored Procedure	Function
Input Parameters	✅ Yes	✅ Yes
Output Parameters	✅ Yes (@param OUTPUT)	❌ No (use return value only)
Default Values	✅ Yes	✅ Yes

✅ 7. Execution Context

Feature	Stored Procedure	Function
Can be executed directly	✅ Yes (via EXEC)	❌ No (must be called inside SQL)
Execution Plan caching	✅ Yes	✅ Yes

✅ 8. Performance

• Stored Procedures are generally used for action-based tasks, transaction management, and complex logic.

• Functions are typically used for calculation or returning data inside queries.

• Overuse of scalar functions inside large queries may lead to performance issues (e.g., row-by-row execution).

---

✅ Use Case Summary

Use Case	Use Stored Procedure	Use Function
Modifying data (INSERT/UPDATE)	✅ Yes	❌ No
Returning single value	❌ Use OUTPUT param	✅ Yes (scalar function)
Returning table for joins	❌ Use temp tables	✅ Yes (table-valued)
Used inside SELECT or WHERE	❌ Not allowed	✅ Yes
Error handling with TRY...CATCH	✅ Yes	❌ No

✅ Final Recommendation

Use Stored Procedure When…
- You need to perform transactions (insert/update/delete).
- You want to return multiple result sets.
- You need error handling (TRY…CATCH).
- You want procedural logic (e.g., loops, conditionals).

Use Function When…
- You want to use logic inside queries (like SELECT/WHERE).
- You want to return a calculated value or derived table.
- You need deterministic logic without side effects.