Groups > comp.databases.ms-sqlserver > #178

Re: Use External value and use it from some views

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 <esquel@sommarskog.se>
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	<Xns9EC6F33F2C632Yazorman@127.0.0.1> (permalink)
References	<98788af4-5f23-4e7f-a516-9195e5b7a41a@o10g2000vbg.googlegroups.com> <Xns9E9C5ACDD31F4Yazorman@127.0.0.1> <d08a7fc8-844b-4d49-bea2-b21b0e622176@v31g2000vbs.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

Show key headers only | View raw

omtechguy (omtechguy@gmail.com) writes:
> On 2 ???, 10:55, Erland Sommarskog <esq...@sommarskog.se> 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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

Re: Use External value and use it from some views omtechguy <omtechguy@gmail.com> - 2011-04-12 23:15 -0700
  Re: Use External value and use it from some views Erland Sommarskog <esquel@sommarskog.se> - 2011-04-13 23:54 +0200

csiph-web