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:

http://sdutools.sqldownunder.com

 

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.

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:

http://sdutools.sqldownunder.com

 

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.

 

SDU Tools: Time Periods Between in SQL Server T-SQL

As part of our free SDU Tools for developers and DBAs, we have a function that generates dimension columns for individual time periods. To make that easy to use across a range of times in day, we've added a function called TimePeriodsBetween.

It's a table-valued function that takes three parameters:

@StartTime time is the first time to return
@EndTime is the last time to be returned
@MinutesPerPeriod is the number of minutes in each time period for the day (e.g. it's 15 for every quarter of an hour)

For each time period, it returns a key (an incrementing number for the position of the time period within the entire day), and the time.

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:

http://sdutools.sqldownunder.com

 

T-SQL 101: #53 Date and time data types in SQL Server T-SQL

SQL Server has a rich set of data types. Some of them were older data types and a number of new ones were introduced in SQL Server 2008.

One of the important ones was the date data type. It's just a date. It has no time associated with it and it's from the year 1 to the year 9999 based upon the Gregorian calendar. Well, they say the Gregorian calendar, even though that's funny because they was no Gregorian calendar in the year 1. Regardless, for the range of dates that we would work with, this will always work fine.

The time data type is one that varies in accuracy, much the same way that the decimal type does. You determine the precision that you need and it can go from zero up to 7. A value of 7 means you have 100 nanosecond accuracy.

The datetime2 was also part of SQL Server 2008 and that's just one of these new date data types combined with one of the new time data types. So it has both values using exactly the same range of values.

The datetimeoffset was also introduced in SQL Server 2008.This one takes a datetime2 value and adds a time zone offset to it. The offset can be from minus 14 (hours) to plus 14.

Now there are also two very common older data types: datetime and smalldatetime. Very few people would use smalldatetime today. It went from 1900 to 2079, but notice that it was only with a one minute precision. By comparison datetime was from 1753 to 9999 to the nearest 3 milliseconds.

It started at 1753 because that's basically when the English moved across to the Gregorian calendar. Regardless, it's hard to imagine you'll be working with dates back that far anyway. So a datetime had a reasonable range, but it always had a time value.

Ever since I've worked with SQL Server, there were requests for separate date and time data types until they appeared in 2008.

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.

Snowflake SQL for SQL Server Users – UNDROP

Overall the SQL language for Snowflake is somewhat of a subset of what's available in SQL Server with T-SQL. But there are some commands that really are useful. UNDROP is one of them.

I've talked in earlier posts about how Snowflake stores data in immutable micropartitions, and in other posts mentioned the concept of timetravel. Well, an added advantage of having older data still accessible is that you can quickly recover from "incidents".

Ever have a sinking feeling when you've run a script that drops an object and suddenly realized you were connected to the wrong database or server?

If you've been in this industry for any length of time, I know you have.

Things go wrong, no matter how careful you are. So it's good to have an option to help you out.

can be used to bring back a table that's previously been dropped. By default, that works for up to 24 hours but it's also possible to have longer retention periods.

When things really go wrong

But it's not just tables that can be undropped. For the really adventurous, there are also:

And these commands are fast. We're talking milliseconds.

There's a psychological aspect to this as well. Having the ability to recover so easily is also useful for increasing the confidence of the people who are making the changes. (Note: I'm not suggesting that should make people more careless. It's just important to know there is a better fallback).