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

Примеры с SQL

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

Как работает реляционная БД (26.09.2015) #⇑

Отличная статья (перевод), в которой довольно подробно рассматривается вопрос: Как работает реляционная базы данных?
В очередной раз пополнил свои знания и на всякий случай сохраняю ссылку себе. Основные рассматриваемые моменты:

  • Понятие сложности алгоритмов (O) при работе с базами данных (с примерами)
  • Принципы массива, дерева (индекса) и hash-таблицы
  • Общий обзор базовых компонент базы данных:
    • Диспетчер клиентов
    • Диспетчер запросов (статистика, оптимизатор запросов)
    • Диспетчер данных (транзакции, параллелизм, блокировки)
Добавить комментарий

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

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

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

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

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

Комментарии

Denis Wilson (17.11.2014)↑
В сообщении пару неточностей - этим способом нельзя узнать, какой пользователь изменил запись в таблице, если в запросе 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_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. Выяснилось, что остались неиспользуемые более индексы, после удаления которых занимаемый объём значительно сократился, и данные таблицы "выпали" из рассматриваемых.

Комментарии

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

Комментарии

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   следующая ›   последняя »

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