Geek Answers Handbook

SQL relationships and indexes

I have an MS SQL server application where I defined my relationships and primary keys.

However, I need to further define indexes of relationship fields that are sometimes not used in joins and as part of the where clause?

I am working on the assumption that the definition of a relationship creates an index that the sql server can use.

+3
source share
7 answers

No indexes will be automatically created with foreign key constraints. But unique and primary constraints will create them.

Creating indexes for the queries you use, whether in joins or in a WHERE clause, is the way to go.

+4
source

Some very thick books have been written on this subject!

Here are some thumb ruins: -

Do not put indexing (except PK) on any table with <1000 rows.

Otherwise, index all of your FKs.

Examine your SQL and find where clauses that will most reduce your result sets and index this columun.

eg. Given:

  SELECT OWNER FROM CARS WHERE COLOR = 'RED' AND MANUFACTURER = "BMW" AND ECAP = "2.0";

You can have 5,000 red cars out of 20,000, so indexing this will not help. However, you can only have 100 BMWs, so indexing MANUFACURER will immediately reduce your score to 100, and you can eliminate blue and white cars just by looking at a hundred lines.

Typically, dbms selects one or two of the available indices based on power, so it pays for a second guess and determines only those indices that are likely to be used.

+6
source

Like everything in the programming world, it all depends. You obviously want to create indexes and relationships in order to maintain normalization and speed up database searches. But you also want to balance this by not having too many indexes so SQL Server gets more time to create each index. In addition, the more indexes, the more fragmentation that may occur in your database.

So, what I'm doing is being inserted into obvious indexes and relationships, and then optimized after the application is built on possible slow queries.

+2
source

Defining a relationship does not create an index.

Usually in places where you have a where clause regarding a field, you need an index, but be careful not just to index the indices everywhere, because they can and can affect insert / update performance.

+1
source

I would start by making sure that every PK and FK has an index.

In addition to this, I found that using the Index Setup Wizard in SSMS provides excellent recommendations when you submit the correct information.

0
source

Database Notes

When you create an index, consider the following database recommendations: A large number of indexes in a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements, because all indexes must be adjusted accordingly as the data in the table changes.

  • Avoid over-indexing highly updated tables and keeping indexes narrow, i.e. with as many columns as possible.

  • Use many indexes to improve query performance in tables with low upgrade requirements but large amounts of data. A large number of indexes can help fulfill queries that do not modify data, such as SELECT statements, since the query optimizer has more indexes to choose from to determine the fastest access method.

  • Indexing small tables may not be optimal, as it may take the query optimizer longer to traverse the index looking for data than for a simple table scan. Therefore, indexes on small tables will never be used, but should still be supported as data in table changes.

  • View indices can provide significant performance gains when the view contains aggregations, table joins, or a combination of aggregation and join. A view does not have to be explicitly referenced in a query optimizer query to use it.

- Stay_Safe -

0
source

Indexes are not very expensive and speed up queries more than you understand. I would recommend adding indexes to all key and non-key fields that are often used in queries. You can even use the execution plan to recommend additional indexes that would speed up your queries.

The only time that indexes are not in your favor is when you do large volumes of data inserts. Each insertion requires that each index in the table is updated with the data in the table.

You can wait for the application to run and you have some well-known database queries that you want to improve, or you could do it now if you have a good idea.

-one
source

More articles: