Category - SQL Server 2016

SQL Server 2019 CE, Local Variables and Nullable Columns

In this article, I will demonstrate a small change in the Cardinality Estimator component under the latest compatibility level – 150 (SQL Server 2019).

Let’s first create and populate a sample table:

DROP TABLE IF EXISTS dbo.T;
CREATE TABLE dbo.T(
id INT IDENTITY(1,1) NOT NULL,
c1 INT NOT NULL,
c2 INT NULL,
c3 CHAR(5) DEFAULT 'test',
CONSTRAINT PK_T PRIMARY KEY CLUSTERED (id)
);
GO
CREATE INDEX ix1 ON dbo.T(c1);
GO
CREATE INDEX ix2 ON dbo.T(c2);
GO
INSERT INTO dbo.T(c1)
SELECT 1 + ABS(CHECKSUM(NEWID())) % 10000
FROM dbo.GetNums(1000000);
GO

--update the c2 column to not null for about 5% rows
UPDATE dbo.T SET c2 = c1 WHERE c1 >= 9500;
GO

Ensure that the database runs under the compatibility level 140 and consider the following simple query with a local variable:

ALTER DATABASE New SET COMPATIBILITY_LEVEL = 140;
GO
DECLARE @v INT = 600000;
SELECT * FROM dbo.T WHERE c1 = @v;
Here is its execution plan:

e00
This is a very known situation – an estimation for an equality predicate with local variable. The estimated number of rows is calculated as the All density attribute from the stats density vector multiplied by the Rows from the stats header.

e02


SELECT 0.00010002*1000000;
--100.02000000

The c1 column does not allow NULLs. What would be the estimation for a nullable column? The c2 column in our table is nullable. Let’s have a check:

DECLARE @v INT = 600000;
SELECT * FROM dbo.T WHERE c2 = @v;


e01

The estimation is significantly higher and even lead to a plan with Clustered Index Scan! However, it follows the same formula for the estimation. Let’s have a look at the statistics object:

e03

By using the same formula, the estimated number of rows is:

SELECT 0.001992032*1000000
--1992.032000000

This is exactly what we see in the execution plan. OK, that was CL 140, let’s see how SQL Server 2019 handles this simple case.

When we switch to CL 150, the plan and estimations for the c1 column (non-nullable) are the same. However, the estimation for the nullable column is changed!

ALTER DATABASE New SET COMPATIBILITY_LEVEL = 150;
GO
DECLARE @v INT = 600000;
SELECT * FROM dbo.T WHERE c2 = @v;
Here is the plan:

e04

This time, the estimation is not so high, and we are getting more appropriated plan, based on the Nested Loop join operator. But, where is 104.397 coming from? Let’s look at the statistics again:

e05

Since the column is nullable, CE apply the All density against non-nullable rows only. Thus, the formula seems to be All density x (RowsEQ_ROWS for RANGE_HI_KEY = NULL).

SELECT 0.001992032*(1000000-947697);
--104.189249696

Well, it is not exactly 104.397, but very, very close to it.  I did not find official documentation for this, therefore cannot confirm that the mentioned calculation is always correct. However, this new behavior makes more sense: the average value from the stats density vector refers to non-nullable set of values, and it’s wrong to multiply it with all rows.

Funfact – the behavior is actually not new at all – that’s exactly how the old CE (under the compatibility levels <=110) handles this case.

I would like to thank my colleague Vladimir Đorđević for making me aware of this behavior.

Thanks for reading.

Quo vadis iliti j*** te fičer koji sve radi umesto mene a ja moram ništa

Ovaj post ima previše psovki da bi bio napisan na engleskom jeziku, al računam da nije malo i to što će dopreti do onih koji se služe srpskohrvatskim jezikom.

G1Postalo je gotovo nesnosno kako predstavnici mnogog proizvođača softvera (ne samo iz ogranka marketinga) najnovije fičere, alate i servise prodaju koristeći sintagmu “to sve radi za vas ispod haube,  ne morate kurcem mrdnuti, ne morate ništa znati o tome, ne morate programirati, sve je transparentno, dva-tri klika, malo drag and drop i fičer će sve uradit za vas”. Tome se pridružuje sve više predavača i tzv. eksperata ističući u prvi plan da je neki fičer do jaja jer njegov korisnik gotovo ništa ne mora da zna i ne mora više da brine o sistemu. Doduše i ne mora da brine, al sve dok ga sistem kurčinom ne tresne po čelu.

Toliko je novih stvari koje ne znaš, da prosto ne znaš gde i kako da počneš da ih učiš, a vremena nikad dovoljno. I onda ti neko kaže e ovo uopšte ne moraš da znaš, to radi samo, naravno da ćeš to malo vremena da posvetiš nečemu drugom, potpuno se oslanjajući na taj moćni fičr. Dok god ta stvar radi ono što korisnik očekuje i to radi dovoljno brzo, nema nikakvih problema. Dođe li do neželjenih efekata ili je stvar prespora onda kompania od tebe očekuje da nešto preduzmeš. A ako ne znaš kako to radi, ako ne razumeš concept, nemaš workaround, nisi se pripremio za tu situaciju onda možeš kurac.

Nove softverske verzije, novi alati samo su oruđe koje vam može pomoći da dođete do proizvodnog cilja: bržeg razvoja, skalabilnog sistema, boljih performansi. Ali to su samo jebeni alati. Oni ne rade za vas ili umesto vas; vi njih koristite i vi treba da radite sa njima. Treba dobro da ih poznajete, da znate šta i koliko mogu i možda još bitnije šta ne mogu. I valja gvirnuti poda haubu da vidiš kako to sve radi da bi znao da li je to za tebe.

Kad javno kažeš ovaj fičer je do jaja, biće ti query do 100 puta brži, a ne kažeš kad neće (a većini jebeno neće) i da može pod nekim uslovima i da bude i sporiji time si uljuljkao posetioca svoje sesije i potencijalno ga naveo na lošu poslovnu odluku. On obično nema vremena da nakon konferencije dalje razrađuje tu temu – bio je, čuo od eksperta i uzeće to kao istinu. I kad se suoči sa teškoćama u vezi sa tim fičerom, vendor ili spiker će bu obećati noviji, brži, jači i bolji.

Zato treba ljude podsećati na to da je baš lepo imati super fičere i alate, i da nesumljivo u njih treba investirati, ali da je još uvek jebeno najbitnije znanje i da treba da što više uče i znaju i da se prema svemu odnose kritički, a ne da se poput šiparica primaju na pojedine stvari pa se onda u javnim blog-postovima razočaravaju. I da treba da investiraju u znanje.

 

Altering an Indexed Text Column

In this post, I am going to demonstrate how increasing the size of a text column with an Hastindex on it can lead to serious troubles without warning. I tested it two instances of SQL Server: SQL Server 2016 and 2017, but the same behavior should be expected with older versions; only the maximal allowed column size would be different (900 instead of 1.700 bytes).

 

Use the following code to create a sample table and populate it with 1M rows:

DROP TABLE IF EXISTS dbo.AlterColumnTest;
CREATE TABLE dbo.AlterColumnTest(
id INT NOT NULL,
descr NVARCHAR(500) NOT NULL DEFAULT 'test',
CONSTRAINT PK_AlterColumnTest PRIMARY KEY CLUSTERED (id ASC)
);
GO
INSERT INTO dbo.AlterColumnTest(id)
SELECT n
FROM dbo.GetNums(1000000)
GO
CREATE INDEX ix1 ON dbo.AlterColumnTest(descr)
GO

As you can see, there is a non-clustered index on the descr column. From SQL Server 2016 the maximum size for index keys with nonclustered indexes has been increased to 1.700 bytes. That means that the size of the column cannot exceed value of 850. However, when you try to increased it to a value grater than 850, this will be accepted. The following command will be successfully executed:

ALTER TABLE dbo.AlterColumnTest ALTER COLUMN descr NVARCHAR(1000) NOT NULL;
GO 
Commands completed successfully.

 

The command will be executed instantly since it updates only meta-data; existing rows are not touched at all. And there is no warning about the potential exceeding of allowed index key size!

When you try to update a row so that it contains a 850 character long text value, the update will work:

UPDATE dbo.AlterColumnTest SET descr = REPLICATE(N'a', 850) WHERE id = 1;

(1 row affected)

(1 row affected)

However, if text is longer than this, you will get an error:

UPDATE dbo.AlterColumnTest SET descr = REPLICATE(N'a', 851) WHERE id = 1;

