СОДЕРЖАНИЕ

1. СТАТЬИ 1.1. Применение коррелированных подзапросов в T-SQL 2. ССЫЛКИ НА СТАТЬИ 2.1. Статьи на русском языке 2.2. Англоязычные статьи 3. ФОРУМ SQL.RU 3.1. Самые популярные темы недели 3.2. Вопросы остались без ответа 4. КНИГИ 4.1. Работа с базами данных в Delphi 5. АНОНСЫ 5.1. Журнал "SQL Server для профессионалов" 5.1. Журнал "Алгоритм", первый номер

СТАТЬИ

Применение коррелированных подзапросов в T-SQL

По материалам статьи Gregory A. Larsen: Using a Correlated Subquery in a T-SQL Statement
Перевод Алексея Сафонова

В прошлом месяце в своей статье я рассматривал вопрос применения подзапросов в операторах T-SQL. В этот раз я более подробно остановлюсь на этой теме, и рассмотрю коррелированные подзапросы. Я объясню, что же представляет собой коррелированный подзапрос, и на нескольких примерах продемонстрирую его возможности.

Что такое - Коррелированный Подзапрос?

Коррелированный подзапрос - это оператор SELECT, вложенный в другой оператор T-SQL, и ссылающийся на один или несколько столбцов внешнего запроса. Поэтому можно сказать, что коррелированный подзапрос зависит от внешнего запроса. Это - главное различие между коррелированным и простым подзапросом. Простой подзапрос не ссылается на внешний запрос, он может быть выполнен независимо от него. После того как коррелированный подзапрос будет связан с внешним запросом, он будет возвращать сообщение о синтаксической ошибке, если попытается вызвать самого себя.
Коррелированный подзапрос может быть исполнен несколько раз в процессе обработки оператора T-SQL, содержащего такой подзапрос. Он будет исполняться для каждой строки, отобранной во внешнем запросе. На каждом из этих шагов поля внешнего запроса, на которые ссылается коррелированный подзапрос, будут сравниваться с результатами выборки коррелированного подзапроса. Результат выполнения коррелированного подзапроса определит, попадет ли строка внешнего запроса в результирующую выборку.

[В начало]

Применение коррелированного подзапроса в условии WHERE

Предположим, что Вы хотите получить список всех OrderID, для которых покупатели приобрели не больше 10% от среднего объёма продаж каждого из товаров. Подобный анализ покажет тех покупателей, с которыми нужно связаться, чтобы выяснить причину столь низкого интереса к приобретённому товару. Для этих целей можно использовать коррелированный подзапрос, который будет помещён в предложении WHERE. Вот запрос, который вернет интересующий нас список товаров:

select distinct OrderId
  from Northwind.dbo.[Order Details] OD
  where 
    Quantity <= (select avg(Quantity)  *  .1 
                      from Northwind.dbo.[Order Details] 
                      where OD.ProductID = ProductID)

В приведённом выше запросе коррелированный подзапрос располагается в круглых скобках. Как Вы могли заметить, этот коррелированный подзапрос содержит ссылку на "OD.ProductID". Эта ссылка участвует в сравнении "ProductID" внешнего запроса с "ProductID" внутреннего запроса. Движок SQL Server будет исполнять внутренний запрос (коррелированный подзапрос) для каждой записи "[Order Details]". Этот внутренний запрос подсчитает среднее количество (Quantity) для записей каждого товара (ProductID), отобранных во внешнем запросе. Средствами коррелированного подзапроса будет определено, возвращает ли внутренний запрос значение, удовлетворяющее условию WHERE. Если да, то строка, возвращенная внешним запросом, будет включена в итоговую выборку всего запроса T-SQL.

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

select CompanyName, ContactName, Address,
       City, Country, PostalCode from Northwind.dbo.Customers OuterC
where CustomerID in ( 
select top 2 InnerC.CustomerId
     from Northwind.dbo.[Order Details] OD join Northwind.dbo.Orders O
               on OD.OrderId = O.OrderID
          join Northwind.dbo.Customers InnerC
               on O.CustomerID = InnerC.CustomerId
     Where Region = OuterC.Region
     group by Region, InnerC.CustomerId
     order by sum(UnitPrice * Quantity * (1-Discount)) desc
     ) 
order by Region

Как видно из примера, внутренний запрос - коррелированный, потому что он ссылается на "OuterC", псевдоним использующейся во внешнем запросе таблицы "Northwind.DBO.Customer". Внутренний запрос использует значения поля "Region" для определения двух лучших покупателей в каждом регионе, ассоциированных со строкой внешнего запроса. В результирующую выборку попадут записи о тех двух "CustomerID" из внешнего запроса, которые попадут в число лучших покупателей.

[В начало]

Коррелированный подзапрос в разделе HAVING

Допустим, в целях увеличения своего дохода ваша организация решила в течение года проводить акцию по стимуляции потребительского спроса. Для этого покупатели извещаются о том, что если каждый сделанный ими в течение года заказ будет превышать сумму 750$, то в конце года на каждый их заказ вы сделаете скидку в 75$. Ниже приведен пример вычисления размера скидки. В этом примере для определения подпадающих под условие получения скидки покупателей, используется коррелированный подзапрос, помещённый в раздел HAVING.

select C.CustomerID, Count(*)*75 Rebate
  from Northwind.DBO.Customers C
         join
       Northwind.DBO.Orders O
         on c.CustomerID = O.CustomerID
  where Datepart(yy,OrderDate) = '1998'
  group by C.CustomerId
  having 750 < ALL(select sum(UnitPrice * Quantity * (1-Discount)) 
           from Northwind.DBO.Orders O
                  join
                Northwind.DBO.[Order Details] OD
                  on O.OrderID = OD.OrderID
           where CustomerID = C.CustomerId 
             and Datepart(yy,OrderDate) = '1998'
           group by O.OrderId
            )

