Transcript
SQL Server 2016 – T-SQL Enhancements What’s new?
About me Cláudio Silva SQL Server DBA @ Portugal Telecom by iCreateConsulting @claudioessilva http://pt.linkedin.com/in/claudioessilva/
[email protected]
2
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Agenda Features by role Administration Development Both This categorization is open to discussion
3
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Administration features (1/4) MAXDOP to specify the degree of parallelism for: DBCC CHECKDB DBCC CHECKTABLE DBCC CHECKFILEGROUP DBCC CHECKDB (AdventureWorks) With MAXDOP 250 200 150 100 50 0
221 126 88 1
2 Duration (seg)
4
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
4
Administration features (2/4) CREATE USER with ALLOW_ENCRYPTED_VALUE_MODIFICATION S Suppresses cryptographic metadata checks on the server in bulk copy operations. This enables the user to bulk copy encrypted data between tables or databases, without decrypting the data. The default is OFF. Suppressing cryptographic metadata checks means SQL Server/Azure SQL Database will allow your application to insert an arbitrary varbinary value into an encrypted column. Corruption alert!! Must see: https://blogs.msdn.microsoft.com/sqlsecurity/2016/01/07/best-practicesfor-moving-data-encrypted-with-always-encrypted/ 5
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Administration features (3/4) CREATE DATABASE SCOPED CREDENTIAL A credential can now be created at the database level (in addition to the server level credential that was previously available). AZURE Only The credential is used by the database to access to the external location anytime the database is performing an operation that requires access. Examples: PolyBase uses a database scoped credential to access Azure blob storage. SQL Database uses database scoped credentials to write extended event files to Azure blob storage. 6
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Administration features (4/5) SERVERPROPERTY – Nine new properties are added: 1. 2. 3. 4.
InstanceDefaultDataPath InstanceDefaultLogPath ProductBuild - The build number ProductBuildType - OD | GDR | NULL 5. ProductMajorVersion
7
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
6. ProductMinorVersion 7. ProductUpdateLevel – CUn | NULL 8. ProductUpdateReference - KB article for that release 9. IsPolybaseInstalled
Development features (1/7) FORMATMESSAGE In the previous versions, the FORMATMESSAGE function could use only previously added custom messages Now we can define a mask
Example:
8
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Development features (2/7) SESSION_CONTEXT sp_set_session_context procedure sp_set_session_contet @key=N’key’, @value=N’value’ [, @read_only = {1 | 0 }] Values are stored as sql_variant data type and keys are of sysname type
Includes the SESSION_CONTEXT() function is session-scoped and is reset by sp_reset_connection Is limited to 256 kb per connection Retrieve values using SESSION_CONTEXT(N’key’)
CURRENT_TRANSACTION_ID() function Before we need to use “SELECT transaction_id from sys.dm_tran_current_transaction” and user must have VIEW SERVER STATE permissions
Now any user can return the transaction ID of the current session without VIEW SERVER STATE permissions. 9
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Development features (3/7) Advanced Analytics Extensions Install the Advanced Analytics Extensions feature during SQL Server setup Allow users to execute scripts written in a supported language such as R. Transact-SQL supports R by introducing the sp_execute_external_script stored procedure, And the external scripts enabled Server Configuration Option sp_configure 'external scripts enabled', 1; 10
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Development features (4/7) Supported Features for Natively Compiled TSQL Modules
Disjunction (OR, NOT) UNION and UNION ALL SELECT DISTINCT OUTER JOIN Subqueries in SELECT statements (EXISTS, IN, scalar subqueries) Nested execution (EXECUTE) of natively compiled modules 11
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Development features (5/7) In-Memory OLTP Altering Natively Compiled Stored Procedures – Before DROP and CREATE Altering Memory-Optimized Tables - ALTER TABLE ... ADD/DROP/ALTER INDEX Full support for all Collation and Unicode Support NULLable index key columns. LOB types [varchar(max), nvarchar(max), and varbinary(max)] can be used with columns in memory-optimized tables UNIQUE indexes in memory-optimized tables. EXECUTE AS CALLER Inline table-values functions Security built-ins and increased math function support 12
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Development features (6/7) JSON ISJSON - tests whether a string contains valid JSON JSON_VALUE - extracts a scalar value from a JSON string JSON_QUERY - extracts an object or an array from a JSON string. OPEN_JSON - Table value function that parses JSON text and returns rowset view of JSON.
13
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Development features (7/7) TEMPORAL TABLES
FOR SYSTEM_TIME AS OF 'A'- Returns the version of a row valid on point A in time. If A is on the boundary of two versions, the most recent one is returned.
FOR SYSTEM_TIME FROM 'A' TO 'B' – Returns all versions of a row which were at some point in time valid in the range between A and B. Boundaries are not included.
FOR SYSTEM_TIME BETWEEN 'A' AND 'B' – Returns all versions of a row which were at some point in time valid in the range between A and B. The upper boundary is included.
FOR SYSTEM_TIME CONTAINED IN ('A','B') – Returns all versions of a row which were for its entire duration between the two points in time.
FOR SYSTEM_TIME ALL – Returns all rows from both the history table and the main table.
14
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Both (1/7) DROP IF EXISTS DROP Before to drop an Stored Procedure if it exists: IF OBJECT_ID(‘dbo.Clients’, ‘U’) IS NOT NULL DROP PROCEDURE dbo.Clients
Now: DROP TABLE IF EXISTS dbo.Clients
Supported for: AGGREGATE, ASSEMBLY, COLUMN, CONSTRAINT, DATABASE, DEFAULT, FUNCTION, INDEX, PROCEDURE, ROLE, RULE, SCHEMA, SECURITY POLICY, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, USER and VIEW
ALTER TABLE – DROP COLUMN & DROP CONSTRAINT ALTER TABLE Client DROP Name IF EXISTS 15
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Both (2/7) TRUNCATE TABLE WITH PARTITIONS TRUNCATE TABLE PartitionTable1 WITH (PARTITIONS (2, 4, 6 TO 8));
16
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Both (3/7) ALTER TABLE Adding mutiple columns ALTER COLUMN ONLINE
17
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Both (4/7) COMPRESS / DECOMPRESS Convert values into and out of the GZIP algorithm. Most suitable for compressing portions of data when archiving old data for long term storage. Per column COMPRESS Returns varbinary(max)
DECOMPRESS SELECT _id, name, surname, datemodified, CAST(DECOMPRESS(info) AS NVARCHAR(MAX)) AS info FROM player;
18
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Both (5/7) DATEDIFF_BIG AT TIME ZONE SELECT SalesOrderID, OrderDate, OrderDate AT TIME ZONE 'Pacific Standard Time' AS OrderDate_TimeZonePST FROM Sales.SalesOrderHeader;
The sys.time_zone_info view are added to support date and time interactions (https://msdn.microsoft.com/en-us/library/mt612790.aspx) 19
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Both (6/7) HASHBYTES The input length limit of 8,000 bytes for function is removed.
20
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Both (7/7) NO_PERFORMANCE_SPOOL query hint can prevent a spool operator from being added to query plans. This can improve performance when many concurrent queries are running with spool operations.
21
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
Q&A
@claudioessilva http://pt.linkedin.com/in/claudioessilva/
[email protected] 22
| 08/03/16 |
SQL Server 2016 – T-SQL Enhancements
