SQL server-Simple Query Example (11)

Objective

In this section we will talk about examples of simple query statements and the areas needing attention, short content, and in-depth understanding.

EOMONTH

In the tutorial example for SQL Server 2012, for a query on a sales.orders table, you need to return an order for the last day of the month. Our common query is as follows

' 19991231 ' ' 19991231 ')

However, in SQL Server 2012, a new function returns the order of the last day of each month directly, through the Eomonth function,

' 19991231 ' ' 19991231 ')

Replaced by

EOMONTH (OrderDate)

Simple and rude as above.

Having and WHERE

We use the Sales.orderdetails table to query for orders with a total price (Qty*unitprice) greater than 10000, sorted by Total price.

*10000ORDER by Totalvalue DESC

In this example we say the difference between where and having, the following example is equivalent

>10357>10357

But can it be equated with aggregate functions?

SELECT orderidfrom sales.orderdetailswhere   * UnitPrice) >10000* unitprice) >10000

The difference between the two we summarize:

(1) Where can be used in update, DELETE, select statements, and have only be used in the SELECT statement.

(2) Where the filter line is before group by and having a filter line after group by.

(3) Where cannot be used in an aggregate function unless the aggregate function is in a subquery contained in a HAVING clause or select list.

Speaking so much, the difference between where and having, in fact, where the application scenario more, we in the end of a sentence to summarize the use of having.

Having to filter groups (group BY) or aggregate functions (AGGREGATE) in a SELECT statement only

INSERT Top Analysis

When inserting the queried data into a table, we actually have two solutions.

Programme I

Nsert into TABLE ... SELECT TOP (N) Cols ... From Table

Programme II

INSERT TOP (N) into TABLE ... SELECT Cols ... From Table

Scenario one is the need to query a few to insert a few, scenario two is to query all we need to insert a few data, next we look at the difference and the two performance problems, create a query table and insert data.

12 3)45

Two tables that need to be inserted

Use Tsql2012gocreate table inserttestvalue (id int) CREATE table InsertTestValue1 (id int)

Insertion of Scenario I

INSERT into Inserttestvalue (ID) SELECT TOP (2) idfrom testvalueORDER by ID DESCGO

Insert of Scenario Two

INSERT TOP (2) into InsertTestValue1 (ID) SELECT idfrom testvalueORDER by ID DESCGO

Next, query the data for scenario I and scenario two

SELECT ** frominserttestvalue1go

Before we insert data into scenario one and program two, we are descending on the data of the query, at which point we can clearly see that the query data in scenario one is actually descending, and scenario two ignores the descending order, which is a very interesting place, and we see the difference between the two.

Performance comparison

When inserting data, we analyze the cost of it as follows:

Here we can know the use of inset TOP (N) than insert ... Select TOP (n) performs better, and select Top (n) ignores the sort of data that is queried. At this point we can draw a conclusion

Conclusion: Insert TOP (N) than insert ... SELECT TOP (N) inserts better data performance.

Count (DISTINCT) and count (all)

About DISTINCT, this keyword filtering is repeated for all column data consistent filtering instead of for single-column data consistent filtering, we look at Count (DISTINCT) and count (all) query the data is consistent or inconsistent? We first create a test table

CREATE TABLE TestData (    Id INT not null IDENTITY PRIMARY KEY,    NAME VARCHAR (max) NULL);

Insert the following test data

Next we make the following query

Use Tsql2012goselect COUNT (NAME) as Count_namefrom dbo. Testdataselect COUNT (all NAME) as Count_allnamefrom dbo. Testdataselect COUNT (DISTINCT NAME) as Count_distinctnamefrom dbo. TestData

At this point we can see very clearly that the result of Count (colname) and count (all colname) is the same, in fact count (all colname) is the default option and includes all non-null values, In other words all do not need us to specify.

Summarize

In this section we briefly talk about simple query statements and what needs to be noted, about simple queries and basic concepts here we come to the end, the next section we start to join the table, the next thing will be more interesting, short content, in-depth understanding, we'll see you next week.

SQL server-Simple Query Example (11)