SQL Server


Об индексах и о том какие индексы нужны для запросов

Для чего нужны индексы? У индексов таблиц в РСУБД два назначения – используются для эффективного поиска и для сортировки.

Сегодня хочу рассказать о том, каким образом устроены индексы в реляционных базах данных, в частности в Microsoft SQL Server. Сразу оговорюсь, что этот материал для начального уровня, здесь я не буду рассматривать структуру B-Tree и многие глубокие вещи. В этом обзоре предлагаю на начальном уровне разобраться с базовыми понятиями индексов. В обзоре будут примеры алгоритмов, поэтому у тебя, для понимания, ты должен знать, что такое сортировка, разница в поиске в сортированном и несортированном массивах. В этом обзоре я буду проводить ассоциации со структурами и массивами – как аналог таблиц в реляционных СУБД.

Итак – поехали.читать дальше...
добавлено: 08 авг 17 просмотры: 1155, комментарии: 1



Сборка КБД

Постановка задачи

В фирме есть 80 филиалов в 80 городах по РФ с одинаковой по структуре базой данных. В базах данных филиалов порядка 150 таблиц с одинаковым названием, но с разным, своим наполнением, за исключением общих справочников. Все филиалы имеют индивидуальные 2-значные номера. Для каждого филиала выделен свой SQL Server 2008R2 Enterprise edition (x64). Базы данных в филиалах имеют одинаковое название. Филиалы различаются разными IP-адресами и доменными именами. Все сервера БД объединены в единую сеть и единый домен (Active Directory) предприятия. Данные в таблицах филиалов могут как добавляться, так и изменяться и удаляться, изменения могут происходить и за долгий срок от текущего момента времени.

