June 30, 2013 at 10:03 am
We get a daily file of employee ids and their associated social security numbers.
Right now, I have a stored procedure
create procedure put_secret(@id int,
@ssn int)
as
begin
open symmetric key ... decryption by certificate ...
insert into secret(id,ssn)
values (@id,EncryptByKey(Key_GUID(...), @ssn))
close symmetric key ...
end
and a program that reads one record from the file, calls the stored procedure put_secret passing the record, and I do this in a while (not EOF). Everything is working well. But it is s-l-o-w.
Is there a way to either create the table so that it knows that a column is, by default, encrypted,
create table secret
(
id int not null,
ssn varbinary(MAX) default encryptByKey(Key_GUID(...)),
primary key(employee_number)
)
or a way to have bcp XML format file to do this
<RECORD>
<FIELD ID="ID" xsi:type="CharFixed" LENGTH="9" />
<FIELD ID="SSN" xsi:type="CharFixed" LENGTH="9" />
</RECORD>
<ROW>
<COLUMN SOURCE="ID" NAME="id" xsi:type="SQLINT"/>
<COLUMN SOURCE="SSN" NAME="ssn" xsi:type="SQLVARBIN" ENCRYPTION="..."/>
</ROW>
or a way to have BULK INSERT, or OPENROWSET(BULK...) do this?
Again, the while loop works and no one is complaining (so I guess it's academic), but I can foresee some shop somewhere in the world with a huge number of records where this might be useful.
June 30, 2013 at 1:45 pm
Of course it is slow. For each row you open and close a certificate, insert just one row and overhead of calling procedure adds-up to that.
Use table parameter to pass all data at once to procedure, open certificate just once and insert all in one big insert command, no loops are needed at all. You will be amazed.
July 1, 2013 at 7:20 am
bulk insert into a staging table, then do a single insert from the stage table into destination table like this:
open symmetric key ... decryption by certificate ...
insert into secret(id,ssn)
select id, EncryptByKey(Key_GUID(...), ssn) from stage table
close symmetric key ...
The probability of survival is inversely proportional to the angle of arrival.
July 1, 2013 at 7:31 am
These are wonderful recommendations!
It always amazes me how the thinking processes of the various specialties differ: as a "regular" programmer, my thinking is nitty-gritty cursor based I/O (you know, open file, read one record at a time until EOF, close file). The database person probably looks at cursors with a horrified expression.
🙂
July 1, 2013 at 7:45 am
jhom (7/1/2013)
The database person probably looks at cursors with a horrified expression.🙂
You would be amazed at some the things we see from programmers when left to their own devices.;-)
They usually see a relational database as merely a place to store un-related pieces of data that they will eventually fetch back out (one thing at a time) and beat into submission with VB code. LOL
Sad but true. lol You display early signs of becoming a good database guy. Keep it up.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply