Sql-Server

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

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.

One data element or more?

The first thing you need to decide is whether an email address is really one data element or more.

2019-05-17

This is part 5 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

Today, though, I want to look at how applications should deal with deadlocks.

Application Deadlock Handling

In the last post, I talked about what deadlocks are (under the covers) and how SQL Server “resolves” them.

2019-05-16

I carry out a lot of basic health checks on SQL Server databases. One issue that I come across fairly regularly is that the databases contain indexes that have been disabled.

Most of the time, when I investigate why that’s happened, the customer has used some sort of ETL process and has disabled the indexes to improve loading speed. But then something went wrong, and the indexes didn’t get rebuilt (i.e. re-enabled).

2019-05-15

I’ve lost count of the number of computer systems that I’ve worked with over the years, that insist that everyone has at least two names. Yet there are many people in the community who only have a single name. I’m not just talking about people like Cher who might well have a stage name but then have another name on her passport. I’m talking about people who really only have one name.

2019-05-14

When I need to display a potentially large number of rows on a screen, I often need to show them in pages. This is called paginating the rows.

For example, if I’m showing bank transactions, I might want to show 25 per page. The challenge is that I also need to be able to ask for a specific page number. So my query becomes: give me page 7 where there are 25 rows per page. In that case, I want to skip the first 150 rows, then ask for the next 25 rows.

2019-05-13