Инкапсулированный запрос внутри объекта базы данных возвращает слишком мало строк

Член моей команды получил какое-то странное поведение, которое можно воссоздать как в среде разработки, так и в тестовой среде MS SQL-баз данных.

Если он запускает этот запрос напрямую, он возвращает 517 строк, что является правильным и ожидаемым результатом:

SELECT 
        p.package_id, la.CODE_KID
    FROM package p with (nolock), Strength s with (nolock),
    ProductCODE la  with (nolock), CODE  a with (nolock)
    where p.Strength_ID = s.Strength_ID
    and la.Product_ID = s.Product_ID
    AND la.CODE_KID = a.CODE_ID
    except 
    select p.package_ID, p.CODE_KID from package p

Однако, если он ставит один и тот же запрос в представлении, он ошибочно возвращает 311 строк - 206 строк меньше, чем при непосредственном запуске запроса.

Если мы запустим Query Analyzer как для прямого запроса, так и для запроса вида, мы видим, что два плана запроса совсем другие, но мы не понимаем, почему.

Он также попытался сбрасывать запрос во временную таблицу:

insert into MyDB.CODE_PACKAGE
    SELECT 
            p.package_id, la.CODE_KID
        FROM package p with (nolock), Strength s with (nolock),
        ProductCODE la  with (nolock), CODE  a with (nolock)
        where p.Strength_ID = s.Strength_ID
        and la.Product_ID = s.Product_ID
        AND la.CODE_KID = a.CODE_ID
        except 
        select p.package_ID, p.CODE_KID from package p

который правильно создает таблицу с 517 строками. Однако, если он помещает тот же SQL в хранимую процедуру, он ошибочно возвращает 311 строк.

Кажется, что когда запрос инкапсулирован в объект базы данных, он возвращает слишком мало строк.

Как уже упоминалось, он снова создал ошибку в других системах баз данных.

Любые идеи, что может вызвать это странное поведение?

Он также безуспешно пробовал следующее:

Удалите nolock
Установить уровень изоляции транзакции для чтения без доступа

Обновление

Я не уверен, что для создания представления использовался мастер SSMS или шаблон, но если я выберем "Script" Просмотр как → CREATE to → New Query Editor Window ", то это будет выход:

USE [TestUtv]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [MyDBviews].[CODE_PACKAGE]
as

    SELECT 
            p.package_id, la.CODE_KID
        FROM package p with (nolock), Strength s with (nolock),
        ProductCODE la  with (nolock), CODE  a with (nolock)
        where p.Strength_ID = s.Strength_ID
        and la.Product_ID = s.Product_ID
        AND la.CODE_KID = a.CODE_ID
        except 
        select p.package_ID, p.CODE_KID from package p

GO

Вот одна из таблиц, которые используются, к сожалению, база данных довольно огромная с сотнями таблиц и представлений, поэтому я не могу публиковать все здесь.

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]

Ответ 1

Попробуйте инкапсулировать ваш запрос в SP и/или функцию и сравните количество возвращенных записей. На то, что вы видите, вероятно, повлияют на настройки ANSI соединения. SSMS и SQL Server устанавливают по умолчанию по каждому соединению, а объекты, подобные представлениям, устанавливаются во время создания и сохраняются во время выполнения. Проверьте параметры, которые в настоящий момент включены для ваших сеансов: https://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/
Проверьте параметры, которые сохраняются в вашем представлении:

SELECT * FROM sys.sql_modules

Ответ 2

Попробуйте использовать тот же запрос, используя инструкции JOIN. вы можете получить разные результаты из разных планов запросов по запросам, которые используют этот старый метод соединения.

SELECT 
    p.package_id, 
    la.CODE_KID
FROM package p with (nolock)
INNER JOIN Strength s with (nolock)
    ON p.Strength_ID = s.Strength_ID
INNER JOIN ProductCODE la with (nolock)
    ON la.Product_ID = s.Product_ID
INNER JOIN CODE a with (nolock)
    ON la.CODE_KID = a.CODE_ID
EXCEPT
SELECT 
    p.package_ID, 
    p.CODE_KID 
FROM package p

Ответ 3

Я рекомендую следующий синтаксис для того, что вы пытаетесь выполнить. Мое предположение - старый синтаксис ANSI JOIN, который вы как-то используете при создании хранимой процедуры.

SELECT 
    p.package_id 
    ,la.CODE_KID
FROM package AS p
JOIN Strength AS s ON p.Strength_ID = s.Strength_ID
JOIN ProductCODE AS la ON la.Product_ID = s.Product_ID
JOIN CODE AS a ON la.CODE_KID = a.CODE_ID
EXCEPT 
SELECT p.package_ID, p.CODE_KID from package p

Ответ 4

На самом деле вопрос в том, какие исправления отфильтровываются, если запрос инкапсулирован в представление?

поэтому, как предлагалось ранее в @Alex, вы должны сравнить результаты с вашим запросом и тем же запросом в представлении.

в SSMS попробуйте что-то вроде:

SELECT *
FROM (
    SELECT p.package_id, la.CODE_KID
    FROM package p with (nolock), Strength s with (nolock),
    ProductCODE la  with (nolock), CODE  a with (nolock)
    where p.Strength_ID = s.Strength_ID
    and la.Product_ID = s.Product_ID
    AND la.CODE_KID = a.CODE_ID
    except 
    select p.package_ID, p.CODE_KID from package p
    ) AS DIRECT_Q
FULL JOIN
    (
    SELECT *
    FROM YOUR_VIEW_CREATED_FROM_QUERY    /* <-- THE NAME OF YOUR VIEW */
    ) AS VIEWED_Q
ON DIRECT_Q.package_id=VIEWED_Q.package_id 
   AND DIRECT_Q.CODE_KID=VIEWED_Q.CODE_KID

таким образом вы увидите, какие записи отсутствуют, и попытайтесь понять причину (нули, акценты, конкретные символы ecc ecc)

Я также дам чек, если тип данных и сортировка для package.CODE_KID и ProductCODE.CODE_KID совпадают.