Explain SQL Server simple query statements and SQL Server statements

Explain SQL Server simple query statements and SQL Server statements

Preface

I have spent a lot of time studying a large number of articles in some theoretical articles, especially the index, understanding the indexing principles will be of great help to understand the query plan and Performance Tuning in the future. However, we will only summarize and summarize some content, in this section, we start to learn simple SQL query statements, brief content, and in-depth understanding.

Simple query statement

All complex statements are composed of simple statements, including SELECT, FROM, WHERE, group by, HAVING, and order by, and some predicates. For example, when we want to query all the data in a table, we will perform the following operations.

SELECT * FROM TABLE

Does the query start with SELECT? We should take the actual life as an example. If we need to buy food from the vegetable market and want to buy celery, we should buy it at the stalls with celery, that is, where to buy it, here we will find that the order of the above query data should be first FROM and then SELECT. In the Basic tutorial of SQL 2012, listing clauses is performed in the following order.

FROMWHEREGROUP BYHAVINGSELECTORDER BY

For example, if we want to query and filter orders placed by customer 71, we will perform the following query.

SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numbers FROM Sales.OrdersWHERE custid = '71'GROUP BY empid, YEAR(orderdate)HAVING COUNT(*) > 1ORDER BY empid, orderyear

But in fact, according to the order we mentioned above, the logical clause is like this.

FROM Sales.OrdersWHERE custid = 71GROUP BY empid, YEAR(orderdate)HAVING COUNT(*) > 1SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numberordersORDER BY empid, orderyear

For the SQL series of bloggers, they will not take SELECT, HAVING, and other statements separately. They are aimed at people with a certain foundation, as will the subsequent content, so here we will describe the simple query statement. However, I have always stressed the short content and a deep understanding. So let's take a look at some things to be aware.

We have seen many articles that have been talking about SQL Performance problems. For example, when querying all data, we need to list all columns rather than SELECT *. Therefore, in this series, I will also discuss performance issues as appropriate, such as the performance issues of SELECT 1 and SELECT * in this section.

Performance of SELECT 1 and SELECT *

When viewing the execution plan in the database, we usually click the [show estimated execution plan] shortcut key Ctrl + L. here we can see that it shows only the estimated execution plan, therefore, it is not accurate. to display the actual execution plan, we should start the [including the actual execution plan]. The shortcut key is Ctrl + M to get a more accurate execution plan, as follows:

Query Method 1 (full table query)

USE TSQL2012GOIF EXISTS(SELECT 1FROM Sales.Orders)SELECT 'SELECT 1'GOIF EXISTS(SELECT *FROM Sales.Orders)SELECT 'SELECT *'GO

The execution plan is the same, as shown below:

Query Method 2 (conditional query on index columns)

We create an index for a column.

CREATE INDEX ix_shipnameON Sales.Orders(shipname)

Next, view the execution plan.

The query plan is still the same. Let's take a look at other query methods.

Query Method 3 (Using Aggregate functions)

USE TSQL2012GOIF (SELECT 1FROM Sales.OrdersWHERE shipname = 'Ship to 85-B') = 1SELECT 'SELECT 1'GOIF (SELECT COUNT(*)FROM Sales.OrdersWHERE shipname = 'Ship to 85-B') = 1SELECT 'SELECT *'GO

We can see that the query plan is still the same.

Query Method 4 (use the aggregate function Count to search for non-index columns)

USE TSQL2012GOIF (SELECT COUNT(1)FROM Sales.OrdersWHERE freight = '41.3400') = 1SELECT 'SELECT 1'GOIF (SELECT COUNT(*)FROM Sales.OrdersWHERE freight = '41.3400') = 1SELECT 'SELECT *'GO

We can see that the execution plan is the same.

Query Method 5 (subquery)

Let's take a look at the performance of the two subqueries.

USE TSQL2012SELECT custid, companyname FROM Sales.Customers AS CWHERE country = N'USA' ANDEXISTS (SELECT * FROM Sales.Orders AS O WHERE O.custid = C.custid)GOSELECT custid, companyname FROM Sales.Customers AS CWHERE country = N'USA' ANDEXISTS (SELECT 1 FROM Sales.Orders AS O WHERE O.custid = C.custid)

Check whether the execution plan is the same.

Query Method 6 (query in view)

We create a view to continue to compare the performance of SELECT 1 and SELECT *.

USE TSQL2012GoCREATE VIEW SaleOdersViewASSELECT shipaddress,shipname,(SELECT unitprice FROM Sales.OrderDetails AS sod where sod.orderid = so.orderid) as tc3FROM Sales.Orders AS soGO

View query

USE TSQL2012SELECT 1 FROM dbo.SaleOdersViewgoSELECT * FROM dbo.SaleOdersViewgo

The result execution plan is as follows:

In this case, we can see from the preceding figure that the performance of SELECT * is so low as 97%, while that of SELECT 1 is only 3%. Why? If you do not understand the reason, I hope that you can leave your comments to give a reasonable explanation.

Performance of all SELECT columns and SELECT *

All the tutorials have been talking about the performance of SELECT *, which is lower than that of all SELECT columns. At the same time, they also give reasonable reasons. I have always believed that, but during the course of data query, the following section is found.

I don't think there is any difference, as long as the SELECT 1/* is inside EXISTS, which really doesn't return any rows – it just returns boolean as soon as condition of the WHERE is checked.I'm quite sure that the SQL Server Query Optimizer is smart enough not to search for the unneeded meta data in the case of EXISTS.I agree that in all the other situations SELECT * shouldn't be used for the reasons Simon mentioned. Also, index usage wouldn't be optimal etc.For me EXISTS (SELECT * ..) is the only place where I allow myself to write SELECT * in production code ;)

The last sentence indicates that the only scenario used by SELECT * Is EXISTS. It is not clear that the previous tutorial ideas are overturned here. Is that true?

Summary

Through the above discussion on the Performance of SELECT 1 and SELECT *, the performance of using SELECT * in the view is lower, and the use of SELECT * should be avoided as much as possible, can I conclude that I prefer SELECT 1? The second point is to see whether the performance of SELECT * In Exist is the same as that of all SELECT columns? These are two questions I have doubts about. Are there two questions I have doubts about? I have no specific answers. Do I need to look at the application scenarios? Where is the application scenario? After all, I am not a professional DBA, and I am not very familiar with SQL at the same time. So I hope that readers of this Article can give wonderful answers and learn from them.

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message and share it with us!