Get total number of rows

To get total number of rows in a table, use the COUNT or COUNT_BIG aggregate functions.
The COUNT_BIG is the same like the COUNT function, the difference between them being the returned data type. COUNT returns an int data type value, while COUNT_BIG returns a bigint.

Examples

USE model;
GO
CREATE TABLE register_course (
id INT NOT NULL,
student_id INT NULL,
course_id INT NULL,
CONSTRAINT PK_REG_C_ID PRIMARY KEY CLUSTERED (id ASC)
);
GO
INSERT INTO register_course (id, student_id, course_id) VALUES (1, 1, 1)
INSERT INTO register_course (id, student_id, course_id) VALUES (2, 2, 7)
INSERT INTO register_course (id, student_id, course_id) VALUES (3, 1, 3)
INSERT INTO register_course (id, student_id, course_id) VALUES (4, 3, 8)
INSERT INTO register_course (id, student_id, course_id) VALUES (5, 3, 9)
INSERT INTO register_course (id, student_id, course_id) VALUES (6, 3, 10)
INSERT INTO register_course (id, student_id, course_id) VALUES (7, 4, 2)
INSERT INTO register_course (id, student_id, course_id) VALUES (8, 4, 8)
INSERT INTO register_course (id, student_id, course_id) VALUES (9, 4, 9)
INSERT INTO register_course (id, student_id, course_id) VALUES (10, 1, 4)
INSERT INTO register_course (id, student_id, course_id) VALUES (11, 5, NULL)
GO

COUNT(*)

COUNT(*) counts all the rows including NULL values and duplicates.

select COUNT(*) from register_course;
Result: 11

COUNT(1)

COUNT(1) is the same like COUNT(*) and counts all the rows including NULL values and duplicates.

select COUNT(1) from register_course;
Result: 11

COUNT(column_name)

COUNT(column_name) counts all the rows but not NULL values.

select COUNT(course_id) from register_course;
Result: 10

COUNT(ALL column_name)

COUNT(ALL column_name) counts all the rows but not NULL values.

select COUNT(ALL course_id) from register_course;
Result: 10

COUNT(DISTINCT column_name)

COUNT(DISTINCT column_name) counts all the rows but not NULL values and duplicates.

select COUNT(DISTINCT course_id) from register_course;
Result: 8