См. также: Delphi, Python, Go, Java и Мои программы

Обновлено: 22.05.2023 г.

Примеры с SQL

« первая ‹ предыдущая 1 2 3 4 5 6 следующая › последняя »

Microsoft SQL-Server 2012 (18.02.2015) ^#^⇑

Небольшая подборка статей по нововведениям MS SQL-Server 2012 (Denali):

Импорт данных (05.10.2012) ^#^⇑

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

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

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

Добавить комментарий

Количество строк и размер таблицы (27.03.2012) ^#^⇑

Довольно часто, по мере роста размера базы данных, возникает вопрос - "Откуда?" Тут на помощь придёт обновлённая версия моего запроса, которая формирует по-табличный отчёт о занимаемом пространстве. В полученной таблице прежде всего следует обратить внимание на колонку 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. Выяснилось, что остались неиспользуемые более индексы, после удаления которых занимаемый объём значительно сократился, и данные таблицы "выпали" из рассматриваемых.

CREATE PROCEDURE [UTILS].pTableSize
AS
BEGIN
  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,
      SchemaName NVARCHAR(256), 
      TableName NVARCHAR(256),  
      RowsCount INT,   
      DataSize INT,   
      IdxSize INT,   
      TotalSize INT,  
      IdxDivData MONEY);
      
  DECLARE
  	@SchemaName NVARCHAR(256),
    @TableName NVARCHAR(256),
    @TablePath NVARCHAR(512);
  
  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 @SchemaName, @TableName
  
    WHILE @@fetch_status = 0 BEGIN
    	SET @TablePath = @SchemaName + '.' + @TableName
    	INSERT INTO #svv_tab_row_Count  
        (RowsCount, DataSize, IdxSize)  
    	EXEC sp_MStablespace @TablePath
   
    	UPDATE #svv_tab_row_Count
    	SET
      	TableName = @TableName,
      	SchemaName = @SchemaName,
        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 @SchemaName, @TableName
		END;

	CLOSE tablecursor;  
	DEALLOCATE tablecursor;  

	SELECT *
  FROM #svv_tab_row_Count
  ORDER BY TotalSize DESC

END

Спасибо за предоставленное решение.
Рад, что моя разработка пригодилась.

Добавить комментарий

Простейший backup (24.12.2010) ^#^⇑

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

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

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

Недостатки:

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

set @st = 'D:BACKUPDATABASE_NAMEDATABASE_NAME_' +
          CONVERT(varchar(15), GetDate(), 112)+ '_' +REPLACE(CONVERT(varchar(15), GetDate(), 108), ':', '')

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

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

Добавить комментарий

@@IDENTITY и SCOPE_IDENTITY (03.12.2009) ^#^⇑

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

SELECT @@IDENTITY

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

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

create procedure [dbo].[Str_GUID]
(
  @sGUID varchar(50) OUTPUT
)
AS
BEGIN
/*
Вычислить GUID и вернуть его строковое представление (без разделителей)
Пример:
  DECLARE @Sx varchar(50)
  Execute dbo.Str_GUID @Sx OUTPUT
  select @Sx, len(@Sx)
*/
  DECLARE  @myid uniqueidentifier
  SET @myid = NEWID()
  set @sGUID=cast(@myid as varchar(50))
  set @sGUID=UPPER(rtrim(ltrim(REPLACE(@sGUID , '-', ''))))
  RETURN
END

Это может быть полезным, когда нужно идентифицировать строки таблиц из разных баз данных.

Спасибо за комментарий, но хотел бы дополнить:
не уверен, что Guid преобразованный в строку будет хорошо работать с точки зрения производительности, индексов и т.п. К тому же мне не понятно, как будет работать Ваше решение при вставке нескольких строк сразу (так называемый "INSERT FROM SELECT").
Думаю, в данном случае более удачным будет решение:

CREATE TABLE MyUniqueTable
(UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),
Characters VARCHAR(10) )

INSERT INTO MyUniqueTable(Characters) VALUES ('abc')

SELECT * FROM MyUniqueTable

Постараюсь разместить здесь придуманное и реализованное у нас решение, которое позволяет получать не пересекающиеся уникальные ключи, с привязкой к отдельным серверам.

Добавить комментарий

Первый пропуск в порядковом ряду (26.02.2009) ^#^⇑

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

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

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

Округление при дробных вычислениях (19.08.2009) ^#^⇑

Вчера наткнулся на неожиданное для меня поведение 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

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

swi, www (05.02.2009)^↑

тогда можно:
SELECT @s=24000
SELECT (@s - @s / 1.18)
Здесь нет умножения на приближённое число

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

Добавить комментарий

« первая ‹ предыдущая 1 2 3 4 5 6 следующая › последняя »

Программирование на Delphi

Мои программы

В начало

Примеры с SQL

Microsoft SQL-Server 2012 (18.02.2015) ^#^⇑

Импорт данных (05.10.2012) ^#^⇑

Количество строк и размер таблицы (27.03.2012) ^#^⇑

Комментарии

Простейший backup (24.12.2010) ^#^⇑

@@IDENTITY и SCOPE_IDENTITY (03.12.2009) ^#^⇑

Комментарии

Первый пропуск в порядковом ряду (26.02.2009) ^#^⇑

Комментарии

Округление при дробных вычислениях (19.08.2009) ^#^⇑

Комментарии

Примеры с SQL

Microsoft SQL-Server 2012 (18.02.2015) #⇑

Импорт данных (05.10.2012) #⇑

Количество строк и размер таблицы (27.03.2012) #⇑

Комментарии

Простейший backup (24.12.2010) #⇑

@@IDENTITY и SCOPE_IDENTITY (03.12.2009) #⇑

Комментарии

Первый пропуск в порядковом ряду (26.02.2009) #⇑

Комментарии

Округление при дробных вычислениях (19.08.2009) #⇑

Комментарии

Microsoft SQL-Server 2012 (18.02.2015) ^#^⇑

Импорт данных (05.10.2012) ^#^⇑

Количество строк и размер таблицы (27.03.2012) ^#^⇑

Простейший backup (24.12.2010) ^#^⇑

@@IDENTITY и SCOPE_IDENTITY (03.12.2009) ^#^⇑

Первый пропуск в порядковом ряду (26.02.2009) ^#^⇑

Округление при дробных вычислениях (19.08.2009) ^#^⇑