Exame 70-461: Create Database Objects – Parte 1

Olá!

Começarei a abordar o primeiro tópico do Exame 70-461, o Create Database Objects, que corresponde a 24% do exame, usarei a mesma divisão do site Microsoft Learning, que divide o tema em 5 tópicos, vamos ao primeiro:

Create and alter tables using T-SQL syntax (simple statements).
May include but not limited to: create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE

Os objetivos desse tópico são os comandos ALTER, DROP, ALTER COLUMN e CREATE do SQL Server, podemos usar uma base de dados qualquer para executar os comandos abaixo, eu criei um banco de dados DbBlog só por conveniência, poderia usar qualquer outro.

Para criar uma tabela num database utilizaremos o comando CREATE como no exemplo abaixo:

CREATE TABLE Clientes
(
CodCliente int,
Nome varchar(50),
Sobrenome varchar(50)
)

A sintaxe do comando é simples, “Clientes” é o nome da tabela, “CodCliente”, “Nome” e “Sobrenome” são as colunas da tabela e “int” e “varchar” são os tipos de dados de cada coluna, as especificações de tipos de dados (datatypes) são abordados no exame 70-462 (não lembro de ter caído no meu exame, mas tava no training kit), portanto, não entrarei em maiores detalhes sobre eles agora.

Depois de criada a tabela vamos inserir uma linha na tabela com o comando INSERT:

INSERT INTO Clientes (Nome) values ('a')

Gerando o resultado que pode ser conferido com o comando SELECT:

SELECT * FROM Clientes
CodCliente  Nome                                               Sobrenome
----------- -------------------------------------------------- -------------
NULL        a                                                  NULL

Como no comando INSERT eu preenchi somente o campo “Nome” com o valor ‘a’ e não havia nenhuma outra restrição quanto a possibilidade de inserção de valores nulos, os outros campos foram inseridos com valores NULL. Executando mais 3 vezes o mesmo INSERT o resultado será:

CodCliente  Nome                                               Sobrenome
----------- -------------------------------------------------- -----------
NULL        a                                                  NULL
NULL        a                                                  NULL
NULL        a                                                  NULL
NULL        a                                                  NULL

(4 row(s) affected)

Ou seja, criamos diversas linhas com valores repetidos, o que dificulta a organização da tabela.
Podemos especificar quais linhas podem conter valores NULL e quais devem ser obrigatoriamente preenchidas, para isso vou apagar a tabela e criar novamente com as restrições:

OBS: Já vou aproveitar e falar aqui sobre o comando DROP, não tem muito o que falar sobre ele, serve para apagar uma tabela de um database, basta selecionar o banco e executar um dos comandos abaixo:

DROP TABLE Clientes --selecionando só a tabela
DROP TABLE dbo.Clientes --selecionando a tabela e o schema (para quem usa mais de um schema)
DROP TABLE DbBlog.dbo.Clientes --essa é a forma mais segura de executar, evita um DROP acidental ao selecionar um database errado.
CREATE TABLE Clientes
(
CodCliente int NULL,
Nome varchar(50) NOT NULL,
Sobrenome varchar(50) NOT NULL
)

Agora somente a coluna CodCliente pode possuir valores NULL, tentando fazer o mesmo INSERT do exemplo anterior, onde o campo “Sobrenome” é nulo, é retornado uma mensagem de erro.

Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column ‘Sobrenome’, table ‘DbBlog.dbo.Clientes’; column does not allow nulls. INSERT fails.
The statement has been terminated.

Então vamos inserir também o campo Sobrenome e verificar como fica a tabela:

INSERT INTO Clientes (Nome, Sobrenome) values ('a','b')
CodCliente  Nome                                               Sobrenome
----------- -------------------------------------------------- ------------
NULL        a                                                  b

Mas ainda temos o mesmo problema anterior ao executar o INSERT várias vezes, várias linhas idênticas:

CodCliente  Nome                                               Sobrenome
----------- -------------------------------------------------- -------------
NULL        a                                                  b
NULL        a                                                  b
NULL        a                                                  b

(3 row(s) affected)

Podemos utilizar o parâmetro IDENTITY para campo CodCliente, criando um valor numérico que será adicionado automaticamente na coluna, criando um registro “único” (já explico o porquê das aspas).

CREATE TABLE Clientes
(
CodCliente int NOT NULL IDENTITY(1,1),
Nome varchar(50) NOT NULL,
Sobrenome varchar(50) NOT NULL
)

Fazendo o mesmo INSERT anterior algumas vezes temos o resultado:

CodCliente  Nome                                               Sobrenome
----------- -------------------------------------------------- -------------
1           a                                                  b
2           a                                                  b
3           a                                                  b
4           a                                                  b
5           a                                                  b

(5 row(s) affected)

OBS: Os valores passados no parâmetro IDENTITY(x,y) são: x=valor inicial e y=incremento a cada insert, caso fosse criado com o parâmetro IDENTITY(5,3) as 5 linhas do exemplo anterior na coluna CodCliente teriam os valores: 5, 8, 11, 14 e 17.

Aparentemente temos registros únicos, eles nunca serão repetidos, já que a coluna CodCliente é preenchida automaticamente, mas podemos desligar isso e adicionar registros repetidos ativando o parâmetro IDENTITY_INSERT, que permite inserir dados na coluna marcada como IDENTITY:

SET IDENTITY_INSERT Clientes ON
INSERT INTO Clientes (CodCliente, Nome, Sobrenome) values (1,'a','b')
CodCliente  Nome                                               Sobrenome
----------- -------------------------------------------------- -----------
1           a                                                  b
2           a                                                  b
3           a                                                  b
4           a                                                  b
5           a                                                  b
1           a                                                  b

