SQL Server - Computed column counting over partition
<p>I am fairly new to creating tables in SQL Server - Especially to computed columns - And am looking to make sure that I'm not creating a terribly inefficient database.</p>

<p>As a simplified example of what I'm trying to accomplish, suppose I have the following table definition:</p>

<pre><code>CREATE TABLE [dbo].[MyTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Strategy] [varchar](30) NOT NULL,
    [Strategy_Variation] [int] NOT NULL
)
</code></pre>

<p>With simplified data looking as follows:</p>

<pre><code>ID:      Name:       Strategy:      Strategy_Variation:
1        Name1       Strat1         1
2        Name2       Strat2         1
3        Name3       Strat2         2
4        Name4       Strat1         2
5        Name5       Strat1         3
</code></pre>

<hr>

<p>Basically, my question comes about regarding the <code>Strategy_Variation</code> column. What I would like to have happen would be, for each <code>Strategy</code>, increment the variation based upon order entered into the table (using the <code>ID</code> incrementing identity index as the way to order the entries). Or, via SQL:</p>

<pre><code>COUNT(Strategy) over(partition by Strategy order by ID)
</code></pre>

<p>My question is whether this is or is not a good idea to have this as a computed column in my table definition, or if I simply leave this kind of column out completely and add it into a view, say, to keep this table leaner.</p>

<p>Overall, I'm a newbie to this and would love any pointers as to how a seasoned DB admin would handle such a situation.</p>

<p>Thanks!</p>