SQL: Why do my SQL Server backup files keep getting much larger?

Another question that I keep seeing on SQL Server forums is:

Why do my SQL Server backup files keep getting much larger?

The backup commands that the poster is using look like this:

When you do a backup, what you're actually doing is adding a backup set to a media set. Each time you do a backup like that, you're adding another backup set to the same media set.

So, what the poster is missing is that the default for SQL Server backups, is to APPEND to the operating system file. So more and more backups are now being included in that file.

If you want the file to only include the single backup, and overwrite what's already there, you can do this instead:

INIT overwrites the backup file but keeps the media header. FORMAT overwrites the media header as well.

 

 

T-SQL 101: #71 Subtracting dates and times in SQL Server T-SQL with DATEDIFF and DATEDIFF_BIG

When you're writing T-SQL code, it's also common to need to work out how far apart two dates are, or it could even be dates and times.

The DATEDIFF() function does that. In this case I'm asking how many days is it from 28th of February 2019 to 31st of July 2019?  The answer is:

So it says the difference is 153 days. Now we could have used any of those other intervals for that. I mentioned them before in this post.

I could work at how many minutes that was or how many seconds that was between the two times. I could even ask how many months it is.

The first date that we pass is the from date or the from starting point. The second one is the ending time. So if I put those around the other way it will also work it out backwards, but I would see -153.

Finally, it might occur to you that if I asked for nanoseconds over a long period, a very big number would come back. It could be too big for an integer. So to get around that, there's also a DATEDIFF_BIG() function that does the same thing but returns a bigint instead.

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: List all SQL Server columns and their extended properties

I answered a forum question the other day about how to list all the columns in a SQL Server database. That's straightforward enough, but they also asked for all the extended properties for the column.

In case you need to do this, here's some code:

How it works

I start with the sys.columns view and join it to sys.tables and sys.schemas, to get the schema and table name. The other reason is to make sure it's not a Microsoft-supplied table. I also wanted to exclude the sysdiagrams table that is created when you first create a database diagram using SQL Server Management Studio. (Curiously, that one's not flagged as a Microsoft-supplied table).

The next join was to sys.types to get details of the data type. Notice that the join is on both system_type_id and user_type_id. You need to do that to allow for aliased data types. If you want the underlying base-type, change the ON clause to this:

While the first query would return names like sysname, the alternative ON clause would return same column as the underlying type like nvarchar(128).

Finally, I'm outer applying a query that finds the extended properties for a column. The sys.extended_properties view provides that. It can work with many different types of objects, so you need to closely define what you're looking for. The major_id is the object (i.e. the table) and the minor_id is the column_id. I've used an OUTER APPLY to make sure the column comes back, even if it doesn't have any extended properties.

If you only want columns with extended properties, you could change the OUTER APPLY to a CROSS APPLY instead.

 

 

 

T-SQL 101: #70 Adding and subtracting intervals in SQL Server T-SQL using DATEADD

When you're writing T-SQL, you might also need to add intervals to dates, perhaps add days or subtract days and so on. The DATEADD() function is the one that we can add or subtract intervals to the date and time.

Now in the example shown here, what I've said is in 20190228 or  28th of February 2019. I want to add on 12 days. To subtract 12 days, I would have just put -12 instead of 12. You can see the output here:

Now there are a wide variety of intervals. It's not just days. There are values right down to nanosecond.

Notice also though, that there are abbreviations for each of these. So instead of writing day, you could just write d. Or you could write dd, but I'd suggest you don't do that.

You might imagine that minute is m. But m is actually month and so on, so it can get very confusing. For someone who's reading your code, it's much, much better if you actually use the full word. So even words like nanosecond, second or millisecond, just put the whole word.

You'll see lots of old programming where people have used the abbreviation instead. Don't follow that.

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: Date Dimension Period Columns in SQL Server T-SQL

The DateDimensionColumns function in our free SDU Tools for developers and DBAs, has been really popular. It provides the standard columns that are part of a typical date dimension in a data warehouse. But we're especially proud of a new function that can be used to expand a date dimension with really useful additional information. We call it DateDimensionPeriodColumns.

The DateDimensionColumns function had the usual columns:

Date, Day Number, Day Name, Short Day Name, Month Name, Short Month Name, Month Number, Month Label, Year, Year Label, Day of Year, Fiscal Month Number, Fiscal Month Label, Fiscal Year, Fiscal Year Label, Day of Fiscal Year, ISO Week Number

But the DateDimensionPeriodColumns function adds many more useful details:

