Press "Enter" to skip to content

Month: November 2016

Discovering Deprecated Functionality

Published 2016-11-08 by Kevin Feasel

Jason Brimhall shows how to see if you’re using deprecated functionality in SQL Server:

Right here would be a good time to point out that your mileage may vary. Some items that are deprecated are more painful than others. Some may be deprecated and may have been on the list for 10+ years at this point. The point is, know your environment and then use good judgement to determine which items in your results need the most attention to fix and update. Why? Well, things really may break especially if you are looking to upgrade to a new version of SQL Server. Just because an item is still available in your current edition, that does not ensure it will still be available in a future release of SQL Server.

Now for the juicy stuff. As I mentioned, finding when and where a deprecated feature or syntax is employed, there are a couple of neat little events within Extended Events that can help to track each time a deprecated feature is accessed or employed. How do we do that? Use the deprecation_announcement and deprecation_final_support events. To help create sessions to track these events, I have the following script to create an “audit” session to do exactly that.

Click through for Extended Event scripts and demo code.

Comments closed

Globe Map Visual

Published 2016-11-08 by Kevin Feasel

Devin Knight has part 24 of his custom visuals series:

  • The Globe Map is a 3D globe visualization.

  • It looks similar to the technology Power Map in Excel but lacks a few features like animating the data across time.

  • The map can have multiple data visualization layers on top of the map like a bar chart and a heat map.

Under the right circumstances, this can be a useful visualization.  I think its benefit is mostly limited to the “wow, this looks cool” effect.

Comments closed

Machine Learning With R Q&A

Published 2016-11-08 by Kevin Feasel

Ginger Grant answers a series of questions about R and machine learning:

Question: Is it possible to run R processes in diffrent boxes other than SQL Server itself for scalability reasons?

You have the option of installing the R Server on another server. Just keep in mind that you do have to account for the additional overhead of moving all the data over the network, which needs to weigh in on your decision to move processing to a different server.

Click through for plenty more questions and answers.

Comments closed

Test Connection With HDInsight

Published 2016-11-07 by Kevin Feasel

I have a post trying to test a connection using HDInsight:

WebHCat is a web-based REST API for HCatalog, a management layer for dealing with files in HDFS.  If you’re looking for configuration settings for WebHCat, you’ll want generally to look for “templeton” in config files, as Templeton was the project name before WebHCat.  In Ambari, you can go to the Hive configs and look at webhcat-site.xml for configuration settings.  For WebHCat, the default port in HDInsight is 30111, which you should find in the templeton.port configuration setting.

I don’t like the fact that WebHDFS is blocked, but at least WebHCat is functional.

Comments closed

Computing Holidays

Published 2016-11-07 by Kevin Feasel

Gerald Britton has a few ways of calculating holidays for date dimensions:

Notice also that I use CROSS APPLY as an expression evaluator.  This keeps the code a little DRY-er.  In OOP-speak, I’ve encapsulated what varies.

Now that I’ve got a basic date table, let’s see about updating those holiday columns.   I’ll do it with an UPDATE command here, though it is possible to make them computed also — it just gets a little messy since there are so many different holidays with different calculations.  Let’s start with Thanksgiving.  In Canada, Thanksgiving Day is the second Monday in October.  In the US, it’s the fourth Thursday in November.  I’ve seen some interesting approaches to solving the problem.  One (for US Thanksgiving) looks like this:

I like the Easter formula.  Click through for details.

Comments closed

Run And RunOnce Registry Key Limits

Published 2016-11-07 by Kevin Feasel

Denny Cherry runs into a limit in the Run and RunOnce registry value lengths:

Microsoft has had the registry keys for Run and RunOnce in the registry since the registry was introduced in Windows 95 and Windows NT 4.  But in the 20+ years that those keys have been there (and I’ve used them for a variety of things) I’ve never known that there was a limit on the length of the commands that you could put into those keys.

I found this while working on a client project when I needed to kick off some powershell automatically when the server restarted to get it added to the domain, as well as do a few other things.  But for some reason the key just wasn’t running.

The limit does seem a bit short, though at least it’s one longer than the max length of a file path.

Comments closed

Concatenation Using CONCAT

Published 2016-11-07 by Kevin Feasel

Neil Gelder has a look at the CONCAT() function in SQL Server 2012:

This function removes all need to convert values just to concatenate text and string values together, but for some reason whenever I see code posted on various websites, even from SQL Server MVP’s they seem never to utilise this function.

One excellent use for this is when you attempting to created the SQL Statement for a dynamic SQL execution.  Instead of the usual conversions and + signs joining the text and parameters together its far simpler.

CONCAT is one of my favorite T-SQL enhancements from 2012 (after window functions).

Comments closed

Using Spark MLlib For Categorization

Published 2016-11-04 by Kevin Feasel

Taras Matyashovskyy uses Apache Spark MLlib to categorize songs in different genres:

The roadmap for implementation was pretty straightforward:

  • Collect the raw data set of the lyrics (~65k sentences in total):

    • Black Sabbath, In Flames, Iron Maiden, Metallica, Moonspell, Nightwish, Sentenced, etc.
    • Abba, Ace of Base, Backstreet Boys, Britney Spears, Christina Aguilera, Madonna, etc.

  • Create training set, i.e. label (0 for metal | 1 for pop) + features (represented as double vectors)

  • Train logistic regression that is the obvious selection for the classification

This is a supervised learning problem, and is pretty fun to walk through.

Comments closed