Методы получения плана запроса в СУБД MS SQL Server. Ms sql запросы

Рекурсивный запрос на MS SQL с использованием обобщенного табличного выражения (CTE) - «Ask SQL»

Рекурсивный запрос на MS SQL с использованием обобщенного табличного выражения (CTE)

Рекурсивный SQL запрос - один из способов решения проблемы дерева и других проблем, требующих рекурсивную обработку.В MS SQL нет ключевого слова recursive, но рекурсию можно выполнить используя создание простого обобщенного табличного выражения CTE (с использованием ключевого слова WITH). С его помощью можно написать рекурсивный запрос, т.е. запрос, который, написанный один раз, будет повторяться многократно пока истинно некоторое условие.

Рекурсивный CTE имеет следующий вид:

WITH <имя>[(<список столбцов>)] AS( < SELECT... > -- анкорная часть UNION ALL -- рекурсивная часть < SELECT...FROM <имя>… > WHERE <условие продолжения итераций> )

От обычного WITH CTE-запроса рекурсивный отличается только рекурсивной частью, которая вводится предложением UNION ALL. Обратите внимание, что в рекурсивной части присутствует ссылка на имя CTE, т.е. внутри CTE ссылается само на себя. Это, собственно, и есть рекурсия. Естественно, анкорная и рекурсивная части должны иметь одинаковый набор столбцов.

Проведем небольшой тест, для этого в нижеприведенном примере объявим временную таблицу и наполним ее тестовыми данными (вместо CTE будем использовать слово Rec, чтобы читателю было понятно что CTE не является ключевым словом, а лишь посняет суть).

DECLARE @TableVar TABLE ( tree_id INT, parent_id INT, name VARCHAR(32) ) INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES (1, 0, 'test1') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES (2, 1, 'test2') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES (3, 2, 'test3') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES (4, 1, 'test4') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES (5, 2, 'test5') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES (6, 5, 'test6') INSERT INTO @TableVar ( tree_id, parent_id, name ) VALUES (7, 1, 'test7') -- SELECT * FROM @TableVar declare @node as INTEGER; Set @node = 1; -- устанавливаем начальную ноду, т.е. 1-ый идентификатор WITH Rec(tree_id, parent_id, name, lvl) AS ( SELECT tree_id, parent_id, name, 0 as lvl FROM @TableVar e WHERE e.tree_id = @node -- здесь задаётся параметр UNION ALL SELECT e.tree_id, e.parent_id, e.name, r.lvl + 1 as lvl FROM @TableVar e JOIN Rec r ON e.parent_id = r.tree_id ) SELECT tree_id, parent_id, name, lvl, replicate(char(160), 2*lvl) + name as brench FROM Rec ORDER BY tree_id, parent_id

Получаем такой результат:

Продолжение статьи.

Попробуем теперь научиться считать по иерархии сумму элементов.

Код SQL:

-- Hierarchical Cumulative Sum Using SQL Server -- DECLARE @ProductCategories TABLE ( Id int IDENTITY(1,1) NOT NULL, Amount float NOT NULL, ProductCategoryId int NULL, Name VARCHAR(150) NULL ) INSERT @ProductCategories VALUES (100, NULL, N'A1') INSERT @ProductCategories VALUES (90, NULL, N'A2') INSERT @ProductCategories VALUES (80, NULL, N'A3') INSERT @ProductCategories VALUES (20, 1, N'A11') INSERT @ProductCategories VALUES (30, 1, N'A12') INSERT @ProductCategories VALUES (10, 1, N'A13') INSERT @ProductCategories VALUES (70, 2, N'A21') INSERT @ProductCategories VALUES (50, 2, N'A22') INSERT @ProductCategories VALUES (5, 4, N'A11.1') INSERT @ProductCategories VALUES (10, 4, N'A11.2') INSERT @ProductCategories VALUES (15, 5, N'A12.1') INSERT @ProductCategories VALUES (20, 5, N'A12.2') INSERT @ProductCategories VALUES (25, 9, N'A11.1.1') INSERT @ProductCategories VALUES (30, 9, N'A11.1.2') INSERT @ProductCategories VALUES (35, 10, N'A11.2.1') INSERT @ProductCategories VALUES (40, 10, N'A11.2.2') ;WITH C AS ( SELECT T.id, T.Amount, T.id AS RootID FROM @ProductCategories T UNION ALL SELECT T.id, T.Amount, C.RootID FROM @ProductCategories T INNER JOIN C ON T.ProductCategoryId = C.id ) SELECT T.id, T.ProductCategoryId, T.Name, T.Amount, S.AmountIncludingChildren FROM @ProductCategories T INNER JOIN ( SELECT RootID, SUM(Amount) AS AmountIncludingChildren FROM C GROUP BY RootID ) AS S ON T.id = S.RootID ORDER by T.id OPTION (MAXRECURSION 0)

