View's column contains data which looks like UUID but the column type is int. Why?

Question

I've access to a view on a SQL Server 2016 database.

The column named 'id_key' contains such data:

id_key
D93F37FC-3C2A-EB11-B813-00505690E502
B03D37FC-3C2A-EB11-B813-00505690E502
AC644CFC-3C2A-EB11-B813-00505690E502

I've checked the type of the column: it's int

Truly, the result of:

    SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_NAME = 'yourTableName' AND 
     COLUMN_NAME = 'yourColumnName'

returns just int.

I've not found any explanation for that in SQL Server 2016 docs. Have I missed something? How int type store data which looks like strings/uuids?

Answer 1

If the view was not created using the WITH SCHEMABINDING option then the underlying tables that it references are freely able to change.

It is possible that the problematic column was originally using an int data type when the view was created but has subsequently changed to uniqueidentifier, e.g.:

drop view if exists dbo.yourViewName;
drop table if exists dbo.yourTableName;
go
create table dbo.yourTableName (
    ignore int,
    yourColumnName int
);
go
create view dbo.yourViewName --with schemabinding
as
    select yourColumnName as id_key
    from dbo.yourTableName
go
alter table dbo.yourTableName
    drop column yourColumnName
go
alter table dbo.yourTableName
    add yourColumnName uniqueidentifier
go
insert dbo.yourTableName (yourColumnName) values
    ('D93F37FC-3C2A-EB11-B813-00505690E502'),
    ('B03D37FC-3C2A-EB11-B813-00505690E502'),
    ('AC644CFC-3C2A-EB11-B813-00505690E502')
go
select * from dbo.yourViewName
go
select data_type
from information_schema.columns
where table_name = 'yourViewName'
and column_name = 'id_key'

Which yields:

id_key
------------------------------------
D93F37FC-3C2A-EB11-B813-00505690E502
B03D37FC-3C2A-EB11-B813-00505690E502
AC644CFC-3C2A-EB11-B813-00505690E502

data_type
----------
int

See the CREATE VIEW (Transact-SQL) documentation for more information.