Force prints to display

I you wan't to force print statements to display continuously when executing script in management studio, insert the following statement. SQL server do not treat severity 1-10 as an error, just a message.

RAISERROR ('Push the message!', 0, 1) WITH NOWAIT

T-SQl Query performance

Just some short notes on what to be aware of when experiencing bad performance.

• Table Scans for sure, but also be aware of key-lookups. Those normally can be avoided be including the lookup column in the index. This is in my experience one of the more common opportunities to improve performance and not that obvious to spot.
•  Table compression actually increases Query performance, but slows inserts down a bit. Generally if you have big tables, use table compression
• If using a view as a source in a merge statement, you can save a lot of time by doing it in two steps, first store the result of the view in a temporary table and then do the merge with the temporary table as source.
• A left join can in some cases be faster than an inner join. Strange but true.

T-sql toggle bit data type

Even after ten years in sql-server world, you can still find something you didn't know..

I have a field in a table called IsTrue of the type bit. I just want to toggle it, if it's true, set to false and if it's false, set to true.

I was on my way to do a case statement when I realized there is at least to better ways of doing this.

the bitwise NOT operator way
update table set IsTrue = ~IsTrue

the bitwise  XOR operator way:
update table set IsTrue = IsTrue^1

found at:
http://johnnycoder.com/blog/2006/10/04/t-sql-tip-flip-bit/

Can't believe I didn't know you could do that in SQL Server... =)

RandomForest.se

Starting up new company: http://RandomForest.se

Combining consulting and innovation within the area of Business Intelligence.

Great fun!

sp_xml_preparedocument open handles

To see all open handles, regardless of session,  when using sp_xml_preparedocument use this command:

select * from sys.dm_exec_xml_handles(0)

http://msdn.microsoft.com/en-us/library/ms408299(v=sql.105).aspx

Slow Oracle inserts from SSIS

Struggled for a while, with terrible performance when inserting a couple of millions of rows. Took about 4,5 hours using ole-db or ado.net with different drivers from both Oracle and others.

Tried the Attunity driver, and was stunned. 3,5 minutes instead of 4,5 hours.

Maps with Ssrs

http://blogs.msdn.com/b/querysimon/archive/2012/06/17/bi-data-on-maps-in-sql-server-2012.aspx

Good post on maps with Reporting Services by Simon Lidberg, Microsoft.

One strong advantage using this technique, is that you can simplify the maps  by removing parts of the world that are of no interest. Picture below is a reporting services 2008 r2 report.