Declare variable in a View

• prakash dedhury

  SSC Journeyman

  Points: 75

  More actions

  January 4, 2006 at 7:31 am

  #103014

  Hi

  i want to create a view.

  It has a join wth 7- 8 tables. Some tables are used just to select some sort of constants values.

  Like a SP i want to get those constants values from tables to some variable and use the variables in the query of the view to reduce the complexity and the no of records to few thousand from few lakh.

  Any idea how to go about this

  thanks

  Prakash

   

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  January 4, 2006 at 8:43 am

  #612639

  What is your reason for the view in the first place? The most common reasons are to use views as a security layer and/or as containers for complex code.

  It's common to place statements like;

  SELECT t1.col1, t1.col2, t1.col3....

  FROM table1 t1

  JOIN  table2 t2

  ON    t1.PK = t2.PK

  <...more joins etc..>

  into a view, thus the complex query build doesn't have to be repeated by each other query, but a simple

  SELECT col1, col2...

  FROM   myView

  WHERE col1 = 'someValue'

  ...is enough.

  Normally the viewdefinition doesn't hold any filtering criteria, although it could do that depending on the purpose of the view. So, the answer to your question should be that you control your resultset by applying the appropriate WHERE clause each time you query your view.

  /Kenneth

• prakash dedhury

  SSC Journeyman

  Points: 75

  More actions

  January 5, 2006 at 8:39 am

  #612905

  Its only for  view performance.

   

  But what i understand from your respone

  no matter how complex the query is or how much rows the view has. it does not affect the view performance much. is it?

   

  Thanks

  Prakash

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  January 9, 2006 at 2:23 am

  #613477

  Well, not necessarily. It depends.

  Given enough complexity and/or large enough tables a view may yield less performance than a 'pure statement'. When joining between views this risk can also become greater.

  There is no right or wrong answer here, it all depends on the particulars. You have to test your specific view under your circumstances to decide if performance is good enough for it's purpose.

  There's also some info in BOL on the subject if you search for 'materialized views'

  /Kenneth