Is Today, Is Yesterday, Is Tomorrow, Is Future, Is Working Day, Is Last Working Day, Is Next Working Day, Is Weekend, Is Same Month, Is Month to Date, Is Same Month Last Year, Is Month to Date Last Year, Is Same Calendar Year, Is Calendar Year to Date, Is Last Calendar Year, Is Last Calendar Year to Date, Is Same Fiscal Year, Is Same Fiscal Year to Date, Is Last Fiscal Year, Is Last Fiscal Year to Date

That should let you say bye-bye to a whole lot of tricky DAX; it's easy to just use those columns for relative or period filtering

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

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

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

Latest version of the code

Note: the code might wrap when displayed below.

 

T-SQL 101: #69 Extracting date components in SQL Server T-SQL with DAY, MONTH, and YEAR

When you're writing T-SQL code, it's really common to need to extract components of a date, and the most common need is for year, month, and day. So T-SQL has separate functions just for that.

You can see the output from this query here:

The YEAR(), MONTH(), and DAY() functions take a date (or datetime, datetime2, smalldatetime) as input and return an integer for the year, month, or day.

You can also do this with the DATEPART() function but these are simpler and clearer when you want these main components of 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: How do I stop my database files going into the Users folder with localdb?

I've seen questions on the forums asking about where localdb stores its database files. Often, the question is about how to stop them going into the C:\Users folder. Clearly, that doesn't seem to be a good spot for them.

localdb stores database files in the C:\Users area by default as it's trying to find a location that the current user will have permission to write to. While I understand the rationale, that's not where most users will want those files.

The answer is to not let localdb pick where the files will go.

Instead of writing:

use a more complete command like:

While you're at it, check out the CREATE DATABASE syntax and examples for other options that might be useful to you, particularly the ones around filegrowth. (Hint: Use a fixed increment, not a percentage) Not everything will work on localdb but most will.

 

SQL: Why didn't my SQL Server transaction roll back on a primary key violation?

There's a question that I hear all the time in SQL Server forums:
 
Why didn't the transaction roll back when a primary key violation occurred?
Take a look at the code in the main image above. Would you expect it to execute the second and third INSERTs if the first INSERT failed with a primary key violation? So many people would. If you're one of them, read on.
 
By default, that's not how SQL Server works.
 
It might not be a primary key violation. It might be any number of other errors in the question, but the answer is still the same.
 

What's going on?

The primary key violation is an example of what's called a statement-terminating error. T-SQL data modification statements are atomic. If the INSERT was inserting three rows, none get inserted.
 
But only the statement ends, not the transaction.
 
If, instead, the error was a batch-terminating error, there's a difference. SQL Server aborts the transaction and rolls it back.
 

How can I change that?

If you want to change that behaviour, put
 

at the start of your code.

It tells SQL Server to promote statement-terminating errors to batch-terminating errors. Once you do that, the transaction code above works as expected. The primary key violation aborts and rolls back the transaction.

But I've seen it work without that!

I also occasionally hear people say that they've seen a different outcome. They didn't use XACT_ABORT and it still rolled back. What they're usually missing is that their data access library has set it on for them.
You can tell if it's on by executing this code:

Note: there are a few edge cases on this. Errors that relate to invalid object names, etc. are different. But for standard code, this is how it all works.

SDU Tools: Extracting initials from a name in SQL Server T-SQL

I recently came across a requirement to extract someone's initials from within their name. That was a new one for me, so we added a new function into our free SDU Tools for developers and DBAs. It's called InitialsFromName.

It's a straightforward scalar function that takes two parameters:

@Name nvarchar(max) is the name to extract the initials from

@Separator nvarchar(max) is a separator placed between the returned initials.  (Make it an empty string for none)

In the main image above, you can see the difference between the first two examples. The only difference is the separator.

You can use our tools as a complete set or as a library that provides great examples 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. The full current version of the code is also shown below:

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

Latest version of the code

Note: the code might wrap when displayed below.

 

T-SQL 101: #68 Current date and time in SQL Server T-SQL with SYSDATETIME and SYSDATETIMEOFFSET

It's really common to need to know the current date and time in T-SQL code. Ever since the beginning of the SQL Server product, we've had a function called GETDATE(). It returns the current date and time of the server as a datetime value.

In SQL Server 2008, we got new higher precision data types, and so new current date and time functions were added as well.

SYSDATETIME() is a function that returns the current date and time as a datetime2 value. And SYSDATETIMEOFFSET() returns the timezone offset for the server as well.

You can see the output below:

You might also want the current date and time, based on Universal Time (UTC aka GMT). SYSUTCDATETIME() does that. It calculates UTC time by deducting the server's current timezone offset from the current local date and time.

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.