Using Rank() to Remove Duplicates

In a recent post on SQL Server Window Functions I looked at "scoring" rows of data based on the value of specified columns.

The same approach can be used to help cleanse data prior to, or as part of ETL processes.  Data issues should be corrected at source, but in the real world this isn't always possible.  The source may be external, there may be issues of data ownership, or there simply may not be time to carry out the process.

It often falls to the SQL Developer to handle these issues. Here I will look at handling the issue of two records in a table for the same entity but with slightly different spelling (I refer to this as  "duplicate" records, though the records are not exact copies).

I've encountered this problem several times at different organisations.  If the two records were identical, it would simply be a case of using a SELECT DISTINCT. You might also consider imposing a unique constraint to ensure there are no actual duplicates.  But the spelling difference means that they are regarded as two completely different entities by the code.

When presenting this to business users, the response has often been "Just take the first one".  But how do you actually just take the first one? And first one based on what exactly?

First let's create some data:   

CREATE TABLE Stones

(

ID          INT IDENTITY(1,1),

FirstName   VARCHAR(10),

LastName    VARCHAR(20)

)

INSERT INTO Stones(FirstName, LastName) 

VALUES ('Mick','Jagger')

INSERT INTO Stones (FirstName, LastName) 

VALUES ('Michael','Jagger')

INSERT INTO Stones(FirstName, LastName) 

VALUES ('Kieth','Richards')

INSERT INTO Stones(FirstName, LastName) 

VALUES ('Ronnie','Wood')

INSERT INTO Stones(FirstName, LastName) 

VALUES ('Charlie','Watts')

SELECT * FROM Stones

ID          FirstName  LastName

----------- ---------- --------------------

1           Mick       Jagger

2           Michael    Jagger

3           Kieth      Richards

4           Ronnie     Wood

5           Charlie    Watts

(5 row(s) affected)

Note IDs 1 and 2 for Mick and Michael Jagger.  These are actually the same person, and we only want to pick up the first one.  We only want 4 rows in our result.

We can use the RANK() function to now score these records, partitioning by LastName (see here for more on how to do this):

  SELECT ID, 

       FirstName, 

       LastName,

       RANK() OVER (PARTITION BY LastName ORDER BY ID ASC) AS Score

  FROM Stones ORDER BY 1

ID          FirstName  LastName             Score

----------- ---------- -------------------- --------------------

1           Mick       Jagger               1

2           Michael    Jagger               2

3           Kieth      Richards             1

4           Ronnie     Wood                 1

5           Charlie    Watts                1

(5 row(s) affected)

I have ordered by ID, on the assumption that "Take the first one" means the first one to enter the table (the seeding of the table I have therefore used as the indicator of "first". If there are timestamps, they can be used instead.) 

Examining the results of the query above shows that we now only require records with a score of 1.  It is then quite a simple process to use the above code in a sub-query with a WHERE clause to restrict to the rows we want:

SELECT * FROM

(

  SELECT ID, 

       FirstName, 

       LastName,

       RANK() OVER (PARTITION BY LastName ORDER BY ID ASC) AS Score

  FROM Stones

)a

WHERE Score = 1

ID          FirstName  LastName             Score

----------- ---------- -------------------- --------------------

1           Mick       Jagger               1

3           Kieth      Richards             1

5           Charlie    Watts                1

4           Ronnie     Wood                 1

(4 row(s) affected)

We are now left only with the four rows we want. All duplicates have been excluded.

The database principal owns a schema in the database, and cannot be dropped (Error: 15138)

On a recent audit of a database I noticed that there were a lot of logins for a database that were not required.  I set about removing the logins one by one, but soon hit the following error:

Error: 15138 The database principal owns a schema in the database, and cannot be dropped.

Well this threw me a bit, and I could not figure out what was going on and why, this one particular login could not be removed.  As the message indicates, this account owns a schema on the DB. So I opened the user properties from SSMS and sure enough, the user  in question owned three schemas: db_datareader, db_datawriter, db_owner.  However, SSMS will not simply allow you to unselect the checkboxes here, so it is still not so easy to remove this link and allow the user to be deleted!

The schemas owned by the user can also be displayed by running the following query, within the context of the database under scrutiny:

