SQL Server Simple Query sample rollup _mssql

Objective

In this section we'll talk about simple query examples and places to look at, short content, and deep understanding.

EOMONTH

In the tutorial example of SQL Server 2012, for queries on sales.orders tables, you need to return orders for the last day of the month. Our general enquiry is as follows

Use TSQL2012
go
SELECT OrderID, OrderDate, CustID, empid from
sales.orders
WHERE orderdate = DATEADD ( MONTH, DATEDIFF (MONTH, ' 19991231 ', OrderDate), ' 19991231 ')

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

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

Replaced by

SELECT OrderID, OrderDate, CustID, empid from
sales.orders
WHERE orderdate = EOMONTH (OrderDate)

As simple and rough as that.

Having and WHERE

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

Use TSQL2012
go
SELECT orderid,sum (UnitPrice *qty) as Totalvalue to
sales.orderdetails
GROUP by OrderID has
SUM (UnitPrice *qty) > 10000 ORDER by
Totalvalue DESC

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

Select OrderID from
sales.orderdetails
WHERE OrderID >10357
GROUP by OrderID
SELECT OrderID From
sales.orderdetails
GROUP by OrderID has
OrderID >10357

But is it equal to the use of aggregate functions?

SELECT OrderID from
sales.orderdetails
WHERE COUNT (Qty * unitprice) >10000
GROUP by OrderID
SELECT OrderID from
sales.orderdetails
GROUP by OrderID having
COUNT (Qty * unitprice) >10000

The difference between the two we sum up:

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

(2) Where the row is filtered before group by, and the having filter row is after group by.

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

Having said so much, the difference between where and having, in fact, where the application scenario is more, we are summed up in the final sentence to the use of the.

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

INSERT Top Analysis

When you insert data from a query 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

Solution one is to query a few to insert a few, plan two is to query all we need to insert a few data, next we take a look at the two different and performance problems, create a query table and insert data.

CREATE TABLE testvalue (id INT)
INSERT into TestValue (ID)
Select 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
Select 4
UNION ALL
Select 5

Two tables that need to be inserted

Use TSQL2012
go
CREATE TABLE inserttestvalue (ID int)
CREATE TABLE InsertTestValue1 (ID int)

Insert of Scheme one

INSERT into Inserttestvalue (ID)
SELECT top (2) ID from TestValue ORDER by
ID DESC
Go

Insert of Scenario Two

INSERT Top (2) to InsertTestValue1 (ID)
SELECT ID from TestValue ORDER by
ID DESC
Go

Next, query the data for scenario I and scenario two

SELECT * from
inserttestvalue
go
select * from
InsertTestValue1
go

Before we insert data into scenario I and scenario two, we're descending on the query data. At this point we can clearly see that the query data in scenario one is indeed descending, and scenario two ignores the descending order, which is a very interesting place, so we see the difference between the two.

Performance comparison

We analyze the cost of inserting data as follows:

Here we can know the use of inset top (N) than insert ... Select TOP (n) performs better, while select TOP (n) ignores the sort of data that is queried. So we can draw a conclusion

Conclusion: Insert Top (N) is more than insert ... SELECT Top (N) inserts data for better performance.

Count (DISTINCT) and count (all)

Needless to say about DISTINCT, this keyword filter repeats that all column data is filtered consistently, rather than filtered in a single row of data, do we see whether the data in count (DISTINCT) and count (all) are consistent or inconsistent? We first create the test table

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

Insert the following test data

Next we do the following query

Use TSQL2012
down
SELECT COUNT (NAME) as Count_name from
dbo. TestData
SELECT COUNT (all NAME) as Count_allname from
dbo. TestData
SELECT COUNT (DISTINCT NAME) as Count_distinctname from
dbo. TestData

At this point we can clearly see 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.

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!