T-SQL 101: #20 Filtering rows to return with WHERE clause predicates

I showed earlier how we can retrieve data from a table using a SELECT clause. It can also be used to determine which columns are returned, and which table the data is being retrieved from. But we don't always want all the rows to be returned. The WHERE clause fixes that.

It's important to understand that the WHERE clause limits the rows returned to ones where the expression in the WHERE clause evaluates to a logical TRUE value.

In the example above, we're returning rows where the Size column contains the string value 370ml.

What often confuses users though is the opposite. If I write:

WHERE Size <> '370ml'

then I'm trying to get all the values except 370ml. Some rows might not have a Size specified i.e. they are NULL. You might expect them to be returned because they aren't equal to 370ml, however, the comparison:

NULL <> '370ml'

doesn't return TRUE. It returns NULL. And because we said that the WHERE clause only returns rows where the expression will be TRUE, those rows aren't returned.

This means that the WHERE clause:

WHERE Size <> '370ml'

will return only rows that have a value for Size and it isn't equal to 370ml.

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.

Learning Mandarin – Exaggeration for emphasis – to Death !

Most languages have expressions that are really massive exaggerations and Mandarin is no different. One of my favorites is the way they say:

死了 (Sǐle) which is basically like "to death"

An example would be:

我饿死了。(Wǒ è sǐle.) which is literally "I hungry death" followed by 了 (le) which some people regard as "past tense" but really is an indication that a state has changed. This sentence basically means "I'm starving" and so the state change is that I wasn't starving but now I am.

There are several other common examples of "to death":

我累死了。(Wǒ lèi sǐle) lèi means tired. So this is really "I'm exhausted".

我害怕死了。 (Wǒ hàipà sǐle) is really scared to death. Curiously, Google Translate gives it as "I'm scared of death". I suspect they're wrong there.

While these work OK in English, a number of other common ones don't.

我忙死了。(Wǒ máng sǐle) Google says "I'm busy" but it's really "I'm really, really, really busy". We don't really say we're "busy to death".

它贵死了。(Tā guì sǐle) I don't quite get how "expensive" to death makes much sense but it's OK in Mandarin. This means "it's so very, very expensive".

它难吃死了。(Tā nán chī sǐle) is even stranger. It's basically "it tastes really bad".

我都急死了。(Wǒ dū jísǐle) Google says "I'm in a hurry" but this is more like "I'm really anxious" or perhaps "I'm really nervous".

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

Book Review: The Science of Likability

A while back I purchased a number of Audible titles, thinking I'd listen to them as I traveled around. I grabbed a number of ones related to presentation and I thought I'd also check out some general self-improvement titles.

What I didn't realize, is although they had different titles, I'd basically bought a number of copies of essentially the same book, but with different titles. This was one of them.

The Science of Likability: 27 Studies to Master Charisma, Attract Friends, Captivate People, and Take Advantage of Human Psychology is a book by Partick King. This book is a 2017 update of a 2015 book that titled The Science of Likability: Charm, Wit, Humor, and the 16 Studies That Show You How to Master Them. 

However, it's not just this series of books. I was amazed how many other books by Patrick King I had inadvertently purchased. After listening to most of them, even though they all have different titles, so many basically deliver the same message.

And what is sad though, is that I didn't really find the message all that compelling in the first place. Many of the techniques that Patrick discussed seemed pretty cheesy to me. However, I'm sure there is an audience for this type of book. You only have to read the comments on Amazon to find how many people this sort of content helps.

I also have many introverted friends who I think would find many ideas in these books useful. If you are hesitant to join a group of people, or to enter a discussion, or to meet new people, or just to break the ice with someone you don't know, this could well be the book for you. While I find meeting and talking to new people quite invigorating, I do not underestimate how intimidated many people are about doing these things.

Bottom line?

I think I'm the wrong audience for Patrick's books. If however, you struggle with introversion and shudder when you think about talking to new people, etc. this book could well be for you.

Greg's rating: 5 out of 10

Note: as an Amazon Associate I earn (a pittance) from qualifying purchases but whether or not I recommend a book is unrelated to this. One day it might just help cover some of my site costs. (But given the rate, that's not really likely anyway).

Fixing Locking and Blocking Issues in SQL Server – Part 7 – Handling Deadlocks in T-SQL

This is part 7 in a series of posts:

  • Part 1 covered being sure there is a locking and blocking issue
  • Part 2 covered the impact of RCSI
  • Part 3 looked at the impacts of indexing on locking and blocking
  • Part 4 looked at what deadlocks really are and how SQL Server handles them
  • Part 5 looked at how applications should handle deadlocks
  • Part 6 looked at how to avoid deadlocks in the first place

Today, though, I want to look at how to handle deadlocks if you must do that in T-SQL.

The Situation

OK, let's start with the following situation:

Your application is calling a stored procedure, and it regularly ends up being involved in deadlocks. To make this easy to demonstrate, let's create a table of data:


USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Products;
GO

CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_dbo_Products PRIMARY KEY,
    ProductName nvarchar(50) NOT NULL,
    UnitPrice decimal(18,2) NOT NULL,
    OnHandQuantity decimal(18,3) NULL
);
GO

