Wednesday, June 29, 2011

Create Table with datatype examples


use master;
go
drop database TestDb;
GO
CREATE DATABASE TestDB;
GO
Use TestDB;
GO
CREATE SCHEMA TEST;
GO
CREATE TABLE TEST.Customers (
CustomerId int identity(100,1) NOT NULL,
Name nvarchar(70),
CreatedDateTime DateTime2,
CreditLimit decimal(13,5));
GO

create type Test.NAME from nvarchar(70);
create type Test.CURRENCYVALUE from decimal(14,5);
GO

alter table Test.Customers alter column name Test.Name not null;
alter table Test.Customers alter column CreditLimit Test.CURRENCYVALUE NULL;
alter table Test.Customers alter column CreatedDateTime datetime2 NOT NULL;
GO

insert Test.Customers (Name, CreatedDateTime, CreditLimit)
select top (100000)
so1.name, SYSDATETIME(),case when ABS(so1.object_id) > 10000000 then null else ABS(so1.object_id) end
from sys.all_objects so1 cross join sys.all_objects as sol2;

exec sp_spaceused @objname = 'Test.Customers',@updateusage='true';
Go

alter table Test.Customers rebuild with (data_compression=none);
GO

alter table Test.Customers rebuild with (data_compression=row);
GO

alter table Test.Customers rebuild with (data_compression=page);
GO