title: How to create identity column in SQL Server using with statement
body: <p>I am looking for some help in SQL, I am using following query</p>
  
  <pre><code>with t AS 
  (
      select  
          EmpID, mgrid, HierarchyLevel, Description 
      from 
          empdatatest 
  )
  select * 
  from t 
  order by empid
  </code></pre>
  
  <p>I want a way so that table T has an identity column </p>
  
  <p>Data output should be like</p>
  
  <pre><code>ID EmpID mgrid HierarchyLevel Description
  ------------------------------------------
  1   201    7        1            Partner
  2   202   201       2          Senior Manager
  3   221   202       3           Manager
  4   343   221       4           employee
  5   534   221       4           employee
  6   552   221       4           employee
  </code></pre>