Create and Alter Tables Using T-SQL Syntax

(This is part of the Study Guide series, 70-457)

Microsoft’s Measured Skill description: This objective may include but is not limited to: create tables without using the built-in tools; ALTER; DROP; ALTER COLUMN; CREATE

What I see:

·         CREATE, ALTER, and DROP tables

·         ALTER COLUMN

CREATE, ALTER, and DROP Tables

                These are relatively basic T-SQL statements, and I won’t go into great depth on them.  Review BOL for these particular commands for a brush-up and some corner-case “gotchas”.

ALTER COLUMN

                The ALTER COLUMN T-SQL portion of the ALTER TABLE command is used to change a named column.  There is a list of scenarios where this is not possible, such as a column used in a PRIMARY KEY or FOREIGN KEY constraint.  Below shows some of these examples of what is possible and not possible:

use MeasureMySkills;

go

create table TestAlterColTable

(

       id int identity(1, 1) not null

              primary key clustered,

       SomeLongText nvarchar(512) not null

              default replicate('a', 512)

);

go

insert into TestAlterColTable

values(default);

go 10

select

       *,

       len(SomeLongText) as SomeLongText_length

from TestAlterColTable;

-- this is not possible due to truncate

alter table TestAlterColTable

alter column

       SomeLongText nvarchar(256);

go

-- this is possible

alter table TestAlterColTable

alter column

       SomeLongText nvarchar(1024);

go

-- also possible (no truncation)

alter table TestAlterColTable

alter column

       SomeLongText nvarchar(512) null;

go

insert into TestAlterColTable

values(null);

create unique index IX_TestAlterColTable_SomeLongText

on TestAlterColTable(id) include(SomeLongText);

go

-- not possible, as an index depends on the column

alter table TestAlterColTable

alter column

       SomeLongText varchar(512) not null;

go

References

·         BOL reference on CREATE TABLE

·         BOL reference on ALTER TABLE

·         BOL reference on DROP TABLE

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.