SQL Question ?

Create a table and insert data with the following query:

CREATE TABLE CountTest(ID INT,Name varchar(10));
GO
INSERT INTO CountTest VALUES (-100,'Aim');
INSERT INTO CountTest VALUES (100,'Bob');
INSERT INTO CountTest VALUES (110,'Dual');
INSERT INTO CountTest VALUES (120,'Dual');
INSERT INTO CountTest VALUES (130,'Dual');
INSERT INTO CountTest VALUES (140,NULL);
INSERT INTO CountTest VALUES (150,NULL);
GO 

What will be the output from this query?

SELECT 
 COUNT(-100),
 COUNT(Name),
 COUNT(ID),
 COUNT('Aim'),
 COUNT(DISTINCT Name),
 COUNT(DISTINCT ID),
 COUNT(ALL Name),
 COUNT(ALL ID) 
FROM CountTest;

Answer: 7,5,7,7,3,7,5,7

Explanation:

The COUNT() function has some interesting defaults. Count with a scalar gives you a count of all rows. Count with a column name, ignores NULLs in those columns, so count(Name) has 5 rows.

Count(DISCTINCT NAME) also ignores NULLs and has only 3 distinct values.

Ref: COUNT – https://msdn.microsoft.com/en-us/library/ms175997.aspx