Дата публикации: 2018-05-22 11:12:22

asksql.org

Методы получения плана запроса в СУБД MS SQL Server — AUsevich

Планом запроса называется последовательность логических и физических операторов, которые должны быть выполнены СУБД для того чтобы получить результат SQL-запроса. Получение плана запроса может потребоваться в различных ситуациях, а в первую очередь при задачах оптимизации запросов.

Я хочу описать три способа получить план запроса используя инструменты СУБД MS SQL Server, каждый из способов может оказаться предпочтительнее в той или иной ситуации.

Получение плана запроса с помощью Profiler’а (или Extended Events)

Получение плана запроса с помощью Profiler’а (или нового механизма — Extended Events) является, наверное, наиболее популярным способом и причина заключается в том, что с помощью Profiler’а можно перехватить информацию о любом запросе, исполняемом в данный момент экземпляром SQL Server’а. Типичным, с целью получения плана запроса, является приведенный ниже способ работы с Profiler’ом (в связке с 1С:Предприятие):

Настроить Profiler (или Extended Events) и поставить его на паузу
Если трассировка содержит данные, очистить окно трассировки
С помощью отладчика в 1С:Предприятие остановиться перед выполнением необходимого запроса
Снять Profiler с паузы
Выполнить запрос
Остановить Profiler
Проанализировать полученную информацию

Profiler является инструментом для создания трассировок и управления ими. С помощью данного инструмента можно получать различную информацию о работе СУБД. В данном случае мы будем использовать его для получения информации о выполняемом запросе, в частности, для того чтобы получить его план выполнения.

Для того чтобы получить план запроса в Profiler следует добавить следующие события:

Также можно добавить необходимые отборы: по имени/идентификатору базы данных, номеру соединения, длительности выполнения.

Более подробная информация о работе с профайлером приведена в статье «Работа с Profiler SQL Server. Примеры настройки трассировок».

Получение кэшированного плана запроса с помощью динамической функции

Перед выполнением запроса СУБД проверяет наличие актуального кэшированного плана запроса. Если такой план запроса существует, тогда СУБД использует его, а не компилирует план запроса заново. Это позволяет сократить время выполнения запроса и именно поэтому, после выполнения очистки процедурного кэша, запросы выполняются дольше (происходит компиляция плана запроса). Таким образом, если мы знаем текст искомого запроса, мы можем получить его план из кэша (если он есть в кэше). Для этого необходимо обратиться к следующим динамическим функциям:

Для поиска плана запроса необходимо выполнить в SQL Server Management Studio нижеприведенный запрос (также доступен во вложении к статье), при этом изменив условия отбора:

SELECT TOP 20 qs.last_execution_time AS Last_execution_time, SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS Query_text, qp.query_plan AS Query_plan, qs.execution_count AS Execution_count FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp WHERE qs.last_execution_time > '2016-08-01 11:30:00.000' /* 1. Date & Time filter */ and qt.text like '%FROM dbo._AccumRg17539 T1%' /* 2. SQL query text filter */ and qt.text not like '%Query Finder%' /* 3. Special condition */

SELECT TOP 20

qs.last_execution_time AS Last_execution_time,

SUBSTRING(qt.text,

(qs.statement_start_offset/2) + 1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2) + 1) AS Query_text,

qp.query_plan AS Query_plan,

qs.execution_count AS Execution_count

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qs.last_execution_time > '2016-08-01 11:30:00.000' /* 1. Date & Time filter */

