1

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.

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

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
)

With simplified data looking as follows:

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

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

COUNT(Strategy) over(partition by Strategy order by ID)

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.

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

Thanks!

John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • 1
    I sugest you to use that column into a view, scalar function or the way you put, because when you have no much information, the column will be ok, even if it need to recalc all time. But if the table grow to much, I'll keep one information at the table that you can use only when you need as a function, in example. Everytime you modify the Strategy column, all the Strategy_Variation will be updated and that is not good, talking about performance or the way you store your data. – warwreken Mar 09 '16 at 14:06
  • 1
    http://stackoverflow.com/questions/4340648/when-are-computed-columns-appropriate – Tab Alleman Mar 09 '16 at 14:23
  • Thanks both of you for your help - I checked and realized I couldn't even put this into my table definition even if I wanted to since it relies on other rows in the table... So into a view it goes. Thank you!! – John Bustos Mar 09 '16 at 14:31

0 Answers0