INSERT dbo.Products
(
    ProductID, ProductName, UnitPrice, OnHandQuantity
)
VALUES
    (1, N'Product 1', 12.35, 150),
    (2, N'Product 2', 14.30, 155),
    (3, N'Product 3', 15.80, 165);
GO

Then we'll create a stored procedure that can be used to convert a quantity of one product into another product. This can lead to deadlocks:


CREATE OR ALTER PROCEDURE dbo.ConvertProduct
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    UPDATE dbo.Products
        SET OnHandQuantity -= @FromProductQuantity
        WHERE ProductID = @FromProductID;

    WAITFOR DELAY '00:00:10';

    UPDATE dbo.Products
        SET OnHandQuantity += @ToProductQuantity
        WHERE ProductID = @ToProductID;

    COMMIT;
END;
GO

Note that I added a delay of 10 seconds between the updates to make it easy for us to generate a deadlock. So let's cause one now.

In one query window, execute the following:


-- Query window 1

USE tempdb;
GO

EXEC dbo.ConvertProduct
    @FromProductID = 2,
    @FromProductQuantity = 12.0,
    @ToProductID = 3,
    @ToProductQuantity = 1;

And at the same time, in another query window, execute a similar but different query:


-- Query window 2

USE tempdb;
GO

EXEC dbo.ConvertProduct
    @FromProductID = 3,
    @FromProductQuantity = 1,
    @ToProductID = 2,
    @ToProductQuantity = 14.1;

No surprise, we get the dreaded deadlock message in one window:

Adding Retry Logic

So how do we fix this? We can either change this procedure to have retry logic, or we can rename this procedure and call it from a wrapper procedure that has the deadlock retry logic.

Let's do the second option this time.

We'll recreate the procedure as ConvertProduct_Inner:


CREATE OR ALTER PROCEDURE dbo.ConvertProduct_Inner
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    UPDATE dbo.Products
        SET OnHandQuantity -= @FromProductQuantity
        WHERE ProductID = @FromProductID;

    WAITFOR DELAY '00:00:10';

    UPDATE dbo.Products
        SET OnHandQuantity += @ToProductQuantity
        WHERE ProductID = @ToProductID;

    COMMIT;
END;
GO

And then we'll add a wrapper procedure:


CREATE OR ALTER PROCEDURE dbo.ConvertProduct
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @MAXIMUM_RETRIES int = 5;

    DECLARE @RemainingRetries int = @MAXIMUM_RETRIES;

    WHILE (@RemainingRetries &gt; 0) -- retry update for victim
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            EXEC dbo.ConvertProduct_Inner
                @FromProductID = @FromProductID,
                @FromProductQuantity = @FromProductQuantity,
                @ToProductID = @ToProductID,
                @ToProductQuantity = @ToProductQuantity;

            SET @RemainingRetries = 0;

            COMMIT;
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) -- deadlock victim
            BEGIN
                SET @RemainingRetries -= 1;
                PRINT 'Warning: Deadlock occurred';
                PRINT 'Remaining retries: '
                      + CAST(@RemainingRetries AS varchar(20));
            END
            ELSE BEGIN
                SET @RemainingRetries = -1;
            END;
            IF XACT_STATE() &lt;&gt; 0
            BEGIN
                ROLLBACK TRANSACTION;
            END;
      END CATCH;
    END;
END;
GO

Now I'll execute both procedures again, and notice that both complete as expected. One has a warning about a deadlock that had occurred but was retried:

This is great, and is something we couldn't do before SQL Server 2005 so many people weren't used to doing it. However, while this will help at the T-SQL end, most other types of retries need to happen at the client end.

 

 

 

SDU Tools: List user access to Reporting Services content

I often need to document the access that various users have to content stored in SQL Server Reporting Services (SSRS). Surprisingly, I didn't find any built-in way to do that.

We had started to add some Reporting Services related options to our free SDU Tools for developers and DBAs, and, just for this, we added a new procedure RSListUserAccessToContent.

It takes two parameters:

@IsOrderedByUserName bit -> Is the output ordered by user (default yes else by item)
@RSDatabaseName sysname -> Reporting Services DB name (default is ReportServer)

