Computed field references

I am currently developing a stored procedure that includes a number of computed fields. Is it possible to reference a computed value, (eg. FLdA), or do I need to CREATE a temp file and then reference the FldA and FldB values. I have simplified my code, it is much more extensive in that there are numerous WHEN clauses attached to each FldA and FldB computation.

SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN FldA * CurValue
WHEN ... THEN FldB * CurValue
etc.I'm not sure I understand the question...Do you want to reference the value again inside the sproc?

Then Yes...use a local table variable...

If it's being part of a result set being passed back, then you're already refrencing it...

I'm confused...|||I want to reference the value within the sproc and pass only those records where the OldValue is not equal to the NewValue. In the case I mentioned, I am trying to reference FldA and FldB to compute the NewValue from within the same SELECT stmt, but SQL does not let me reference the FldA and FldB computed values. Is that as clear as mud?|||Reference them, where? In the same query? Or later on in the sproc.

If it's later on in the sproc

SELECT <whatever> INTO #TEMP FROM <whatever>

Then just query the local temp table...

Is that what you mean?|||I'm trying to reference them in the same query.

The INSERT .. INTO stmt seems cumbersome as it appears I would have to define each field as part of the CREATE TABLE stmt. Can't see why it doesn't just pickup the data types from the TABLE.|||Well it's not data type is it...it's column names

Well do this...Keep your computed stuff isolated...and join to a derived table

SELECT * FROM (SELECT <your derived columns> FROM table join table ect) AS A
LEFT JOIN B ON a.key = b.key
WHERE <now you can reference the derived column name> = 'bananas'

Whatever...

I fyou make the derivation this derived table you'll be able to reference the column names you made up...|||Why so complicated?

select * from (
SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END * CurValue
WHEN ... THEN CASE .... * CurValue
) x
where OldValue != NewValue

In other words, instead of trying to reference FldA, use its CASE...END when calculating NewValue. Same with FldB.|||I had mentioned earlier that the code was simplified. The CASE logic is fairly complex, could be up to 20 lines of code. That would mean that I would have to repeat the code everytime the field ('FldA') was referenced. I may just leave the logic in VBA code as it seems a lot easier to manipulate fields in code. My goal was to restrict the query ouput lines so the Access code would run quicker.|||Thanks Brett ... I'll give it a go.|||Here's a model

USE Northwind
GO
SELECT SUM(OutOfBusinessDays) AS VacationDays
FROM (
SELECT ShipLate-ShipDelay AS OutOfBusinessDays
FROM (
SELECT DATEDIFF(dd,OrderDate,ShippedDate) As ShipDelay
, DATEDIFF(dd,OrderDate,RequiredDate) As ShipLate
FROM Orders
) AS XXX
) AS DerivedTableName

Composite primary key

Hello,

Does composite primary key affect performance on the table that contains the composite primary key or tables that references this table?

When composite primary key should be used?

In my experience composite keys are a through back to old systems that where converted to databases such as SQL Server / Oracle.

As a developer, rather than a DBA, composite keys are a real pain to program against especially in the modern world of object programming, data objects etc. I would always use a single unique value to identify a row rather than using composite keys. If you regularly query dataing using mulitple values from columns in the where statement then by all means create a normal index based on these columns (in the order that you use them in the where statement).

|||

so considering performance issue!! does databases with such structure perform worse than ones with single keys!! i mean directly or indirectly.

*Directly: if it has a bad performance in join clauses or in search criteria.

*Indirectly: in programming specially in .Net when using datagrids and such controls that generate relations and act with whole tables in runtime.

Thnx

compilation and execution plan

