Press "Enter" to skip to content

Day: April 6, 2016

Views Aren’t Tables

Published 2016-04-06 by Kevin Feasel

Grant Fritchey looks at how the query optimizer treats views:

The important point to note is that the optimizer is absolutely not treating the view like a table. The optimizer is treating the view like a query, which is all it is. This has both positive and negative impacts when it comes to query performance tuning and this view. You could spend all sorts of time “tuning” the view, only to find all that tuning you’ve done tossed out the window when the query doesn’t reference a column in the view and that causes the optimizer to rearrange the plan. I don’t want to convey that this is an issue. It’s not. I’m just trying to emphasize the point that a view is just a query.

In a subsequent post, Grant promises to talk about the potential perils of nested views.  That’s where people start running into trouble, when a nested view gets to be so complex that the query optimizer gives up and takes it literally.

Comments closed

Collapsing Lists In R

Published 2016-04-06 by Kevin Feasel

Steph Locke shows how to collapse a list of data frames into a single data table:

With my HIBPwned package, I consume the HaveIBeenPwned API and return back a list object with an element for each email address. Each element holds a data.frame of breach data or a stub response with a single column data.frame containing NA. Elements are named with the email addresses they relate to. I had a list of data.frames and I wanted a consolidated data.frame (well, I always want a data.table).

Enter data.table …

data.table has a very cool, and very fast function named rbindlist(). This takes a list of data.frames and consolidates them into one data.table, which can, of course, be handled as a data.frame if you didn’t want to use data.table for anything else.

Something that continuously amazes me with R is just how terse the language can be without collapsing into Perl.

Comments closed

Conditional Ordering

Published 2016-04-06 by Kevin Feasel

Kenneth Fisher shows various methods for conditional ordering:

Simple sorts are pretty easy. In the ORDER BY clause you can list out the columns by name or by position in the field list and determine if you want them sorted ascending or descending. But how about going beyond that?

Every now and again you need a fairly complex sort order. Say for example you want to order your data differently depending on the Status (say DueDate if theStatus is 1, 2 or 3 and ShipDate otherwise). But you also want anyone with aTotalDue greater than $5000 sorted separately from those with a TotalDue less than $5000.

Conditional sorting isn’t something you should be doing in every query, but it’s important enough to know about.

Comments closed

Service Broker Conversations

Published 2016-04-06 by Kevin Feasel

Colleen Morrow goes into conversations in the context of Service Broker:

Like real-life conversations, Service Broker conversations are a reliable-bidirectional stream of messages exchanged between two participating services.  A Service Broker conversation can be short-lived, a simple exchange of 2 messages, or it can span days, weeks, even years.  There are two key elements of a Service Broker conversation that are important to note, however.  The first is that messages are guaranteed to be delivered in order, and only once.  Service Broker uses sequencing and acknowledgement mechanisms to ensure this.  The second key is that conversations are persistent.  Persistent across network interruptions.  Across server restarts.  In fact, conversations persist until they are explicitly ended.

In the world of Service Broker, you’ll sometimes see the term “conversation” used.  Sometimes it’s “dialog”.  Sometimes it’s even “dialog conversation”.  Although “conversation” and “dialog” are distinct concepts in the greater world of messaging services, in the context of Service Broker they are interchangeable.

We’re getting close to seeing Service Broker in action here, so stay tuned.

Comments closed

Retrieving WMI Information

Published 2016-04-06 by Kevin Feasel

Dave Mason shows how to retrieve WMI information (perhaps the “wrong” way):

In another post I explored a way to run WMI queries from tsql (sort of). Let’s do it again. The script below enables xp_cmdshell, runs WMIC.exe (capturing the output to a temp table), shreds the XML, returns a result set of columns and rows, and disables xp_cmdshell. If you’re adventurous and daring, run the script in a test environment.

With Powershell, as Dave mentioned, this really isn’t the best way to do the job, but there are times when you aren’t able to use Powershell.

Comments closed

Object Naming

Published 2016-04-06 by Kevin Feasel

Andy Galbraith warns you against…odd…database names:

I went and looked on the server, and sure enough in Management Studio I saw one database named “FinanceDB” and a database named “[FinanceDB]”.

This was on a SQL 2008R2 instance, but as a test I created a database named [test] on my local SQL 2014 instance and sure enough it worked!

The source of the problem at the client was the LiteSpeed maintenance plan.  Even though the backup task was set to backup all user databases, it wasn’t picking up the square-bracketed database.

I’d go a bit further and say that you should avoid everything but alpha-numeric characters and maybe underscore for databases, tables, views, and all other database objects.

Comments closed