SELECT s.name FROM sys.schemas s
WHERE s.principal_id = USER_ID('UserInQuestion'');

For a more complete picture of all schemas and users associated with the database:

SELECT *, USER_NAME(principal_id) as username
FROM sys.schemas;


In order to remove this user we have to first transfer ownership of these schemas to a different user.  Typically this would be dbo.  This has to be done once for each schema owned, using the ALTER AUTHORISATION ON SCHEMA command:

 ALTER AUTHORISATION ON SCHEMA::db_datareader TO dbo
 ALTER AUTHORISATION ON SCHEMA::db_datawriter TO dbo
 ALTER AUTHORISATION ON SCHEMA::db_owner TO dbo

(note the double colon!)

Re-run the previous scripts to ensure that the user in question no longer owns any schemas.  Now if we again try the delete again, this user will be removed successfully.

Random Data Using SQL Default Values

Wow, I decided to take a bit of time off over the winter and the next thing I know it's been two months since the last  SQL Banana update!

While faffing around over the last few weeks I came across a few code snippets I found quite useful.  Here's the first :)

Often I need to fill a table with some random dummy data.  Lot's of people have different ways of doing this, and I don't want to get into a discussion of what "random" actually means when working with computers, but 99% of the time this approach works just fine for me.

First create your table.  Set a default constraint to use the NEWID() function - this will generate a uniqueidentifier value.  The value will be unique every time it is generated.  Then use the CHECKSUM() function around it to compute it's hash value,  for our purposes effectively generating an int based on the uniqueidentifier value.

CREATE TABLE dbo.Dummy
(
Id INT IDENTITY (1,1) PRIMARY KEY,
IntData INT CONSTRAINT DummyDefault DEFAULT CHECKSUM(NEWID()))

Next, insert as much row data as required:

INSERT INTO dbo.Dummy DEFAULT VALUES
GO 500

This will run the insert 500 times, each time the identity column will increment, and a random integer will be inserted into the IntData field.

If you require varchar data then simply adjust the default constraint as required. EG:

CharData VARCHAR(MAX) CONSTRAINT TestCharDefault DEFAULT CAST(NEWID() as VARCHAR(MAX)) 

And that's it. Dummy data is now ready for use.

SQL Server Global Variables: @@Rowcount, @@Error and more

There are lots of SQL Server global variables (prefixed with @@), but I thought I'd list a few that I have found particularly useful in the past.  I often find I need to make use of them to gather data when I first go on client sites:

@@ROWCOUNT

Stores the number of rows affected by the last command.  I find myself using this variable all the time for logging.  For example, if I have a stored procedure inserting/updating/deleting data from a table, I use this variable to store the results of each of those commands in a log table.  This makes debugging much easier and any spikes or drops in records can be captured and easily viewed.  A word of warning though - this variable only stores the rowcount for the last command.  If you find that you are not storing the correct number, it is likely you have another line of code executing before you are reading this variable.

@@ERROR

Stores the error code for the immediately previous command.  I use this most often in conjunction with the @@ROWCOUNT variable, and store them together for logging/debugging purposes.  Note that if you want to log both of them for the same line of code, you need to make sure to write them both to the output in a single command. If you don't do this, then whichever variable's value is written second, will contain the result from the writing of the first variable, and not the actual insert/update/delete that is of concern.

@@SPID

Stores the session ID.  Again, my advice would be to log it with the previous two values.  If you have a number of sessions performing a range of activities, the log table will be confusing without being able to identify which sessions are doing what.

@@TRANCOUNT

Stores the number of currently open transactions.  I often perform a check using this variable in my CATCH block to determine if we have hit an error inside a transaction and left it open : IF (@@TRANCOUNT > 0) ROLLBACK

@@CPU_BUSY, @@IDLE, @@IO_BUSY

These ones are useful for gathering stats on the SQL box.  They store the number of "ticks" that the CPU has been busy doing SQL Server activities, the amount of ticks SQL Server has been idle and the number of ticks SQL Server has spent doing IO.  All are measured since the last time SQL Server was started.

@@TIMETICKS

The number of microseconds per tick - this helps to transform the previous stats into understandable time :)

There are many other global variables available in SQL Server, but this is simply meant to be a list of the ones I find myself using most often.

SQL Server Window Functions: RANK, DENSE_RANK, NTILE, ROW_NUMBER

Recently I have been finding it more and more useful to work with Window functions. These are sometimes referred to Analytic functions or more commonly Ranking functions:

RANK
DENSE_RANK
NTILE
ROW_NUMBER

These functions essentially "score" each row that is returned by the select statement.  So for example if we look at the Sales.SalesTerritory table in AdventureWorks and run the following query:

SELECT

t.Name,

t.CountryRegionCode,

t.[Group],

t.SalesYTD,

RANK() OVER (ORDER BY SalesYTD ) AS Ranked

FROM sales.SalesTerritory t

This results in the final column contain the "rank" of the record (ascending order), ie if it is the second lowest SalesYTD this column will contain a two. 

These functions become much more powerful, however, when using the optional PARTITION BY clause that can be stipulated.  Using this clause we can "subrank" within partitions: 

SELECT t.Name,

t.CountryRegionCode,

t.[Group],

t.SalesYTD,

RANK() OVER (PARTITION BY [Group] ORDER BY SalesYTD) AS SubRank,

RANK() OVER (ORDER BY SalesYTD) AS OverallRank

FROM sales.SalesTerritory t