Msg 1946, Level 16, State 3, Line 34
Operation failed. The index entry of length 1702 bytes for the index 'ix1' exceeds the maximum length of 1700 bytes for nonclustered indexes.

OK, you have got an exception, only after we tried to store data longer than the limit and now you have to alter the text column to the allowed size (850):

ALTER TABLE dbo.AlterColumnTest ALTER COLUMN descr NVARCHAR(850) NOT NULL;

Instead of decreasing the column size and an intently operation, you will get the following error:

Msg 5074, Level 16, State 1, Line 42
The index 'ix1' is dependent on column 'descr'.
Msg 4922, Level 16, State 9, Line 42
ALTER TABLE ALTER COLUMN descr failed because one or more objects access this column.

To proceed with this action, you need to drop the underlined index, alter the column size and rebuild the index!

DROP INDEX IX1 ON dbo.AlterColumnTest;
GO
ALTER TABLE dbo.AlterColumnTest ALTER COLUMN descr NVARCHAR(850) NOT NULL WITH (ONLINE = ON);
GO
CREATE INDEX IX1 ON dbo.AlterColumnTest(descr) WITH (ONLINE = ON);
GO

For large tables, this action can take some time and during it, you will have no index on that column. The ALTER TABLE statement this time is not a meta-data operation; since the column size is decreased, the action affect all rows of the table, although there is no single rows with a column longer than the new length! For the ALTER statement, you can choose between the offline and online operation. In the first case, it will be faster, but the table will be locked and unavailable for querying and modifications; an online operation will let table be available for these actions, but it will take more time and resources (a new table will be created in shadow and will replace the original one).

This is definitely not something that you have expected after a successful initial command which increased the column size to 1.000 characters. At least a warning should be shown in this case.

Thanks for reading.

M.

SQL Server 2016 Developer’s Guide

Together with Dejan Sarka (blog | twitter) and William Durkin (blog | twitter) I have finished writing on the SQL Server 2016 Developer’s Guide book.

B05694The book is available at the Packt Publishing portal https://www.packtpub.com/big-data-and-business-intelligence/sql-server-2016-developer-guide and Amazon https://www.amazon.com/SQL-Server-2016-Developers-Guide-ebook/dp/B01MS5L01Q.

Here is a very brief description of all chapters of the book.
Chapter 1: Introduction to SQL Server 2016

Many new improvements have been made to SQL Server 2016. In this chapter we’ll cover very briefly most important features and enhancements, not only those for developers. We want to show the whole picture and to point where the things are moving on. Although this book is for developers and covers developer related features, it is pretty clear that in 5-10 years all developers need to deal with some features which are currently developed and are under development within the business intelligence scope. Therefore, it is important to show tendencies so that everyone could consider to embrace some of them. We will also present how Microsoft plan to deliver services and products in the future.

Chapter 2: Review of SQL Server Features for Developers
A brief recapitulation of the features available for developers in previous versions of SQL Server in this chapter serves as a foundation for explanation of the many new features in SQL Server 2016. Some best practices are covered as well.

Chapter 3: SQL Server Tools
Understanding changes in the release management of SQL Server tools and exploring small and handy enhancements in SQL Server Management Studio (SSMS). Using new fancy feature live query statistics. Exploring SQL Server Data Tools (SSDT) and its support for continuous integration and deployment automation.

Chapter 4: Transact-SQL Enhancements
Exploring Transact-SQL enhancements: new functions and syntax extensions, discovering ALTER TABLE improvements for online operations and considering new query hints for query tuning.

Chapter 5: JSON Support
Supporting JSON data was the most requested feature on the Microsoft SQL Server connect site. This feature has been finally added in SQL Server 2016. Having JSON support built into SQL Server should make it easier for applications to exchange JSON data with SQL Server.

Chapter 6: Stretch Database
Understanding how to migrate historical or less accessed data transparently and securely to the Microsoft Azure by using Stretch Database (Stretch DB) feature.

Chapter 7: Temporal Tables
SQL Server 2016 introduces support for system-versioned temporal tables based on the SQL:2011 standard. We’ll explain how this implemented in SQL Server is and demonstrates some use cases for it (time-travel application). We’ll also discuss what is still missing for the full Temporal Data support in SQL Server

