Press "Enter" to skip to content

Curated SQL Posts

Excluding Schema Comparison Sections

Published 2017-01-24 by Kevin Feasel

Angela Henry shows how to exclude entire sections of a schema comparison in Visual Studio:

When using VS for database projects I typically use my environment specific Publish xml file to deploy changes to my local database when experimenting with code changes.  However, every once in a while I will have to use the New Schema Compare tool from the Tools | SQL Server menu when I have a “one-off” database that I need to synch to my database project.

Quite frequently because these are one-off databases there will be a ton of junk items that are in the database, but not in my database project or lots of objects in my project that aren’t in my database.  Either way I want to ignore those changes.  In the past, I’ve always manually unchecked each item, tedious when you have more than two items to uncheck.

Click through for more details.

Comments closed

Extending Nested Sets

Published 2017-01-24 by Kevin Feasel

Nate Johnson extends the nested sets model to include a depth attribute:

Depth is pretty simple to add if you’ve already got a tree full of data.  We can use a recursive common table expression, or “rCTE“.  While normally these are frown-worthy (remember, recursion is not SQL’s strong suite), we’re only using it one time to populate an existing data-set, so we can keep on smiling.

Hierarchies in SQL are an important but not well understood topic.

1 Comment

The Cost Of Compression

Published 2017-01-24 by Kevin Feasel

Erin Stellato looks at the COMPRESS function:

While this data is interesting, I’m more curious about how compression affects my everyday SELECT queries. I have a set of three stored procedures that each have one SELECT query, so that each index is used. I created these procedures for each table, and then wrote a script to pull values for first and last names to use for testing. Here is the script to create the procedures.

Once we have the stored procedures created, we can run the script below to call them. Kick this off and then wait a couple minutes…

To me, the COMPRESS function is most useful for compressing information you tend not to search through but need to keep the in the database, like HTML markup or long descriptions.

Comments closed

Query Plan Attributes

Published 2017-01-24 by Kevin Feasel

Grant Fritchey explains the sys.dm_exec_plan_attributes DMV:

There is a DMV that isn’t used a lot of the time because the information within it frequently doesn’t have a lot of bearing on solving fundamental query tuning issues such as out of date statistics, bad or missing indexes, or poorly structured T-SQL. This DMV, sys.dm_exec_plan_attributes, contains a bunch of values that are used by the optimizer to identify a plan in cache, such as object_id (if any), database_id, and compatibility level (compat_level). In addition to these clear & easy to understand attributes, there’s one more, set_options, that’s not immediately clear.

Read on for more information and a sample call.

Comments closed

Finding Abnormally Long-Running Jobs

Published 2017-01-23 by Kevin Feasel

Lori Brown has a script to find jobs running longer than their 30-day average:

I needed to update some of our long running job monitoring code to improve it from the version that we have right now. I like this version because it uses msdb.dbo.syssessions (https://msdn.microsoft.com/en-us/library/ms175016.aspx) to validate that a job is actually running. I also wanted to know the percent difference between the current run duration versus an average duration per job from the past 30 days. I decided to place the calculated average into a table variable and then join on it to get my results. I also used the IIF function (https://msdn.microsoft.com/en-us/library/hh213574.aspx) to help me avoid a divide by zero error that comes up when the average duration equals 0.

One thing which could cut down on false positives would be to calculate the standard deviation as well.  I wouldn’t automatically assume that job executions were normally distributed, but if you look at things more than one standard deviation away from the mean, it should remove noise of jobs which are just a little over the average but not in dangerous territory.

Comments closed

Graphs In SQL

Published 2017-01-23 by Kevin Feasel

Joe Celko models graphs using ANSI SQL:

The initial proposed solutions to construct the subgraphs were essentially procedural traversal, dumping pairs of nodes into a temp table and incrementing a counter.

Let us try getting out of a procedural mindset and starting to think in sets instead. Let us give each subgraph a name, and a member node. Essentially, this directly models. The diagram I just gave you a paragraph or two ago. The next question is how do we get names for the subgraphs. I will propose the simple solution that each subgraph takes the name of the lowest element in it. This would give us a table that looks like this:

Read the whole thing.  Given the recent revival of graph databases, it’s important to take note that you can model the network-style problems using either graphs or relations; the trick is figuring out which is going to give you better long-run performance.

Comments closed

CTP 1.2 For vNext

Published 2017-01-23 by Kevin Feasel

The SQL Server team has announced that the latest CTP of vNext is available:

Key CTP 1.2 enhancement: Support for SUSE Linux Enterprise

In SQL Server v.Next, a key design principle has been to provide customers with choice about how to develop and deploy SQL Server applications: using technologies they love like Java, .NET, PHP, Python, R and Node.js, all on the platform of their choosing. Now in CTP 1.2, Microsoft is bringing the power of SQL Server to SUSE Linux Enterprise Server, providing more deployment options and a streamlined acquisition process.

That makes three mainline distributions supported:  Ubuntu, Red Hat, and now SuSE.

Comments closed

Thinking About Dead Connections

Published 2017-01-23 by Kevin Feasel

Lonny Niederstadt has a half-baked idea on dead connections:

In SQL Server 2005, a valuable addition was made.  SQL Server would use a “Keep Alive” value specified specifically per instance, rather than the KeepAliveTime specified by the Windows registry.  It defaulted to 30000 ms/30 seconds.

The blog post below explains this new addition, and mentions that the interval for SQL Server will be a fixed 1000 ms/1 second, regardless of the KeepAliveInterval specified in the Windows registry. At the time SQL Server 2005 was introduced, TCPMaxDataRetransmissions from the Windows registries still controlled the maximum number of probes.

Read on for more.

Comments closed

Streaming Data To Power BI

Published 2017-01-23 by Kevin Feasel

Sacha Tomey builds a quick Powershell script to feed streaming data into Power BI:

Peter showed off three mechanisms for streaming data to a real time dashboard:

  • The Power BI Rest API
  • Azure Stream Analytics
  • Streaming Datasets

We’ve done a fair bit at Adatis with the first two and whilst I was aware of the August 2016 feature, Streaming Datasets I’d never got round to looking at them in depth. Now, having seen them in action I wish I had – they are much quicker to set up than the other two options and require little to no development effort to get going – pretty good for demo scenarios or when you want to get something streaming pretty quickly at low cost.

Click through for more details and a sample script.

Comments closed

CONCAT_WS()

Published 2017-01-23 by Kevin Feasel

Manoj Pandey points out a new string function in SQL Server vNext:

Here in this post I’ll discuss about one more new function i.e. CONCAT_WS(), here “_WS” means “With Separator”.

This is very similar to the existing CONCAT() function introduced back in SQL Server 2012, which concatenates a variable number of arguments or string values.

The difference is the new function CONCAT_WS() accepts a delimiter specified as the 1st argument, and thus there is no need to repeat the delimiter after very String value like in CONCAT() function.

It’s a small change, but I think a rather useful one.  Do think about how you’d want to interpret NULL values, though, as CONCAT_WS() does not include separators for NULL values.

Comments closed