Amsterdam, Noord Holland, Nederlands, European Union. — April 1, 2016 — Microsoft Corp. on Friday morning announced that it has released the next version of its flagship Relational (and beyond) Database Management System SQL Server Horizontica.
Here is a quick rundown of all the new, exciting and shiny things......
Goes to eleven.
What we have done here is, if you select this option, we will offload very CPU intensive operations to the GPU. Think about it, GPUs are many times faster than CPUs… hookup a bunch of graphic card to you server and your CPU utilization goes down by 99%. Here is what an external GPU rig look like from one of our biggest beta testers
Mr Stein who is a member of that team will explain what he likes the most about
Goes To Eleven.
Frank E. Stein
The best part about
Goes To Eleven is that if the GPU used capacity is below 50%, SQL Server will automatically use the excess capacity to start bitcoin mining. This in turn will lower the cost of running SQL Server, it really is a no brainer. When you need capacity it is there, when you don't need it, you earn money. Being a former Oracle customer where the licensing model is "How much money do you have?" this is really welcome.
Some more new goodies
BSON
Every vendor has JSON by this point, we took it s step further and added Binary JSON also known as BSON support
MINDOP
Minimum degree of parallelism. Brand new in this version, you can now specify the minimum number of CPUs/GPUs that a query should use. If you know your query is going to return a lot of data...instead of adding indexes...think big and use a minimum of 128 CPUs/GPUs for your query
Rowstore
When we looked at Vertica and Sybase IQ customers we noticed a lot of queries executed were using SELECT *, this is very slow with a columnar data storage. With our patent pending rowstore, it is as fast as the traditional page store. The ROI is immense, no more do programmers and brogrammers have to find all the code where a table is used to add the additional column, SELECT * is always correct. Rowstore is also more natural, when getting the data back people read from left to right or right to left, people don't read from top to bottom, since the data is already stored in rows...no transform needs to happen, this also makes data display faster.
Variable page sizes
Instead of just 8K page sizes, SQL Server now also offers the following page sizes: 2K, 4K, 8K, 16K, 32K, 64K and 640K. The reason we have 640K is because that is the max page size anyone will ever need, you probably will not use it now, but you might in 10 years, we are already prepared for that
Smallishint
Smallishint is a 3 byte integer data type, bigger than tinyint and smaller than smallint. You can store values up to 4096
Myriaton
We took the concepts of Hekaton and got another 100x improvement
One of our customers will explaining how they are using it and how it has improved their applications
Dr A. Cula
I am Dr A. Cula and I work for Ichor, a company specializing in eternal life. We are running a big blood bank, we need to keep this stocked for our mmm inner circle members. We use tagging of subjects so that we can go and mmm get more blood when needed. We were always running into problems when summer came along..our mmm dispatchers and collectors don't like to work when it is sunny. So we needed really good performance during summer hours. After switching SQL Server Horizontica and using Myriaton we now are so efficient that we opened up 4 more blood banks. We could never have envisioned something like this was possible, because of this we also expanded our inner circle by 200%
Universal Stretch and Universal Availability Groups
This is really exciting, instead of a regular stretch database on Azure, Universal Stretch will copy your data on one of our data centers located in the universe. This year it is just on the Moon where we have a data center already, in 2 years you can also use the one we are currently sending to Mars. Then when we start sending people to Mars, you are already open for business...the data is already there and it will be synchronized between Earth, Moon and Mars. Universal Availability Groups work the same way, you plug in the planet or moon and we take care of the rest.
ChandarUma ChandranJaya (also known as CU) program manager on the SQL Server programmability team will fill us in on some additional new features not mentioned yet.
New JOINS
ANTI JOIN
This is a brand new JOIN and it is ANSI SQL 2022 compliant. It is basically a JOIN between 2 tables on a column where the values are not the same (Think like NOT EXISTS)
SELECT *
FROM Table1 t1
ANTI JOIN Table2 t2
ON t1.ID = T2
CUBE JOIN
The CUBE JOIN takes the CROSS JOIN to whole other level...instead of returning all rows in one table for each row in the other tables.... the CUBE JOIN returns all values in all columns in all rows in one table for each value in all columns in all rows in another table... it is like a hypercube...
SELECT *
FROM Table1 t1
CUBE JOIN Table2 t2
NATURAL JOIN
The NATURAL JOIN joins two tables on columns with the same name
SELECT *
FROM Table1 t1
NATURAL JOIN Table2 t2
KEY JOIN
The KEY JOIN is really cool, what it does it joins tables based on primary and foreign keys, no need to specify these in the join condition anymore
SELECT *
FROM Table1 t1
KEY JOIN Table2 t2
New Traceflags
Traceflag 666
This is a brand new traceflag, it will give your SQL Server instance hellish performance. It acts like a Double Distributed Denial Of Service (DDDOS) attack against you SQL Server instance...... IO, Memory.... everything will be completely saturated... however you can still use the DAC (Dedicated Administrative Connection) to get in and run queries against DMVs to check the Subsystem poerformance
New functions
We have implemented the following new functions
IsDaylightSavingsTime
IsNotWeekend
Trim
sp_MsForEachServer
DBCC CheckCrappyCode
DBCC
Quartile
DCOUNT
DAYS360
IsOdd
IsNontext
Gigo
Lifo
Fifo
Fido
Specialized indexes
We now have the following new indexes
DateIndex designed to work with all the date data types
FunctionIndex, finally you can index for example the first 5 characters of a column only, you can use all the functions available in SQL Server
Swift
Swift is a brand new storage engine, you can use this when you don't need constraints, keys..after all real developers manage those in the app tier. You enable this per database and you can make the same face as Taylor when thinking how you don't have to deal with all those nasty constrains that just get in the way. The beauty of Swift is that if you do have constraints in your DDL scripts, they will be just silently ignored. No more scripts that have to run in a specific order...it always runs fine..., your manager will love, no adore you..
There are many more things that are new, you can visit the SQL Server homepage to find out even more exciting stuff
