April 25, 2005 at 12:29 pm
Hello,
Is it possible to create an index from a computed value that is a variable?
Such as:
UPDATE ITSTestTransfers.dbo.PARTEOrderManifest
SET bpcsInvn = b.bpcsInvn, invheadid = b.invheadid, campaignid = b.campaignid,
campName = company_code
FROM ITSTestTransfers.dbo.PARTEOrderManifest a
INNER JOIN ITSTestTransfers.dbo.PARTEOrderH b on a.order_num = b.order_num
WHERE a.flags & 8 = 0 and a.flags & 32 = 0
The a.Flags = 0 is a computed variable derived from another procedure.
Any help would be appreciated
April 25, 2005 at 12:40 pm
The answer is yes and no. Or is it no and yes?
You might be better off stating your issue/objective than asking this question. What are you trying to accomplish?
April 25, 2005 at 1:12 pm
In a perfect world….I would like to create an index on a computed variable. We use a lot “check digits” binary numbers throughout our stored procedures.
Example:
Begin select @bCount = count(*)
from itsTestTransfers.dbo.PARTEOrderH
where flags & 8 = 8 and flags & 32 = 0
end
insert ZTErejectLog (sysId, orderId, order_num, reason)
select @sysId, a.order_num , a.order_num, @jobStep
from itsnfsrv05.exactadb.dbo.exp_pk_header a
left join itsTestTransfers.dbo.PARTEOrderH b on a.order_num = b.order_num
where export_Status = '1' and left(a.order_num, 1) '-' and (b.order_Num is null or b.flags & 8 = 8)
more statements……………
The flags are being used as a check digit where the sum of the digits would follow a logical progression.
If flags = 0 then go update something
If flags = 32 then go insert into something
If flags = 12 then delete
If flags = 44 rollback Trans
Even though the flags variable is not stored anywhere I would still like to create an index on it.
The reason behind this is a deadlocking issue competing for the same flag on two different tables.
April 25, 2005 at 1:53 pm
from your examples I would assume that when you said "computed variable" you are actually saying "computed column" if that is the case then yes you can! if that is not the case then can you said where is that "flag" column coming from in your statements?
by the way you may want to combine as many equal binary operations as possible like:
flags & 8 = 8 and flags & 32 = 0
can be chaged to
flags & 40 = 8 -- 8+32
* Noel
April 26, 2005 at 12:26 am
>Even though the flags variable is not stored anywhere I would still like to create an index on it.
If it is a variable, then no, you cannot create an index on it. There is no reason, since it is only a single scalar value in memory.
>The reason behind this is a deadlocking issue competing for the same flag on two different tables.
If it is a variable then there cannot be a deadlocking issue.. And an index is not used to solve a deadlock.
If noeld's answer does not solve the problem, could you in text describe what you are trying to accomplish and what issues you are having.
April 27, 2005 at 9:02 am
Sorry for not being clear.
What I am trying to accomplish is:
Using - Where Flags & 2 = 0
Flags are a preexisting index. What I would like to do is have [Flags & 2] be a separate index. Is this possible?
Thanks
April 27, 2005 at 9:12 am
Ah...
Suppose you create a computed column with [Flag & 2] and set up an index on it, that column may no be "selective" enough, therefore you have waisted space and resources in something that is not giving you extra speed. On the other hand (I don't know your data) if it is selective enough you "may" get the speed that you need.
To be honest the My first approach would be to ENCODE the bits with characters in acomputed column so that you can use the like 'x_x_x%' operator and that may take you where you want
hth
* Noel
April 27, 2005 at 3:20 pm
This is one of the reasons why using bitmasks coded in ints is not really a good thing in SQL Server. I would probably store the flags in a separate table and join it in the query.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply