Примеры с SQL

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

Раньше в таких случаях, я пользовался штатной возможностью SQL-сервера дли импорта данных, но иногда проще и быстрее использовать знание SQL и возможностей самого Excel. Надо просто написать запрос на вставку (обновление и т.д.) одной записи в необходимую таблицу базы данных, а затем с помощью Excel-функции СЦЕПИТЬ (в английской версии CONCATENATE) собрать в последней колонке первой строки Excel-таблицы запрос на вставку (используя подготовленный пример) и содержащий необходимые данные из этой таблицы. Потом достаточно "протянуть" эту формулу до последней строки таблицы, скопировать получившиеся данные в среду выполнения sql-запросов и выполнить получившиеся запросы.

Данный нехитрый способ частенько выручает меня в последнее время.

Довольно часто, по мере роста размера базы данных, возникает вопрос - "Откуда?" Тут на помощь придёт обновлённая версия моего запроса, которая формирует по-табличный отчёт о занимаемом пространстве. В полученной таблице прежде всего следует обратить внимание на колонку TotalSize с общим объёмом занимаемым каждой таблицей. Также может оказаться полезной колонка IdxDivData, отображающая соотношение объёма всех индексов таблице к объёму её данных. Таблицы, для которых он более 1, и общий объём значителен точно следует рассмотреть внимательнее.

IF ( OBJECT_ID('tempdb..#svv_tab_row_Count') IS NOT NULL)
  DROP TABLE #svv_tab_row_Count
  
CREATE TABLE #svv_tab_row_Count (
    ID INT IDENTITY not null, 
    Name NVARCHAR(255),
    RowsCount INT, 
    DataSize INT, 
    IdxSize INT, 
    TotalSize INT,
    IdxDivData MONEY)  
    
DECLARE @tableName NVARCHAR(255)

DECLARE tableCursor CURSOR for
  SELECT TABLE_SCHEMA + '.' + TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE'  

OPEN TableCursor
FETCH NEXT FROM tableCursor INTO @tableName

WHILE @@fetch_status=0
BEGIN
    INSERT INTO #svv_tab_row_Count
	    (RowsCount, DataSize, IdxSize)
    EXEC sp_MStablespace @tableName
 
    UPDATE #svv_tab_row_Count
    SET NAME = @tableName,
        TotalSize = DataSize + IdxSize,
        IdxDivData = CASE WHEN DataSize = 0 THEN 0 ELSE IdxSize * 1.0 / DataSize END
    WHERE ID = IDENT_CURRENT('#svv_tab_row_Count')    
    
    FETCH NEXT FROM tableCursor INTO @tableName
END
CLOSE tablecursor
DEALLOCATE tablecursor
GO
SELECT * FROM #svv_tab_row_Count ORDER BY TotalSize DESC 

У нас с проекте нашлись таблицы этим коэффициентом большим 2 и, иногда, даже 5. Выяснилось, что остались неиспользуемые более индексы, после удаления которых занимаемый объём значительно сократился, и данные таблицы "выпали" из рассматриваемых.

Иногда бывает ситуация, когда новая БД уже запущена (или планируются длительные изменения на тестовой базе данных), а нормальное средство для создания архивных копий ещё не настроено, или же настраивать его нецелесообразно. В этом случае меня всегда выручает следующий SQL-запрос:

DECLARE @st varchar(250)
set @st = 'D:\BACKUP\DATABASE_NAME\DATABASE_NAME_' + CONVERT(varchar(15), GetDate(), 112)
BACKUP DATABASE DATABASE_NAME TO DISK = @st

Добавляем его в качестве шага в одно из регулярных заданий или же создаём новое и всё! Автоматический backup готов.

Недостатки:

• Резервная копия делается непосредственно на сервер, а часто и на тот же RAID, где лежит база, т.е. если произойдет сбой на уровне дисков, то такой архив не поможет.
• Созданные архивы автоматически не удаляются, поэтому со временем возможно исчерпание всего свободного объема на диске.
• Данный запрос генерирует название файла, учитывая создание только одного архива в день. Если необходимо большее количество, то необходимо изменить значение переменной @st для добавлении времени:

set @st = 'D:\BACKUP\DATABASE_NAME\DATABASE_NAME_' +
          CONVERT(varchar(15), GetDate(), 112)+ '_' +REPLACE(CONVERT(varchar(15), GetDate(), 108), ':', '')

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

set @st = 'D:\BACKUP\DATABASE_NAME\DATABASE_NAME_' + Cast(((@@DateFirst + DatePart(weekday, GetDate()) - 2) % 7 + 1) as nvarchar(1))

Сегодня наткнулся на неправильное поведение разрабатываемой программы, и покопавшись нашёл ошибку, которую в «рабочем» режиме выловить было бы довольно сложно. А дело вот в чём: мне нужно было выяснить ID вставляемой запросом строки. Для этого, я сразу после запроса на вставку записи добавил строку:

SELECT @@IDENTITY

Всё работало отлично, до тех пока другой разработчик не добавил к таблице trigger на after insert со вставкой данных в другую таблицу. Вот тут-то моё решение и дало сбой. @@IDENTITY содержало ID последней вставленной строки, но не в моей, а в «тригеровской» таблице.

Прочитав справку, нашёл функцию SCOPE_IDENTITY(), которая работает в таком случае правильно! Так что теперь во всех проектах буду для надёжности использовать её.

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

SELECT TOP 1 Number + 1
FROM Table1
WHERE Number + 1 NOT IN (select Number from Table1)
ORDER BY Number

Недостаток: значение ищется больше минимального в таблице, но для нашего случая подошло.

Вчера наткнулся на неожиданное для меня поведение MS SQL 2005. Задача была простая: из суммы выделить размер НДС. И хотя я знаю, что чисто математически эта задача неразрешима, но что поделаешь сказали надо. Пишу запрос:

SELECT 24000 * (1 - 1 / 1.18) as NDS 

И получаю значение 3661,032, которое немного (на несколько копеек) не сходится со значением, посчитанным на калькуляторе – 3661,016949. После некоторых экспериментов выяснилось, что MS SQL округляет значение коэффициента 1 / 1.18 до 6 знаков после запятой, и похоже что с бухгалтерским округлением. То есть получается 0,847457 вместо 0,84745762711, что при больших суммах даёт приличную погрешность.

Решить удалось чётким указанием типа, так:

SELECT 24000 * (1 - CAST(1 as NUMERIC(15,8))/CAST(1.18 as NUMERIC(15,8))) as NDS

Или:

SELECT 24000 * (1 - 1/CAST(1.18 as NUMERIC(15,8))) as NDS  

Или так:

DECLARE  @i NUMERIC (15, 8), @n NUMERIC (15, 8)
SELECT @i = 1, @n = 1.18
SELECT 24000 * (@i - @i / @n) as NDS 

Функция для проверки контрольного разряда кода ОКПО. Проверяются как 8-ми, так и 10-ти значные коды, при необходимости автоматически добавляются ведущие нули. Если ОКПО правильный, то функция возвращает 0, иначе 1.

CREATE FUNCTION dbo.Check_OKPO (@OKPO VARCHAR(11))
RETURNS INT
AS
BEGIN
  DECLARE @I INT, @S INT, @SUMMA INT
  SET @OKPO = LTRIM(RTRIM(@OKPO))
  IF (@OKPO IS NULL OR @OKPO = '') RETURN(1)
  IF LEN(@OKPO) < 8 
    SET @OKPO = RIGHT('00000000' + @OKPO, 8)
  IF LEN(@OKPO) > 8 
    SET @OKPO = RIGHT('00' + @OKPO, 10)
  SET @I = 1
  SET @SUMMA = 0
  WHILE @I < LEN(@OKPO)
  BEGIN
    SET @SUMMA = @SUMMA + CAST(SUBSTRING(@OKPO, @I, 1) AS INT) * @I
    SET @I = @I + 1
  END
  SET @S = @SUMMA % 11
  IF @S = 10 SET @S = 0
  IF @S <> CAST(RIGHT(@OKPO, 1) AS INT) RETURN(1)
  RETURN(0)
END