SQL SERVER optimization suggestions and Methods

In actual work, especially in the production environment, the optimization of SQL statements is very important, and it also plays a significant role in improving the database performance. we are always complaining about machine performance problems, and we are always complaining about the cumbersome problems caused by concurrent access. However, if we optimize none of the SQL statements, although it cannot be said, we can solve all the problems, but at least it can solve most of the problems.
1. Top Sorting Problem.
We often need to sort a table field and then take the first N. Therefore, we will write the following SQL statement:
Select top 100 * from table
Order by Score desc
If the table is very large, this operation consumes a lot of resources, because SQL SERVER sorts the entire table and then obtains the first N records. such an operation is performed in Temdb. In extreme cases, an error such as full Log is reported. to avoid sorting the entire table, we only need to create an index on the Score, because the leaf level of the Score index is ordered, as long as the first 100 records are obtained at the Score page level and the actual records are found based on the bookmarks, the database performance will be greatly improved.
2. Same day issue.
We often need to find records on the same day as a date, so we write back the following SQL statement;
Declare @ DateTime datetime
Set @ DateTime = getdate ()
Select * from table
Where convert (char (10), F_Time, 120) = convert (char (10), @ datetime, 120)
However, the problem caused by such SQL statements is that indexes on F_Time cannot be used. in order to use the F_Time index, we can use time period queries instead of the preceding statements.
Declare @ start datetime
Declare @ end datetime
Declare @ datetime
Set @ datetime = getdate ()
Set @ start = convert (char (10), @ datetime, 120) -- the start time of a day
Set @ end = dateadd (ss,-1, dateadd (d, 1, @ start) -- end time of a day
Select * from table where F_Time between @ start and @ end
In this way, the index cannot be used.
3. group operations using indexes.] we often need to group A field and aggregate other fields. if we use indexes properly for grouping fields, we can speed up grouping. the following uses the Orders table of Northwind as an example:
-- An index is created on the employee ID of the orders table.
Select EmployeeID, count (*)
From orders
Group by EmployeeID
-- View the execution plan. This query uses the index on the employee ID, as shown in the following figure:
Select EmployeeID, sum (Freight)
From orders
Group by EmployeeID
-- View the execution plan. This query does not use the index on the employee ID. Instead, it uses a full table scan. Why? Because Freight is not on the index of the employee ID, The result cannot be obtained through the index. if the cost of querying through bookmarks is too high, SQL SERVER chooses to use full table scan. what if we create a composite index on employee ID and Freight?
Create index idx_EmployeeID on orders (EmployeeID, Freight)
-- Execute the second query again. view the execution plan. SQL SERVER uses the index we created. You only need to use the index to query the results, which greatly improves our query speed.