70-461 – notes for ch. 2 – datatypes

arms

Varchar vs. char

To demo, creating two tables one with varchar and one with char

drop table aa.tax_dodgers

create table aa.tax_dodgers (name char(2000))

-- create a few rows
insert into aa.tax_dodgers values ('Google'), ('Amazon'), ('Facebook'),('Google'), ('Amazon'), ('Facebook'),
('Google'), ('Amazon'), ('Facebook'),('Google'), ('Amazon'), ('Facebook'),('Google'), ('Amazon'), ('Facebook'),
('Google'), ('Amazon'), ('Facebook'),('Google'), ('Amazon'), ('Facebook')

drop table aa.vtax_dodgers

create table aa.vtax_dodgers (name varchar(2000))

-- create a few rows
insert into aa.vtax_dodgers values ('Google'), ('Amazon'), ('Facebook'),('Google'), ('Amazon'), ('Facebook'),
('Google'), ('Amazon'), ('Facebook'),('Google'), ('Amazon'), ('Facebook'),('Google'), ('Amazon'), ('Facebook'),
('Google'), ('Amazon'), ('Facebook'),('Google'), ('Amazon'), ('Facebook')

then see how much space each takes up, by running sp_spaceused

sp_spaceused 'aa.tax_dodgers';
go
sp_spaceused 'aa.vtax_dodgers';
go

This gives:
table_space

Cast vs try_cast


SELECT CAST('Google tax avoidance hurts the NHS' AS INT);

gives

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'abc' to data type int.

Whereas

SELECT TRY_CAST('Google tax avoidance hurts the British Army' AS INT);

returns

NULL

Datatype precedence

The following:

select 1 + '1'

returns

2

because of

  • implicit datatype conversion
  • datatype precedence

The latter is defined here: Data Type Precedence (Transact-SQL). It means 1 + ‘1’ is 2, not 11!

An integer divided by an integer is an integer

This code

declare @a int = 5
declare @b int = 2
select @a/@b

gives

2

This will also return 2:

declare @a int = 5
declare @b int = 2
select convert(float, @a/@b)

…presumably because the calculation is done before the convert. i.e. Convert doesn’t work on both of the variables before conversion

To get it to return 2.5 you have to convert each of the variables separately.

declare @a int = 5
declare @b int = 2
select convert(float, @a)/ convert(float, @b)

Image from: Bell’s Cathedrals: The Cathedral Church of Salisbury by Gleeson White

Advertisements