Есть отдельный сервер, где реализована еженедельная сборка данных со всех филиалов в единую корпоративную базу данных (далее будем называть эту БД — ЕКБД по следующему алгоритму:

  • Из файла бэкапа восстанавливается пустая ЕКБД без индексов, но со всеми таблицами и процедурами.
  • С помощью bcp.exe и job SQL Server в ЕКБД загружаются данные в 8 параллельных потоков со всех филиалов.
  • Первичные ключи — идентификаторы в таблицах при загрузке преобразуются по принципу добавления номера филиала в начало. Например, на филиале 17 загружаем с ID = 177, этот ключ в ЕКБД будет — 1700000000177. Т.е. код филиала умножается на 10^N + ID.
  • После загрузки последнего job стартует построение необходимых индексов для работы.
  • Весь процесс загрузки выполняется порядка 1.5-2 суток, при этом ЕКБД во время сборки не доступна.
    читать дальше...
  • добавлено: 16 июл 17 просмотры: 991, комментарии: 0



    Остатки на складах - оптимизация, когда нужна и сортировка и поиск.

    Остатки на складах

    Постановка задачи

    Задачу необходимо решить на SQL Server 2014 Enterprise Edition (x64).
    В фирме есть много складов. В каждом складе ежедневно по нескольку тысяч отгрузок и приемок продуктов. Есть таблица движений товаров на складе приход/расход. Необходимо реализовать:

    Расчет баланса на выбранную дату и время (с точностью до часа) по всем/любому складам по каждому продукту.
    Для аналитики необходимо создать объект (функцию, таблицу, представление) с помощью которого за выбранный диапазон дат вывести по всем складам и продуктам данные исходной таблицы и дополнительную расчетную колонку — остаток на складе позиции.читать дальше...
    добавлено: 30 июн 17 просмотры: 1747, комментарии: 2



    Перенос логинов между Microsoft SQL серверами.

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

    Надеюсь еще кому пригодится.

    + На мой взгляд попроще запрос

    -- здесь рекомендую нажать Ctrl+T в Management Studio перед выполнением ))
    select
    	'create login ['+name+'] with
    	password = '+convert(varchar(max),password_hash,1)+' hashed,
    	sid = '+convert(varchar(max),sid,1)+',
    	default_database = ['+default_database_name+'],
    	default_language = '+default_language_name+',
    	check_expiration = '+iif(is_expiration_checked=0,'off','on')+',
    	check_policy = '+iif(is_policy_checked=0,'off','on')+'
    go
    alter login ['+name+'] '+case when is_disabled=1
    	then 'disable' else 'enable'
    end+'
    go' as SQL_COMMAND
    from sys.sql_logins
    where sid != 0x01
    and name not like '##%'
    
    union all
    
    select 'create login ['+name+'] from windows with
    	default_database = ['+default_database_name+'],
    	default_language = '+default_language_name+'
    go
    alter login ['+name+'] '+case when is_disabled=1
    	then 'disable' else 'enable'
    end+'
    go'
    from sys.server_principals
    where type = 'U'
    and name not like 'NT AUTHORITY\%'
    and name not like 'NT Service\%'
    
    union all
    
    select
    	'alter server role '+txt+' add member ['+loginname+']
    go'
    from sys.syslogins l
    cross apply
    (values
    	(denylogin,'denylogin'),
    	(sysadmin,'sysadmin'),
    	(securityadmin,'securityadmin'),
    	(serveradmin,'serveradmin'),
    	(setupadmin,'setupadmin'),
    	(processadmin,'processadmin'),
    	(diskadmin,'diskadmin'),
    	(dbcreator,'dbcreator'),
    	(bulkadmin,'bulkadmin')
    ) o (opt, txt)
    where sid != 0x01
    and name not like '##%'
    and name not like 'NT AUTHORITY\%'
    and name not like 'NT Service\%'
    and opt != 0
    


    + А этот немного модифицированный, не во всех ManagementStudio перенос строки копипастится нормально

    with cte (num, query, a,b,c,d,e,f,g,h,i,j) as
    (
    select
    row_number() over (order by name), 1,
    'create login ['+name+'] with',
    '	password = '+convert(varchar(max),password_hash,1)+' hashed,',
    '	sid = '+convert(varchar(max),sid,1)+',',
    '	default_database = ['+default_database_name+'],',
    '	default_language = '+default_language_name+',',
    '	check_expiration = '+iif(is_expiration_checked=0,'off','on')+',',
    '	check_policy = '+iif(is_policy_checked=0,'off','on'),
    'go',
    'alter login ['+name+'] '+case when is_disabled=1 
    	then 'disable' else 'enable'
    end,
    'go'
    from sys.sql_logins
    where sid != 0x01
    and name not like '##%'
    
    union all
    
    select
    row_number() over (order by name), 2,
    'create login ['+name+'] from windows with',
    '	default_database = ['+default_database_name+'],',
    '	default_language = '+default_language_name,
    'alter login ['+name+'] '+case when is_disabled=1
    	then 'disable' else 'enable'
    end,'go',null,null,null,null,null
    from sys.server_principals
    where type = 'U'
    and name not like 'NT AUTHORITY\%'
    and name not like 'NT Service\%'
    
    union all
    
    select
    row_number() over (order by name), 3,
    	'alter server role '+txt+' add member ['+loginname+']'
    	,'go',null,null,null,null,null,null,null,null
    from sys.syslogins l
    where sid != 0x01
    and name not like 'NT AUTHORITY\%'
    and name not like 'NT Service\%'
    cross apply
    (values
    	(denylogin,'denylogin'),
    	(sysadmin,'sysadmin'),
    	(securityadmin,'securityadmin'),
    	(serveradmin,'serveradmin'),
    	(setupadmin,'setupadmin'),
    	(processadmin,'processadmin'),
    	(diskadmin,'diskadmin'),
    	(dbcreator,'dbcreator'),
    	(bulkadmin,'bulkadmin')
    ) o (opt, txt)
    where sid != 0x01
    and name not like '##%'
    and name not like 'NT AUTHORITY\%'
    and name not like 'NT Service\%'
    and opt != 0
    )
    select
    	o.command
    from cte
    cross apply
    (values
    	(num,'a',a),
    	(num,'b',b),
    	(num,'c',c),
    	(num,'d',d),
    	(num,'e',e),
    	(num,'f',f),
    	(num,'g',g),
    	(num,'h',h),
    	(num,'i',i),
    	(num,'j',j)
    ) o (num,ord,command)
    where o.command is not null
    order by cte.query, o.num, o.ord
    

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

    Материал взят с сайта автора.
    добавлено: 20 фев 16 просмотры: 1245, комментарии: 0



    Резервное копирование в Microsoft SQL Server средствами Transact SQL.

    Руководство предназначено для начинающих администраторов БД, в нем если будем касаться смежных, но важных тем, то эти смежные темы мы здесь не будем полностью рассматривать. Материал разбит на 5 глав, от простого к сложному.читать дальше...
    добавлено: 25 янв 16 просмотры: 2517, комментарии: 0



    Вывод дерева блокировок на T-SQL

    Предлагаю вашему вниманию самописную хранимую процедуру, с помощью которой можно оперативно посмотреть на SQL Server 2008 (и версией выше) наличие заблокированных процессов. Процедура отображает все заблокированные и блокирующие процессы на момент ее вызова в виде дерева.читать дальше...
    добавлено: 15 апр 14 просмотры: 2507, комментарии: 2



    Реализация инкрементной загрузки с использованием Change Data Capture в SQL Server.

    Эта статья будет интересна, прежде всего, тем, кому часто приходится сталкиваться с задачами интеграции данных.читать дальше...
    добавлено: 20 июн 13 просмотры: 3295, комментарии: 0



    Отображение древовидных структур на T-SQL.

    Тема рекурсии достаточно хорошо освещена в литературе, но, тем не менее, задача вывода «дерева» не средствами клиента, а SQL Server многих ставит в тупик. читать дальше...
    добавлено: 20 июн 13 просмотры: 3928, комментарии: 2