0

I'm having trouble understanding performance with queries on views. Given this two scenarios, the queries internally will be the same?

SCENARIO 1

View:

CREATE VIEW TEST.SIMPLEVIEW AS
SELECT
    TABLE1.*,
    TABLE2.*
FROM TABLE1
INNER JOIN TABLE2 ON
    TABLE1.id = TABLE2.fk_id;

Query:

SELECT * FROM SIMPLEVIEW WHERE status = 'P'; -- P in select

SCENARIO 2

View:

CREATE VIEW TEST.SIMPLEVIEW AS
SELECT
    TABLE1.*,
    TABLE2.*
FROM TABLE1
INNER JOIN TABLE2 ON
    TABLE1.id = TABLE2.fk_id
WHERE TABLE1.status = 'P'; -- P in view definition

Query:

SELECT * FROM SIMPLEVIEW;

QUESTION

The queries internally will be the same? Disregarding the fact that the condition of status would be or not permanent one.

  • Yes they should be the same. – Erik Mar 22 '16 at 14:29
0

Generally speaking a simple view is the same as a query, behind the scenes the view is expanded and passed along to the query optimizer as a normal query. Such a view is an organizational tool, not a performance enhancing one. Use these sort of views to limit what data some users can access directly (if users are accessing your DB directly instead of through Store Procedures) or to simplify referencing a commonly repeated query.

Indexed Views are a type of view for MSSQL Server implementations that can have performance implications as they possess their own unique clustered index on a subset of the underlying tables' data. This sort of view can improve performance as the clustered index is a copy of the data kept logically separate from the original tables, however it entirely depends on how the Indexed View is built. If you see references to views improving performance they may be talking about these and this option is only available on MSSQL Server to my knowledge.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.