Chapter 8: Tightening the Security
SQL Server 2016 introduces three new security features. With Always Encrypted SQL Server finally enables full data encryption, so that no tools or persons regardless their database and server permissions can read encrypted data except the client application with an appropriate key. Row-level security on the other side restricts which data in a table can be seen by specific user. This is very useful in multi-tenant environments where you usually want to avoid data-reading intersection between different customers. Dynamic data masking is a soft feature that limits sensitive data exposure by masking it to non-privileged users.

Chapter 9: Query Store
Understanding how to use Query Store to troubleshoot and fix performance problems that are related to execution plan changes. Although this is primarily DBA feature, it can be also very useful for developers to identify most expensive and queries with regressed (or heavily changed) execution plans. It will also help them to analyze and become more familiar with the workload patterns generated by their applications and services.

Chapter 10: Columnstore Indexes
Columnar storage was first added to SQL Server in version 2012. It included nonclustered columnstore indexes (NCCI) only. Clustered columnstore indexes (CCI) were added in version 2014. In this chapter, the readers revise the columnar storage and then explore huge improvements for columnstore indexes in SQL Server 2016: updateable nonclustered columnstore indexes, columnstore indexes on in-memory tables, and many other new features for operational analytics.

Chapter 11: Introducing SQL Server In-Memory OLTP
Understanding in SQL Server 2014 introduced, but still underused In-Memory database engine that provides significant performance gains for OLTP workloads.

Chapter 12: In-Memory OLTP Improvements in SQL Server 2016
With the new SQL Server 2016 release many of the issues that might block the adoption of In-Memory OLTP have been eliminated: supporting foreign keys, check and unique constraints, parallelism, recommended maximum size of In-Memory tables has been increased to 2 TB, tables, stored procedures and indexes can be altered… Also Transact-SQL constructs support for by In-Memory tables and compiled stored procedures has been extended. All these improvements extend the number of potential use cases and allow the implementation with less development effort and risk.

Chapter 13: Supporting R in SQL Server
SQL Server R Services combines the power and flexibility of the open source R language with enterprise-level tools for data storage and management, workflow development, and reporting and visualization. This chapter introduces the R Services and the R language.

Chapter 14: Data Exploration and Predictive Modeling with R in SQL Server
Just knowing that you can use the R language inside SQL Server does not help much. After R and R support in SQL Server were introduced in the previous chapter, this chapter shows how you can use R for advanced data exploration and manipulation and for statistical analysis and predictive modeling way beyond the possibilities when using T-SQL language only.

I hope, you’ll enjoy reading it.

DROP IF EXISTS aka D.I.E.

In my first post in the series “SQL Server 2016 Features for Developers” I will write about probably the smallest one – conditional DROP statement or DROP IF EXISTS.

grizzly bearWith a conditional DROP statement, you can avoid getting an exception if the object you want to drop does not exist. If a table you want to drop is already removed or it was not created at all, the DROP TABLE statement will fail with the following error message:

Msg 3701, Level 11, State 5, Line 4
Cannot drop the table 'dbo.MyTable', because it does not exist or you do not have permission.

SQL Server 2016 introduces conditional DROP statement for most of the database objects. Conditional DROP statement is a DROP statement extended with the IF EXISTS part. Let us repeat the above command with this extended syntax:

DROP TABLE IF EXISTS dbo.MyTable;

You can execute this statement any number of times and you will get no error. To achieve this prior to SQL Server 2016 we had to check the existence of the object before we remove it, like in this code:

IF OBJECT_ID('dbo.MyTable ','U') IS NOT NULL
DROP TABLE dbo.MyTable;

So, one code line more and it is error prone – you have to write the name of the object twice. It’s not a big deal, but this new form is shorter and not error prone.

Conditional DROP statement is supported for most of the objects, but not for all of them. For instance, you cannot use it to conditionally remove a partitioned function. The full list of supported objects you can find in BOL.

How does IF EXISTS work? It simply suppresses the error message. This is exactly what we need if the reason for the error is non-existence of the object, but if user who wants to drop the object does not have appropriate permission, we would expect an error message. However, the command is executed successfully and the caller does not get an error regardless of the object existence and user permissions! To demonstrate this let’s create first a test login and database user in the new SQL Server sample database WideWorldImporters. In addition we will create a single column test table Z90.

