Why you need additional privileges to truncate tables compared to delete statements

One of the people on our team wanted to have the ability to truncate tables on the staging database while this person was doing some testing.

 Here is what Books On Line has about permissions for the TRUNCATED statement

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

Before I answer why someone would need ALTER TABLE permissions when the person already has DELETE permissions, let’s run some code that will show the ‘problem’.

CREATE DATABASE Test
go
 
USE Test
GO
 
CREATE TABLE TestTruncate(Id int)
GO
 
INSERT TestTruncate values(1)
GO

Now create a new user and give the user datareader and datawriter permissions

USE master
GO
CREATE LOGIN TestLogin WITH PASSWORD=N'Test', 
DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE Test
GO
CREATE USER TestLogin FOR LOGIN TestLogin
GO
USE Test
GO
ALTER ROLE db_datareader ADD MEMBER TestLogin
GO
USE Test
GO
ALTER ROLE db_datawriter ADD MEMBER TestLogin
GO

Now that the user is created, login as that user and run the TRUNCATE TABLE command

TRUNCATE TABLE TestTruncate

Msg 1088, Level 16, State 7, Line 1
Cannot find the object “TestTruncate” because it does not exist or you do not have permissions.

As you can see, you don’t have permission. Executing a delete will work just fine

DELETE TestTruncate

(1 row(s) affected)

Before I give you a workaround, let’s try to figure out why the minimum requirement is ALTER TABLE.
What is the difference between a DELETE and a TRUNCATE in terms of logging? When a TRUNCATE occurs, the operation does not log individual row deletions, a DELETE operation does. The reason this is important is because if you have a trigger on the table, in needs to be disabled before the TRUNCATE occurs. Now you know why ALTER TABLE is required, triggers need to be disabled.

ALTER TABLE SomeTable DISABLE TRIGGER SomeTrigger

And in order to disable the trigger, ALTER TABLE permissions are required as a minimum.

But I don’t want people altering tables on our staging and QA servers, so here is one way of giving the person the ability to TRUNCATE a table without giving them permissions explicitly. Create a stored procedure and use WITH EXECUTE AS, this will define the execution context of the stored procedure. In the example below, I picked a user that has sufficient privileges to perform the TRUNCATE.

CREATE PROCEDURE prTruncate
WITH EXECUTE AS 'SuperUser'
AS
TRUNCATE TABLE TestTruncate
GO

All you have to do is give your user execute permissions to the stored procedure you just created

GRANT EXECUTE ON prTruncate TO TestLogin
GO

Now if you execute the stored procedure as the TestLogin user, you will see it will run just fine

EXEC prTruncate

Hope this helps someone in the future who is filling up his or her transaction log these days with all those DELETE statements

Did you know that the MSSQL Tiger Team has a GitHub repo?

I just found out that the Microsoft SQL Server Tiger team has a GitHub repository where they host a bunch of SQL Server scripts

It is called the tigertoolbox. In the image below, you can see what is in this repository

You can find that repository here: https://github.com/Microsoft/tigertoolbox

If you want, you can watch this repo, you will then be notified for any changes to any of  the files

Also make sure to follow the @mssqltiger twitter account to stay up to date with any news from the MS Tiger Team.

Finally, make sure to add their blog to your favorite rss reader: https://blogs.msdn.microsoft.com/sql_server_team/

Interesting bug with milliseconds and datetime on SQL Server 2016

The other day, I was messing around and ran code like the following on SQL Server 2016

DECLARE @ms int
DECLARE @datetime datetime = '2016-11-03T10:18:17.847'   --846 on SQL 2016
SET @ms = DATEPART(ms, @datetime)

SELECT  @ms
GO

DECLARE @ms int  
DECLARE @datetime datetime2 = '2016-11-03T10:18:17.847'  --847
SET @ms = DATEPART(ms, @datetime)

SELECT  @ms
GO

DECLARE @ms int
DECLARE @datetime time = '2016-11-03T10:18:17.847'   --847
SET @ms = DATEPART(ms, @datetime)

SELECT  @ms
GO

When I ran the code on SQL Server 2014 and SQL Server 2012, I got back 847 for all 3 queries. Running it on SQL Server 2016 returns 846 instead of 847 for the datetime data type.

Interesting, something must have changed. I then did a couple of searches on the internetz and .....


....I actually found this comment from Erland Sommarskog on the MS forums

Yes, this is an intended change.
datetime has a resolution of 3 1/3 ms and the exact value of what looks like 2016-09-27 18:03:03.297 is 2016-09-27 18:03:03.296666...
Previously this was not reflected in the internal represenation, but this was changed in SQL 2016.
It seems that you found the earlier thread about this. The poster there expressed concern about Entity Framework, but there was a suggestion from Erik Eskjov Jensen, which I am not able to evaluate myself. Does his suggestion help you?

From there I found that someone already filed a bug for this: https://connect.microsoft.com/SQLServer/feedback/details/3104723

And just 3 days ago, a comment was posted

Posted by Dan Guzman on 10/1/2016 at 9:04 PM
Yes, this is an intended change but has not yet been documented in the Breaking Changes topic in the SQL Server 2016 Books Online as of this writing.
The datetime value of '2016-09-27 18:03:03.297' is actually '2016-09-27 18:03:03.2966666...' because SQL Server internally uses 1/300 second interval units for the time portion. The infinitely repeating value is rounded to the fixed datetime precision of 3, resulting in the '2016-09-27 18:03:03.297' value. However, when the value is converted to a higher precision type, the repeating value is rounded to the target data type precision, which is '2016-09-27 18:03:03.2966667' in the case of datetime2(7).

This behavior is different than before SQL 2016, where the internal value was first rounded to a precision of 3, losing the available sub-millisecond value. As you observed, the conversion behavior may be controlled with the database compatibility level.

So there you have it, now you know the reason and this is indeed a breaking change.

So I guess it is a feature not a bug

Are your foreign keys indexed? If not, you might have problems

When you add a primary key constraint to a table in SQL Server, an index will be created automatically. When you add a foreign key constraint no index will be created. This might cause issues if you don't know that this is the behavior in SQL Server. Maybe there should be an option to automatically index the foreign keys in SQL Server, what do you think?

The other day some deletes on a newer table in the test environment became really slow. We had a primary table with a couple of hundred rows, we loaded up between 200 and 300 million rows into the child table. Then we deleted the child rows, this was fast. After this, we deleted one row from the primary table and this took several seconds.

When I looked at this I noticed something interesting, the most time during the delete was spent doing a lookup at the child table. Then I noticed that the foreign key was not indexed. After we added the index the delete became thousands of times faster

Let's try to replicate that behavior here

First create these primary table, we will add 2048 rows to this table

--Table that will have 2048 rows
CREATE TABLE Store(StoreID int not null,
  DateCreated datetime not null,
  StoreName varchar(500),
  constraint pk_store primary key   (StoreID))
GO

--insert 2048 rows
INSERT Store
SELECT ROW_NUMBER() OVER(ORDER BY t1.number) AS StoreID,DATEADD(dd,t1.number,'20161101') 

AS datecreated, NEWID()
FROM master..spt_values t1
WHERE t1.type =  'p'

Now create the child table and add 500K rows, this might take up to 1 minute to run since the rows are pretty wide.

-- table that will also have 500000 rows, fk will be indexed  
CREATE TABLE GoodsSold (TransactionID int not null,
   StoreID int not null,
   DateCreated datetime not null,
   SomeValue char(5000),
   constraint pk_transaction primary key   (TransactionID))

INSERT GoodsSold
SELECT top 500000 ROW_NUMBER() OVER(ORDER BY t1.number) AS TransactionID, t2.StoreID, 

DATEADD(dd,t1.number,'20161101') AS datecreated, REPLICATE('A',5000)
FROM master..spt_values t1
CROSS JOIN  Store t2
WHERE t1.type =  'p'

Now it is time to add the foreign key constraint and index this foreign key constraint

-- adding the foreign key
ALTER TABLE GoodsSold  WITH CHECK ADD  CONSTRAINT FK_StoreID FOREIGN KEY(StoreID)
REFERENCES Store(StoreID)
GO

ALTER TABLE GoodsSold CHECK CONSTRAINT FK_StoreID
GO

-- index the foreign key
CREATE index ix_StoreID on GoodsSold(StoreID)
GO

We will create another set of tables, let's start with the primary table, we will just insert into this table all the rows from the primary table we created earlier

-- create another primary table
CREATE TABLE StoreFK(StoreID int not null,
  DateCreated datetime not null,
  StoreName varchar(500),
  constraint pk_storefk primary key   (StoreID))
GO


-- add the same 2048 rows from the primary table with indexed FK
INSERT StoreFK
SELECT * FROM Store
GO

For the child table, it is the same deal, we will add all the rows from the child table we created earlier into this table

-- Add another FK table
CREATE TABLE GoodsSoldFKNoIndex (TransactionID int not null,
   StoreID int not null,
   DateCreated datetime not null,
   SomeValue char(5000),
   constraint pk_transactionfk primary key   (TransactionID))

-- add same 500K rows from table with FK index
INSERT GoodsSoldFKNoIndex
SELECT * FROM GoodsSold

Let's add the foreign key constraint, but this time we are not indexing the foreign key constraint

-- add the FK but do not index this
ALTER TABLE GoodsSoldFKNoIndex  WITH CHECK ADD  CONSTRAINT FK_StoreID_FK 

FOREIGN KEY(StoreID)
REFERENCES StoreFK(StoreID)
GO

