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

Примеры с SQL

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

Ошибка в возвращаемом значении SCOPE_IDENTITY() и @@IDENTITY (18.02.2015) #⇑

We've caught both SCOPE_IDENTITY() and @@IDENTITY returning the incorrect value after doing an insert. It appears to be some combination of our database settings, the number of rows in the tables, and the particulars of the query plan.

https://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value

То есть в 2008 SQL-сервере SCOPE_IDENTITY() (а также @@IDENTITY) иногда может и "пошутить" - вернуть некорректное значение. Ошибка проявляется при некоторых настройках базы, и определенном кол-ве записей в таблице (>1000000). Вроде как устранено в пакете обновления SP3.

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

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

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

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