How to read the errorlog on SQL Server installed on Linux

I finally installed SQL Server on Linux, while I was messing around with some queries, I noticed the following command fails with an error

exec xp_readerrorlog

The error is

Started executing query at Line 1
Msg 22004, Level 16, State 1, Line 0 Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0 Error log location not found.
Total execution time: 00:01:01.34


So how do you then look at the log on Linux? I don't have SSMS installed on the host machine where the Linux Virtual Machine lives. SQLCMD gives the same error. I don't know if it even works from SSMS from a windows box.

Edit: so it looks like it works from SSMS

@DenisGobo SSMS reading SQL logs works. Also quick tested xp_readerrorlog 0, 1 - works fine as well.

— Andy Yun (@SQLBek) November 20, 2016

To answer this question, the errorlog is stored in this location  /var/opt/mssql/log. You need to be a superuser to access this location

So in my case, I can just execute the following from a terminal in Ubuntu

sudo cat /var/opt/mssql/log/errorlog

And it looks like this

There you have it, this is how you can look at the error log

To see all my SQL Server on Linux posts, click here: SQL Server on Linux

What we learned from the reddit SQL Server AMA thread on /r/sqlserver

The SQL Server AMA thread on reddit, has finished. I have put 8 most interesting answers here and then gave my take on it, the questions will be linked straight to the question on reddit. The answer will be in italics and my take on it will be below in regular font


What was the reasoning for having SQL Server for Linux run atop Drawbridge, rather than directly interfacing with Linux? It seams like it's not a true Linux port if NT Kernel emulation is required 

I guess you can always debate whether it is better to have an abstraction layer or do a full port. I am a practical person, and like to judge based on results. I myself am not on the Linux team, but am really impressed with what the team has managed to deliver thus far, both in terms of feature set and in terms of performance. A full port would have taken much longer and would have led to a code base that is much harder to maintain. 

I never heard of Drawbridge either. From the Microsoft Drawbridge research page: Drawbridge combines two core technologies: First, a picoprocess, which is a process-based isolation container with a minimal kernel API surface. Second, a library OS, which is a version of Windows enlightened to run efficiently within a picoprocess.

I really like the JSON support you guys added. Is JSON in SQL Server still evolving, or are you guys happy with where it is at?I would love to see a JSON data type, like XML support. Thanks!

We are collecting feedback and we will decide what would be the next step. Current NVARCHAR representation enables us to integrated JSON with in-memory, column store, does not require new types in drivers, etc. Also, some experiments show that text representation is even faster than XML type in some scenarios. The next step would be better integration with native modules that will speed up queries. We would like to know how your are using current JSON, what are limitations, and then we will decide what would be the next step.

It makes sense that they decided to use nvarchar since you can then use it with in-memory OLTP and Columnstore. We currently store json in some of our tables but we never search on it or manipulate it in the DB

How does making SQL Server available for Linux relate (or not relate) to Microsoft's famous "Embrace, Extend, Extinguish" strategy?

Two things - Satya Nadella, cloud economics - changed a lot of the way we think about things here at Microsoft now. The way I like to think of this strategy of bringing SQL Server to Linux is that it maps to Microsoft's new mission statement - "Empower every person and every organization to achieve more". SQL Server on Linux is about bringing the really powerful capabilities of SQL Server to everybody, not just those that are using Windows/Windows Server and seeing what people can do with it. It's inspiring for example to think about what the scientific or academic communities (which are oftentimes on Linux) could now achieve with SQL Server. Let's call it Microsoft's new 'Embrace, Enable, and Empower' strategy. :)

Haha I knew this would be asked, great answer also. We all remember the Ballmer Linux is a cancer quote, how things have changed

I am so sorry, but I just had to add this image  :-)

SSMS coming for Linux? If so when?

We do not plan to port SSMS "as is" to Linux/macOS for 2 main reasons: 1) SSMS on Windows depends on the VS2015 Isolated shell which is not available on Linux/macOS and 2) a direct port of SSMS will not align with the design guidelines & information architecture of apps that natively run on Linux/macOS (e.g. buttons, menus, etc. are different, the window chrome is different, etc.)

That said, we are working on a new set of multi-platform SQL client tools that DBAs and database developers can use on Linux/macOS/Windows. The new 'mssql' extension for Visual Studio Code is our first attempt in this area and we are using it to prove out the multi-platform SQL tools stack "backend" we've created.

We don't have specific dates yet but stay tuned for more details in the coming months. Meanwhile, you can try the mssql extension for VS Code on Linux/macOS/Windows from here: https://aka.ms/mssql-marketplace


We plan to have a cross-platform tool that will be Linux native. We don't have an exact date set but it's in the works for 2017. In the meantime, you can connect to SQL Server on Linux through the Visual Studio Code mssql extension as well as SSMS.

So it looks like there will be a cross-platform tool, maybe something like Visual Studio Code, make it web based. As long as it is not based on Java and has that nasty purple shiny look and feel. But in reality, all the people I worked with that used *nix always used windows and then would use PuTTY or something similar to SSH into the box and just do everything from the command line.

will SQL on Linux have an Express edition? (I think it should, to give a better alternative to all those LAMP proponents - though admittedly I think a number of switching to postgres, which is at least better than mysql)

Yes, it will! Cost = free.

This is nice, also it seems that when you license SQL Server in the future, the OS doesn't matter, if you get a 4 core license, you can install SQL Server on Linux or Windows, your choice

Is there any plan to make SQL Server work on Windows Server Nano?

This is something that we are looking into. It would require creating a new appx-style package for deployment. That in itself would be a good thing actually that could make it easier to install SQL Server (like the package based install for SQL Server on Linux) but it would be a very large development effort. Spending dev effort on that would take away from other things we would like to do. It would also mean that we end up having two different methods of patching SQL Server (.msp and appx) which could further complicate servicing. On the other hand, WS Nano is really well suited for SQL Server due to it's higher availability (less patching), smaller attack surface area, fewer services running, etc. Those are the trade offs we are thinking through, but no concrete plans one way or another right now. Let us know what you think we should do!

Installing SQL Server on Linux is really nice, here is what it looks like on redhat

sudo su

curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo

sudo yum install -y mssql-server

sudo /opt/mssql/bin/sqlservr-setup   #follow the prompts

systemctl status mssql-server #verify service is running

It also makes sense to run it on a server that is bare metal, no need for IE or printer service running.

Any chance we will ever get a step by step course that uses the developer edition of SQL server to walk someone through basic use cases of all the applications. For instance, creating a sample database, adding tables, keys. Scripting user permisions for them and encrypting them. Setting up the report server. Making a report. Building a cube, making a small data analysis setup. All in an easy to follow step by step? A basic use case example for r services.

Great minds think alike! We actually just this Wednesday posted our first set of developer tutorials for lots of different languages (node, python, C#, Java, and more) and platforms (macOS, Docker, Linux, and Windows). They cover how to get SQL Server installed, get some tools, get your first basic app going, and how to use some powerful features like columnstore. There is a tutorial for R language/services too. If that seems to be a popular thing, we'll keep going and add more to it. Check it out and spread the word! http://aka.ms/sqldev

You can find R Services samples in the following links:
https://gallery.cortanaintelligence.com/Collection/ML-Templates-with-SQL-Server-R-Services-1

https://msdn.microsoft.com/en-US/library/mt591993.aspx

Additionally, we have a new website focused around easy to use "Getting Started" tutorials. There is one R sample there:
https://www.microsoft.com/en-us/sql-server/developer-get-started/r


let me add a couple more R-related articles :)

https://github.com/Microsoft/sql-server-samples/tree/master/samples/features/r-services/Implementing%20Predictive%20Analytics

and a simple blogpost I wrote some time ago just in case it helps with the 101 https://medium.com/@davidsb/datascience-for-developers-build-your-first-predictive-model-with-r-a798f684752f

Is using the GPU for extra processing power on the horizon?

The trick with GPUs is balancing the cost of moving data onto the GPU vs the compute gains. While I can't comment on the future, we are always looking at ways to improve the performance of SQL Server and leverage the latest hardware to its fullest potential. I'd encourage you to take a look at the "SQL 2016 - It Just Runs Faster" blog series for examples of what the team has done recently.

Here are a few that I like:

• Improvements in columnstore indexes and batch processing (link)
• Automatic soft NUMA for large CPU system deployments (link)
• Hardware acceleration for encryption features like TDE by leveraging Intel's AES-NI instruction set (link)

We evaluate hardware advancements periodically for SQL Server. The new Microsoft ML library that is available with SQL Server vNext uses GPU for the neural net algorithms so you may see such capabilities in other areas of the product.

This is definitely an area we are looking at. But we don't have any concrete at the moment.

I remember when postgres did this, the performance improvement was impressive, you can read about that here PG-STROM

That is all, if you want to see all the questions and answers go here:  SQL Server AMA thread on reddit,

Interesting SQL related links for the week of Nov 18, 2016

Here are some interesting articles I read and tweeted about this past week, I think you will like these as well. If you are bored this weekend, some of these might be good for you to read


Massive Disruption Is Coming With Quantum Computing
Next year, we may see the launch of the first true quantum computers.

The implications will be staggering.

This post aims to answer three questions:

1. What are quantum computers?
2. What are their implications?
3. Who’s working on them?

New Showplan XML properties in SSMS October Release

Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016. However, SQL Server Management Studio (SSMS) did not expose this information, and so users had to look in the Showplan XML, in the context of the appropriate node and operator.

This caused some friction, and so we are happy to announce that in the latest (October) release of SSMS, these attributes are now readily available in the Properties window of an Actual Execution Plan, in the scope of each operator.

Microsoft signs deal to power data center entirely by wind power

You need a lot of energy to run a data center. Especially when your name is Microsoft, and you’re the biggest technology company on the planet. But this doesn’t necessarily have to be environmentally harmful.

The company just announced that it has inked deals with two wind farms, with the aim of entirely powering its Cheyenne, Wyoming data center from renewable sources.

Microsoft has contracted Bloom Wind farm in Kansas to provide 178 megawatts, and the Silver Sage and Happy Jack farms in Wyoming to provide an additional 59 megawatts.

Unable to drop a user in a database

A user called in for help because he wasn’t able to drop a user from a database. The error message is below

Msg 15136, Level 16, State 1, Line 2
The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.


From the error, the logical starting point is sys.sql_modules because functions, stored procedures have entries in this catalog view. Additionally, it has a column called execute_as_principal_id

So I looked there but found no entries whose execute_as_principal_id is the same user id of the user this customer tried to drop. Next I started to look at source code and found out this error is raised in a couple of other places. It gave me the clue that I didn’t have to do source code research, TSQL is all I needed to solve the problem.

Connect(); // 2016

Videos from the Connect(); // 2016 event can be found here

Screenshot of some of these

SQL Server 2016 Service Pack 1 (SP1) released !!!

With cloud first strategy, the SQL Product Team has observed great success and adoption of SQL Server 2016 compared to any previous releases. Today, we are even more excited and pleased to announce the availability of SQL Server 2016 Service Pack 1 (SP1). With SQL Server 2016 SP1, we are making key improvements allowing a consistent programmability surface area for developers and organizations across SQL Server editions. This will enable you to build advanced applications that scale across editions and cloud as you grow. Developers and application partners can now build to a single programming surface when creating or upgrading intelligent applications, and use the edition which scales to the application’s needs.


In addition to a consistent programmability experience across all editions, SQL Server 2016 SP1 also introduces all the supportability and diagnostics improvements first introduced in SQL 2014 SP2, as well as new improvements and fixes centered around performance, supportability, programmability and diagnostics based on the learnings and feedback from customers and SQL community.


SQL Server 2016 SP1 also includes all the fixes up to SQL Server 2016 RTM CU3 including Security Update MS16–136.

SQL Server on Linux Documentation

SQL Server vNext CTP1 now runs on Linux! Learn about the core relational database capabilities in this release of SQL Server, and how to install, manage, secure, and develop for this new release.

• 

  Install: SQL Server on Ubuntu, Red Hat Enterprise Linux, or run the Docker image

• 

  Manage: SSMS, PowerShell, mssql-conf

• 

  Develop: Connectivity libraries, Visual Studio tools, tutorials

• 

  Download SSMS: Run on Windows to access, manage, and develop SQL Server on Linux

• 

  Download SSDT: Development tools for building SQL Server relational databases and SQL Azure databases

• 

  Install Visual Studio Code with the vscode-mssql extension: SQL Server support in VS Code

What's New in SQL Server vNext

SQL Server vNext represents a major step towards making SQL Server a platform that enables choices of development languages, data types, on-premises and in the cloud, and across operating systems by bringing the power of SQL Server to Linux, Linux-based Docker containers, and Windows.

You can find all the new stuff at that page

Now pay attention to the next two links... did you noticed it was getting colder since Wednesday? That is because Hell just froze over!!!!

