T-SQL 101: #56 Date components YEAR, MONTH, DAY in SQL Server T-SQL

Sometimes we have a date and we need to extract components of the date. For example, we might have a date and wonder what's the year or we have a date and wonder what the month is or the day.

While there are several ways to extract the year, the month, and/or the day from a date in SQL Server, there are functions designed to do precisely that.

The YEAR function takes a date and extracts the year. The MONTH function takes a date and extracts a month (as a month number from 1 to 12). And the DAY function takes a date and returns the day (again as a day number like 28, not the name of the day like Tuesday).

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 T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SDU Tools: Version 18 shipped and adds support for Azure SQL Database

I just wanted to give everyone a heads-up that we recently shipped version 18 of our free SDU Tools for developers and DBAs.

We've had so many requests for a version of SDU Tools that will work with Azure SQL Database. So, in version 18.0, we added that support.

So what's changed?

We've added two new scripts included in the download: one for adding the tools to Azure SQL DB and one for removing them.

The tools are very similar with a few exceptions.

  • Some tools (mostly ones that are server-related) don't make sense on Azure SQL DB and are removed from that version.
  • A number of tools could work across multiple databases. In that case, we've created new versions of those same tools but put the suffix "InCurrentDatabase" on the end of the name of the tool.

We hope you really find these useful and, as usual, welcome any feedback.

And if you haven't been using SDU Tools, you really should be. Even if you don't use them directly, they provide wonderful examples of how to write T-SQL functions and procedures.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:



Opinion: Inability to hire is an underestimated aspect of technical debt and old tooling

Most developers and analysts today are fairly aware of the impacts of technical debt. As technical debt grows, it takes longer and longer to get real customer or end-user work done. Worse, more and more time is spent triaging and squashing bugs. And one interesting aspect of technical debt is old tooling.

Old Tooling

I've written before about modern not being a synonym for better, but there comes a point where you need to modernise your tooling, even if it seems to be doing the job.

For me, the most tell-tale sign is when the author of the tools has apparently moved on from being interested in the tool. No matter how good a tool was, it needs to keep moving forward as requirements and operating environments evolve.

I deal a lot with Microsoft applications, and I often see these signs:

  • People from the development team seem to have moved on to new jobs
  • No-one is posting blog posts about what's happening
  • The tool stops turning up on conference sessions, Channel 9 videos, Azure Fridays, etc.
  • The flow of new features slows to a trickle or stops

If you ask the company, the tool is still supported and not going anywhere, but given the interest has moved on, you probably should too. I can only imagine that there's a desire to avoid upsetting anyone who's made a commitment to using the tool.

Many people refer to this type of tool as abandonware.

