Sql-Server

While it’s not 100% required, your life is generally better if your databases are set to the maximum database compatibility level that your server supports. But it’s a bit of a pain to actually make that change. So in our free SDU Tools for developers and DBAs, we have a procedure that does just that. It’s called SetDatabaseCompabilityForAllDatabasesToMaximum.

It’s a stored procedure and takes no parameters at all.

It checks all the user databases and sets them to the maximum level allowed by the server that they are attached to. It is applicable to SQL Server only. (This procedure doesn’t exist in the Azure SQL Database version of the tools).

2020-02-19

I don’t like seeing abbreviations used when there’s no need to have them. Abbreviations can be overused and many times they’re cryptic. Worse, I often see them applied inconsistently.

In general, I try to avoid abbreviations unless they are on a tight list of ones that I use all the time.

A pet dislike of mine is seeing them used (even if consistently) for interval values in T-SQL functions like DATEADD, DATEDIFF, etc.

2020-02-18

When you’re working with T-SQL, you’ll often need to add time periods onto a date, perhaps onto today’s date. You might be adding (or subtracting) days, or hours, or minutes, or months, and more.

The DATEADD function is the one that we can use add or subtract intervals to the date and time. Now in the example shown here, I’ve started with 20190228, or 28th February 2019. I want to add on 12 days:

2020-02-17

I was working with a client the other day and we had what we thought was a simple situation:

• A table in an SSAS tabular model project
• The table had two partitions
• We needed to remove 6 columns from the table

So, we’d already removed the 6 columns from the SQL Server view that the tables were being loaded from. We just needed to update the tabular model.

Now for an unpartitioned table, that’s easy. You open the table properties, click Design, wait for a moment, click below the query, then on the Query menu, click the option to Refresh. When you then click Import, you can then save the changes. We do that all the time.

2020-02-14

It’s common to need to get a table of all dates between two dates, and in our free SDU Tools for developers and DBAs, we have a function DatesBetween that I’ve previously talked about. But sometimes it’s not a start and end date that you have, it’s a start date and a number of intervals. For example, I might want all the dates for the next three months, or three weeks, or two years. To make that easy, we’ve added a function called DatesInPeriod.

2020-02-12

SQL Server Reporting Services (SSRS) is a wonderful product, that’s still really important, even though the product group in Microsoft hasn’t been showing it much love lately.

I was at a client site the other week, and while we were using Power BI (PBI) for their dashboards and general visualizations, we were looking to use SSRS for part of the solution.

What fascinated me, is that when they talked to the local Microsoft field staff, they kept being told how SSRS was old technology, and asking why they’d look to use such old technology.

2020-02-11

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).

2020-02-10

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.

2020-02-05

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.

2020-02-03

I’ll start this post by mentioning that there’s a general consensus that you should avoid columns in tabular data models that have both dates and times. That’s largely because they don’t compress well. However, sometimes you really do need to have both a date and a time in a single column.

For example, if you want to sort one column by the date and time, we have no option to sort one column by two other columns. And if you’re going to create a composite column to get around that, you’re really back in the same problem as storing a datetime anyway.

2020-01-31