SQL Server 2019 Transact-SQL optimization tips (Part 2) Try to avoid using the DISTINCT clause, whenever possible. The DISTINCT clause should only be used if you know that duplicate returned rows are a possibility. Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary. Consider using a MAXDOP option...
Tag: index
Troubleshooting problems with views in SQL Server 2017
Troubleshooting problems with views in SQL Server 2017 If you have problems with views in SQL Server 2017, review this troubleshooting checklist to find potential solutions. 1. You can get the error 1086 when you use FOR XML clause in a view. This error indicates that the FOR XML clause is invalid in views when they contain a set operator....
Troubleshooting SQL Server 2017 Indexes
Troubleshooting SQL Server 2017 Indexes If you have problems with SQL Server 2017 indexes, review this troubleshooting checklist to find potential solutions. 1. You can get the error 602. This error indicates that SQL Server could not find an entry for index in a database. This error may occur if a stored procedure references a dropped table, or metadata is...
Tips for using indexes in SQL Server 2017 (Part 1)
Tips for using indexes in SQL Server 2017 (Part 1) Create a clustered index for each table. If you create a table without clustered index, the data rows will not be stored in any particular order. This structure is called a heap. Every time data is inserted into this table, the row will be added to the end of the...
Tips for using indexes in SQL Server 2016 (Part 2)
Tips for using indexes in SQL Server 2016 (Part 2) Drop indexes that are not used. Because each index take up disk space and slow down the adding, deleting, and updating of rows, you should drop indexes that are not used. You can use Database Engine Tuning Advisor to identify indexes that are not used in your queries. Keep your...
How to identify missing SQL Server indexes
How to identify missing SQL Server indexes Microsoft SQL Server supports many useful stored procedures, functions and operators. Some actions you can perform without using these features, but usually using them provides more easy and effective way to accomplish the same goal. For example, in SQL Server 2005, Microsoft introduced Dynamic Management Views (DMVs). Of course the DMVs are supported...
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 […]
Tips for using constraints in SQL Server 2016
Tips for using constraints in SQL Server 2016 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 default constraints instead of DEFAULT objects. DEFAULT object are provided for backward compatibility and has been replaced by default definitions (default constraints) created using […]
Troubleshooting SQL Server 2014 Indexes (Part 3)
Troubleshooting SQL Server 2014 Indexes (Part 3) If you have problems with SQL Server 2014 indexes, review this troubleshooting checklist to find potential solutions. 1. You can get the error 666. This is the error message text: “The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID %I64d.” To resolve this problem, you can […]
Troubleshooting SQL Server 2014 Indexes (Part 2)
Troubleshooting SQL Server 2014 Indexes (Part 2) If you have problems with SQL Server 2014 indexes, review this troubleshooting checklist to find potential solutions. 1. The error occurs when you try to create indexed view on a partitioned table that has a clustered columnstore index. This is the error message text: “Internal Query Processor Error: The query processor could not […]