Hi guys,
I'm confused on following situation, - I'm definitely missing something. I
have SP which plan is already in cache. The SP references table MyTable. I
setup trace on recompile and all cache related events and it runs during
test nonstop.
Here is what I did (before dash) and
what I saw in Profiler (after dash) on each action:
DBCC FREEPROCCACHE - sp:cacheremove
exec MySP - sp:cachemiss, sp:cacheinsert
drop procedure MySP - sp:cacheremove
CREATE PROCEDURE MySP - none
exec MySP - sp:cachemiss, sp:cacheinsert
exec sp_recompile MyTable - none
exec MySP - sp:ExecContextHit, sp:cacheremove,
sp:recompile,
sp:cachemiss, sp:cacheinsert
My question is Why I can't see sp:recompile events
when I execute SP after DBCC and then after DROP/CREATE statements?
My understanding is that if plan is removed from cache
it doesn't exist anymore and has to be compiled to enable SP to execute next
time (what we see when SP is executed after
exec sp_recompile MyTable)?
But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
inserted into cache but from where? On which step did it get compiled?
I'd be highly grateful for any information.
Alex
Alex,
The difference is in the difference between the words "compile" and "recompile". If you drop and
create the proc, the proc plan doesn't exist and fir the first execution you get a compilation. Same
if you empty the proc cache.
However, if you sp_recompile a table that the proc is using, the plan is still in cache and for the
next execution SQL Server will need to REcompile that plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_remove_this_@.telus.net> wrote in message news:pSepd.2111$cE3.1783@.clgrps12...
> Hi guys,
> I'm confused on following situation, - I'm definitely missing something. I
> have SP which plan is already in cache. The SP references table MyTable. I
> setup trace on recompile and all cache related events and it runs during
> test nonstop.
> Here is what I did (before dash) and
> what I saw in Profiler (after dash) on each action:
> DBCC FREEPROCCACHE - sp:cacheremove
> exec MySP - sp:cachemiss, sp:cacheinsert
> drop procedure MySP - sp:cacheremove
> CREATE PROCEDURE MySP - none
> exec MySP - sp:cachemiss, sp:cacheinsert
> exec sp_recompile MyTable - none
> exec MySP - sp:ExecContextHit, sp:cacheremove,
> sp:recompile,
> sp:cachemiss, sp:cacheinsert
> My question is Why I can't see sp:recompile events
> when I execute SP after DBCC and then after DROP/CREATE statements?
> My understanding is that if plan is removed from cache
> it doesn't exist anymore and has to be compiled to enable SP to execute next
> time (what we see when SP is executed after
> exec sp_recompile MyTable)?
> But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
> inserted into cache but from where? On which step did it get compiled?
> I'd be highly grateful for any information.
> Alex
>
|||Thank you Tibor,
So Profiler has event related to recompilation (sp:recompile) and doesn't
have event related to compilation, that's why we see only sp:cachemiss,
sp:cacheinsert and nothing in between?
Thank you,
Alex
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
"recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
news:pSepd.2111$cE3.1783@.clgrps12...[vbcol=seagreen]
I[vbcol=seagreen]
I[vbcol=seagreen]
next[vbcol=seagreen]
It's
>
|||Yep. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_removethis_@.healthmetrx.com> wrote in message
news:10qc0uncauftpd9@.corp.supernews.com...
> Thank you Tibor,
> So Profiler has event related to recompilation (sp:recompile) and doesn't
> have event related to compilation, that's why we see only sp:cachemiss,
> sp:cacheinsert and nothing in between?
>
> Thank you,
> Alex
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> "recompile". If you drop and
> you get a compilation. Same
> still in cache and for the
> news:pSepd.2111$cE3.1783@.clgrps12...
> I
> I
> next
> It's
>
|||On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
wrote:
>But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
>inserted into cache but from where? On which step did it get compiled?
>I'd be highly grateful for any information.
sp_recompile only marks the SP, it doesn't actually *do* the
compilation at that time, SQLServer "lazily" waits for the next call
for execution, and then compiles it.
J.
|||JXStern wrote:
> On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
> wrote:
> sp_recompile only marks the SP, it doesn't actually *do* the
> compilation at that time, SQLServer "lazily" waits for the next call
> for execution, and then compiles it.
> J.
This "laziness" avoids unnecessary compilations.
However, there is also a technical reason that SP's are not recompiled
immediate after a schema change or sp_recompile. This is because the
compilation phase uses the stored procedure parameters so the most
representative statistics can be used, and thus will not 'guess'
parameter values. This basically means that SP's cannot be compiled
without calling them (with the appropriate parameters).
Gert-Jan
|||Not quite.
The explanation of the terms is fine, but this is not how Profiler records
them.
If you actually look at what happens when you issue the sp_recompile, you'll
see a SP:CacheRemove event. Then the next time you call the proc, you'll see
SP:CacheInsert just as for the first time.
AFAIK, the SP:Recompile event is ONLY generated in the situation where a
stored procedure is recompiled while it is executing. This is caused by
activities in the sproc like DDL, updating statistics or changing a SET
option.
See KB 308737 for more details.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
> "recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
> you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
> still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
> news:pSepd.2111$cE3.1783@.clgrps12...
>
|||Thank you guys for all that information
Alex
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41A77B97.DA602FD7@.toomuchspamalready.nl...[vbcol=seagreen]
> JXStern wrote:
It's
> This "laziness" avoids unnecessary compilations.
> However, there is also a technical reason that SP's are not recompiled
> immediate after a schema change or sp_recompile. This is because the
> compilation phase uses the stored procedure parameters so the most
> representative statistics can be used, and thus will not 'guess'
> parameter values. This basically means that SP's cannot be compiled
> without calling them (with the appropriate parameters).
> Gert-Jan

compilation and execution plan