and qt.text like '%FROM dbo._AccumRg17539 T1%' /* 2. SQL query text filter */

and qt.text not like '%Query Finder%' /* 3. Special condition */

В запросе добавлены условия по:

Времени последнего выполнения
Тексту искомого запроса (таких фильтров можно добавить несколько, уточняя результат поиска)
Специальное условие для того чтобы сам запрос поиска не попадал в результат поиска (менять не надо)

Результатом запроса будет таблица с колонками: Last_execution_time (последнее время выполнения), Query_text (текст SQL-запроса), Query_plan (План SQL-запроса) и Execution_count (количество выполнений).

Щелкнув в Management Studio по ссылке в колонке Query_plan будет открыто графическое представление плана запроса.

Получение плана запроса в Management Studio

Еще один способ получить план запроса — выполнить SQL-запрос непосредственно в Management Studio. Специально для этих целей в командном меню «Запрос» (Query) присутствует два пункта:

Показать предполагаемый план выполнения (Display Estimated Execution Plan)
Включить действительный план выполнения (Include Actual Execution Plan)

Команды подменю «Запрос» в Management Studio

Для получения предполагаемого плана необходимо ввести текст запроса и нажать на пункт «Показать предполагаемый план выполнения», выполнять сам запрос не требуется. Получение актуального плана непосредственно связано с выполнением запроса, поэтому пункт «Включить действительный план выполнения» включает/отключает возможность вывода информации об актуальном плане запроса. Таким образом, для получения действительного плана выполнения необходимо включить вышеуказанную опцию и выполнить запрос.

ausevich.ru

Отправка запроса на все базы данных всех указанных серверов на примере MS SQL Server и C#.NET

USE [НАЗВАНИЕ_БАЗЫ_ДАННЫХ] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [inf].[vTableSize] as with pagesizeKB as ( SELECT low / 1024 as PageSizeKB FROM master.dbo.spt_values WHERE number = 1 AND type = 'E' ) ,f_size as ( select p.[object_id], sum([total_pages]) as TotalPageSize, sum([used_pages]) as UsedPageSize, sum([data_pages]) as DataPageSize from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id WHERE OBJECTPROPERTY(p.[object_id], N'IsUserTable') = 1 group by p.[object_id] ) ,tbl as ( SELECT t.[schema_id], t.[object_id], i1.rowcnt as CountRows, (COALESCE(SUM(i1.reserved), 0) + COALESCE(SUM(i2.reserved), 0)) * (select top(1) PageSizeKB from pagesizeKB) as ReservedKB, (COALESCE(SUM(i1.dpages), 0) + COALESCE(SUM(i2.used), 0)) * (select top(1) PageSizeKB from pagesizeKB) as DataKB, ((COALESCE(SUM(i1.used), 0) + COALESCE(SUM(i2.used), 0)) - (COALESCE(SUM(i1.dpages), 0) + COALESCE(SUM(i2.used), 0))) * (select top(1) PageSizeKB from pagesizeKB) as IndexSizeKB, ((COALESCE(SUM(i1.reserved), 0) + COALESCE(SUM(i2.reserved), 0)) - (COALESCE(SUM(i1.used), 0) + COALESCE(SUM(i2.used), 0))) * (select top(1) PageSizeKB from pagesizeKB) as UnusedKB FROM sys.tables as t LEFT OUTER JOIN sysindexes as i1 ON i1.id = t.[object_id] AND i1.indid < 2 LEFT OUTER JOIN sysindexes as i2 ON i2.id = t.[object_id] AND i2.indid = 255 WHERE OBJECTPROPERTY(t.[object_id], N'IsUserTable') = 1 OR (OBJECTPROPERTY(t.[object_id], N'IsView') = 1 AND OBJECTPROPERTY(t.[object_id], N'IsIndexed') = 1) GROUP BY t.[schema_id], t.[object_id], i1.rowcnt ) SELECT @@Servername AS Server, DB_NAME() AS DBName, SCHEMA_NAME(t.[schema_id]) as SchemaName, OBJECT_NAME(t.[object_id]) as TableName, t.CountRows, t.ReservedKB, t.DataKB, t.IndexSizeKB, t.UnusedKB, f.TotalPageSize*(select top(1) PageSizeKB from pagesizeKB) as TotalPageSizeKB, f.UsedPageSize*(select top(1) PageSizeKB from pagesizeKB) as UsedPageSizeKB, f.DataPageSize*(select top(1) PageSizeKB from pagesizeKB) as DataPageSizeKB FROM f_size as f inner join tbl as t on t.[object_id]=f.[object_id] GO

