Sql-Server

In my last T-SQL 101 post, I described the CASE statement. Until SQL Server 2012, that was the only real option that we had for choosing between alternate values. In SQL Server 2012, Microsoft gave us another option with the IIF function.

The IF function is very similar to the IF function in Microsoft Excel. It takes three parameters:

• A boolean value to check (normally this is an expression)
• A value that will be returned if the first parameter is true.
• A value that will be returned if the first parameter is false.

In the main image above, you can see the equivalent CASE statement and the simplified IF function equivalent.

2021-04-26

Another request that I received a while back, for a new function to our free SDU Tools for developers and DBAs, was to be able to find the day of the week, for the same day and month, over a range of years. A simple example would be to find what day Christmas will be each year for the next ten years. So we’ve added a new function WeekdayAcrossYears.

It takes four parameters:

2021-04-23

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Intro

Question:

Consider the following code:

SELECT CustomerID, CustomerName 
FROM Sales.Customers
WHERE BusinessCategory = 'Cafe'

UNION

SELECT CustomerID, CustomerName
FROM Sales.Customers
WHERE City = 'Toronto';

A: What would be the difference in results if the query was changed to use a UNION ALL instead of the UNION?

2021-04-22

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

When you add a column to an existing SQL Server table, the column is added to the end of the table. The column will end up with the highest column_id.

If a developer asks you how a column can be added to the middle of a table instead, what is your advice and how would you approach the request?

2021-04-21

The good news is that Visual Studio 2022 has been announced, and if you haven’t read the announcements, the big deal is that it’s finally a 64 bit tool. You can still build 32 bit apps with it, but the tool is now 64 bit. I hope SQL Server Management Server (SSMS) will also follow it to 64 bit but that’s not likely to be known as yet.

However, one interesting part of the announcement is a new font designed to be used for development. It’s called Cascadia Code. And even though Visual Studio 2022 isn’t available yet, the font is. I’ve been trying it in SSMS and quite like it. Previously I was using Consolas.

2021-04-20

You often need to choose from many outcomes, based upon a specific value. You might want to say if the value is 3 then choose Red, but if the value is 4 then choose Blue, and if the value is 5, then choose Green, etc. The way that you apply this type of logic in T-SQL is by using the CASE statement.

There are several ways you can write CASE statements. In the main image above, I have said that if the value is greater than or equal to 25, then say “Large”. If the value was between 20 and 24, then “Medium”. Otherwise (for all other values), choose “Small”.

2021-04-19

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

When you define a column default with code like below:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Customers;
GO

CREATE TABLE dbo.Customers
(
    CustomerID int IDENTITY(1,1)
        CONSTRAINT PK_dbo_Customers PRIMARY KEY,
    TradingName nvarchar(100) NOT NULL,
    CreatedDate datetime2(3) NOT NULL DEFAULT (SYSDATETIME())
);
GO

the system will define the name of the default. Can you give examples of why specifying the name of the default instead of letting the system supply it would be a good practice?

2021-04-15

Version 21 of our free SDU Tools for developers and DBAs is now released and winging their way out to our SDU Insiders.

You can find details on the tools here.

If you haven’t been using SDU Tools yet, I’d suggest downloading them and taking a look. At the very least, it can help when you’re trying to work out how to code something in T-SQL.

Along with the normal updates to SQL Server versions and builds, we’ve added the following new functions:

2021-04-14

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Development Level: Medium

Question:

Consider the following code:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Customers;
GO

CREATE TABLE dbo.Customers
(
    CustomerID int IDENTITY(1,1)
        CONSTRAINT PK_dbo_Customers PRIMARY KEY,
    TradingName nvarchar(100) NOT NULL,
    CreatedDate AS SYSDATETIME()
);
GO

INSERT dbo.Customers (TradingName)
VALUES (N'Customer A');
GO

SELECT * FROM dbo.Customers;
GO
SELECT * FROM dbo.Customers;
GO

Will the CreatedDate column return the same value in both SELECT statements?

2021-04-13

There are times when you’re writing in any programming language, that you want to output messages back to the client. In T-SQL, the statement provided for that is the PRINT statement.

PRINT is simple enough to use. You give it a  string to return and it prints that string.

It’s different to the SELECT statement, because the SELECT statement returns a rowset (i.e. a set of rows).

In the main image above, you can see a script with both a PRINT and a SELECT. When that script is executed, two things happen. The output of the SELECT statement is returned in the Results tab in SQL Server Management Studio as below:

2021-04-12