|
|
Posted by David Portas on 10/20/05 01:27
Hi Erland,
You don't believe everything MS tell you, do you? :-) Thanks due to Gert-Jan
for the following repro. Tested on SP4 (with or without MAXDOP) it doesn't
order the INSERT (YMMV). Admittedly it might sometimes be possible to order
*some* INSERTs but how are we supposed to know which will work, which won't
and which will fall apart after then next hotfix?
Hopefully the ORDER BY on INSERT nonsense will go away one day. It's plainly
a limitation rather than a feature.
--drop table Item
create table Item
(ItemID int not null primary key
,ItemGUID uniqueidentifier not null -- newid()
,ParentTopicID int null
,OwningUserID int null
,Name varchar(100) null
,SEName varchar(100) null
,ShortName varchar(50) null
,AboutDisabled bit null
,DisplayOrder int null
,Description varchar(200) null
,ShopDisabled tinyint null
,ShopNoCaption varchar(20) null
,GeneralShopDisabled tinyint null
,RelatedSitesDisabled tinyint null
,IsDisabled tinyint null
,CreatedOn datetime null
,VisitURL varchar(256) null
,VisitPrompt varchar(200) null
,ShopURL varchar(256) null
,Address1 varchar(100) null
,Address2 varchar(100) null
,Suite int null
,City varchar(30) null
,State char(2) null
,ZipCode char(5) null
,Country varchar(50)
,Phone varchar(20)
,FAX varchar(20)
,URL varchar(256)
,Email varchar(100)
,OvertureKeywords varchar(200)
,SE_Title varchar(100)
,SE_Description varchar(50)
,SE_Keywords varchar(50)
,ExtendedData varchar(100)
,SubmittedByUserID int
)
insert into Item (ItemID,IsDisabled,ZipCode,Name,itemguid,CreatedOn
,RelatedSitesDisabled,GeneralShopDisabled,ShopDisabled,DisplayOrder,AboutDisabled
,ShortName
) select id,0,'abcd',name,newid(),dateadd(ms,-(id%2000000),getdate())
,id%2,id%3,id%4,1,0, left(name,15)
from sysobjects
--drop table ItemRatings
create table ItemRatings
(ItemID int not null primary key
,rating int not null
,HasComment tinyint not null
,TopicID int null
,userID int null
,userAge int null
,userGender char(1) null
,f7 varchar(20) null
,f8 varchar(20) null
,f9 varchar(20) null
,f10 varchar(20) null
,f11 varchar(20) null
,f12 varchar(20) null
,f13 varchar(20) null
,f14 varchar(20) null
,f15 varchar(20) null
,f16 varchar(20) null
,f17 varchar(20) null
,f18 varchar(20) null
,f19 varchar(20) null
,f20 varchar(20) null
)
insert into ItemRatings(ItemID,rating,HasComment,TopicID,userID
,userAge,userGender,f7,f8,f9,f10,f11,f12)
select id,id%10,id%2,90,20069715,id%80,'M'
,'aaaaaaaaaa','aaaaaaaaaa','aaaaaaaaaa','aaaaaaaaaa','aaaaaaaaaa','aaaaaaaaaa'
from sysobjects
insert into ItemRatings(ItemID,rating,HasComment,TopicID,userID
,userAge,userGender,f7,f8,f9,f10,f11,f12)
select id%2000000000+1000000,id%10,id%2,90,id%19,id%80,'M'
,'bbbbbbbbbbbbbbbbbbbb','bbbbbbbbbbbbbbbbbbbb','bbbbbbbbbbbbbbbbbbbb','bbbbbbbbbbbbbbbbbbbb','bbbbbbbbbbbbbbbbbbbb','bbbbbbbbbbbbbbbbbbbb'
from sysobjects
--drop table Alerts
create table Alerts
(AlertID int not null primary key
,ItemID int not null
,alertOwnerUserID int not null
,alertType int not null
,f4 char(200) null
)
insert into Alerts
select id,id,20069715,1,' '
from sysobjects
--drop TABLE #tmp
CREATE TABLE #tmp
(rownum int not null identity(1,1) primary key
,[ItemID] [int] NOT NULL
,[ItemGUID] [uniqueidentifier] NOT NULL
,[ParentTopicID] [int] NULL
,[OwningUserID] [int] NULL
,[Name] [varchar] (250) NOT NULL
,[SEName] [varchar] (250) NULL
,[ShortName] [varchar] (35) NOT NULL
,[AboutDisabled] [bit] NOT NULL
,[DisplayOrder] [int] NOT NULL
,[Description] [ntext] NULL
,[ShopDisabled] [tinyint] NOT NULL
,[ShopNoCaption] [tinyint] NULL
,[GeneralShopDisabled] [tinyint] NOT NULL
,[RelatedSitesDisabled] [tinyint] NOT NULL
,[IsDisabled] [tinyint] NOT NULL
,[CreatedOn] [datetime] NOT NULL
,[VisitURL] [ntext] NULL
,[VisitPrompt] [varchar] (50) NULL
,[ShopURL] [ntext] NULL
,[Address1] [varchar] (255) NULL
,[Address2] [varchar] (255) NULL
,[Suite] [varchar] (100) NULL
,[City] [varchar] (255) NULL
,[State] [varchar] (100) NULL
,[ZipCode] [varchar] (10) NULL
,[Country] [varchar] (100) NULL
,[Phone] [varchar] (50) NULL
,[FAX] [varchar] (50) NULL
,[URL] [ntext] NULL
,[Email] [varchar] (100) NULL
,[OvertureKeywords] [ntext] NULL
,[SE_Title] [ntext] NULL
,[SE_Description] [ntext] NULL
,[SE_Keywords] [ntext] NULL
,[ExtendedData] [ntext] NULL
,[SubmittedByUserID] [int] NULL
,[NumRatings] [int] not null
,[AvgRating] [decimal](6, 5) not null
,[NumComments] [int] not null
,[UserRating] [decimal](3,2) not null
,[AlertUser] [int] not null
)
-- set showplan_text on
INSERT #tmp (ItemID, ItemGUID, ParentTopicID, OwningUserID, Name,
SEName, ShortName, AboutDisabled, DisplayOrder,
Description, ShopDisabled, ShopNoCaption, GeneralShopDisabled,
RelatedSitesDisabled, IsDisabled, CreatedOn, VisitURL,
VisitPrompt, ShopURL, Address1, Address2, Suite, City, State, ZipCode,
Country, Phone, FAX, URL, Email,
OvertureKeywords, SE_Title, SE_Description, SE_Keywords, ExtendedData,
SubmittedByUserID, NumRatings, AvgRating,
NumComments, UserRating, AlertUser)
SELECT
i.*
, isnull(a.NumRatings,0) NumRatings
, isnull(a.AvgRating, 0) AvgRating
, isnull(a.NumComments, 0) NumComments
, isnull(r.rating, 0)
, isnull(al.alertOwnerUserID, -1)
FROM Item i (nolock)
join (
select
ir.itemid
, count(ir.rating) NumRatings
, avg(ir.rating*1.0) AvgRating
, sum(ir.HasComment) NumComments
from ItemRatings ir with (nolock)
where ir.TopicID = 90
and ir.userAge between 0 and 255
and ir.userGender = coalesce(null, ir.userGender)
group by ir.itemid
) a on i.itemid = a.itemid
left join ItemRatings r
on i.itemid = r.itemid and r.userid = 20069715
left join Alerts al
on i.itemid = al.itemid and al.alertOwnerUserID = 20069715 and
alertType = 1
WHERE i.IsDisabled = 0
and isnull(i.ZipCode, '') = coalesce(null, isnull(i.ZipCode, ''))
ORDER BY a.AvgRating desc, a.NumRatings desc, i.Name asc
-- set showplan_text off
select top 100 rownum,avgrating,numratings,name
from #tmp
order by rownum
--
David Portas
SQL Server MVP
--
Navigation:
[Reply to this message]
|