SQL Tips – Update From, Delete Into, and more

Over the last few years I have spent a lot of time working with Microsoft SQL Server. I frequently get asked SQL questions by my co-workers so I thought I would compile a list of some of those most commonly asked SQL questions.

Checking Index Fragmentation

As an index becomes more fragmented, performance of the index will degrade. You can check the fragmentation of an index with the following query:

declare @table sysname;
set @table = 'dbo.MYTABLE';
declare @indexid int;
select      @indexid = index_id
from        sys.indexes
where       object_id = object_id(@table) and [name] = 'IX_MYTABLE_MYINDEX';

select      *
from        sys.dm_db_index_physical_stats(db_id(), object_id(@table), @indexid, NULL, NULL);

Update From (With Joins)

When migrating data it is common to update a value in one table, filtering by a value stored in another table. Usually the simple answer is to do the following:

UPDATE dbo.Friends
SET status='Declined'
WHERE LastName IN
(
Select LastName
From dbo.Enemies
Where status='Active'
)

However, sometimes you may need to filter an update based on the values in two columns such as LastName and FirstName. This is where the SQL statement “UPDATE FROM” becomes very useful:

UPDATE fr
SET status='Declined'
FROM dbo.Friends fr
JOIN dbo.Enemies en
ON fr.LastName = en.LastName
AND fr.FirstName = en.FirstName
WHERE en.status='Active'

Delete Into (Output)

Let’s say that I want to delete old friends, but at the same time I also need to capture the list of friends deleted so that I can cleanup data in other tables. Now this can be accomplished with two statements, a SELECT followed by a DELETE, however it is much cleaner to just do this in one statement with a DELETE INTO statement. Here is a simple example:

Declare a table to store the deleted friendIDs:

declare @deletedFriendIDs table (FriendID int);

Delete old Friends, and output the list into the table variable:

delete
from	dbo.Friends
output	deleted.Friends INTO @deletedFriendIDs
where	LastAccessDate < (getdate() - 90)

Now let’s display the list of the deleted FriendIDs to make sure that it worked

Select * from @deletedFriendIDs

Case Sensitive Query

SQL is case insensitive. However, sometimes you may need to do a query where case is important. The following example will return all Friends with the first name “chris” but exclude “Chris”.

select * from dbo.Friends
where FirstName = 'chris'
COLLATE SQL_Latin1_General_CP1_CS_AS

How to Capitialize the first letter and make all other letters lower case

It is common that you might have some data, such as a list of names where the first letter is capitalized in some names and not in others, so you will want to clean up this data. Here is a simple query to capitalize the first letter in each name and make the rest of the name lower case:

update dbo.Friends
Set firstname = upper(left(firstname, 1)) + lower(right(firstname, len(firstname) - 1)),
lastname = upper(left(lastname, 1)) + lower(right(lastname, len(lastname) - 1))

Removing spaces from a varchar

So it is possible that some data slipped into your database without spaces being trimmed off. So if this happens and after the code bug is fixed you need to cleanup existing data, here is a simple query to do it:

UPDATE dbo.Friends
set LastName = replace (LastName,' ','')
where LastName IS NOT NULL

How to get the total row count of a very large table without using a “Select count(*)…” statement

For some production tables that are very large doing the typical “Select count(*) from dbo.Friends” statement can be very expensive and slow. SQL Server has a property on each table which stores the current row count for the table. So rather than using an expensive select you can just access that property:

select      ISNULL(sum(spart.rows), 0)
from        sys.partitions spart
where       spart.object_id = object_id('dbo.Friends') and spart.index_id < 2