Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 14 of 212

4 C H A P T E R 1 | Faster queries and then re-create it with the changes. For better performance, you can now natively compile objects besides stored procedures and support more T-SQL constructs in your code. ALTER TABLE statement Beginning in SQL Server 2016, you can use the ALTER TABLE statement to change the table definition and indexes on memory-optimized tables, as shown in Example 1-2. You can add or drop columns, constraints, or indexes or change the bucket count of an index. Note You cannot use CREATE INDEX, DROP INDEX, or ALTER INDEX statements with memory- optimized tables. The only way to add, remove, or change indexes for these tables is to use the ALTER TABLE statement. Example 1-2: Using the ALTER TABLE statement with a memory-optimized table -- Add a column and an index ALTER TABLE dbo.ShoppingCart ADD Quantity INT NULL, INDEX ix_CreatedDate(CreatedDate);-- Alter an index by changing the bucket count ALTER TABLE dbo.ShoppingCart ALTER INDEX ix_UserId REBUILD WITH ( BUCKET_COUNT = 2000000); -- Drop an index ALTER TABLE dbo.ShoppingCart DROP INDEX ix_CreatedDate; In most cases, the ALTER TABLE statement runs in parallel and writes only metadata changes to the transaction log as a performance optimization. However, the following single-threaded operations continue to require writing the entire altered table to the transaction log: Adding or altering a column with a LOB data type. Adding or dropping a columnstore index. Any operation affecting an off-row column—a column with a data type of char, nchar, varchar, nvarchar, binary, or varbinary that does not fit in the 8,060-byte row—except if the operation lengthens a column that is already off-row. Natively compiled modules Compiling tables and stored procedures as native code was introduced in SQL Server 2014 to enable more efficient query execution and data access for memory-optimized tables. New to SQL Server 2016 is the ability to natively compile other objects, such as scalar user-defined functions (UDFs), inline table-valued functions (TVFs), and triggers. In addition to the support of new objects, the query surface area for native compilation is expanded and applies not only to natively compiled stored procedures but to all natively compiled modules. You can also alter a natively compiled module rather than drop and re-create it when a change to its functionality is necessary. Much like you do when creating a natively compiled stored procedure, you must include the NATIVE_COMPILATION and SCHEMABINDING options in the WITH clause when you create a natively compiled scalar UDF, as shown in Example 1-3. Another new capability in SQL Server 2016 is support for LOBs as an argument to built-in string functions and LOB as a return type, as shown in the example.

Articles in this issue

Archives of this issue

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