TSQL Learning

This post is based on very good reading from Article T-SQL Misconceptions - JOIN ON vs. WHERE. These are new learning's for me


1. For Readability - Refrain from using search arguments in the ON clause, and use the WHERE clause instead
2. When Outer Join is Converted to Inner Join - Adding references to the table in the right side of a JOIN to the WHERE clause will convert the OUTER JOIN to an INNER JOIN. I'm trying out Example Code to reproduce the scenario. This Shows the behavior when Left Join in Query is Converted to Inner Join during Execution.


Step 1 - Create Tables

CREATE TABLE Table1(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

CREATE TABLE Table2(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

Step 2 - Populate Data

DECLARE @J INT

 SET @J = 1
 WHILE 1 = 1
 BEGIN
 IF @J < 1000
     INSERT INTO Table1(Col2, Col3)
     VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
     INSERT INTO Table2(Col2, Col3)
     VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
     SET @J = @J + 1
     IF @J > 1000
     BREAK;
 END


Step 3 - Run Queries

SELECT

 N1.Col1, N2.Col2
 FROM Table1 N1
 LEFT JOIN Table2 N2
 ON N1.Col1 = N2.Col1
 WHERE N2.Col1 = 100
 

SELECT

 N1.Col1, N2.Col2
 FROM Table1 N1
 LEFT JOIN Table2 N2
 ON N1.Col1 = N2.Col1
 WHERE N1.Col1 = 120


Step 4 - Execution Plan

Happy Learning!!!

Big Data Updates

Interesting Big Data Updates


[Good Read] - Big Data Analysis Takes a Big Bite Out of HPC Workloads: IDC
Summary - High Performance Servers used to run / benchmark big data workloads

[Learning Resource] - Hadoop 101: Programming MapReduce with Native Libraries

[Good Read] - Big Data Transforming Traditional DW Ecosystem - Why Hadoop and Solr in DataStax Enterprise?

Summary 

• NOSQL feasible alternative competing OLTP Space
• Big Data Replacing OLAP Ecosystem

Happy Reading!!!

MYSQL Exercises

Couple of learning exercises while working on MYSQL. Earlier MYSQL post please refer link
Learning #1 - Load data from flat file
Created a Temp table, Loaded bulk data using command - LOAD DATA LOCAL INFILE

CREATE TABLE TestTable2 ( A INT NOT NULL, B INT NOT NULL, C INT NOT NULL, D INT NOT NULL );

Dataset looks as below

LOAD DATA LOCAL INFILE 'E:\\DataUpload50K.txt' INTO TABLE TestTable2 FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n';

Reference - link1 , link2
Learning #2 - Enable Remote Access to MYSQL

Execute the Grant All Privileges command by specifying username, IP Address of remote host and password to logon
GRANT ALL PRIVILEGES ON *.* TO 'username'@'XX.XX.XX.XX' IDENTIFIED BY 'pwd';
Encountered error codes 1130, 1045
Reference - link1, link2, link3

Learning #3 - Show System Configuration Settings
Command - SHOW VARIABLES
Reference - link1

Happy Learning!!!

heidisql - SQL Interface to MSSQL and MYSQL

This quick post is based on tool shared by my colleague Ambuj. Heidisql - SQL Editor for MSSQL and MYSQL

Step 1 - Pretty simple and quick to install. Downloaded and Installed it from link

Step 2 - You can setup sessions and connect to MSSQL / MYSQL instances. I have used SSMS, Atlantis SQL Explorer. This single interface for both MYSQL and MSSQL is very useful.

Happy TSQL coding !!!

Compiled List of Short Talks on Career Success

Happy Learning!!!