#SQLSERVERLearning : Alias are mandate for inline views

New challenge added to my career life – Microsoft SQL Server.!
Now need to work on multiple database along with Oracle (My Fav) as primary.

Most of folks feel , both are traditional RDBMS database and should or need to work some what “SIMILAR”.
But, But when we actually start working on core with queries, development and tuning.We identified a lot of difference between any two database.

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.

Lets start with very simple case, INLINE View.

In Oracle we can run below SQL without any error.

SELECT DEPTNO, DNAME
FROM (SELECT * FROM DEPT);

In upper Select we are querying an inline part and project desired columns.
Without assigning any alias to Inline we can access same provided we don’t have any column conflict.

Lets try to run same example in SQL Server 2012.

SELECT DEPTNO, DNAME
FROM (SELECT * FROM DEPT);

On execution :
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘;’.

In SQL Server, we need to add alias to Inline view and use same as reference for final projections.

Not sure why it is mandate, feel it have been should have allowed.
Not because oracle support it, but some time it give ease to write less code.

SELECT d.DEPTNO, d.DNAME
FROM (SELECT * FROM DEPT) as d;

First learning :
In SQL Server alias all Inline View!

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in SQL Server Learning and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s