This was my first time at SQL Saturday, and I had a great time at sessions and lightning talks (here’s the event page). I want to share what I learned.

Interject: Data rich features for Excel– tabular reports + client/server apps

Jeff Honsowetz led this session to introduce Interject’s platform. This PaaS/SaaS allows end users to use a familiar front end (Excel) to use and build powerful web-based and database-based tools.

I was dubious when I heard Excel (was this going to be a bunch of macros? VBA?), but by the end, I was so pleased with what I heard.

Using Interject, the data lives on the database (specified MS SQL Server database and connection), not stored in multiple versions of report.xlsx emailed back and forth and downloaded. Users can specify their own forms and reports, drill into and search stored data, and even push new data through Excel without learning a new front end.

I like the autonomy and control this gives end users (they already know and use Excel), and I really like that developers manage the back end and database side instead of reinventing the wheel with front-end. I’ll be keeping an eye on this company.

Why is my DBA so grumpy?

Presented by Rick Lowe, who jokingly described himself as a relationship counselor for DBAs and devs, the session was a good reminder to write T-SQL that makes DBAs’ jobs easier!

The first topic was NOLOCK, both why devs think they can use it and how it creates problems down the line during race conditions. Rick’s example of a dirty read that returned data that subsequently was rolled back (ie that data didn’t exist) was very illuminating. Good info about shared and exclusive locks.

JASON and the Astronauts

Awesome talk by Peter Kral (Data Engineer at FTD) about the new JSON support in MS SQL Server (after many requests from devs that this be incorporated)

I really liked that SSMS will now play nicer with data from the web, since JSON is such a common format (over XML) in API returned data, etc.

The influence of a JavaScript philosophy (obviously, since it’s JSON) was clear in error handling in  JSON functions, for example, a default to return NULL over throwing an error in default lax mode when parsing JSON with JSON_VALUE (use ‘strict’ keyword).

On the other hand, while [key] and [value] tools help parse the JSON schema are helpful, there are limits in using JSON_QUERY to drill down. In a non-relational database, changing entity structures are par for the course, but it’s not SSMS’s way of doing things. Working with either well-defined/consistent JSON data or being prepared for handling change will make JSON in T-SQL smoother for devs.