Indexing a view in SQL2000

  • krypto69

    SSChampion

    Points: 13494

    Hi,

    I understand it is possible to index a view in SQL2000.

    I have an old legacy system that has this view - but it is not indexed. All fields are used:

    SELECT dbo.RQHDR.Branch + N'-' + LEFT(RIGHT(N'0' + LTRIM(STR(dbo.RQHDR.Requisition)), 7), 2) + N'-' + RIGHT(dbo.RQHDR.Requisition, 5)

    + N'-' + ISNULL(dbo.coreclass.RECLASS, dbo.RQHDR.Co) AS requisition, dbo.RQHDR.OrigDate, ISNULL(Email_3.UNAME, dbo.RQHDR.Originator)

    AS Originator, ISNULL(dbo.vSTATUS.[Status Description], ISNULL(RTRIM(dbo.vUSERS.TITLE) + N' Approval ', N'Approver Review'))

    AS [Status Description], ISNULL(Email_2.UNAME, dbo.RQHDR.Approver) AS Approver, dbo.RQHDR.ApprDate, dbo.RQHDR.TotAmt,

    ISNULL(Email_1.UNAME, dbo.RQHDR.SubmittedTo) AS SubmittedTo, dbo.RQHDR.ReqName, ISNULL(dbo.RQHDR.CMP, ISNULL(dbo.RQHDR.Just, N''))

    + ISNULL(N' - ' + dbo.RQHDR.CM11, N'') + ISNULL(N' - ' + dbo.RQHDR.CM1, N'') + ISNULL(N' - ' + dbo.RQHDR.CM2, N'')

    + ISNULL(N' - ' + dbo.RQHDR.CM3, N'') + ISNULL(N' - ' + dbo.RQHDR.CA1, N'') + ISNULL(N' - ' + dbo.RQHDR.CA2, N'')

    + ISNULL(N' - ' + dbo.RQHDR.CM4, N'') + ISNULL(N' - ' + dbo.RQHDR.CM5, N'') + ISNULL(N' - ' + dbo.RQHDR.CM6, N'') AS Comments,

    dbo.RQHDR.Branch AS Dept, dbo.RQHDR.PO, ISNULL(dbo.coreclass.RECLASS, dbo.RQHDR.Co) AS CO, dbo.RQHDR.Requisition AS myReq,

    dbo.RQHDR.Originator AS UID, dbo.RQHDR.Closed, dbo.RQHDR.SubmittedTo AS Expr1, ISNULL(dbo.vwPOSENDCOUNT.SendCount, 0) AS Sent

    FROM dbo.coreclass RIGHT OUTER JOIN

    dbo.RQHDR LEFT OUTER JOIN

    dbo.vwPOSENDCOUNT ON dbo.RQHDR.Requisition = dbo.vwPOSENDCOUNT.Requisition LEFT OUTER JOIN

    dbo.vUSERS ON dbo.RQHDR.SubmittedTo = dbo.vUSERS. ON dbo.coreclass.COMPANY = dbo.RQHDR.Co LEFT OUTER JOIN

    dbo.Email AS Email_3 ON dbo.RQHDR.Originator = Email_3. LEFT OUTER JOIN

    dbo.vSTATUS ON dbo.RQHDR.Status = dbo.vSTATUS.Status LEFT OUTER JOIN

    dbo.Email AS Email_1 ON dbo.RQHDR.SubmittedTo = Email_1. LEFT OUTER JOIN

    dbo.Email AS Email_2 ON dbo.RQHDR.Approver = Email_2.

    WHERE (NOT (dbo.RQHDR.Status = N'X'))

    How would I index this view?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714637

    Read this first: http://technet.microsoft.com/en-us/library/aa902643%28v=sql.80%29.aspx

    There are restrictions for your base tables. Essentially you build the view WITH SCHEMABINDING and add an index. An example about 2/3 of the way down the paper.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply