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
