Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Posts
    26

    Unanswered: Indexes on views

    Hi,

    I would like to know if SQL Server creates automatic index on the used tables' common fields.

    For example:

    Code:
    CREATE VIEW [dbo].[ORMAN]
    AS
    SELECT     dbo.PARSEL.OBJECTID, dbo.PARSEL.ADAPARSEL, dbo.PARSEL.ORMANADI, dbo.PARSEL.MULKIYET, dbo.PARSEL.ALAN, dbo.PARSEL.YERKOD, 
                          dbo.PARSEL.UYG_KAN, dbo.PARSEL.KomNo, dbo.PARSEL.KadKan, dbo.PARSEL.Mescere, dbo.PARSEL.ACIKLAMA, dbo.PARSEL.URETIM_YONTEMI, 
                          dbo.PARSEL.KOORD_HASSASIYET, dbo.PARSEL.OLCU_TARIHI, dbo.PARSEL.URETIM_PROJESI_ID, dbo.PARSEL.POLY, dbo.PARSEL.CLLX, dbo.PARSEL.CLLY, 
                          dbo.PARSEL.CURX, dbo.PARSEL.CURY, dbo.PARSEL.RASTER, dbo.MAHALLE.ONAY, dbo.PARSEL.KULLANICI
    FROM         dbo.PARSEL INNER JOIN
                          dbo.MAHALLE ON dbo.PARSEL.YERKOD = dbo.MAHALLE.YERKOD
    WHERE     (dbo.PARSEL.PARSELDURUM = 'ORMAN')
    On the above view example, if table PARSEL has 1 million records do I have to create an index for PARSELDURUM or when I create a view does SQL server create automatic virtual index?

    Many thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Creating a view does not create an index. You will have to use the CREATE INDEX statement yourself if required.

    There are such things as indexed views that are different to your regular views that are created slightly differently. You can read up about indexed views in BoL
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2010
    Posts
    26
    Hi Gvee,

    Thanks for your reply. As you may have seen, my view gets the related records using a "where" statement from a varchar field. Do I have to add index to the table "PARSEL" for the field "PARSELDURUM" or directly create an indexed view?

    PARSEL table has 590.000 records and the case of "PARSEL.PARSELDURUM = 'ORMAN'" has 6600 records. If I create an indexed view, will it ignore the rest of the results on the table PARSEL and bring only the records of PARSELDURUM='ORMAN' without losing time?

    Many thanks

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Indexes do not change the data itself. They are much like the index in something like a phone book; you can use them to find the data you are interested in faster.

    So adding an index will not affect the results of your query. The same goes for an indexed view as the query itself stays the same.

    Hope this helps
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  6. #6
    Join Date
    Jan 2010
    Posts
    26
    Hi gvee,

    I know the indexes do not change the results of a query. My bad, I couldn't explain. I was talking about choosing the right records in a short time as there'S a "where" statement in my view. My problem is the query execution times of the indexes as there are 500.000 rows on my table.

    @PracticalProgram thanks I already read that. Did not really help.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Without knowing more information about your data I can only really suggest adding nonclustered indexes to the following columns. Because I am not familiar with your tables or the data within them this is a guess.

    • parsel.parseldurum
    • parsel.yerkod
    • mahalle.yerkod


    You can often get a pretty good guess in SSMS by including the actual execution plan when running your query. It includes a suggested index if appropriate. This feature may not be available in all versions of SSMS.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •