-- **************************************************************************
-- * COALESCE FUNCTION *
-- * *
-- * This functions returns the first non-null value of its arguments. *
-- * When all arguments are null; null is returned. *
-- **************************************************************************
exec spDeleteObj 't1', 3, 0
go
create table t1
(
id int identity(1,1) not null,
cola int,
colb int,
colc int
)
set nocount on
insert into t1 values (1, null, null)
insert into t1 values (null, 2, null)
insert into t1 values (null, null, 3)
insert into t1 values (null, null, null)
set nocount off
-- When none of the columns contains a value 999 is returned
select cast(coalesce(cola, colb, colc, 999) as money) * 2 ColX from t1
-- When all values are null the row is in the resultset
select id from t1 where coalesce(cola, colb, colc) IS NULL
-- **************************************************************************
-- * GROUP BY and HAVING clause *
-- * *
-- * Table "Customer" and table "Region" *
-- * - a customer belongs to 1 region. A region has 0 or more customersn *
-- * *
-- * Only the column on which is grouped can be in the selectlist. All other*
-- * columns in the selectlist must have a aggregate function. *
-- **************************************************************************
exec spDeleteObj 'Customer', 3, 0
exec spDeleteObj 'Region', 3, 0
go
create table Region
(
RegionId int identity(1,1) not null,
RegionName varchar(30) not null
constraint pk_region primary key clustered (RegionId)
)
set nocount on
insert into region (RegionName) values ('friesland')
insert into region (RegionName) values ('groningen')
set nocount off
create table Customer
(
CustomerId int identity(1,1) not null,
CustomerName varchar(30) not null,
CustomerAmount int not null,
CustomerRegion int not null
constraint fk_region foreign key (CustomerRegion) references region(RegionId)
)
set nocount on
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('a', 1, 1)
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('b', 2, 1)
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('c', 3, 1)
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('a', 4, 2)
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('b', 5, 2)
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('c', 6, 2)
set nocount off
select
region.RegionName,
sum(CustomerAmount) as Total
from
Customer inner join region on Customer.CustomerRegion = region.RegionId
group by
region.RegionName
select
region.RegionName,
sum(CustomerAmount) as total
from
Customer inner join region on Customer.CustomerRegion = region.RegionId
group by
region.RegionName
having
sum(CustomerAmount) > 7
-- *****************************************************************************************
-- * Tables, pages and extends:
-- * - Max rowsize is 8060 bytes
-- * - Max key length is 900 bytes
-- *
-- * - 1 page = 8 Kb = 8192 bytes
-- * - 1 extend = 8 pages = 8 * 8 = 64 Kb = 123136 bytes
-- *
-- * - Table contig has a rowsize of 900 + 7135 = 8060 bytes. So every row is 1 page.
-- * When we insert the first row 1 page (on the first extend is allocated).
-- * DBCC SHOWCONTIG scanning 'contig' table...
-- * Table: 'contig' (1461580245); index ID: 1, database ID: 8
-- * TABLE level scan performed.
-- * - Pages Scanned................................: 1
-- * - Extents Scanned..............................: 1
-- * ....
-- *****************************************************************************************
exec spDeleteObj 'contig', 3, 0
go
create table contig
(
id int identity(1,1) not null,
keyval char(900),
long1 char(7135)
)
create clustered index c_id on contig(keyval)
set nocount on
insert into contig values ('1', '1')
set nocount off
dbcc showcontig(contig)