The output has the following columns: UserName, RoleName, PathName, FolderName, ItemName, ItemType.

You can see the procedure in action in the image above, and in the video here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

 

Opinion: Whatever happened to "ly" ?

In recent years, there's an odd trend that I've been noticing. Adverbs seem to be getting replaced by adjectives, and at an increasingly fast rate. I see signs that say things like this:

Drive Safe

Now when I was at school, we'd have been given a hard time for writing that. We'd have been told in no uncertain terms that it should have been:

Drive Safely

I was trying to work out if it was more of a US-based thing. I see it far more often in US-based writing, yet it's also happening in the UK, Australia, and others as well.

Puzzled by this, I was starting to wonder if it was just me, and, more importantly:

Whatever happened to "ly" ?

Turns out that I'm not alone in wondering this. This Quora discussion asked Why have so many people stopped using adverbs and instead use adjectives, such as "quick" instead of "quickly"?

The article argues that it's part of a very long-term trend, and that you notice it more as you age, and as you visit other places and come across other dialects.

More concerning(ly), this article asks: Is it poor style to use adverbs ending in "ly" in formal writing? Some commenting on the article make a more curious claim: Some grammarians consider "ly" ending adverbs as bad style in formal writing.

Now I don't see specific evidence to support that, and one person commented that it was primarily advice for sci-fi writers.

A recent article in the Guardian asks:  Where have all the adverbs gone? And how did they go?

The author says: Meanwhile, in everyday parlance in America, people are quite happy to do things "real quick". I hope that doesn't catch on here. There's plenty of time to bother saying "really quickly". 

At least it appears that I'm not the only one that's wondering where "ly" went. I'd love to hear your thoughts on it.

T-SQL 101: #19 Querying literals, expressions, and functions in T-SQL

Apart from data just in a table SQL server can select other things like the ones shown here:

If I say SELECT 2, it just returns the value 2.

If I say SELECT 'Hello', it just returns Hello.

Both of those are examples of what's called a literal value, which is an exact value that doesn't change.

SELECT 4 + 5 is an example of an expression. This is where we can work something out to get the value that needs to be returned. No surprise, that will return 9 just as you'd.

Finally, we can also select from functions. SYSDATETIME()  is a function that returns the current date and time at the server. We don't have to care about how that works internally, we can just SELECT it, and it'll tell us the current date and time.

So we're able to use expressions and values and both can be returned. Functions, though, are programmable objects, which can be used either as values or even as tables in a FROM clause but we'll see more about that in a later post.

Not all SQL engines do this

It's worth noting that not all SQL database engines allow us to have a SELECT statement without a FROM clause. For example, in Oracle, you can't just say SELECT 4 + 5. Instead, they have a dummy table called dual, which is a single column, single row table with a dummy value. Then you can say SELECT 4 + 5 FROM dual; instead.

I'm glad SQL Server doesn't require this.

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.

Learning Mandarin: Him, her, it, they, and them

In my previous Learning Mandarin post, I discussed personal pronouns and adjectives. Additional useful related words are:

Him, her, it, they, and them

Similar to the way that we have male, female, and other genders for these pronouns, Mandarin has the same concept but what's interesting is that the spoken words are the same for all of them. The written characters are different for each:

(Tā) is the word for "he".

(Tā) is the word for "she".

Notice that the right-hand side of both characters is the same but the left-hand part differs. In the male version, the left-hand is a squashed form of the character (Rén) which means person. You might recognize it from the Chinese currency 人民币 (Rénmínbì) which is literally "the people's currency".

For the female version, the left-hand part (known as a radical) is a squashed form of the character (Nǚ) which means "women", hence its use in "she".

For inanimate objects, yet another different written character is used:

(Tā) is the word for "it".

In English, we also change the words depending upon whether they are subjects or objects in a sentence i.e. He did it. I gave it to him.

Mandarin is easier with this because it uses the same words:

他做了。(Tā zuòle) is "He did it".

把这个给他。(Bǎ zhège gěi tā) is "Give it to him"

And finally, similar to last time, you make these plural by adding (Men) to the end, so

他们 (Tāmen) is the equivalent of "they" when it's the subject of a sentence, and the equivalent of "them" when it's the object.

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

SQL: Storing the names of objects in SQL Server tables and variables

When I'm writing SQL Server scripts, I often need to store the name of SQL Server objects (like databases, schemas, tables, procedures, etc.) in variables.

That can also happen when I'm creating tables. I might need to store a SQL Server object name (like a login name or a user name) in a column of a table.

So which data type should be used? varchar(100), varchar(200), nvarchar(max), etc. etc. ??

The answer is: sysname

sysname is a built-in data type that's currently stored as nvarchar(128).