Как Вы можете заметить, имеющийся в разделе HAVING коррелированный запрос используется для того, чтобы вычислить сумму заказа для каждого заказа клиента. Из внешнего запроса выбираются "CustomerID" и "Datepart (yy, OrderDate)" - год, когда был сделан заказ - которые нужны для того, что бы отобрать те заказы клиента, которые были сделаны в 1998 году. Для отобранных таким образом записей вычисляется сумма покупки по каждому заказу, для чего суммируются все записи "[Order Details]" по такой формуле: sum(UnitPrice * Quantity * (1-Discount)). Если каждая сделанная в 1998 покупка клиента превышает в сумме 750$, тогда во внешнем запросе вычисляется размер скидки по формуле: "Count (*) * 75".
Процессор запросов SQL Server выполнит помещённый в разделе HAVING внутренний коррелированный подзапрос для всех отобранных внешним запросом покупателей, которые делали заказы в 1998 году.

[В начало]

Применение коррелированного подзапроса в оператора Update

Коррелированный подзапрос также может использоваться и в операторе Update:

create table A(A int, S int)
create table B(A int, B int)
 
set nocount on 
insert into A(A) values(1)
insert into A(A) values(2)
insert into A(A) values(3)
insert into B values(1,1)
insert into B values(2,1)
insert into B values(2,1)
insert into B values(3,1)
insert into B values(3,1)
insert into B values(3,1)
 
update A 
  set S = (select sum(B)
             from B 
             where A.A = A group by A)
 
select * from A
 
drop table A,B

В результате мы получим:

A           S           
----------- ----------- 
1           1
2           2
3           3

В приведенном выше запросе коррелированный подзапрос используется для замены значений в столбце S таблицы А на сумму столбца B таблицы B , для тех строк, которые имеют одинаковые значения столбцов А, как в используемых для суммирования, так и в обновляемых строках.

[В начало]

Заключение

Давайте теперь подведём небольшой итог в этой статье. Подзапрос и коррелированный подзапрос - это операторы SELECT, используемые в другом запросе, называемом внешним. Коррелированный подзапрос и простой подзапрос очень полезны в оформлении выборки данных. Подзапрос, когда он выполняется независимо от внешнего запроса, возвращает выборку, которая также будет независимой от внешнего запроса. В свою очередь, коррелированный подзапрос не может исполняться независимо от внешнего запроса, потому что он ссылается на один или несколько столбцов внешнего запроса. Надеюсь, что теперь вы понимаете разницу между обычным и коррелированным подзапросом, и как их можно применять в T-SQL.

[В начало]

ССЫЛКИ НА СТАТЬИ

Статьи на русском языке

SQL Server: цены растут, но есть версия для рабочих групп
Поль Тюрро
MSSQLServer: Неоднократно переносившийся срок начала поставок семейства продуктов Microsoft SQL Server 2005 наконец-то определен окончательно: середина 2005 года. Объявив об этом, представитель Microsoft отметил, что будет предложено новое семейство продуктов и по более высоким ценам. Для начала Microsoft добавила сервер баз данных для рабочих групп - SQL Server Workgroup Edition. Но этот продукт не будет дожидаться появления SQL Server 2005, вместо этого компания запускает SQL Server 2000 Workgroup Edition уже этой весной, раньше SQL Server 2005...
Дебаты вокруг открытых баз данных
Брайан Моран
MSSQLServer: Есть ли смысл использовать открытые базы данных? Выражаясь точнее, разумно ли выбирать базу данных с открытым исходным кодом вместо SQL Server, если основной проблемой является стоимость приобретения технологии? По этому вопросу хочется поделиться некоторыми соображениями. Такое желание возникло у меня после прочтения ряда статей, посвященных открытым базам данных, таким как MySQL или Ingres, с описанием их преимуществ по сравнению с технологиями, за которые приходится платить...
Данные, их представление и формы пользовательского интерфейса
Дмитрий Шейко
XML: XML за последние годы обрел заслуженную популярность, и уже нет сомнений в перспективах его повсеместного применения. Понятие "семантический веб" перестает быть абстракцией и уверенно воплощается в жизнь. В информационной какофонии всемирной сети постепенно начинает узнаваться гармония. В руках талантливых дирижеров, таких как W3C, ISO, OASIS, она вполне может превратиться в симфонию...
ADO и XML
Дэн Фокс
ADO: Рассказано о том, как извлекать данные XML из хранимых процедур, выполнять шаблоны XML и обращаться с запросами XPath к представлениям XML...
Что надо знать о ERP-системах
ERP
CASE: Внедрив ERP-систему, предприятие получает современное комплексное решение для всей производственной цепочки: от закупки сырья до реализации конечной продукции, а также возможность объединения таких сложных процедур, как работа с персоналом филиалов компании, расположенных в разных точках земного шара. Лидирующие позиции среди полномасштабных ERP-систем занимают MFG/PRO компании QAD и iRenaissance компании ROSS Systems. Обе эти системы успешно работают на тысячах предприятиях в десятках стран мира...

[В начало]

Англоязычные статьи

