New row not returning when selected from the View but returns from a normal SQL query

Question

I have a SQL view for my User & person table called vw_user. This view simply joins the user and person table. The view is below. When I select from the view and filter by username, I do not get a result however when I copy SQL from the view into a separate query, and filter by username, I get a result. This applies to both, stored procedures and manually running the queries in SSMS. Could this be a bad index or some sort of SQL caching the needs to be cleared?

ALTER VIEW [civic].[vw_User]
AS

SELECT TOP (1000) 
       p.[Email]
      ,p.[FirstName]
      ,p.[MiddleName]
      ,p.[LastName]
      ,p.[Phone]
      ,u.*
      ,u.[PersonID] as ID        -- temp for for previous version
      ,p.[Address1] as [Address] -- temp for for previous version
  FROM [civic].[User] u
  JOIN [civic].[Person] p on p.PersonID=u.PersonID
GO

When I run the select below, I do not get a result:

SELECT  
     u.*
FROM [civic].[vw_User] u
WHERE   
    -- t4-where begin
    u.[Username] = 'exampleUser'
    -- t4-where end

When I run the query below, I do get a result:

 SELECT TOP (1000) 
           p.[Email]
          ,p.[FirstName]
          ,p.[MiddleName]
          ,p.[LastName]
          ,p.[Phone]
          ,u.*
          ,u.[PersonID] as ID        -- temp for for previous version
          ,p.[Address1] as [Address] -- temp for for previous version
      FROM [civic].[User] u
      JOIN [civic].[Person] p on p.PersonID=u.PersonID
WHERE u.[Username] = 'exampleUser'

UPDATE: We have over 1000 users and removing the top 1000 from the view corrected the problem.

`SELECT TOP 100` without an order by returns an arbitrary 100 rows from the table, and those rows might change from one run to the next. I consider this a typo. — Gordon Linoff, Jul 07 '21 at 18:22
Using select * in a view is a particularly bad idea. You should ideally select the columns explicitly in all queries. https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful — JeffUK, Jul 07 '21 at 18:34

score 2 · Accepted Answer · answered Jul 07 '21 at 18:34

2

Those queries are not semantically the same. Having the WHERE clause in the same scope as TOP forces SQL Server to evaluate the WHERE first. When the WHERE clause is logically after the TOP values SQL Server can (must?) pick the TOP rows first, then apply the filter. eg, only the first of these queries returns 10 rows (usually):

select top 10 *
from Sales.SalesOrderDetail
where ProductID = 711

go

with q as
(
    select top 10 *
    from Sales.SalesOrderDetail 
)
select * 
from q
where ProductId = 711

answered Jul 07 '21 at 18:34

David Browne - Microsoft

80,331
6
39
67

Must not can. But only logically, the physical implementation could be anything – Charlieface Jul 08 '21 at 02:04
Since the TOP-without-order-by is non-deterministic pushing the predicate wouldn’t be obviously wrong. – David Browne - Microsoft Jul 08 '21 at 03:43

New row not returning when selected from the View but returns from a normal SQL query

1 Answers1