The Bit Bucket

We’ve been building tools to create scripts for various SQL Server T-SQL objects for a long time. Part of scripting a table is the scripting of the data type. That means its datatype name, precision, scale, and maximum length.

In our free SDU Tools for developers and DBAs, we added a scalar function called FormatDataTypeName ot make that easy. It takes the following parameters:

@DataTypeName sysname - the name of the data type @Precision int - the decimal or numeric precision @Scale int - the scale for the value @MaximumLength - the maximum length of string values

2018-08-29

In the 1980’s and 1990’s, part of my time was spent as a lecturer and tech services manager at a university. I particularly loved working with final year students and their project work. At our regular meetings though, I also often got into discussion with the students about their career plans, as they were about to graduate. What amazed me was how many super-bright students were looking to take incredibly boring jobs working on ancient technologies, in what were basically programmer graveyards, and when I asked them why they were intending to go there, invariably they’d tell me that they thought those jobs would be long term and low risk.

2018-08-28

I’ve just finished delivering a bunch of presentations across New Zealand, and one of the sessions that I’ve delivered in several places is Things I Wish Developers Knew About SQL Server. In that session, I mentioned briefly that most single column SQL Server indexes that people create are at best pointless, and at worst counterproductive.

I often see people making a poor attempt at indexing, and when they don’t know what they need to do, they often add individual indexes on many columns in their tables. This isn’t a good idea.

2018-08-27

I’ve been listening to a lot of audio books lately and one that caught my eye was Mistakes were made (but not by me) by Carol Tavris and Elliot Aronson.

One of the mistakes that we all can make (and I’ve done it so many times myself) is to make a decision, find out it’s wrong, but to then hang on to it for way too long.

It was fascinating (and probably a little depressing) how often I could recognize aspects of myself while reading this book.

2018-08-24

I commonly run into a few connection-related scenarios:

• I’m working on a large query and need to run it against several servers, not concurrently, but one after the other.
• I’ve just made a database connection, and got my query ready, only to discover that I’ve connected to the wrong server.

Either way, what I’ve seen people do in these scenarios is to:

• Select all the text in the current query
• Copy it
• Open a new query window
• Paste the code

That’s all good but SQL Server Management Studio (SSMS) has had a simpler way of doing this for quite a while.

2018-08-23

Fellow MVP Paul White and others have discussed the indexing of small segments of strings, to make fast indexes that I think are somewhat like full-text indexes but potentially more efficient. Three character substrings are common, and are known as trigrams.

I’ve experimented with these at a number of site and seen really interesting (good) results.

In our free SDU Tools for developers and DBAs, we added a table-valued function ExtractTrigrams to help with this. You can pass it a string, and it will pull it apart for you, and return the set of trigrams. This would make it much easier for you to experiment with these types of indexes.

2018-08-22

There was a discussion the other day (on one of my lists), where someone pointed out that over many years, they had made tape backups, sent them via secured transport, and then paid for high-tech storage. And when they went to restore one of the tapes recently, there was nothing on the tape.

Over the years, I’ve lost count of the number of times I’ve heard stories like this. Long ago, I realized that you must never trust hardware.

2018-08-21

I regularly see code where SQL Server DBAs and developers are trying to log which user/login took a particular action within the database. There are many functions which appear to return the information needed, but there’s only one that should normally be used: ORIGINAL_LOGIN().

A login is the way that a connection is authenticated to the server ie: it’s the “who are you?” at the server level.

Most times, a user is a mapping of that login to a particular database.  The login and user will often have the same name (and I’d recommend that you do that to avoid confusion) but they do not have to be. A login Terry could be a user Mary in one database and a user Nga in another database.

2018-08-20

Over the last few months, I’ve also been reading a number of branding-related books. One that caught my eye was Introduction To Personal Branding: Ten Steps Toward A New Professional You by Mel Carson.

Part of the reason I looked into it was that Mel was an evangelist at Microsoft and as most would know, I spend a lot of time dealing with Microsoft in various ways.

It was also interesting as it’s a low cost book that Mel has published using CreateSpace and I’d often wondered about using that so I thought I’d check the outcome of that as well. Finally, it’s a short book. Listening to it on Audible would probably only take an hour or two.

2018-08-17

While SQL Server is quite fast at executing queries, when you are connecting from a client application like SQL Server Management Studio (SSMS), you might wonder how much time SQL Server spent executing the query, as opposed to how long the communication with the server took.

This type of information is available in the Client Statistics.

Let’s see an example. If I connect to a server in an Azure data center, I’ll have higher latency than for one in my own site. That will affect the wait time for a server response.

2018-08-16