Detailed SQL Server simple query statement _mssql

Objective

For some of the principles of the article Park has a large number of articles, especially on the index of this piece, I also spend a lot of time to learn, for understanding the principle of indexing for subsequent understanding of the query plan and performance tuning has a great help, and we are just some content to summarize and summarize, In this section we begin to formally step into the learning SQL of simple query statements, short content, in-depth understanding.

Simple query statement

All complex statements consist of a simple statement consisting of a select, a from, a WHERE, a GROUP by, a having, an order by, and, of course, predicates, and so on. For example, when we want to query all the data in a table, we will do as follows.

SELECT * FROM TABLE

Is this a query that starts with a select? We should take examples from the real life, such as we need to buy vegetables, we want to buy celery, we should go to have celery stalls to buy, that is, where to buy, we will find that the order of the above query data should be first from then select. The list of clauses in the SQL 2012 Basic tutorial is logically handled in the following order.

From
WHERE
GROUP by
has
SELECT
order BY

For example, we would like to query to filter the customer 71 orders, we will conduct the following query.

SELECT Empid, Year (OrderDate) as OrderYear, COUNT (*) as numbers from 
sales.orders
WHERE custid = ' Up '
Grou P by Empid, year (OrderDate) has
COUNT (*) > 1
Order by Empid, OrderYear

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

From Sales.orders
WHERE CustID = "
GROUP by Empid", year (OrderDate) has
COUNT (*) > 1
SELECT empid, Y EAR (OrderDate) as OrderYear, COUNT (*) as Numberorders order
by Empid, OrderYear

For the blogger's SQL series will not be select, having and other statements alone, for a certain basis of the crowd, the follow-up content is also so, so here we will be a simple query statement completed. But what I've been emphasizing is short content, deep understanding, so let's take a look at some of the areas that need to be noticed.

We've seen a lot of articles that have been talking about SQL performance issues, like listing all the columns instead of SELECT * When querying all the data, so in this series, I'll also talk about performance issues, such as the performance issues of select 1 and select * in this verse.

Select 1 and select * Performance discussion

When viewing execution plans in a database, we usually click on the "Show Estimated execution plan" shortcut key is ctrl+l, here we can see that it has been shown that only the estimated execution plan is displayed, so it is inaccurate, so in order to show the actual execution plan, we should start "including the actual execution plan", The shortcut key is ctrl+m so that you can get a more accurate execution plan, as follows

Query mode one (full table query)

Use TSQL2012
go
if EXISTS (
select 1 from
sales.orders)
Select ' Select 1 '
go
if EXISTS (
SELECT *
From sales.orders)
select ' Select # '
go

View the execution plan is the same at this time, as follows:

Query mode two (on index columns condition lookup)

We create an index on a column

CREATE INDEX ix_shipname on
sales.orders (ShipName)

Next, continue to view its execution plan.

This shows that the query plan remains the same. Let's take a look at other ways of querying.

Query mode three (using aggregate functions)

Use TSQL2012
go
IF (
Select 1 from
sales.orders
WHERE shipname = ' ship to 85-b ') = 1
Select ' Select 1 '
go
IF (
Select COUNT (*) from
sales.orders
WHERE shipname = ' ship to 85-b ') = 1
S Elect ' SELECT * ' Go

We see that the query plan remains the same.

Query mode four (find on non-indexed columns using the aggregate function count)

Use TSQL2012 "Go
IF" (
Select COUNT (1) from
sales.orders
WHERE freight = ' 41.3400 ') = 1
Select ' Select 1 '
go
IF (
Select COUNT (*) from
sales.orders
WHERE freight = ' 41.3400 ') = 1
Select ' SELECT * ' Go '

We see the execution plan still the same.

Query mode five (subquery)

Let's look at the performance of the two in subqueries

Use TSQL2012
Select CustID, CompanyName from Sales.customers as C
WHERE country = N ' USA ' and
EXISTS (select * FROM Sales.orders as O WHERE o.custid = c.custid)
go
SELECT CustID, CompanyName from Sales.customers as C
   where country = N ' USA ' and
EXISTS (SELECT 1 from sales.orders as O WHERE o.custid = c.custid)

At this point, the result is the same as the execution plan.

Query mode six (query in view)

We continue to create views to compare the performance of select 1 and select *

Use TSQL2012
go
CREATE VIEW saleodersview
as Select Shipaddress,shipname, (select UnitPrice from Sales.orderdetails as sod where Sod.orderid = So.orderid) as TC3 from sales.orders as so go

Make a view query

Use TSQL2012 the
SELECT 1 from dbo. Saleodersview
Go
SELECT * FROM dbo. Saleodersview Go

The results of the implementation plan are as follows:

At this point, we find that the performance of the SELECT * is so low that it occupies 97% and select 1 is only 3% when we use the view query. Do not understand the reason, I hope that there are clear reasons for the park friends can leave your comments to give a reasonable explanation.

Select all columns and select * Performance discussion

All the tutorials have been talking about select * Performance is lower than all of the Select column performance, but also give a reasonable reason, I always think so, but in the data learning process, found the following passage.

I don ' t-is-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's SQL Server Query Optimizer is smart enough isn't to search for the unneeded meta data in the case of EXISTS.
I agree that and all of the other situations SELECT * shouldn ' t being used for the reasons Simon mentioned. Also, index usage wouldn ' t be optimal etc.
For me EXISTS (SELECT * ...) is the ' place where I ' allow myself to write SELECT * in Production code;)

The last sentence shows that the only scene used by SELECT * is in exists, and it's not clear that the idea of the tutorial that subverts what I used to see here is really that true?

Summarize

With the above discussion of select 1 and select * Performance, the use of select * Performance in the view is lower and combined with SELECT * Try to avoid it, can I conclude that I can be more inclined to use Select 1? The 2nd is to see the above given data select * In the exist performance is not the same as a certain select all columns? This is my question two questions, is not my question two questions, does not have the concrete answer, needs to see the application scene? Where is the application scene? After all, not a professional DBA, but also the study of SQL is not deep, so I hope to see this article readers, can give a wonderful answer, but also let me learn.

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, if there are questions you can message exchange, but also hope that a lot of support cloud Habitat community!