A funny comment on slashdot
Microsoft joins the Linux foundation, Google joins the .NET foundation. What's next? Hillary joins the Trump Foundation?

What?

Google Cloud to join .NET Foundation Technical Steering Group

Google is pleased to be joining the Technical Steering Group of the .NET Foundation. .NET is a key component in the modern enterprise, and the Google Cloud Platform (GCP) team has worked hard to ensure that .NET has first-class support on Google’s infrastructure, including excellent infrastructure for Windows. For years, Google has offered .NET libraries for more than 200 of its cloud services. More recently, we’ve built native GCP support for Visual Studio and PowerShell.

Come again?

Microsoft Fortifies Commitment to Open Source, Becomes Linux Foundation Platinum 

Member

The Linux Foundation, the nonprofit advancing professional open source management for mass collaboration, today announced that Microsoft has joined the organization at a Platinum member during Microsoft’s Connect(); developer event in New York.

From cloud computing and networking to gaming, Microsoft has steadily increased its engagement in open source projects and communities. The company is currently a leading open source contributor on GitHub and earlier this year announced several milestones that indicate the scope of its commitment to open source development. The company released the open source .NET Core 1.0; partnered with Canonical to bring Ubuntu to Windows 10; worked with FreeBSD to release an image for Azure; and after acquiring Xamarin, Microsoft open sourced its software development kit. In addition, Microsoft works with companies like Red Hat, SUSE and others to support their solutions in its products.

Disable CREATE OR ALTER for DDL triggers (or fix it)

I filed this bug while working on the post What's new in SQL Server 2016: CREATE OR ALTER

On SQL Server 2016 SP1 and SQL Server vNext (neither available from drop down, so I picked 2016 RTM) When executing CREATE OR ALTER with a DDL trigger, it will run fine the first time, when you execute the script again, you will get an error like the following 

Msg 2714, Level 16, State 2, Procedure safety, Line 1 [Batch Start Line 77] There is already an object named 'safety' in the database. 

So either don't allow CREATE OR ALTER when the trigger is a DDL trigger or fix it so it doesn't throw the error if you run it more than once

And to finish it of, here are some of my blog posts about SQL Server vNext and SQL Server 2016 Service Pack 1 from this week

SQL Server 2016 SP1 released, SQL Server vNext available for download

Playing around with the new stuff in SQL Server vNext and SQL Server 2016 SP1

What's new in SQL Server 2016: CREATE OR ALTER

SQL Server AMA thread on reddit is live now

The SQL Server AMA thread on reddit is now live, you can start asking questions now and they will start answering Friday 11/18 at 9:30 PDT until 14:00 PDT.

One person already has a ton of questions, just look at this list, some of these would really make a nice addition.

SQL Server is a great product, thank you for it, but we need more from you.
And here's my question.

There are many issues on Connect that desperately need resolving, some of them are open for more than 10 years. My favorite is from Adam Machanic: https://connect.microsoft.com/SQLServer/feedback/details/252226/allow-enabling-and-disabling-of-a-columns-identity-property

There are also many pointed out by the SQL MVPs and experts for years, like these:https://sqlperformance.com/2013/04/t-sql-queries/filtered-indexes

I've collected some more stuff that is missing in SQL Server and should be implemented:

• using GPU for processing!
• native regular expression support! Don't force every user to implement their own CLR, just give us optimized regexes!
• Git support for SSMS (just like it's in Visual Studio)
• row constructors! - like in WHERE (orderdate, orderid) > (@orderdate, @orderid)
• order preserving clustered columnstore
• implement PERCENT and WITH TIES for OFFSET-FETCH (it's a standard feature) to align it with old TOP operator
• TOP() OVER operator - like SELECT TOP (3) OVER (PARTITION BY custid ORDER BY orderdate, orderid)
• NTH_VALUE window function
• FILTER clause for window functions - http://modern-sql.com/feature/filter, similar to Itzik's suggestion https://connect.microsoft.com/SQLServer/feedback/details/532474
• IS [NOT] DISTINCT FROM operator
• INTERSECT ALL in addition to INTERSECT
• EXCEPT ALL in addition to EXCEPT
• RANGE BETWEEN INTERVAL '3' MONTH PRECEDING AND CURRENT ROW ability for window frames
• WITH DROP_EXISTING (alternatively CREATE OR ALTER) for everything
• ALTER TABLE .. CHECK CONSTRAINT should be a no-op if the constraint is already trusted
• ORDER BY ... NULLS FIRST/LAST
• user interface (wizards, monitoring) for Service Broker
• column name in 'string or binary data would be truncated' message!
• dark theme for SSMS
• simple change of server collation on Windows (it's super-easy in SQL Server on Linux)
• multiple tempdb databases + one "official" tempdb backwards compatible
• In-Memory tempdb
• search engine similar to Elastic Search / Splunk
• ability to install In-Database R Services on FCI
• generally speaking better coverage of standard SQL features - T-SQL is for DEVELOPERS who are constantly pushed by management to be more effective - so finally make their lives easier

This list is by no means exhaustive. Most of them are just features desctibed in ISO/IEC SQL standard.

A lot of Connect feedback has been prematurely and improperly closed with various reasons: Won't fix, by design, some are active for ages. What I would ideally want from you is to review all unfixed items and decide again whether they're worth doing or not.

Like this one - 124 votes and closed/won't fix!

• https://connect.microsoft.com/SQLServer/feedback/details/728969

This one - 179 votes - active since 2007!

• https://connect.microsoft.com/SQLServer/feedback/details/254390/over-clause-enhancement-request-top-over

Some limitations are just silly, like the one requiring something to be a single statement in a batch. If I can bypass that requirement by putting my code in a dynamic SQL and do EXEC(string) then why SQL Server cannot do that for me??

And please fix this darn slow Connect site. Maybe a reindex and statistics update will do? :-)

Finally the question: do you acknowledge this? Will you fix all this? Any comments?

What's new in SQL Server 2016: CREATE OR ALTER

SQL Server 2016 Service Pack 1 added the CREATE OR REPLACE functionality, however in SQL Server, it is called CREATE OR ALTER.  Finally it is here, this has been asked for since the 90s, it was on the SQL Server wishlist (who remembers that before they had connect :-)

CREATE OR ALTER can be used with the following four object types

STORED PROCEDURES
FUNCTIONS
TRIGGERS
VIEWS

Let's take a look how this all works

If you want to run this code, you need to be on SQL Server 2016 Service pack 1 or higher or on vNext, the @@VERSION function on SP1 and vNext returns the following


Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30
Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)   Nov  1 2016 23:24:39


Before they added CREATE OR ALTER, you had  a couple of options to create rerunable scripts. The first way was dropping the proc if it existed and having the CREATE PROC in the same script, it looked like this

--old way of dropping a proc then having a create script
IF OBJECT_ID('procTest') is not null
DROP PROCEDURE procTest
GO

CREATE PROCEDURE procTest
AS
BEGIN
 PRINT (1)
END;
GO

Another way would be to create a dummy proc if the proc didn't exist already and then just have the ALTER PROC part in your script

--another way is to have a dummy proc created,
--that way your alter part is always the same
IF OBJECT_ID('dbo.procTest') IS NULL
  EXEC ('CREATE PROCEDURE dbo.procTest AS RETURN 0;')
GO

ALTER PROCEDURE  procTest
AS
BEGIN
 PRINT (1)
END;
GO

In SQL Server 2016 with Service Pack 1, this becomes so much easier. Here is what it looks like

-- the easier way in sql server 2016 SP1 and up
CREATE OR ALTER PROCEDURE procTest
AS
BEGIN
 PRINT (1)
END;
GO

Here is how that looks with a function, pretty much the same as a procedure

-- works the same with functions
CREATE OR ALTER FUNCTION fnTest()
RETURNS INT
AS
BEGIN
 RETURN(1)
END;
GO

The view looks like this

-- also works with views
CREATE OR ALTER VIEW vwTest
AS
 SELECT 1 AS col;
GO

With triggers it is pretty much the same, here is the code that you can run, I first created a table otherwise I would not be able to create a trigger

-- first we need a table for the trigger
IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = 'BooFar')
CREATE TABLE BooFar(id int)
GO

-- works with triggers
CREATE OR ALTER TRIGGER trTest
ON BooFar
AFTER INSERT, UPDATE
AS
 RAISERROR ('Hello from trigger', 1, 10);
 GO

 -- just a test to make sure the trigger works
 INSERT BooFar values(1)

 -- you should see this in the message tab
 /*
Hello from trigger
Msg 50000, Level 1, State 10

(1 row(s) affected)
*/

The CREATE OR REPLACE syntax does NOT work with DDL triggers. If you execute this the first time it will run fine.

CREATE OR ALTER TRIGGER safety   
ON DATABASE   
FOR DROP_TABLE, ALTER_TABLE   
AS   
   PRINT 'You must disable Trigger "safety" to drop or alter tables!'   
   ROLLBACK;  

Now run it again, you will get this error

Msg 2714, Level 16, State 2, Procedure safety, Line 1 [Batch Start Line 77]
There is already an object named 'safety' in the database.

So be aware that CREATE OR ALTER does not work with DDL triggers

I submitted a connect item for this: https://connect.microsoft.com/SQLServer/feedback/details/3111866

I want them to either disable CREATE OR ALTER for DDL triggers or they should fix it so it doesn't error on a second run


Tor wrap this up, let's drop all these objects we just created, we will do that by using the Drop if exists syntax

--  drop everything by using
--  DROP object IF EXISTS
DROP TABLE IF EXISTS  BooFar
DROP PROCEDURE IF EXISTS  procTest
DROP VIEW IF EXISTS  vwTest

You can find the github repo for the series here whatisnewinsqlserver2016, all the code for this post can be found here CREATE Or ALTER

Playing around with the new stuff in SQL Server vNext and SQL Server 2016 SP1

Earlier today Microsoft released the first CTP of the next version of SQL Server, I have a post about that here SQL Server 2016 SP1 released, SQL Server vNext available for download

I downloaded and installed SQL Server on a VM (But only after the VM decided to install Windows 10 anniversary update first). I played around with some of the new things, let's take a look

Version
If you select @@VERSION, you get the following back

Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)   Nov  1 2016 23:24:39   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: ) (Hypervisor)

The compatibility for this version of SQL Server is 140

STRING_AGG

This is a brand new string function in SQL Server, If you have used MySQL then the STRING_AGG is similar to the GROUP_CONCAT function, however you can't use DISTINCT like you can in MySQL

The function STRING_AGG concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string. This function is available beginning with SQL Server 2016 SP1

Here is a sample query

SELECT STRING_AGG (name, ',') AS csv 
FROM master..spt_values
WHERE type = 'A'; 

Here is the output

rpc,pub,sub,dist,dpub,rpc out,data access,collation compatible,system,use remote collation,lazy schema validation,remote proc transaction promotion

Nothing special of course but at least you don't have to write your own version and thinking about removing the last comma

A better example would be if you wanted to get all the columns for a table next to the table name
You would think the query would be like this

SELECT t.name,STRING_AGG (c.name, ',') AS csv
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
ORDER BY 1

However that gives you the following error

Msg 8120, Level 16, State 1, Line 41
Column 'sys.tables.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

You need to add a GROUP BY to the query

SELECT t.name,STRING_AGG (c.name, ',') AS csv
FROM sys.tables t
JOIN sys.columns c on t.object_id = c.object_id
GROUP BY t.name
ORDER BY 1

Here is what you get back

That looks much better, I can see myself using this a lot in the future

In memory OLTP/Columnstore


According to the documentation sp_spaceused is now supported for in-memory tables.

I tried it out and with SQL Server 2016 I get back 0 rows and 0 KB, with vNext I get back rows but NULL for everything else

  name rows reserved data index_size unused

ShoppingCart 0                   0 KB 0 KB 0 KB 0 KB  -- 2016

ShoppingCart 53248              NULL NULL NULL NULL -- vNext

Not sure what is going on there.

You can now add a columnstore index to a table that has a varchar(max) data type in vNext

CREATE TABLE t_bulkload (
accountkey int not null,
accountdescription varchar (max),
accounttype char(500),
AccountCodeAlternatekey int)
GO

CREATE CLUSTERED COLUMNSTORE index t_bulkload_cci on t_bulkload

Running that on SQL Server 2016 gives the following error

Msg 35343, Level 16, State 1, Line 39
The statement failed. Column 'accountdescription' has a data type that cannot participate in a columnstore index. Omit column 'accountdescription'.

Running that same code on SQL Server vNext 2016 gives the following warning

Warning: Using Lob types (NVARCHAR(MAX), VARCHAR(MAX), and VARBINARY(MAX)) with Clustered Columnstore Index is in public preview. Do not use it on production data without backup during public preview period.


Here are some more new things in regard to in memory tables

• sp_spaceused is now supported for in-memory tables.
• sp_rename is now supported for native modules.
• CASE statements are now supported for native modules.
• The limitation of 8 indexes on in-memory tables has been removed.
• TOP (N) WITH TIES is now supported in native modules.
• ALTER TABLE against in-memory tables is now substantially faster in some cases.
• Transaction redo In-memory tables is now done in parallel. This substantially reduces the time to do failovers or in some cases restarts.
• In-memory checkpoint files can now be stored on Azure Storage. This provides equivalent capabilities to MDF compared to LDF files, which already have this capability.

As a final note on in memory OLTP, I must say that I ran everything in SQL Server 2016 and also in SQL Server vNext, running the stuff on the vNext instance seemed faster to me.

sys.dm_os_host_info

This is a new OS related system DMV and it returns one row that displays operating system version information.

SELECT * FROM sys.dm_os_host_info;  

Here is the output

host_platform	host_distribution	host_release	host_service_pack_level	host_sku	os_language_version
Windows	Windows 10 Pro	6.3		48	1033

Here is a sample result set on Linux:

host_platform	host_distribution	host_release	host_service_pack_level	host_sku	os_language_version
Linux	Ubuntu	16.04		NULL	1033

Here is what it looks like in SSMS

That is all for now..... you can find all the new stuff on MSDN https://msdn.microsoft.com/en-US/library/mt788653(SQL.130).aspx

SQL Server 2016 SP1 released, SQL Server vNext available for download

Today Microsoft announced the CTP of the next version of SQL Server, you can download it here https://www.microsoft.com/evalcenter/evaluate-sql-server-vnext-ctp

As you can see I am already downloading this version

What's New in SQL Server vNext https://msdn.microsoft.com/en-US/library/mt788653(SQL.130).aspx

Install SQL Server on Linux https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup

SQL Server on Linux Documentation https://docs.microsoft.com/en-us/sql/linux/


SQL Server Service Pack 1

Also announced was Service Pack 1 of SQL Server 2016, you can download that here https://go.microsoft.com/fwlink/?linkid=835368

There are so many cool things in this service pack. For one,  all the editions now support all the programmability features like indexed views, columnstore indexes, partitioning etc etc, see image below

You are still bound by the memory and CPU limits but at least your code doesn't have to change, this is good news for ISVs.

A couple of more tidbits....

• CREATE OR ALTER (Yes, we heard you !!!) – New CREATE OR ALTER support makes it easier to modify and deploy objects like Stored Procedures, Triggers, User–Defined Functions, and Views. This was one of the highly requested features by developers and SQL Community.

• New DMF sys.dm_exec_query_statistics_xml – Use this DMF to obtain actual query execution showplan XML (with actual number of rows) for a query which is still being executed in a given session (session id as input parameter). The showplan with a snapshot of current execution statistics is returned when profiling infrastructure (legacy or lightweight) is on.
• New DMF for incremental statistics – New DMF sys.dm_db_incremental_stats_properties to expose information per–partition for incremental stats.
• Better correlation between diagnostics XE and DMVs – Query_hash and query_plan_hash are used for identifying a query uniquely. DMV defines them as varbinary(8), while XEvent defines them as UINT64. Since SQL server does not have “unsigned bigint”, casting does not always work. This improvement introduces new XEvent action/filter columns equivalent to query_hash and query_plan_hash except they are defined as INT64 which can help correlating queries between XE and DMVs.

There is much more, see all the news here: https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/


I also played around with some of the new stuff after installing vNext, see here:
Playing around with the new stuff in SQL Server vNext and SQL Server 2016 SP1

What's new in SQL Server 2016: Truncate partitions

In my The best thing about the PASS summit for me is... post I said that I would do the following

I will also do a series about new stuff in SQL Server 2016, but that will be in between all the other posts. Whenever someone asked during a session how many people were already on SQL Server 2016, very few hands went up, this is the reason, I want to blog about this as well.
For all these series of posts, I plan to have a repo on github with a yet to be determined name. Then I will have all the SQL code for all the series organized there

This is the first post in that series

You can find the github repo for the series here whatisnewinsqlserver2016, all the code for this post can be found here TruncatePartitions

New in SQL Server is the ability to truncate a partition or more than 1 partition, this is very nice because we all know that truncate is much faster than a delete. Let's see how this all works

First create this simple table