habr.com

Запрос MS SQL Server. Начало

Для практики приведенных запросов необходимо в MS SQL Server исполнить следующий код, который создаст все необходимые таблицы и заполнит их образцами данных (необходимо предварительно создать базу данных TestDB):

USE [TestDB]GO/**** Object: Table [dbo].[m_unit] Script Date: 07/24/2013 08:50:32 ****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[m_unit]([title] [nvarchar](10) NOT NULL,[description] [nvarchar](30) NULL) ON [PRIMARY]GOINSERT [dbo].[m_unit] ([title], [description]) VALUES (N'кг', N'Килограмм')INSERT [dbo].[m_unit] ([title], [description]) VALUES (N'л', N'Литр')INSERT [dbo].[m_unit] ([title], [description]) VALUES (N'м', N'Метр')INSERT [dbo].[m_unit] ([title], [description]) VALUES (N'шт', N'Штука')/**** Object: Table [dbo].[m_supplier] Script Date: 07/24/2013 08:50:32 ****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[m_supplier]([id] [int] NOT NULL,[title] [nvarchar](30) NOT NULL,[phone] [nvarchar](20) NULL,[address] [nvarchar](255) NULL) ON [PRIMARY]GOINSERT [dbo].[m_supplier] ([id], [title], [phone], [address]) VALUES (1, N'ЗАО Комбинат "Огого"', N'1212', NULL)INSERT [dbo].[m_supplier] ([id], [title], [phone], [address]) VALUES (2, N'ООО "Сладкое"', N'1213', NULL)INSERT [dbo].[m_supplier] ([id], [title], [phone], [address]) VALUES (3, N'ИП Каримов А.Б.', N'12345', NULL)/**** Object: Table [dbo].[m_product] Script Date: 07/24/2013 08:50:32 ****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[m_product]([id] [int] NOT NULL,[title] [nvarchar](30) NOT NULL,[supplier_id] [int] NULL,[ctgry_id] [int] NOT NULL,[unit] [nvarchar](255) NULL,[lifedays] [int] NULL) ON [PRIMARY]GOINSERT [dbo].[m_product] ([id], [title], [supplier_id], [ctgry_id], [unit], [lifedays]) VALUES (1, N'Шоколад плиточный', 1, 4, N'шт', 90)INSERT [dbo].[m_product] ([id], [title], [supplier_id], [ctgry_id], [unit], [lifedays]) VALUES (2, N'Конфеты Карамель', 3, 4, N'кг', 90)INSERT [dbo].[m_product] ([id], [title], [supplier_id], [ctgry_id], [unit], [lifedays]) VALUES (3, N'Молоко', 2, 1, N'л', 2)INSERT [dbo].[m_product] ([id], [title], [supplier_id], [ctgry_id], [unit], [lifedays]) VALUES (4, N'Масло сливочное, упаковка', 2, 2, N'шт', 30)INSERT [dbo].[m_product] ([id], [title], [supplier_id], [ctgry_id], [unit], [lifedays]) VALUES (5, N'Масло растительное, бутылка', 3, 2, N'шт', 90)INSERT [dbo].[m_product] ([id], [title], [supplier_id], [ctgry_id], [unit], [lifedays]) VALUES (6, N'Масло сливочное, развесное', 1, 2, N'кг', 10)INSERT [dbo].[m_product] ([id], [title], [supplier_id], [ctgry_id], [unit], [lifedays]) VALUES (7, N'Мясо говяжье', 1, 3, N'кг', 5)INSERT [dbo].[m_product] ([id], [title], [supplier_id], [ctgry_id], [unit], [lifedays]) VALUES (8, N'кефир, упаковка', 1, 1, N'шт', 2)INSERT [dbo].[m_product] ([id], [title], [supplier_id], [ctgry_id], [unit], [lifedays]) VALUES (9, N'Хлеб', 2, 5, N'шт', 1)INSERT [dbo].[m_product] ([id], [title], [supplier_id], [ctgry_id], [unit], [lifedays]) VALUES (10, N'Батон', 3, 5, N'шт', 1)INSERT [dbo].[m_product] ([id], [title], [supplier_id], [ctgry_id], [unit], [lifedays]) VALUES (11, N'Сметана', 1, 1, N'шт', 2)/**** Object: Table [dbo].[m_outcome] Script Date: 07/24/2013 08:50:32 ****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[m_outcome]([id] [int] NOT NULL,[dt] [datetime] NULL,[product_id] [int] NOT NULL,[amount] [int] NOT NULL,[price] [float] NOT NULL) ON [PRIMARY]GOINSERT [dbo].[m_outcome] ([id], [dt], [product_id], [amount], [price]) VALUES (1, CAST(0x00009F0000000000 AS DateTime), 1, 10, 12.7)INSERT [dbo].[m_outcome] ([id], [dt], [product_id], [amount], [price]) VALUES (3, CAST(0x00009F0000000000 AS DateTime), 3, 23, 3.7)INSERT [dbo].[m_outcome] ([id], [dt], [product_id], [amount], [price]) VALUES (4, CAST(0x00009F0000000000 AS DateTime), 3, 34, 3.8)INSERT [dbo].[m_outcome] ([id], [dt], [product_id], [amount], [price]) VALUES (5, CAST(0x00009F0000000000 AS DateTime), 4, 30, 11)INSERT [dbo].[m_outcome] ([id], [dt], [product_id], [amount], [price]) VALUES (6, CAST(0x00009F0000000000 AS DateTime), 7, 20, 20.4)INSERT [dbo].[m_outcome] ([id], [dt], [product_id], [amount], [price]) VALUES (7, CAST(0x00009F0000000000 AS DateTime), 4, 1, 12)INSERT [dbo].[m_outcome] ([id], [dt], [product_id], [amount], [price]) VALUES (8, CAST(0x00009F0000000000 AS DateTime), 5, 20, 13)INSERT [dbo].[m_outcome] ([id], [dt], [product_id], [amount], [price]) VALUES (10, CAST(0x00009F0100000000 AS DateTime), 6, 25, 18.2)INSERT [dbo].[m_outcome] ([id], [dt], [product_id], [amount], [price]) VALUES (11, CAST(0x00009F0600000000 AS DateTime), 8, 20, 3.4)INSERT [dbo].[m_outcome] ([id], [dt], [product_id], [amount], [price]) VALUES (12, CAST(0x00009F1300000000 AS DateTime), 11, 4, 5.5)INSERT [dbo].[m_outcome] ([id], [dt], [product_id], [amount], [price]) VALUES (14, CAST(0x00009F0000000000 AS DateTime), 4, 23, 11.2)/**** Object: Table [dbo].[m_income] Script Date: 07/24/2013 08:50:32 ****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[m_income]([id] [int] NOT NULL,[dt] [datetime] NULL,[product_id] [int] NOT NULL,[amount] [int] NOT NULL,[price] [float] NOT NULL) ON [PRIMARY]GOINSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (26, CAST(0x00009E5E00000000 AS DateTime), 1, 1000, 12.5)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (27, CAST(0x00009E7E00000000 AS DateTime), 3, 200, 3.74)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (28, CAST(0x00009EA300000000 AS DateTime), 4, 100, 10)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (29, CAST(0x00009EFF00000000 AS DateTime), 7, 200, 20)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (30, CAST(0x00009EFF00000000 AS DateTime), 5, 235, 12.35)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (31, CAST(0x00009F0000000000 AS DateTime), 1, 300, 12.4)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (32, CAST(0x00009F0000000000 AS DateTime), 7, 100, 19.5)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (33, CAST(0x00009F0000000000 AS DateTime), 2, 100, 15.75)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (34, CAST(0x00009F0000000000 AS DateTime), 5, 3000, 11.8)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (36, CAST(0x00009F0000000000 AS DateTime), 6, 200, 17.9)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (37, CAST(0x00009F0000000000 AS DateTime), 3, 150, 3.74)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (38, CAST(0x00009F0100000000 AS DateTime), 3, 150, 3.74)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (39, CAST(0x00009F0100000000 AS DateTime), 10, 100, 4.2)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (40, CAST(0x00009F0100000000 AS DateTime), 9, 100, 2.95)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (41, CAST(0x00009F0200000000 AS DateTime), 10, 130, 4.2)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (42, CAST(0x00009F0200000000 AS DateTime), 9, 110, 2.95)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (43, CAST(0x00009F0600000000 AS DateTime), 8, 155, 3.1)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (44, CAST(0x00009F0700000000 AS DateTime), 8, 450, 3.1)INSERT [dbo].[m_income] ([id], [dt], [product_id], [amount], [price]) VALUES (45, CAST(0x00009EFF00000000 AS DateTime), 5, 535, 12.35)/**** Object: Table [dbo].[m_category] Script Date: 07/24/2013 08:50:32 ****/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[m_category]([id] [int] NOT NULL,[title] [nvarchar](30) NOT NULL) ON [PRIMARY]GOINSERT [dbo].[m_category] ([id], [title]) VALUES (1, N'Молочные продукты')INSERT [dbo].[m_category] ([id], [title]) VALUES (2, N'Масло, жир')INSERT [dbo].[m_category] ([id], [title]) VALUES (3, N'Мясо')INSERT [dbo].[m_category] ([id], [title]) VALUES (4, N'Кондитерские изделия')INSERT [dbo].[m_category] ([id], [title]) VALUES (5, N'Хлебобулочные изделия')

