sql - Encapsulated query within a database object returns too few rows -

a member of team has gotten strange behaviour can recreated both in development environment , system test environment ms sql databases.

if runs query directly returns 517 rows, correct , expected result:

select          p.package_id, la.code_kid     package p (nolock), strength s (nolock),     productcode la  (nolock), code  (nolock)     p.strength_id = s.strength_id     , la.product_id = s.product_id     , la.code_kid = a.code_id     except      select p.package_id, p.code_kid package p 

however, if puts same query in view wrongly returns 311 rows - 206 rows less if runs query directly.

if run query analyser both direct query , view query see 2 query plans quite different, don't understand why.

he tried dump query temporary table:

insert mydb.code_package     select              p.package_id, la.code_kid         package p (nolock), strength s (nolock),         productcode la  (nolock), code  (nolock)         p.strength_id = s.strength_id         , la.product_id = s.product_id         , la.code_kid = a.code_id         except          select p.package_id, p.code_kid package p 

, correctly creates table has 517 rows. however, if puts same sql in stored procedure wrongly returns 311 rows.

it seems once query encapsulated within database object returns few rows.

as mentioned, has recreated error on other database systems too.

any ideas can cause strange behaviour?

he has tried following without success:

• remove nolock
• set transaction isolation level read uncommitted

update

i'm not sure if ssms wizard or template used create view, if select "script view -> create -> new query editor window" output:

use [testutv] go  set ansi_nulls on go  set quoted_identifier on go  create view [mydbviews].[code_package]      select              p.package_id, la.code_kid         package p (nolock), strength s (nolock),         productcode la  (nolock), code  (nolock)         p.strength_id = s.strength_id         , la.product_id = s.product_id         , la.code_kid = a.code_id         except          select p.package_id, p.code_kid package p  go 

here 1 of tables used, unfortunately there database quite huge hundreds of tables , views can't post here.

create table [dbo].[package](     [package_id] [uniqueidentifier] not null,     [multiple] [int] null,     [multiple2] [int] null,     [outprodnum] [varchar](6) null,     [outprodnumdate] [datetime] null,     [zzzpackage_kid] [uniqueidentifier] null,     [strength_id] [uniqueidentifier] null,     [indi] [varchar](4096) null,     [createddate] [datetime] null,     [createdby] [varchar](255) null,     [lastchangeddate] [datetime] null,     [lastchangedby] [varchar](255) null,     [code_kid] [uniqueidentifier] null,     [markdate] [datetime] null,     [amount] [int] null,     [kipackage_id] [uniqueidentifier] null,     [xyz] [bit] null,     [ean] [varchar](255) null,     [d_id] [uniqueidentifier] null,     [abc_id] [uniqueidentifier] null,     [ddd] [decimal](18, 4) null,     [era_kid] [uniqueidentifier] null,     [uuu] [decimal](18, 4) null,     [ueer_kid] [uniqueidentifier] null,     [abcidstring] [varchar](4095) null,     [externalid] [varchar](255) null,     [dpack_kid] [uniqueidentifier] null,     [tttpacks_kid] [uniqueidentifier] null,  constraint [package_pk] primary key clustered  (     [package_id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary] 

try incapsulating query in sp and/or function , compare numbers of records returned. seeing impacted connection ansi settings. ssms , sql server sets default on each connection , objects views have them set @ creation time , persisted @ execution time. check options enabled sessions: https://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/
check options persisted view:

select * sys.sql_modules