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

ещё

Новые комментарии
23.11.2024 17:09
swi
06.11.2024 22:04
Лена (отвечено)
23.04.2024 19:33
Мой комментарий

ещё

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

Примеры с SQL

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

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

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

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

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

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

  1. 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) #

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

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

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

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

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

Довольно часто, по мере роста размера базы данных, возникает вопрос - "Откуда?" Тут на помощь придёт обновлённая версия моего запроса, которая формирует по-табличный отчёт о занимаемом пространстве. В полученной таблице прежде всего следует обратить внимание на колонку TotalSize с общим объёмом занимаемым каждой таблицей. Также может оказаться полезной колонка IdxDivData, отображающая соотношение объёма всех индексов таблице к объёму её данных. Таблицы, для которых он более 1, и общий объём значителен точно следует рассмотреть внимательнее.

  1. IF ( OBJECT_ID('tempdb..#svv_tab_row_Count'IS NOT NULL)  
  2.   DROP TABLE #svv_tab_row_Count  
  3.     
  4. CREATE TABLE #svv_tab_row_Count (  
  5.     ID INT IDENTITY not null,   
  6.     Name NVARCHAR(255),  
  7.     RowsCount INT,   
  8.     DataSize INT,   
  9.     IdxSize INT,   
  10.     TotalSize INT,  
  11.     IdxDivData MONEY)    
  12.       
  13. DECLARE @tableName NVARCHAR(255)  
  14.   
  15. DECLARE tableCursor CURSOR for  
  16.   SELECT TABLE_SCHEMA + '.' + TABLE_NAME  
  17.   FROM INFORMATION_SCHEMA.TABLES  
  18.   WHERE TABLE_TYPE = 'BASE TABLE'    
  19.   
  20. OPEN TableCursor  
  21. FETCH NEXT FROM tableCursor INTO @tableName  
  22.   
  23. WHILE @@fetch_status=0  
  24. BEGIN  
  25.     INSERT INTO #svv_tab_row_Count  
  26.         (RowsCount, DataSize, IdxSize)  
  27.     EXEC sp_MStablespace @tableName  
  28.    
  29.     UPDATE #svv_tab_row_Count  
  30.     SET NAME = @tableName,  
  31.         TotalSize = DataSize + IdxSize,  
  32.         IdxDivData = CASE WHEN DataSize = 0 THEN 0 ELSE IdxSize * 1.0 / DataSize END  
  33.     WHERE ID = IDENT_CURRENT('#svv_tab_row_Count')      
  34.       
  35.     FETCH NEXT FROM tableCursor INTO @tableName  
  36. END  
  37. CLOSE tablecursor  
  38. DEALLOCATE tablecursor  
  39. GO  
  40. SELECT * FROM #svv_tab_row_Count ORDER BY TotalSize DESC   

У нас с проекте нашлись таблицы этим коэффициентом большим 2 и, иногда, даже 5. Выяснилось, что остались неиспользуемые более индексы, после удаления которых занимаемый объём значительно сократился, и данные таблицы "выпали" из рассматриваемых.

Комментарии

Андрей (12.01.2014)
Большое спасибо!
Прекрасно работает
Небольшая доработка в отношении схем:
  1. CREATE PROCEDURE [UTILS].pTableSize  
  2. AS  
  3. BEGIN  
  4.   IF ( OBJECT_ID('tempdb..#svv_tab_row_Count'IS NOT NULL)    
  5.     DROP TABLE #svv_tab_row_Count    
  6.       
  7.   CREATE TABLE #svv_tab_row_Count (    
  8.       ID INT IDENTITY not null,  
  9.       SchemaName NVARCHAR(256),   
  10.       TableName NVARCHAR(256),    
  11.       RowsCount INT,     
  12.       DataSize INT,     
  13.       IdxSize INT,     
  14.       TotalSize INT,    
  15.       IdxDivData MONEY);  
  16.         
  17.   DECLARE  
  18.     @SchemaName NVARCHAR(256),  
  19.     @TableName NVARCHAR(256),  
  20.     @TablePath NVARCHAR(512);  
  21.     
  22.   DECLARE TableCursor CURSOR FOR    
  23.     SELECT TABLE_SCHEMA, Table_Name  
  24.     FROM INFORMATION_SCHEMA.TABLES    
  25.     WHERE TABLE_TYPE = 'BASE TABLE'      
  26.     
  27.     OPEN TableCursor    
  28.     FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName  
  29.     
  30.     WHILE @@fetch_status = 0 BEGIN  
  31.         SET @TablePath = @SchemaName + '.' + @TableName  
  32.         INSERT INTO #svv_tab_row_Count    
  33.         (RowsCount, DataSize, IdxSize)    
  34.         EXEC sp_MStablespace @TablePath  
  35.      
  36.         UPDATE #svv_tab_row_Count  
  37.         SET  
  38.         TableName = @TableName,  
  39.         SchemaName = @SchemaName,  
  40.         TotalSize = DataSize + IdxSize,    
  41.         IdxDivData = CASE WHEN DataSize = 0 THEN 0 ELSE IdxSize * 1.0 / DataSize END    
  42.         WHERE ID = IDENT_CURRENT('#svv_tab_row_Count')        
  43.         
  44.         FETCH NEXT FROM tableCursor INTO @SchemaName, @TableName  
  45.         END;  
  46.   
  47.     CLOSE tablecursor;    
  48.     DEALLOCATE tablecursor;    
  49.   
  50.     SELECT *  
  51.   FROM #svv_tab_row_Count  
  52.   ORDER BY TotalSize DESC  
  53.   
  54. END  
