Tips for using XML in SQL Server 2014 Use SQLXML instead of Native XML Web Services. Beginning in SQL Server 2014, the Native XML Web Services is removed. So, you can no longer use CREATE ENDPOINT or ALTER ENDPOINT to add or modify SOAP/HTTP endpoints. Consider using the RAW mode of FOR XML queries, instead of AUTO or EXPLICIT modes....
Tag: SQL Server 2014
Tips for using SQL Server 2014 Distributed Queries
Tips for using SQL Server 2014 Distributed Queries One of the first steps to optimize distributed queries against a SQL Server 2014 linked server is rewriting queries so, that the most work will be performed on the remote server, not the local server. You can run the remote query from Management Studio and take a look at the query plan...
Tips for using SQL Server 2014 cursors
Tips for using SQL Server 2014 cursors Try to avoid using insensitive, static and keyset cursors, whenever possible. These types of cursor produce the largest amount of overhead on SQL Server 2014, because they cause a temporary table to be created in TEMPDB, which results in some performance degradation. Use FAST_FORWARD cursors, whenever possible. The FAST_FORWARD cursors produce the least...
Microsoft SQL Server 2014: Database Engine key new features – (Part 3)
Microsoft SQL Server 2014: Database Engine key new features – (Part 3) Author: Basit A. Farooq This is the third part of my article series on Microsoft SQL Server 2014: Database Engine key new feature. In this part, I will talk about following two SQL Server 2014 features: Encryption for Backups and Resource Governor Enhancements for Physical I/O control. Encryption...
Tips for using SQL Server 2014 triggers
Tips for using SQL Server 2014 triggers Try to minimize the DML trigger’s code size. Triggers fire during INSERT, UPDATE, or DELETE statements, so the more code that runs in the trigger, the slower each INSERT, UPDATE, and DELETE that fires it will be. Use cascading referential integrity constraints instead of triggers whenever possible. For example, if you need to...
Microsoft SQL Server 2014: Database Engine key new features – (Part 2)
Microsoft SQL Server 2014: Database Engine key new features – (Part 2) Author: Basit A. Farooq This is the second part of my article series on Microsoft SQL Server 2014: Database Engine key new feature. In this part, I will talk about SQL Server 2014 columnstore indexes. Updateable clustered columnstore index With the release of SQL Server 2012, Microsoft introduced...
Microsoft SQL Server 2014: Database Engine key new features – (Part 1)
Microsoft SQL Server 2014: Database Engine key new features – (Part 1) Author: Basit A. Farooq Earlier this year, Microsoft released SQL Server 2014. Like previous releases of SQL Server, Microsoft further enhances the SQL Server Database Engine. Microsoft not only improved existing SQL Server Database Engine features, but also introduces many new Database Engine features, including new in-memory OLTP...
Guidelines and Best Practices for developing and implementing a Reporting Solution with SQL Server 2014 – (Part 2)
Guidelines and Best Practices for developing and implementing a Reporting Solution with SQL Server 2014 – (Part 2) Author: Basit A. Farooq Editor’s Note: In the first of this three part article series, I discussed the considerations for creating reports. In this part, I will talk about the guidelines and best practices for implementing data sources and report layouts. Guidelines...