Ms sql создать таблицу – Создание таблиц в Microsoft SQL Server (CREATE TABLE) – подробная инструкция | Info-Comp.ru

Содержание

MS SQL Server и T-SQL

Создание таблиц

Последнее обновление: 26.06.2017

Ключевым объектом в базе данных являются таблицы. Таблицы состоят из строк и столбцов. Столбцы определяют тип информации, которая хранится, а строки содержат значения для этих столбцов.

В прошлой теме была создана база данных university. Теперь определим в ней первую таблицу. Опять же для создания таблицы в SQL Server Management Studio можно применить скрипт на языке SQL, либо воспользоваться графическим дизайнером. В данном случае выберем второе.

Для этого раскроем узел базы данных university в SQL Server Management Studio, нажмем на его подузел Tables правой кнопкой мыши и далее в контексто меню выберем New -> Table…:

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

Column Name: имя столбца
Data Type: тип данных столбца. Тип данных определяет, какие данные могут храниться в этом столбце. Например, если столбец представляет числовой тип, то он может хранить только числа.
Allow Nulls: может ли отсутствовать значение у столбца, то есть может ли он быть пустым

Допустим, нам надо создать таблицу с данными учащихся в учебном заведении. Для этого в дизайнере таблицы четыре столбца: Id, FirstName, LastName и Year, которые будут представлять соответственно уникальный идентификатор пользователя, его имя, фамилию и год рождения. У первого и четвертого столбца надо указать тип int (то есть целочисленный), а у столбцов FirstName и LastName — тип nvarchar(50) (строковый).

Затем в окне Properties, которая содержит свойства таблицы, в поле Name надо ввести имя таблицы — Students, а в поле Identity ввести Id, то есть тем самым указывая, что столбец Id будет идентификатором.

Имя таблицы должно быть уникальным в рамках базы данных. Как правило, название таблицы отражает название сущности, которая в ней хранится. Например, мы хотим сохранить студентов, поэтому таблица называется Students (слово студент во множественном числе на английском языке). Существуют разные мнения по поводу того, стоит использовать название сущности в единственном или множественном числе (Student или Students). В данном случае вопрос наименования таблицы всецело ложится на разработчика базы данных.

И в конце нам надо отметить, что столбец Id будет выполнять роль первичного ключа (primary key). Первичный ключ уникально идентифицирует каждую строку. В роли первичного ключа может выступать один столбец, а может и несколько.

Для установки первичного ключа нажмем на столбец Id правой кнопкой мыши и в появившемся меню выберем пункт Set Primary Key.

После этого напротив поля Id должен появиться золотой ключик. Этот ключик будет указывать, что столбец Id будет выполнять роль первичного ключа.

И после сохранения в базе данных university появится таблица Students:

Мы можем заметить, что название таблицы на самом деле начинается с префикса dbo. Этот префикс представляет схему. Схема определяет контейнер, который хранит объекты. То есть схема логически разграничивает базы данных. Если схема явным образом не указывается при создании объекта, то объект принадлежит схеме по умолчанию — схеме dbo.

Нажмем правой кнопкой мыши на название таблицы, и нам отобразится контекстное меню с опциями:

С помощью этих опций можно управлять таблицей. Так, опция Delete позволяет удалить таблицу. Опция Design откроет окно дизайнера таблицы, где мы можем при необходимости внести изменения в ее структуру.

Для добавления начальных данных можно выбрать опцию Edit Top 200 Rows. Она открывает в виде таблицы 200 первых строк и позволяет их изменить. Но так как у нас таблица только создана, то естественно в ней будет никаких данных. Введем пару строк — пару студентов, указав необходимые данные для столбцов:

В данном случае я добавил две строки.

Затем опять же по клику на таблицу правой кнопкой мыши мы можем выбрать в контекстном меню пункт Select To 1000 Rows, и будет запущен скрипт, который отобразит первые 1000 строк из таблицы:

metanit.com

Создание и удаление таблиц в MS SQL Server

Создание и удаление таблиц в ms sql server

Все данные в БД sql server хранятся в таблицах.
Таблицы состоят из колонок, объединяющих значения одного типа, и строк — записей в таблице. В одной БД может быть до 2 миллиардов таблиц, в таблице — 1024 колонки, в одной строке (записи) — 8060 байтов.

sql server поддерживает следующие типы данных:

Тип данных Обозначение Размер, байт
Бинарные данные binary
varbinary[(n)] 1-8000
Символы char[(n)]
varchar[(n)] 1-8000
( до 8000 символов)
Символы unicode nchar[(n)]
nvarchar[(n)] 1-8000
(до 4000 символов)
Дата и время datetime 8
smalldatetime 4
Точные числа decimal[(p[,s])]
numeric[(p[,s])] 5-17
Приблизительные числа float[(n)] real 4-8 4
Глобальный идентификатор uniqueidentifier 16
Целые числа int smallint, tinyint 4 2, 1
Денежки money, smallmoney 8, 4
Специальные bit, cursor,

sysname, timestamp 1, 0-8
Текст и изображение text, image 0-2 Гб
Текст unicode ntext 0-2 Гб

Таблицы можно создавать с помощью оператора create table языка transact-sql, а также с помощью enterprise manager. Рассмотрим сначала как это делается с помощью transact-sql.

Содание таблиц с помощью create table
Для создания таблиц применяется оператор create table.
Вот как выглядит упрощенный синтаксис этого оператора:

create table table_name
(column_name data_type [null | not null]
[,…n])
Например:

create table member
( member_no int not null,
lastname char(50) not null,
firstname char(50) not null,
photo image null
)