ALTER TABLE GoodsSoldFKNoIndex CHECK CONSTRAINT FK_StoreID_FK
GO

Let make sure that the tables have the same number of rows

-- check that the tables have the same rows
exec sp_spaceused 'GoodsSold'
exec sp_spaceused 'GoodsSoldFKNoIndex'

name	rows	reserved	data	index_size	unused
GoodsSold	500000	4024976 KB	4000000 KB	22520 KB	2456 KB
GoodsSoldFKNoIndex	500000	4015440 KB	4000000 KB	14936 KB	504 KB

Now that we are setup, let's wipe out all the rows from the child table for a specific StoreID, the SELECT statements should return 0 rows

DELETE GoodsSoldFKNoIndex
WHERE StoreID = 507


DELETE GoodsSold
WHERE StoreID = 507


SELECT * FROM GoodsSoldFKNoIndex
WHERE StoreID = 507

SELECT * FROM  GoodsSold
WHERE StoreID = 507

Now we are getting to the interesting part, turn on Include Actual Execution Plan, run statistics IO or run this in Plan Explorer

DELETE Store
WHERE StoreID = 507


DELETE StoreFK
WHERE StoreID = 507

You will see something like this

So 75% compared to 25%, not good but doesn't look catastrophic, if you have statistics time on, you will see the following

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 16 ms.


 SQL Server Execution Times:
   CPU time = 561 ms,  elapsed time = 575 ms.


Now it looks much worse

What about statistics io?

Table 'GoodsSold'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Store'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'GoodsSoldFKNoIndex'. Scan count 1, logical reads 501373, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StoreFK'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

That is terrible....

Here is also the view from Plan Explorer, look at Est. CPU Cost and Reads

There you have it, not indexing foreign keys can have a big impact even though the child table might not have any data at all


See also When did SQL Server stop putting indexes on Foreign Key columns? by Kimberly Tripp

Want to know if your connect item is fixed? There is a twitter account for that

Did you know there is a twitter account that will tweet when a connect item is closed or resolved as fixed? I first heard about this account at the Pass Summit last week, then I promptly forgot about it. This morning I was listening to SQL Server Radio show 59, in this show the hosts Matan Yungman and Guy Glantser were talking about this twitter account. I decided to check out this account and follow this account


Here are some examples of tweets from the Closed as Fixed twitter account


Publishing dacpac to SQL Database fails when database uses memory optimized features & target database is in database pool

Publishing dacpac to SQL Database fails when database uses memory optimized features & target database is in data... https://t.co/BLuccCmk1C

— Closed as Fixed (@closedasfixed) November 1, 2016

In the actual execution plan, display Reads, writes, CPU ,duration top 3 waitypes/wait duration info for each query

In the actual execution plan, display Reads, writes, CPU ,duration top 3 waitypes/wait duration info for each que... https://t.co/3ZiGRa2RY4

— Closed as Fixed (@closedasfixed) October 29, 2016

SSMS silently reconnects to the default database when connection is lost

SSMS silently reconnects to the default database when connection is lost - Erland Sommarskog https://t.co/g9rnYVPl2h

— Closed as Fixed (@closedasfixed) October 28, 2016

The best thing about the PASS summit for me is...

This past week I attended the PASS Summit 2016. I went there with 6 co-workers and I had a terrific time. This time the weather wasn't that bad, when I left Princeton, NJ, it looked like it was going to rain for 5 days straight. It was actually sunny on a couple of occasions  :-)

One thing I really liked were the chalk talk sessions they had with the program managers of SQL Server and Azure SQL DB. People attending these sessions could ask questions and the team then answered these questions. There were also some cool announcements, I documented those here: Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection

Another thing I really liked, and this is not specific to this PASS summit, is that I got to see some of the people I befriended over the years. I chatted and hung out with some of them.

I liked all the sessions I attended, the one negative feedback I have is that there were a couple of sessions at the same time, so I had to pick one of them. This is not such a big deal because once the sessions are made available online, I will just watch the ones I really wanted to attend first.


So now back to the title of this post..... what was the best thing about this PASS summit for me? It is the fact that going to this summit reignited my love for SQL Server, and the passion for learning about SQL Server. I have laid low for the last couple of years but now I feel like a spark has been lit again,  I have so much to learn.

One of the best ways for me to learn is to blog about it. So here is what I am thinking I will do:

For December I will do a SQL advent series, this series will be about windowing functions. Everyone knows about the basic ones that were introduced in 2005, but very few people are using the stuff introduced later.

After that I will do a  series about r and SQL Server, it will take me around 3 months,  and I will probably do two posts per week.

After the r series, I want to do a series about the QueryStore.

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


So that is my plan


P.S.

If you want to read about my time at SQL Pass 2016, read these posts


SQL Pass Summit 2016... day 2
SQL Pass Summit 2016... day 3
SQL Pass Summit 2016... day 4
SQL Pass Summit 2016... day 5


You might be thinking, where is day 1?  The day 1 posts is mostly about Seattle, if you are interested in that one, here it is:  SQL Pass Summit 2016... day 1.... just looking around

Two free SQL Server books from Microsoft Press

I noticed that the Microsoft Virtual Academy has a bunch of SQL Server books available that you might enjoy. These books are free, the data science one come in pdf format, while the introducing SQL

Data Science with Microsoft SQL Server 2016
Buck Woody, Danielle Dean, Debraj GuhaThakurta, Gagan Bansal, Matt Conners, Wee-Hyong Tok


Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud
Stacia Varga, Denny Cherry, and Joseph D’Antoni





There is also a Power BI book available, if you interested in that instead

Here is what is in the 2 books I mentioned


Data Science with Microsoft SQL Server 2016

Chapter 1: Using this book 1
For the data science or R professional  1
Solution example: customer churn 3
Solution example: predictive maintenance
and the Internet of Things 4
Solution example: forecasting  5
For those new to R and data science  7
Step one: the math  8
Step two: SQL Server and Transact-SQL 11
Step three: the R programming language
and environment 14

Chapter 2: Microsoft SQL Server R
Services 16
The advantages of R on SQL Server  16
A brief overview of the SQL Server R
Services architecture  21
SQL Server R Services 21
Preparing to use SQL Server R Services 24
Installing and configuring  24
Server  25
Client 28
Making your solution operational  36
Using SQL Server R Services as a
compute context 36
Using stored procedures with R Code  40

Chapter 3: An end-to-end data science
process example 43
The data science process: an overview  44
The data science process in SQL Server R
Services: a walk-through for R and SQL
developers 47
Data and the modeling task 48
Preparing the infrastructure,
environment, and tools 51
Input data and SQLServerData object  65
Exploratory analysis 68
Data summarization 68
Data visualization 70
Creating a new feature (feature engineering)76
Using R functions 77
Using a SQL function  80
Creating and saving models  83
Using an R environment  84
Using T-SQL  86
Model consumption: scoring data with a
saved model 89
Evaluating model accuracy 95
Summary 97

Chapter 4: Building a customer churn
solution 98
Overview 99
Understanding the data  101
Building the customer churn model 105
Step-by-step 108
Summary 118


Chapter 5: Predictive maintenance and
the Internet of Things 120
What is the Internet of Things?  122
Predictive maintenance in the era of
the IoT  124
Example predictive maintenance use
cases 127
Before beginning a predictive
maintenance project 129
The data science process using SQL Server
R Services  132
Define objective 136
Identify data sources. 137
Explore data 140
Create analytics dataset 142
Create machine learning model  155
Evaluate, tune the model 157
Deploy the model  161
Summary 165

Chapter 6: Forecasting  167
Introduction to forecasting 169
Financial forecasting 169
Demand forecasting 170
Supply forecasting 171
Forecasting accuracy  171
Forecasting tools 173
Statistical models for forecasting 174
Time–series analysis 174
Time–series forecasting 179
Forecasting by using SQL Server R
Services 183
Upload data to SQL Server 183
Splitting data into training and testing 185
Training and scoring time–series
forecasting models 186
Generate accuracy metrics


Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud


This book does not have chapters 1 and 5, so don't think that you are going crazy


Chapter 2 Better security 1
Always Encrypted 1
Getting started with Always Encrypted 1
Creating a table with encrypted values 7
CREATE TABLE statement for encrypted columns 7
Migrating existing tables to Always Encrypted 9
Row-Level Security 11
Creating inline table functions 11
Creating security policies 14
Using block predicates 15
Dynamic data masking 15
Dynamic data masking of a new table 16
Dynamic data masking of an existing table 16
Understanding dynamic data masking and permissions 17
Masking encrypted values 18
Using dynamic data masking in SQL Database 18


Chapter 3 Higher availability 20
AlwaysOn Availability Groups 20
Supporting disaster recovery with basic availability groups 21
Using group Managed Service Accounts 23
Triggering failover at the database level 23
Supporting distributed transactions 24
Scaling out read workloads 25
Defining automatic failover targets 26
Reviewing the improved log transport performance 27
Windows Server 2016 Technical Preview high-availability enhancements 28
Creating workgroup clusters 28
Configuring a cloud witness 29
Using Storage Spaces Direct 32
Introducing site-aware failover clusters 32
Windows Server Failover Cluster logging 33
Performing rolling cluster operating system upgrades 33

Chapter 4 Improved database engine 35
TempDB enhancements   35
Configuring data files for TempDB 36
Eliminating specific trace flags 37
Query Store 38
Enabling Query Store 38
Understanding Query Store components 39
Reviewing information in the query store 40
Using Force Plan 42
Managing the query store 43
Tuning with the query store 44
Stretch Database 44
Understanding Stretch Database architecture 45
Security and Stretch Database 45
Identifying tables for Stretch Database 46
Configuring Stretch Database 47
Monitoring Stretch Database 48
Backup and recovery with Stretch Database 49

Chapter 6 More analytics 50
Tabular enhancements 50
Accessing more data sources with DirectQuery 51
Modeling with a DirectQuery source 51
Working with calculated tables 54
Bidirectional cross-filtering 56
Writing formulas 60
Introducing new DAX functions 60
Using variables in DAX 63
R integration 64
Installing and configuring R Services 64
Getting started with R Services 65
Using an R Model in SQL Server 74

Chapter 7 Better reporting 77
Report content types  77
Paginated report development enhancements 77
Introducing changes to paginated report authoring tools 78
Exploring new data visualizations 79
Managing parameter layout in paginated reports 84
Mobile report development  85
KPI development 85
Report access enhancements 86
Accessing reports with modern browsers 86
Viewing reports on mobile

You can get these books here: https://mva.microsoft.com/ebooks/

No, you are not getting the dark theme for SSMS anytime soon

At the SQL Server chalk talk session the question was asked when SQL Server Management Studio would get a dark theme. This is not an unreasonable question, after all, SQL Server Management Studio is built on top of the Visual Studio shell.

Here is what a light theme looks like in Visual Studio

And here is the same when I apply the dark theme

For me the light theme looks better...but what do I know
So why won't we get the dark theme for SQL Server Management Studio anytime soon?

The person answering the questions said that hears this asked all the time, he sees it on twitter, he sees it on facebook. He actually loves the dark theme in Visual Studio.

So the problem is that there are hundreds and hundreds of dialog boxes, wizards and views in SQL Server Management Studio that are not theme aware, those were written somewhere over the last 10 to 13 years.

They just finished the high DPI fix, that took hundreds if not thousand of code changes in files. So this is just basically a huge funding cost, priority wise it is really tough to justify doing this work, compared to feature requests and bug fixes.

So there you have it.... NO DARK THEME FOR YOU!!!!

Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection

There were a bunch of cool announcement at the SQL QA and chalk talk sessions at the PASS Summit

Here are the people presenting that session(not all pictured here)  Lindsey Allen  Jakub Szymaszek  Jovan Popovic  Kevin Farlee  Tomas Talius  Sunil Agarwal  Joseph Sack and Tomas Talius

Resumable Index Rebuilds

For now this will be row-store only, this will not be available for columnstore indexes for the coming release. I assume it will work like showcontig back in the day. I need nore info and have the following questions....

What happens if you stop the index rebuild and you add more data?
Are statistics update only at the end, when everything is done?
Is this just a reorg in disguise?
Is this online or offline only?


SQL Graph

Think about this like Neo4J but then in SQL Server and probably better


SQL Graph adds graph processing capabilities to SQL Server, which will help you link different pieces of connected data to help gather powerful insights and increase operational agility. Graphs are well suited for applications where relationships are important, such as fraud detection, risk management, social networks, recommendation engines, predictive analysis, dependence analysis, IoT suites, etc.
Initially, SQL Server will support CRUD graph operations and multi-hop graph navigation, and the following functionality will be available in the private preview:
Create graph objects, that is, nodes to represent entities and edges to represent relationships between any 2 given nodes. Both Nodes and Edges can have properties associated to them.
SQL language extensions to support join free, pattern matching queries for multi-hop navigation.


If you like to signup for the private preview...go here: http://aka.ms/GraphPreview



Adaptive Query Plans
I was sitting in this session, and this is more or less what I remembered

This is for starters about Multi-statement Table Valued Functions (MSTVF). The legacy cardinality estimator will use 1,  in 2014 the new new cardinality estimator will use the number 100. Both are of course wrong. The idea with Adaptive Query Plans is when the tree is build, grab the correct value from the MSTVF, inject that into the plan and pass it down the pipeline

Adjust the plan..consecutive execution, if you use too much memory and start spilling, this will be adjusted and you will stop spilling
Adaptive joins, the idea is if they think that it is appropriate that they can have a nested loop operation but there might be a chance that the estimate is wrong, they will revert to a hash join otherwise they will stick with their original nested loop join

The plan is changed in flight, there will be an intra-plan change, same thing with the adaptive join.

For starters, they will just address MSTVF, other stuff will follow later


If you like to signup for the private preview...go here: http:/aka.ms/adaptiveqppreview


SQL Server  vNext CTP1 coming next month
CTP1 for SQL Server vNext will be available next month, this is both for the Linux as well as the Windows version. This is available now, here is the link https://www.microsoft.com/evalcenter/evaluate-sql-server-vnext-ctp

See also: SQL Server 2016 SP1 released, SQL Server vNext available for download

SQL Server Standard Edition announcement on November 15th

There will be a big announcement about SQL Server Standard Edition on November 15th....  mmmm maybe the ability to use more memory? We will have to wait and see what will be announced

SQL Injection detection
The way this will work is that if they detect a query that has been SQL injected with for example DROP TABLE, they will block it and sent an email to a DL or group that you have defined explaining that the statement has been blocked



Python coming to SQL Server
It looks like in addition to R, Python will be coming to SQL Server as well




P.S.

If you want to read about my time at SQL Pass 2016, read these posts


SQL Pass Summit 2016... day 2
SQL Pass Summit 2016... day 3
SQL Pass Summit 2016... day 4
SQL Pass Summit 2016... day 5


You might be thinking, where is day 1?  The day 1 posts is mostly about Seattle, if you are interested in that one, here it is:  SQL Pass Summit 2016... day 1.... just looking around

SQL Pass Summit 2016... day 5

Sadly this was the last day of the Pass Summit, but I think I was my most favorite day. First of all it didn't rain, it turned out to be a nice day.

08:00 AM - 09:15 AM
Data Partitioning for Database Architects and Mere Mortals [AD-303]
Speaker(s):  Dmitri Korotkevitch

Data partitioning is an essential technique that simplifies database administration tasks, improves system availability and reduces the cost of the solution. Contrary to popular belief, data partitioning is not limited to partitioned tables and the Enterprise Edition of SQL Server.

This session demonstrates the data partitioning techniques available in the various editions of SQL Server. It discusses benefits, downsides and the best use-cases for partition tables, local and distributed partitioned views and stretch databases. The session will also demonstrate how data partitioning helps boost performance of systems handling a mixed workload, improves cardinality estimations with large tables, and reduces the system’s storage cost. 

Finally, the session provides a set of guidelines on how to split the data between relational and non-relational storages in OLTP and IoT systems hosted in the Cloud.

The room was packed and I had to stand in the back...it also didn't help that this ession was on level 2, it took me a while to find this room

This session was okay, I knew most of the stuff covered, below are two slides that give a summary between native partitioning and partitionmed view

09:30 AM - 10:45 AM
Chalk Talk with Azure SQL Database PM Team [AD-501-M]
Speaker(s):  Tomas Talius  Lindsey Allen

Join us in an open dialog with Microsoft Partner Architect Tomas Talius and members of the Azure SQL Database team. Don't miss this unique opportunity to ask the hard questions. Seating is limited to 50 attendees so arrive early. Chalk Talk sessions are not recorded.

This and the session after this one were my two favorite sessions. A couple of big announcement.. First CTP of the next version of SQL Server will drop next month, this includes both the Linux as well as the Windows version
SQL Injection detection
SQL Server optimizer has machine learning built in
SQL Graph (Think Neo4J) SQL Graph adds graph processing capabilities to SQL Server
Adaptive Query Plan
There will be a big announcement about SQL Server Standard Edition on November 15th....  mmmm maybe the ability to use more memory? We will have to wait and see what will be announced

I will go into some of these announcements into more details in a separate blog post. That post can be found here: Some cool SQL Server announcements SQL Graph, Adaptive Query Plan, CTP1 of SQL vNext, SQL Injection detection

Here is a picture of the team... after I posted this I got a funny comment from Adam Machanic (see below picture)

This image would make a great cover for a Christian rock album. https://t.co/W8MkhCBJeY

— Adam Machanic (@AdamMachanic) October 28, 2016

11:00 AM - 12:15 PM
Chalk Talk with the SQL Server Team [DBA-501-M]
Speaker(s):  Lindsey Allen  Jakub Szymaszek  Jovan Popovic  Kevin Farlee  Tomas Talius  Sunil Agarwal  Joseph Sack

More or less the same concept as the session before it.

02:00 PM - 03:15 PM
Go, Go, QueryStore! [DBA-210]
Speaker(s):  Gail Shaw

One of the hardest things to do in SQL is to identify the cause of a sudden degradation in performance. The DMVs don’t persist information over a restart of the instance and, unless there was already some query benchmarking (and there almost never is), answering the question of how the queries behaved last week needs a time machine. Up until now, that is. The addition of the QueryStore to SQL Server 2016 makes identifying and resolving performance regressions a breeze.

In this session we’ll take a look at what the QueryStore is and how it works, before diving into a scenario where overall performance suddenly degraded, and we’ll see why QueryStore is the best new feature in SQL Server 2016, bar none.

Gail Shaw did a good job giving an overview of the Querystore and how one might use it

03:30 PM - 04:45 PM
Microsoft R for the Architect [BIA-323-M]
Speaker(s):  Buck Woody

This was the last session, if you have never been to a Buck Woody presentation..you are missing something

In this hands-on workshop you’ll cover a series of modules that guide you from a review of the R programming environment, the Cortana Intelligence Suite Process, the Cortana Intelligence Suite Platform, to the Microsoft R platforms including: Microsoft Open R, the Microsoft R Client, Microsoft R Server, SQL Server with R Services, R in Azure ML, and HDInsight with R. Final lab is an SQL Server R Services solution.

Buck gave a nice overview of R Services and data science on the Microsoft stack

And this was taken on my way to the hotel after all the sessions were done  :-(

SQL Pass Summit 2016... day 4

I got to the conference center, nice an early, as you can see if was still not completely light after I finished my breakfast.

This is day four for me, but it's day two of the regular conference. Today started with the the second day keynote.




8:15 AM - 10 AM

Keynote

The first thing we learned about was some stuff about financials as well as the member growth. A BA day in Chicago was announced as well. 

The main keynote presenter was David DeWitt. David talked about data warehouse technologies, The three products discussed were Amazon Redshift, Snowflake, and SQL DW.

I took some pictures, I will only post 6 of those so that you get an overall idea of what was discussed

Hash Key Partitioning

Round Robin Partitioning

Table Replication

Here are the summary slides for each product

Microsoft Azure SQL Data Warehouse

Redshift

Snowflake

10:15 AM - 11:30 AM
SQL Server 2016 R Services - How Can You Make Your Apps Intelligent Today? [AD-310-M]
Speaker(s):  Umachandar Jayachandran  Nellie Gustafsson  Jarupat Jisarojito

Attend this session to get an overview of SQL Server 2016 R Services and how you can use R in T-SQL today to make your applications more intelligent and build predictive logic.

This was an introductory session to R services and how you can call R from within SQL Server. I took a picture of Nellie and UC

01:30 PM - 02:45 PM
Lightning Talks 101
Speaker(s):  Hugo Kornelis  Wayne Sheffield  Russ Thomas  Rob Volk  Jeff Renz

This session is one of four Lightning Talks sessions, each featuring five 10-minute talks on a variety of topics.

Lightning Talks 101 focuses on the AppDev track, featuring:

1. Hugo Kornelis - "Managing Execution Plans"

2. Wayne Sheffield - "How Referential Integrity Helps SQL Server Optimize Queries"

3. Russ Thomas - "GIT Your Scripts"

4. Rob Volk - "Variations on SQL_VARIANT"

5. Jeff Renz - "Do This... NOT This!"

So this was kind of an interesting idea, 5 really short sessions. The sessions were interesting, however some of them were running out of time



Somewhere around lunch time I snapped this pic

My ex boss is Scottish, so of course I had to send this to him.....



03:15 PM - 04:30 PM
Focus Group

I was invited with 9 other people to be part of a PASS focus group, this was an interesting session, we basically were giving feedback about PASS, the PASS sessions, as well about the changing roles in the IT industry

04:45 PM - 06:00 PM

UNPLUGGED: SQL Server 2016 [AD-404-M]

Speaker(s):  Scott Klein  Rohan Kumar  Lindsey Allen 

SQL Server Unplugged is your opportunity to meet Rohan Kumar and the SQL Engineering Team – the individuals who own the relational database at Microsoft. The session is hosted by Scott Klein and the show is your avenue to ask questions and talk directly to Rohan and Engineering PMs about anything and everything related to SQL Server and Azure SQL Database, and get “unplugged” scoop and insights into the inner workings of the SQL Server engineering team. No NDA material will be discussed during this session.

A picture of Mark Souza, Rohan Kumar and Lindsey Allen

This sessions was very interesting, I even asked a question. The question I asked was if in the future they can make an option so that a temporary table could be hekatonized (created as a In Memory OLTP table), The answer was that they are looking into it.

There was also something funny that happened

Person: "Will you add sharding to SQL Server?"
Rohan: "No"
Person: " Oracle just announced this yesterday"
Rohan "Ok, it's more real now"

The room erupts in laughter  :-)

They also announced that Python will come to SQL Server, right now we have R, but it looks like Python will be coming down the road as well

One more interesting  thing announced was resumable index rebuild. I am not even sure how this would work... maybe like how you can stop and resume index defragmentation??

There was also the raffle, but in my case it might as well not have been.

SQL Pass Summit 2016... day 3

Just like the other two days, day three in Seattle started early....very early. This is day three for me, but this is the first day of the general sessions. This year over 6000 people came to attend the Pass Summit.

My day started with a 6:50 AM session

6:50 AM -  8 AM

Why Use AWS for Running Your Databases
A session explaining how you can run databases on Amazon Web Services, they explained the difference between running RDS and EC2. Overall the presenter gave a nice overview of what is all available within the Amazon cloud

8:15 AM - 10 AM

Keynote

Keynote was well attended, Joseph Sirosh Corporate Vice President for the Data Group, Microsoft started the keynote (he is not pictured here)

