Deep dive–SQL Server 2012

	Tobias	Simon	Mikael

A lot of buzz – new release in SQL Server, four years of intense development…. I was very curious about what has happened during this long period. Will there be anything that could change my life? So I went to this seminar with a lot of expectations early in the morning of April 4th in Malmö.

It was a promising agenda with a Swedish guy – direct from Redmond – working as a program manager in Redmond, Tobias Ternström, warmed up by Simon Lidberg and Mikael Colliander  from MS in Sweden.

First was an overview by Simon presenting the three main pillars: “Mission Critical Confidence”, “Breakthrough Insight” and “Cloud On Your Terms”. ? What is he talking about?? The intro was ended by a demo that was supposed to show a new feature called “AlwaysOn”. Unfortunately this seemed to be only a vision since Mikael failed to get the fall over Server to start… So the start was a bit from 100% .

Tobias arriving a little bit late started to talk about news in T-SQL, now available in SQL Server 2012. Functions that has been in PostGre for some time,  making it possible to do something useful (windowing) with the OVER-clause from 2005 and which has been dorming for seven years . Of course not yet fully implemented, but a good start! Still limitations with time interval not being a native SQL Server data type, yet… And NEW!! SEQEUNCE:s also in PostGre for some time. Some nice new functions for easing data cleansing with T-SQL, TRY_CONVERT as an example. The most trivial data cleansing functions of them all TRIM, still not implemented!! I don’t know of any database engine not having this function, they didn’t have the time was the excuse??? I can’t take more than a few minutes to include that functions, but we have to wait at least four more years . Strange, do they have a party in Redmond or what are these guys doing?

Mikael followed up with the new possibilities to interact with the file system from within SQL with SQL-syntax (functions of the fso-object in scrrun.dll since the end of the 90:s) The strange limitation was though that the folder structure has to be generated from within SQL, I don’t know of any messy G-disk that is created that way?? It is there the search logic is needed not in some parts of it imported into SQL-Server, or should it all be moved over to SQL-Server. If the user would like to make a new subfolder he has to contact the DB-admin??? Can’t really see how they thought that would ever happen? There must be something I missed on this , it was just before the very nice lunch served, so I slept???
Very nice and tasty! 

Simon had the chance “after lunch” to give us the insight into the need of order in the database, a term called Data Dictionary in the 80:s and now renamed to “EIM in SQL Server 2012”. Nice feature but only available in Enterprise edition, so of no interest in my business. Still I am not sure that the central data storage with order is more than a dream, is it possible in realty, I doubt it.

Next on stage was Tobias again talking now about SQL Azure. $5/month for 100Mb SQL-server database, without having to ask for help from the IT-department. For me this is a dream come true!! So my brain boiled up at least ten new business ideas based on this concept. This pitch was the value of the day for me, I will cover my discoveries in posts to follow. This is good, very good news!! AND in Azure you only wait months for new features, so hopefully TRIM appears their before I die.

Mikael was next on stage talking about performance in the big arena. Not some stupid performance issue with a user complaining about response time. It was bulk loading times (DW:s I know of load in 15-20 minutes without partitioning..) and it was “Columnstore indexes” both features of Enterprise, ie not my business.

The show ended with questions to the panel. I asked about getting full use of my 24 cores in my Server, making the Optimizer understand to use parallel plans instead of running in one core. I was told it was my fault that the stupid engine handled things wrong, parallelism is “black magic” covered in some “white papers” nobody understands. And have you updated statistics?? Is not that what SQL Server is meant to understand and do if it is needed?? In other words they didn’t have a clue. I was advised to use some words to google on: Paul White, Conner (Angry Irish) and Joe Chang. When I have read and understood I will probably tell you here…  (Half promise – black magic)
If I fail I will probably convert to QlikView as Donald Farmer, where the data is always stored in the server for me, without having to tell how, so it could not be my fault.

So to sum up. Some useful stuff, and hopefully a day spent worth the cost, at least I had a nice day, with nice company and some really enthusiastic choir leaders.  Thanks!  /Rickard