Press "Enter" to skip to content

Day: January 7, 2016

Always Encrypted

Published 2016-01-07 by Kevin Feasel

Warner Chaves has a video introducing Always Encrypted:

This is the big difference of this new feature, that the operations to encrypt/decrypt happen on the client NOT on SQL Server. That means that if your SQL Server is compromised, the key pieces to reveal the data are NOT with the server. This means that even if your DBA wants to see the data, if they don’t have access to the CLIENT application then they won’t be able to see the values.

Always Encrypted strikes me as something that will be incredibly useful for 2-3% of the population, somewhat painful for 3-5% of the population, and completely ignored by the rest.  I’m currently on the fence about whether, three years from now, I will consider “completely ignored by the rest” to be a shame.

Comments closed

Anglicize Values

Published 2016-01-07 by Kevin Feasel

Dave Mattingly shows an easy way to anglicize values:

If your customer’s name is “José” but you search for “Jose”, you won’t (by default) find him.

Here’s a simple way to take care of that in your SQL database, without changing the data that you have.

If a particularly system only needs to support one language (e.g., English), this can be helpful, at least until somebody throws in Chinese or Hebrew characters.  That said, supporting Unicode is the best move when available.

Comments closed

Check Your CHECKDBs

Published 2016-01-07 by Kevin Feasel

Richie Lee has a script to check the last known CHECKDB run date:

One of the most important duties of a DBA is to ensure that CHECKDB is run frequently to ensure that the database is both logically and physically correct. So when inheriting an instance of SQL, it’s usually a good idea to check when the last CHECKDB was last run. And ironically enough, it is actually quite difficult to get this information quickly, especially if you have a lot of databases that you need to check. The obvious way is to run DBCC DBINFO against the specific database. This returns more than just the last time CHECKDB was run, and it is not especially clear which row returned tells us the last CHECKDB (FYI the Field is “dbi_dbccLastKnownGood”.)

It’s a bit of a shame that this information isn’t made available in an easily-queryable DMV.

Comments closed

Multiple Common Table Expressions

Published 2016-01-07 by Kevin Feasel

Steve Jones shows how to chain Common Table Expressions:

In this way I can more easily see in the first example I’m joining two tables/views/CTEs together. If I want to know more about the details of one of those items, I can easily look up and see the CTE at the beginning.

However when I want multiple CTEs, how does this work?

The answer is simple but powerful.  Once you’ve read up on CTEs, you start to see the power of chaining CTEs.  And then you go CTE-mad until you see the performance hit of the monster you’ve created.  Not that I’ve ever done that…nope…

Comments closed

Common Table Expressions

Published 2016-01-07 by Kevin Feasel

Aaron Bertrand shows us Common Table Expressions:

A CTE is probably best described as a temporary inline view – in spite of its official name, it is not a table, and it is not stored (like a #temp table or @table variable). It operates more like a derived table or subquery, and can only be used for the duration of a single SELECT, UPDATE, INSERT, or DELETE statement (though it can be referenced multiple times within in that statement).

This is a great article on CTEs; give it a read, even if you’re familiar with them.

1 Comment