The following SQL statements produce an unexpected result. I would expect
that the two select statements would result one row, but the first one
doesn't. Is this a bug of a feature?
I'm running Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86) Feb 26
2008 18:15:01 Copyright (c) 1988-2005 Microsoft Corporation Standard
Edition on Windows NT 6.0 (Build 6001: Service Pack 1)
--select @@version
--go
SET NOCOUNT ON
GO
CREATE TABLE t1(
id INT IDENTITY NOT NULL,
amount INT NOT NULL,
CONSTRAINT pk_t1 PRIMARY KEY CLUSTERED
(
id ASC
)
)
GO
create table t2 (
id INT IDENTITY NOT NULL,
source INT NOT NULL,
amount INT NOT NULL,
CONSTRAINT pk_t2 PRIMARY KEY CLUSTERED
(
id ASC
)
)
GO
CREATE VIEW vw_tot_2 WITH SCHEMABINDING
AS
SELECT
source,
amount = SUM(amount), cnt=COUNT_BIG(*)
FROM
dbo.t2
GROUP BY
source
GO
CREATE UNIQUE CLUSTERED INDEX vw_tot_2 ON vw_tot_2(source)
GO
CREATE VIEW vw_tot_1 WITH SCHEMABINDING AS
SELECT
T1.id,
T1.amount,
amount2 = 100 --T2.amount
FROM
dbo.t1 T1
INNER JOIN dbo.vw_tot_2 T2 WITH (NOEXPAND)
ON T1.id = T2.source
GO
CREATE UNIQUE CLUSTERED INDEX vw_tot_1 ON vw_tot_1(id)
GO
-- test
INSERT INTO t1(amount) VALUES (1000)
INSERT INTO t2(source, amount) VALUES (SCOPE_IDENTITY(), 1000)
SELECT * FROM vw_tot_1 WITH (NOEXPAND)
SELECT * FROM vw_tot_1
GO
-- Cleanup
DROP VIEW vw_tot_1
GO
DROP VIEW vw_tot_2
GO
DROP TABLE t2
GO
DROP TABLE t1
GO
>> Stay informed about: Problems with NOEXPAND