While you could use nvarchar(128), that wouldn't be a great idea. If the SQL Server team ever need to change the size of object names, you'll have a problem if you'd used nvarchar(128). If, as recommended, you'd used the sysname data type, you'll be fine.

Fixing Locking and Blocking Issues in SQL Server – Part 6 – Avoiding Deadlocks

This is part 6 in a series of posts:

  • Part 1 covered being sure there is a locking and blocking issue
  • Part 2 covered the impact of RCSI
  • Part 3 looked at the impacts of indexing on locking and blocking
  • Part 4 looked at what deadlocks really are and how SQL Server handles them
  • Part 5 looked at how applications should handle deadlocks

Today, though, I want to look at how to try to avoid deadlocks in the first place.

You Can't

Let's start with the bad news. You can't totally avoid deadlocks without doing something like serializing all access to the database.

As I mentioned in Part 5, you need to expect and handle deadlocks when they occur.

Now we have the bad news out of the way, let's look at things that can help to minimize them. Even though we make our applications capable of handling them, I want to see as few of them happening as possible.

Fix the query performance

Part one of this series talked a bit about this. I often see significant blocking issues (and associated deadlocks) completely disappear when the queries run quickly.

The longer that a query runs for, the longer it's likely to hold locks. We want to minimize that time. Indexing is your friend here.

Minimize the locking (volume and duration)

The fewer things that are blocked, and the shorter time that they're blocked, the less chance there are for deadlocks in the first place. There are four key aspects of this:

First, (no surprise), indexing is a key issue here. If you don't give SQL Server suitable indexes to let it find just the data that you need to lock, you shouldn't be surprised when it ends up locking entire partitions or tables.

Second, the design of your tables is also an issue. A properly normalized database schema is easier to work with in terms of minimizing locks.

Third, you need to use the lowest isolation level that works for your queries. Part two of this series talked about using RCSI. Often that will really help. Your biggest pain point though is going to come from applications that insist on using Serializable as an isolation level. Unfortunately, many Microsoft applications default to using this (I'm looking at you Biztalk's SQL Adapter), as do many other things in Windows like Component Services. Whenever you have a choice and it's appropriate, reduce the isolation level to the lowest you can work with.

You might be wondering about the use of NOLOCK here. Clearly it might help if you can deal with the risk. Basically, you're giving up consistency for reduced locking and performance. If you're working only with archive data that isn't changing, it might be an ok option.

If you're working with data that's being actively changed, it's a significant risk. It's hard to explain duplicated or missing rows on reports, and even harder to explain data on your report that doesn't exist in the database because it was rolled back.

I also see people trying to put NOLOCK on INSERT, UPDATE, DELETE. Here's a hint: that's not going to work.

Fourth, you need to hold locks for the shortest time that you possibly can. I'm not talking about holding locks across user interactions, that would be just plain dumb. You need to get as much work done as quickly as possible. If you want high concurrency, holding transactions across multiple round trips to the database server isn't clever, yet I see it all the time.

Don't create a proc to insert an order header, then another proc to insert an order detail line, and then call them one by one within a transaction started by the client application. Instead, pass the whole order (or even multiple orders where it makes sense) directly to a procedure on the SQL Server that does the whole job at once. Table-valued parameters are your friend here.

Locks are typically happening within transactions so you need to keep the transaction durations very short. And that means that you need to manage them properly. Start them only when needed and commit them or roll them back as soon as possible.

The transaction handling in JDBC is a great example of how not to handle transactions.

It turns on chained mode (which means that SQL Server starts a transaction automagically when a data modification occurs), and then just periodically runs a command that says "if there is a transaction, commit it". Please don't do this. At least not if you're looking for high performance and/or concurrency.

Serialize the workload

One useful, (but possibly contentious) method of reducing deadlocks, is to try to ensure that you lock objects in the database in the same order within all your logic.

I remember a site that I went to in the 1980s where they had a rule that tables were always used in alphabetical order. That sounded like a bad idea at the time, and it still sounds like a bad idea.

However, you can make a big difference to deadlocks by using tables in the same logical order every time. For example, if you acquire an order number from a sequence, then using it in an order header, then write the order detail lines, write an audit entry, etc. and keep to that same type of order, you'll have a lot less deadlocks to deal with, as it will naturally serialize the work to be done.

When all else fails

Even with all the above, you still will likely have some deadlocks. Apart from all the handling options, you might consider exerting some control over who loses when a deadlock occurs. Perhaps you have a background task that you don't mind dying and restarting. In that case, you can set its deadlock priority low so it offers itself as a victim, rather than just having SQL Server work out who's written the least to the transaction log.

In the next post, I'll look at retry logic at the server end.