USE WideWorldImporters;
--Create a test login and database user
CREATE LOGIN TestConditionalDrop WITH PASSWORD = 'A$§h8_098hkdg&!';
CREATE USER TestConditionalDrop FOR LOGIN TestConditionalDrop;
--Create test table
CREATE TABLE dbo.Z90(id INT);

Now we’ll try to drop newly created and a non-exiting table with this new account. This account does not have even SELECT permissions. Let’s check this:

--Switch to user TestConditionalDrop
EXECUTE AS USER = 'TestConditionalDrop';
--try to read the table Z90
SELECT * FROM dbo.Z90;

Let’s try now to drop a non-existing table dbo.Z91 and the existing dbo.Z90:

--try to drop a non-existing table
DROP TABLE dbo.Z91;
/*
Msg 3701, Level 11, State 5, Line 31
Cannot drop the table 'dbo.Z91', because it does not exist or you do not have permission.*/
--try DROP an existing table
DROP TABLE dbo.Z90;
/*
Msg 3701, Level 14, State 20, Line 37
Cannot drop the table 'Z90', because it does not exist or you do not have permission.*/

So, in both cases we got the same message, because the newly created account does not have permissions to drop tables. However, when we try the same with the conditional DROP statement:
--Ensure that statement is executed in the context of new user
SELECT SUSER_NAME();
--drop a non-existing table
DROP TABLE IF EXISTS dbo.Z91;
--drop an existing table
DROP TABLE IF EXISTS dbo.Z90;
--back to the original user
REVERT;
--check if the table dbo.Z9 exists
SELECT OBJECT_ID('dbo.Z90','U');
--1351675863

No error messages at all! DROP IF EXISTS simply suppressed the error message and the command caller can think that the action has been executed successfully. Of course, the existing table was not removed, due to insufficient permissions, but this information should be shared with the command caller and I see this behavior as a bug. It is OK to suppress the error message when the object does not exist, but if permissions were the problem, the exception should be raised.

When we would ignore a previous bug, this enhancement is handy; it helps you abbreviate your code, and it will be intensive used by… consultants, trainers and conference speakers. They usually create database objects to demonstrate some feature, code technique or behavior and then drop them from the system. And they do this again and again.

However, conditional DROP statement will not be used so often in the production systems. How often do we remove database objects from SQL Server? Very rare, right? When we perform some cleanup or remove intermediate database objects. In most of the cases we add new or change existing objects. Therefore, I would like to see similar implementation for the object’s creation or updating. To extend Transact-SQL syntax with, for instance, CREATE OR ALTER or CREATE or REPLACE command.  This would more important for script deployment then DROP statement extensions. I hope, we’ll see it in the next SQL Server version.

Thanks for reading.

SQL Server 2016 CTP2 First Impressions

SQL 2016As most of you probably know on 27th May Microsoft announced the first public Community Technology Preview (CTP2) for SQL Server 2016. The version can be downloaded here: https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

This versions brings a number of new features and a lot of enhancements for existing ones.

Since we in bwin keep up-to-date on the latest technology releases we already tried the new version and want to share our impressions with you and the public SQL Server community.

Therefore we are inviting you to join the event SQL Server 2016 CTP2 First Impressions on Thursday, 25th June at 14.00 in the bwin Event Zone, Marxergasse 1B, 1030 Vienna.

In our sessions we’ll present and demonstrate new features and enhancements in SQL Server 2016. Here is the agenda:

  • 14:00 Doors opening
  • 14:15 Keynote or few words from the Event Organizer
  • 14:30 First Session by Milos Radivojevic: SQL Server 2016 – What’s New for Developers?
    • Temporal Tables in SQL Server 2016
    • JSON Support in SQL Server 2016
    • In-Memory OLTP from Developer Perspective
    • Query Store and Live Query Statistics
    • Dynamic Data Masking
  • 16:00 Break
  • 16:30 Second Session by Rick Kutschera: SQL Server 2016 – What’s New for Database Administrators?
    • Columnstore Indexes in SQL Server 2016
    • In-Memory OLTP from DBAs Perspective
    • Polybase
    • Row-Level Security and Security Enhancements
    • High Availability and Other Enhancements
  • 18:00 End of the program and get together

Please use this link https://www.eventbrite.de/e/pass-austria-sql-server-community-special-sql-server-2016-registrierung-17368085411 to register for the event.

Thank you and see you there!