True or False

Question

True or False

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

We are not able to see the execution plan of encrypted stored procedure, if that is the case then there is the need of encryption keyword 🙂

If we use SQL Profiler and add performance -> "Showplan XML" or "Showplan text", then profiler displays "Encrypted text" when it is executing actual procedure.

Thanks

Yousaf Khan Ten Centuries Points: 1147 More actions · Answer 2

Referencing to the link bellow

http://blog.sqlauthority.com/2008/11/01/sql-server-stored-procedure-with-encryption-and-execution-plan/

if we create procedure like

CREATE PROCEDURE #RegularSP

AS

SELECT TOP 10 City

FROM Person.Address

GO

/* Create SP with Encryption */

CREATE PROCEDURE #EncryptSP

WITH ENCRYPTION

AS

SELECT TOP 10 City

FROM Person.Address

GO

/* Execute SP - Execution Plan Tab shows up */

EXEC #RegularSP

GO

and execute the #RegularSP then it will show the execution plan

and if we execute the #EncryptSP then the execution will be disappear.

Yousaf Khan Ten Centuries Points: 1147 More actions · Answer 3

Referencing to the bellow link

http://blog.sqlauthority.com/2008/11/01/sql-server-stored-procedure-with-encryption-and-execution-plan/

here the example

CREATE PROCEDURE #RegularSP

AS

SELECT TOP 10 City

FROM Person.Address

GO

/* Create SP with Encryption */

CREATE PROCEDURE #EncryptSP

WITH ENCRYPTION

AS

SELECT TOP 10 City

FROM Person.Address

GO

/* Execute SP - Execution Plan Tab shows up */

EXEC #RegularSP

GO

if create the stored procedure like above and execute the #RegularSP then it will show the executio plan and if we execute the #EncryptSP then the execution plan will be disappear.

Yousaf Khan

igsri SSC Enthusiast Points: 186 More actions · Answer 4

Many thanks to all of you..

I was wrong, but now i understand..

UMG Developer SSChampion Points: 13482 More actions · Answer 5

Thanks for the question, it made me think about it some and learn something. (Though I still don't think encrypted procedures are worthwhile.)

SQLZealot Valued Member Points: 63 More actions · Answer 6

Nice question!

I was tricked by the 'Estimated execution plan' and 'Actual Execution Plan'.

You definitely don't see the Actual Execution Plan for encrypted stored procs. However, you can see 'something' in the estimated execution plan, which is not relevant, but still...enough for me to rush and press 'Yes' 🙂