Нужно количество строк после оператора SELECT: каков оптимальный подход к SQL?

Есть только два способа быть на 100 % уверенными в том, что COUNT(*) и фактический запрос дадут согласованные результаты:

• Объедините COUNT(*) с запросом, как в вашем подходе 2. Я рекомендую форму, которую вы показываете в своем примере, а не коррелированную форму подзапроса, показанную в комментарии от kogus.
• Используйте два запроса, как в подходе 1, после запуска транзакции на уровне изоляции SNAPSHOT или SERIALIZABLE.

Использование одного из этих уровней изоляции важно, потому что любой другой уровень изоляции позволяет новым строкам, созданным другими клиентами, стать видимыми в вашей текущей транзакции. Дополнительные сведения см. в документации MSDN по адресу SET TRANSACTION ISOLATION.

Если вы используете SQL Server, после запроса вы можете выбрать @@RowCount (или, если ваш результирующий набор может содержать более 2 миллиардов строк, используйте функцию RowCount_Big()). Это вернет количество строк, выбранных предыдущим оператором, или количество строк, затронутых оператором вставки/обновления/удаления.

SELECT my_table.my_col
  FROM my_table
 WHERE my_table.foo = 'bar'

SELECT @@Rowcount

Или, если вы хотите включить количество строк в отправляемый результат, аналогичный подходу № 2, вы можете использовать предложение OVER.

SELECT my_table.my_col,
    count(*) OVER(PARTITION BY my_table.foo) AS 'Count'
  FROM my_table
 WHERE my_table.foo = 'bar'

Использование предложения OVER будет иметь гораздо лучшую производительность, чем использование подзапроса для получения количества строк. Использование @@RowCount будет иметь наилучшую производительность, потому что не будет никаких затрат на запрос для оператора select @@RowCount.

Обновление в ответ на комментарий: в приведенном мной примере указано количество строк в разделе, определяемое в данном случае как «PARTITION BY my_table.foo». Значение столбца в каждой строке равно количеству строк с одинаковым значением my_table.foo. Поскольку в вашем примере запроса было предложение «WHERE my_table.foo = 'bar'», все строки в результирующем наборе будут иметь одинаковое значение my_table.foo, и поэтому значение в столбце будет одинаковым для всех строк и равным (в в этом случае) это количество строк в запросе.

Вот лучший/более простой пример того, как включить столбец в каждую строку, которая представляет собой общее количество строк в наборе результатов. Просто удалите необязательное предложение Partition By.

SELECT my_table.my_col, count(*) OVER() AS 'Count'
  FROM my_table
 WHERE my_table.foo = 'bar'

Подход 2 всегда будет возвращать количество, соответствующее вашему набору результатов.

Я предлагаю вам связать подзапрос с вашим внешним запросом, чтобы гарантировать, что условие вашего подсчета соответствует условию в наборе данных.

SELECT 
  mt.my_row,
 (SELECT COUNT(mt2.my_row) FROM my_table mt2 WHERE mt2.foo = mt.foo) as cnt
FROM my_table mt
WHERE mt.foo = 'bar';

Если вас беспокоит, что количество строк, соответствующих условию, может измениться за несколько миллисекунд с момента выполнения запроса и получения результатов, вы можете/должны выполнять запросы внутри транзакции:

BEGIN TRAN bogus

SELECT COUNT( my_table.my_col ) AS row_count
FROM my_table
WHERE my_table.foo = 'bar'

SELECT my_table.my_col
FROM my_table
WHERE my_table.foo = 'bar'
ROLLBACK TRAN bogus

Это всегда будет возвращать правильные значения.

Кроме того, если вы используете SQL Server, вы можете использовать @@ROWCOUNT, чтобы получить количество строк, затронутых последним оператором, и перенаправить вывод реального запроса во временную таблицу или табличную переменную, поэтому можно вернуть все целиком, и не нужна транзакция:

DECLARE @dummy INT

SELECT my_table.my_col
INTO #temp_table
FROM my_table
WHERE my_table.foo = 'bar'

SET @dummy=@@ROWCOUNT
SELECT @dummy, * FROM #temp_table

Вот несколько идей:

• Перейдите к подходу № 1 и измените размер массива, чтобы он содержал дополнительные результаты, или используйте тип, который автоматически изменяет размер по мере необходимости (вы не указываете, какой язык вы используете, поэтому я не могу быть более конкретным).
• Вы можете выполнить оба оператора в подходе № 1 в транзакции, чтобы гарантировать, что счетчики будут одинаковыми оба раза, если ваша база данных поддерживает это.
• Я не уверен, что вы делаете с данными, но если можно обработать результаты, не сохраняя их все сначала, это может быть лучшим методом.

Если вы действительно обеспокоены тем, что ваш счетчик строк изменится между счетчиком выбора и оператором выбора, почему бы сначала не выбрать ваши строки во временной таблице? Таким образом, вы знаете, что будете синхронизированы.

Почему бы вам не поместить свои результаты в вектор? Таким образом, вам не нужно знать размер заранее.

Возможно, вы захотите подумать о лучшем шаблоне для работы с данными этого типа.

Ни один драйвер SQL с самоподготовкой не скажет вам, сколько строк будет возвращено вашим запросом, прежде чем вернуть строки, потому что ответ может измениться (если только вы не используете транзакцию, которая сама по себе создает проблемы).

Количество строк не изменится - погуглите ACID и SQL.

Просто добавить это, потому что это лучший результат в Google для этого вопроса. В sqlite я использовал это, чтобы получить количество строк.

WITH temptable AS
  (SELECT one,two
   FROM
     (SELECT one, two
      FROM table3
      WHERE dimension=0
      UNION ALL SELECT one, two
      FROM table2
      WHERE dimension=0
      UNION ALL SELECT one, two
      FROM table1
      WHERE dimension=0)
   ORDER BY date DESC)
SELECT *
FROM temptable
LEFT JOIN
  (SELECT count(*)/7 AS cnt,
                        0 AS bonus
   FROM temptable) counter
WHERE 0 = counter.bonus