SQL скрытые методы? [дубликат]

В Sql Server предложение HAVING. В частности, HAVING (COUNT DISTINCT FOO)> @SomeNumber для быстрого поиска строк с более чем одним отличным значением для данной группы.

Из MSDN :

USE AdventureWorks2008R2 ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
ORDER BY SalesOrderID ;

Общие табличные выражения (SQL Server 2005 +)

WITH x AS (
    SELECT 1 as A, 2 as B, 3 as C
),
WITH y AS (
    SELECT 4 as A, 5 as B, 6 as C
    UNION
    SELECT 7 as A, 8 as B, 9 as C
)
SELECT A, B, C FROM x
UNION
SELECT A, B, C FROM y

Они действительно хороши для разбивки ваших запросов на шаги

Из документации PostgreSQL:

Разделение таблицы

Разделение относится к разделению того, что логически является одной большой таблицей, на более мелкие физические части. Секционирование может дать несколько преимуществ:

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

• Производительность обновления также может быть улучшена, поскольку каждая часть таблицы имеет индексы меньше, чем индекс всего набора данных. Когда индекс больше не умещается в памяти, операции чтения и записи индекса требуют все большего количества обращений к диску.

• Массовое удаление может быть выполнено простым удалением одного из разделов, если это требование запланировано в проекте разделения. DROP TABLE намного быстрее, чем массовое DELETE, не говоря уже о последующих накладных расходах VACUUM.

• Редко используемые данные можно перенести на более дешевые и медленные носители.

Два из Postgresql: DISTINCT ON (см. пример) и новый WITH.

в MySQL, используя ключевое слово "STRAIGHT_JOIN". Если вы знаете свои данные и связи таблиц поиска, к которым вы присоединяетесь, иногда оптимизатор рассматривает меньшие таблицы как основу для присоединения и пытается запросить "меньшее количество записей" к вашей "большей" таблице, что занимает значительно больше времени. Если ваша основная таблица стоит первой в "from", а ее "критерии" находятся впереди, то прямое соединение попадет в нее первой, соединится с остальными таблицами и будет выполнено в кратчайшие сроки.

Мне приходилось делать это, имея дело с государственными данными из 10+ миллионов записей, соединенных с 15+ таблицами поиска. Без straight-join система задыхалась через 20 с лишним часов. Добавив Straight-join, все было сделано примерно за 2 часа.

Люди недостаточно используют встроенные функции и любят изобретать велосипед, вот Десять функций SQL Server, которые вы игнорировали до сих пор

Использование NEWSEQUENTIALID () вместо NEWID () в кластеризованном столбце uniqueidentifier будет работать намного лучше, так как он не вызовет разделения страниц и, следовательно, фрагментации

Использование вспомогательной таблицы чисел, чтобы вы могли быстро выполнить некоторую логику, основанную на наборах

, например

select DATEADD(m,number,'20010101')
from master..spt_values
where type = 'P'
order by 1

ЛЮБЫЕ, ВСЕ и НЕКОТОРЫЕ

EXISTS. Я поражен, как много людей все еще используют COUNT(*) для проверки существования или IN (SELECT...), когда EXISTS может сделать эту работу намного быстрее.

Чаще всего можно встретить :

SELECT @MyVar = Count(*) FROM Table1 WHERE....
If @MyVar <> 0
BEGIN
   --do something
END

when

IF EXISTS(SELECT 1 FROM Table1 WHERE...)
BEGIN
    --don something
END

всегда лучше.

SELECT... EXCEPT SELECT...

и

SELECT... INTERSECT SELECT...

могут быть полезны (и очень эффективны) при выделении различных или общих строк - и это для всех столбцов в строке - между наборами. Это чрезвычайно полезно, когда у вас много столбцов.

SQL GROUP BY - CUBE, предложения ROLLUP

Аналитические (ранжирование AKA, оконное управление AKA) функции IE:

• ROW_NUMBER
• RANK
• DENSE_RANK
• NTILE
• OVER

Просмотры : нормальные и материализованные

Трудно сказать много, не ссылаясь на синтаксис производителя

Производные таблицы для создания «переменных» и сокращения повторяющегося кода.

Что-то вроде этого, но можно расширить. Очевидно, что «Среднее значение» может быть гораздо более сложным вычислением, и если у вас их несколько, это поможет очистить код.

Select *, case when AverageValue > 50 then 'Pass' Else 'Fail' end
From
(
 Select ColA, ColB, AverageValue = (ColA+ColB)/2
 From InnerMostTable
) AverageValues
Order By AverageValue Desc

В SQL Server использование функции Convert() для получения дат в формате mm/dd/yyyy вместо функции Cast()

SELECT convert(datetime,  '1/1/2010', 101)

Я использую это постоянно

Pivot

Это новинка 2005 года (я знаю, что это было давно, но многие люди все еще используют 2000). Сохранения, выполняющие кучу «case when name = 'tim' then value else 0 end» для построения ваших агрегатов в эти выходные.

В последнее время я часто использую CROSS APPLY .

OVER Clause (SQL Server) a.k.a. Window functions (PostgreSQL) или analytic functions (Oracle)

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