database - SQL Server Column From Another Table With Static Value In View -

database - SQL Server Column From Another Table With Static Value In View -

is there way have column table value same within view> example:

select *, (select value tblstudentprefixes prefixname = 'seniorprefix') studentprefix tblstudents

will above nested query executed fro each row? there way execute 1 time , utilize rows.

please note, i'm talking view, not stored procedure. know can done in stored procedure.

this depends on table set up. unless prefixname constrained unique come across errors, subquery returns more 1 row. if not constrained unique, happens unique seniorprefix query executed 1000 times. demonstrate have used next ddl:

create table #tblstudents (id int identity(1, 1), filler char(100)); insert #tblstudents (filler) select top 10000 null sys.all_objects a, sys.all_objects b; create table #tblstudentprefixes (value varchar(10), prefixname varchar(20)); insert #tblstudentprefixes (value, prefixname) values ('a value', 'seniorprefix');

running query gives next io output:

table '#tblstudentprefixes'. scan count 10000, logical reads 10000

table '#tblstudents'. scan count 1, logical reads 142

the key beingness 1000 logical reads on tblstudentprefixes. other problem not beingness constrained unique if have duplicates query fail error:

subquery returned more 1 value. not permitted when subquery follows =, !=, <, <= , >, >= or when subquery used expression.

if can't constrain prefixname unique, can stop executing each row , avoid errors using top:

select *, (select top 1 value #tblstudentprefixes prefixname = 'seniorprefix' order value) studentprefix #tblstudents

the io becomes:

table '#tblstudentprefixes'. scan count 1, logical reads 1

table '#tblstudents'. scan count 1, logical reads 142

however, still recommend switching cross bring together here:

select s.*, p.value studentprefix #tblstudents s cross bring together ( select top 1 value #tblstudentprefixes prefixname = 'seniorprefix' order value ) p;

inspection of execution plans shows sub-select using table spool unnecessary single value:

so in summary, depends on table set whether execute each row, regardless giving optimiser improve chance if switch cross join.

edit

in lite of fact need homecoming rows tblstudent when there no match seniorprefix in tblstudentprefixes, , prefixname not currenty constrianed unique best solution is:

select *, (select max(value) #tblstudentprefixes prefixname = 'seniorprefix') studentprefix #tblstudents;

if constrain unique, next 3 queries produce (essentially) same plan , same results, personal preference:

select *, (select value #tblstudentprefixes prefixname = 'seniorprefix') studentprefix #tblstudents; select s.*, p.value studentprefix #tblstudents s left bring together #tblstudentprefixes p on p.prefixname = 'seniorprefix'; select s.*, p.value studentprefix #tblstudents s outer apply ( select value #tblstudentprefixes prefixname = 'seniorprefix' ) p;

sql-server database view