A Fine Slice Of SQL Server
Mikey Bronowski continues a series on dbatools functionality vis-a-vis SQL Server Management Studio:
The SSMS offers to script out lots of the SQL Server objects, however it can be limited in some areas. Using Get-Dba* commands and piping them into Export-DbaScript may add few more options. For example SQL Agent jobs:
Click through for just shy of a dozen cmdlets to help you run your data import-export business.
Comments closedRandolph West digs into an issue:
Two years ago I wrote a post that got a lot of traction in the comments at the time. Last month there was renewed interest because one of the commenters noted that the official SQL Server documentation for
DATETIME2disagreed with my assertions, and that I was under-representing the storage requirements.To remind you, I have been saying for years that you can use
DATETIME2(3)as a drop-in replacement forDATETIME, and have better granularity (1ms versus 3ms) for 12.5% less storage (1 byte per column per row). The commenter intimated that because my statement conflicted with the documentation, that I must be wrong. As it turns out the documentation was wrong, but I also learned something new in the process!
It’s an interesting internal look at how difficult it is to get documentation right, even on something which sounds simple.
Comments closedKenneth Igiri walks us through restoring the master database in SQL Server:
The master database contains records of the structure/configuration for both the current instance and all other databases. When you run sp_configure, you are writing data to the master database. It also contains most of the dynamic management views that are necessary to monitor the instance.
The importance of the master database is crucial. First, it has the information necessary for opening all other databases and has to be opened first. Then, it involves all instance level principals for the current instance.
It is crucial to back up the master database daily. Equally important is to know how to restore the master database to the instance. The most frequent cases are the database crash or the necessity to restore the master database to another instance when no longer use the source instance. In this article, we will examine the specific case of moving the master database to another instance.
It’s definitely not as easy as restoring other databases, but it is possible.
Comments closedAnjali Agarwal and Laura Jiang announce a new product:
The release of the Azure Data Studio extension generator is now available. Install the generator through npm and get started with extension development with these Azure Data Studio extension tutorials.
The Azure Data Studio extension generator is a command line tool designed to help extension authors get started with the process of extension development. It includes extension templates that enable users to create and publish extensions with minimal technical knowledge required. In our most recent release, we have added three highly requested extension templates to the generator.
Anything which helps make extension development easier is fine by me.
Comments closedAaron Bertrand doesn’t like a common insert/update pattern:
I think everyone already knows my opinions about
MERGEand why I stay away from it. But here’s another (anti-)pattern I see all over the place when people want to perform an upsert (update a row if it exists and insert it if it doesn’t):
IF EXISTS (SELECT 1 FROM dbo.t WHERE [key] = @key) BEGINUPDATE dbo.t SET val = @val WHERE [key] = @key;ENDELSEBEGININSERT dbo.t([key], val) VALUES(@key, @val);ENDThis looks like a pretty logical flow that reflects how we think about this in real life:
Does a row already exist for this key?
– YES: OK, update that row.
– NO: OK, then add it.
Click through to learn why this is a bad idea.
Comments closedAriel Muldoon shows off a couple of helpful functions in R’s purrr library:
The
possibly()function is a wrapper function. It wraps around an existing function. Other than defining the function to wrap, the main argument of interest isotherwise. Inotherwisewe define what value to return if we get an error from the function we are wrapping.
Read the whole thing. H/T R-Bloggers.
Comments closedMrinal Walia takes us through the concept of Long Short Term Memory:
A simple Recurrent Neural Network has a very simple structure, that forms a chain of repeating modules of a neural network, with just a single activation function such as tanh layer, similarly LSTM too have a chain-like structure with repeating modules just like RNN but instead of a single Neural network layer in RNN, LSTM has four layers which are interacting in a very different way each performing its unique function in the network.
Read on for a good amount of theory followed by an example using Keras.
Comments closedJeffin Mathew walks us through SQL Server Machine Learning Services:
The purpose of this blog is to explore the process of running ML predictions on SQL server using Python. We are going to train and test the data to predict information about bike sharing for a specific year. We are going to be using the provided 2011 data and predict what 2012 will result in. The 2012 data already exists inside the dataset, so we will be able to compare the predicted to the actual amount.
For certain use cases—especially when the data already exists in SQL Server, and especially especially when you can use native scoring—Machine Learning Services does a great job.
Comments closedReading data from a highly compressed analytical structure is quite different from the query patterns used on transactional data. By leveraging metadata, data order, segment elimination, and compression, large tables can be quickly read and results returned in seconds (or less!).
Taking this further, read queries against columnstore indexes can be further optimized by simplifying queries and providing the query optimizer with the easiest path to the smallest columnstore scans needed to return results.
This article explores the most efficient ways to read a columnstore index and produce guidelines and best practices for analytics against large columnstore data structures.
Read on for good advice.
Comments closedLouis Davidson won’t be stuck with uniform distributions:
The thing is, while I have 77 pictures of my favorite roller coaster, Expedition Everest at Disney’s Animal Kingdom, and 70 of The Tower of Terror at Hollywood Studios, I only have 2 of the Flame Tree Restaurant at Animal Kingdom and many other things that aren’t as exciting to post about. If I randomly choose attractions using a non-weighted random number generator, it would be just as likely to get the lesser items as the same frequency as the greater items. Hence, I want my popular items to come up most frequently, but every once in a while, I want to be surprised by something different.
This is where I needed to build a weighted randomized value.
Read on to see how Louis implements this.
Comments closed