The STRING_AGG function has been introduced in SQL Server 2017.
Below shows a simple example of it in use to comma separate a column of data into a single row:
DECLARE @t TABLE (c varchar(10));
--insert some data
INSERT INTO @t(c) VALUES ('one'),('two'),('three'),('four'),('five');
SELECT c FROM @t;
DECLARE @old varchar(100);
SELECT @old = COALESCE(@old + ',', '') + c FROM @t;
SELECT @old AS old_way;
SELECT STRING_AGG(c,',') AS new_way FROM @t;
It took a while, but finally SQL Server Management Studio 17.0 is available for download.
At 729MB the download exe is smaller than the 16.5.3 version which was 898MB. This huge size difference could mainly be due to the PowerShell module being moved into the PowerShell gallery. Just imagine attempting to install this via the good old 1.44 floppy disks.
It seems that these days to install SQL Server you’ll spend a lot of time trying to locate the pieces to install i.e. installation media + ssms + powershell + snac + patches etc, that all adds up to a lot of links. I kind of liked the old install where everything was in one place, but times are definitely a changing.
Extract from the SSMS download site: •Over 100 connect issues fixed •SQL Server PowerShell module moved out of SSMS and into the PowerShell gallery •Icons updated to support high-DPI display modes •Numerous performance improvements in Object Explorer •WSUS support for upgrading to future 17.X versions •Improved Multi-factor authentication support •Enhancements to Availability Group functionality (Support for SQL Server on Linux, direct seeding, endpoint URL handling, and more) •Showplan enhancements including new feature to analyze actual existing showplan to help diagnose issues •SQL Server Analysis Server 1400 support •New DAX Query Windows •Numerous other enhancements for Power Query support
In SQL Server when you divide by 0 you get an error.
0 divided by a number works just fine.
Back to the topic; handling divide by 0 is typically performed using CASE
--declare a table
DECLARE @t TABLE (c1 int, c2 int);
--insert some test data
INSERT INTO @t (c1, c2) VALUES (0,2);
INSERT INTO @t (c1, c2) VALUES (2,0);
--view the table
SELECT * FROM @t;
--handle a divide
CASE WHEN c2 = 0 THEN 0 ELSE c1/c2 END AS result
Yes SQL Server vNext officially has the name SQL Server 2017.
CTP 2.0 is now available to download and further details on the announcement can be found here
Self tuning databases? Sounds interesting or possibly a cause for concern. Hopefully these features have an off switch in case SQL starts optimizing in the wrong direction :)
“New in SQL Server 2017, we’re adding the Adaptive Query Processing family of intelligent database features. These features automatically keep database queries running as efficiently as possible without requiring additional tuning from database administrators.”
What does surprise me is the rate of new version releases.
Progress is good, but SQL Server 2012 -> 2014 -> 2016 -> 2017, this really starts to raise a dilemma for businesses/developers/DBAs as to which version should they be using/developing/training for. Throw Linux and the cloud into the mix and well it just gets even more interesting.
Businesses like stability and these new versions can introduce risk which is exactly what businesses want to avoid.
To me, this release cycle has to slow down or it could end badly for all parties. Although in saying that; DBAs would become more in demand as resolving incompatibility and version issues are likely to significantly increase.