Shortcut: Using Quick Launch in SQL Server Management Studio

In SQL Server Management Studio (SSMS) for SQL Server 2016, a new search tool called Quick Launch was added. It's this bar up the top:

Note that there's another bar underneath it to the left. That's not the Quick Launch bar. That's the Find bar.

While the Find bar is useful for searching for text within queries, etc., the Quick Launch bar is useful for searching within SSMS itself.

This is great because it means you don't have to remember where all the options for various things are set. Here's an example:

I typed theme and it told me about relevant things. The first is spot on, and is where I can change the theme, and the second is relevant too. Perhaps it's just the fonts that I want to change.

SDU Tools: XMLEncodeString and XMLDecodeString in T-SQL

On occasions, I need to work with XML data in SQL Server T-SQL and one problem is that I need to be able to encode and decode strings, using the XML encoding standards. For example, my strings might include % characters or < or > characters, etc.

So we added a pair of tools (XMLEncodeString and XMLDecodeString) our free SDU Tools for developers and DBAs.

You can see them in action in the main image above, and in this video:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

SQL: Text vs Varchar(max) – in row vs out of row storage

There was an interesting discussion on a local mailing list about the performance of a table that was just used for logging. One of the things that intrigued me was that the developer had used the text data type to store his log messages.

Now text, along with ntext and image, was deprecated back in 2005. Instead of text, ntext, and image, you should be using varchar(max), nvarchar(max), and varbinary(max).

The way the data is stored was also changed. The old data types used to store their data out of the table's data rows, and just store pointers within the table's data rows. This can be really slow. By comparison, the new types default to storing the data within the table's data rows, and using overflow pages if need be. For many applications, this can be much faster. Simple writes to the end of a log file are a good example of that.

The problem with this is that you can't just change the data type of a column from text to varchar(max). If you do that, the data won't get moved in-row. You need to rebuild the table.

Let's take a look at an example. I'll start by creating two tables, one using text; the other using varchar(max):

Then we'll populate both with 10,000 rows of data:

Finally, we'll query to see where the data was allocated:

Note the one table is significantly smaller and only has in-row data.

Let's try altering the column to change the data type:

And check again:

You can see that nothing has changed. Now let's try rebuilding the table:

And still no prize. So we'll do it the hard way:

And then we're happy:

T-SQL 101: #6 What are SQL Server databases?

You might be wondering what databases are.

A database is just a collection of information. Let's take a look at an example:

On this system, I have a number of user databases (AdventureWorks, PopkornKraze, and others). There are also some system databases:

These are ones that are provided by SQL server itself, and mostly are used to hold SQL Server's own configuration.

Now if we look inside any of the databases, we'll see a list of tables, views, and programmable objects like stored procedures, and functions. Tables are where most of the information lives. In PopkornKraze, you can see the tables that are present:

I have tables about cinemas and employees and orders and so on. Each table holds details about one specific type of thing. A database is a collection of relevant tables.

Inside each table, we then have columns that are used to determine the type of information held about the thing (cinema, employee, etc.) If I right-click the table, I can choose to select some rows from the tables. That can help me see what's in it.

Now the reason that we often break up databases rather than just having all of our information in a single database is that we might need different people to administer them and we might also be trying to reduce the complexity. So instead of having one big very complicated database, we might instead have a series of smaller databases such as human resource is and sales and so on.

And database then is a collection of related information.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing Queries for SQL Server course is online, on-demand, and low cost.

Shortcut: Configure SSMS auto-recover time, and recover unsaved queries

Every now and again, I come back to my laptop and find that it has rebooted for some reason, while I wasn't expecting it. A prime cause of that is Windows Updates. I really, really wish that wasn't so, but someone at Microsoft has decided that I must apply these updates. I have very little control over the time when that occurs. For example, if I'm on the road delivering presentations, there's no "wait till I get home" option for Windows Updates.

Either way, it's really helpful that SQL Server Management Studio (SSMS) now creates periodic backups of unsaved queries.

You can control how often it does this, and how long it keeps the files for by adjusting the values in Tools, Options here:

If SSMS crashes, you'll be prompted to recover the unsaved files when it restarts. But if you want to just go and find these files after another type of shutdown that you weren't planning on, you'll find them in your Documents folder in Windows, under the subfolders, SQL Server Management Studio, then Backup Files, then the name of a solution (likely Solution1 if you didn't create a scripts project).