Designing Cross Database Queries
Steve Jones
I've been working with multiple databases recently in trying to tie a few applications together and share data without using replication, DTS, or some other data movement mechanism. I wanted to keep the data within the databases that it resides and perform some cross database work within applications. In doing so, I realized there were a few ways of doing this and came to some conclusions about the best ways to handle this
SQL Building Blocks and Server Settings
Deac Lancaster
This chapter covers the building blocks available to the database designer and database user to create and modify database objects and data. The SQL Data Types will be covered along with data representation as literals (constants) and expressions
XML and the SQL 2000 Server, Part 1
Jayaram Krishnaswamy
In this second article in a series, IIS XML support for SQL 2000 Server is explored
Inside SQL Server 2005
Tom Rizzo
Tom Rizzo, the director of product management for Microsoft SQL Server, talks about Reporting Services, SQL Server 2005, and more. Note: This is a transcript of an interview conducted at VSLive! San Francisco
Performance Tips Part 1
Leo Peysakhovich
There is no shortage of articles and books about improving performance of a database overall, SQL statements, and other types of performance. For example, http://www.mssqlcity.com/Tips/tipTSQL.htm , or http://www.sql-server-performance.com/transact_sql.asp
Simple COM registration using a side-by-side method
Phil Wilson
When .NET first appeared it wasn't unusual to hear the question "Is COM dead?" In fact, COM seems to be alive and well, and in this article I'll look at the way that Windows XP and Windows Server 2003 allow you to use COM in a side-by-side way without installing the usual registration entries – you won't need to use the Windows registry to use side-by-side COM components
Changing XML Schema attached in SQL Server 2005
ExtremeExperts.com
XML has become an first class datatype in SQL Server 2005 . You can check my previous articles for an idea of XML features introduced. In this article we will take a look at how we can alter a XML Schema attached to an Typed XML data. XML's can be caegorized into two categories, typed and un-typed. Any XML that conforms to an XML schema is called as typed. And on the contrary plain XML strings are called as un-typed
Quick! Which specific SQL Server user is logged into which database?
Serdar Yegulalp
SQL Server's master database stores tables with important and constantly updated information about the state of the SQL server. One of these tables, sysprocesses, can be used to track (in SQL Server itself) which SQL Server users are currently logged in and which database(s) they are accessing
MDX Essentials: Set and String Functions: The GENERATE() Function
William Pearson
In this lesson, we will introduce a function that provides us the ability to derive a set by systematically applying a set expression to each of the members of a set we define. GENERATE() is a potent function in our MDX toolset, in that it enables us to select precisely only certain members of a dimension level. It effectively operates upon two sets to create a new set, based upon the members of a second set that are also in a primary set
Calendar in SQL Server 2000
Arun
Build one calander in SQLSERVER used Functions. The function shows below
Access Controls in SQL
Mike Chapple
Security is paramount to database administrators seeking to protect their gigabytes of vital business data from the prying eyes of unauthorized outsiders and insiders attempting to exceed their authority. All relational database management systems provide some sort of intrinsic security mechanisms designed to minimize these threats. They range from the simple password protection offered by Microsoft Access to the complex user/role structure supported by advanced relational databases like Oracle and Microsoft SQL Server. This article focuses on the security mechanisms common to all databases that implement the Structured Query Language (or SQL). Together, we'll walk through the process of strengthening data access controls and ensuring the safety of your data
Tips for Hardware Tuning (SQL Server)
Kevin Kline
I'm presenting a session at the upcoming SQL Connections event (www.sqlconnections.com) called ‘Bare Metal Tuning’. The idea behind the session is that I describe how to configure an ‘ideal’ SQL Server from the bare metal server on up. In this session, I point out actual quantitative performance differences between the different options available to your. For example, you’ve probably already heard that when choosing RAID, mirroring (RAID1) performs better than striping with parity (RAID5). This presentation actually tells you in relative terms how much performance improvement you can expect to get, based on the comparison of a baseline server configuration, and the ‘upgraded’ server configuration
Performance Tuning SQL Server Hardware
SQL-Server-Performance.Com
Although heavy-duty hardware can help SQL Server's performance, application and database design can play a greater part in overall performance than hardware. Keep this in mind, as throwing good money after bad on server hardware does not always fix SQL Server performance problems. [6.5, 7.0, 2000] Updated 8-30-2003
SSIS: A warning about using child packages!
Jamie Thomson's Blog
In an earlier blog post I talked about how my biggest wish for SSIS vNext is that they find some way of letting us build libraries of pre-configured data-flows, tasks and transformations so that we can easily pick them up and drop them into our packages. Any changes to the task/component in the library would be reflected wherever that component is used. In other words a task/component exists in one place and we instantiate that object in our packages...a bit like instantiating class objects in managed code
Hits and Misses
Mike Metcalf
Just thought that I would share a little something that I heard at last weeks SQL DevCon 2005 in London. They did say that it wasn't documented yet but I think it’s a fairly useful snippet of information
Generate SQL Automatically Without Compromising Quality
Alan Fisher
Most database developers dread including such navigation features as paginated reports, hierarchical navigation, filtering, and full-text search because the amount of time they take to develop is not proportional to the utility or interest they create. In addition, connecting individual pages and controls to the database is mind-numbing work. Since writing SQL is non-trivial in any circumstance, it typically requires a specialist DBA's involvement
Add "Select All" to parameter lists in SQL Reporting
Sven Cipido
How to add a "select all" option to your parameter lists in SQL Reporting
Upgrading Batch-Execution Feature
Umachandar Jayachandran
Congratulations to Ahmad Mohamed, a DBA and developer for Ernst & Young in Cleveland, Ohio, and Daniele Pierasco, a DBA for Brain Force Software Italia in Milan, Italy. Ahmad won first prize of $100 for the best solution to the March Reader Challenge, "Upgrading Batch-Execution Feature." Daniele won second prize of $50. Here’s a recap of the problem and the solution to the March Reader Challenge
Project REAL: SSIS in the real world
Jamie Thomson's Blog
Project REAL is a Microsoft led initiative to build an end-to-end demonstrable BI solution on SQL Server 2005 BI tools (i.e. SSIS, SSAS, SSRS). A paper has been released documenting the experiences gained whilst building the ETL portion of this project using SSIS
The XML changes in the February CTP
Michael Rys
As you probably have heard by now, we not only have released a new SQL Server edition called Workgroup that is quite a bargain given the included functionality, but now have also released the February CTP for SQL Server 2005. Tom lists many of the cool additions (Report Builder and some others). Although I was awake earlier than him (well, I am on the East Coast right now, more on that in a separate post). Christa points to where to get the SQL Server 2005 Express CTP. If you have a MSDN Universal, Enterprise, or Professional subscription, you can get it from the MSDN Subscriber Downloads site, and beta program participants can download it from BetaPlace
Protect the master database in SQL Server 2005
The Bit Bucket
Over the years, I've accidentally run scripts in the master database more times than I care to remember. I mentioned it in the Ascend class that Bob Beauchemin was running today and everyone went “oh yes, we do that all the time“. I then realised I can “fix“ 99% of that in SQL Server 2005 with a DDL trigger
Expanding the SQL Server Product Line
Tom Rizzo
Well, you should be careful what you ask for, because you might get it. With the introduction of the expanded SQL Server product line, you now have a range of different SQL Server product choices at different price levels
Version Control -Part 1- Dealing with Code
Steve Jones
This article is part 1 of my series on version control
Reporting Strictly for Developers: Microsoft SQL Server Reporting Services
Anthony Glenwright
With the release of SQL Server Reporting Services, Microsoft finally makes a commitment to developer-centric reporting. They've integrated the design-time environment integration with Visual Studio and enabled report delivery through Web browsers, PDFs, Excel, and XML. Learn how to make this tool work for you
What are all these dt_ stored procedures, and can I remove them?
aspfaq
There are a set of stored procedures that get created when you use various visual tools against SQL Server. For example, this can happen using Visual Studio's Server Explorer to view/create database objects, or something as simple as opening the diagrams node in Enterprise Manager. Even if you haven't created any diagrams, the procedures get created
DataGrid Filter
Aviram Solomon
How to customize DataGrid column & Filter data
Heterogeneous Replication - Part I
Andrew Carter's Weblog
With SQL Server 2005 Beta 2 out the door, it's time to start sharing information on the upcoming features. One of the big features I've been working on the past three years is Heterogeneous Replication. Put simply, Oracle is a fully supported publisher for SQL Server 2005 Replication. I'm going to share some general overview information over the next few weeks
Column-Level Application Security
Michael P. Deignan
How to restrict access when you need to. If you're a Visual Basic (VB) programmer on SQL Server, you can control application security in different ways. For example, you can examine the contents of SQL Server tables to determine a user's security permissions, then use that information to prevent users from entering a record edit dialog when they don't have update permission to the underlying SQL Server table
Dr. Tom's Workshop: Multiple-Child Aggregation
Tom Moreau
Aggregation, which is commonly used to deliver reports to users, often also delivers poor performance. To help guarantee that you're delivering the best performance possible, you should explore different versions of the query and pick the best one. This month, look over Tom Moreau's shoulder as he shows you how to work with multiple-child aggregations
Automatically Utilizing XML's Untapped Semantic Goldmine
Michael M. David
A mind is a terrible thing to waste and so is the vast quantity of untapped data structure meta information available free for the using in any XML document. This semantic data available in XML is like gold lying on the ground ready to be scooped up and cashed in with little or no effort. This is meta information that can be utilized immediately to significantly increase the value of the raw XML data. By automatically utilizing this valuable structure meta information, hierarchical fourth generation languages (4GLs) can nonprocedurally process XML's complex multi-leg hierarchical structure. This significantly increases the usefulness of the data and makes it immediately available for use at its enhanced value. This article describes the semantics of hierarchical data structures and how they can be automatically utilized by 4GLs
Microsoft SQL Server 2005 Analysis Services – Unified Dimensional Model (UDM). Can you get your head round it? If you do BI, you’ll need to!
Mat Stephen's WebLog
There are very many, and from my own professional perspective, too many (I’ve got to try and learn all this stuff) new features in SQL 2005. Some of them can be described as ‘catch up’ features and some of them might be described as ‘tick in the box’ features. There is one that is wholly revolutionary and which shouldn’t be described as a feature and that is the Unified Dimensional Model (UDM)
xp_execresultset
darshan
I wanted to see all the rows in all the user tables in one database that I am working on. Here is how I achieved it in a single statement (database name changed to AdventureWorks here)
About the new SQLCLR TVFs
Bob Beauchemin's Blog
In the new Feb CTP release, how your implement a table-valued function in SQLCLR has been re-architected. This is in the readme (CTPNotes) This was done because implementing ISqlReader was quite complicated and overkill for most scenarios. Chapter 3 of our book "A First Look at SQL Server 2005 for developers" contains a very simple TVF (Bernoulli) implemented using ISqlReader. It contains over 400 lines of code. Many of the methods are stubbed-out because they are never used, but must exist to satify the interface definition. Using the new implementation this method would be less than 15 lines of code

