Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 15 of 212

5 C H A P T E R 1 | Faster queries Example 1-3: Using LOBs in a natively compiled scalar UDF CREATE FUNCTION dbo.TRIM_CONCAT (@string1 NVARCHAR(MAX), @string2 NVARCHAR(MAX)) RETURNS NVARCHAR(MAX) WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English') RETURN RTRIM(@string1) + LTRIM(@string2); END; Note More details about working with natively compiled scalar UDFs is available at "Scalar Defined Functions for In-Memory OLTP," https://msdn.microsoft.com/en-us/library/dn935012.aspx. SQL Server 2016 also supports the following capabilities for natively compiled modules: Use the OUTPUT clause with the INSERT, UPDATE, and DELETE statements in a natively compiled stored procedure. Omit the EXECUTE AS clause when you want EXECUTE AS CALLER as the execution context of a natively compiled module. You include an EXECUTE AS clause in a module only if you need to enforce a different execution context. Use any SQL Server collation in an expression. Use any of the built-in math and security functions. Use SELECT DISTINCT in the SELECT clause. Use OUTER JOIN, EXISTS, IN, and scalar subqueries in the FROM clause. Use OR or NOT in the WHERE clause. Combine result sets by using the UNION or UNION ALL operations. Configure nested execution of natively compiled modules by using the EXECUTE statement. Managing memory-optimized tables Statistics for memory-optimized tables are updated automatically as long as database compatibility is set to 130. Statistics are updated by data sampling rather than by a full scan. If you created a memory-optimized table prior to SQL Server 2016 CTP 3.3 or in SQL Server 2014, you must run the script shown in Example 1-4 once to add the automatic-update functionality. Example 1-4: Adding automatic update of statistics to a memory-optimized table -- Enable automatic update on the database first, if necessary ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS ON; GO -- Set the compatibility level, if necessary ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130; GO -- DECLARE @sql NVARCHAR(MAX) = N''; SELECT

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016