Sdu-Tools

Everyone working with databases knows that having too many indexes can be a problem. Indexes need to be modified whenever the data in the associated tables need to be modified, so yes it can slow down data inserts, updates, and deletes.

How big a problem is too many indexes?

Overall, I’m not as concerned about this as many other people seem to be. I always want to focus on what the system is spending its time doing, and on most systems that I work on, that’s reading, not writing.

2017-11-24

This post covers another pair of useful functions that are part of the SDU Tools collection.

The first tool AsciiOnly deals with situations where you need to convert Unicode data (multi-byte data) to ASCII data (single byte). It processes each character and if it’s not in the ASCII range, it replaces the character with the value that you supply.  If your second parameter is an empty string, you’ll effectively strip out the non-ASCII characters, but your replacement can also be one or more characters.

2017-11-22

SQL Server 2012 added the function EOMONTH. It returns the end of the month. That’s useful but there are two things that bug me about it:

First: What’s with the name? Do we really have to save 3 characters from ENDOFMONTH, particularly when the same version added functions like DATETIMEOFFSETFROMPARTS? When I asked, I was told it was the name of the function in Excel. I can’t admit to being happy that SQL Server and T-SQL is following the naming of a function that someone added to Excel in the 1980’s in some adhoc way.

2017-11-17

There is a SQL Server T-SQL function that calculates the number of days (or months, or years, or other time periods) between any two dates.

SELECT DATEDIFF(day, 'firstdate', 'seconddate');

But I regularly get asked how to work out the number of days between two dates, excluding the weekends (Saturday and Sunday).

I’ve seen a few functions around that do a pretty good job at this but many don’t work properly if you have DATEFIRST changed in your session ie: if you change the first day of the week.

2017-11-15

Most of the time, we want SQL Server T-SQL queries to run as fast as possible, just the same as we want programs in any programming language to run as fast as possible.

But there are times when you want SQL Server to just sleep for a while, and without tying up system resources (like spinning around on the CPU).

Example of this would be waiting for new entries in Service Broker queues, or waiting to retry deadlocks.

2017-11-10

I love it when SQL Server gets brand new T-SQL language features.

SQL Server 2017 added a great new language feature called TRANSLATE. It’s been in some other products like Oracle for a while but it’s just been added to SQL Server.

Imagine I have a string like this:

’[02] 9992:2343’ 

But what I really want is one like this:

’(02) 9992-2343’

Up to SQL Server 2016, I could do this with a series of replace statements:

2017-11-08

Occasionally I run into a need to clear out all objects in a schema in a SQL Server database. Sometimes it’s because I want to remove the schema itself. It’s often painful to do as I have to find all the types of T-SQL objects, and then often have to remove them in a particular order.

So I added a tool to my SDU Tools collection to do just that.

2017-11-03

I recently shipped the latest update to my SDU Tools collection.

This time I decided to add a tool that’s in the “cute” category. It’s called InvertString and you can see it action in the image above.

Yes, it takes an input string and returns a string that looks quite like an upside down version of the same string.

As part of the inversion I also reversed the order of the string so when you look at it upside down, it still looks correct. But if what you really want is a mirror image, you could combine it with REVERSE to achieve that outcome:

2017-10-31

Hi Folks,

We’ve just created the May 2017 update for SDU Tools. We’re pretty excited about what we’re delivering this month.

The first part that we’ve added is a set of tools to make it really, really easy to capture a performance query trace, to load it, and to perform a basic analysis of it. There are three tools involved in this:

• CapturePerformanceTuningTrace – Just give the trace a name, say where it should be stored, which databases to filter on (if any), and how long you want the trace to run. We do the rest.
• LoadPerformanceTuningTrace – This tool loads the completed trace into a table.
• AnalyzePerformanceTuningTrace - And then we find the things in the trace that are of interest – automatically

Next, we’ve added a useful tool to help with your database consistency. I often run into databases where columns that hold the same information are defined differently across the database.

2017-05-07

When you output a series of numbers in T-SQL, people often want to right-align the numbers. So instead of numbers that look like this:

They want an output that looks like this:

Now the first thing to understand is that this is generally a client-tool or program issue, not a T-SQL issue. I ran these queries in SQL Server Management Studio and it could just as easily have decided to show me numbers right-aligned.

2017-04-09