[В начало]

ФОРУМ SQL.RU

Самые популярные темы недели

Новые упражнения на http://sql.ipps.ru
ХП против View
Больше книг хороших и разных!
HELP!!! Удалил юзера dbo из базы!
Куда девать Иванова?
Unable to connect to server...
Какой максимальный и средний размер хранимой процедуры на вашем сервере?
TPG, с днем рождения!
Есть ли какой удобный инструмент под MSSQL?
переход с ID (int identity) на GUID (uniqueidentifier)
Вопрос не совсем по теме форума
Подружить MSSQL и PostgreSQL
Импорт данных из MS Excel
users
VSS + MSSQL (принципы работы)
преобразование строки в datetime
DB-Library. Стоит ли ей пользоваться при написании новой клиентской программы.
Возможно ли Linked server c "windows pass-through authentication"?
Как убить автоматом зависший job ?
Конструктор вьюшек добавляет приставку DBO во все дыры, как запретить?

[В начало]

Вопросы остались без ответа

Замедление работы SQL при ключах /PAE /3GB
create view with view_metadata
А знаете ли вы .. (osql)
выборка СЛОВА из рекордсета
Использование стастистики
efficient SQL performance profiling
Merge Репликация и Network Packet Size
Где взять примеры из учебного 228 курса(SQL Server)?

[В начало]

КНИГИ

Работа с базами данных в Delphi

