Microsoft and R?

I've read here that Microsoft had acquired evolution Analytics, the company who provide open source distributions of R, alongside commercial “Enterprise” extensions for big data infrastructures.
Since I've decided to broaden my skills set lately by learning R, I believe these are exciting news for the following reasons. The writer of the post, Tony Davis, mentions that it must be to replace/extend SSRS; learning it for a short while, I believe that on top of SSRS, you can use R functionality for acquiring data, modeling, data mining and reporting. Essentially: everything BI, or SSIS, SSAS and SSRS.. Since R reads all the data into RAM, it can also replace/extend the in memory OLAP which exists nowadays in SQL Server 2012 (or the Tabular Model).
Another thing which is important to point out for the SQL developer who's keen to learn R, is the "thinking" of the language. As an SQL developer (especially MS, not necessarily Oracle), we're used to think in "Sets". We don't go row-by-row when we write code, but instead it's table-by-table. It was a type of thinking which is hard to get used to (it was difficult to learn normalization). And then when you need to learn BI, you had to "unlearn" the normalization part, but you're still going by rows. Writing code for regular programming languages (like C, Java, whatever) requires you to go row-by-row, one data (structure) after the other. 
In R you have to go row-by-row, while you could still query after a set of data. It requires a completely different type of thinking; if you tell me "data set", I immediately think "SELECT FROM WHERE". It doesn't work this way in R (though you could implement this type of syntax in R). So not only you can have many uses of R, but if might even use R instead of writing a cursor. 

So essentially I believe that Microsoft had purchased R in order to extend SQL in many good ways. Either way it makes sense to take the time and learn it!

My first cursor - faster than SET based operation

Of course, you should NEVER write cursor. Especially not for SQL server. Or so I was told.

The rational behind the code

I believe people hate them so much because usually you might find them in some legacy code, where there could be some other proper SET based operations, or at least they might exist nowadays. Nobody likes legacy code. Nothing too delightful about jumping into code that you don't know and you didn't write. Another reason is of course, for the usual T-SQL code that you write some smart people wrote optimizer for. If you run a Cursor the optimizer cannot kick in, so it's likely that the code would run slower.

But sometimes you do need to write cursors. The main use is when you need to go row by row. Then why not use client side programs, like C# or VB? Well, maybe it's because the developer feels more competent using SQL than by using C#? That's been my excuse for writing the code below.
So I had to go row by row for a one off. My SQL skills exceed my C# skills. I needed to run this code once and that's all. So I choose to write a cursor.

What does it do?

There had been some duplicated rows of data in the "attachment" table. The data is a file, saved in the "content" column as varbinary(VARMAX), and I just wanted to make sure that the duplicated file name didn't stem from a different files, but rather, represented the same file on multiple instances. I've made sure that all attachments were saved to a new table called "multiple_attachment" when "min_attachid" is the ref (there's obviously another field called "file name" which I didn't bother with this time).
Since it's hard to trace errors in Cursor I've added the "INSERT" statement.
Using SET based operation would not be quicker (tested!) due to the INNER JOIN of a table on itself, on top of the expensive operation of comparing varbinary(max).

The code

DECLARE first_cursor CURSOR
FOR
SELECT content, attachid, min_attachmentid
FROM dbo.multiple_attachment
WHERE attachid <> min_attachmentid

DECLARE @content VARBINARY (max)
DECLARE @attachid BIGINT
DECLARE @min_attachid BIGINT

OPEN first_cursor

FETCH NEXT FROM first_cursor
INTO @content, @attachid, @min_attachid

WHILE @@FETCH_STATUS = 0
BEGIN
IF @content <> (SELECT content
FROM dbo.attachment
WHERE attachid = @min_attachid)
INSERT  [dbo].[trace_errors]
           ([attachid])
     SELECT (@attachid)

FETCH NEXT FROM first_cursor
END

CLOSE first_cursor
DEALLOCATE first_cursor

A problem:

The code above got stuck with the error "An error occurred while executing batch. Error message is: Error creating window handle."
because there were over 2000 lines in the table and there was a limit to how much "Display Result" window panes you can create. For every line it produced a new message! the solution was to disable the results pane in the Query Option (Query -> Query Options -> Results
choose "Discard results after execution" in SQL Server 2012). The above proc run much quicker and I would recommend setting this option each time you need to write a Cursor.

Investigating undocumented database - part 2

I've written in the past about how to investigate an undocumented database, and here are a few more insights to my previous post.
First of all, if you need to list all the tables with data you can use the following script:


SELECT
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnuasedSpaceKB
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
AND p.rows > 0 --As we want only tables with data
GROUP BY
    t.Name, p.Rows
ORDER BY
    t.Name;

Warning: sometimes if a table is very large due to partitioning it could appear twice on the list! so you'd see both tables with the same name and only one schema... that's confusing, but it could happen.

When you look around the different tables, sometimes they are not all perfectly normalized! It's very frustrating, and in that case have a look in the triggers:

To List all triggers:

SELECT
     sysobjects.name AS trigger_name
    ,USER_NAME(sysobjects.uid) AS trigger_owner
    ,s.name AS table_schema
    ,OBJECT_NAME(parent_obj) AS table_name
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects

INNER JOIN sysusers
    ON sysobjects.uid = sysusers.uid

INNER JOIN sys.tables t
    ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id

WHERE sysobjects.type = 'TR'


Sometimes you need to have a printout of an "on delete trigger" to find out all the tables that are related to a certain tables, when foreign key isn't always defined.

Ah, and if there's a certain and not quite common data type that you need to find out about. I'm not talking about INT here, maybe it's XML or Money or in the following case, Varbinary:


select IC.TABLE_NAME, IC.COLUMN_NAME
 from INFORMATION_SCHEMA.COLUMNS IC where IC.DATA_TYPE = 'VARBINARY'


Ah, and BTW: SQL Server 2012 is FUN to work with, you're welcome to try out!