char Vs varchar

char Vs varchar

Most common rule for any database systems if you have fixed length column use "char" data type else, (if you have variations) then use "varchar". but after read some forum post, noticed this is not common for less variation columns(less than 10 digits columns). I have created sample script to validate this as follows,

USE tempdb

GO

DECLARE

  @char CHAR(5) = 'ABCD',

  @nchar NCHAR(5) = N'ABCD',

  @varchar VARCHAR(10) = 'ABCD',

  @nvarchar NVARCHAR(10) = N'ABCD';

SELECT

  DATALENGTH(@char) AS CharDataLength,

  DATALENGTH(@nchar) AS NcharDataLength,

  DATALENGTH(@varchar) AS VarcharDataLength,

  DATALENGTH(@nvarchar) AS NvarcharDataLength

GO

-------------------------------------------------------------

CREATE TABLE #char(strchar char(8));

CREATE TABLE #varchar(strvarchar varchar(8));

GO

INSERT INTO #char VALUES('A');

INSERT INTO #char VALUES('AB');

INSERT INTO #char VALUES('ABC');

INSERT INTO #char VALUES('ABCD');

INSERT INTO #char VALUES('ABCDE');

INSERT INTO #char VALUES('ABCDEF');

INSERT INTO #char VALUES('ABCDEFG');

INSERT INTO #char VALUES('ABCDEFGH');

INSERT INTO #varchar VALUES('A');

INSERT INTO #varchar VALUES('AB');

INSERT INTO #varchar VALUES('ABC');

INSERT INTO #varchar VALUES('ABCD');

INSERT INTO #varchar VALUES('ABCDE');

INSERT INTO #varchar VALUES('ABCDEF');

INSERT INTO #varchar VALUES('ABCDEFG');

INSERT INTO #varchar VALUES('ABCDEFGH');

GO 10000

SET STATISTICS IO ON

SELECT * FROM #char;

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

SELECT * FROM #varchar;

SET STATISTICS IO OFF

EXEC sp_spaceused '#char';

GO

EXEC sp_spaceused '#varchar';

GO

DROP TABLE #char;

DROP TABLE #varchar;

with above script statistics you will noticed less space used (and less pages) for "char" column apart from "varchar" column. 

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. 

Hierarchyid DataType in SQL Server

Hierarchyid DataType in SQL Server

SQL Server 2012 will introduced new data type call hierarchyid. This data type makes it easier to store and query hierarchical data.This does not automatically represent a tree you have to used hierarchyid function as follows, 

use tempdb
GO
IF OBJECT_ID('EmployeeWithHierarchyID') IS NOT NULL BEGIN
DROP TABLE EmployeeWithHierarchyID;
END
GO
CREATE TABLE EmployeeWithHierarchyID
(
    [Id] hierarchyid not null primary key,
    [Name] nvarchar(50) not null
)
GO
INSERT INTO EmployeeWithHierarchyID ([Id], [Name]) VALUES
    (hierarchyid::GetRoot(), 'General Manager'),
    ('/1/', 'Manager'),
    ('/1/1/', 'Assistant Manager')
GO
SELECT
    Id,
    Id.ToString() AS [Path],
    Id.GetLevel() AS [Level],
    Name
FROM EmployeeWithHierarchyID
GO