Post reply

Indexing a view in SQL2000

  • January 9, 2015 at 8:05 am

    #397855

    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?

  • January 9, 2015 at 10:16 am

    #1769147

    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 1 (of 1 total)

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