The Bit Bucket

I’ve seen questions on the forums asking about where localdb stores its database files. Often, the question is about how to stop them going into the C:\Users folder. Clearly, that doesn’t seem to be a good spot for them.

localdb stores database files in the C:\Users area by default as it’s trying to find a location that the current user will have permission to write to. While I understand the rationale, that’s not where most users will want those files.

2020-05-08

There’s a question that I hear all the time in SQL Server forums:

Why didn’t the transaction roll back when a primary key violation occurred?

Take a look at the code in the main image above. Would you expect it to execute the second and third INSERTs if the first INSERT failed with a primary key violation? So many people would. If you’re one of them, read on.

By default, that’s not how SQL Server works.

2020-05-07

I recently came across a requirement to extract someone’s initials from within their name. That was a new one for me, so we added a new function into our free SDU Tools for developers and DBAs. It’s called InitialsFromName.

It’s a straightforward scalar function that takes two parameters:

@Name nvarchar(max) is the name to extract the initials from

@Separator nvarchar(max) is a separator placed between the returned initials.  (Make it an empty string for none)

2020-05-06

I hear about frequent drive-by shootings in some countries. Fortunately that doesn’t happen where I live. But what I come across all the time on Q&A forums, is what I’d like to call DBTs (Drive by trashings).

It usually starts when someone makes a genuine effort to try to help answer a question. The DBT (drive-by trasher) pops in and leaves a nasty unhelpful message. It could be “That’s misleading” or “That’s wrong” or “You don’t understand how it works”.

2020-05-05

It’s really common to need to know the current date and time in T-SQL code. Ever since the beginning of the SQL Server product, we’ve had a function called GETDATE(). It returns the current date and time of the server as a datetime value.

In SQL Server 2008, we got new higher precision data types, and so new current date and time functions were added as well.

SYSDATETIME() is a function that returns the current date and time as a datetime2 value. And SYSDATETIMEOFFSET() returns the timezone offset for the server as well.

2020-05-04

Today I wanted to call out a common mistake that I see at websites all over the country. Don’t add pages to your website if you’re not going to update them.

I’m particularly talking about pages with names like “News”, “Articles”, “Blog Posts”, etc. They’re often added when someone first builds a website and is full of hope for how it will be used.

And then it isn’t.

Old News

I’ve lost count of how many sites I visit where there’s a News section and when I visit it, there are two or three entries, often years apart. Or worse, there are a few entries from five years ago when the website was first created.

2020-05-01

In SQL Server 2005, I really appreciated the addition of DDL replication.  Before that, I found that making schema changes to replicated tables was a pain in the neck. Almost every time, I was there dropping replication and later setting it up again. That’s easy but takes a long time, so sometimes, I’d resort to lots of manual hacking.

Using DDL replication, I could make a whole lot of different types of changes and the subscribers would be updated automatically, at least in most standard subscriptions.

2020-04-30

I mentioned in my last two SDU_Tools posts that I get really good feedback all the time from users of our free SDU Tools for developers and DBAs, but none more than when we have more and more date and time functions. Yet another pair of functions that we added in v19 that was recently shipped was StartOfYear and EndOfYear.

We had feedback that even though we had StartOfFinancialYear and EndOfFinancialYear, we didn’t have just StartOfYear and EndOfYear. So we’ve fixed that. These are simple scalar functions that take a single date parameter, and return the date for the start of the calendar year and the end of the calendar year that contain the date provided.

2020-04-29

I’ve had a number of people asking me lately about what’s happening with SQL Server certifications. There are a number of clients that I deal with, where they require their staff to regularly update their certifications. It’s been part of their ongoing commitment to training.

In particular, I’m asked When will the new SQL Server certifications be available?

And the (perhaps surprising for some) answer is: They won’t be.

The way that Microsoft certifies people has gone through a seismic shift. The entire focus is now on certifying people’s ability to perform roles, rather than their ability to use a particular product.

2020-04-28

One of the challenges when you go to write dates is that there’s no standard format in SQL Server for how to write a date. Some other languages have a specific format for writing dates. In T-SQL, we have to write it as a string.

Because there’s no special way to write dates, it’s very important to come up with a format that will work properly all the time. The example I’ve got here for OrderDate is 20190128. That is a safe format that always works. SQL Server will interpret it as YYYYMMDD every time, no matter what regional or language settings you have configured.

2020-04-27