Главная страница  Карта сайта  Печать  Написать письмо  Twitter  RSS
Войти
Персональный сайт
Стеллецкого Владимира
Обновлено: 25.09.2018 г.

Примеры с SQL

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

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_Name
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = 'dbo' AND
        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. Выяснилось, что остались неиспользуемые более индексы, после удаления которых занимаемый объём значительно сократился, и данные таблицы "выпали" из рассматриваемых.

Комментарии

Андрей (12.01.2014)↑
Большое спасибо!
Прекрасно работает
Небольшая доработка в отношении схем

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:\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))
Добавить комментарий

@@IDENTITY и SCOPE_IDENTITY (03.12.2009) #⇑

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

SELECT @@IDENTITY

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

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

Комментарии

roamer (21.05.2013)↑
Как вариант, можно (в дополнение к "стандартному" автоинкрементному ключу) создать доп. поле (varchar).
И идентифицировать (по GUID) строку по этому полю: 
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

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

Комментарии

(13.10.2014)↑
Можно и таким путем:
SELECT t1.id+1
FROM tabl AS t1
LEFT JOIN tabl AS t2 ON t1.id+1 = t2.id
WHERE t2.id IS NULL
ORDER BY t1.id
Добавить комментарий

Округление при дробных вычислениях (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

Комментарии

swi, www (05.02.2009)↑
А почему не SELECT (24000 - 24000 / 1.18) as NDS ???
Потому, что 24000 это для примера, а на деле сумма вычисляется запросом. Мне показалось, что разницы быть не должно, ан нет. Математика тут не работает, поэтому и написал это сообщение.
swi, www (05.02.2009)↑
тогда можно:
SELECT @s=24000
SELECT (@s - @s / 1.18)
Здесь нет умножения на приближённое число
Да можно, но такой вариант не подойдёт для вычисления сумм для нескольких строк.
Добавить комментарий

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

Программирование на Delphi Мои программы В начало
  Вы 44841 посетитель этой странички
с 07 октября 2006 года		 © – http://svv-home.ru
О сайте