Press "Enter" to skip to content

Day: January 25, 2016

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