Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Use External value and use it from some views Date: Wed, 13 Apr 2011 23:54:43 +0200 Organization: Erland Sommarskog Lines: 33 Message-ID: References: <98788af4-5f23-4e7f-a516-9195e5b7a41a@o10g2000vbg.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx02.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="8925"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/KD+fNM+tpZQVT20RKioh9" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:1cQqoM7kbqHxhVEE3YLvFVGie20= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:178 omtechguy (omtechguy@gmail.com) writes: > On 2 ???, 10:55, Erland Sommarskog wrote: >> omtechguy (omtech...@gmail.com) writes: >> Are these the actual queries, or are they just sketches of the real >> views? >> >> If they are the real McCoy is there an index on TheValue? Is that a >> clustered or a non-clustered index? >> >> With a hardcoded value, the optimizer has more exact information than >> if you have something variable. >> >> I would try an inline-table function, and then use OPTION(RECOMPILE) >> when I query the function. > > Can you please share an example? CREATE FUNCTION myfunc(@myglobbval int) RETURNS TABLE AS RETURN (SELECT * FROM MyTable where TheValue = @myglobval) go SELECT @myglobvalue = myglobalvalue FROM MySettingTable SELECT * FROM myfunc(@myglobval) OPTION (RECOMPILE) -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx