(테이블 구조)

[CHAR vs VARCHAR 비교 1]

use temp

select * from t

select * from t where a = 'aa'

select * from t where b = 'bb'

select * from t where a = 'aa '

select * from t where b = 'bb '

select * from t where a like 'aa'

select * from t where b like 'bb'

select * from t where a like 'aa '

select * from t where b like 'bb ' (X)

(결과 - SQL 문 순서대로)

[CHAR vs VARCHAR 비교 2]

(테이블 구조)

use temp

select * from tt

select * from tt where a = 'aa'

select * from tt where b = 'bb'

select * from tt where a = 'aa '

select * from tt where b = 'bb '

select * from tt where a like 'aa'

select * from tt where b like 'bb'

select * from tt where a like 'aa ' (X)

select * from tt where b like 'bb ' (X)

(결과 - SQL 문 순서대로)

[CTAS 문의 기능]

- Create Table as Select ... 문의 정확한 테이블 복사 기능

-- 테스트 코드

use master

DROP DATABASE [madang1]

create database madang1

go

use [madang1]

go

create table book (

bookid      int PRIMARY KEY,

bookname    varchar(40),

publisher   varchar(40) NOT NULL,

price       int

)

insert into book values(1,'축구의 역사','시공사',7000)

insert into book values(2,'2030 축구아는 여자','나무수',13000)

insert into book values(3,'축구의 이해','대한미디어',22000)

insert into book values(4,'골프 바이블','대한미디어',35000)

insert into book values(5,'피겨 교본','시공사',NULL)

create table orders (

orderid int  primary key,

custid  int  UNIQUE,

bookid  int references book(bookid),

saleprice int NOT NULL,

orderprice int CHECK( orderprice <= 50000 ),

orderdate smalldatetime

)

insert into orders values (1,1,1,7000,6000,'2012-07-01')

insert into orders values (3,2,5,8000,6000,'2012-07-03')

insert into orders values (4,3,2,6000,5000,'2012-07-04')

insert into orders values (5,4,3,20000,18000,'2012-07-05')

SELECT * INTO myorders FROM orders;

exec sp_help 'dbo.orders';

exec sp_help 'dbo.myorders';

ORDERS 테이블

MYORDERS 테이블

-아래 orderid와 saleprice의 Nullableaks no 이고 나머지 제약들이 사라진듯합니다.