Этим оператором создается таблица member, состоящая из четырех колонок:

member_no — имеет тип int, значения null не допускаются
lastname — имеет тип char(50) — 50 символов, значения null не допускаются
firstname — аналогично lastname
photo — имеет тип image (изображение), допускается значение null
Примечание
null — специальное обозначение того, что элемент данных не имеет значения. В описании типа колонки указывается, что элементы данных могут быть неинициализированы. При указании not null — «пустые» значения не допускаются. Если при вставке записи пропустить значение для такой колонки, вставка не произойдет, и sql server сгенерирует ошибку.

Попробуйте выполнить эту команду. Запустите query analyzer. Соединитесь с Вашим сервером. Из списка БД выберите sqlstep. Скопируйте в окно команд команду создания таблицы и выполните ее. (Если не забыли, надо нажать f5 или ctrl-e).

Чтобы точно удостовериться, в том, что таблица была создана, наберите команду:

sp_help member
Выделите ее (как в обычном редакторе) и снова нажмите f5. В окно результатов будет выведена информация о таблице member.

На заметку!

sp_help — системная процедура, которая возвращает информацию об объектах БД (таблицах, хранимых процедурах и пр.).
Формат вызова таков:

sp_help <имя таблицы>

Удалить таблицу проще простого. Там же, в запросчике (так у нас называют query analyzer), наберите:

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

Как создать таблицу с помощью sql server enterprise manager
Раскройте последовательно: sql server group, <Ваш sql server>, databases. Выберите БД (sqlstepbystep, я думаю :), нажмите правую кнопку мыши и выберите в контекстном меню пункт «new», а затем пункт «table…». Первым делом Вас спросят имя таблицы. Введите его и нажмите enter. На экране появится окно, в котором можно вводить:

имена колонок, тип, длину, размерность, точность (эти три колонки блокируются в зависимости от типа), флаг разрешения null, значение по умолчанию. Последние три колонки, пока не представляют интереса.

Введите названия колонок, их тип и длину также как в примере выше. Нажмите на иконку с дискетой для сохранения таблицы и можете закрыть окно. Раскройте вашу БД, щелкните на категории «tables» и в списке таблиц увидите только что введенную таблицу. Для ее удаления выделите ее в списке, нажмите правую кнопку мыши и в контекстном меню выберите «delete». Таблица будет удалена.

Мы изучили как создаются и удалются таблицы. Следующий наш шаг — создание полноценной БД, на примере которой мы будем изучать:

что такое реляционная целостность БД и как она обеспечивается в sql server
как модифицировать данные в таблицах (операторы insert, update, delete, select)
как использовать хранимые процедуры и триггеры

www.internet-technologies.ru

Инструкция SELECT INTO в T-SQL или как создать таблицу на основе SQL запроса? | Info-Comp.ru

Если у Вас возникала необходимость сохранить результирующий набор данных, который вернул SQL запрос, то данная статья будет Вам интересна, так как в ней мы рассмотрим инструкцию SELECT INTO, с помощью которой в Microsoft SQL Server можно создать новую таблицу и заполнить ее результатом SQL запроса.

Начнем мы, конечно же, с описания самой инструкции SELECT INTO, а затем перейдем к примерам.

Инструкция SELECT INTO в Transact-SQL

SELECT INTO – инструкция в языке в T-SQL, которая создает новую таблицу и вставляет в нее результирующие строки из SQL запроса. Структура таблицы, т.е. количество и имена столбцов, а также типы данных и свойства допустимости значений NULL, будут на основе столбцов (выражений), указанных в списке выбора из источника в инструкции SELECT. Обычно инструкция SELECT INTO используется для объединения в одной таблице данных из нескольких таблиц, представлений, включая какие-то расчетные данные.

Для того чтобы использовать инструкцию SELECT INTO требуется разрешение CREATE TABLE в базе данных, в которой будет создана новая таблица.

Инструкция SELECT INTO имеет два аргумента:

new_table — имя новой таблицы;
filegroup – файловая группа. Если аргумент не указан, то используется файловая группа по умолчанию. Данная возможность доступна начиная с Microsoft SQL Server 2017.

Важные моменты про инструкцию SELECT INTO

Инструкцию можно использовать для создания таблицы на текущем сервере, на удаленном сервере создание таблицы не поддерживается;
Заполнить данными новую таблицу можно как с текущей базы данных и текущего сервера, так и с другой базы данных или с удаленного сервера. Например, указывать полное имя базы данных в виде база_данных.схема.имя_таблицы или в случае с удаленным сервером, связанный_сервер.база_данных.схема.имя_таблицы;
Столбец идентификаторов в новой таблице не наследует свойство IDENTITY, если: инструкция содержит объединение (JOIN, UNION), предложение GROUP BY, агрегатную функцию, также, если столбец идентификаторов является частью выражения, получен из удаленного источника данных или встречается более чем один раз в списке выбора. Во всех подобных случаях столбец идентификаторов не наследует свойство IDENTITY и создается как NOT NULL;
С помощью инструкции SELECT INTO нельзя создать секционированную таблицу, даже если исходная таблица является секционированной;
В качестве новой таблицы можно указать обычную таблицу, а также временную таблицу, однако нельзя указать табличную переменную или возвращающий табличное значение параметр;
Вычисляемый столбец, если такой есть в списке выбора инструкции SELECT INTO, в новой таблице он становится обычным, т.е. не вычисляемым;
SELECT INTO нельзя использовать вместе с предложением COMPUTE;
С помощью SELECT INTO в новую таблицу не переносятся индексы, ограничения и триггеры, их нужно создавать дополнительно, после выполнения инструкции, если они нужны;
Предложение ORDER BY не гарантирует, что строки в новой таблице будут вставлены в указанном порядке.
В новую таблицу не переносится атрибут FILESTREAM. Объекты BLOB FILESTREAM в новой таблице будут как объекты BLOB типа varbinary(max) и имеют ограничение в 2 ГБ;
Объем данных, записываемый в журнал транзакций во время выполнения операций SELECT INTO, зависит от модели восстановления. В базах данных, в которых используется модель восстановления с неполным протоколированием, и простая модель, массовые операции, к которым относится SELECT INTO, минимально протоколируются. За счет этого инструкция SELECT INTO может оказаться более эффективней, чем отдельные инструкции по созданию таблицы и инструкции INSERT по заполнение ее данными.

