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 scenarioswhere 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 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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s