Author: Alexander Chigrik

Pro Members SQL Server Standard Members

Tips for using Very Large Databases in SQL Server 2019

Tips for using Very Large Databases in SQL Server 2019 Avoid using SQL Server Management Studio to maintain Very Large Databases. Because using SQL Server Management Studio is very resource expensive, use stored procedures and Transact-SQL statements, in this case. For heavily accessed table with text/image columns, place this table in one filegroup and place text/image columns in a different…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale, and Standard Member Monthly - Fall Sale members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using linked servers in SQL Server 2019

Tips for using linked servers in SQL Server 2019 Rewrite remote 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 to find out which parts of the remote query are performing on the remote server…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale, and Standard Member Monthly - Fall Sale members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using indexed views in SQL Server 2019

Tips for using indexed views in SQL Server 2019 Avoid creating indexes on a view when the queries that use the view don’t contain JOINs or aggregations. In this case, the queries will not take advantages of the view’s indexes and the queries performance will be the same. Don’t create index on column(s) which values has low selectivity. For example,…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale, and Standard Member Monthly - Fall Sale members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using User-Defined Functions in SQL Server 2019

Tips for using User-Defined Functions in SQL Server 2019 Try to use Scalar UDF Inlining feature. SQL Server 2019 supports Scalar UDF Inlining. This feature automatically transforms scalar UDFs into relational expressions and embeds them in the calling SQL query. This transformation improves the performance of workloads that take advantage of scalar UDFs. A Scalar UDF can be inline when…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale, and Standard Member Monthly - Fall Sale members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using full-text search in SQL Server 2019

Tips for using full-text search in SQL Server 2019 Make full-text index population during periods of low database access. Because full-text index population takes some time, these updates should be scheduled during CPU idle time and slow production periods. Reduce the full-text unique key size. To create a full-text index, the table to be indexed must have a unique index…....

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale, and Standard Member Monthly - Fall Sale members only.
Log In Register
Pro Members SQL Server Standard Members

Some tips for using SQL Server 2019 cursors

Some tips for using SQL Server 2019 cursors Try to avoid using SQL Server cursors, whenever possible. SQL Server cursors can results in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations. Use READ ONLY cursors, whenever possible, instead of updatable cursors. Because using cursors can…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale, and Standard Member Monthly - Fall Sale members only.
Log In Register
Pro Members SQL Server Standard Members

Some tips for using SQL Server 2019 triggers

Some tips for using SQL Server 2019 triggers Try to minimize the number of rows affected in a trigger. The more number of rows affected in a trigger, the more time a trigger takes to run. So, try to reduce the number of rows affected in a trigger. Consider disabling trigger’s recursion. Triggers are said to be recursive when a…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale, and Standard Member Monthly - Fall Sale members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using constraints in SQL Server 2019

Tips for using constraints in SQL Server 2019 Avoid using CHECK_CONSTRAINTS hint with bulk copy program. Using this hint can significantly degrade performance of the bulk copy operation, because for each row loaded the CHECK constraints defined on the destination table will be executed. Without the CHECK_CONSTRAINTS hint, any CHECK constraints will be ignored. Consider using the NOT FOR REPLICATION…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale, and Standard Member Monthly - Fall Sale members only.
Log In Register
Pro Members SQL Server Standard Members

Tips for using backup and restore in SQL Server 2019

Tips for using backup and restore in SQL Server 2019 Use separate storage for the database backups. Ensure that you place your database backups on a separate physical location or device from the database files. You can use backup/restore of memory-optimized files on Azure Storage. In SQL Server 2019 memory-optimized filegroup files can be stored on Azure Storage. Backup/Restore of…...

This content is for Pro, Pro Member, Pro Member Annual - Fall Sale, Pro Member Monthly - Fall Sale, Standard, Standard Member, Standard Member Annual - Fall Sale, and Standard Member Monthly - Fall Sale members only.
Log In Register