Hi guys,
I'm confused on following situation, - I'm definitely missing something. I
have SP which plan is already in cache. The SP references table MyTable. I
setup trace on recompile and all cache related events and it runs during
test nonstop.
Here is what I did (before dash) and
what I saw in Profiler (after dash) on each action:
DBCC FREEPROCCACHE - sp:cacheremove
exec MySP - sp:cachemiss, sp:cacheinsert
drop procedure MySP - sp:cacheremove
CREATE PROCEDURE MySP - none
exec MySP - sp:cachemiss, sp:cacheinsert
exec sp_recompile MyTable - none
exec MySP - sp:ExecContextHit, sp:cacheremove,
sp:recompile,
sp:cachemiss, sp:cacheinsert
My question is Why I can't see sp:recompile events
when I execute SP after DBCC and then after DROP/CREATE statements?
My understanding is that if plan is removed from cache
it doesn't exist anymore and has to be compiled to enable SP to execute next
time (what we see when SP is executed after
exec sp_recompile MyTable)?
But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
inserted into cache but from where? On which step did it get compiled?
I'd be highly grateful for any information.
AlexAlex,
The difference is in the difference between the words "compile" and "recompi
le". If you drop and
create the proc, the proc plan doesn't exist and fir the first execution you
get a compilation. Same
if you empty the proc cache.
However, if you sp_recompile a table that the proc is using, the plan is sti
ll in cache and for the
next execution SQL Server will need to REcompile that plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_remove_this_@.telus.net> wrote in message news:pSepd.2111$cE3.1783@.clgrps12...[v
bcol=seagreen]
> Hi guys,
> I'm confused on following situation, - I'm definitely missing something. I
> have SP which plan is already in cache. The SP references table MyTable. I
> setup trace on recompile and all cache related events and it runs during
> test nonstop.
> Here is what I did (before dash) and
> what I saw in Profiler (after dash) on each action:
> DBCC FREEPROCCACHE - sp:cacheremove
> exec MySP - sp:cachemiss, sp:cacheinsert
> drop procedure MySP - sp:cacheremove
> CREATE PROCEDURE MySP - none
> exec MySP - sp:cachemiss, sp:cacheinsert
> exec sp_recompile MyTable - none
> exec MySP - sp:ExecContextHit, sp:cacheremove,
> sp:recompile,
> sp:cachemiss, sp:cacheinsert
> My question is Why I can't see sp:recompile events
> when I execute SP after DBCC and then after DROP/CREATE statements?
> My understanding is that if plan is removed from cache
> it doesn't exist anymore and has to be compiled to enable SP to execute ne
xt
> time (what we see when SP is executed after
> exec sp_recompile MyTable)?
> But instead we see sp:cacheinsert event right after sp:cachemiss event. It
's
> inserted into cache but from where? On which step did it get compiled?
> I'd be highly grateful for any information.
> Alex
>[/vbcol]|||Thank you Tibor,
So Profiler has event related to recompilation (sp:recompile) and doesn't
have event related to compilation, that's why we see only sp:cachemiss,
sp:cacheinsert and nothing in between?
Thank you,
Alex
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
"recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
news:pSepd.2111$cE3.1783@.clgrps12...
I[vbcol=seagreen]
I[vbcol=seagreen]
next[vbcol=seagreen]
It's[vbcol=seagreen]
>|||Yep. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_removethis_@.healthmetrx.com> wrote in message
news:10qc0uncauftpd9@.corp.supernews.com...
> Thank you Tibor,
> So Profiler has event related to recompilation (sp:recompile) and doesn't
> have event related to compilation, that's why we see only sp:cachemiss,
> sp:cacheinsert and nothing in between?
>
> Thank you,
> Alex
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> "recompile". If you drop and
> you get a compilation. Same
> still in cache and for the
> news:pSepd.2111$cE3.1783@.clgrps12...
> I
> I
> next
> It's
>|||On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
wrote:
>But instead we see sp:cacheinsert event right after sp:cachemiss event. It'
s
>inserted into cache but from where? On which step did it get compiled?
>I'd be highly grateful for any information.
sp_recompile only marks the SP, it doesn't actually *do* the
compilation at that time, SQLServer "lazily" waits for the next call
for execution, and then compiles it.
J.|||JXStern wrote:
> On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
> wrote:
> sp_recompile only marks the SP, it doesn't actually *do* the
> compilation at that time, SQLServer "lazily" waits for the next call
> for execution, and then compiles it.
> J.
This "laziness" avoids unnecessary compilations.
However, there is also a technical reason that SP's are not recompiled
immediate after a schema change or sp_recompile. This is because the
compilation phase uses the stored procedure parameters so the most
representative statistics can be used, and thus will not 'guess'
parameter values. This basically means that SP's cannot be compiled
without calling them (with the appropriate parameters).
Gert-Jan|||Not quite.
The explanation of the terms is fine, but this is not how Profiler records
them.
If you actually look at what happens when you issue the sp_recompile, you'll
see a SP:CacheRemove event. Then the next time you call the proc, you'll see
SP:CacheInsert just as for the first time.
AFAIK, the SP:Recompile event is ONLY generated in the situation where a
stored procedure is recompiled while it is executing. This is caused by
activities in the sproc like DDL, updating statistics or changing a SET
option.
See KB 308737 for more details.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
> "recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
> you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
> still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
> news:pSepd.2111$cE3.1783@.clgrps12...
>|||Thank you guys for all that information
Alex
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41A77B97.DA602FD7@.toomuchspamalready.nl...
> JXStern wrote:
It's[vbcol=seagreen]
> This "laziness" avoids unnecessary compilations.
> However, there is also a technical reason that SP's are not recompiled
> immediate after a schema change or sp_recompile. This is because the
> compilation phase uses the stored procedure parameters so the most
> representative statistics can be used, and thus will not 'guess'
> parameter values. This basically means that SP's cannot be compiled
> without calling them (with the appropriate parameters).
> Gert-Jan