Be careful what you delete for II - Judgment Day

A while back I posted this regarding deleting data from Sitecore's Analytics database.

TL;DR - Sitecore 6.4 (and possibly other versions using SQL-based analytics) do not want you to truncate the IPOwner table. Analytics stops working.

Lo and behold, we discover today that one of our Sitecore applications hasn't been collecting analytics for a while. Investigation of the database revealed - gasp - no data in the IPOwner table !!!

The Sitecore logs revealed a multitude of Foreign Key constraint violations; specifically the foreign key IP.IPOwnerID - IPOwner.IPOwnerID. It's trying to add a row to the IP table using an IPOwnerID that does not exist.

Wait, what ? There aren't any rows in IPOwner ?

Suspecting magic GUID foul play and thinking that Sitecore is perhaps expecting a row to be there, we crack open a fresh copy of Sitecore 6.4's OMS database and sure enough, hiding in there was this little fella: -

IpOwnerID	Name	Country	VisitorIdentification	ExternalUser
9F28FB1A-BDDA-4CE6-9521-D7E6C6D8BB9D	(Pending)	(Pending)	0

And it all makes sense. While waiting for MaxMind GeoIP service to return something, Sitecore is using this guy as the IPOwner for a new session until the queued service lookup completes (hopefully), so it can write some meaningful location data to the IP record.

Added this row.. tested the site.. analytics starts recording stuff !

So now we know. Delete all the IPOwner rows if you want, just make sure this row finds its way back in.

SQL Row_Number() OVER and how to cure indeterminate sort order

There's been plenty of wibbling on Stack Overflow about how paging and sorting at SQL Server level using the Row_Number() function can produce records ordered unpredictably between repeated executions of the same query; perhaps you wouldn't normally do this, perhaps you would and as required by my scenario, I did.

And saw it myself - the variations weren't great but they were there and that's annoying.

I checked and double-checked my stored procedure - it was doing what it was supposed to.

Came across this via Stack Overflow - http://msdn.microsoft.com/en-us/library/ms186734.aspx - and I'll repeat the most pertinent part here: -

There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true: -

• Values of the partitioned column are unique.
• Values of the ORDER BY columns are unique.
• Combinations of values of the partition column and ORDER BY columns are unique.

How to fix this ?

So - Microsoft themselves admit this is a problem. There is of course a solution, and mine was a table variable.

DECLARE @IDTable TABLE
(
  InvoiceID INT,
  RowNumber BIGINT,
  TotalRowCount BIGINT
)

Because the actual results my query returns contained a number of identical field values in many cases (same Customer Name, same Status.. etc) the three rules outlined in the MSDN article weren't being met; hence the problem. You can guarantee the order by stripping the columns returned from the actual query back to a bare minimum - a key field (InvoiceID in my example), the RowNumber itself and (optionally) the result of the partition column expression.

Having defined our table, I then run the complete query but returning only those 3 fields needed: -

INSERT INTO @IDTable (InvoiceID, RowNumber, TotalRowCount)
 SELECT * FROM
 (
  SELECT DISTINCT i.InvoiceID,
  Row_Number() OVER
  (
   ORDER BY
   CASE
    WHEN @SortExpression = 'DateCreated'  
     THEN  i.DateCreated
   END ASC,

    -- snip - long list of fields to order by

  ) AS RowNum,
  COUNT(*) OVER(PARTITION BY 1) as TotalRowCount 

  FROM ..
   
  WHERE ..

 ) results

 WHERE RowNum 
                BETWEEN (@PageIndex * @PageSize + 1) 
                AND     ((@PageIndex * @PageSize) + @PageSize)
 ORDER BY RowNum

That deals with correct ordering of results; now run another query to join back to @IDTable, this time also retrieving all the coulmns needed: -

 SELECT i.InvoiceID, c.CustomerName, .. etc
 FROM @IDTable idt
  INNER JOIN Invoice i WITH (NOLOCK) ON idt.InvoiceID = i.InvoiceID
  INNER JOIN Customer c ..

    -- and so on 

Ok so there's some overhead involved with running two queries as opposed to one; but the execution time is still fast and it gives me exactly what I want. You should have seen what was happening before..

SQL 2008 - Stupid options and sensible practices

http://www.danrigsby.com/blog/index.php/2008/09/26/sql-server-2008-error-saving-changes-is-not-permitted/
H/T to Dan Rigsby for saving me a metric crapload of time.

Ok I can see the sense, somehow - that option prevents you from saving table changes that require the table to be copied, dropped and re-created with the changes, for example, when changing column nullability.

But to have this enabled when you're in the thick of the database design and creation process is just bloody annoying. Took me a few expletives raged at the monitor too. Grrr.

After thinking about this a bit further, it's actually a really stupid option. Anyone making changes to production databases using the table designer should be put up against a wall and shot.

Production databases. These are things that must be updated with extreme caution.

Scripting is the only way to go. At a push, use SqlCompare if you're lazy, but I've seen that go wrong too.

Script any and all database changes as you make them in development. Round-table these with the others  if you're working in a team. Work out a strategy for updating, migrating data, and so on.

In other words, do it properly ffs. Database changes are the cause of at least 176% of all production problems and if you and the guys work out a process, you are thinking correctly. If you rely on stupid table designer features to save you from your own stupidity, then you're stupid.

Thanks for listening.