Примеры использования SELECT INTO

Все примеры я буду выполнять в СУБД Microsoft SQL Server 2016 Express.

Исходные данные

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

   
   CREATE TABLE TestTable(
        [ProductId] [INT] IDENTITY(1,1) NOT NULL,
        [CategoryId] [INT] NOT NULL,
        [ProductName] [VARCHAR](100) NOT NULL,
        [Price] [money] NULL
   ) ON [PRIMARY]

   GO

   CREATE TABLE TestTable2(
        [CategoryId] [INT] IDENTITY(1,1) NOT NULL,
        [CategoryName] [VARCHAR](100) NOT NULL
   ) ON [PRIMARY]

   GO

   INSERT INTO TestTable
        VALUES (1,'Клавиатура', 100),
                   (1, 'Мышь', 50),
                   (2, 'Телефон', 300)
   GO

   INSERT INTO TestTable2
        VALUES ('Комплектующие компьютера'),
                   ('Мобильные устройства')
   GO
                  
   SELECT * FROM TestTable

   SELECT * FROM TestTable2

Пример 1 – Создание таблицы с помощью инструкции SELECT INTO с объединением данных

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

   
   --Операция SELECT INTO
   SELECT T1.ProductId, T2.CategoryName, T1.ProductName, T1.Price
   INTO TestTable3 
   FROM TestTable T1
   LEFT JOIN TestTable2 T2 ON T1.CategoryId = T2.CategoryId

   --Выборка данных из новой таблицы
   SELECT * FROM TestTable3

В итоге мы создали таблицу с названием TestTable3 и заполнили ее объединёнными данными.

