Press "Enter" to skip to content

Curated SQL Posts

SQLCover

Published 2016-04-11 by Kevin Feasel

Ed Elliott has upgraded his T-SQL code coverage tool:

What is code coverage?

Code coverage is a way to see how many statements in your database code have been executed when you ran your tests(s). It is a way to see how well covered with tests a particular area is – the better covered with tests, the less likely you will miss issues when you make changes in those areas.

What is code coverage for SQL Server?

SQL Server gives us a great tracing mechanism, either profiler or extended events which details exactly which statements have been run. SQL Server doesn’t do so well at telling us what statements we could possibly run in some code but the Transact Sql Script Dom that is part of the DacFx does give us the ability to break T-SQL code into statements so combining the two we have the ability to take a stored procedure such as:

This is pretty snazzy.

Comments closed

CLR Turned Off In Azure SQL Database

Published 2016-04-11 by Kevin Feasel

Brent Ozar reports that Azure SQL Database’s CLR will be turned off:

Details are still coming in, but in the Reddit AMA for the Azure database teams (going on as we speak), it’s one of the users reports that they got an email that SQL CLR will be shut off in one week due to a security issue.

The cloud: at the end of the day, it’s just someone else’s server, and they can – and will – take tough actions to protect their product, their users, their security, and their profits.

I’m curious for more details.  I’d like to know if this is particular to Azure or affect on-prem installations as well.

Comments closed

Keep Check Constraints Simple

Published 2016-04-08 by Kevin Feasel

Erik Darling shows performance implications around having scalar UDFs in check constraints:

Really. Every single time. It started off kind of funny. Scalar functions in queries: no parallelism. Scalar functions in computed columns: no parallelism, even if you’re not selecting the computed column. Every time I think of a place where someone could stick a scalar function into some SQL, it ends up killing parallelism. Now it’s just sad.

This is (hopefully. HOPEFULLY.) a less common scenario, since uh… I know most of you aren’t actually using any constraints. So there’s that! Developer laziness might be a saving grace here. But if you read the title, you know what’s coming. Here’s a quick example.

Yeah, UDFs in check constraints is a pretty bad idea most of the time.

Comments closed

Power BI Pivoting

Published 2016-04-08 by Kevin Feasel

Reza Rad shows how to pivot and unpivot using Power BI:

So Pivot is easy and simple to do, but you have to be careful about the nature and quality of source data set. If it is normal to have a name repeated in the source data, then an aggregation needs to be set properly. if you expect each name to appear once, then setting it as Do Not Aggregate works better because you can use error handling mechanism in Power Query to handle error somehow.

This is a good sight easier than writing a bunch of SUM(CASE) statements or using the PIVOT operator in T-SQL.

Comments closed

SSMS Execution Plan Improvements

Published 2016-04-08 by Kevin Feasel

Kendra Little shows Management Studio execution plan improvements in 2016:

The best features are the ones that you use all the time. SQL Server 2016 Management Studio’s bringing improvements in navigating around execution plans.

Click + Mouse Scroll: Zooming!

You can now make your plans bigger and smaller with this combo. It will zoom into the region where you have the mouse.

Click + Drag lets you move the plan

This is really handy for moving right to left.

Good for those times when SQL Sentry Plan Explorer isn’t available.

Comments closed

Linear Regression In Azure ML

Published 2016-04-08 by Kevin Feasel

Ginger Grant gives a brief discussion of linear regression:

There are two types of indicators for linear correlation, positive and negative as shown on the following charts. The Y axis represents Grades, and the x axis is changed to show positive and negative correlation of the amount of X on grades. When X is the amount of study hours, there is a positive correlation and the line goes up. When X is changed to watching cat videos, there is a negative correlation. If you can’t draw a line around the points there is no correlation. If I were to create a graph where X indicated the quantity of the bags of Cheese Doodles consumed on grades, it would not be possible to draw a straight line, where the data points cluster around it. Since this is Line-ar regression, if that line doesn’t exist there is no correlation. Knowing there is no correlation is also useful.

Simple linear regression is a powerful tool and gets you to “good enough” more frequently than you’d think.

Comments closed

Branching Strategy

Published 2016-04-07 by Kevin Feasel

Richie Lee points out an article on the SQL Server team’s source control strategy:

I’ve always advocated a dev/main/release process, but I’ll admit this has weaknesses, not least that testing will usually only take place properly in one branch, and that bugs found in one branch may not find there way “back” or “forward”, but to go with one branch means that you are forced to keep the quality at production-code quality and make use of feature switches. Certainly it’s an ambitious way of working, and Microsoft’s ALM documentation suggests that no branches is reserved for smaller teams, but surely if the SQL team at Microsoft are able to do it then certainly it’s a branching strategy worth considering?

Read the linked article as well.  This is an interesting look from the inside of how SQL Server gets developed.

Comments closed

Temp Table Usage

Published 2016-04-07 by Kevin Feasel

Paul Randal discusses common temp table anti-patterns:

It’s quite common for there to be a latching bottleneck in tempdb that can be traced back to temporary table usage. If there are lots of concurrent connections running code that creates and drops temporary tables, access to the database’s allocation bitmaps in memory can become a significant bottleneck.

This is because only one thread at a time can be changing an allocation bitmap to mark pages (from the temp table) as allocated or deallocated, and so all the other threads have to wait, decreasing the workload throughput. Even though there has been a temporary table cache since SQL Server 2005, it’s not very large, and there are restrictions on when the temporary table can be cached (e.g. only when it’s less than 8MB in size).

This is great advice; read the whole post.

Comments closed

Using Dates And Times

Published 2016-04-07 by Kevin Feasel

Aaron Bertrand has an intro-level post on using dates and times in SQL Server:

I urge you to always use yyyymmdd (without the dashes) for a date without time – it will never fail, regardless of regional, language, or dateformat settings, and across any of the date/time data types. (And absolutely do not store it as a string data type in SQL Server – always store it as a proper date or time data type.)

This was a big one for me because I tend to use yyyy-mm-dd.

Comments closed