Sql-Server

I’ve lost count of the number of times I’ve seen people searching for details on what the latest SQL Server builds are. In a previous post, I mentioned that our free SDU Tools for developers and DBAs had a view called SQLServerProductVersions that showed all the SQL Server builds.

But that shows all the builds. What if you just want the latest details? We added a new view that works that out for you. It’s called LatestSQLServerBuilds. You can see it in use in the main image above.

2019-06-26

WHERE clauses can have more than one predicate.

In the case shown above, I’m saying

“where the description starts with hydro and not size equals 370ml”

The operators AND, OR, and NOT can be used to form a group of logical decisions that need to be made.

Now notice that in this case, it’s exactly the same as if I had said

“where the description starts with hydro and size is not equal to 370ml”

2019-06-24

There are many times when I need to use a queue in my database designs. Service Broker is a transacted queue that lives right in your SQL Server database. If you haven’t used it, or don’t even know why you should, read on.

The primary reason that I like to use queues is that they let you decouple different applications or different parts of applications. Let me give you some examples:

2019-06-20

In the last SDU Tools post, I mentioned new functions to determine if a day is a weekday or a weekend. (Based on Saturday / Sunday being weekends but easy for you to change if you have different weekends).  Another issue that I often have is the need to add a number of weekdays to a given date. To make it easy as well, we added a function AddWeekdays to our free SDU Tools for developers and DBAs.

2019-06-19

I know that SQL Server has a number of data types that look like they could be used for storing amounts of money. Developers go looking for data types that have a decimal point in them, and there are a few:

Float - this one seems to get picked by people from a Java background. And it’s pretty much never the right choice. It doesn’t store values exactly because it’s an approximation. Just like we have numbers in decimal that we can’t write exactly (like 1/3 as a decimal is 0.33333 and so on), there are numbers in binary that we can’t store exactly. And that includes numbers like 0.1 (yes, ten cents). So using this data type for money is usually a newbie mistake, and the same applies to the potentially even more approximate real.

2019-06-18

I’ve discussed standard operators in previous posts but an interesting additional one is the LIKE operator. This allows for basic pattern matching in T-SQL predicates.

Let’s look at a simple example:

WHERE ProductName LIKE ‘Grant%’

The % symbol is a wild-card that matches any set of characters, including no characters at all. So this expression would match the following and more:

Grant Grants Cola Grants Lemonade

If we’re looking for a word that’s contained inside a string, we can use it like this:

2019-06-17

A few months ago, I was asked a question by a friend of mine who is also a Data Platform MVP. He was trying to express a query like this:

SELECT si.StockItemID, si.StockItemName, si.Size, si.LeadTimeDays FROM Warehouse.StockItems AS si WHERE (si.Size, si.LeadTimeDays) IN (('M', 7), ('XXL', 7), ('XXL', 12));

He was trying to select stock items where the combinations of Size and LeadTimeDays are in the list. Unfortunately, in SQL Server and T-SQL, an IN can only be used with a single column. We can’t use a row constructor like (si.Size, si.LeadTimeDays) as a lookup into a list of constructed rows.

2019-06-13

I often need to know if a date that I’m working with is a week day or a weekend. While T-SQL has some functions that help with that, you need to use a combination of them to get a reliable outcome that’s independent of the current session settings. (Based on Saturday / Sunday being weekends but easy for you to change if you have different weekends). To make it easy, we added two functions IsWeekday and IsWeekend to our free SDU Tools for developers and DBAs.

2019-06-12

We use comparison operators in T-SQL expressions. And the expressions are often used in WHERE clauses. For example:

WHERE CreditLimit = 10000

The = (equals) sign shown is a simple one but T-SQL has a number of operators.

Most people writing T-SQL code are familiar with these:

= Equals <> Does not equal < Less than > Greater than <= Less than or equals >= Greater than or equals

However, less people seem to be aware of the other operators added a while back:

2019-06-10

I was working in a bank a few weeks back. They were planning an upgrade from SQL Server 2008 R2 to SQL Server 2017. One of the things that I raised with them is why there were extended stored procedures that had been added to the master database. And they told me they were the result of a penetration test.

OK, so this had me intrigued.

The logic that was being applied was that the penetration testers required the bank to have SQL CLR disabled. No-one in the bank could tell me why; just that it was required to be off.

2019-06-06