Sql-Server

I’ve shown in recent posts how to extract components out of dates and times. You might also want the name of a month or a day, rather than just its number. The DATENAME() function does that.

In this query, I’ve asked for the name of the month that’s part of the date 28th February 2019. So it returns:

An important concept here though is that this is based upon my regional settings. Mine currently are set to US English, to it returned February. But if I had different regional settings, it would display the name in whatever region I was configured to use. So if I had a French configuration that would come back with Février instead.

2020-06-08

I often say that I love writing code that writes code. I use T-SQL to generate T-SQL scripts all the time. (I use Excel to do it too but that’s a story for another day). An issue I ran into a while back though, was how to output decimal numbers. I wanted to make sure I was using the correct decimal separator for the user who was running the script.

2020-06-03

In previous posts, I mentioned that you can easily extract the year, month, and day from dates and times, but you might want other components. That’s what the DATEPART() function does.

You can see the output from the query above here:

To use this function , we put the interval that we’re after and then the value we want to find it out from. So if you look at this one, I said I wanted the day from 28th of February 2019. The day is 28.

2020-06-01

I’m often working with developers who are migrating to SQL Server from Oracle. (Well, who can blame them?) One of the first questions that I’m often asked though, is where is the dual table?

In Oracle, a SELECT statement must have a FROM clause.

In SQL Server, this code will work fine:

SELECT UPPER('Hello');

It will just return the string converted to upper case. You can alias it as well:

SELECT UPPER('Hello') AS Greeting;

Again, you’ll get the converted string but the column will be called Greeting.

2020-05-29

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:

BACKUP DATABASE @DatabaseName
    TO DISK = @BackupFile
    WITH COMPRESSION;

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.

2020-05-28

I’ve found the sys.time_zone_info system view that was added in SQL Server 2016, really useful in a number of situations. It returns the name of each timezone, along with their current offset from UTC (like +01:00 or -07:00 ) and if they are or are not currently in daylight savings time.

However, what I’ve found myself needing to do is to convert the timezone offset part to a decimal number of hours.

2020-05-27

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.

2020-05-25

I used to be able to tell when someone moved from working with Access databases and arrived at SQL Server. What was the tell-tale sign?

All their tables had names like tblCustomers.

We’ve pretty much stopped people doing that, and we also have pretty much stamped out Hungarian Notation, at least in SQL Server.  We don’t use variable names like @intCreditRating, although there are still a number of people that I see clinging to Hungarian Notation when they work with SSIS, particularly in their .NET code. In their code, you’ll see values like strCustomerName and so on. The irony is that .NET developers long since moved past doing that, yet people writing .NET code in SSIS still do it.

2020-05-22

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:

SELECT s.[name] AS SchemaName,
       t.[name] AS TableName,
       c.[name] AS ColumnName,
       c.is_nullable AS IsNullable,
       typ.[name] AS DataTypeName,
       c.max_length AS MaximumLength,
       c.[precision] AS [Precision],
       c.scale AS Scale, 
       ep.ExtendedPropertyName,
       ep.ExtendedPropertyValue
FROM sys.columns AS c
INNER JOIN sys.tables AS t
ON t.object_id = c.object_id 
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id 
INNER JOIN sys.types AS typ
ON typ.system_type_id = c.system_type_id 
AND typ.user_type_id = c.user_type_id 
OUTER APPLY 
(
    SELECT ep.[name] AS ExtendedPropertyName,
           ep.[value] AS ExtendedPropertyValue
    FROM sys.extended_properties AS ep
    WHERE ep.major_id = c.object_id 
    AND ep.minor_id = c.column_id
) AS ep
WHERE t.is_ms_shipped = 0
AND t.[name] <> N'sysdiagrams'
ORDER BY SchemaName, TableName, ColumnName, ExtendedPropertyName;

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

2020-05-21

The DatesBetweenNoWeekends function in our free SDU Tools for developers and DBAs, is really popular. It provides a range of dates between starting and ending dates, ignoring weekends.

DatesBetweenNoWeekends is a simple table-valued function that takes two parameters:

@StartDate - the first date to return @EndDate - the last date to return

The columns returned are:

DateNumber - a sequential number for the dates returned DateValue - the date

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:

2020-05-20