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

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

Примеры с SQL

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

Текущий пользователь (18.11.2014) ^#^⇑

Иногда на прямо на SQL-сервере бывает необходимо узнать от имени какого пользователя выполняется тот или иной запрос. В этом случае на выручку приходит функция:

print SYSTEM_USER -- Пользователь, под которым подключились к базе данных

Чаще всего я использую данную функцию как временное (отладочное) решение, когда необходимо определить какой пользователь создал или изменил запись в таблице. Для этого достаточно в таблицу добавить поле (NOT NULL) и указать для него в качестве значения по умолчанию SYSTEM_USER.

UPD: Появилось только 2005 MSSQL (было исправлено после уточнения в комментариях)

В сообщении пару неточностей - этим способом нельзя узнать, какой пользователь изменил запись в таблице, если в запросе UPDATE явно не указано обновление поля значением SYSTEM_USER - инструкция UPDATE изменяет только те поля, которые там указаны, а пользователь останется прежним. Этот метод работает только для INSERT. А сама функция появилась в MS Sql Server 2005.

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

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

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

Большое спасибо!
Прекрасно работает
Небольшая доработка в отношении схем

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(), которая работает в таком случае правильно! Так что теперь во всех проектах буду для надёжности использовать её.

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

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

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

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

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

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

В начало

Примеры с SQL

Текущий пользователь (18.11.2014) ^#^⇑

Комментарии

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

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

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

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

Комментарии

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

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

Комментарии

Примеры с SQL

Текущий пользователь (18.11.2014) #⇑

Комментарии

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

Microsoft SQL-Server 2012 (18.02.2015) #⇑

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

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

Комментарии

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

@@IDENTITY и SCOPE_IDENTITY (03.12.2009) #⇑

Комментарии

Текущий пользователь (18.11.2014) ^#^⇑

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

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

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

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

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

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