(6 row(s) affected)

Para garantir mesmo que não haverão dados repetidos vamos criar uma PRIMARY KEY para o campo CodCliente da tabela:

CREATE TABLE Clientes
(
CodCliente int NOT NULL IDENTITY(1,1),
Nome varchar(50) NOT NULL,
Sobrenome varchar(50) NOT NULL
PRIMARY KEY(CodCliente)

Agora ao tentar inserir um registro já existente é mostrada a mensagem de erro:

Msg 2627, Level 14, State 1, Line 2

Violation of PRIMARY KEY constraint ‘PK__Clientes__DF8324D7AFE11E25’. Cannot insert duplicate key in object ‘dbo.Clientes’. The duplicate key value is (1).
The statement has been terminated.

O SQL Server criou a Primary Key com esse nome maluco da mensagem de erro (PK__Clientes__DF8324D7AFE11E25), podemos criar nomes personalizados para as ela, no caso criei com o nome PK_CODCLIENTE que facilita bastante a manutenção no futuro:

CREATE TABLE Clientes
(
CodCliente int NOT NULL IDENTITY(1,1),
Nome varchar(50) NOT NULL,
Sobrenome varchar(50) NOT NULL
)
GO
ALTER TABLE Clientes ADD CONSTRAINT [PK_CODCLIENTE]
PRIMARY KEY (CodCliente ASC)

Ao criar uma Primary Key (PK) normalmente é criado um clustered index para essa coluna. O “normalmente” eu coloquei porque há a possibilidade de criar PK’s usando índices non-clustered.

Agora vamos falar um pouco sobre o parâmetro DEFAULT, vamos criar a tabela Clientes com um campo DataCriacao do tipo datetime:

CREATE TABLE Clientes
(
CodCliente int NOT NULL IDENTITY(1,1),
Nome varchar(50) NOT NULL,
Sobrenome varchar(50) NOT NULL,
DataCriacao datetime
)

E fazendo um INSERT só com os campos Nome e Sobrenome temos o resultado abaixo:

CodCliente  Nome                 Sobrenome            DataCriacao
----------- -------------------- -------------------- -----------------------
1           a                    b                    NULL

(1 row(s) affected)

Podemos notar que o campo DataCriacao foi preenchido com NULL, já que não foi passado nenhum valor para a coluna no INSERT e esta permitia valores nulos.

Mas eu quero que esse campo seja preenchido automaticamente com a data da inserção do registro na tabela, para isso posso criar um valor DEFAULT para a coluna DataCriacao que será preenchido com a data atual (comando GETDATE) caso não seja passado valor para esse campo no insert.

CREATE TABLE Clientes
(
CodCliente int NOT NULL IDENTITY(1,1),
Nome varchar(20) NOT NULL,
Sobrenome varchar(20) NOT NULL,
DataCriacao datetime DEFAULT getdate()
)

Agora ao fazer o INSERT temos:

CodCliente  Nome                 Sobrenome            DataCriacao
----------- -------------------- -------------------- -----------------------
1           a                    b                    2012-09-10 19:14:57.413

(1 row(s) affected)

Nos exemplos anteriores, para alterar dados estruturais da tabela, como tipo de dados, inserção e remoção de colunas nós apagávamos a tabela e criávamos ela novamente, agora vamos fazer isso sem usar o DROP TABLE, com a mesma tabela criada anteriormente eu inseri um registro e ela está assim:

CodCliente  Nome                 Sobrenome            DataCriacao
----------- -------------------- -------------------- -----------------------
2           Bruno                Feldman              2012-09-13 19:09:30.337

(1 row(s) affected)

Agora vamos adicionar a coluna CEP na tabela:

ALTER TABLE Clientes
ADD CEP numeric(8)

E a coluna é criada na tabela, com valores NULL preenchidos nas colunas já existentes:

CodCliente  Nome                 Sobrenome            DataCriacao             CEP
----------- -------------------- -------------------- ----------------------- ---------------------------------------
2           Bruno                Feldman              2012-09-13 19:09:30.337 NULL

(1 row(s) affected)

Para remover alguma coluna, basta usar o DROP COLUMN no comando abaixo:

ALTER TABLE Clientes
DROP COLUMN CEP

OBS IMPORTANTE: Repare que para adicionar uma coluna usamos o comando ADD, e para remover usamos DROP COLUMN, veja que não usei ADD COLUMN e sim ADD, sem o COLUMN, mas ele é necessário para o DROP.

Para alterar um tipo de dado de uma tabela, usamos o ALTER COLUMN, no exemplo abaixo alterei a coluna CEP de numeric para int:

ALTER TABLE Clientes
ALTER COLUMN CEP int

Ou para mudar a restrição para aceitar ou não valores nulos:

ALTER TABLE Clientes
ALTER COLUMN DataCriacao datetime not null

Uma última observação, se tentarmos adicionar uma coluna com o parâmetro “not null” para uma tabela que já possui registros o SQL não vai permitir, pois ele adiciona a coluna com valores null para as linhas já existentes. O que podemos fazer é usar o ADD COLUMN com um valor DEFAULT para a coluna, que será preenchido com esse valor, ou criar a coluna permitindo valores nulos, depois fazer um UPDATE preenchendo valores nessa coluna e depois alterar para not null novamente.

E assim terminamos a primeira quinta parte do primeiro objetivo do exame 70-461, nos vemos no próximo post! Até lá!!

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s