Databases the SQL [see-kwuhl]

Indexing

Indexing is a crucial aspect of SQL performance, with clustered and non-clustered indexes playing significant roles. Alan Underwood explains that a clustered index sorts data within the table, allowing for efficient data retrieval, while non-clustered indexes store pointers to the data, enabling fast lookups without sorting the table itself 1. Filtered indexes, introduced in SQL Server 2008, allow indexing of specific data subsets, enhancing query speed by focusing only on relevant records 2.

In the olden days, when you created an index, you indexed every record in the table in a filtered index. If you know you're only going to be searching for a certain subset of data, you can create what's called a filtered index that will only index that certain subset of data.

--- Alan Underwood

However, creating too many indexes can be counterproductive, as the query optimizer may only use one per table, necessitating strategic index creation for optimal performance.

   

Optimization

Optimizing SQL queries involves using set operations and parameterized queries to enhance performance. Alan Underwood highlights set operators like INTERSECT and EXCEPT, which simplify syntax by eliminating the need for multiple join predicates, thus streamlining data retrieval 3. Parameterized queries are essential for security and speed, as they prevent SQL injection and allow databases to cache query plans more efficiently 4.

Parameterized queries basically say that you're not going to be able to hack your database all that easily.

--- Alan Underwood

These techniques are vital for developers aiming to write efficient and secure SQL code, reducing complexity and improving execution times.