SQL Server simple query example summary, SQL Server example summary

SQL Server simple query example summary, SQL Server example summary

Preface

This section describes some examples of simple query statements, as well as some notes, brief content, and in-depth understanding.

EOMONTH

In the Tutorial example of SQL Server 2012, for the query of the Sales. Orders table, the Order on the last day of each month needs to be returned. The common query is as follows:

USE TSQL2012GOSELECT orderid, orderdate, custid, empidFROM Sales.OrdersWHERE orderdate = DATEADD(MONTH, DATEDIFF(MONTH, '19991231', orderdate), '19991231')

However, in SQL Server 2012, a new function is used to directly return the order of the last day of each month. You can use the EOMONTH function

WHERE orderdate = DATEADD(MONTH, DATEDIFF(MONTH, '19991231', orderdate), '19991231')

Replace

SELECT orderid, orderdate, custid, empidFROM Sales.OrdersWHERE orderdate = EOMONTH(orderdate)

The above is simple and rude.

HAVING AND WHERE

We use the Sales. OrderDetails table to query orders with a total price (qty * unitprice) greater than 10000 and sort the orders by the total price.

USE TSQL2012GOSELECT orderid,SUM(unitprice *qty) AS TotalValueFROM Sales.OrderDetailsGROUP BY orderidHAVING SUM(unitprice *qty) > 10000ORDER BY TotalValue DESC

 

In this example, the difference between WHERE and HAVING is described. The following example is equivalent.

SELECT orderidFROM Sales.OrderDetailsWHERE orderid >10357GROUP BY orderidSELECT orderidFROM Sales.OrderDetailsGROUP BY orderidHAVING orderid >10357

But can aggregate functions be equivalent?

SELECT orderidFROM Sales.OrderDetailsWHERE COUNT(qty * unitprice) >10000GROUP BY orderidSELECT orderidFROM Sales.OrderDetailsGROUP BY orderidHAVING COUNT(qty * unitprice) >10000

 

The twoDifferencesLet's summarize:

(1) WHERE can be used in UPDATE, DELETE, SELECT statements, while HAVING can only be used in SELECT statements.

(2) WHERE filters rows before group by, while HAVING filters rows after group.

(3) WHERE cannot be used in aggregate functions unless the aggregate function is located in the HAVING clause or the subquery contained in the selection list.

HAVING said so much about the difference between WHERE and HAVING, in fact, there are more application scenarios of WHERE. In the final analysis, we can summarize the usage of HAVING in one sentence.

HAVING only filters group by or AGGREGATE functions in SELECT statements.

Insert top Analysis

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

Solution 1

NSERT INTO TABLE …SELECT TOP (N) Cols…FROM Table

Solution 2

INSERT TOP(N) INTO TABLE …SELECT Cols…FROM Table

Solution One is to query several items and insert several items. Solution Two is to query all the data we need to insert a few items. Next let's take a look at the differences between the two and their performance problems, create a query table and insert data.

CREATE TABLE TestValue(ID INT)INSERT INTO TestValue (ID)SELECT 1UNION ALLSELECT 2UNION ALLSELECT 3UNION ALLSELECT 4UNION ALLSELECT 5

Two tables to be inserted

USE TSQL2012GOCREATE TABLE InsertTestValue (ID INT)CREATE TABLE InsertTestValue1 (ID INT)

Insert solution 1

INSERT INTO InsertTestValue (ID)SELECT TOP (2) IDFROM TestValueORDER BY ID DESCGO

Insert solution 2

INSERT TOP (2) INTO InsertTestValue1 (ID)SELECT IDFROM TestValueORDER BY ID DESCGO

Next, query the data in solution 1 and solution 2.

SELECT *FROM InsertTestValueGOSELECT *FROM InsertTestValue1GO

Before inserting data in solution 1 and solution 2, we sorted the queried data in descending order. At this time, we can clearly see that the query data in solution 1 is actually in descending order, in solution 2, descending order is ignored, which is very interesting. So far we can see the difference between the two.

Performance Comparison

When inserting data, we analyze the overhead as follows:

Here we can know how to use inset top (N) to compare INSERT... Select top (N) has better performance. At the same time, select top (N) ignores the queried data sorting. So far, we can draw the following conclusions:

Conclusion: insert top (N) is more efficient than INSERT... Select top (N) inserts data with better performance.

COUNT (DISTINCT) and count (ALL)

You don't need to talk about DISTINCT. This keyword is used to filter ALL columns for Data Consistency instead of for data consistency in a single column. Let's look at the COUNT (DISTINCT) and COUNT (ALL) are the queried data consistent or inconsistent? 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 will perform 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 clearly see that the results of COUNT (colName) and COUNT (ALL colName) are the same. In fact, COUNT (ALL colName) is the default option and includes ALL non-null values, in other words, we do not need to specify ALL.

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!