Tuesday, August 15, 2017

SQL Server Trace

SQL Server Trace

In SQL Server you need to find information, then most of DBA's using SQL Server Error Log.But in SQL Server Error Log Bit difficult to filter information and it does not display all information.

SELECT TG.*,TE.name FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_993.trc', default) TG INNER JOINsys.trace_events TE ON TG.EventClass=TE.trace_event_idWHERE DatabaseName='<YOUR DB NAME>';GO

Some information you can find table name start with "sys.trace_" but some pieces of information do not contain "trace"  tables. examples like "ObjectType" columns.  "ObjectType Trace Event Column" Microsoft Docs will help you to find more details.

Thursday, August 10, 2017

CREATE SCHEMA PL/SQL(Oracle)

CREATE SCHEMA PL/SQL(Oracle)

  • First Need to create table space for located all object belong to schema,
CREATE TABLESPACE TBS_DATABASE_USER_GROUP DATAFILE 'C:\ORACLE\tbs_database_user_group_01.dbf' SIZE 256M REUSE AUTOEXTEND OFF; 

  • Next Create  "TEMPORARY TABLESPACE" as follows,
CREATE TEMPORARY TABLESPACE TBS_TEMP_DATABASE_USER_GROUP TEMPFILE 'tbs_temp_database_user_group.dbf' SIZE 5M AUTOEXTEND ON;
  • Next Create User(or Schema)

CREATE USER Tharindu  IDENTIFIED BY password123
  DEFAULT TABLESPACE TBS_DATABASE_USER_GROUP
  TEMPORARY TABLESPACE TBS_TEMP_DATABASE_USER_GROUP
  QUOTA 20M on TBS_DATABASE_USER_GROUP;

  • Finally Grant for above created user 

GRANT create session TO Tharindu;
GRANT create table TO Tharindu;
GRANT create view TO Tharindu;
GRANT create any trigger TO Tharindu;
GRANT create any procedure TO Tharindu;
GRANT create sequence TO Tharindu;
GRANT create synonym TO Tharindu;

With using above steps, allow you to create oracle user!  

Tuesday, April 4, 2017

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