The results of this query display each record, ranked within its Group (Europe, North America, Pacific):

Name CountryRegionCode Group    SalesYTD      SubRank OverallRank

-------------------------------------------------- ----------------- ---Northeast      US North America 2402176.8476  1       1

Southeast      US North America 2538667.2515  2       2

Central        US North America 3072175.118   3       3

Germany        DE Europe        3805202.3478  1       4

France         FR Europe        4772398.3078  2       5

United Kingdom GB Europe        5012905.3656  3       6

Australia      AU Pacific       5977814.9154  1       7

Canada         CA North America 6771829.1376  4       8

Northwest      US North America 7887186.7882  5       9

Southwest      US North America 10510853.8739 6       10

(10 row(s) affected)

This makes it very easy for us to produce reports that return, say, only the top performers in each region.

These functions avoid the need to write subqueries or CTEs to produce their results and are therefore highly efficient.  More details on these four functions can be found on technet here.

In the next post we'll look at additional lesser known window functions within SQL Server.

Truncate vs Delete - Myth vs Reality

I have been asked many times (often in interviews) the following question:

Q. What is the difference between Truncate and Delete in SQL Server?

The answer that, in my experience, is most expected and most often given is that Delete allows you roll back the data you have wiped, while Truncate does not - Truncate is irreversible.  A quick search on google gives a number of posts that say as much.

Simple.  But wrong!  Just try this simple test.

1. Insert records into a table. 
2. Open a transaction. 
3. Truncate the table. 
4. View the contents of the table.
5. Rollback the transaction.
6. View the contents of the table.

 The code below is a very simple example:

CREATE TABLE MyTable (col1 CHAR(1), col2 INT)

GO

INSERT INTO MyTable (col1, col2)

SELECT 'a', 1 UNION ALL SELECT 'b', 2 UNION ALL SELECT 'c', 3

GO

SELECT * FROM MyTable --(3 row(s) affected)

BEGIN TRAN

TRUNCATE TABLE MyTable

GO

SELECT * FROM MyTable --(0 row(s) affected)

ROLLBACK

SELECT * FROM MyTable --(3 row(s) affected)

Before the transaction is rolled back, there are no rows in the table, but after the rollback all three records have returned, despite having used Truncate to remove them.  Clearly Truncate is reversible!

So what actually is the difference between Truncate and Delete?  There are a number of general differences, but the relevant one here is the following:

When rows are DELETE'd the operation is logged for each row removed. TRUNCATE logs only the deallocation of the data pages rather than the rows themselves, making it a much faster operation.  

There are many other differences related to permission levels, seeding, triggers, contraints etc. That's for a future post.  For now, the point to note is that TRUNCATE can indeed be rolled back, and the myth has been busted! 

SSAS: MDX True Average VS AverageOfChildren

Recently I was tasked with working on some slow performing calculations in a cube.  In the Calculation script a developer had used the DECENDANTS function, with the LEAVES flag to navigate all the way down to the bottom of several dimensions' hierarchies. He had then used AVG across the returned set for a specified measure, effectively aggregating all the way back up again.

All the developer actually wanted to do was to have a measure that returned the average value for the current member selection.  By going down to the leaf level and then backup, however, the cube is having to scan through a potentially huge volume of data to produce the answer. Inevitably this will result in degraded query performance for users.

In fact this can be a lot simpler to do than people realise.  True, SSAS 2008R2 still does not supply an inbuilt function to do this, which surprises many new users. One day maybe it will,  but in the meantime here is how I do it.

First, it should be pointed out that there is a certain "type" of averaging that SSAS does support out of the box.  If you look at the options in the AggregationFunction property for a measure you will see that, although by default set to Sum, there is also one option called AverageOfChildren. This name is very misleading.  It is not a true average as I would understand.  It is actually an average only across the Time dimension.  If you have two dates selected and are looking at the Sales Value measure you will see it averaged across those two days.  However, if you are looking at a single day, but across two sales areas, you will not see it averaged across those areas, but summed.  Confusing, but that is just what it does. Note that to enable this correctly your time dimension needs to have its Type property set to Time. 

If, however, you want to do a true average, you will need to create a calculated member in the calculations tab of your cube.  The calculation is very simple, for what is an average? It is the sum of the values, divided by the number of values.  The sum we have as the base measure from the fact table, say Sales Value. When we create a new measure group in the cube it automatically generates a new measure suffixed "Count".  This measure is none other than the count of values (or fact table rows) in the measure group.

Therefore the average calculation is the one measure divided by the other:

CREATE MEMBER CURRENTCUBE.[Measures].AvgSales
AS Measures.Sales / [Measures].[SalesFactCount]

Since both measures are essentially straight from the fact table, their values are stored during processing time, and benefit from aggregation design too.  As a result the AvgSales measure ought to perform very fast.