Below are some pictures I took of the keynote, I have mostly one liners below the images. The keynote was not bad, it wasn't the best ever but I think it was better than the last couple of years.

ACID doesn't mean what you think it means

You can run queries against a variety of data source straight from SSMS

Intelligent Database Service...this means taking the intelligence out of the apps and moving it into the database, think of running R service inside the database.

This Data Warehouse on Azure will be general available in a couple of weeks

A lot of people are already looking at SQL Server running on Linux

Installing SQL Server on Linux. And just like that, by being available on Linux, Microsoft just doubled the number of platforms that SQL Server runs on :-)

The Planet of the Apps. Apps completely dominate how much data they use and also how much data they generate

Some stuff about DocumentDB

DocumentDB is Rick Grimes favorite weapon to kill walkers, 43 million walker eliminated each day....

Here is what the explosion of data looks like when you look at it as rice

A look from the back of the room...


10:15 AM - 11:30 AM

Powershell Jumpstart for SQL Server DBAs [DBA-102] presented by Michael Fal

Powershell can be intimidating. There are many challenges to using it, especially for those not familiar with its nuances. Once understood, the language can be used for effective and robust automation that brings together SQL Server with other Windows components, such as the file system or Active Directory. 

In this session, we will cover the Powershell fundamentals, how you can use Powershell itself to learn about concepts and syntax, and techniques for using Powershell with SQL Server. We will focus on core language patterns that you can use immediately, take the mystery out of the code, and help you get started writing your own PowerShell scripts. Attendees will not only acquire an understanding of Powershell, but see practical examples of how it can be used with SQL Server, along with a set of resources they can use to learn more about the language.

I attended this session with 3 of my co-workers, I saw some interesting stuff, I only played around a little with powershell and I will spend more time with it for sure.


01:30 PM - 02:45 PM
03:15 PM - 04:30 PM
Inside SQL Server In-Memory OLTP [DBA-500-HD] presented by Bob Ward

Hekaton is the original project name for In-Memory OLTP and just sounds cooler for a title name. Keeping up the tradition of deep technical “Inside” sessions at PASS, this half-day talk will take you behind the scenes and under the covers on how the In-Memory OLTP functionality works with SQL Server. 

We will cover “everything Hekaton”, including how it is integrated with the SQL Server Engine Architecture. We will explore how data is stored in memory and on disk, how I/O works, how native complied procedures are built and executed. We will also look at how Hekaton integrates with the rest of the engine, including Backup, Restore, Recovery, High-Availability, Transaction Logging, and Troubleshooting. 

Demos are a must for a half-day session like this and what would an inside session be if we didn’t bring out the Windows Debugger. As with previous “Inside…” talks I’ve presented at PASS, this session is level 500 and not for the faint of heart. So read through the docs on In-Memory OLTP and bring some extra pain reliever as we move fast and go deep. 

This session will appear as two sessions in the program guide but is not a Part I and II. It is one complete session with a small break so you should plan to attend it all to get the maximum benefit.

This was a 3 hour session and Bob also cut the break down to 15 minutes...30 minute breaks are for wimps he said  :-)

This was a very good and very fast sessions, the way you attend these sessions is that you attend it in person and then you watch the recorded session at least two more times...and only then will you be able to understand everything.

Of course Bob was very cruel, he had the Cowboys use In Memory OLTP tables and the Eagles were stuck with regular disk based tables....  Kidding aside, if you ever have a chance to attend one of Bob's session, make sure you do... who else would run the debugger against SQL Server and step through a Hekaton Stored Procedure



04:45 PM - 06:00 PM

This Batch-Mode Window Aggregate Operator Will Change Your Life! [AD-400] presented by Itzik Ben-Gan

Window functions introduced a paradigm shift in solving T-SQL querying tasks. They allow more elegant, and often more efficient, solutions compared to traditional predicate-based solutions. Still, prior to SQL Server 2016, there were a number of inefficiencies in their optimization. SQL Server 2016 introduces the new batch-mode Window Aggregate operator, which changes everything as far as performance is concerned. This session describes and demonstrates this new operator, and also provides tips and tricks to enable its use in cases you might not have expected.

Itzik is a very good presenter and he always shows cool stuff

In this session he showed how you can make SQL Server run in batch mode instead of row mode by having a dummy columnstore index. You create a dummy table by adding a where clause and you do for example  WHERE Col1 =2 AND Col1 = 1, this is a filtered index with a filter that can never be true. This session was very good


06:00 PM - 08:00 PM
Exhibitor Reception

This was the time to go see the vendors, learn about the products, meet and greet with your peers, there was also food.

 A view of the hall

 Another view of the hall

 We are sooo tired from sitting whole day long, we need a massage

Some swag I managed to grab, these shirts will be mostly to work out in.

That's all for today, tomorrow is another jam-packed day....