Tips for using SQL Server 2012 Distributed Queries Ensure that user own the table or a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server. To create the best query plans when you are using a table on a linked server that an instance of SQL Server,...
Author: Alexander Chigrik
SQL Server 2012 XML Optimization Tips
SQL Server 2012 XML Optimization Tips Consider using the XML data type. This data type is used to store XML documents in table columns or Transact-SQL variables. The XML data type can be used in variables, columns, or in stored procedure and function parameters. You can build, rebuild or drop XML indexes online. In SQL Server 2012, indexes that include...
Tips for using SQL Server 2012 backup and restore
Tips for using SQL Server 2012 backup and restore Consider using page restores. SQL Server Management Studio supports page restores in SQL Server 2012. Now you can check database pages for corruption and restore selected corrupt pages from a database backup and subsequent log backups. Use the Database Recovery Advisor to construct restore plans that implement optimal correct restore sequences....
Troubleshooting problems with SQL Server 2012 cursors
Troubleshooting problems with SQL Server 2012 cursors If you have problems with cursors in SQL Server 2012, review this troubleshooting checklist to find potential solutions. 1. Install the latest SQL Server 2012 service pack. Because many SQL Server 2012 cursor bugs were fixed in SQL Server service packs, you should install the latest SQL Server service pack. At the time...
Tips for using SQL Server 2012 data types
Tips for using SQL Server 2012 data types Use tinyint data type, if you need to store integer data from 0 through 255. The columns with tinyint data type use only one byte to store their values, in comparison with two bytes, four bytes and eight bytes used to store smallint, int and bigint values accordingly. For example, if you...
Troubleshooting problems with SQL Server 2012 Triggers
Troubleshooting problems with SQL Server 2012 Triggers If you have problems with SQL Server 2012 triggers, review this troubleshooting checklist to find potential solutions. 1. Check that you use the latest SQL Server service pack. Because many SQL Server 2012 triggers bugs were fixed in SQL Server service packs, you should install the latest SQL Server service pack. At the...
Tips for using SQL Server 2012 constraints
Tips for using SQL Server 2012 constraints Try to create a single column constraint. The more columns the constraint will have, the slowly it will work and the more stored space it will require. Use cascading referential integrity constraints instead of triggers whenever possible. For example, if you need to make cascading deletes or updates, specify the ON DELETE or...
Tips for using SQL Server 2012 cursors
Tips for using SQL Server 2012 cursors Reduce the number of rows to process in the cursor. To reduce the cursor result set use the WHERE clause in the cursor’s select statement. Use FAST_FORWARD cursors, whenever possible. The FAST_FORWARD cursors produce the least amount of overhead on SQL Server 2012, because there are read-only cursors and can only be scrolled...
Tips for using SQL Server 2012 bulk copy
Tips for using SQL Server 2012 bulk copy Use the -K option of the bcp utility. This option was first introduced in SQL Server 2012. If you specify "ReadOnly" for the -K option the read-only access to a secondary replica in an AlwaysOn availability group will be used. Consider using minimally logged bulk copy whenever possible. The minimally logged bulk...
Tips for using SQL Server 2012 triggers
Tips for using SQL Server 2012 triggers Microsoft SQL Server 2012 introduces many new useful stored procedures, functions and operators. Some actions you can perform without these new features, but usually using them provides more easy and effective way to accomplish the same goal. In this article, you can find some tips to use triggers in SQL Server 2012. Try...