Database Flashcards
What is an index?
We use indexes in relational databases to improving performance. Indexes are used to speed up query processes by providing quick access to data tables.
What is a key?
A subset of columns that allow a record/row to be uniquely identified. A unique identifier.
What is an inner join?
An inner join returns rows from both tables when there is a match on join criteria.
What is a join?
in SQL we use joins to query multiple tables with related information.
What is a transaction?
A transaction allows us to execute several related SQL statements before committing changes to the database. To ensure consistency and reliability they all either succeed together or fail together.
What are some considerations/ best practices for index design? HINT THERE IS 5
1) Use a few columns on an index of a table that is heavily updated.
2) Use many indexes on tables that contain a lot of data and are not often modified.
3) No need to use indexes on small tables might take longer to scan the index.
4) The more unique the values the better the index performs. indexed columns especially clustered index columns should be as unique as possible.
5) Bulk inserts/Mods is better for index performances as well.
What are some disadvantages of indexes?
1) Indexes can take up a lot of disk space to create and manage, so you don’t want to have too many of them.
2) An index can hurt performance when it’s created on a table that is heavily updated. every time a row is updated the index needs to be updated as well.
What are the two types of indexes?
Clustered index and non-clustered index.
What is a clustered index?
A clustered index stores the actual data row at the leaf node of the index. You can only have one clustered index per table and works best on columns whose values are naturally in ascending order and unique.
What is a non clustered index?
A non-clustered index stores the value of the indexed column and a pointer to the row.
What is the difference between a clustered and non clustered index?
The main difference is how they store their value. clustered indexes store the entire data row at the leaf node of the index, non clustered indexes only store the indexed column and pointer to the row on the table. You can have many non clustered indexes per table, but only one per clustered index per table.
How do indexes work?
When a query is performed against an indexed column it searches the index till it finds the leaf node that matches.
Describe an index.
Distinct structure in relational databases that helps speed up database performance. It uses a B-tree structure. because of this updating, an index can be expensive.
