Примеры с SQL

Вчера наткнулся на неожиданное для меня поведение 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 

Функция для проверки контрольного разряда кода ОКПО. Проверяются как 8-ми, так и 10-ти значные коды, при необходимости автоматически добавляются ведущие нули. Если ОКПО правильный, то функция возвращает 0, иначе 1.

CREATE FUNCTION dbo.Check_OKPO (@OKPO VARCHAR(11))
RETURNS INT
AS
BEGIN
  DECLARE @I INT, @S INT, @SUMMA INT
  SET @OKPO = LTRIM(RTRIM(@OKPO))
  IF (@OKPO IS NULL OR @OKPO = '') RETURN(1)
  IF LEN(@OKPO) < 8 
    SET @OKPO = RIGHT('00000000' + @OKPO, 8)
  IF LEN(@OKPO) > 8 
    SET @OKPO = RIGHT('00' + @OKPO, 10)
  SET @I = 1
  SET @SUMMA = 0
  WHILE @I < LEN(@OKPO)
  BEGIN
    SET @SUMMA = @SUMMA + CAST(SUBSTRING(@OKPO, @I, 1) AS INT) * @I
    SET @I = @I + 1
  END
  SET @S = @SUMMA % 11
  IF @S = 10 SET @S = 0
  IF @S <> CAST(RIGHT(@OKPO, 1) AS INT) RETURN(1)
  RETURN(0)
END

SELECT CAST(FLOOR(CAST(GetDate() as float)) as Datetime)

UPD: Недавно наткнулся на другое решение:

-- Округлить дату
declare @date datetime
set @date=CONVERT(varchar(8), GETDATE(), 112)
-- Изъять время из даты
declare @time datetime
set @time=CONVERT(varchar(8), GETDATE(), 108)

-- Количество строк во ВСЕХ таблицах
create table #svv_tab_row_Count (ID int IDENTITY not null, Name varchar(100), col int)
declare @i int, @j int
declare @t varchar(100)
insert into #svv_tab_row_Count (Name)
select Table_Name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='dbo' and TABLE_TYPE='BASE TABLE'
SET @i = 1
SET @j = 1
WHILE (@j < 2)
BEGIN
 IF NOT EXISTS(select * from #svv_tab_row_Count where ID=@i) SET @j = 2
 ELSE
 BEGIN
	select @t = Name from #svv_tab_row_Count where ID=@i
  exec ('update #svv_tab_row_Count set col=(select Count(*) from ['+@t+']) where id='+@i+'')
 end
 SET @i = @i + 1
END
select * from #svv_tab_row_Count
drop table #svv_tab_row_Count

UPD: Обновил данный запрос - добавил информацию по размеру данных и индексов, их сумму и отношение (подробнее здесь)

Часто, при разработке приложений возникает необходимость предоставить возможность выбора базы данных. При этом будет красиво, если в списке не будет системных и отключенных (остановленных) баз. Со вторым все очень просто: в статусе проверяем нужный бит на наличие, и если он установлен, то база остановлена (первое условие в примере). А вот определения именно системности базы я не нашел. Поэтому предлагаются следующие решения (для второго условия):

• Проверять поле sid - здесь кодируется владелец (Owner) базы, так вот для пользователя sa он равен 1. У нас только системные таблицы имеют пользователя sa, поэтому это решение нам подошло.
• Проверять поле dbid - идентификатор базы, для несистемных он более 5 (на нашем сервере), Думаю на других тоже. Так что выбирайте, что больше нравится.
• Исключить системные базы по именам. Мне этот подход совсем не нравится.

Я знаю, что в Books-OnLine рекомендуют пользоваться INFORMATION_SCHEMA, а не прямым обращением в системные таблицы, но в данном случае INFORMATION_SCHEMA.SCHEMATA работает скажем так "странно".
Поэтому решение было выбрано такое:

SELECT * FROM sysdatabases WHERE status&100000=0 AND sid<>1 ORDER BY name

Цикл один из методов наглядной проверки скорости выполнения SQL-запроса. Лучше, конечно, использовать Profiler или анализировать план выполнения, но часто быстрее и проще "наглядная проверка". Итак, имеем два запроса. Надо определить какой из них предпочтительнее по скорости выполнения. Пишем два цикла в каждом, по одному запросу. Прогоняем каждый два-три раза и определяем среднюю скорость выполнения. Где меньше время, тот и быстрее, хотя не факт, что лучше. Количество выполнений запросов в цикле следует подбирать таким, чтобы время выполнения было около 20 секунд.

DECLARE @i INT
SET @i = 1

WHILE (@i < 500)
BEGIN
// здесь пишется выполняемый в цикле код
SET @i = @i + 1
END

 ADOQuery1.Close;
 ADOQuery1.SQL.Text:='SELECT * FROM TAB1 WHERE DATE>='+
                     QuotedStr(FormatDateTime('yyyy-mm-dd 00:00:00', dtpFrom.Date))+
                     ' AND DATE<='+
                     QuotedStr(FormatDateTime('yyyy-mm-dd 23:59:59', dtpTo.Date));
 ADOQuery1.Open;

Часто в SQL-запросах необходимо сделать выборку по дате, и тут многие сталкивались, наверное, с проблемой что по разному настроенные MS SQL сервера "ожидают" дату в немного разном формате. Я в своих программах использую выше приведённый пример, и с проблемами пока не сталкивался.
dtpFrom и dtpTo это компоненты типа TDateTimePicker с датой начала периода и конца периода соответственно.

Часто необходимо обеспечить возможность выбора строк из Таблицы MS SQL при многопользовательской работе. Т.е. первый редактирует в транзакции, а второй в это время читает предыдущий вариант.

 ADOQuery1.Connection.IsolationLevel := ilReadUncommitted;
 ADOQuery1.Connection.Execute('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');
 ADOQuery1.SQL.Text:='...';
 ADOQuery1.Open;