В SQL Server есть две схемы для метаданных:
Я слышал, что таблицы INFORMATION_SCHEMA основаны на стандарте ANSI. При разработке, например, хранимые процедуры, целесообразно ли использовать таблицы INFORMATION_SCHEMA поверх таблиц sys?
Я всегда старался использовать представления Information_schema по запросу схемы sys.
Представления соответствуют требованиям ISO, поэтому теоретически вы должны иметь возможность легко переносить любые запросы на разные РСУБД.
Однако были случаи, когда информация, которая мне нужна, просто недоступна в представлении.
Я предоставил некоторые ссылки с дополнительной информацией о представлениях и запросах в каталоге SQL Server.
Если вы не пишете приложение, которое, как вам известно, должно быть переносимым или вам нужна только базовая информация, я бы просто по умолчанию использовал проприетарные системные представления SQL Server.
В представлениях Information_Schema отображаются только объекты, совместимые со стандартом SQL-92. Это означает, что нет представления схемы информации даже для довольно простых конструкций, таких как индексы (они не определены в стандарте и остаются в качестве деталей реализации). Не говоря уже о каких-либо свойствах SQL Server.
Кроме того, это не совсем панацея от переносимости, которую можно принять. Реализации все еще различаются между системами. Oracle не реализует его "из коробки" вообще и в документах MySql говорит:
Пользователи SQL Server 2000 (которые также следуют стандарту) могут заметить сильное сходство. Однако в MySQL было опущено много столбцов, которые не относящихся к нашей реализации, и добавили столбцы, которые MySQL-специфичны. Одной из таких столбцов является столбец ENGINE в Таблица INFORMATION_SCHEMA.TABLES.
Даже для SQL-конструкций с хлебом и маслом, таких как ограничения внешнего ключа, представления Information_Schema могут быть значительно менее эффективными для работы с представлениями sys., поскольку они не отображают идентификаторы объектов, которые могли бы обеспечить эффективный запрос.
например. См. Вопрос SQL-запрос замедляется с 1 секунды до 11 минут - почему? и планы выполнения.
INFORMATION_SCHEMA больше подходит для внешнего кода, который может потребоваться для взаимодействия с различными базами данных. Как только вы начнете программировать в базе данных, вид переносимости выходит из окна. Если вы пишете хранимые процедуры, это говорит о том, что вы посвятили себя определенной платформе базы данных (лучше или хуже). Если вы перешли на SQL Server, то непременно используйте представления sys.
Я не буду повторять некоторые другие ответы, но добавляю перспективы производительности. Представления information_schema, как упоминает Мартин Смит в своем ответе, не являются наиболее эффективным источником этой информации, поскольку они должны раскрывать стандартные столбцы, которые необходимо собирать из нескольких исходных источников. Представления sys могут быть более эффективными с этой точки зрения, поэтому, если у вас есть высокие требования к производительности, и вам не нужно беспокоиться о переносимости, вы должны, вероятно, перейти к представлениям sys.
Например, в первом запросе ниже используется data_schema.tables, чтобы проверить, существует ли таблица. Второй использует sys.tables, чтобы сделать то же самое.
if exists (select * from information_schema.tables where table_schema = 'dbo' and table_name = 'MyTable')
print '75% cost';
if exists (select * from sys.tables where object_id = object_id('dbo.MyTable'))
print '25% cost';
Когда вы просматриваете IO для них, первый запрос имеет 4 логических чтения для sysschobjs и sysclsobjs, а второй - нет. Кроме того, первый из них выполняет два некластеризованных индекса и поиск ключей, а второй - только для одного кластерного индекса. Первая стоит ~ 3 раза больше, чем вторая по планам запросов. Если вы должны делать это много раз в большой системе, скажем, для времени развертывания, это может скомпенсировать и вызвать проблемы с производительностью. Но это действительно относится только к сильно загруженным системам. Большинство ИТ-систем бизнес-систем не имеют таких проблем с производительностью.
Опять же, общая стоимость их очень мала по отдельности по сравнению с другими запросами в большинстве систем, но если ваша система имеет много такого вида деятельности, она может складываться.