title: TSQL OVER clause: COUNT(*) OVER (ORDER BY a)
body: <p>This is my code:</p>
  
  <pre><code>USE [tempdb];
  GO
  
  IF OBJECT_ID(N'dbo.t') IS NOT NULL
  BEGIN
      DROP TABLE dbo.t
  END
  GO
  
  CREATE TABLE dbo.t
  (
      a NVARCHAR(8),
      b NVARCHAR(8)
  );
  GO
  
  INSERT t VALUES ('a', 'b');
  INSERT t VALUES ('a', 'b');
  INSERT t VALUES ('a', 'b');
  INSERT t VALUES ('c', 'd');
  INSERT t VALUES ('c', 'd');
  INSERT t VALUES ('c', 'd');
  INSERT t VALUES ('c', 'd');
  INSERT t VALUES ('e', NULL);
  INSERT t VALUES (NULL, NULL);
  INSERT t VALUES (NULL, NULL);
  INSERT t VALUES (NULL, NULL);
  INSERT t VALUES (NULL, NULL);
  GO
  
  SELECT  a, b,
      COUNT(*) OVER (ORDER BY a)
  FROM    t;
  </code></pre>
  
  <p>On <a href="http://msdn.microsoft.com/en-us/library/ms189461.aspx" rel="noreferrer">this page of BOL</a>, Microsoft says that:</p>
  
  <blockquote>
    <p>If PARTITION BY is not specified, the function treats all rows of the
    query result set as a single group.</p>
  </blockquote>
  
  <p>So based on my understanding, the last <code>SELECT</code> statement will give me the following result. Since all records are considered as in one single group, right? </p>
  
  <pre><code>a        b        
  -------- -------- -----------
  NULL     NULL     12
  NULL     NULL     12
  NULL     NULL     12
  NULL     NULL     12
  a        b        12
  a        b        12
  a        b        12
  c        d        12
  c        d        12
  c        d        12
  c        d        12
  e        NULL     12
  </code></pre>
  
  <p>But the actual result is:</p>
  
  <pre><code>a        b        
  -------- -------- -----------
  NULL     NULL     4
  NULL     NULL     4
  NULL     NULL     4
  NULL     NULL     4
  a        b        7
  a        b        7
  a        b        7
  c        d        11
  c        d        11
  c        d        11
  c        d        11
  e        NULL     12
  </code></pre>
  
  <p>Anyone can help to explain why? Thanks.</p>