SQL Server与Oracle在 count() over() 函数的差异

最近一个项目使用了oracle（11g），在编写sql时，要知道数据总条数，可以这么写：

select id,
       v,
	   count(*) over() as num
from 
(
	select 1 as id,'a' v from dual
	union all
	select 2 as id,'b' v from dual
	union all
	select 3 as id,'c' v from dual
	union all
	select 4 as id,'d' v from dual
	union all
	select 1 as id,'e' v from dual
	union all
	select 2 as id,'f' v from dual
	union all
	select 3 as id,'f' v from dual
)tb

sql server的写法差别不大，只要把 from dual去掉就可以了：

select id,
       v,
	   count(*) over() as num
from 
(
	select 1 as id,'a' v 
	union all
	select 2 as id,'b' v 
	union all
	select 3 as id,'c' v 
	union all
	select 4 as id,'d' v 
	union all
	select 1 as id,'e' v
	union all
	select 2 as id,'f' v 
	union all
	select 3 as id,'f' v
)tb

如果我要的是不重复的id字段的个数，oracle可以这么写：

select id,
       v,
	   count(distinct id) over() as num
from 
(
	select 1 as id,'a' v from dual
	union all
	select 2 as id,'b' v from dual
	union all
	select 3 as id,'c' v from dual
	union all
	select 4 as id,'d' v from dual
	union all
	select 1 as id,'e' v from dual
	union all
	select 2 as id,'f' v from dual
	union all
	select 3 as id,'f' v from dual
)tb

而在sql server 2012中写成这样：count(distinct id) over() as num，则会直接报错：

消息 10759，级别 15，状态 1，第 3 行
OVER 子句不允许使用 DISTINCT。
消息 102，级别 15，状态 1，第 19 行
“tb”附近有语法错误。

说明在对count() over()的支持上，sql server还要进一步加强.