The Bit Bucket

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.

2019-05-30

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)

2019-05-29

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.

2019-05-28

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.

2019-05-27

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

2019-05-24

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.

2019-05-23

I write a lot of utility code for SQL Server. Many of the system tables include values for LoginTypeID and UserTypeID but I’ve never found a view in SQL Server that returns a description of each of those values.

To make it easy, in our free SDU Tools for developers and DBAs, we added two views (LoginTypes and UserTypes) to help.

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

2019-05-22

One of the things that I love about digital books and audio books is how quickly I can go from a friend talking about one, to actually having it. This book is one of those. I can’t actually remember who recommended this one but I recall looking it up immediately and purchasing it. It’s The Little Book of Luck by Richard Wiseman.

Wiseman is a professor for public understanding of psychology in the UK. He states his interests as “unusual areas including deception, luck, humour, and the paranormal”.

2019-05-21

If I query rows from a SQL Server table, I don’t always want all the rows returned. In particular, I might not want any duplicates. I can remove them by using DISTINCT.

Here’s a simple example. I want a list of the sizes that products can come in, so I execute this query:

Note that although I get a list of sizes, I get a row returned for every row in the table. If I add DISTINCT to the query, look at the effect that it has:

2019-05-20

I spend a lot of time reviewing database designs at customer sites. So often, I come across bizarre decisions that have been taken in the choice of data types within the databases. One problem that I see all the time is the way that email addresses have been stored.