When calls to decrypt
methods return NULL, either the column is NULL or the account that is using the methods doesn't have access to the key.
From BOL
Permissions
Requires VIEW DEFINITION permission on the symmetric key and CONTROL permission on the certificate.
Look at at the account that is executing the SSIS package and verify it has the required permissions.
You can also launch SSMS as the account that runs the SSIS packages and try stepping through the underlying code of DecryptByKeyAutoCert. Something approximately like
OPEN SYMMETRIC KEY MyKeyNameHere
DECRYPTION BY CERTIFICATE Usercert;
SELECT
city_id
, CONVERT(nvarchar(50), DecryptByKey(city_name)) AS city_name
FROM city;
CLOSE SYMMETRIC KEY MyKeyNameHere;
This looks like an oversight.
The optimizer cannot generate an apply-style indexed loops join when the update is performed using WHERE CURRENT OF
and the target view (schema-bound or not) has a T-SQL instead-of update trigger, regardless of the number of rows, or any other considerations:
That shows an example with almost 20,000 rows in the table (copied from the AdventureWorks' Person table, as it happens).
The join predicate gets 'stuck' on the nested loops join operator itself, rather than being pushed into the inner side to produce a seek:
Since you cannot change the code, you should report this as a bug through your normal Microsoft Support channel. You may also report the bug on Connect, but the chances are much lower of getting a quick response or fix via that route.
Just for interest's sake, the plan you are after is possible using an API cursor positioned update (the most analogous operation internally):
DECLARE
@cur integer,
@scrollopt integer = 2 | 8192 | 32768 | 131072, -- DYNAMIC | AUTO_FETCH | CHECK_ACCEPTED_TYPES | DYNAMIC_ACCEPTABLE
@ccopt integer = 2 | 32768 | 131072, -- SCROLL_LOCKS | CHECK_ACCEPTED_OPTS | SCROLL_LOCKS_ACCEPTABLE
@rowcount integer = 1;
-- Open the cursor
EXECUTE sys.sp_cursoropen
@cur OUTPUT,
N'
SELECT * FROM dbo.vPerson WHERE Id = 1;
',
@scrollopt OUTPUT,
@ccopt OUTPUT,
@rowcount OUTPUT;
-- Request a positioned update
EXECUTE sys.sp_cursor
@cur,
1, -- UPDATE
1, -- row number in buffer
'dbo.vPerson', -- table (unambiguous in this case)
'Name=''Banana'''; -- new value
-- Close
EXECUTE sys.sp_cursorclose -1;
The execution plan is:
Notice the index seek on Person (the predicate is not 'stuck'):
This is not a workaround for you since you cannot change the source query. There's no way to hint or plan guide your way around your issue; the optimizer simply cannot generate the seek plan you expect in your specific case. Attempting e.g. a FORCESEEK
hint simply results in an error message saying the optimizer could not produce an execution plan.
Best Answer
The issue is that your query through the view is the same as doing
Pretty much any
CAST
of a column in a predicate will render that predicate unsargable. The only exceptions that I am aware of are aCAST
ofdatetime
column todate
andVARCHAR
toNVARCHAR
under some collations.There is no such exception for
NVARCHAR
toINT
.You might hope that it would do something like
but it is not as simple as that. If your search string is
'1'
the two would return the same results but for the search string'ยน'
(superscript 1) the casting toint
fails but the string comparison compares equal under some collations. Conversely for the search string' 1'
(with leading space) theint
cast and comparison would discard the leading space and compare equal but the string comparison would compare unequal (and similarly with empty string which is cast to0
when converted toint
)Possible solutions
In general you could create either an indexed view or a new index on the base table referencing a computed column but both seem quite suboptimal compared with removing the
CAST
from the view so the existing index can be seeked.Computed Column
You could create a computed column on
SomeTable
with definitionCAST(ID as NVARCHAR(10))
and then index that.Indexed view
Given the restrictions in the question the computed column idea seems ruled out. An alternative will be to create an indexed view but likely you will need to change the query text to get this to work.
NOEXPAND
hint will be needed to get the indexed view to be matched.