Спасибо за предоставленное решение.
Рад, что моя разработка пригодилась.

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

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

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

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

Недостатки:

  • Резервная копия делается непосредственно на сервер, а часто и на тот же RAID, где лежит база, т.е. если произойдет сбой на уровне дисков, то такой архив не поможет.
  • Созданные архивы автоматически не удаляются, поэтому со временем возможно исчерпание всего свободного объема на диске.
  • Данный запрос генерирует название файла, учитывая создание только одного архива в день. Если необходимо большее количество, то необходимо изменить значение переменной @st для добавлении времени:
  1. set @st = 'D:BACKUPDATABASE_NAMEDATABASE_NAME_' +  
  2.           CONVERT(varchar(15), GetDate(), 112)+ '_' +REPLACE(CONVERT(varchar(15), GetDate(), 108), ':''')  

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

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

@@IDENTITY и SCOPE_IDENTITY (03.12.2009) #

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

  1. SELECT @@IDENTITY  

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

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

Комментарии

roamer (21.05.2013)
Как вариант, можно (в дополнение к "стандартному" автоинкрементному ключу) создать доп. поле (varchar).
И идентифицировать (по GUID) строку по этому полю:
  1. create procedure [dbo].[Str_GUID]  
  2. (  
  3.   @sGUID varchar(50) OUTPUT  
  4. )  
  5. AS  
  6. BEGIN  
  7. /*  
  8. Вычислить GUID и вернуть его строковое представление (без разделителей)  
  9. Пример:  
  10.   DECLARE @Sx varchar(50)  
  11.   Execute dbo.Str_GUID @Sx OUTPUT  
  12.   select @Sx, len(@Sx)  
  13. */  
  14.   DECLARE  @myid uniqueidentifier  
  15.   SET @myid = NEWID()  
  16.   set @sGUID=cast(@myid as varchar(50))  
  17.   set @sGUID=UPPER(rtrim(ltrim(REPLACE(@sGUID , '-'''))))  
  18.   RETURN  
  19. END  
Это может быть полезным, когда нужно идентифицировать строки таблиц из разных баз данных.
Спасибо за комментарий, но хотел бы дополнить:
не уверен, что Guid преобразованный в строку будет хорошо работать с точки зрения производительности, индексов и т.п. К тому же мне не понятно, как будет работать Ваше решение при вставке нескольких строк сразу (так называемый "INSERT FROM SELECT").
Думаю, в данном случае более удачным будет решение:
  1. CREATE TABLE MyUniqueTable  
  2. (UniqueColumn UNIQUEIDENTIFIER DEFAULT NEWID(),  
  3. Characters VARCHAR(10) )  
  4.   
  5. INSERT INTO MyUniqueTable(Characters) VALUES ('abc')  
  6.   
  7. SELECT * FROM MyUniqueTable  
Постараюсь разместить здесь придуманное и реализованное у нас решение, которое позволяет получать не пересекающиеся уникальные ключи, с привязкой к отдельным серверам.

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

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

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

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

Комментарии

(13.10.2014)
Можно и таким путем:
  1. SELECT t1.id+1  
  2. FROM tabl AS t1  
  3. LEFT JOIN tabl AS t2 ON t1.id+1 = t2.id  
  4. WHERE t2.id IS NULL  
  5. ORDER BY t1.id  

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

Вчера наткнулся на неожиданное для меня поведение MS SQL 2005. Задача была простая: из суммы выделить размер НДС. И хотя я знаю, что чисто математически эта задача неразрешима, но что поделаешь сказали надо. Пишу запрос:

  1. SELECT 24000 * (1 - 1 / 1.18) as NDS   

И получаю значение 3661,032, которое немного (на несколько копеек) не сходится со значением, посчитанным на калькуляторе – 3661,016949. После некоторых экспериментов выяснилось, что MS SQL округляет значение коэффициента 1 / 1.18 до 6 знаков после запятой, и похоже что с бухгалтерским округлением. То есть получается 0,847457 вместо 0,84745762711, что при больших суммах даёт приличную погрешность.

Решить удалось чётким указанием типа, так:

  1. SELECT 24000 * (1 - CAST(1 as NUMERIC(15,8))/CAST(1.18 as NUMERIC(15,8))) as NDS  

Или:

  1. SELECT 24000 * (1 - 1/CAST(1.18 as NUMERIC(15,8))) as NDS    

Или так:

  1. DECLARE  @i NUMERIC (15, 8), @n NUMERIC (15, 8)  
  2. SELECT @i = 1, @n = 1.18  
  3. 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   следующая ›   последняя »

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