Welcome to dbFreaks.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Problems with NOEXPAND

 
   Database Help (Home) -> MSEQ RSS
Next:  sql server 2005 different sql server 2000  
Author Message
Emiel

External


Since: Dec 14, 2008
Posts: 2



(Msg. 1) Posted: Sun Dec 14, 2008 6:40 am
Post subject: Problems with NOEXPAND
Archived from groups: microsoft>public>sqlserver>mseq (more info?)

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 
Back to top
Login to vote
Emiel

External


Since: Dec 14, 2008
Posts: 2



(Msg. 2) Posted: Mon Dec 15, 2008 1:56 am
Post subject: RE: Problems with NOEXPAND [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Registered problem at microsoft connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fee...ckID=38

 >> Stay informed about: Problems with NOEXPAND 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
index on a view - Hello, I have a table (TAB) and A View with alias (VIEW) Table cod varchar 3 descr carchar 60 my view cod alias COd1 descr alias DES Now i need a index on view with key COD1 I can't create it. Can..

Creating an alias - I have an alias that can get very long because it maintains a information of where it came from. Is there a way to alias an alias? My first thought was: Declare @T1 varchar(128) Set T1 = 'MyAlias' .... Inner Join Table1 as @T1 This of course does no...

Formatting numeric fields in select-clause - This is propably a very simple question, but I can“t seem to find the answer to it in the documentation. I want to format a numeric field so the result is right justified and zero-filled. ex select 1 will give the result 01 How do I manage this..

Simple Query problem - Sample table as follows Order ID Stock Code Status --------- ------- ------- 203 STK1 3 203 STK2 2 203 STK4 3 204 STK1 3 204 STK5 3 205 ..

query assistance -return most recent date - I have a table that has two fields, pkg_num, which is a number, and del_date_time, which is a date-time. The table can contain duplicate pkg_num values, as long as the del_date_time values are different for any given number. I need a query that will...
   Database Help (Home) -> MSEQ All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]