SDU Tools: Time Period Dimension Columns in T-SQL

In a previous SDU Tools post, I described how to calculate the columns required for a date dimension. Another related tool that we have in our free SDU Tools for developers and DBAs, is a function that creates the columns needed for a time period dimension. It's called TimePeriodDimensionColumns.

You supply the starting time, and the number of minutes per time period,  and it supplies the output columns.

You can see it in action in the main image above, and in this video:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

SQL: The outcome of how DateDiffNoWeekends should work

In a recent post, I discussed questions that had come up about how we should calculate the number of days between two given dates, but excluding the weekends i.e. people just wanted the number of weekdays.

This relates to one of our SDU Tools called DateDiffNoWeekends. Some customers using it felt that it didn't calculate the days in the way they thought it should. And on reflection, I agreed.

Much of the discussion centred around how we should treat start and end dates, depending upon whether they were on the weekends or not.

I spent a lot of time considering this, and in the end, I think it came down to a pretty simple set of rules. When you specify just a date, you are really specifying the time 00:00:00 (i.e. midnight) on that date. So when I look at the period from StartDate to EndDate, I'm really looking at the whole of the StartDate, and none of the EndDate.

We then modified the logic to apply those rules. So if the period is from a Friday to the next day (Saturday), the number of weekdays would be 1. The period from the Friday to the Sunday (2 elapsed days later) would also be 1, as would the period from the Friday to the Monday (3 elapsed days later).  The only weekday would be the day time of the Friday.

This all seems to then make sense, and the update will be in v14 of SDU Tools that will ship about mid-March.



T-SQL 101: #5 Logging on to SQL Server

Before, you can access anything on SQL Server, apart from knowing what to call the server, the server itself needs to know who you are. This process is called authentication.

In SQL Server, a login is person that the server has identified.

Now access to the server by a login doesn't mean that you can access anything much on the server, it's just the first step where you get the server to trust who you are.

SQL server has 2 security modes.

In one mode (called Windows mode), SQL Server trusts the Windows operating system about who you are. You've already proven to Windows who you are, SQL Server trusts Windows, and so it trusts who you are, from your Windows login.

In the other mode (called mixed mode), SQL Server not only trust Windows logins, but it is willing to trust a list of logins that it manages itself, rather than just the logins that Windows manages. It records its own set of usernames, and passwords and uses those for authentication. (It doesn't store the password, it just stores a hash of the password, for security). The usernames, and password hashes are stored in the system master database.

Once you have connected to the server, you are considered to have "logged on", and you are considered to be a "login" by SQL Server.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing Queries for SQL Server course is online, on-demand, and low cost.

SQL: Calculating Day of the Week Reliably in T-SQL

Some T-SQL functions like DATEPART work differently based upon your session settings. If you want to write reliable code, you should always check for these issues, and try to write code that's impervious to these settings.

Let's take finding the day of the week as an example. It might seem that to find out if a day is a Sunday, you could just do this:

DECLARE @DayToCheck date = '20190224';

SELECT DATEPART(weekday, @DayToCheck);

By default, it returns the value 1 as expected. But it only does that if the DATEFIRST setting in the session was 7.

If I had changed the DATEFIRST value in my session like this:


Then that same code would return 6 instead. What we want is to get the desired output, regardless of the current session settings. The way to do this is instead to pick a date that we know was say a Saturday, like 6th January 1900. Then if we just work out the number of days from that date to our calculated date, modulo 7, we'll end up with 1 for Sunday, 2 for Monday, etc.

You can see the output in the main image above. And no matter what you change DATEFIRST to, the output doesn't change.


Shortcut: Clear server list in SSMS connection dialog

SQL Server Management Studio (SSMS) keeps a list of the server names that you have connected to, and prompts you with those when you drop-down the list while making a connection:

Eventually, that list can either become messy, it can include servers that don't exist anymore, and so on. You might want to clear up the list.

To do this in early versions of SSMS, you needed to locate the SqlStudio.bin file from the Documents and Settings area in your user profile.

Fortunately, that's no longer required.

All you need to do is to open this dialog, arrow down to the ones that you want to remove, and hit the Delete key.