Tips for using data types in SQL Server 2019 (Part 2) Use Table data type instead of temporary tables whenever possible. A Table data type is a special data type that used for temporary storage of a set of rows. In comparison with the temporary tables, table variables have some benefits. For example, table variables are cleaned up automatically at...
Tag: SQL Server 2019
Tips for using data types in SQL Server 2019 (Part 1)
Tips for using data types in SQL Server 2019 (Part 1) Consider using Java data types. In SQL Server 2019 Java data types are currently supported for Input/Output data sets and Input/Output parameters. For example, you can execute a Java script by using the sp_execute_external_script system stored procedure. Use bigint data type, if you need to store integer data from...
Tips to work with SQL Server 2019 clustering
Tips to work with SQL Server 2019 clustering Consider using SQL Server 2019 Big Data Clusters. SQL Server Big Data Clusters allow you to deploy scalable clusters of SQL Server, Spark, and HDFS containers running on Kubernetes. With Big Data Clusters you can read, write, and process big data from Transact-SQL or Spark. Set the performance boost for the foreground...
Tips for using ASP.NET with SQL Server 2019
Tips for using ASP.NET with SQL Server 2019 Use the SQL Server .NET data provider to access SQL Server 2019 data using ADO.NET. Because using the SQL Server .NET data provider provides better performance in comparison with other providers, you should use the SQL Server .NET data provider whenever possible. Try to avoid synchronous calling when developing ASP.NET applications. Synchronous...
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...
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,...
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...
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....
Tips for using tempdb database in SQL Server 2019
Tips for using tempdb database in SQL Server 2019 Permit the tempdb database to automatically grow. Autogrow feature is set by default. Each time the SQL Server 2019 is started, tempdb database is recreated and reset to its default size. Automatically growing results in some performance degradation, therefore you should set a reasonable size for tempdb database and a reasonable...
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...