CREATE TABLE SalesPartitioned(
 YearCol SMALLINT NOT NULL,
 OrderID INT NOT NULL, 
 SomeData UNIQUEIDENTIFIER DEFAULT newsequentialid())
GO

Now it is time to insert some data, we are going to create data for 6 years, we will use those years then to partition the data on. This query will insert 6 times 2048 rows, 2048 rows per year

INSERT SalesPartitioned (YearCol,OrderID)
SELECT 2013,number
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2014,number + 2048
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2015,number + 4096
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2016,number + 6144
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2017,number + 8192
FROM master..spt_values
WHERE type = 'P'
UNION ALL
SELECT 2018,number + 10240
FROM master..spt_values
WHERE type = 'P'

Now let's create the partition function

CREATE PARTITION FUNCTION pfFiscalYear(SMALLINT)
AS RANGE LEFT FOR VALUES(2013,2014,2015,2016,2017)
GO

Create the partition scheme

CREATE PARTITION SCHEME psFiscalYear
AS PARTITION pfFiscalYear ALL TO ([PRIMARY])
GO

Add a primary key to the table, add it to the partition scheme we created above

ALTER TABLE dbo.SalesPartitioned ADD CONSTRAINT
    PK_Sales PRIMARY KEY CLUSTERED (YearCol,OrderID)
ON psFiscalYear(YearCol)
GO

Now let's see what we have as far as counts for each partition

SELECT partition_number,rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('SalesPartitioned')

Here are the results

partition_number rows
1 2048
2 2048
3 2048
4 2048
5 2048
6 2048

As you can see we have 6 partitions, each partition has 2048 rows


We can verify this by running a count ourselves

SELECT YearCol, $PARTITION.pfFiscalYear(YearCol) AS Partition,
COUNT(*) AS [COUNT] FROM SalesPartitioned
GROUP BY $PARTITION.pfFiscalYear(YearCol),YearCol
ORDER BY Partition;
GO

YearCol Partition COUNT
2013 1 2048
2014 2 2048
2015 3 2048
2016 4 2048
2017 5 2048
2018 6 2048


Now it is time to truncate a partition.
In order to truncate a partition, you use the following syntax




TRUNCATE TABLE ........
WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) )

So if we just want to truncate partition 2, we would execute the following

TRUNCATE TABLE SalesPartitioned
WITH (PARTITIONS (2));
GO

Checking those same count queries from before shows that partition 2 has no rows after we executed the truncate command

SELECT partition_number,rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('SalesPartitioned')

partition_number rows
1 2048
2 0
3 2048
4 2048
5 2048
6 2048

SELECT YearCol, $PARTITION.pfFiscalYear(YearCol) AS Partition,
COUNT(*) AS [COUNT] FROM SalesPartitioned
GROUP BY $PARTITION.pfFiscalYear(YearCol),YearCol
ORDER BY Partition;
GO

YearCol Partition COUNT
2013 1 2048
2015 3 2048
2016 4 2048
2017 5 2048
2018 6 2048



There are two ways you can truncate a bunch of partitions, for example if you want to remove 4,5 and 6, you can use WITH (PARTITIONS (4, 5, 6)) or you can use a range like shown in the code below

TRUNCATE TABLE SalesPartitioned
WITH (PARTITIONS (4 TO 6));
GO

After we execute that and we check the counts again, we see that we are now only left with partitions 1 and 3

SELECT partition_number,rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('SalesPartitioned')

Here are the results from that query

partition_number rows
1 2048
2 0
3 2048
4 0
5 0
6 0

SELECT YearCol, $PARTITION.pfFiscalYear(YearCol) AS Partition,
COUNT(*) AS [COUNT] FROM SalesPartitioned
GROUP BY $PARTITION.pfFiscalYear(YearCol),YearCol
ORDER BY Partition;
GO

Here are the results

YearCol Partition COUNT
2013 1 2048
2015 3 2048



What happens if you specify an invalid partition in your truncate statement?

TRUNCATE TABLE SalesPartitioned 
WITH (PARTITIONS (12)); 

You get the following message

Msg 7722, Level 16, State 2, Line 1
Invalid partition number 12 specified for table 'SalesPartitioned', partition number can range from 1 to 6.

There you have it, this is how truncate partitions work


You can find the github repo for the series here whatisnewinsqlserver2016, all the code for this post can be found here TruncatePartitions