#sql #sql-server #database
#sql #sql-сервер #База данных
Вопрос:
Я создаю новую базу данных SQL, которая должна подключаться к нескольким существующим базам данных, все из которых имеют одну и ту же схему.
Я хотел бы создать хранимые процедуры, которые можно использовать для доступа к любой из существующих БД при максимально возможном повторном использовании кода. Мой вопрос в том, каков наилучший способ написания общих SP, которые можно использовать для доступа к любой из этих существующих баз данных?
Вот несколько вариантов, которые я рассматривал до сих пор. Обратите внимание, что это всего лишь простые фрагменты кода, иллюстрирующие вопрос, а не реальный код. Сначала я попытался включить имя базы данных:
IF @db = 'A'
BEGIN
SELECT * FROM A.dbo.SERIES_DATA
END
IF @db = 'B'
BEGIN
SELECT * FROM B.dbo.SERIES_DATA
END
Недостатком этого является то, что один и тот же оператор SQL повторяется несколько раз. Тогда я подумал об использовании динамического SQL:
DECLARE @Command varchar(100)
SET @Command = 'select * from ' @db '.dbo.SERIES_DATA'
EXEC (@Command)
Это решает проблему дублирования операций, Но сопряжено с рисками динамического SQL (например, инъекционных атак и т. Д.). Итак, наконец, я натолкнулся на идею создания табличной функции:
CREATE FUNCTION [dbo].[ufnSeries_Data] (@db varchar(10))
RETURNS TABLE
AS
RETURN (
SELECT * FROM A.dbo.Series_Data WHERE @db = 'A'
UNION
SELECT * FROM B.dbo.Series_Data WHERE @db = 'B'
)
GO
Это дает мне возможность писать единый общий код для доступа к любой из БД:
SELECT * FROM ufnSeries_Data(@db)
Проблема с этим подходом заключается в том, что я должен предоставить всем пользователям системы доступ на чтение ко всем базам данных, чтобы этот запрос работал, в противном случае я получаю сообщение об ошибке отказа в доступе. Я также не уверен в влиянии на производительность создания подобного TVF.
Есть ли у кого-нибудь другие идеи о том, как я могу структурировать свой код, чтобы минимизировать дублирование операторов? Может быть, есть гораздо более простой подход, который мне не хватает? Я был бы очень благодарен за любые предложения.
Комментарии:
1. Можете ли вы написать a
VIEW, который выполняет конкатенацию, и позволить sprocs обращаться к этому представлению?2. Вы должны быть в состоянии решить проблему с разрешениями, используя
EXECUTE AS, однако я не могу говорить за часть, влияющую на производительность.3. View — лучший вариант, чем TVF
4. @adrianm Не будет ли view by design запрашивать все базы данных, в то время как TVF будет запрашивать только одну? В этом случае «лучше» будет зависеть от варианта использования. Или вы имеете в виду другой дизайн представления?
5. @JoachimIsaksson, Нет, определение представления становится частью плана выполнения, и доступ осуществляется только к необходимым базам данных / таблицам. т.е. Если запрос содержит
WHERE [Database] = 'A'только базу данных ‘A’, будет доступен. Определение TVF не является частью плана
Ответ №1:
Вы можете сделать это, удалив ссылку на вашу базу данных и схему
SELECT * FROM SERIES_DATA
Речь идет о создании схемы во всех ваших базах данных и предоставлении доступа для входа в базы данных и схемы (в этом случае вы используете схему dbo по умолчанию).
Если вы используете Management Studio, при создании учетных записей вы можете обрабатывать все остальное с помощью сопоставления пользователей.
Если у вас одна и та же схема (например, dbo), вы можете изменить свой выбор на:
SELECT * FROM dbo.SERIES_DATA
Вы действительно не хотите включать имя базы данных, если вы не объединяете таблицы между базами данных.