Press "Enter" to skip to content

Author: Kevin Feasel

De-Duplicating Delimited Lists

Published 2016-01-25 by Kevin Feasel

Phil Factor looks at de-duplicating lists:

So there you have it. With XML tricks and window functions, we have more opportunity for kicking out any need for functions. To use this code, you’d just swap out the select statement that supplied my samples to the routine, for the lists that you want to deduplicate. Sure, this sort of job will never be quick because there are still correlated subqueries in there to upset the CPU! I am intrigued that there are such different ways of doing a solution for this task in SQL server. Are there yet other ways of doing it?

Cf. Aaron Bertrand’s tally table method.  Bonus points if you’re mentally screaming “CLR!”

Comments closed

Azure Blob Storage Sync Updated

Published 2016-01-25 by Kevin Feasel

Randolph West reports on some bug fixes to Azure Blob Storage Sync:

During a SQL Server migration this month, I found some inconsistencies in my Azure Blob Storage Sync tool, so I made several improvements, and fixed an outstanding bug.

As you know, it relies on the naming convention provided in Ola Hallengren’s Maintenance Solution and comes in two parts: the AzureBlobStorageSync command-line application, and the AzureBlobStorageRestore command-line application.

Using Azure blob storage (or S3 if you go the Amazon way) as a long-term storage mechanism for database backups is a pretty smart idea.

Comments closed

SSMSBoost

Published 2016-01-25 by Kevin Feasel

Allen McGuire talks about SSMSBoost:

Tip 4: Results Grid aggregates – Some people do a lot of calculations with their data, whether it’s sales data or whatever.  You end up saving the query results to Excel and about five minutes later you have the totals, etc.  With SSMSBoost, you can have your totals in seconds.  Below I’m selecting 10 records from a sales related table.  Say I want to get the SUM, MIN, MAX and Count of the SALEPRICE.  All I do is slide my mouse down the column highlighting the cells and a pop-up will appear with that information:

This is probably my second-favorite feature of SSMSBoost; my favorite is automated crash recovery, and my third-favorite its snippet support.  SSMSBoost has a free version and I use it myself.  I try not to push many tools here on Curated SQL, but this is one worth checking out.

Comments closed

Starting SQL Server Without TempDB

Published 2016-01-25 by Kevin Feasel

Kenneth Fisher shows us how to start an instance in safe mode in case the drive hosting tempdb gets fried:

The situation: Your server is down. The drive/directory where tempdb is supposed to be doesn’t exist. Who knows why. Maybe those evil SAN guys forgot to re-attach your storage during a DR situation. You may or may not realize it but SQL Server will not start without tempdb. Which is fine. Just move it to a location that exists right? Well, yes. That is an important step. So here is how we

Move tempdb

I like the way Russ Thomas (and Kenneth Fisher) put it:  this is a low-occurrence, high-liability issue.

Comments closed

Logins

Published 2016-01-25 by Kevin Feasel

Ed Leighton-Dick on logins:

The first concept to understand about SQL Server’s security model is the difference between authentication and authorization.

  • Authentication defines who is being given a right. SQL Server formally calls the authentication objects principals, but you’ll also see the older terms logins and users.

  • Authorization defines what rights are being given. Formally, these are called permissions. In modern versions of SQL Server, permissions are very granular and can be found on nearly every object in the instance. There’s also a vast hierarchy that interrelates all of the permissions. (We’ll cover permissions in a future post. For now, know that they’re there.)

Ed has started a series on security basics.  Given that there are relatively few people who talk security (and even fewer who know security), I consider this a great thing.

Comments closed

Single-Package Deployments In SSIS 2016

Published 2016-01-22 by Kevin Feasel

Andy Leonard looks at what happens when your first SSIS project deployment is a single-package deployment:

To test, I created a new SSIS project named “DeploymentTest1” and added three simple SSIS packages. I right-clicked Package3.dtsx and clicked Deploy Package. The Integration Services Deployment Wizard started, as expected. Since I was deploying to a pristine Catalog, I created a new Catalog Folder named “Deployment”. There were no projects in my catalog, and I was curious how the Deployment Wizard would handle this.

Andy notes that this works, but you might want to stick to the tried-and-true method of deploying entire projects and naming your catalog project the same as your SSDT project.

Comments closed

HA/DR With Azure SQL Database

Published 2016-01-22 by Kevin Feasel

James Serra looks into high availability and disaster recovery options for Azure SQL Database:

When you use the Azure portal to create a SQL Database, the various plans under the pricing tier include three service tiers: Basic, Standard, and Premium.  Here are those three plans with their high-availability (HA) and disaster recovery (DR) options:

Considering the price point, Microsoft offers some pretty good HA & DR capabilities for Azure SQL Databases.

Comments closed

External Temp Tables And Plan Cache Bloat

Published 2016-01-22 by Kevin Feasel

Sebastian Meine warns us about using external temporary tables in stored procedures:

When a stored procedure is compiled that is accessing an external temp table, SQL Server has no guarantee that the next time this stored procedure is called it is called from the same connection. However, if it is called from a different connection, the accessed temp table might contain significantly more (or less) data making a different execution plan preferable.

A simple way to deal with this situation is to force a recompilation every time a procedure that works with external temporary tables is executed. SQL Server is not going that route. Instead, SQL Server caches the procedure once for each connection. That can safe a significant amount of CPU resources when the procedure in question is called within a loop.

Try to avoid using external temp tables.  There are some cases in which it’s a very useful construct, but

Comments closed

VLFs And Temp Tables

Published 2016-01-22 by Kevin Feasel

Andy Galbraith was trying to update a script which counts VLFs and ran into problems defining his temp table:

Michelle’s code uses INSERT…EXEC to populate a temporary table with the VLF info, and the addition of this extra column breaks the original script.  Glenn’s versions of the scripts handle this issue easily since they are version-specific – in the SQL 2012/2014/2016 versions of the script, the temp table declaration is modified to include the extra RecoveryUnitID column, which allows the rest of the script to function as designed.

—

My problem is I wanted a version of the script that could be used across versions 2005+, and this presented a problem.  At first I tried to add an IF…ELSE block to the start of the script to handle the differing CREATE TABLE statements:

This is a good example of working around a problem rather than simply giving up.

Comments closed