A Fine Slice Of SQL Server
Rayis Imayev takes us through the Union Transformation in Mapping Data Flows:
Recently I had a chance to work on a special case of creating a file where the first two rows were static and contained output table metadata (columns names and column types), assuming that further reading process would be able to explicitly identify each column data type and consume new file correctly in a single run.
Read on to see how Rayis solved this with Azure Data Factory.
Comments closedErik Svensen shows how we can copy queries from Power BI’s Power Query Editor into Excel and back:
Today I discovered a way to easily copy all queries (including the groups) from a PBIX file to Excel without entering the power query editor in Excel.
In my case I needed to copy the data returned by a query and find it some much easier to get the data using Excel instead of generating a csv file from a visual in Power BI Desktop and importing that into Excel.
Click through for a step-by-step demonstration of the process.
Comments closedSarfaraz Hussain takes us through one of the most important concepts in functional languages:
Normally we write function and it seems like below:
def multiplySimple(a: Int, b: Int): Int = a * bWe declare a function with all the arguments needed inside a single parameter list.
In currying however, we can split this parameter list into multiple parameter lists.
def multiplyCurry(a: Int)(b: Int): Int = a * b
This doesn’t seem like much, but it does lead to partial application of functions and is one method for how we can have multiple inputs in a functional programming language when mathematical functions allow one input.
Comments closedHasan Savran shows how we can write queries against spatial data in Cosmos DB:
We are ready to use spatial functions to retrieve data. I have the location of Hurricane Katrina. I want to find my customers who might be affected by Katrina. I want to use the Distance function for this. I have the locations of eye of Katrina in my database, first I will pick one of those locations then look for my customers close to that location. How close? You can control the distance with spatial function named distance, In the following example, I use 100 km which is approximately 62 miles.
Hasan has a demo for us, so check it out.
Comments closedAndrew Pruski walks us through Azure SQL Database Edge:
This allows SQL Server to run on ARM devices which will expand the range of SQL Server considerably.
Just think how many devices are out there that run ARM. That includes my favourite device, the Raspberry Pi.
So, let’s run through how to get SQL running on a Raspberry Pi!
The process is fairly simple—among all of the steps, the one I had the most trouble with was actually finding the right marketplace, as I ended up in a different one somehow.
Comments closedOne of the cool things of calculation groups is they cannot just be used to apply a calculation over your “base measure” but also apply a dynamic formatstring. You could do this without calculation groups before too as I described here. This method had one big drawback though, it uses the FORMAT function and when doing that all results are transformed into strings. This does help in showing the right format, but you lose a lot of other functionality like sorting or conditional access. Now with calculation groups we can do custom formatting and keep the data type. How does that work?
Read on for an example.
Comments closedQuery Store only allows you to force plans that the Query Store has “seen” on that instance, and in that database. If you have a query and you want to force a specific plan, you will need to first ensure that the query runs, at least once, under the right circumstances to create the desired plan, so that the Query Store can capture it. Sometimes that is easy, in which case this blog is not for you.
What if you know you need to force the execution plan, you know you’ll get that execution plan only when a lot of data is already in the database, and you need to ship your software with an empty database to your customers? How do you set it up to force the plan you need?
Click through for the answer, and I give Hugo bonus points for using Raleigh as the example.
Comments closedRandolph West covers the internals of how date and time data types are stored in SQL Server:
DATEis the byte-reversed number of days since the year 0001-01-01, stored as three bytes. It goes up to 9999-12-31, which is stored as0xDAB937. You can check this value by reversing the bytes and sticking them into a hex calculator.37 B9 DAequals 3,652,058, which is the number of days since 0001-01-01.If you try to cast
0xDBB937as aDATEvalue (by incrementing the least significant bitDAby 1), it will throw a conversion error. There is obviously some overflow detection that protects against corruption in a date type.
Randolph looks at DATE, TIME, DATETIME(2), and DATETIME and explains how each is storedon a page.
Nellie Gustafsson announces some exciting news:
Today, we’re thrilled to announce that we are open sourcing the Java language extension for SQL Server on GitHub.
This extension is the first example of using an evolved programming language extensibility architecture which allows integration with a new type of language extensions. This new architecture gives customers the freedom to bring their own runtime and execute programs using that runtime in SQL Server while leveraging the existing security and governance that the SQL Server programming language extensibility architecture provides.
This opens up the possibility for additional languages. .NET languages (C# and F#) would be a natural fit and languages like Go might have enough dedicated support to give this a try.
Comments closedHong Ooi announces that a revamp of the checkpoint package is now in beta:
Checkpoint has been around for nearly 6 years now, helping R users solve the reproducible research puzzle. In that time, it’s seen many changes, new features, and, inevitably, bug reports. Some of these bugs have been fixed, while others remain outstanding in the too-hard basket.
Many of these issues spring from the fact that it uses only base R functions, in particular
install.packages, to do its work. The problem is thatinstall.packagesis meant for interactive use, and as an API, is very limited. For starters, it doesn’t return a result to the caller—instead, checkpoint has to capture and parse the printed output to determine whether the installation succeeded. This causes a host of problems, since the printout will vary based on how R is configured. Similarly,install.packagesrefuses to install a package if it’s in use, which means checkpoint must unload it first—an imperfect and error-prone process at best.In addition to these, checkpoint’s age means that it has accumulated a significant amount of technical debt over the years. For example, there is still code to handle ancient versions of R that couldn’t use HTTPS, even though the MRAN site (in line with security best practice) now accepts HTTPS connections only.
Click through to see what’s in the new checkpoint package.