Tips for using SQL Server 2016 Integration Services (Part 3) Consider encrypting Integration Services packages. SQL Server 2016 Integration Services packages can be encrypted with various levels of encryption. By encrypting packages, you can protect sensitive data. If you need to modify column values, you can use the Data Conversion and Derived Column transformations. These transformations were first added in...
Tag: tips
Some tips for designing SQL Server 2016 tables (Part 2)
Some tips for designing SQL Server 2016 tables (Part 2) Try to reduce the number of columns in a table. The fewer the number of columns in a table, the less space the table will use, since more rows will fit on a single data page, and less I/O overhead will be required to access the table’s data. Consider using...
Some tips for designing SQL Server 2016 tables (Part 1)
Some tips for designing SQL Server 2016 tables (Part 1) Avoid using timestamp column as a primary key. Timestamp is a data type that exposes automatically generated, unique binary numbers within a database. Every time that a row with a timestamp column is modified or inserted, the incremented database rowversion value is inserted in the timestamp column. This property makes...
Some tips for using temporary tables in SQL Server 2016
Some tips for using temporary tables in SQL Server 2016 Consider using system-versioned temporal tables. A system-versioned temporal table is a new type of user table in SQL Server 2016 that provides correct information about stored facts at any point in time. Each temporal table consists of two tables actually, one for the current data and one for the historical...
Some tips for using SQL Server 2016 distributed queries
Some tips for using SQL Server 2016 distributed queries Try to avoid using distribution queries or minimize it using. Because distribution transactions incur more overhead than general transactions, avoid using distribution queries, whenever possible. The first steps to optimize distributed queries against a SQL Server 2016 linked server is rewriting queries so, that the most work will be performed on...
Some tips for using views in SQL Server 2016
Some tips for using views in SQL Server 2016 Use views to enhance security and conceal underlying data objects. For example, you can give to users the permission to access a view, which contain a restricted set of the columns and data, not allow selecting or updating the underlying data objects. By using views, the permission management could also be...
Some tips for using data types in SQL Server 2016
Some tips for using data types in SQL Server 2016 Use Date/Time data types to store date/time information separately. In SQL Server 2016 you can store the date and time information separately. For example, if you need to work with date data only, use the Date data type instead of datetime or smalldatetime to optimize storage space and simplify date […]
Some tips for SQL Server 2016 clustering
Some tips for SQL Server 2016 clustering Avoid running other software on the SQL Server 2016 cluster nodes. To increase SQL Server 2016 performance, SQL Server clusters should be dedicated to SQL Server only. Consider using Distributed Availability Groups (DAG) if you have two availability groups residing on different Windows Server Failover Clusters (WSFC). DAG enables you to associate two […]
Some tips for using table hints in SQL Server 2016
Some tips for using table hints in SQL Server 2016 Use the INSERT … SELECT statement with the TABLOCK hint. In SQL Server 2016, the insert in an INSERT … SELECT statement is multi-threaded or can have a parallel plan. To get a parallel plan, you can use the compatibility level 130 and the INSERT … SELECT statement must use […]
Some tips for using full-text search in SQL Server 2016
Some tips for using full-text search in SQL Server 2016 Use a full-text query instead of the LIKE Transact-SQL predicate if you need to query formatted binary data or query a large amount of unstructured text data. A full-text query against millions of rows of text data can take only seconds; whereas a LIKE query against the same data can […]