Подпишитесь на нашу еженедельную почтовую рассылку "SQL - примеры запросов".

Подробности Автор: Рахматджон Хакимов Родительская категория: Программирование Категория: Запросы MS SQL Server

oftob.ru

Запрос MS SQL Server. Введение

Приведены примеры запросов SQL (Structure Query Language) для Microsoft SQL Server и необходимые для них таблицы. Вы можете самостоятельно проверять и изменять приведенные примеры запросов SQL. Надеемся, что данный практикум поможет Вам получить базовые знания и навыки в построении различных запросов на языке SQL и применить их при работе с другими СУБД, такими как Microsoft Access, MySQL, Oracle и т.д.

Описание учебного проекта "Магазин"

Схема связей таблиц

Описание таблиц

m_category - категории товаров

Имя поля	Тип данных	Описание
id	Счетчик	Код категории товара
title	Текстовый	Название категории

m_income - приход товаров

Имя поля	Тип данных	Описание
id	Счетчик	Код записи
dt	Дата/время	Дата прихода
product_id	Числовой	Код товара
amount	Числовой	Количество прихода
price	Числовой	Цена за единицу

m_outcome - расход товаров

Имя поля	Тип данных	Описание
id	Счетчик	Код записи
dt	Дата/время	Дата продажи
product_id	Числовой	Код товара
amount	Числовой	Количество прихода
price	Числовой	Цена за единицу

