Indexes on views

telmessos · #1 (**permalink**) 01-06-12, 04:35

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

gvee · #2 (**permalink**) 01-06-12, 21:17

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

telmessos · #3 (**permalink**) 01-07-12, 05:56

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

gvee · #4 (**permalink**) 01-08-12, 19:45

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

PracticalProgram · #5 (**permalink**) 01-08-12, 20:37

Improving Performance with SQL Server 2008 Indexed Views

telmessos · #6 (**permalink**) 01-09-12, 01:58

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.

gvee · #7 (**permalink**) 01-09-12, 12:18

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech