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.

Learning Mandarin: The direction words

I mentioned in an earlier post that I find it easier to remember groups of words, rather than trying to remember individual words. An interesting (and very useful) group of words, are the direction words.

The first of these are the compass directions.

In English, we say "north, south, east, and west". Directly translated, they would be:

北,南,东,西  (Běi, nán, dōng, xī)

Now I don't know how we ever came to choose that order to say them, and it's interesting that we go top, bottom, right, left.

Chinese don't do that. They say "east, south, west, and north". So, you'll instead hear:

东,南,西,北   (Dōng, nán, xī, běi)

As well as the basic directions, in English, we also say "northward, southward", etc. To indicate the "in a northerly direction", in Mandarin, the word  方 (Fāng) is appended. So:

北方  (Běifāng) is like "northward".

Another useful suffix word is (Biān) is like "side". So

北边 (Běibian) is the "north side".

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite is Tutor Ming. If you decide to try it, click here and it's a bit cheaper for you, and for me.

Opinion: Security is hard – the Sad Tale of the Windows Calculator

Ever since I've done development work on Windows, I've seen two things happening:

  • People arguing that development should never be performed in an admin account
  • People using admin accounts for development because otherwise they can't get anything done

This is a long-term nasty problem, but I thought I'd share today an anecdote I was told by a Microsoft product group member about how easy it is to get security wrong during development, if you always develop as an admin.

I remembered this today when I noted that Windows Calculator was now going to be open source.

When moving to a new version of Windows, one of the existing applications that failed a security review was Windows Calculator. When I heard this, I thought they were joking.

The application does almost nothing. But it still failed security testing.

The reason was that when you changed from normal mode to scientific mode, it was storing your setting in the wrong registry key. It was storing the value in an admin-only key.

So calculator was an admin-only application at that time.

If it had been being tested as a normal user, it would not have worked.

I've always thought it was the best example of how easy it is to mess up building applications when you're always running as an administrator.

 

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:

http://sdutools.sqldownunder.com

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:

SET DATEFIRST 2;

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.

SDU Tools: Calculate Date Dimension Columns in T-SQL

Calculating the columns required for a date dimension using T-SQL is a pretty common request from SQL Server developers. So we decided to make it really easy to do. In our free SDU Tools for developers and DBAs, there is a table-valued function called DateDimensionColumns, for just this purpose.

You supply a date, and the month that your financial year starts in, and it supplies the output columns.

You can also easily combine it with our DatesBetween function to get dimension columns for a range of dates:

You can see it in action here:

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

http://sdutools.sqldownunder.com