m_product - справочник, описание товаров

Имя поля	Тип данных	Описание
id	Счетчик	Код товара
title	Текстовый	Название товара
supplier_id	Числовой	Код поставщика
ctgry_id	Числовой	Код категории товара
unit	Текстовый	Единица измерения
lifedays	Числовой	Срок годности в днях

m_supplier - справочник; информация о поставщиках

Имя поля	Тип данных	Описание
id	Счетчик	Код поставщика
title	Текстовый	Имя поставщика
phone	Текстовый	Телефон
address	Текстовый	Адрес

m_unit - справочник; единицы измерения

Имя поля	Тип данных	Описание
title	Текстовый	Тип единицы товара
description	Текстовый	Описание

Для практической проверки примеров приведенных в данном учебном материале, необходимо иметь в наличие следующее программное обеспечение:

- Microsoft SQL Server 2008 или новей.

- Microsoft SQL Server Management Studio

Заметим, что таблицы (отношения) являются учебными.

Тренироваться, тренироваться, тренироваться!

Подпишитесь на нашу еженедельную почтовую рассылку "SQL - примеры запросов".

Подробности Автор: Рахматджон Хакимов Родительская категория: Программирование Категория: Запросы MS SQL Server

oftob.ru

Рекурсивные SQL запросы / Хабр