Пример 2 – Создание временной таблицы с помощью инструкции SELECT INTO с группировкой данных

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

   
   --Создаем временную таблицу (#TestTable) с помощью инструкции SELECT INTO
   SELECT T2.CategoryName, COUNT(T1.ProductId) AS CntProduct
   INTO #TestTable
   FROM TestTable T1
   LEFT JOIN TestTable2 T2 ON T1.CategoryId = T2.CategoryId
   GROUP BY T2.CategoryName

   --Выборка данных из временной таблицы
   SELECT * FROM #TestTable

Как видим, у нас получилось создать временную таблицу #TestTable и заполнить ее сгруппированными данными.

Вот мы с Вами и рассмотрели инструкцию SELECT INTO в языке T-SQL, в своей книге «Путь программиста T-SQL» я подробно рассказываю про все конструкции языка T-SQL (рекомендую почитать), а у меня на этом все, пока!

info-comp.ru

Как создать таблицу в базе данных MS SQL Server 2008

В предыдущей статье показано как создавать новую базу данных в MS SQL Server. Информация в базе данных храниться в таблицах, которые являются отображением некоторых логических общностей. В нашей учебной базе, которая будет моделировать работу некой коммерческой фирмы по продаже товаров, как минимум, будут необходимы пять таблиц:
Sotrudniki — в этой таблице будет храниться информация о сотрудниках — Справочник сотрудников фирмы;
Tovary — будет содержать информацию о товарах — Справочник товаров;
Zakazy — сюда будем заносить информацию о сделанных заказах — оперативная таблица о сделанных заказах;
Zakazchiki — будет хранить информацию о заказчиках — Справочник заказчиков;
Postavschiki — хранит информацию о поставщиках товаров — Справочник поставщиков
Приблизительная номенклатура таблиц ясна. Конкретную структуру таблиц с необходимыми полями будем задавать во время их создания. Сейчас аккурат и займёмся этим моментом. Опять запускаем SQL Server Management Studio, выбираем БД, отмечаем ветку Таблицы и по правой кнопке нажимаем по пункту меню «Создать таблицу»:

Для начала попробуем создать таблицу, в которой будем хранить данные о сотрудниках. Пусть она будет иметь три поля — SotrID (идентификатор сотрудника), FIO (Ф.И.О сотрудника) и Zarplata (Зарплата). Для выполнения этой задачи в колонках Имя столбца и Тип данных заносим соответствующие реквизиты полей таблицы:
Для сохранения введённой информации о структуре таблицы щелкаем правой кнопкой по ярлыку с именем таблицы по умолчанию:
В следующей форме задаём имя таблицы:
После обновления, в «Обозревателе объектов» можно увидеть в списке таблиц только что созданную таблицу:
Первую таблицу в MS SQL Server успешно создали. (пока не обращайте внимание на префикс dbo в имени таблицы. Он означает владельца таблицы — database owner).
Естественно, если мы что-то забыли, всегда можно вставить в структуру таблицы новый столбец. Для этого становимся на нужную таблицу, жмём на правую кнопку мыши и выбираем в контекстном меню «Создать столбец«:
Далее становимся на строке столбца, перед которым желаем вставить новый и по правой кнопке нажимаем по «Вставить столбец«:

Вконтакте

Одноклассники

Мой мир

Facebook

E-mail

www.itworkroom.com

Создание таблиц в Transact-SQL — Transact-SQL В подлиннике : Персональный сайт Михаила Флёнова

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

Когда мы создали базу данных, можно переходить к созданию таблиц, которые будут содержать непосредственно данные. Если сравнить базу данных с файловой системой, то ее можно рассматривать как папку, а таблицы можно рассматривать как файлы в этой папке. Только базы данных не поддерживают вложенности, т.е. в одной базе не может быть другой базы данных.

База данных сама по себе не несет никакой пользы, как и папка без файлов, потому что и то и другое не могут хранить данные. Данные хранятся в таблицах (файлах файловой системы) и именно их мы научимся создавать в этом разделе.

Что такое таблица в базе данных MS SQL Server? Данный сервер работает с реляционными таблицами, которые представляют собой двухмерный массив данных, в котором колонка определяет значение, а строки – это данные. Например, таблица базы данных схожа с классическими таблицами (списками), которые мы строим в программе Excel или на бумаге в рефератах/курсовых.

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

Таблица 1.1. Список жителей дома

Фамилия	Имя	Отчество	Дата рождения	Адрес
Иванов	Иван	Иванович	1.1.1950	Шаболовка 37
Петров	Петр	Петрович	4.7.1967	Королева 12
…	…	…	…	…

В ведении мы уже кратко говорили об уникальности, а сейчас нам предстоит поговорить об этом более подробно. Исходя из реляционной модели, все строки в базе данных должны быть уникальными. Это значит, что не может быть в списке жителей дома две записи для одного и того же жителя. Некоторые могут сказать, что это не проблема, потому что вероятность того, что в одном доме будет жить два человека с одними и теми же данными в полях ФИО и даты рождения. Вероятность маленькая, но она же есть! А ведь есть еще вероятность ошибки пользователя программы, который может ввести дважды одного и того же человека. Вот это уже станет действительно проблемой.

Чтобы понять, какие проблемы могут возникнуть при появлении в таблице одинаковых записей, представим, что у нас в списке 10 человек с одинаковыми ФИО и дата рождения (адрес опустим и не учитываем, чтобы пример был более реальным). Такой пример вполне реален. Если взять всех жителей России, то количество людей с фамилией Иванов будет не просто большим, а огромным, а совпадение остальных параметров также возможно.

Теперь представим, что у одной из записей, фамилия введена неверно и запись случайно двоит с 10 другими записями. Какую из 10 одинаковых строк должен изменить сервер? Любую, потому что записи одинаковые? Это не очень корректный выход, поэтому в стандарте SQL-92 сказано, что в реляционной таблице не может быть двух абсолютно одинаковых записей, хотя многие базы данных позволяют нарушить это ограничение и MS SQL Server не является исключением.

Чтобы обеспечить уникальность, было введено понятие ключевое поле. Это одно или несколько полей, которые обеспечивают уникальность строк в таблице. В нашем случае, ключевыми можно сделать ФИО и дату рождения. В этом случае, при попытке добавить новую строку, в которой значения в этих полях будут одинаковы с любой из уже существующих строк таблицы, база данных возвратит ошибку и не сможет добавить такую строку. Получается, что с помощью ключевых полей базы данных обеспечивают уникальность и по ним гарантировано можно найти одну и только одну запись. То есть, запросив из базы данных строку с определенными ФИО и датой рождения, мы получим только одну запись.

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

В настоящее время Microsoft не рекомендует использовать в качестве ключа автоматически увеличиваемые поля. Да, эти поля достаточно просты в сопровождении, но не всегда эффективны, потому что автоматически увеличиваемое поле нельзя редактировать. Сейчас рекомендуется для ключа создавать поле типа GUID (Global Unique Identifier, Глобально Уникальный Идентификатор). Значения полей этого типа можно редактировать и достаточно просто сгенерировать самостоятельно.

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

В оптимистическое утверждение мне вериться с трудом, ведь количество возможных GUID ограничено и после генерации хотя бы 1/3 из этих значений вероятность получения повтора увеличивается в два раза. Но к пессимистам меня тоже отнести нельзя, потому что возможных вариантов GUID настолько много, что хватит даже для очень большой базы данных, с которой вы скорей всего никогда не встретитесь в своей практике. Поэтому, я использую GUID поля (и пока не разу не встречался с конфликтами GUID), о которых мы еще поговорим в разделе 2.24. А в этом разделе мы будем в основном создавать таблицы с ключевым полем в виде автоматически увеличиваемого поля, что намного удобнее при изучении языка запросов SQL.

Теперь посмотрим, как сервер хранит данные в строке. Иногда возникает необходимость хотя бы приблизительно рассчитать размер строки, а лучше всегда знать это значение. Это позволит вам более эффективно управлять размером базы данных.

Строка данных состоит из строки заголовка и порции данных. Заголовок содержит 4 байта информации о поле строки данных, таких как указатель, на расположение конца фиксированной порции данных строки, и какой размер переменной используется в строке.

Поле может содержать следующие элементы:

Данные фиксированной длины (char, int и.т.д.) – помещаются в страницу перед данными, переменной длинны.
Нулевые блоки (NULL) — это набор байтов переменной длинны.
Переменные блоки (varchar) – содержат два байта, описывающие, какой длинны колонка. Дополнительные два байта на колонку указывают на конец каждой колонки, переменной длинны.
Данные переменной длины (text, image) – Данные переменной длины хранятся в странице после переменных блоков.

По возможности, делайте размер строки (общий размер всех полей) компактным, что позволяет поместить больше строк в страницу. Это позволит уменьшить операции ввода вывода и улучшить работу буфера. Для того, чтобы размер строки был компактным, необходимо выделять каждому полю только необходимый размер. Например, для хранения данных о фамилии вполне достаточно 20 символов строки переменной длинны varchar, и не имеет смысла выделять поле типа char, фиксированной длинны в 50 символов.

Тип char имеет фиксированную длину, и в странице данных будет всегда съедать определенный размер данных (в данном случае 50 символов). Тип varchar будет отнимать только столько, сколько необходимо. Например, вы объявили поле типа varchar размером 50 символов, но записали в него только 10. В странице данных на диске будет выделено (помимо заголовка) только 10 символов и не каплей больше.

Типы данных переменной длины (text, ntext и image) могут храниться как одна коллекция страниц или данные в строке. Посмотрим, как они хранятся:

text – может хранить 2 147 483 647 символов. Не Unicode текстовые данные не могут использоваться в качестве переменных или параметров в хранимых на сервере процедурах.
ntext – может хранить 1 073 741 823 символа или 2 147 483 647 Unicode данных переменной длины.
image – может хранить от 0 до 2 147 483 647 бинарных данных.

Так как text, ntext и image типы данных обычно большие, SQL Server хранит их вне строк. В строке на хранящиеся данные указывает только шестнадцати битный указатель. Если эти типы данных хранят небольшое количество данных, то вы можете включить опцию text in row. Вы можете также указать предел от 24 до 7000 байтов. Эту опцию для таблицы можно включить, используя системную процедуру sp_tableoption.

В следующем примере с помощью функции sp_tableoption устанавливается опция text in row и указывается, что до 1000 символов будет храниться в строке:


EXEC sp_tableoption N'TestDatabase', 'text in row', '1000'

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

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

MS SQL Server позволяет создавать до 2 биллионов таблиц в базе данных, каждая таблица может содержать до 1024 колонки и общий размер полей в таблице может достигать 8060 байтов на строку (без учета text, ntext и image). Это действительно очень много. Превысить этот предел очень сложно, и я пока не слышал, чтобы кому-то его не хватило. Если вам все же не хватит количества полей, то можно создать две таблицы и связать их один к одному. Для этого используются внешние ключи, о которых мы будем говорить в разделе 1.2.6.

1.2.1. Оператор CREATE TABLE

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

Общий вид команды CREATE TABLE показан в листинге 1.5.

Листинг 1.5. Общий вид команды CREATE TABLE


CREATE TABLE 
    [ database_name.[ owner ] . | owner. ] table_name 
    ( { < column_definition > 
     | column_name AS computed_column_expression 
     | < table_constraint > ::= [ CONSTRAINT constraint_name ]}
     | [ { PRIMARY KEY | UNIQUE } [ ,...n ] 
    ) 

[ ON { filegroup | DEFAULT } ] 
[ TEXTIMAGE_ON { filegroup | DEFAULT } ] 

< column_definition > ::= { column_name data_type } 
    [ COLLATE < collation_name > ] 
    [ [ DEFAULT constant_expression ] 
    | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
    ] 
    [ ROWGUIDCOL] 
    [ < column_constraint > ] [ ...n ] 

< column_constraint > ::= [ CONSTRAINT constraint_name ] 
    { [ NULL | NOT NULL ] 
        | [ { PRIMARY KEY | UNIQUE } 
            [ CLUSTERED | NONCLUSTERED ] 
            [ WITH FILLFACTOR = fillfactor ] 
            [ON {filegroup | DEFAULT} ] ] 
        ] 
        | [ [ FOREIGN KEY ] 
            REFERENCES ref_table [ ( ref_column ) ] 
            [ ON DELETE { CASCADE | NO ACTION } ] 
            [ ON UPDATE { CASCADE | NO ACTION } ] 
            [ NOT FOR REPLICATION ] 
        ] 
        | CHECK [ NOT FOR REPLICATION ] 
        ( logical_expression ) 
    } 

< table_constraint > ::= [ CONSTRAINT constraint_name ] 
    { [ { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        { ( column [ ASC | DESC ] [ ,...n ] ) } 
        [ WITH FILLFACTOR = fillfactor ] 
        [ ON { filegroup | DEFAULT } ] 
    ] 
    | FOREIGN KEY 
        [ ( column [ ,...n ] ) ] 
        REFERENCES ref_table [ ( ref_column [ ,...n ] ) ] 
        [ ON DELETE { CASCADE | NO ACTION } ] 
        [ ON UPDATE { CASCADE | NO ACTION } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] 
        ( search_conditions ) 
    }

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

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

Теперь посмотрим, как создать таблицу и удалить ее. Подключитесь к своей тестовой базе данных (TestDatabase) и выполните следующий запрос:


CREATE TABLE TestTable 
(
 id int
)

Невозможно создавать пустую таблицу, без описания полей. Хотя бы одно поле должно быть создано. В данном примере создается таблица с именем TestTable. После имени в скобках указываются имена и типы полей в формате:


имя тип параметры

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

Если полей несколько, то все они перечисляются в скобках через запятую. В данном случае, описано только одно поле с именем id, тип int, и без параметров. Какие могут быть типы данных? Стандарт SQL описывает только основные типы: строка, число и т.д., но в MS SQL Server их намного больше. Все типы, которые поддерживает SQL Server 2000 можно увидеть в приложении 1.

Для удаления таблицы используется команда:


DROP TABLE имя

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

Следующий запрос показывает, как удалить таблицу TestTable, созданную нами ранее:


DROP TABLE TestTable

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


CREATE TABLE TestTable 
(
 id int,
 vcName varchar(30),
 vcLastName varchar(30),
 vcSurName varchar(30),
 cPol char(1),
 dBirthDay datetime
)

В качестве дополнительных параметров можно указывать, например, явную кодировку (Collation) для поля. В следующем примере создается таблица с двумя текстовыми полями. В первом поле для хранения фамилии не указывается кодировка, поэтому для него будет взято значение из базы данных, а у второго поля явно указываем латинскую кодировку:


CREATE TABLE TestTable 
(
   Famil varchar(10), 
   Name varchar(10) collate latin1_general_cs_as
)

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


CREATE TABLE TestTable 
(
   Famil varchar(10), 
   Name varchar(10) collate latin1_general_cs_as
) ON group1

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

1.2.2. Автоинкрементные поля

Очень часто необходим какое-либо поле-счетчик, значение которого будет автоматически увеличиваться без вмешательства пользователя. Например, таким образом часто создаются ключевые поля и об этом методе мы говорили в разделе 1.2. Опытные программисты, которые работают с базами данных уже не первый день, с помощью таких полей добиваются уникальности данных. Например, очень часто можно видеть таблицы, в которых первое поле называется «id» (я люблю именовать их как Key1 или префикс id+имя таблицы), которое автоматически увеличивается при добавлении новых строк. Таким образом, можно сказать, что это поле всегда уникально.

Вы можете использовать свойство Identity (тождество) для создания колонки, которая содержит сгенерированное системой следующее значение, определяющее каждую строку, вставленную в таблицу. Тождественная колонка может использоваться не только для хранения значения первичного ключа, но и для определения строки, которая была вставлена в таблицу последней, ведь каждая новая строка будет получать число, которое больше любого существующего.

Когда SQL Server автоматически предоставляет значение ключа, можно уменьшить затраты, потому что облегчается программирование, первичный ключ будет коротким и уменьшаются пользовательские транзакции.

Автоматически увеличиваемое поле имеет следующие ограничения:

В таблице может быть только одна Identity колонка, да я и не вижу смысла от создания двух автоматически увеличиваемых полей;
Она должна использоваться с целочисленными типами данных, дробные типы запрещены;
Значение в этом поле не может обновляться. В MS SQL Server есть способ обойти это ограничение (мы его увидим далее в этом разделе), но так можно нарушить целостность данных, поэтому этот метод обхода ограничения на редактирование поля необходимо использовать аккуратно и только в крайних случаях;
Не разрешаются нулевые значения. Колонка всегда должна быть заполнена, и при этом, значение заноситься только сервером, вы не можете на него повлиять.

При разработке запросов вы можете использовать ключевое слово IDENTITYCOL в месте с именем колонки в запросе. Это позволяет вам ссылаться на колонку в таблице со свойством Identity, не зная имени колонки. Так как в таблице может быть только одна колонка со свойством Identity, сервер сам сможет найти нужное поле.

Вы можете управлять свойством Identity несколькими путями:

Тождественное свойство не обеспечивает уникальности. Для уникальности автоматически увеличиваемого поля создавайте уникальный индекс. Да, он создается по умолчанию, но удалять его не стоит, иначе сервер не сможет гарантировать уникальности;
Автоматическое увеличение можно устанавливать на поля следующих типов: tinyint, smallint, int, bigint, decimal(n,0) или numeric(n,0). Обратите внимание, что у типов decimal и numeric в качестве значения точности (количество знаков после запятой) должно быть 0, то есть дробной части не должно быть. Вы должны также учитывать, что количество возможных записей в таблице зависит от выбранного типа. Это значит, если выбрать тип tinyint (может принимать значения от 0 до 255), то в таблице может быть только 256 записей (если расчет начать с нуля). Дальше увеличение будет невозможно, а значит, новая запись не сможет быть добавлена. Выбирайте тот тип, которого будет достаточно.

В современных базах данных для обеспечения уникальности рекомендуют использовать глобально уникальные идентификаторы GUID (Globally Unique Identifier). Это действительно более мощный способ обеспечения целостности, но не очень удобный с точки зрения использования, поэтому программисты старой закалки продолжают использовать автоматическое увеличение.

Некоторые базы данных создают GUID идентификаторы для каждой строки, даже если пользователь не затребовал этого. При этом, идентификатор прячется и для его отображения необходимо включать специальную опцию или использовать специальную команду в SQL запросе. В MS SQL Server идентификаторы GUID создаются автоматически, если таблица участвует в репликации.

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


IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ]

После слова IDENTITY необходимо в круглых скобках указать два параметра – начальное значение (будет присвоено первой строке в таблице) и приращение. По умолчанию обоим параметрам присваивается значение 1. Следующий пример, показывает создание таблицы, с автоматически увеличиваемым полем ID:


CREATE TABLE TestTable 
(
 id int IDENTITY(100, 2)
)

В качестве начального значения выбрано число 100. При добавлении каждой последующей строки в поле будет записываться число на 2 больше, т.е. 102, 104, 106, 108 и т.д.

Для чего нужен ключ NOT FOR REPLICATION (не для репликации)? Допустим, что у вас есть два сервера баз данных, которые расположены в разных офисах и не объединены каналом связи. Необходимо иметь какую-то возможность объединения баз данных в одно целое. В MS SQL Server есть удобный инструмент решения данной проблемы – репликация. Репликация — это процесс слияния двух баз данных.

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

www.flenov.info

Создание таблиц в SQL Server 2005 (Урок 2)

Создание таблиц и ограничений

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

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

Любое поле таблицы характеризуется как минимум тремя обязательными свойствами:

Имя столбца. Реализует способ обращения к конкретному полю в таблице. Рекомендуется всегда присваивать полям смысловые имена.
Тип данных. Определяет, информация какого типа может храниться в данном поле.
Разрешить значения null. Определяет, допустимо ли для данного поля отсутствие фактических данных, для обозначения которого используется так называемый маркер пустого значения null.

Типы данных

При выборе типа данных для столбца следует отдавать предпочтение типу, который позволит хранить любые возможные для этого столбца значения и занимать при этом минимальное место на диске. Типы данных в MS SQL Server можно разделить на восемь категорий:

Целочисленные данные
- bit (1 байт). Может хранить только значения 0, 1 или null (пустое значение, сообщающее об отсутствии данных). Его удобно использовать в качестве индикатора состояния – включено/выключено, да/нет, истина/ложь.
- tinyint (1 байт).Целые значения от 0 до 255.
- smallint (2 байта). Диапазон значений от -215 (-32768) до 215 (3767).
- int (4 байта). Может содержать целочисленные данные от -231 (-2147483648) до 231 (21474833647).
- bigint (8 байт). Включает в себя данные от -263 (9223372036854775808) до 263 (9223372036854775807). Удобен для хранения очень больших чисел, не помещающихся в типе данных int.
Текстовые данные
- char. Содержит символьные не Unicode-данные фиксированной длины до 8000 знаков.
- varchar. Содержит символьные не Unicode-данные переменной длины до 8000 знаков.
- nchar. Содержит данные Unicode фиксированной длины до 4000 символов. Подобно всем типам данных Unicode его удобно использовать для хранения небольших фрагментов текста, которые будут считываться разноязычными клиентами.
- nvarchar. Содержит данные Unicode переменной длины до 4000 символов.
Десятичные данные
- decimal. Содержит числа с фиксированной точностью от -1038-1 до 1038-1. Он использует два параметра: точность и степень. Точностью называется общее количество знаков, хранящееся в поле, а степень – это количество знаков справа от десятичной запятой.
- numeric. Это синоним типа данных decimal – они идентичны.
Денежные типы данных
- money (8 байт). Содержит денежные значения от -263 до 263 с десятичной точностью от денежной единицы. Удобен для хранения денежных сумм, превышающих 214768,3647.
- smallmoney (4 байта). Содержит значения от -214748,3648 до 214748,3647 с десятичной точностью.
Данные с плавающей точкой
- float. Содержит числа с плавающей запятой от -1,79Е+38 до 1,79Е+38.
- real. Содержит числа с плавающей запятой от -3,40Е+38 до 3,40Е+38.
Типы данных даты и времени
- datetime (8 байт). Содержит дату и время в диапазоне от 1 января 1753 года до 31 декабря 9999 года с точностью 3,33 мс.
- smalldatetime (4 байта). Содержит дату и время, начиная от 1 января 1900 года и заканчивая 6 июнем 2079, с точностью до 1 минуты.
Двоичные типы данных
- binary. Содержит двоичные данные фиксированной длины до 8000 байт.
- varbinary. Содержит двоичные данные переменной длины до 8000 байт.
Специализированные типы данных
- sql_variant. Используется для хранения значения с различными типами данных.
- timestamp. Используется для установки временных меток записей при вставке, которые соответствующим образом обновляются. Удобен для отслеживания изменений в данных.
- uniqueidentifier. Глобальный уникальный идентификатор.
- xml. Используется для хранения целых документов или фрагментов XML.

Создание пользовательских типов данных

SQL Server позволяет на основе системных типов данных создавать пользовательские типы со всеми предварительно заданными параметрами, включая все ограничения и умолчания. В качестве примера создадим тип данных phone, который будет использоваться в таблице Customer для хранения телефонного номера клиента. Для его создания воспользуемся графическим интерфейсом утилиты Management Studio.

В дереве обозревателя объектов раскройте папки «Базы данных – Sales – Программирование — Типы». В контекстном меню узла «Определяемые пользователем типы данных» выберите команду «Создать определяемый пользователем тип данных».
В появившемся окне в текстовом поле «Имя» введите phone. В раскрывающемся списке «Тип данных» выберите nchar. В качестве длины введите 10. Отметьте параметр «Разрешить значения null», чтобы иметь возможность не указывать телефонный номер при добавлении нового клиента.
В секции «Привязки» оставьте пустые значения и щелкните на кнопке Ok. Созданный пользовательский тип данных должен появиться в дереве обозревателя объектов.

Создание таблиц

Создадим в базе данных Sales пять таблицы. Первая таблица, Customer, будет хранить информацию о клиентах, вторая таблица City – справочник городов, третья, Product, — информацию о товарах, четвертая, Order, будет содержать подробную информацию о заказах и пятая, OrdItem, — о составе заказа (перечне товаров входящих в заказ). Ниже представлены все поля этих таблиц и их основные свойства.

Таблицы можно создавать как в графическом интерфейсе (в утилите Management Studio), так и с помощью кода T-SQL. Воспользуемся самым простым, графическим способом. Сначала создадим таблицу Customer:

В дереве обозревателя объектов в базе данных Sales в контекстном меню узла «Таблицы» выберите команду «Создать таблицу…». В рабочей области должна появиться вкладка с конструктором таблиц.
В первую строку в столбце «Имя столбца» введите IdCust, в столбце «Тип данных» выберите int. Убедитесь что параметр «Разрешить значения null» отключен.
В нижней половине экрана в разделе «Свойства столбцов» введите описание поля и измените значение параметра «Спецификация идентифицирующего столбца/(Является идентифицирующим столбом)» на «Да» для того чтобы значения номера клиента формировались автоматически. Свойство «Идентифицирующий столбец» (Identity), обычно используемое совместно с типом данных int, предназначено для автоматического приращения значения на единицу при добавлении каждой новой записи. К примеру, клиент, добавленный в таблицу первым, будет иметь значение идентификатора 1, вторым – 2, третьим – 3, и т.д.
Аналогичным образом введите описания всех остальных полей и закройте окно конструктора таблиц. Введите в качестве имени таблицы Customer. Вновь созданная таблица должна появиться в дереве обозревателя объектов в папке «Таблицы».

Задание для самостоятельной работы: В соответствие с вышеприведенным описанием создайте оставшиеся четыре таблицы: City, Product, Order и OrdItem.

Еще записи по теме

www.ikasteko.ru

Табличные функции и временные таблицы в Transact-SQL | Info-Comp.ru

Сегодня мы поговорим об очень полезном функционале языка Transact-SQL — это табличные функции. Так как у нас сайт для начинающих программистов, а такие программисты по началу боятся использовать табличные функции или просто не знают, зачем они нужны. Поэтому я решил рассказать про основы создания таких функций. А также рассказать про использование временных таблиц в процедурах или SQL инструкциях.

Табличные функции в Transact-SQL – описание и примеры создания

Раньше мы уже знакомились с функциями, которые возвращают таблицу, правда, на языке PL/pgSQL для сервера PostgreSQL (Написание табличной функции на PL/pgSQL). Теперь пришло время поговорить о такой реализации на Transact-SQL.

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

Пример создания простой табличной функции

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

Примечание! Данный пример можно реализовать и с помощью представления. Но мы пока только учимся писать такие функции.

   
   --название нашей функции
   CREATE FUNCTION [dbo].[fun_test_tabl]  
   (    
   --входящие параметры и их тип
   @id INT
   )
   --возвращающее значение, т.е. таблица
   RETURNS TABLE
   AS
   --сразу возвращаем результат
   RETURN 
   (
        --сам запрос
        SELECT * FROM table WHERE id = @id
   )
   GO

В итоге мы создали функцию, в которую будем передавать один параметр id, его мы используем в условии исходного SQL запроса.

Получить данные из этой функции можно следующим образом:

   
   SELECT * FROM dbo.fun_test_tabl (1)

Как видите все проще простого. Теперь давайте создадим функцию уже с использованием программирования в этой функции.

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

   
   --название нашей функции
   CREATE FUNCTION [dbo].[fun_test_tabl_new]  
   (    
   --входящие параметры
   @number INT
   )
   --возвращающее значение, т.е. таблица с перечислением полей и их типов
   RETURNS @tabl TABLE (id INT, number INT, summa MONEY)
   AS
   BEGIN
           --объявляем переменные
           DECLARE @var MONEY
           --выполняем какие-то действия на Transact-SQL
           IF @number >=0 
           BEGIN 
                SET @var=1000
           END
           ELSE
                        SET @var=0
                --вставляем данные в возвращающий результат
                INSERT @tabl
                        SELECT id, number, summa 
                        FROM tabl 
                        WHERE summa > @var
           --возвращаем результат
           RETURN 
   END

Здесь мы уже программируем и можем выполнять любые действия как в обычных функциях и процедурах, при этом получая результат в виде таблицы. В этом примере мы передаем один параметр внутрь нашей функции (их может быть несколько!), внутри функции мы уже смотрим, что за параметр к нам пришел, и на основе этого уже формируем условие для запроса. Как Вы понимаете это тоже простой пример, но можно писать очень и очень сложные алгоритмы как в процедурах, именно поэтому, и созданы эти табличные функции.

Теперь давайте обратимся к нашей функции, например, вот так

   
   SELECT * FROM dbo.fun_test_tabl_new (1)

Временные таблицы в Transact-SQL — описание и пример создания

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

Например, в процедуре Вам необходимо выполнять много расчетов и запоминать их результат, а создавать дополнительные представления, табличные функции или вообще таблицы, которые потом нужно удалять, так как они Вам после выполнения процедуры будут не нужны, Вам просто не охота, да и это не очень удобно. Поэтому в Microsoft SQL Server существует такая возможность как «Временные таблицы». Давайте научимся их создавать. А создавать и использовать их очень просто, например, в коде своей процедуры, Вы хотите сохранить результат в таблицу, для этого просто создаете эту таблицу вот так (перед названием таблицы ставится знак решетки #).

   
   CREATE TABLE #tmp(column1 INT, column2 INT, column3 MONEY)
        INSERT INTO #tmp(column1, column2, column3)
                SELECT data1, data2, data3 
                FROM table
   --Затем по коду ниже Вы ее можете использовать, а в конце процедуры удалить ее 
   DROP TABLE #tmp

Таким образом, у Вас в базе фактически таблица не будет создана (и Вы ее нигде не увидите и, соответственно, не сможете обратиться к ней), она будет создана только для этой сессии, и обратиться к ней можно только из этой сессии, в которой Вы ее создали. То есть, даже если Вы создаете такую таблицу в одном окне запроса Management Studio, то из другого окна, Вы к ней не сможете обратиться. Это упрощает разработку процедуры и в некоторых случаях сильно ее ускоряет (в качестве альтернативы можно использовать табличные переменные).

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

Для более детального изучения языка Transact-SQL рекомендую книгу «Путь программиста T-SQL», в которой я очень подробно рассказываю про все основные конструкции и возможности данного языка.

Удачи!