With this setup on the database side of things, we proceed to develop our client application to work around the TVP limitation. The key to doing this is to use the SqlBulkCopy class in .NET Framework 4.6 or above. This class ‘understands’ Always Encrypted and should need minimal rework on the developer front. The reason for the minimal rework is that this class actually accepts a DataTable as parameter, which is previously what the TVP was passed as. This is an important point, because it will help minimize the changes to the application.
Let’s get this working! The high-level steps are outlined below; there is a full code listing at the end of this blog post as well.
The upshot is that, at least as of today, Table-Valued Parameters are not supported with Always Encrypted. Arvind does give an alternative, however, so click through for more information.
When you’re developing on an instance you might want to change something in a database where the change might require to re-create the table. By default, the SQL Server Management Studio (SSMS) will prevent saving changes that require the table to be recreated.
Examples of changes that require table re-creation:
Change a column to no longer allow NULL values
Adding columns in the before another column
Moving a column
I agree with Sander: this is a useful feature, but not something you want to abuse. If you don’t understand the magnitude of your change, it could cause production problems. And if you do understand the magnitude of your change, typically you’ll want to script it out for later.
Now wait, why are they getting a read error when trying to UPDATE or DELETE? Because of the WHERE clause. The WHERE requires reading the data to see if a row meets the required conditions.
It turns out that write-only permissions don’t really work the way you’d want, as typically you want to read data even if your final goal is to update or delete rows.
For example, when I run it with 10,000 joins:
- Msg 8631, Level 17, State 1, Line 1
- Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.
When I drop it down to a much more realistic 5,000 joins:
- Msg 4074, Level 16, State 1, Line 1
- Client drivers do not accept result sets that have more than 65,535 columns.
Ah! Okay, that’s fair. (That’s also two error messages I’ve never seen before. Probably a good thing.) Alright, let’s take out the SELECT * and replace it with SELECT p1.* and see what happens:
This is a fun read.
When you think of technical debt, you may think only of classic shortcuts like making assumptions about the data, not using a TRY-CATCH block or perhaps hard-coding a manual correction into a stored procedure or view.
But I would argue that not paying attention to performance is just as much a technical debt. And rather than just crashing with an error message, performance issues are not always easy to just fix in production when your business users are working late to meet their deadlines, or when your web request are timing out. Start thinking of performance as an important part of your development process – half the job is getting the right data in the right place, the other half is making sure that your solution will handle double or triple the workload, preferably under memory pressure conditions with other workloads running at the same time.
Read the whole thing.
There are a handful of activities that have broken my SQL Server backup routines. Sometimes a DIFFERENTIAL (or LOG) backup would fail because there was no FULL backup. Other times, a LOG backup would fail because the log chain was broken. Some of those activities include:
Creating a new database.
Changing the recovery model to SIMPLE.
Restoring/reverting a database from a snapshot.
Any other activity that breaks the transaction log chain.
Dave discusses an easy way of figuring out if you’ve just performed an activity which breaks the log chain.
Start writing queries that demonstrate TSQL anti patterns – and make them slow
You know how people say that the best way to learn something is to teach it?
The best way to learn to speed up queries is to write slow ones.
The best way to get a job speeding up queries is to write a blog about the queries you’ve sped up.
This is a long-term learning process, but is absolutely a worthwhile skill for any database professional.