Shulou(Shulou.com)06/01 Report--

Count (*) or Count (1) or Count ([column]) are probably the most commonly used aggregate functions in SQL Server. In fact, many people do not know the difference between these three. This article will explain the role of the three, the relationship and the principles behind them.

I often see some so-called optimization recommendations that use Count (1) instead of Count (*) to improve performance on the grounds that Count (*) brings full table scans. In fact, how to write Count makes no difference.

What Count (1) and Count (*) actually mean is to evaluate whether the expression in Count () is NULL, and if it is NULL, it does not count, while non-NULL counts. For example, as shown in code 1, you specify NULL in Count (the optimizer does not allow you to specify NULL explicitly, so you need to assign a value to a variable).

DECLARE @ xx INTSET @ xx=NULLSELECT COUNT (@ xx) FROM [AdventureWorks2012]. [Sales]. [SalesOrderHeader]

NULL is specified in the code listing 1.Count. Since all lines are NULL, the result is not counted. Obviously, the result is 0.

So the result will be the same when you specify Count (*) or Count (1) or regardless of Count ('anything'), because none of these values are NULL, as shown in the following figure

As long as a non-NULL expression is specified in Count, the result makes no difference.

If you just look at the results, Select Count (*) and Select Count (1) return the same results.

If the table does not have a primary key (Primary key), then count (1) is faster than count (*), and if there is a primary key, count (primary key) is the fastest when the primary key is a condition for count.

If your table has only one field, then count (*) is the fastest.

The results of count (*) and count (1) include the statistics of NULL, while count (column) does not include the statistics of NULL.

1. The difference between select 1 and select *

Selelct constant from... For all rows, only one value is always returned, that is, a constant. So normal can only be used to determine whether there is one or not (such as the exists clause). And select * from... Is all columns that return all rows.

The difference in performance depends on your from and where clauses. For example, if you can pass the index in your where condition, then obviously select 1 from. The performance is better than that of select * from. good.

2. The use of select sum (1)

Select count (*) returns the number of records that meet the criteria, which is the same as select sum (1).

But sum () can pass any number, either a negative number or a floating point number, and the returned value is the input value n * the number of records that satisfy the condition.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.