Suggested Reading

I came across some nice articles and blog posts in the last weeks I wanted to share:

• Ultimate Query Tuning by @Rob_Farley
• Incorrect Results Caused by Adding an Index by @SQL_Kiwi
  You can vote for the Connect item here.
• Interested in Power BI? @Technitrain Summed it all up!
• Dump The Results Of A DAX Query To CSV Using Powershell by @Kjonge
• @MarcoRus started a great discussion about advanced BI certifications like SSAS Maestro, MCA:BI, MCM:BI. Check it here.
• Last but not least: Excel 2013 Standalone will NOT add Power Pivot / Power View until September 10, 2013. You can check the SQL Server Forum for more information.

Hopefully you can get somehting useful out of here!

@NickyvV

Suggested Reading

I came across some nice articles and blog posts in the last weeks I wanted to share:

• SQL Server Problem Solving Methodology by @grrl_geek
• Counting Things That Didn't Happen by @PowerPivotPro
• New to SSRS? Build Your First SQL Server Reporting Services Report by @grrl_geek
• More advanced with SSRS? @sqlJason does a great job explaining Bullet Graphs in SSRS: Part 1 and Part 2. Part 3 will follow shortly to complete the series
• In case you haven't heard of Power BI by this time, check out this post by @Technitrain, it sums it all up pretty well
• For those who love to write blog posts, but can't seem to find the time should check out Speedblogging

Hopefully you can get somehting useful out of here!

You can also follow me on Twitter: @NickyvV

Suggested reading

I came across some nice articles and blog posts in the last weeks I wanted to share:

• How to communicate effectively in business by @SQLRockstar 
• PowerPivot vs Power View: Whats The Difference? by @PowerPivotPro
• The Index Your Queries will Never Request (A Clustered Index!) by @kendra_little
• The BI Survey 13 is now open, you can take it here

Hopefully you can get somehting useful out of the links!

You can also follow me on Twitter: @NickyvV

The slight difference between ISNULL and COALESCE

I ran into a little issue yesterday when I was solving an issue for a customer. An Slowly Changing Dimension was implemented for the Organization table and I was looking for the currently active records in that table. I'll explain the issue with the use of the well known AdventureWorks2012 database.

My goal is to select the currently active records in the [HumanResources].[EmployeeDepartmentHistory] table, so I take the next select statement:

This results in 0 rows returned. I'm stunned, because I saw a bunch of records which have a NULL for EndDate so I would expect at least those records to be returned.
I leave out the where clause and add a row so my output also shows the ISNULL(EndDate, GETDATE()) column as follows:









This results in the following output (abbreviated):





I now realize my GETDATE() is converted to a DATE instead of a DATETIME column. But why?
Well, you might say: it was designed that way... as BOL (ISNULL (Transact-SQL)) states:

Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

Because my EndDate column is of type DATE, the GETDATE() function (inside the ISNULL) will be converted to DATE also. So my where clause will actually look like this:





which is the 'same' as the following, technically spoken it's not the same, because the ISNULL will output a DATE data type, but for explaining my point I will use this :




This will only result in output when you execute the query at exactly 00:00:00.000 hours, which will *never* happen in real time.
Instead of ISNULL I will use COALESCE (Transact-SQL) which, according to BOL,

returns the data type of expression with the highest Data Type Precedence (Transact-SQL)  If all expressions are nonnullable, the result is typed as nonnullable.

This will result in the DATETIME data type for the last expression when the EndDate column is null and returns the correct result set for my goal.



There also is a slight difference when using non-null parameters:

ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

Hopefully you found this useful and will also never make the same mistake again... :-)

Suggested reading

I came across some nice articles and blog posts in the last weeks I wanted to share:

• Why won't my PowerPivot for Excel 2010 workbook upgrade to Excel 2013?
• The sessions and speakers for PASS Summit 2013 have been announced, check it out here!
• Firestorm: The story of the bushfire at Dunalley: A so-called "Interactive" by The Guardian
• PowerPivot: Relationship may be needed but I already have one?! by @PowerPivotPro
• SQLServerCentral Runs sp_Blitz - Query Plans
• A Sample SSRS Dashboard and some Tips & Tricks by @SQLJason

Hopefully you can get somehting useful out of the links!

You can also follow me on Twitter: @NickyvV

SQL03070: This statement is not recognized in this context

I just ran into the following problem:
when trying to build a database project in my VS2010 solution I got this error:

SQL03070: This statement is not recognized in this context.

After searching the first hit in Google already solved the problem:
The reason for this error is the following:

The project system expects the stored procedure as a create statement only. The additional statements to drop if existing are not necessary within the project system. Project deployment takes care of detecting if the sproc already exists and if it needs to be updated.

To resolve this error

• Simply remove the additional statements other then your create SP, Function etc.

OR

• Exclude the file from build. Right Click on your file in Solution Explorer, Click Properties > Build Action > Not in Build

Credits to: Prashant Acharya.

Suggested reading

I came across some nice articles and blog posts this week I wanted to share:

• The (unintended) network effect of Excel by @PowerPivotPro
• Junior DBA interview questions by @SQLRockstar
• Comments and descriptions in DAX by @TechniTrain
• How far can you push tabular? by @MarcoRus
• The emergence of Silicon Valley (documentary) by @AlexanderNL (+Alexander Klöpping)

Hopefully you can get somehting useful (or funny) out of the articles!

You can also follow me on Twitter: @NickyvV