Sql-Server

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

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: Security Level: Intro

Question:

Many applications connect to SQL Server databases with users that are members of the db_datareader and/or db_datawriter roles?

Why should you avoid using those roles?

Answer:

db_datareader and db_datawriter are fixed database roles that were provided for convenience. They aren’t a good option from a security perspective.

2021-04-09

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:

Many versions of SQL require every SQL statement to be terminated with a semicolon.

Since it was created, T-SQL has listed statement terminators as optional.

Are there any situations where they are required, and not optional?

2021-04-08

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:

Look at the following multi-row INSERT statement:

INSERT Sales.CustomerGroups
(
    CustomerGroupID, CustomerGroupName
)
VALUES (1, 'Group A'),
       (2, NULL),
       (3, 'Group C');

The column CustomerGroupName is defined as NOT NULL so the second row cannot be inserted.

2021-04-06

Imagine that I’ve used ROW_NUMBER to number all the cinemas in my database. I’ll get values from 1 to the maximum number of cinemas. But what if I want to number the cinemas within each city? i.e. Aberdeen has three cinemas, so number them 1, 2, and 3. But when we get to the next city, start the numbering again. We can do this by adding PARTITION BY to the OVER clause.

2021-04-05

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:

If you create a table using the following code, what would the name of the primary key constraint look like?

CREATE TABLE Sales.CustomerGroups
(
    CustomerGroupID int IDENTITY(1, 1) PRIMARY KEY,
    CustomerGroupName nvarchar(50) NOT NULL
);

How could you specify the name?

2021-04-01

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 are writing an INSERT statement in T-SQL, the column list is usually optional.

Why would including it be a good idea? When would it ever be required (i.e. not optional)?

Answer:

2021-03-30

I’ve been talking about the basic window functions in T-SQL and one that’s not well known but is surprisingly useful is NTILE.

I’m not sure on the name but it’s probably short for percentile. I don’t know why they didn’t call it a slightly more meaningful name, but what it says is take the output and break it up into bands or chunks of data.

So if I say NTILE(10), that’ll give me a tenth of the rows with a value of 1, another tenth of the rows with a value of 2, and so on.

2021-03-29