A Fine Slice Of SQL Server
Bert Wagner explains some of the trickiness of COUNT and NULL values in SQL Server:
One thing I see fairly often (and am occasionally guilty of myself) is using COUNT(DISTINCT) and DISTINCT interchangeably to get an idea of the number of unique values in a column.
While they will sometimes give you the same results, depending on the data the two methods may not always be interchangeable.
Click through for examples.
Comments closedKenneth Fisher explains a nasty-looking error to us:
So, are you seeing this error?
Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
If you read the error it might freak you out a bit. The key words memory and corrupt can be a bit .. concerning. Fortunately in this case they are also rather misleading.
Click through to understand what’s going on and how you can fix the problem if you see this error.
Comments closedMax Trinidad shares some tools you can use to automate SQL Server processes with Powershell:
For script automation we could install either or both version of PowerShell Core: (As of February 19th, 2019)
PowerShell Core GA version 6.1.3
PowerShell Core Preview 6.2.0 Preview 4Here are some important PowerShell Modules to use for SQL Server management scripting:
*SQLServer – This module currently can be use on SQL Server 2017 and greater.
*DBATools – This a community supported module that will work with SQL Server 2000 and greater.
DBAReports – Supports for Windows SQL Server.
DBCheck – Support for Windows SQL Server.
Automation is a great DBA’s best weapon. Knowing the tools which help you automate your tasks is critical.
Comments closedLouis Davidson shares some tips on using the QUOTENAME function:
Or if you are Rob Volk (@sql_r on Twitter), and you want to create an annoying database on your best frenemy’s SQL Server that includes brackets in the name, like:
This [database] Is Awesome
You will need to do:
CREATE DATABASE [This [database]] Is Awesome];
I’m not saying you should do that, but I’m also not saying you shouldn’t.
Comments closedJess Pomfret shows us what compressed data looks like in SQL Server:
We first need to switch on trace flag
3604: this will write the output of ourDBCC PAGEcommand to the messages tab instead of the event log.There are 4 parameters for
DBCC PAGE: we will need to pass in the database name (or id), the file number, the page id and the print option. Using a print option of 0 will give us just the page header. In these examples I’m going to use option 3 which gives us more details on the rows stored on the page. For more information on usingDBCC PAGEI’d recommend Paul Randal’s post “How to use DBCC PAGE“.
This kind of investigation lets you see how compression really works.
Comments closedSebastian Sauer shows how you can use the ggformula package combined with ggplot2 to enhance your R visuals:
Since some time, there’s a wrapper for
ggplot2available, bundled in the packageggformula. One nice thing is that in that it plays nicely with the popular R packagemosaic.mosaicprovides some useful functions for modeling along with a tamed and consistent syntax. In this post, we will discuss some “ornaments”, that is, some details of beautification of a plot. I confess that every one will deem it central, but in some cases in comes in handy to know how to “refine” a plot usingggformula.Note that this “refinement” is primarily controlled via the function
gf_refine()(most stuff),gf_lab()(for labs), andgf_lims()(for axis limits). Themes can be adjusted usinggf_theme().
Click through for several examples.
Comments closedVGG16 is a built-in neural network in Keras that is pre-trained for image recognition.
Technically, it is possible to gather training and test data independently to build the classifier. However, this would necessitate at least 1,000 images, with 10,000 or greater being preferable.
In this regard, it is much easier to use a pre-trained neural network that has already been designed for image classification purposes.
This is probably the best generally available technique for image classification.
Comments closedJen Stirrup explains why ISNUMERIC isn’t all that great:
I noted that one of the columns failed to convert VARCHAR to DECIMAL.
The error message is below, and it’s usually fairly easy to sort:
Error converting data type varchar to numericNormally, I’d use ISNUMERIC to identify the rows that fail to have a value in that column that could be converted to a number. Then, I could identify the value, and then I could replace or exclude it, as required.
However, on this occasion, using ISNUMERIC failed to identify any columns as being non-numeric.
Click through to see why Jen got this result.
Comments closedBrad Llewellyn shows us how to feed Azure ML predictions into Power BI via Azure Stream Analytics:
Today, we’re going to talk about combining Stream Analytics with Azure Machine Learning Studio within Power BI. If you haven’t read the earlier posts in this series, Introduction, Getting Started with R Scripts, Clustering, Time Series Decomposition, Forecasting, Correlations, Custom R Visuals, R Scripts in Query Editor, Python, Azure Machine Learning Studio and Stream Analytics, they may provide some useful context. You can find the files from this post in our GitHub Repository. Let’s move on to the core of this post, Stream Analytics.
This post is going to build directly on what we created in the two previous posts, Azure Machine Learning Studio and Stream Analytics. As such, we recommend that you read them before proceeding.
Read on for the demo.
Comments closedMatt Allington visualizes changes in the Gartner magic quadrant for BI tools:
Today Gartner released the 2019 magic quadrant for Business Intelligence. As expected (by me at least), Microsoft is continuing its trail blazing and now has a clear lead over Tableau in both ability to execute and completeness of vision. I thought it would be interesting to see a trend over time for the last 5 years, as this is the time period that I have been a professional Power BI Consultant. I needed some way to extract the numerical data points from the images I had collected. This article shows you how to do that. Here is the final output – a scatter chart with a play axis in Power BI of course.
I was just commenting the other day about how somebody should do this and Matt went and did it.
Comments closed