Category Archives: Beginning SQL

SQL Server has no CTRL+Z

Using explicit transactions

OOPS! I didn’t mean to do that!

So, you are doing some harmless updates and all of a sudden, you realize that you just updated the whole entire table. NOT GOOD!

Hopefully this happens in a DEV environment, and the damage is at best an inconvenience.  Here are a few tips to help you make data changes in a NON production environment without the accidental mishaps. Use an explicit transaction.

Example:

You need to update some rows to create data that fits a scenario that you want to test.

In adventureworks, I select from the person table where Suffix = ‘Jr.’ I get 39 rows

I want to update that suffix to ‘II’ after making a change to my webform to use ‘II’ instead of ‘Jr.’ Innocent enough. this should take no time at all.

I begin my script with a “Begin Tran”

Oh NO! No Where Clause!
Oh NO! No Where Clause!

I run my update, but it is taking WAYYYYYYYYY too long. OH NO! I didn’t have a where Clause, and now every row in the person table has the Suffix of ‘II’ !!!! AGONY!

No problem, I have an open transaction. I can simply rollback, and add a DADGUM WHERE CLAUSE!

A where clause was added.
A where clause was added.

There now, that’s better. 39 rows updated. But wait, there’s more. You have an OPEN TRANSACTION!!! That can do all kinds of damage. Let’s commit. And as a bonus, we can use a built in function to check the number of open transactions for our spid. Select @@TRANCOUNT The answer should be 0 before we go on about our day.

CODE:

Begin Tran

Update [Person].[Person]
Set [Suffix] ='II'

--add where clause
Where Suffix = 'Jr.'

--Rollback tran
--Commit tran

-- BONUS!
Select @@TRANCOUNT

I hope this helps!
Yell McGuyer

T-Sql Bad Habits- Select *

Oh, Select *… You’re a hard habit to break.

OK, let’s talk about a few bad habits that we have when we are first using SQL on any Relational Database. I will give you some tips below that will have everyone fooled into thinking you really know what you are doing!

SELECT *


This is a tough one. We have all used it. But, do you know the harm it can cause?

What it does:

It simply returns all the rows and all the columns from a table. This will scan the entire table to return the data, and very likely cause lock escalation which can block other users from reading or writing to the table. There are other issues like schema locks, but that is another lesson. Just know that it is NOT GOOD.

What are you trying to do?

Are you trying to see a sample of the data in the table?

Use a TOP Clause : Select TOP (100) * from tableA

Are you trying to see the columns that are in the table?

USE built in functions: exec sp_help to to see the columns, datatypes, indexes, and much more.  –EXEC sp_help ‘tablename’

With(NoLock)

This hint will nicely ask SQL Server to forgo locks and bring you dirty data. You can use this hint, or  set the transaction isolation level to read uncommitted.

 Select TOP (100) * from tableA with(NoLock)

This is just the beginning, and I hope this helps. I will continue to post some tips for beginners all the way to architects.
-Yell McGuyer