Press "Enter" to skip to content

Curated SQL Posts

UPDATE FROM Clause Usage

Published 2016-01-13 by Kevin Feasel

Rob Farley shows us UPDATE FROM:

1. It can let you access data in the columns of those tables, to use in predicates or expressions.

2. It can let you filter the data in the base table, by only allowing rows which match, such as when using an inner join or right outer join.

3. It can cause rows in the base table to be returned multiple times, if multiple rows in the joined table match a single row in the base table.

4. It can introduce NULL rows, if a full or right outer join is being done (or a left outer join with the base table second) and there are rows in the joined table that don’t match any rows in the base table.

This is a useful bit of T-SQL-specific syntax, but it’s a sharper edge than most UPDATE statements.  For a look back in history, Hugo Kornelis wanted to deprecate this syntax with the release of SQL Server 2008 (though MERGE has its own bugs and “Won’t Fix” problems, so in retrospect, perhaps it’s best that we still have UPDATE FROM).

Comments closed

Migrating Always Encrypted Data

Published 2016-01-12 by Kevin Feasel

Nitish Upreti shows us how to migrate data encrypted using Always Encrypted:

Our customers use the SQL platform to store volumes of high-valued data assets for their organization. With Always Encrypted, we want to deliver additional security while ensuring complete integrity of stored user data. To achieve this, in a regular workflow SQL Server/Azure SQL Database disallows an application to insert data directly into an encrypted column. If the application has not enabled Always Encrypted in the connection string, an insert statement targeting an encrypted column will fail. To insert a value into a column protected with Always Encrypted, the application must connect to the database with Always Encrypted enabled in the connection string and pass a plaintext value of the datatype configured for the target column. Subsequently, the SQL client driver encrypts the value and sends the ciphertext to the database. This ensures plaintext data is encrypted and stored appropriately.

Read the whole thing.

Comments closed

Restoring An Azure SQL Database

Published 2016-01-12 by Kevin Feasel

Grant Fritchey shows us how to restore a database hosted in Azure SQL Database:

The first, and most important thing to notice here is that it’s supplying me with a new name. I can change that to anything I want as long as it’s not the name of a database already in existence on my Azure SQL Database Server. You read that correctly, you can restore a database to Azure SQL Database, but there is no WITH REPLACE option. The restore creates a new database. This is important. In a recovery scenario, you need to be sure that you’re prepared to deal with this fact. How could you replace the existing database? Immediately run a pair of ALTER DATABASE commands to change the name of the existing database to something else and then change the name of your newly created database to the old name. That’s your choice.

There are a couple of gotchas, so if you are administering Azure SQL Database instances, be aware of these.

Comments closed

ODBC Driver 13 In Preview

Published 2016-01-12 by Kevin Feasel

The Microsoft ODBC Driver 13 is now available (in preview form):

The preview ODBC drivers for Linux now supports Ubuntu, RedHat and SUSE. This is Microsoft’s first ODBC Driver for SQL Server release supporting Ubuntu. You can now enjoy enterprise level support while connecting to SQL Server from Ubuntu. It also updates the drivers to unixODBC driver manager 2.3.1 support.

Full interoperability with distributions of Linux is something I’ve waited a long, long time for.  This is one tiny step closer.

Comments closed

Filtered Indexes Are Tricky

Published 2016-01-12 by Kevin Feasel

Kevin Eckart investigates filtered indexes not being used:

This warning is telling me that Parameterization is to blame for the filtered index not being used. From here, I see 3 options.

  1. Remove the parameters and use literals. (not practical)

  2. Use Dynamic SQL

  3. Use OPTION(RECOMPILE) at the bottom of the query.

This is the classic issue with filtered indexes:  you expect them to be used, but when you check the plan, they aren’t.

Comments closed

Installing TFS

Published 2016-01-12 by Kevin Feasel

Sifiso Ndlovu shares some tips regarding installing Team Foundation Server:

Although, you can get away with using convenience names (i.e. a dot, (local), or locahost) as SQL Server identifier name during the configuration of a SharePoint server farm as shown in Figures 2 & 3, such a practice is not allowed during configuration of TFS (as shown in Figure 1).

Check this out before installing your own TFS server.  Or use Visual Studio Online or GitHub or BitBucket or …

Comments closed

Power BI Matrices

Published 2016-01-11 by Kevin Feasel

Meagan Longoria wants to stack groups of measures in Power BI:

To my surprise, Power BI only lets you put multiple values on columns in a matrix. You can’t stack metrics vertically. Note: this is true as of 8 Jan 2016 but may change in the future. If you agree that this should be a feature in Power BI, please make your voice heard and vote for this idea on the Power BI forum and encourage others to vote for it as well.

The answer is a little complex.  Considering how frequently Power BI gets updated, hopefully they’ll make this a bit easier in the near future.

Comments closed

Compess An Entire Database

Published 2016-01-11 by Kevin Feasel

Shaun J. Stuart has a script which compresses all (compression-worthy) objects in a database:

Reader Dick H. posted a comment on my last version of this script stating that he got an error when this was run against tables containing sparse columns. Data compression does not support tables with sparse columns, so they should be excluded from this process. I’ve modified this script to correct this. I don’t have any tables with sparse columns in my environment, so thanks to Dick for pointing this out!

For instructions on using this script, look here.

This is a very useful script to have in your back pocket.

Comments closed

Row-Level Security

Published 2016-01-11 by Kevin Feasel

Manoj Pandey investigates row-level security:

Here in this post I will talk about the new Block Predicate option available in the CTP 3.0 release. With this option we can restrict write access for specific users.

Block Predicates block all write operations like:

– AFTER INSERT and AFTER UPDATE

– BEFORE UPDATE

– and BEFORE DELETE

I want this to perform well in a busy production environment.  I really, really do.

Comments closed