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
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
Occasionally I’ve needed to take a string, and extract all the words out of it. For example a string like ‘hello there greg’ might lead me to want the three words ‘hello’, ’there’, and ‘greg’. Note that I usually want them trimmed, not just extracted.
In our free SDU Tools for developers and DBAs, we added a table-valued function ExtractTrimmedWords to help with this. You can pass it a string, and it will pull it apart for you, assuming that you have whitespace separating the words.
2018-08-15
When you’ve worked with a product like SQL Server for a long time, and more importantly, are one of the odd people who’ve read a great amount of the documentation simply for interest, it feels really strange to come across a basic function that you’d never noticed before. That’s how I felt when someone mentioned the T-SQL SIGN function.
I thought, “the what function??”.
Now it works pretty much as you’d expect. It returns:
2018-08-13
Hi Folks,
We have a whole series of online and on-demand courses coming. The first two of these are available right now.
The good news? The first one is free and the second one has a big introductory discount.
The first course 4 Steps to Faster SQL Server Applications is a short course for developers, new DBAs, and testers, etc. who don’t know anything much about tuning SQL Server applications. It focuses on finding and fixing the most problematic queries, either in terms of index tuning, or removing repetitive queries, all using free tools.
2018-08-10
When I am writing my own code using a .NET (or other) language, I have a great deal of control of how the connection string that my application uses to connect to SQL Server is configured.
In particular, I might need to add another parameter or two.
As a simple example, you might have a multi-subnet Availability Group, spread across a production site and a disaster recovery site. It’s common to then have an Availability Group Listener in both subnets.
2018-08-09
The PRINT statement in SQL Server’s T-SQL language is useful but one of the biggest restrictions with it is the size of the strings that it can print. Where this becomes a big issue is if you are needing to create dynamic SQL statements (which you obviously need to be careful of in the first place) or scripting database objects, and the statements need to be either executed or printed.
2018-08-08
Choosing the right database permission can be hard. I’ve lost count of the number of times I’ve heard a discussion like this:
I need to let Mary restore truncate one of the tables but I don’t want to give her permission to do it, in case she stuffs it up.
or
I need to let Paul restore this database but I don’t want him to be able to restore other databases, and I’m worried if I give him the permission, he might accidentally do something bad and I’ll be blamed for it.
2018-08-06
Hi Folks, we’re looking forward to doing a number of presentations across New Zealand starting around the end of this month.
Aug 27th (Mon): SQL Server User Group - Wellington (Things I wish Developers knew about SQL Server) Details here
Aug 28th (Tue): Data Management and Analytics Meetup - Wellington (A Comprehensive Look at What’s New in SQL Server 2017 - and ongoing product directions) Details here
Aug 31st (Fri): Pre-conference Day for SQL Saturday - Auckland (Developing SQL Server Applications that Perform) Register here
2018-08-04