Гофман В.Э., Хомоненко А.Д. ISBN: 5-94157-361-8, Издательство: BHV, Год выпуска: 2005, Гриф книги: ; 3-е изд., перераб. и доп., Тираж книги: 4000, Формат книги: 70х100/16, Количество страниц: 640, Цена: 248 р. Рассматривается использование средств Delphi 7 для разработки приложений баз данных. Даются понятия баз данных, характеризуются элементы и описываются этапы проектирования реляционных баз данных, изложена технология разработки информационных систем, освещаются приемы работы с данными, создание таблиц и приложений баз данных, подготовка отчетов. Рассматриваются навигационный и реляционный способы доступа к данным с помощью технологий BDE, ADO, dbExpress и Interbase Express, основы программирования на SQL. Показывается использование локальных и удаленных баз данных, включая создание многоуровневых информационных систем и публикацию баз данных в Интернете. Также рассматриваются возможности Delphi 2005 по работе с базами данных. Благодаря подробному изложению тем и большому числу примеров книга может служить практическим руководством по работе с базами данных.

[В начало]

АНОНСЫ

Журнал "SQL Server для профессионалов", февральский выпуск

Журнал, посвященный вопросам разработки и администрирования Microsoft SQL Server. Подписка без дискеты - индекс: 79946. Подписка с дискетой - индекс: 79947. На дискете размещаются исходные тексты ко всем статьям номера. Исходники также можно скачать с сайта журнала, если обратиться по адресу, приведенному ниже. Периодичность выпуска - один раз в месяц. Минимальный срок подписки - 1 месяц. Информацию о ранее вышедших номерах можно найти по адресу http://newsletter.narod.ru
Анонс журнала представляет собой полный текст передовицы и краткое изложение размещенных в номере статей.

Editorial: Миграция SQL Server на платформу Windows Server 2003

Джошуа Хоскинс (Joshua Hoskins, перевод Ирины Наумовой)

