How to optimize: having COUNT(*)>1 Without considering the indexes,
Is there a way to re-write this query and get the same result quicker?

select fo.OrderNo, fo.OrderItemSeqNo, COUNT(*)
from Facts.FactBackOrder fo
group by fo.OrderNo, fo.OrderItemSeqNo
having COUNT(*)&gt;1


When I re-write it as the the followig, it even gets worse.

;WITH R1 AS (

select ROW_NUMBER() OVER (PARTITION BY fo.OrderNo, fo.OrderItemSeqNo ORDER BY fo.OrderNo, fo.OrderItemSeqNo) AS N
       ,fo.OrderNo
       , fo.OrderItemSeqNo
from Facts.FactBackOrder fo
)

SELECT * FROM R1 WHERE N = 2


OR THIS:

;WITH R1 AS (

select COUNT_BIG(*) OVER (PARTITION BY fo.OrderNo, fo.OrderItemSeqNo) AS N
       ,fo.OrderNo
       , fo.OrderItemSeqNo
from Facts.FactBackOrder fo
)

SELECT * FROM R1 WHERE N &gt; 1


It is a fact table in a datawarehouse. 
the indexes are on the picture below.



An Indexed view in this situation would use much less space than an index.
I have this situation in a similar indexed view -- you can see the code below:

CREATE view [dbo].[VProcessStatus]
WITH SCHEMABINDING
AS
select processstatus, 
      StatusCount = COUNT_BIG(*) 
      FROM DBO.tbltaxtransaction
      GROUP BY processstatus
GO

GRANT SELECT ON [dbo].[VProcessStatus] TO [mydomain\itdevelopment] AS [dbo]
GO


CREATE UNIQUE CLUSTERED INDEX [IDX_VProcessStatus] ON [dbo].[VProcessStatus]
(
    [processstatus] 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 [pscheme_ProcessStatus]([processstatus])
GO


when I look for the space used by this view and the underlying table:

sp_spaceused 'tbltaxtransaction'
go
sp_spaceused 'VProcessStatus'
go


I get the following result:



The only extra is that this indexed view is partitioned according to the PARTITION schema and PARTITION functions below:

CREATE PARTITION SCHEME [pscheme_ProcessStatus] AS PARTITION [pfunc_ProcessStatus] TO ([FG01], [FG02], [FG03], [FG04], [FG05])
GO

CREATE PARTITION FUNCTION [pfunc_ProcessStatus](int) AS RANGE LEFT FOR VALUES (1, 2, 3, 9)
GO
I have now moved from dev to production.
the table FactBackOrder has 4,183,289 rows.

We're in sql server 2012.
Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64) 
    Mar  2 2014 01:25:09 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: )

But I don't want to use Columnstore indexes - these tables are updated very often.
Maybe in sql 2014?           

the original query plan of the query I wanted to improve the performance of is:



the only way I found out to improve the orginal query was to add an index to the table.

create index idx_FactBackOrder_OrderNo_OrderItemSeqNo 
on Facts.FactBackOrder (OrderNo,OrderItemSeqNo) with (online=on)


When I then run the query, I notice it takes longer than the original without index.
the new query plan, after creating the index is as follows:



I immediately drop the newly created index:

drop index idx_FactBackOrder_OrderNo_OrderItemSeqNo on Facts.FactBackOrder


I then create an indexed view:

--drop view [dbo].[VfactBackOder]

CREATE view [dbo].[VfactBackOder]
WITH SCHEMABINDING
AS
select fo.OrderNo, 
       fo.OrderItemSeqNo, 
       COUNT_BIG(*) as the_number
from Facts.FactBackOrder fo
group by fo.OrderNo, fo.OrderItemSeqNo
GO


create the clustered index for the indexed view:

CREATE UNIQUE CLUSTERED INDEX [IDX_VfactBackOder] ON [dbo].[VfactBackOder]
(
    OrderNo, OrderItemSeqNo
)
go


The when I run the query using the newly created indexed view instead of the original query referencing the underlying tables:

select fo.OrderNo, 
       fo.OrderItemSeqNo,
       fo.the_number
from VfactBackOder fo
where fo.the_number &gt; 1


I get a non satisfactory result. 
then I add another index to the view:

CREATE NONCLUSTERED INDEX [&lt;Name of Missing Index, sysname,&gt;]
ON [dbo].[VfactBackOder] ([the_number])
INCLUDE ([OrderNo],[OrderItemSeqNo])
go


Now the query flies like a rocket.
the new query plan is:


And looking at the space used by the indexed view and the underlying table:

sp_spaceused 'Facts.FactBackOrder'
go
sp_spaceused 'VfactBackOder'
go



query is performing well enough, customers happy here.
thanks for all the comments.