There are times though, when sudden spurts of life appear. A classic example is the work that was done on SQL Server Reporting Services in the 2016 edition. (I'll write more about SSRS another day)

Finding Staff

What I really wanted to mention today though is another nearly-hidden downside of using old tools, as a form of technical debt. And that's your ability to find staff. You do not want to underestimate the impact of this.

Today, I was reading about a large company in Europe with untold millions of lines of classic ASP code, embedded with VB. They can't work out how to move forward now.

I feel for them. I've done work with customers where their entire code base was in VB.NET and ASP.NET WebForms. At least that was a step up from classic ASP. They had many hundreds of developers. Even if you're a stalwart who loved the tooling, you need to stop and as yourself where you are going to find staff who'll share that love.

Developers who desperately need an income (and thus a job), and are living from pay to pay, might work for you. But developers who have any options at all, aren't likely to do that.

You'll find it harder and harder to locate good people who want to work for you. You'll end up paying them much, much more than would otherwise be appropriate, and you'll end up lowering your standards for whom you're prepared to employ.

From the point of view of a prospective staff member, do you really want to have your resume showing you starting with that code today?

Awesome image by Pepi Stojanovsky

And if you really are willing to do any coding for the pay, why not learn COBOL and cash in? It won't take that long to learn, and there's still plenty of work around for those wanting to write it.



T-SQL 101: #55 Current date and time values in SQL Server T-SQL

Finding out the current time is a really common requirement. But you also have to always consider whose concept of time you're considering. Is the the time at the server, or the time where you are? (You might not be in the same place, or more importantly, the same timezone).

The function SYSDATETIME() returns the current system date and time right now at the server. The data type that's returned is a datetime2.  It's the higher precision datetime data type that was introduced in SQL Server 2008.

In much older code, you'll see GETDATE() used. That function was a lower precision one that returned the older datetime data type. Unless there's a real need for the older data type, I'd suggest using SYSDATETIME() now.

In the query shown above, there's also a SYSDATETIMEOFFSET() that gives also gives you the current date and time at the server, but it returns a datetimeoffset data type i.e. it has a timezone offset as well. The timezoneoffset is based on the current setting at the server.

Once this code was executed, it returned the following output:

The server time as 9th January 2019 at 10:25 AM. The only difference between the two return values is that the second one includes a timezone offset of +11:00 or eleven hours. (That was daylight saving time in Melbourne which is otherwise 10 hours ahead of UTC).

UTC (universal time) is basically what we called Greenwich Mean Time (GMT) when I was a child. If you really want that time, rather than the local time at the server, there's a function for that too. It's SYSUTCDATETIME(). You can see the effect of it in the output here, where the 11 hours has been removed in the second value:

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 T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Doubling adjectives

One thing that fascinates me in Mandarin is the number of times you see a word repeated in a sentence. This can commonly occur with adjectives.

The simplest example would be a single character adjective that is repeated.

她的脸红红的。(Tā de liǎnhóng hóng de.)

This literally means "her face red red" or more likely "she blushed". But the word for red (Hóng) is repeated, in this case for emphasis.

Instead of saying:

你的眼睛真大的。(Nǐ de yǎnjīng zhēn dà de.) which says "your eyes are really big",

you get a similar effect by doubling the word for big:

你的眼睛大大的。(Nǐ de yǎnjīng dàdà de.)

Now where the fun begins is that many adjectives involve two characters, and instead of A becoming AA, the effect is that AB becomes AABB. (Not ABAB)

高高兴兴 (Gāo gāoxìng xìng) where 高兴 (Gāo xìng)  means happy.

漂漂亮亮 (Piào piàoliang liàng) where 漂亮 (Piào liàng) means beautiful.

Learning Mandarin

I'll write more soon on the best methods for learning Mandarin. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

SDU Tools: Script User Defined Database Roles in SQL Server

As part of our free SDU Tools for developers and DBAs, we have many scripting functions. SQL Server allows you to create your own database roles, and generally, you should do this instead of using the fixed database roles like db_owner, or db_datareader and db_datawriter, as it lets you allocate just the required permissions. To allow scripting these out, we've added a tool called ScriptUserDefinedDatabaseRoles.

It's a stored procedure (as it has to change databases) and takes a single parameter: @DatabaseName (sysname) as the name of the database whose roles you want to script.

You can use our tools as a set or as a great example of how to write functions like these.

Find out more

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

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:



SQL: Fix – Screen repainting issues in SQL Server Management Studio (SSMS)

Once again, I'm seeing lots of customers reporting screen repainting issues in SQL Server Management Studio (SSMS). It mostly seems to affect version 18 but I've also seen it in version 17. And it's most prevalent on Windows 10.

The typical issue is that you click on another open tab, and the contents of the tab doesn't repaint. You are still seeing the previous tab. If you click into the tab, you start to see bits from both tabs.

It's super annoying, and I've seen people lose quite a lot of work, because they didn't realise what was happening.

One possible fix

The one thing that seems to fix this in many situations is to change hardware acceleration. Previously, you'd do this by changing the screen driver settings, but where you need to do it for SSMS, is in the Tools>Options>General tab.

You need to disable the "Automatically adjust visual experience" option, and then you can disable the "Use hardware graphics acceleration if available" option.

For most clients, I've seen this fix the problem. I hope it helps you.

T-SQL 101: #54 Literal date and time values in SQL Server T-SQL

Now, one of the challenges when you go to write dates is that there's no standard separate format in T-SQL for how to write a date. Instead, we need to write it as a string. So it's very important to come up with a format that will work properly all the time the example.

In the example I have here, the order date is 20190128. If you use that format (8 digits for a date), it'll always work OK. SQL Server will take the first four as the year, then 2 for the month, 2 for the day and it does that, no matter what your settings are for your session or for your machine.

I've also shown an example of how to include the time.

Now a lot of people are confused with this, they would expect that the bottom format shown with dashes would be safe, or similar for slashes. It's not. That format is safe on the date data type and on datetime2, but it's not safe on the datetime or smalldatetime data types. For example, if you set your system to British English, that would be parsed as year-day-month, not year-month-day.

I think in general. You're better off to just avoid any confusion just write dates as 8 digits. You could also write them as the full ISO 8601 T format. But it has to be the complete format, including the T which is part of that standard generally that's a way more work than what we're suggesting here.

Ever since SQL Server 2012, another safe way to create a date is to use the DATEFROMPARTS function. It takes a year, a month, and a day and returns a date.

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 T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SQL: Update or insert of view or function 'xxx' failed because it contains a derived or constant field

In a recent forum post, the OP was asking about an issue where he had two tables "users" and "userroles" and was getting the following error when he tried to insert rows into the "userroles" table:

Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'UserRoles' failed because it contains a derived or constant field.

Now the first thing that was odd about this was that the error message was clearly showing that UserRoles wasn't a table. It's either a view or a table-valued function (TVF). An insert into a TVF isn't going to work, so I presume this is a view.

Now it's saying that it contains a derived or constant field. I have no idea what the table was actually called, so let's create one to try this:

Then an insert should be fine:

Running it returns the two rows inserted as expected.

How let's add some derived things. First, I'll add a derived column to the view and try the insert again:

Adding a derived column in the view works just as expected. What about adding it to the table instead?

I've set the view so that it doesn't use that derived column, and all is still good. But what if the view references the derived column?

And that's all good too. So what does that leave us?

Well a common view definition that I often come across is where the view returns data from a table, along with a constant row. Perhaps it's a "superuser" or something in this case. So let's try that:

And there's the error:

Msg 4406, Level 16, State 1, Line 78
Update or insert of view or function 'dbo.UserRoles' failed because it contains a derived or constant field.

You can't insert through a view when you have only this type of setup.