Feature Spelunking

Aaron Bertrand shows us how to find hidden features in CTPs:

In honesty, I’m just meticulous about installing each new build and immediately digging into the metadata. It would be hard to take a look at sys.all_objects and identify what’s new by sight; even columns like create_date and modify_date are not as accurate as you might expect. (For example, in CTP 3.1, sp_helpindex has a create_date of 2015-11-21 18:03:15.267.)

So instead of relying on photographic memory or hoping that something new will jump out at me while scanning the new catalog, I always install the new CTP side-by-side with the previous CTP (or, in the case of the very first CTP, side-by-side with the previous version). Then I can just perform various types of anti-semi-joins across a linked server to see objects and columns that have been added, removed, or changed.

Very interesting.

When To Use Temporal Tables

Randolph West wraps up his temporal tables series by asking when you should use them:

Tracking changes to your data is a big deal, and in databases with heavy churn, you can end up needing a massive amount of space to handle your history, especially if you need to retain seven years of data, like some of our customers.

If data storage is a concern, I’m going to recommend SQL Server Enterprise Edition. The Books Online documentation specifically states that the history tables for Temporal Tables are implemented with Page Compression by default, which is an Enterprise Edition feature.

My quick thought is, use them when you want a type 2 dimension without putting in the effort to create a type 2 dimension—in other words, non-warehouse systems in which you need long-term data changes.

Extensions Are Important

Kenneth Fisher on database file extensions:

So what does this mean? Should we start naming our database files whatever we want? No, absolutely not! It means that you need to be extra careful when specifying the name of the files. You really don’t want to use non-standard file names. The confusion! The misunderstandings! Do you really want the operating system to think your database file is a really big picture? Or even worse (and while I realize this sounds like a stretch I’ve had it happen) you accidentally give your file a .bak extension. Then one day your automated process that deletes old bak files runs as the same time your instance is down. Bye bye database file.

You can use whatever extension you want, but be smart about it.  Also check out Sean McCown’s dirty fun trick.

Azure Storage Options

James Serra walks us through the list of storage options available on Azure:

Microsoft Azure is a cloud computing platform and infrastructure, created by Microsoft, for building, deploying and managing applications and services through a global network of Microsoft-managed and Microsoft partner-hosted datacenters.  Included in this platform are multiple ways of storing data.  Below I will give a brief overview of each so you can get a feel for the best use case for each, with links provided that go into more detail:

There are several options available, running the gamut from unstructured data (blob storage, file & disk storage), semi-structured data (data lake store), to structured data (Azure SQL Database) and a few points in between.

SQL Server Startup Parameters

Shawn Melton shows us how to modify SQL Server startup parameters using Powershell:

Low and behold the StartupParameters property is one that can be read and set. So how do you set it? Well the one thing to remember is you DO NOT need to remove what is already in that property because IT WILL BREAK YOUR SERVER!

Let me be clear, setting the property means you need to append to what is already there, so don’t just go setting it equal to something like “-T1118”. Doing this will remove the required parameters to start SQL Server itself, and no it will never warn you of this…so proceed at your own risk.

Read the instructions; otherwise, you can mess up your installation, and that’d be a bad thing.

Powershell Service Management

Mike Fal gives us a pattern for managing SQL Server services with Powershell, WMI, and SMO:

I have built a function around using the second method that makes handling this process a little easier. Also, because I’m not a fan of passing passwords in plain text, I built the function to take a PSCredential object to keep my account information secure. In order to spare you the wall of text, you can view the full function on my GitHub repository.

The function can be loaded through a variety of methods, but once it is loaded calling it is simply a matter of creating the credential for the service account and calling the function

Good stuff.

SQL Server 2016 CTP 3.1

Manoj Pandey tells us that SQL Server 2016 CTP 3.1 is now available:

–> Following are enhancements in some areas:

1. New In-Memory OLTP improvements, including Unique indexes, LOB data types, and Indexes with NULLable key columns.

2. The COMPRESS and DECOMPRESS functions convert values into and out of the GZIP algorithm.

The list goes on, but item #1 is intriguing.

SSIS Variables

Mark Broadbent has a few nuggets of information regarding using variables in SSIS script components:

Notice that in the example above the assumption is that the SSIS variable datatype is compatible with the script variable type.

Once you have finished writing your code block you may save your code and close the Script Editor. All that is left is to click the OK button to close the Script Task Editor and run your package!

Getting variables to work in script components isn’t terribly difficult, but Mark shows that there are quite a few steps to the process.

Multi-Script Using Object Explorer Details

Andrea Allred shows a nice trick using the Object Explorer Details window in SSMS:

One of the tips that I was super surprised that many people didn’t know is the Object Explorer Details. It allows you to delete multiple objects at once, script out multiple objects at once and just do some really cool stuff. How do I access this magic you are asking? When in management studio, click on View>>Object Explorer Details.

For those one-off jobs where you need to script out a dozen objects, this is very helpful.


Trigger Blue Screens

David Klee uses the registry to generate blue screens:

Voila! Use the USB keyboard registry key. Set it and reboot the machine. To trigger it, hold right-control and hit scroll lock twice.

BOOM! Immediate manually-initiated BSOD. Neat, huh?

Me, I just need to update my video card drivers; that gives me all the blue screens I want…


April 2019
« Mar