Рекурсивны SQL запросы являются одним из способов решения проблемы дерева и других проблем, требующих рекурсивную обработку. Они были добавлены в стандарт SQL 99. До этого они уже существовали в Oracle. Несмотря на то, что стандарт вышел так давно, реализации запоздали. Например, в MS SQL они появились только в 2005-ом сервере. Рекурсивные запросы используют довольно редко, прежде всего, из-за их сложного и непонятного синтаксиса: with [recursive] <имя_алиаса_запроса> [ (<список столбцов>) ]as (<запрос>) <основной запрос> В MS SQL нет ключевого слова recursive, а в остальном все тоже самое. Такой синтаксис поддерживается в DB2, Sybase iAnywhere, MS SQL и во всех базах данных, которые поддерживают стандарт SQL 99.

Проще разобрать на примере. Предположим, есть таблица:

create table tree_sample ( id integer not null primary key, id_parent integer foreign key references tree_sample (id), nm varchar(31) )

id – идентификатор id_parent – ссылка на родитель nm – название.

Для вывода дерева:

with recursive tree (nm, id, level, pathstr)as (select nm, id, 0, cast('' as text) from tree_sample where id_parent is null union all select tree_sample.nm, tree_sample.id, t.level + 1, tree.pathstr + tree_sample.nm from tree_sample inner join tree on tree.id = tree_sample.id_parent) select id, space( level ) + nm as nm from tree order by pathstr

Этот пример выведет дерево по таблице с отступами. Первый запрос из tree_sample этот запрос выдаст все корни дерева. Второй запрос соединяет между собой таблицу tree_sample и tree, которая определяется этим же запросом. Этот запрос дополняет таблицу узлами дерева.

Сначала выполняется первый запрос. Потом к его результатам добавляются результаты второго запроса, где данные таблица tree – это результат первого запроса. Затем снова выполняется второй запрос, но данные таблицы tree – это уже результат предыдущего выполнения второго запроса. И так далее. На самом деле база данных работает не совсем так, но результат будет таким же, как результат работы описанного алгоритма.

После этого данные этой таблицы можно использовать в основном запросе как обычно.

Хочу заметить, что я не говорю о применимости конкретно этого примера, а лишь пишу его для демонстрации возможностей рекурсивных запросов. Этот запрос реально будет работать достаточно медленно из-за order by.

habr.com

Межбазовый запрос на Transact-SQL | Info-Comp.ru

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

Примечание! Сразу хочу сказать, что все примеры будем пробовать на Transact-SQL MS Sql Server 2008 в Management Studio, так как в других СУБД синтаксис будет отличаться. Также хочу заметить, что все примеры ниже требуют начальных знаний SQL, поэтому советую для начала ознакомиться с материалами: Язык запросов SQL – Оператор SELECT, Добавляем в таблицу новую колонку на SQL, Сочетание строковых функций на Transact-SQL, Transact-sql – Табличные функции и временные таблицы эти статьи помогут Вам приобрести начальные знания в SQL.

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

Межбазовый запрос

Межбазовый запрос – это запрос, который в процессе своего выполнения подключается к разным базам данных, а также в некоторых случаях к разным серверам баз данных.

А теперь давайте определимся, для каких целей могут служить межбазовые запросы, допустим, у Вас есть 3 базы данных, 2 из них расположены на одном MSSQL сервере, а одна на другом. Все они служат для какой-то определенной задачи, может быть у них даже схожая структура, но это не важно и Вам как программисту иногда требуется выгружать данные из всех баз, например, для того чтобы предоставить эти данные начальству, и Вы скорей всего запускаете запросы из каждой базы или переключаетесь из менеджера запросов на работу с той ли иной базой, но гораздо удобней было бы запустить один запрос и получить сразу все данные. Именно для этого я пользуюсь данного вида запросами, но Вы, наверное, можете найти применение и для других задач. Если Вы сталкивались с такого рода задачами, то Вам просто необходимо узнать что такое межбазовый запрос.

Примеры межбазовых запросов

И первый пример он достаточно простой, требуется тогда когда необходимо получить данные из нескольких баз расположенных на одном сервере. Для объединения этих данных будем использовать конструкцию union all, которую мы рассматривали в статье – union и union all на Transact-SQL .

И для начала, допустим, у нас есть две базы данных (test и test2), схемы dbo в которых мы создали вот такие таблицы:

Таблица в базе test

CREATE TABLE [dbo].[test_table_base_1]( [id] [bigint] NOT NULL, [text] [varchar](50) NULL, CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

Таблица в базе test2

CREATE TABLE [dbo].[test_table_base_2]( [id] [bigint] NOT NULL, [text] [varchar](50) NULL, CONSTRAINT [PK_test_table] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

Я их заполнил тестовыми данными, сейчас увидите какими, и для того чтобы получить данные из этих таблиц напишем вот такой запрос:

Код:

select * from test.dbo.test_table_base_1 – Первая база select * from test2.dbo.test_table_base_2 –Вторая база

Как видите синтаксис очень простой:

Select * from [база].[схема].[таблица]

Но результата в вышеуказанном запросе будет два, и для того чтобы объединить эти запросы, используем конструкцию union all.

Код:

select * from test.dbo.test_table_base_1 – Первая база union all select * from test2.dbo.test_table_base_2 –Вторая база

И результат будет уже один. И с помощью данного межбазового запроса Вы легко можете объединить данные, а еще для удобства, чтобы каждый раз не писать текст запроса можете создать представление views, для того чтобы обращаться напрямую к этому представлению.

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

Для этого мы будем использовать конструкцию opendatasource.

Сразу скажу, что opendatasource работает, только если на сервере выставлен параметр Ad Hoc Distributed Queries со значением 1. Для того чтобы посмотреть этот параметр выполните процедуру sp_configure и посмотрите значение данного параметра:

Где,

config_value - это значение которое внеслось но еще не сохранилось, т.е. сервер еще не переконфигурировался;
run_value – текущее значение данного параметра, т.е. с которым работает сервер в данный момент.

Кстати данная процедура возвращает очень много конфигурационных параметров, которые Вы можете посмотреть.

И для того чтобы изменить данный параметр, используем туже самую процедуру, синтаксис:

exec sp_configure [Название параметра],[Значение]

А для того чтобы сконфигурировать сервер с новым значением, запустим процедуру reconfigure, и весь запрос будет выглядеть вот так:

exec sp_configure 'Ad Hoc Distributed Queries',1 reconfigure exec sp_configure

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

Код:

select * from opendatasource ('sqlncli','Data Source=myserver;Integrated Security=SSPI') .test.dbo.test_table_base_1 union all select * from opendatasource ('sqlncli','Data Source=myserver;Integrated Security=SSPI') .test2.dbo.test_table_base_2

Как видите результат тот же самый.

Здесь мы указали в первом параметре провайдер источника данных, т.е. SQL server ('sqlncli') и задали строку подключения:

Где,

Data Source – это адрес сервера баз данных;
Integrated Security=SSPI – при подключении использовать проверку подлинности Windows, т.е. аутентификация и авторизация пользователя будет проходить по учетным данным Windows, отлично подходит, если в сети развернута AD(Active Directory).

А если Вы хотите использовать проверку подлинности на уровне SQL сервера, то придется писать имя пользователя и пароль (которые должны быть созданы на SQL сервере) в строке подключения, например, абсолютно такой же результат, как и выше, получится, если мы напишем вот такой запрос:

select * from opendatasource ('sqlncli','Data Source= myserver;user id = username; pwd=password') .test.dbo.test_table_base_1 union all select * from opendatasource ('sqlncli','Data Source= myserver;user id = username; pwd=password') .test2.dbo.test_table_base_2

Т.е. вместо параметра Integrated Security мы укажем параметры:

user id - логин на SQL сервере;
pwd – соответственно пароль.

Примечание! Opendatasource может подключаться и другим отличным от SQL сервера источникам для этого в параметрах указываете нужный Вам провайдер, например, для подключения к Excel документу можете использовать вот такой запрос (Синтаксис):

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\TestExcel.xls; Extended Properties=EXCEL 5.0')...[Sheet1$] ;

По межбазовым запросам все, надеюсь, данный материал был Вам интересен, и пригодится Вам на практике.