Can we create a view without from clause

This question is asked by an interview. Can you create a view with out select …. From XXX? Answer is Yes, a view can be created without a FROM clause in SQL Server, but only when it selects constants, expressions, or system functions. Any reference to table data requires a FROM clause. Such kind of view do not use the table. So, question raise in our mind we are not using any table then why we need to created such type of view.

Such type of view created on below scenarios

Ø  Health-check views

Ø  Environment validation

Ø  Session details

Ø  Etc.

Let’s see the demo

CREATE VIEW dbo.v_SystemInfo AS SELECT     GETDATE() AS CurrentDate,     @@SPID AS SessionID,               @@SERVERNAME as ServerName,     DB_NAME() AS DatabaseName;

View created

See the result

select * from dbo.v_SystemInfo