2

I'm trying to think about the best way to do this. If i have a table like this in sql server:
StudentPointsTable
StudentID
TestDate
Points

I need to have Averages and standard deviation calculated as well for a student. I know i can create a view, or create a new table with these computed columns, or add the computed columns to this table.

Not sure if there are any other options but in terms of performance, what would be the best way to do this?

Improve this question
2
  • 1
    How big is your table, did you test using all the methods you described? If you do not persist and just run direct tsql is that a problem for your application? Do you need average and stdev for all students all the time or a subset of students?
    – SqlWorldWide
    Commented Apr 10, 2017 at 12:36
  • The table has almost 5 million rows. It's not a problem (besides the fact that I might want to index it), but what is your reasoning? Yes, users need the mentioned computations all the time for all students.
    – SQLserving
    Commented Apr 10, 2017 at 12:41

1 Answer 1

Reset to default
5
  1. Creating view will not give you any performance gain but may help you with abstraction of underlying object and management of security from users.

  2. Using Index view can definitely help you by doing the calculations before hand but you need to be careful as explained in detail here.

    SQL Server must always ensure that the data in the index and base tables is synchronized, so we need to be careful when adding indexes to views. Every time an underlying column has a new row added or deleted, or is updated, SQL Server must maintain every clustered and non-clustered index, whether on the base table or the referenced indexed view. This will lead to additional writes, which can decrease performance.

  3. I would not create computed column in the same table as you will have redundant data (average for a student in every row for that student) in your table. Using extra storage, less number of pages in memory etc.

  4. You should also consider indexing strategy based on how you query this table. Meaning is it grouping by one student, student + date, one student + range of date?

Improve this answer
4
  • I'd also say this could be a good use case for an inline table valued function.
    – Gareth Lyons
    Commented Apr 10, 2017 at 13:16
  • 2
    @GarethLyons that would basically be a view and still have to be executed against the table. Not sure what it would buy them from a perf standpoint.
    – Erik Darling
    Commented Apr 10, 2017 at 14:53
  • @sp_BlitzErik absolutely, wasn't offering it as a better performing solution, just an alternative method. Like you say, they'd be pretty much the same code.
    – Gareth Lyons
    Commented Apr 10, 2017 at 15:08
  • So you're saying Index view is pay on write. I think oracle calls them materialized views? I am thinking if the calculation comes from within the row, i use a calculated column. if the calculation comes from an aggregate somewhere, use view
    – JDPeckham
    Commented Dec 15, 2018 at 15:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.