Каталог решений - Шпаргалка по SQL

Шпаргалка по SQL

Предназначена для коллег, плохо знающих язык запросов SQL (как я). Уже давно веду файл, в котором собираю примеры запросов на разные случаи жизни. 
Все запросы создавались и работают в MS SQL. 
 

Соединение нескольких таблиц, выборка конкретных полей

SELECT ОстаткиНоменклатуры.Код, ОстаткиНоменклатуры.Наименование, Таблица.Поле3, Таблица2.Поле4
  FROM [БазаДанных].[dbo].[Таблица1] as Таблица1
  left join  
  [БазаДанных].[dbo].[Таблица2] as Таблица2
  on Таблица1.Поле1 = Таблица2.Поле3
 

Показать 1000 записей, где длина строки, склеенной из нескольких полей, больше 36 символов

SELECT TOP 1000 [Фамилия] + [Имя] + [Отчество] as FIO,len([Фамилия] + [Имя] + [Отчество]) as LENGHT,*
  FROM [БазаДанных].[dbo].[Таблица]
  where LEN([Фамилия] + [Имя] + [Отчество]) > 36
 

Найти максимальную длину строки, которая будет состоять из нескольких столбцов

SELECT MAX(LEN([Фамилия] + [Имя] + [Отчество])) FROM [БазаДанных].[dbo].[Таблица]
 

Получить количество записей во всех таблицах базы данных

SELECT
    o.Name,
    i.rows
FROM sysobjects o
INNER JOIN sysindexes i
ON (o.id = i.id)
WHERE o.xtype = ‘u’
AND i.indid < 2
ORDER BY o.name
 

Получить размер всех баз на сервере баз данных (сортировка по размеру журнала транзакций)

select 
db_name(dbid) as DataBases, 
sum(size) * 8 / 1024 / 1024 as SizeGB,
sum(case when f.groupid = 0 then 0 else size end) * 8 / 1024 / 1024 as DataSizeGB,
sum(case when f.groupid != 0 then 0 else size end) * 8 / 1024 / 1024 as LogSizeGB
from master.sys.sysaltfiles as f
group by db_name(dbid)
order by LogSizeGB desc

Регламентные операции с базами MS SQL Server 

* реиндексация базы (выбрать базу) *
* блокирует работу с базой, поэтому надо выбирать технологические перерывы
sp_msforeachtable N’DBCC dbreindex("?")’

* дефрагментация индексов
* если сделана реиндексация, то дефрагментация индексов не нужна
* если нельзя делать реиндексацию, то делать хотя бы дефрагментацию
sp_msforeachtable N’DBCC INDEXDEFRAG (<имя базы данных>, »?»)’

* обновление статистики SQL Server (для правильного выбора плана запроса 
* выбрать базу!
exec sp_msforeachtable N’UPDATE STATISTICS ? WITH FULLSCAN’

* очистка процедурного кэша (кэша планов) 
* выбрать базу!
* сразу после обновления статистики
dbcc freeproccache

Если есть хотя бы минимальный интерес — продолжу. 

Update 09.02.2018: 

Как узнать размер всех таблиц в базе данных:

DECLARE @pagesizeKB int

SELECT @pagesizeKB = low / 1024 FROM master.dbo.spt_values

WHERE number = 1 AND type = ‘E’

SELECT

table_name = OBJECT_NAME(o.id),

rows = i1.rowcnt,

reservedKB = (ISNULL(SUM(i1.reserved), 0) + ISNULL(SUM(i2.reserved), 0)) * @pagesizeKB,

dataKB = (ISNULL(SUM(i1.dpages), 0) + ISNULL(SUM(i2.used), 0)) * @pagesizeKB,

index_sizeKB = ((ISNULL(SUM(i1.used), 0) + ISNULL(SUM(i2.used), 0))

— (ISNULL(SUM(i1.dpages), 0) + ISNULL(SUM(i2.used), 0))) * @pagesizeKB,

unusedKB = ((ISNULL(SUM(i1.reserved), 0) + ISNULL(SUM(i2.reserved), 0))

— (ISNULL(SUM(i1.used), 0) + ISNULL(SUM(i2.used), 0))) * @pagesizeKB

FROM sysobjects o

LEFT OUTER JOIN sysindexes i1 ON i1.id = o.id AND i1.indid < 2

LEFT OUTER JOIN sysindexes i2 ON i2.id = o.id AND i2.indid = 255

WHERE OBJECTPROPERTY(o.id, N’IsUserTable’) = 1 —same as: o.xtype = ‘IsView’

OR (OBJECTPROPERTY(o.id, N’IsView’) = 1 AND OBJECTPROPERTY(o.id, N’IsIndexed’) = 1)

GROUP BY o.id, i1.rowcnt

ORDER BY 3 DESC