SQL Server Performance

View X Top

Discussion in 'General Developer Questions' started by abianchi, Jul 17, 2003.

  1. abianchi New Member

    Can I use in a View the clause TOP ?

    If I execute the SQL statement out of a view it returns one line, but if execute a view it don't returns a line.

    ALTER VIEW V_PESSOAL_FORMACAO AS
    SELECT
    TP.IDPESSOAL,
    T.IDEMP ,
    T.DATINICIAL,
    T.DATFINAL DATFORMACAO ,
    CR.IDLIVROREGVIG IDLIVROREGVIGF,
    CR.NUMREGISTRO NUMREGISTROF ,
    CR.LETRAREGISTRO LETRAREGISTROF,
    CR.ANOREGISTRO ANOREGISTROF ,
    CR.UFREGISTRO UFREGISTROF,
    CR.DATREGISTRO DATREGISTROF,
    CR.IDEMPCOMISSAO IDEMPCOMISSAOF,
    CR.PAGREGISTRO PAGREGISTROF,
    T.INDREGISTRO,
    TP.INDHISTFORMBASICA,
    T.IDTURMA ,
    T.INDTV
    FROM TURMA_PESSOAL TP WITH (NOLOCK)
    INNER JOIN TURMA T WITH (NOLOCK) ON TP.IDTURMA=T.IDTURMA
    LEFT OUTER JOIN CURSO_REGISTRO CR WITH (NOLOCK) ON TP.IDTURMA=CR.IDTURMA AND
    TP.IDPESSOAL=CR.IDPESSOAL AND CR.INDCANCELADO='N'
    WHERE T.INDFORMBASICA='S' AND
    TP.INDHISTORICO = 'N' AND
    TP.INDAPROVADO='S' AND
    TP.INDHISTFORMBASICA='N'
    UNION ALL
    SELECT top 1 VPFM.IDPESSOAL,
    VPFM.IDEMP ,
    VPFM.DATINICIAL,
    VPFM.DATFORMACAO ,
    IDLIVROREGVIGF,
    NUMREGISTROF ,
    LETRAREGISTROF,
    ANOREGISTROF ,
    UFREGISTROF,
    DATREGISTROF,
    IDEMPCOMISSAOF,
    PAGREGISTROF,
    INDREGISTRO,
    INDHISTFORMBASICA,
    IDTURMA = NULL ,
    VPFM.INDTV
    FROM V_PESSOAL_FORMACAO_MILITAR VPFM WITH (NOLOCK)


  2. trifunk New Member

    I think when using a view you need to use an order by clause in conjunction with the top keyword to get results, or it might be you need to use the top keyword in a view when using the order by keyword, I would try it out but unfortunately the computer I'm currently on doesn't have sql server or query analyser.

    Cheers
    Shaun

    World Domination Through Superior Software
  3. gaurav_bindlish New Member

  4. satya Moderator

    True(shaun), even BOL examples refers the same.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. bambola New Member

    You can use TOP in a view without an ORDER BY. What you cannot do it use an ORDER BY without TOP. In this case you would use ORDER BY with TOP 100 PERCENT.


    USE pubs
    GO
    create view v_test_order
    as
    select au_id, au_lname
    from dbo.authors
    where au_lname like 'b%'

    union all

    select top 1 au_id, au_lname
    from dbo.authors
    where au_lname like 'd%'
    GO
    select * from v_test_order

    Your view seems ok to me. The problem must be elsewhere.

    Bambola.

Share This Page