Problems with NOEXPAND

14 Dec 2008 2:41 PM

Emiel

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

14 Dec 2008 3:15 PM

Uri Dimant

Emiel
I do not have right 9.00.3068.00 build, tested just on 3042 and it cannot
compile
Cannot create index on view 'master.dbo.vw_tot_1' because the view contains
a table hint. Consider removing the hint.

Show quoteHide quote

"Emiel" <Em***@discussions.microsoft.com> wrote in message
news:D3903587-CAC2-4D09-A670-5BD6BC5B0C37@microsoft.com...
> 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

14 Dec 2008 8:28 PM

Emiel

"Uri Dimant" wrote:

> Emiel
> I do not have right 9.00.3068.00 build, tested just on 3042 and it cannot
> compile
> Cannot create index on view 'master.dbo.vw_tot_1' because the view contains
> a table hint. Consider removing the hint.
>

That's strange: when I try to run the code on a server with the following
version:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT
5.0 (Build 2195: Service Pack 4)

I don't get any errors....

On the other hand: when I remove the WITH (NOEXPAND) in the view definition,
I get an error during the creation of the index:

Msg 1937, Level 16, State 1, Line 2

Cannot create index on view 'Emiel.dbo.vw_tot_1' because it references
another view 'dbo.vw_tot_2'. Consider expanding referenced view's definition
by hand in indexed view definition.

This error occurs in both versions.

Is there some setting I'm not aware of?

15 Dec 2008 9:55 AM

Emiel

Registered problem at microsoft connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=388003

16 Dec 2008 2:05 PM

Ekrem_Ã–nsoy

Is your database Compatibility Mode set to 80?

Quote from BOL (http://msdn.microsoft.com/en-us/library/ms181151.aspx):
Hints are not allowed in the definitions of indexed views in SQL Server
2008. In compatibility mode 80 and higher, SQL Server ignores hints inside
indexed view definitions when maintaining them, or when executing queries
that use indexed views. Although using hints in indexed view definitions
will not produce a syntax error in 80 compatibility mode, they are ignored.

I got the same error as Uri (altough my test instance's version is 9.0.3159)
and when I set the Compatibility Mode to 80, I didn't get any error.

--
Ekrem Ã–nsoy

Show quoteHide quote

"Emiel" <Em***@discussions.microsoft.com> wrote in message
news:D3903587-CAC2-4D09-A670-5BD6BC5B0C37@microsoft.com...
> 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