sql_variant Vs varchar

sql_variant Vs varchar

I have done small comparison between  sql_variant Vs varchar data types. not seen big different than I have expected, but it's interesting information,

use [tempdb]

GO

IF OBJECT_ID('tblVarChar') IS NOT NULL BEGIN

DROP TABLE tblVarChar

END

IF OBJECT_ID('tblsql_variant') IS NOT NULL BEGIN

DROP TABLE tblsql_variant

END

GO

CREATE TABLE tblVarChar(name varchar(200));

CREATE NONCLUSTERED INDEX IX_tblVarChar ON tblVarChar (name);

CREATE TABLE tblsql_variant(name sql_variant);

CREATE NONCLUSTERED INDEX tblsql_variant ON tblsql_variant (name);

GO

INSERT INTO tblVarChar VALUES('http://www.databaseusergroup.com');

INSERT INTO tblsql_variant VALUES('http://www.databaseusergroup.com');

GO 100

INSERT INTO tblVarChar VALUES('255');

INSERT INTO tblsql_variant VALUES(CAST(255 AS tinyint));

GO 1000

INSERT INTO tblVarChar VALUES('32767');

INSERT INTO tblsql_variant VALUES(CAST(32767 AS smallint));

GO 1000

INSERT INTO tblVarChar VALUES('9223372036854775807');

INSERT INTO tblsql_variant VALUES(CAST(9223372036854775807 AS bigint));

GO 1000

INSERT INTO tblVarChar VALUES('2016-02-23 12:46:07');

INSERT INTO tblsql_variant VALUES(CAST('2016-02-23 12:46:07.347' AS smalldatetime));

GO 1000

PRINT 'tinyint'

SET STATISTICS IO ON

SELECT * FROM tblVarChar WHERE name='255';

GO

SELECT * FROM tblsql_variant WHERE name=255;

SET STATISTICS IO OFF

GO

PRINT '----------------------------------------------------------------------------------------'

PRINT 'smallint'

SET STATISTICS IO ON

SELECT * FROM tblVarChar WHERE name='32767';

GO

SELECT * FROM tblsql_variant WHERE name=32767;

SET STATISTICS IO OFF

GO

PRINT '----------------------------------------------------------------------------------------'

PRINT 'bigint'

SET STATISTICS IO ON

SELECT * FROM tblVarChar WHERE name='9223372036854775807';

GO

SELECT * FROM tblsql_variant WHERE name=9223372036854775807;

SET STATISTICS IO OFF

GO

PRINT '----------------------------------------------------------------------------------------'

PRINT 'smalldatetime'

SET STATISTICS IO ON

SELECT * FROM tblVarChar WHERE name='2016-02-23 12:46:07';

GO

SELECT * FROM tblsql_variant WHERE name='2016-02-23 12:46:07';

SET STATISTICS IO OFF

GO

PRINT '----------------------------------------------------------------------------------------'

PRINT 'varchar'

SET STATISTICS IO ON

SELECT * FROM tblVarChar WHERE name='http://www.databaseusergroup.com';

GO

SELECT * FROM tblsql_variant WHERE name='http://www.databaseusergroup.com';

SET STATISTICS IO OFF

GO

sp_spaceused 'tblVarChar';

GO

sp_spaceused 'tblsql_variant';

GO

SELECT DISTINCT SQL_VARIANT_PROPERTY(name,'BaseType') BaseType,

SQL_VARIANT_PROPERTY(name,'Precision') Precisions,

SQL_VARIANT_PROPERTY(name,'Scale') Scale,

SQL_VARIANT_PROPERTY(name,'TotalBytes') TotalBytes,

SQL_VARIANT_PROPERTY(name,'Collation') Collation,

SQL_VARIANT_PROPERTY(name,'MaxLength') MaxLengths

FROM      tblsql_variant;

GO

with above SQL example you will notice less page read for "smalldatetime" and "varchar" others almost same. but i'm bit wondering "tinyint" has more pages than "varchar". not really sure why this has allocated extra page. any way it's good if you are using "sql_variant" data type then ignore staring high allocated pages data types.