Merry Christmas and Happy New Year!
2017 went by fast and it's time for a new year.2017 was a year full of content, SQL 2017, new versions of Management Studio and two brand new tools for SQL Server!
With SQL 2017 came a lot of new and improved features. Those that we see made great use include resumable online index rebuild, adaptive query processing, automatic database tuning and improvements to in-memory functionality.
Microsoft has also released a lot of new features this year for Management studio, the latest version has an interesting Vulnerability Assessment feature for security work around a database. Looks very slick, what we used to do manually in Excel sheets (see previous blog posts on SQL Server security) is now available directly in SSMS.
You can read more about it here, among other things:
https://blogs.technet.microsoft.com/dataplatforminsider/2017/12/11/whats-new-in-ssms-17-4-sql-vulnerability-assessment/
One of the new tools looks like a possible replacement for SQLCMD, mssql-cli, built in pyhton (requires an installation of python to run), open source, freeware, OS independent and with intellisense! https://github.com/dbcli/mssql-cli/
For those of you who want a bit more interface but think SSMS is a bit overkill, check out the other tool Microsoft recently released: SQL Operations Studio. It has the same tree structures for databases and objects and query windows etc. as SSMS but not all the functionality. Instead, it provides other capabilities such as being able to have graphs and KPIs of e.g. backups, database sizes, free disk space or performance directly in the tool. https://docs.microsoft.com/en-us/sql/sql-operations-studio/download
Thank you for this year and Merry Christmas and Happy New Year to all blog readers, customers and colleagues!
As usual, below script runs on SQL 2016 or later with the result as text 🙂
SET NOCOUNT ON;
DECLARE @t table (id int, r nvarchar(125))
INSERT INTO @t
VALUES(1,'32;8,44;1,45;3,46;1,95;1'),
(2,'32;6,95;1,47;1,95;1,44;1,95;1,92;1,40;1,95;1,41;1'),
(3,'32;5,40;1,95;1,44;1,95;1,44;1,44;1,95;1,44;1,41;1'),
(4,'32;6,47;1,32;1,97;1,32;1,97;1,32;1,92;1'),
(5,'32;5,47;1,39;1,44;1,46;1,111;1,46;1,44;1,39;1,92;1'),
(6,'32;3,95;1,40;1,44;1,32;1,39;1,32;1,44;1,32;1,39;1,32;1,44;1,41;1,95;1'),
(7,'32;1,44;1,47;1,95;1,47;1,39;1,45;1,44;1,46;1,44;1,46;1,44;1,45;1,39;1,92;1,95;1,92;1,44;1'),
(8,'40;1,44;1,46;1,47;1,44;1,95;4,58;1,95;4,44;1,92;1,46;1,44;1,41;1'),
(9,'40;1,95;1,41;1,40;1,95;4,91;1,45;1,93;1,95;4,41;1,40;1,95;1,41;1'),
(10,'32;3,124;1,32;1,95;3,32;1,124;1,32;1,95;3,32;1,124;1'),
(11,'32;3,40;1,44;1,95;1,44;1,95;1,47;1,32;1,92;1,95;1,44;1,95;1,44;1,41;1'),
(12,'32;4,40;1,95;2,41;1,32;3,40;1,95;2,41;1')
;WITH cc AS (
SELECT id, s.value AS c
FROM @t
CROSS APPLY STRING_SPLIT(r, ',') s
),
c AS (
SELECT id, c, REPLICATE(CHAR(SUBSTRING(c, 1, CHARINDEX(';',c)-1)), SUBSTRING(c, CHARINDEX(';',c)+1, 8000)) AS s
FROM cc
)
SELECT REPLACE(LTRIM(SUBSTRING(
(
SELECT '^' + CAST(t2.s AS varchar(25))
FROM c t2
WHERE t1.id = t2.id
FOR XML PATH('')
)
,0,40)),'^', '') AS [God Jul! wishes AddPro DBA]
FROM c t1
GROUP BY t1.id
ORDER BY t1.id
