5
\$\begingroup\$

Table Structure

ApprovalOrder int

EntityCode varchar

CostCentre varchar

DelegationCode varchar

ProjectCode varchar

RoleGroup varchar

Position varchar

DelegationText varchar

DelegationAmount money

Active bit

Query / Explanation

The purpose is to pull all records for a delegation chain and add a new record at the beginning of the approval chain. RowID is used when talking with an external system and is a required part of the returned results.

I'm wondering if there's a better way that I could have constructed this query instead of the approach I took below? Beyond that I'm definitely interested in general performance / formatting suggestions.

/* Define ye values */
declare @role_group as int = 55
declare @position as char(55) = 'Asset Analyst'
declare @delegation_text as int = null
declare @delegation_amount as int = 0

declare @entity as int = 1410
declare @project_code as int = 10022
declare @delegation_code as varchar(10) = 'NC'

select top 1
    '1' + cast(@entity as varchar(10)) + @delegation_code as 'RowID' ,
    1 as 'ApprovalOrder' ,
    @entity as 'EntityCode',
    CostCentre as 'CostCentre',
    @delegation_code as 'DelegationCode' ,
    @project_code as 'ProjectCode' ,
    @role_group as 'RoleGroup' ,
    @position as 'Position',
    @delegation_text as 'DelegationText' ,
    @delegation_amount as 'DelegationAmount' ,
    Active 
from    
    workflow.delegation_engine
where   
    EntityCode = @entity
    and ProjectCode = @project_code
    and DelegationCode = @delegation_code   

union
select
    de.RowID ,
    cast(de.ApprovalOrder as int) + 1 as 'ApprovalOrder' ,
    de.EntityCode ,
    de.CostCentre ,
    de.DelegationCode ,
    de.ProjectCode ,
    de.RoleGroup ,
    de.Position ,
    de.DelegationText ,
    de.DelegationAmount ,
    de.Active 
from
    (
        select  
            cast(de.ApprovalOrder + 1 as varchar(4))
                + cast(de.EntityCode as varchar(8))
                + cast(de.DelegationCode as varchar(12)) as 'RowID' ,
            ApprovalOrder ,
            EntityCode ,
            CostCentre ,
            DelegationCode ,
            ProjectCode ,
            RoleGroup ,
            Position ,
            DelegationText ,
            DelegationAmount ,
            Active
        from    
            workflow.delegation_engine de
    ) de
where       
    de.EntityCode = @entity
    and de.ProjectCode = @project_code
    and de.DelegationCode = @delegation_code
\$\endgroup\$
  • \$\begingroup\$ I like the formatting style (i.e. the indentation and separation of each condition onto its own line), but (without trying to execute it) I don't see the purpose of the subquery. \$\endgroup\$ – Dr. Wily's Apprentice Jan 12 '12 at 1:00
  • \$\begingroup\$ @Dr.Wily'sApprentice Ah, a bi-product of cutting it down for posting. The full query orders by RowID and the only way to do that (to the best of my knowledge) is by using the sub-query \$\endgroup\$ – Michael A Jan 12 '12 at 1:04
  • \$\begingroup\$ You can do order by 1, 2, 3 to say "order by the first column, then the second, then the third". You should be able to use that to order by your calculated column so that you don't have to use the subquery. Also, I would suggest considering adding an actual "RowID" computed column to the table schema. \$\endgroup\$ – Dr. Wily's Apprentice Jan 12 '12 at 1:06
  • \$\begingroup\$ @Dr.Wily'sApprentice That's a very good point actually - hadn't considered that, had the rigid idea in my head that I had to sort by RowID. Thank-you. \$\endgroup\$ – Michael A Jan 12 '12 at 1:27
5
\$\begingroup\$

My suggestion is to create a "RowID" computed column on the workflow.delegation_engine table.

ALTER TABLE workflow.delegation_engine ADD [RowID] AS
    cast(de.ApprovalOrder + 1 as varchar(4))
    + cast(de.EntityCode as varchar(8))
    + cast(de.DelegationCode as varchar(12))

Your comment indicates that you might be ordering the query on this column. In that case, you might also want to apply an index on this column. I haven't tried this out, so I don't know for sure whether an index on this column is allowed, but I think it does satisfy all of the requirements.


Also, you might want/need to pad your values with leading zeros in order to ensure that your calculated RowID is unique. In that case, you might want to try the following:

    right('0000' + cast(de.ApprovalOrder + 1 as varchar(4)), 4)
    + right('00000000' + cast(de.EntityCode as varchar(8), 8)
    + right('000000000000' + cast(de.DelegationCode as varchar(12), 12)

This would provide uniqueness and would preserve ordering (i.e. ApprovalOrder 10 comes after ApprovalOrder 2). If you don't care about ordering, then you might not need the leading zeros; you could just insert delimiters to ensure uniqueness.

    cast(de.ApprovalOrder + 1 as varchar(4))
    + '|' + cast(de.EntityCode as varchar(8))
    + '|' + cast(de.DelegationCode as varchar(12))

This would protect against a scenario where one row has ApprovalOrder: 1, EntityCode: 'ABC', DelegationCode: 'DEF' and another row has ApprovalOrder: 1, EntityCode: 'AB', DelegationCode: 'CDEF'. In that case, the RowID for both rows would be '2ABCDEF'. Adding the delimiters ensures that the first row would have RowID: '2|ABC|DEF' and the second row would have RowID: '2|AB|CDEF'.


Alternatively, if you can't or don't want to put a "RowID" computed column on the table, then you should be able to just structure the query as shown below. Note that I've included an "order by" clause that was left out of the original query. Comments indicate that the reason for the subquery in the original query was to order by the "RowID" column. The order by 1 clause in the query below accomplishes the same result.

select
    cast(de.ApprovalOrder + 1 as varchar(4))
        + cast(de.EntityCode as varchar(8))
        + cast(de.DelegationCode as varchar(12)) as 'RowID' ,
    cast(de.ApprovalOrder as int) + 1 as 'ApprovalOrder' ,
    de.EntityCode ,
    de.CostCentre ,
    de.DelegationCode ,
    de.ProjectCode ,
    de.RoleGroup ,
    de.Position ,
    de.DelegationText ,
    de.DelegationAmount ,
    de.Active 
from
    workflow.delegation_engine de
where       
    de.EntityCode = @entity
    and de.ProjectCode = @project_code
    and de.DelegationCode = @delegation_code
order by
    1
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ I agree on both points and have raised this with the dba team previously. Unfortunately this hasn't happened yet so my plan is to implement my code using this query, then refer to them for performance issues (knowing that their first point of call will be indexes, only made possible by making this a computed column). Office politics - blargh! \$\endgroup\$ – Michael A Jan 12 '12 at 1:26
  • \$\begingroup\$ @Michael - I understand your pain! Well, a potential alternative is to create a view that includes the RowID column. I believe that an index can be applied to a view, so that might be another way to circumvent performance issues without modifying the actual table structure. \$\endgroup\$ – Dr. Wily's Apprentice Jan 12 '12 at 1:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.