September 20, 2010 at 10:50 pm
Hello,
I have a query that I run against a View to get product Sellers and count of products for each Seller in a specific category. It works very well. The problem is that I need to pass more parameters in the WHERE filter from other tables. But if I add these table and fields to the View, it requires to add the same fields in the Group By (because I have Count_big(*) in the select list). If I add fields to the select list and Group By - I get wrong counts and I am not able to create Unique Clustered Index on this View. What is the best way to handle this? any help is appreciated. I am posting my query along with View and Index.
Eugene.
Query:
with keys as (
select getproductssellers.productslocation_cid,
count(distinct getproductssellers.products_pid) as CNT
from GetProductsSellers WITH (NOEXPAND)
WHERE getproductssellers.products_subcategory=130
group by getproductssellers.productslocation_cid
)
select customer.businessname as Seller, productslocation_cid, CNT
from keys
inner join customer on productslocation_cid=customer.cid
order by CNT desc
View:
USE [WebApp]
GO
/****** Object: View [dbo].[GetProductsSellers] Script Date: 09/20/2010 21:48:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[GetProductsSellers]
WITH SCHEMABINDING
AS
SELECT dbo.Products.subcategory AS Products_subcategory, dbo.ProductsLocation.cID AS Productslocation_cid, dbo.Products.pID AS Products_pid, COUNT_BIG(*)
AS CNT
FROM dbo.Products INNER JOIN
dbo.ProductsLocation ON dbo.Products.pID = dbo.ProductsLocation.pID
GROUP BY dbo.Products.subcategory, dbo.ProductsLocation.cID, dbo.Products.pID
Index:
USE [WebApp]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object: Index [_dta_index__dta_mv_0_8066_Products_c_8_1285683728__K1_K2_K3] Script Date: 09/20/2010 21:48:37 ******/
CREATE UNIQUE CLUSTERED INDEX [_dta_index__dta_mv_0_8066_Products_c_8_1285683728__K1_K2_K3] ON [dbo].[GetProductsSellers]
(
[Products_subcategory] ASC,
[Productslocation_cid] ASC,
[Products_pid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
September 20, 2010 at 11:03 pm
Cross posted, please direct all replies and discussion here: http://www.sqlservercentral.com/Forums/Topic989922-360-1.aspx
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply