CODEWITHSUNDEEP
sqlsql-serversql-server-2017sql-view
Vinny
Vinny

Reputation: 55

Spooky query behavior : Same query, different results when inside a view

The same query brings different results when inside a View.

I tried to force same codepages, same data types, Common Query Expressions... none worked.

Have a look at this simple query:

SELECT TOP 100 CompanyID, SubsidiaryID 
FROM STAGING.SALESAX S
INNER JOIN Subsidiary SU
ON S.CompanyID = SU.SubsidiaryAXCode

It returns all expected 100 rows.

If I use the EXACT SAME QUERY in a view:

CREATE VIEW [STAGING].[VSalesAXBASE]    
AS    
SELECT TOP 100 CompanyID, SubsidiaryID 
FROM STAGING.SALESAX S
INNER JOIN Subsidiary SU
ON S.CompanyID = SU.SubsidiaryAXCode

GO

SELECT * FROM [STAGING].[VSalesAXBASE]

it returns 0 ROWS

screenshots of me running the query in two ways

Screenshot of query updated without TOP Statement

Upvotes: -2

Views: 1523

Answers (4)

Tahari
Tahari

Reputation: 139

This has been answered, but a generic response for others is as follows:

If you have a view using a particular schema, and it references a table using the default schema, then you'd need to check if there is a duplicate table name in the view's schema that has different data in it. So view test.foo is

create view test.foo as
(
  select col1 from bar
)
go

If you just run the query, by itself, it would use the default schema (usually dbo). However, if there is also an existing test.bar table, then it will use that one instead since the view is running with that context.

-- this will give different results if dbo.bar and test.bar have different data

select * from test.foo

View needs to be the following to ensure the correct table is referenced.

create view test.foo as
(
  select col1 from test.bar
)
go

Upvotes: -1

Vinny
Vinny

Reputation: 55

The solution was to add the DBO schemma to the subsidiary table.. thanks to the suggestions of @VladimirBaranov, @IMSoP and @Augustina(Codec)

Looks like the execution context of the view may be different from the current user (need to check more on this).

Solution

Upvotes: 0

Fleshy
Fleshy

Reputation: 182

SQL does not guarantee the ordering of results unless an explicit Order By clause is used.

Upvotes: -1

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

You prefix SALESAX table with STAGING., but don't prefix Subsidiary table in the query. Do prefix all tables in the query.

One possibility is that there are several Subsidiary tables and the query uses different table in the context of the view.

By the way, it is also a good practice to prefix columns with table names in the SELECT part of the query. For example, right now it is hard to know from which table(s) the columns CompanyID and SubsidiaryID come from.

Upvotes: 3

Related Questions