Если вы администратор баз данных, в ваши обязанности, возможно, не входит обновление операционной системы на сервере баз данных, но при решении задач повышения производительности SQL Server путем модернизации операционной системы сервера до Windows 2003 придется расширить таким образом круг своих задач.
Существует несколько результатов тестов, демонстрирующих эффективность модернизации системы (результаты одного из тестов, проведенных Microsoft, см. по адресу http://www.microsoft.com/SQL/evaluation/compare/benchmarks.asp), но я могу подтвердить, что после модернизации наблюдал повышение производительности приблизительно на 25% при выполнении заданий с большим количеством операций ввода­вывода. К счастью, это обновление прошло почти так же безболезненно, как уверяла Microsoft. Я продемонстрирую процесс миграции SQL Server, работающего на кластере из двух узлов Windows 2000 Advanced Server, к Windows Server 2003 Enterprise Edition.

Приготовления

Во­первых, вы должны убедиться в том, что установлен SQL Server 2000 SP3.
Предыдущие версии SQL Server, такие как 7.0 и 6.5, не совместимы с Windows Server 2003 и не поддерживаются Microsoft. Хотя я и слышал, что SQL Server 7.0 успешно работает с Windows Server 2003, но я бы не рекомендовал использовать эту конфигурацию в промышленной среде.
Первое, что вы должны проверить, — это совместимость аппаратного обеспечения и драйверов, установленных на сервере баз данных с системой Windows Server 2003. Большинство компаний­производителей размещают информацию о совместимости продуктов на своем Web­сайте. Если вы не можете найти нужную информацию на сайте компании, необходимо связаться с ней непосредственно для выяснения этого вопроса. Это очень важный шаг при обновлении системы, поскольку проблемы в работе одного драйвера могут привести к тому, что обновление завершится ошибкой, или даже хуже — при видимой на первый взгляд корректной установке обновления возникает множество неожиданных проблем после обновления.
На следующем шаге нужно определить стратегию восстановления в случае сбоя. Это наиболее важная часть обновления. Как минимум, вы должны сделать резервные копии баз данных и других промышленных данных на сервере. Кроме этой резервной копии я настоятельно рекомендую использовать Symantec Ghost либо другое подобное программное обеспечение, чтобы непосредственно перед обновлением создать образ вашей системы. Это способно помочь сэко­номить время в случае необходимости восстановления и определенно стоит потраченного времени при возникновении такой ситуации.

Запуск обновления

Есть два пути обновления вашего кластера, хотя они состоят из похожих шагов. Различаются они только временем, которое проходит между обновлением узлов. Метод поочередного обновления состоит в обновлении сначала одного узла кластера, а затем постепенном перемещении ресурсов кластера на другой узел, что повышает производительность и стабильность системы. Хотя я нахожу этот метод наиболее предпочтительным, так как при этом у вас всегда имеется один исправный узел, он накладывает ограничение на создание кластерных ресурсов в смешанном режиме (когда отличаются операционные системы на разных узлах кластера).
Если же вы не можете позволить себе лишиться возможности создавать кластерные ресурсы, можно немедленно, после окончания обновления первого, обновить и второй узел, выбрав метод одновременной миграции. Различия между этими методами незначительны, и выбор метода ограничивается только требованиями логики вашего бизнес­процесса.
Инструкцию по обновлению Windows 2000 до Windows Server 2003 можно найти по адресу http://download.microsoft.com/download/9/9/6/996f17f2-e008-4581-a26f-9098f87690e2/Upgwin2k.doc В этой документации также можно получить информацию об Application Compatibility Toolkit (набор утилит для проверки совместимости программного обеспечения). Это свободно распространяемый Microsoft комплект утилит, который может помочь в том, чтобы обновление прошло более гладко. Однако самым полезным инструментом из этого набора утилит может стать Application Verifier. Application Verifier применяется, чтобы проверить любое пользовательское программное обеспечение на совместимость с Windows 2003 Server.
После окончания процесса обновления операционной системы и применения всех существующих к этому моменту исправлений первое, что вы должны сделать, — переместить экземпляр SQL Server на активный узел. Завершив это перемещение, необходимо немедленно начать проверку работоспособности процессов этого экземпляра SQL Server. Для этого можно запустить любое задание, которое имеется у этого экземпляра, и просмотреть результаты на предмет выявления отклонений. Если экземпляр участвует в репликации, необходимо проверить, что репликация отрабатывает корректно. И в заключение следует запустить несколько пользовательских процессов и проверить, нет ли отклонений в их работе.
Если вы удовлетворены результатами, полученными для обновленного узла, необходимо подумать, когда лучше выполнить обновление следующего узла. Я рекомендую подождать несколько дней, если для вашей промышленной среды это допустимо, чтобы убедиться, что все функционирует нормально. Как только будете готовы, перенесите все ресурсы кластера на узел Windows Server 2003 и начинайте процесс обновления второго узла. вы не должны упустить из вида, что аппаратные средства, драйверы и программное обеспечение должны быть идентичны на обоих узлах. Если же это не так, необходимо проверить совместимость и отследить любые подобные отклонения.

Итоги

После модернизации вы сразу же заметите повышение производительности процессов, для которых необходимы частые операции ввода­вывода. Как было упомянуто выше, я добился увеличения скорости на 25%. Одно это служит стимулом для перехода на Windows Server 2003. Подготовительная работа перед обновлением может быть несколько утомительна, но она полностью окупается тем, что обновление затем проходит без помех.

[В начало]

DB Design & Warehousing: Делегирование учетных записей средствами деперсонификации

Используйте эти возможности AD для идентификации пользователей

Кевин Кристенсен (Kevin Christensen)

Профессионалы ИТ отвечают за гарантии того, что системы, которые они разрабатывают и администрируют, защищены всеми доступными средствами как от внешних, так и от внутренних угроз. Жизненно важным, но зачастую упускаемым аспектом компьютерной безопасности является безопасность системы управления базами данных. В таких системах управления базами данных, как SQL Server, безопасность начинается с процесса идентификации, который представляет собой процедуру проверки того, что пользователь, запрашивающий некоторую услугу, является легальным пользователем и уполномочен получать доступ к этой услуге.
В SQL Server 2000 доступны два метода идентификации: через Windows и через SQL Server. Идентификация средствами Windows использует учетные записи, поддерживаемые операционной системой Windows. При идентификации с помощью SQL Server используются учетные записи, полностью поддерживаемые средствами SQL Server. В отличие от учетных записей Windows они хранятся не в централизованной базе данных безопасности, поэтому ими нельзя управлять с помощью политик учетных записей. Специалисты Microsoft разъясняют эти вопросы в статье «Система безопасности Microsoft SQL Server 2000» ( http://www.microsoft.com/sql/techinfo/administration/2000/securitywp.asp). В ней утверждается, что система идентификации Windows более безопасна, чем система идентификации SQL Server, которая поддерживается лишь для обеспечения обратной совместимости, а также для работы под управлением Windows Me и Windows 98. При установке SQL Server 2000 по умолчанию применяется конфигурация системы безопасности на основе режима идентификации Windows. Это указывает, что в будущих версиях SQL Server может поддерживать только данную конфигурацию.

(Полный текст статьи опубликован в журнале)

[В начало]

DB Design & Warehousing: Блокировки в SQL Server

По материалам статьи Andres Taylor Advanced SQL Server Locking

Перевод Виталия Степаненко

Я думал, что знаю SQL Server достаточно хорошо. Я использую этот продукт уже больше 6 лет, и мне нравится знать об используемых мною инструментах все. Когда я преподавал на курсах программирования SQL Server, я заметил, что в материалах Microsoft представлена таблица совместимости блокировок. Та же таблица была представлена и в MSDN. Рассматривая эту таблицу, я удивился: неужели здесь нет блокировки Intent Update? Это привело меня к исследованию блокировок. Данная статья и есть результат этого исследования. Я написал эту статью для определенного читателя — для того, кто понимает уровни изоляции, блокировки намерения, мертвые блокировки и уровни блокировок. Если вы недостаточно уверенно разбираетесь в этих областях, вам нужно сначала ознакомиться с ними перед чтением этой статьи.
Я надеюсь, что я расширю ваше понимание блокировок в SQL Server и, возможно, научу вас некоторым приемам, которые вы будете использовать во время программирования на SQL Server.
Должен сказать, что вы можете вполне успешно работать с SQL Server долгое время и не знать, как он блокирует свои ресурсы, и в то же время писать высококачественные код и схемы баз данных. Но если вы похожи на меня и хотите знать внутреннее строение вещей или если вы работаете с системой, которая требует хотя бы небольшого прироста производительности, то я могу научить вас кое­чему полезному.

(Полный текст статьи опубликован в журнале)

[В начало]

Programming: Асинхронное выполнение хранимой процедуры

Грегори Ларсен (Gregory Larsen)

Интересовались ли вы когда­нибудь, можно ли выполнять хранимую процедуру асинхронно по отношению к какому­либо коду T­SQL? Я имею в виду запуск хранимой процедуры в фоновом режиме и продолжение выполнения следующей строки кода сценария T­SQL до окончания выполнения хранимой процедуры. В этой статье рассказывается, когда может понадобиться асинхронное выполнение хранимых процедур, как его осуществить, и рассматривается пример, который поможет вам попрактиковаться в его запуске.
Прежде чем показывать, как выполнять хранимую процедуру асинхронно, давайте разберемся, где асинхронная логика поможет улучшить приложение. Скажем, у меня имеется приложение для ввода заказов в реальном времени, когда оператор принимает заказы по телефону. Мое приложение было сдано в эксплуатацию 12 месяцев назад, а теперь база данных разрослась и содержит миллионы записей. Когда это приложение впервые начало использоваться, на обработку заказа уходило в среднем 2 минуты. Но постепенно время, затрачиваемое на обработку заказа, становилось все больше и больше. Теперь, когда в базе данных содержится более миллиона записей, ввод нового заказа занимает от 15 до 20 минут. Такое падение производительности вызвано ошибками в проектировании базы данных. Этот недостаток приводит к падению доходов, потому что клиенты не желают ждать у телефона 20 минут, пока их заказ будет обработан.
Проанализировав положение, я понял, что основное время ожидания обусловлено спиралевидной структурой базы данных и большой продолжительностью обновления всех таблиц, которое необходимо было проводить после того, как сам заказ уже был введен. Для завершения телефонного разговора в основном требовалось обновить только пару таблиц, а все остальные обновления (те самые, которые требовали 20 и более минут) можно было выполнить после того, как клиент уже повесил трубку. Поэтому я решил, что проще всего будет взять исходную хранимую процедуру ввода заказа и переписать ее запуск так, чтобы 20­минутная транзакция выполнялась бы асинхронно. Такой подход показался мне более предпочтительным, потому что хранимая процедура ввода заказа вызывается из множества точек в приложении, и изменение исходной логики потребует переписывания значительно большего объема кода, чем переделка одной хранимой процедуры ввода заказов.
Теперь, когда понятна проблема с хранимой процедурой, давайте рассмотрим текущее состояние хранимой процедуры с низкой производительностью.

(Полный текст статьи опубликован в журнале)

[В начало]

Programming: Журналирование изменений структуры БД и данных. Часть 1

Наталья Кривонос

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

Задачи, которые решает контроль изменений структуры

1. Кто, когда, с какой машины и каким приложением менял структуру базы.

   Задача скорее административная. Для разбирательств постфактум «кто, когда и зачем все сломал».

2. Ведение истории изменений для последовательного наращивания версий.

   Пример 1: у пользователей есть версия X базы, разработчики постепенно вносят некоторые изменения, и спустя какое­то время появляется версия Y. Задача состоит в том, чтобы, не ломая существующей у пользователей базы, также последовательно нарастить ее до версии Y. Впрочем, такую задачу можно (и даже более удобно) решить и без накапливания последовательных изменений: сравнивая две базы с использованием системных представлений, процедур и таблиц.

3. Автоматизация различных рутинных дейс­т­­вий в базе.

   Пример 2: в разрабатываемой системе есть два типа пользовательских таблиц — реплицируемые и нет. Структура репликации хорошо продумана, все обкатано и работает. Пусть имена реплицируемых таблиц начинаются с префикса «rt», а нереплицируемых — с префикса «t». При добавлении новой таблицы она автоматически включается в публикацию, если имеет соответствующий префикс. Изменение структуры базы — добавление таблицы — является сигналом для запуска кода, который может добавить таблицу в существующую публикацию.

   Пример 3: на таблицы в базе (все или некоторые) автоматически создается набор триггеров определенной структуры для решения стандартных для данной системы задач. Или набор процедур, когда прямые действия с таблицами запрещены, а разрешены только через хранимые процедуры.

Задачи, которые решаются контролем изменений данных

1. Кто, когда, с какой машины и каким приложением менял данные в базе.

   Задача, скорее относящаяся к безопасности. Журнал используется для восстановления реальной картины событий, когда данные испорчены/утеряны, а те пользователи, кто имеет доступ к базе и права на работу с данными, искажают (намеренно или нет) картину прошлых действий. Данные, которые нужны для такой задачи: user_name, date/time, host_name, app_name, table_name, record_id.
   Подвидом данной задачи и ее упрощенным вариантом является хранение сведений о последнем изменении в данных, т. е. last_user, last_datetime и т. д.
   Как в полном виде, так и в сокращенном, данный тип отвечает скорее за административный аспект необходимости журналирования. Длительность хранения данных в журнале может сильно варьироваться.

2. Ведение истории изменения данных.

   Здесь в большей степени имеет значение, какие именно данные менялись, старые/новые значения. Используется в случаях, когда нужна полная история изменения данных. История хранится достаточно долго, возможно, на протяжении всей жизни данных. В некоторых случаях история хранится и после того, как данные были удалены.

3. Уведомление об изменениях данных.

   Уведомления клиентских приложений, что данные, отражаемые на интерфейсе, устарели и должны быть обновлены. Если система кэширует на промежуточном или клиентском уровне какие­то данные, то ее необходимо уведомлять, что данные устарели и кэш надо обновлять. Здесь реализация может сильно отличаться в зависимости от решаемой задачи. В некоторых случаях достаточно журналирования лишь факта операции, в некоторых — журнал должен содержать все сведения вплоть до старых/новых значений. Характерным признаком данного класса задач является короткая продолжительность хранения данных в журнале. Типичная ситуация: с некоторой периодичностью запускается процесс, который считывает все изменения из журнала, рассылает уведомления, очищает журнал и отключается.

4. Нестандартная репликация.

   В MSSQL 2000 есть различные типы репликации, которые при грамотном использовании способны решить большинство задач, связанных с синхронизацией данных на разных серверах. Большин­ство, но не все. Типичный пример — off­line репликация (на дискетах/компактах/по почте). Данную задачу при некотором упорстве можно решить, используя триггеры и журналирование данных.

(Полный текст статьи опубликован в журнале)

[В начало]

Журнал "Алгоритм", первый номер (48 страниц)

Этот журнал возник как логическое продолжение рассылки ".Net Собеседник", которая выпускается почти год и насчитывает на сегодняшний день более 35 выпусков и более 2600 подписчиков. Основное направление статей, которые будут печататься в журнале, будет касаться платформы .Net и сопутствующих ей технологий, языков программирования в среде Windows, администрирования и программирования серверов баз данных. Среди авторов будут многие известные вам по форумам www.sql.ru имена.

Интервью с Александром Гладченко - ведущим рассылки "MS SQL Server- дело тонкое…"

Здравствуйте, Александр! Спасибо за то, что согласились дать интервью. Расскажите немного о себе.
Здравствуйте, Виталий! Я также хочу поприветствовать всех читателей первого выпуска Вашего нового журнала. Надеюсь, что ему будет сопутствовать успех, т.к. тематика и предме тная область, которую представляет это издание, очень актуальны и будут востребованы ещё очень долго. Мне 42 года, из которых десять лет моей жизни связаны с SQL Server. Начинал я с SQL Server 6.0. В начале, это был один из серверов, который мне нужно было администрировать, но он был одним из самых критически важных звеньев в технологической цепочке, т.е. мне приходилось уделять ему самое большое внимание. Внимания было столько, что, по-видимому, именно в те годы я и сроднился с этой СУБД, о чём не жалею и по сей день.
(Полный текст статьи опубликован в журнале)

Резервное копирование в репликации SQL Server

Автор : А. Гладченко

В этой статье рассматриваются требования к резервному копированию баз данных, задействованных в репликации моментальных снимков, репликации транзакций или репликации слиянием. Эти требования зависят от роли, которую сервер исполняет в репликации и от места, где в топологии репликации необходимо обеспечить восстановление тиражируемых данных. Чтобы восстановить участвующие в репликации данные, необходимо регулярно резервировать издателей, дистрибуторов и подписчиков. Статья основана на материалах электронной документации, поставляемой в дистрибутиве сервера баз данных SQL Server 2000 Books Online.
(Полный текст статьи опубликован в журнале)

Передача значений между веб-формами в ASP.NET

Автор : Бипин Джоши; Дополнения : С.Топорков

Веб-формы ASP.NET предлагают прекрасную событийно-управляемую программную модель для разработчиков. В целом она упрощает дизайн вашего приложения, но и сама по себе порождает некоторые проблемы. Например, в традиционном ASP можно было легко передавать значения от одной страницы к другой странице с использованием метода POST. Этим подходом нельзя воспользоваться в ASP.NET, если вы хотите использовать модель веб-форм (т.е. форма и элементы управления обрабатываются на стороне сервера). Однако есть несколько подходов, с помощью которых можно преодолеть эти ограничения. Например, значения можно передавать в строке запроса, используя сессионные переменные или с использованием метода Server.Transfer.
(Полный текст статьи опубликован в журнале)

Компоненты контроля ввода ASP .Net

Автор : В. Чужа

Идея контролировать ввод пользователя программного обеспечения (ПО), в зависимости от типа данных или по шаблону, наверняка возникла у разработчиков ПО вместе с первыми ошибками ввода и была реализована во многих программных продуктах. Например, в Turbo Vision фирмы Borland класс TinputLine имеет метод Valid, перекрыв который можно вести контроль правильности ввода данных. При работе с базами данных есть возможность задать так называемые constraints - ограничения на ввод на уровне, например, поля (столбца) или таблицы. Так, в СУБД MS SQL Server 2000 имеются такие типы ограничений на ввод на уровне поля (домена в терминах SQL Server), как DEFAULT - значение по умолчанию, CHECK - проверка значения на допустимость занесения в поле и REFERENTIAL - значение должно соответствовать одному из значений в другой таблице. Эти примеры являются разными способами реализации контроля ввода пользователя, служащие одной цели - повышению надёжности работы программного обеспечения. Наверняка именно поэтому не обошла вниманием эту идею и компания Microsoft в ASP.Net, представив нам 6 компонент для контроля ввода пользователя (ККВ). Разберём их подробнее.
(Полный текст статьи опубликован в журнале)

Технологии построения распределенных приложений в .NET. Часть 3. Пространство имен System.EnterpriceServices

Автор : М.Сухов

COM+, так же как и MSMQ, пришел к нам из века неуправляемых приложений. Раньше без таких компонентов не могло обойтись ни одно серьезное распределенное приложение. С появлением .NET картина несколько изменилось. COM+ никуда не ушел, зато появилась управляемая надстройка над ним, Serviced Components. И сейчас мы поговорим, как создавать компоненты на базе этой технологии и как использовать в программе поддержку COM+ сервисов.
(Полный текст статьи опубликован в журнале)

System.Object под микроскопом Remotesoft .Net Explorer'a

Автор : В.Чужа

Как вам должно быть известно, типы в C# бывают двух видов - ссылочные и структурные. Первые производятся от класса System.Object, вторые - от класса System.ValueType, который, в свою очередь, является также наследником класса System.Object. Различие переменных этих типов заключается в том, что переменные (или объекты) ссылочных типов размещаются в управляемой куче, в то время как переменные структурных типов (к которым относятся перечисления, структуры и все числовые типы данных) - в стеке. Общность их заключается в том, что все они являются наследниками класса System.Object, о котором мы и поговорим подробнее.
(Полный текст статьи опубликован в журнале)

Подсистема сопоставления записей в Хранилище Данных

Автор : Д.Орлов

Данная статья рассчитана на разработчиков информационных систем, специализирующихся в области обработки и хранения данных. В статье излагаются принципы проектирования и работы подсистемы сопоставления записей (далее - подсистема) хранилища данных, предназначенной для реальной интеграции данных, поступающих из различных информационных систем. Сопоставление записей (record linkage) - задача достаточно нетривиальная и, что немаловажно, мало описанная в русскоязычной технической литературе, так что данная статья является попыткой в какой-то мере восполнить этот пробел.
(Полный текст статьи опубликован в журнале)

Делаем отчёты в .Net своими руками

Автор : В.Чужа

Помню в конце 80-х годов, ещё во времена существования СССР, по телевидению показывали французский мультик-экранизацию романа Ж.Верна "Вокруг света за восемьдесят дней". Мультик был так себе, но запомнились высказывания одного из главных героев романа, Филиаса Фогга, одно из которых взято в роли эпиграфа к данной статье. Дело в том, что в случае необходимости создания отчётов на платформе .Net программисты часто пользуются либо встроенным генератором отчётов Crystal Reports компании Business Solutions, либо генераторами отчётов сторонних производителей. С ними то и дело возникают различные проблемы, в то время как сама платформа предоставляет прекрасные возможности по созданию отчётов в виде html-файлов с использованием Internet Explorer'a (да и вообще любого браузера) в качестве средства для их просмотра и печати.
(Полный текст статьи опубликован в журнале)

[В начало]