Search Case Sensitive

By default, a SQL Server Database is not Case Sensitive. And that also means that selecting data is not Case Sensitive. So the following Queries will give the same results:

select *
from Persons
where Name like '%John%'

select *
from Persons
where Name like '%john%'

select *
from Persons
where Name like '%JOHN%'

Now in most cases, this is a blessing rather than a curse. But every once in a while there is a need for a Case Sensitive search. So how can you do this?

Now when a Database was created with a Case Sensitive Collation, this would be the default behavior. And since we can specify a Collation in a Query, that's the way to go about to do this:

select *
from Persons
where Name like '%john%' collate SQL_Latin1_General_CP1_CS_AS

Changing the Collation in a Query can be necessary with Cross Database joins where the Databases can have different Collations. Usually then specifying the database_default Collation is the way to go. So you might already be familiar with this one.

This is kind of the same trick.

The IsNumeric() Function

In an (Exact Globe or Synergy) Database you might find Columns that appear to be numbers, while in fact they are not. For example the Project Number. A Project Number can be Numeric, like '123456'. But 'PRJ-123456' can also be a valid value and that is certainly not a number.

So, let's say you want to find all the Projects where the Project Number really is a number. For that you can use the IsNumeric() Function for that:

select *
from PrPoject
where IsNumeric(ProjectNr) = 1

The IsNumeric() Function returns 1 if the Value is an actual number, and 0 if it is not. If there are leading or trailing spaces or zero's, it still is seen as a Number by the SQL Server:

select IsNumeric('0010')
select IsNumeric(' 10')
select IsNumeric('10 ')

Just a single space or an empty string are not recognized as a number. Weirdly enough, a Tab is also recognized as a number as is a dash (-). Currency Symbols are also recognized as a number.

-- Not a Number
select IsNumeric('')
select IsNumeric(' ')
select IsNumeric('_')
-- Numbers
select IsNumeric('-')
select IsNumeric(' ')
select IsNumeric('$')

In general use cases this never gave me any problem. But still, be aware of this behavior.

Distinct & Top

Using the Top Function and the Distinct Function can sometimes lead to confusion. Let's try that out with this one to see what the behavior is:

with x as (
select 1 as Number, 'A' as String union
select 1, 'B' union
select 2, 'A' union
select 2, 'B' union
select 3, 'A' union
select 3, 'B' union
select 4, 'A' union
select 4, 'B' union
select 5, 'A' union
select 5, 'B' union
select 6, 'A' union
select 6, 'B' union
select 7, 'A' union
select 7, 'B'
)
select top 10 Number
from x
order by Number

As you can see each number from 1 to 5 appears twice in the results as expected.

Now let's change the Top 10 to a distinct:

As expected, this will give you the numbers 1 to 7.

So what will happen if we combine these?

If the Top 10 Function is applied first, we would get the numbers 1 to 5 since those are the only values we get from the Top 10. On the other hand, if the Distinct Function is applied first, we should get the numbers 1 to 7. So what shall it be?

As you can try and see, the Distinct Function is applied first. As a matter of fact, the Top Function is always applied last when executing the Query.

It's a good thing to keep that in mind so you know what to expect.

Compound Operators

Ok, so you have Variable that is an integer and you want to add something to that Value. You could do that like this:

declare @Number int = 1
set @Number = @Number + 2
select @Number

Nothing wrong there. It works as expected. But typing it out is a lot if work if you have to do this often. And it might become hard to read.

Luckily, T-SQL also has some Compound Operators that combine an Operator (+, -, *, /) and the Equal sign (=). So you could do it also like this:

declare @Number int = 1
set @Number += 2
select @Number
set @Number -= 1
select @Number
set @Number *= 2
select @Number
set @Number /= 2
select @Number

Easier to read, less typing. What's not to like about these ones?

NoLock versus ReadUncommited

Sometimes locking in a Database can occur. Usually this is done to prevent users from editing the same data at the same moment. But while this is a good thing, it can have side effects. Let's create a Table put in some data and we will see what the side effects are.

create Table Products (
ProductID int identity,
Code nvarchar(10),
Description nvarchar(50),
Price float,
Modified datetime
)

insert into Products(Code, Description, Price, Modified)
Values
('PRD-A', 'Product A', 125, getdate()),
('PRD-B', 'Product B', 145, getdate()),
('PRD-C', 'Product C', 195, getdate())

select *
from Products

Now open up a new Window in Management Studio and run the following query. While it is running, go back to the previous Window and run just the select statement again.

begin tran
update Products
set Modified = getdate()
where ProductID = 1
waitfor delay '00:00:10'
rollback

In this Transaction I added a WaitFor Delay of 10 seconds. This will force the Table to be Locked for that 10 seconds. If you execute the "select * from Products" while that Query is running (while that Query is Locking the Table) you will notice that it will wait until that Query is done before showing the results.

In fact, SQL Server is doing this on purpose since somewhere in the results to be shown, something is going to change. So it holds back the Query until it can be run and show the results as they are after the Update Statement is done.

Locks could aso be set from an Application, also to prevent multiple Users editing the same Data.

I don't care, I want results

Regular Update Statements do not take that much time. This one does, because I added the WaitFor Delay in there on purpose so you have time to switch between Windows.

But a Lock from an Application might take a while. For instance, when a User opens up the Edit screen and leaves it open to go for lunch. In that case, you might not want to wait for the User to return and finish editing the Data.

Do keep in mind that the results you will get will be outdated as soon as the blocking Query is finished. In general, this will not be a problem. When the User hits the refresh button, the results will be up to data. So while it might not be perfect, it definitely beats waiting for the results.

There are 2 ways to do this:

select *
from Products with(nolock)

set transaction isolation level read uncommitted
select *
from Products

Both ways are in this case exactly the same. So why would you use one and not the other?

With(NoLock)

As you can see in the example, the with(nolock) hint is specified for the Products Table. If you would add Joins to other Tables or Views, you would need to specify the with(nolock) for those Tables as well. Same goes for SubQueries. That is, if you expect Locking issues on those Tables as well.

Read Uncommited

If you change the Transaction Isolation Level to Read Uncommited, this will work for every Table or View you add with a Join, or SubQueries. It is exactly the same as putting with(nolock) everywhere in the Query.

Using with(nolock) requires a lot more typing and your Query might become a bit harder to read. Then again, you can more finely tune which Table or View should be read while ignoring possible Locks than by setting the Transaction Isolation Level to Read Uncommited.

While both the NoLock hint and the Read Uncommitted are technically the same, I prefer the Read Uncommited. Since you are already ignoring the Locks on one Table, you might as well ignore Locks on other Tables as well.

That's it for Today!

So that was it for today. 5 small tips that will make you write better SQL Queries, or at least have a better understanding of what you are doing.

Got any other questions? Feel free to drop me a note and I will see if I can do another Post on that in the future!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!