sql server - SQL view, performance and count from one-to-many relationship -

i need forming basic sql-views bunch of tables. here's quick overview

• i've claimdetail table , has got lookup fields statusid, brandid, salespersonid, etc..
• as usual, lookup fields map master tables masterstatus, masterbrand, ... {structure: id, title}
• also there're 2 other tables comments , files. claim can have multiple comments , multiple files.
• i need display dashboard list of claims. need display titles master tables , count of comments & files.

now, i've 2 views of dashboard 1 users of type customer limited details , 1 detailed view meant internal users. can customer view sub-set of internal view.

i see 2 options -

1. opt#1: create single vw_internal view , use fetch data both users.
2. opt#2: create vw_customer has onlt fields required customer , create vw_internal like: vw_customer inner join master tables. in short i'll extend basic vw_customer include more fields.

does option#2 make sense speed , performance point of view? opt#1 simple considering huge number of records want make sure customers don't have wait bit longer lookups not going included in dashboard.

finally, there way last feature mentioned? getting count of comments , files has one-to-many relationship claimdetail table. need count or atleast boolean field says whether claim has comments or not (same files) - if'll false if count = 0. i'm concerned performane impact due feature.

thanks in advance.

with regards view definitions, i'd build 2 views, , i'd make them separate--neither view reference other. allow optimize queries independantly, , avoids problems you'd views layered on top of views; many layers can make databases management, maintenance, , refactoring particularly challenging.

as data aggregation, common tactics include following. compare, contrast, test, , extrapolate see fits best in environment:

subqueries

select mt.id, st1.howmany, st2.howmanyother, <etc>  maintable mt   inner join (select id, count(*) howmany                subtable1                group id) st1    on st1.id = mt.id   inner join (select id, count(*) howmany                subtable2                group id) st2    on st2.id = mt.id 

fairly straightforward, though subqueries might kind of costly, proper indexing.

count(distinct xx)

select mt.id, count(distinct st1.uniquekey) howmany, count(distinct st2.uniquekey) howmanyother, <etc>  maintable mt   inner join subtable1 st1    on st1.id = mt.id   inner join subtable2    on st2.id = mt.id 

this requires single unique column in "subtables", , gets messy if have deal outer joins or nulls.

---

added

---

first, replacing inner joins (left) outer joins in either of above queries produce 0+ counts subtables, long make sure count being done on “right” table (because nulls don’t tallied). figure out performs best on environment, you’d have write , test both queries. i’d guess second, since first requires table scans on tables of subqueries while second performs joins , may optimize better, sql query optimizer smarter me (because knows indexes , has distribution histograms of data) want see comes with.

with regards “layered views”, if i’m following logic right, i’d recommend building internal view complex/comprehensive query (all joins, relevant columns), , build customer view that’s simple as

select <customeronlycolumns>  vw_internal