Table Partitioning - Basics

Today I want to revisit on SQL Server Table partitioning basics. To get started

Step 1 - Refresh the SQL Server Notes on Partitioning by Jose Barreto

Step 2 - Horizontal Vs Vertical Partitioning (Reference - Link)
Hortizontal Partition - Partition based on certain column value. Requires Partition Scheme, Partition Function and Partition Tables. Table will have all the columns
Vertical Partitioning - Divide table into multiple tables with fewer columns

Step 3 - Experiment Hortizontal Partitioning
a. Partition Function - A partition function specifies how the table or index is partitioned
b. Partition Scheme - Defines File groups mapping
c. Creating Partitioned Table

Creating a Partitioned Table for Class (Partitioned based on Class 1-12). When we query on a class only the particular partition is queried.
--STEP 1
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'TestPartitionFunction')
DROP PARTITION FUNCTION TestPartitionFunction;
GO
create partition function TestPartitionFunction (int) as range for values (1,2,3,4,5,6,7,8,9,10);

--STEP 2
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'TestPartitionScheme')
DROP PARTITION SCHEME TestPartitionScheme;
GO
create partition scheme TestPartitionScheme as partition TestPartitionFunction all to ([PRIMARY]);
GO

--STEP 3
IF EXISTS (SELECT * FROM sys.tables
WHERE name = 'CLASSTable')
DROP TABLE CLASSTable;
GO

--STEP 4
create table dbo.CLASSTable (Class int, Name VARCHAR(200), Studentid int) on TestPartitionScheme (Class);
GO
CREATE CLUSTERED INDEX CIX ON CLASSTable(Class) ON TestPartitionScheme(Class)
GO

--STEP 5 Insert Data
declare @i int, @j int, @k int;
set @i=1;
set @j=100;
set @k = 1;
while (1=1)
begin;
INSERT INTO CLASSTable (Class, Name, Studentid)
VALUES (@i, Convert(VARCHAR(100),@j)+'Name',@j)
set @j=@j+1;
set @k = @k+1;
if(@k>100)
begin
set @k = 0;
set @j = @j+100;
set @i = @i+1;
end
if(@i>10)
begin
break;
end
end;
go

--Data in Each Partition
SELECT $PARTITION.TestPartitionFunction(class) AS Partition, COUNT(*) AS [COUNT]
FROM dbo.CLASSTable
GROUP BY $PARTITION.TestPartitionFunction(class)
ORDER BY Partition ;

--Data Searched only in required partition
SELECT *,$partition.TestPartitionFunction(Class) AS 'Partition Detail'
FROM CLASSTable WHERE Studentid=108

--Verify
set statistics profile on;
SELECT * FROM dbo.CLASSTable WHERE Studentid = 108 and Class = 1

Related Reads - Enabling Partition Level Locking in SQL Server 2008
Using Filtered Statistics with Partitioned Tables.
Partitioned Tables and Indexes in SQL Server 2005
Partitioning Summary: SQL Server 2005 vs SQL Server 2008


Happy Learning!!!

Alogorithm Problems

Alogorithm Problems
Question #1 - Given an array of N integers in which each element from 1 and N-1 occurs once and one occurs twice. Write an algorithm to find duplicate integer. Note- You may destroy the array.
Solution1:
Example Array is {1,2,4,7,8,9,4}
Use Negation to Identify the duplicate one. When you negate every element when you parse it would be like
{0,0,4,0,0,0}. Since 4 occurs more than once for the second time it would be 0-(-4). The resulting <> 0 number in the array is the duplicate occurence.
Note: Same logic can be used to count number of unique elements in an array.

Solution 2: Use XOR for solving this problem
Question#2 - Write a function to reverse a word in place O(n) time and O(1) space.
Solution:
void ReverseWord( char *t, int len)
{
  if(len<=1) return 1;
  Swap(&s[0],&s[len-1]);
  ReverseWord(s+1,len-2);
}

Question#3- Find Longest Sequence Palindrome in a String "abcabcad"
This is a Dynamic Programming problem.
Answer: Link1, Link2

Dynamic Programming
Good problems and answers
From the above link posting the solution
Let us define the maximum length palindrome for the substing x[i,...,j] as L(i,j)
Procedure compute-cost(L,x,i,j)

Input: Array L from procedure palindromic-subsequence; Sequence x[1,...,n] i and j are indices
Output: Cost of L[i,j]
if i = j:
    return L[i,j]
else if x[i] = x[j]:
    if i+1 < j-1:
        return L[i+1,j-1] + 2
    else:
         return 2
else:
return max(L[i+1,j],L[i,j-1])

Read Quote of Michal Danilák's answer to Dynamic Programming: Are there any good resources or tutorials for Dynamic Programming besides TopCoder tutorial? on Quora

More Reads
Are there any good resources or tutorials for Dynamic Programming besides TopCoder tutorial ?

What are few must-do DP problems and why?

Happy Learning!!!!

Web Driver

WebDriver (Built on top of selenium by google. UI Testing Framework)
Simple Example
1. Installed webdriver from link http://code.google.com/p/selenium/?redir=1
2. Extract and start eclipse plug-in. Create a Java Project and Add the Extracted JAR files in the project

3. Copied the code from Link - http://google-opensource.blogspot.com/2009/05/introducing-webdriver.html. Modified the code to try it out in irctc.co.in
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;
public class TestPoc1 {
/**
* @param args
*/
public static void main(String[] args)
{
    // TODO Auto-generated method stub
    // Create an instance of WebDriver backed by Firefox
    WebDriver driver = new FirefoxDriver();
    driver.get("http://www.irctc.co.in");
    driver.findElement(By.linkText(("Find Agents"))).click();
    WebElement searchBox = driver.findElement(By.name("pincode"));
    searchBox.sendKeys("560001");
    driver.findElement(By.name("Submit")).click();
}
}
4. Run this code as Java Application in elipse editor
5. References
http://google-opensource.blogspot.com/2009/05/introducing-webdriver.html
http://code.google.com/p/selenium/wiki/FrequentlyAskedQuestions
http://code.google.com/p/selenium/w/list
http://blog.activelylazy.co.uk/2010/05/05/testing-asynchronous-applications-with-webdriver/
http://www.phpvs.net/2008/02/25/10-tips-for-building-selenium-integration-tests/
http://seleniumhq.org/docs/09_webdriver.html
http://www.theautomatedtester.co.uk/tutorials/selenium/xpath_exercise1.html
New Web Testing Tool -- WebDriver
What is Selenium WebDriver
Selenium Vs Webdriver
Getting Started with WebDriver (a Browser Automation Tool)
Good Reads

Tool #1 – WebDriver (Built on top of selenium by google. UI Testing Framework)

http://google-opensource.blogspot.com/2009/05/introducing-webdriver.html

http://code.google.com/p/selenium/wiki/FrequentlyAskedQuestions

http://code.google.com/p/selenium/w/list

Tool#2 -fighting-layout-bugs a library for automatic detection of layout bugs in web page

http://www.youtube.com/watch?v=WY3C6FHqSqQ

http://code.google.com/p/fighting-layout-bugs/

Learning about website performance optimization

Yep. Very Interesting topic. I am learning by attending sessions and from my peers on website perf optimization. Couple of basic ideas

Basics Learning
How HTML, CSS and JS Work

• HTML - Contains Text, body, paragraph
• CSS - Font, Color, Size is defined in CSS
• JS - Handling Events are defined in this file

Reference - How HTML, CSS and JS work together, CSS-Example
1. CDN (Content Delivery Network) - Delivering content based on Network Proximity. Duplicating data across multiple data centres. Downloads times would be reduced. More details CDN Link1, CDN Link2, CDN Link3
2. Image Spriting - Group Images together, Select Appropriate co-ordinates to display. One large image instead of several multiple small images. More Details Image Spriting1, CSS Sprites
3. Optimize the order of styles and scripts - I found this link extremenly good and self explanatory. Minimize round-trip times. "Browser delays rendering any content that follows a script tag until that script has been downloaded, parsed and executed".  By Adjusting the order download time can be minimized http://code.google.com/speed/page-speed/docs/rtt.html#PutStylesBeforeScripts
4. Combine external JavaScript - Combine multiple files into optimal files. http://code.google.com/speed/page-speed/docs/rtt.html#CombineExternalJS
5. Several other useful techniques Enable Compression, Optimize Images, Caching are provided..
6. A better way to load CSS

More Reading - http://code.google.com/speed/page-speed/docs/rules_intro.html
Even Faster Web Sites Presentation 2 - High Performance Web Sites
Best Practices for Speeding Up Your Web Site
WebPageTest Demo
Waterfalls 101: How to understand your site’s performance via waterfall chart
Let's make the web faster
Page Test Tools: Which results should you trust?
Three kinds of page test visuals, and the best ways to use them
JavaScript Architecture
Unit Testing JavaScript with FireUnit
RequestReduce - Instantly make any ASP.NET website faster with almost no effort!

Tools
Top 10 Free Online Broken Link Checkers
Top 10 Tools to Test Website Speed
Top 10 Websites That Let You Check If Links Are Safe


Happy Reading!!!

Free Technical Ebooks written by Expert Bloggers - Download

Pablo's S.O.L.I.D. principles ebook (best-practice object-oriented design principles)
Free SQL Server 2008 Ebook
Demystifying The Cloud
Visual Studio Performance Testing Quick Reference Guide (Version 3.5)
Pablo's 31 Days of Refactoring ebook
7 Freely Available Ebooks For .NET developers
Software Performance Testing Handbook - A Comprehensive Guide for Begineers
The Research-Based Web Design & Usability Guidelines
Web usability guidelines
High Performance Web Sites 14 rules for faster-loading pages
Free eBook: Linux 101 Hacks
Free Quick Reference and Cheat sheets in PDF Format.
Data Structures Cheat Sheet

• C# Quick Reference
• VB.NET Quick Reference
• Javascript Quick Reference 

Python E-Book Download
Selenium 1.0 Testing Tools: Beginners Guide
Just Enough Systems Engineering
Free ebook: Introducing Microsoft SQL Server 2008 R2
SSIS Step by Step Ebook
Free testing books

Happy Reading!!!

Learning Selenium - Part II

In continuation to my earlier post. One of my friend asked me Pagination in Selenium. I got help from one of my team members Avinash.

Learning's are
1. Install Firebug add in to firefox
2. Install Xpather add in to firefox
3. From Existing post I want to goto page 2 of Search Results
4. Choose Xpather Location as shown below

5. Select the Xpath for Selecting Second page
6. Add the below line of code as last line. Only from footer part I am selecting the xpath.
selenium.click("//div[@id='foot']/table[@id='nav']/tbody/tr/td[3]/a/span[1]");
7. Modified code snapshot as below

7. Search Results would take you to page 2 of results

Good Read - Identifying XPath of a element using XPath Query
Happy Reading!!!

Database Testing

[You may also like MSBI Testing, Database Performance Tuning]

This post is based on my experience as Database Tester. Database Testing involves the following Activities for a OLTP System

• Verify Table Schema, Column names as per Design Document
• Verify Column Length and DataType
• Verify Unicode Support (Storing Chinese/Japanese Charaters) - NVARCHAR Datatype
• Verify Indexes on Table (Clustered, Non-Clustered), Triggers for Auditing
• Verify Primary Key and Foreign Key Constraints defined on the Tables as in Design Document
• Verify Default Values of Columns, NOT NULLABLE columns, Constraints defined on Columns

Database Testing (Performance Tuning & Functional Testing)
This is more towards White-box testing, Verifing the SQL Statements, Queries in the Procedure from performance perspectice. The objective here is to ensure

• Verify Access Methods (Seeks over SCANS)
• Verify JOINs used and ensure its Optimal (Nested vs Merge Vs Hash)
• Very Logging and Auditing Enabled for Error Handling
• Verify Errors are logged and they contain Error Message, Supporting Details of Error
• Verify Coding Guidelines (Set based Operation vs Cursors, Try-Catch Block). Please refer coding guidelines section
• Verify Isolation levels used. Recommend used of Read-Committed Snapshot isolation level to avoid potential blocking issues
• Verify Rollback is handled in Transactions (Eg. Order Creation is failed due to PK Violation, Entire Transaction should be rolled back, All tables associated with the transaction need to be rolled back)
• Use Profiler, Activity Montiors, DMV queries to find top IO, CPU consuming queries, You can find more details in Performance Tuning Section

Related Reads -

Testing the Migration of Data From One Database to Another
SQLbits Session - Database Testing-Minimizing "If it can break, it will."
Database Testing - John Morrison's Blog
Agile database development 101
SQL University: Database testing and refactoring tools and examples
Database Security Testing
Tools for Everyday Testing – Part 1
Tools for Everyday Testing – Part 2
Tools for Everyday Testing – Part 3


Happy Reading!!!

Learning Eclipse IDE and Java Basics

Back to Java, Below links I found useful working and Debugging with Eclipse Editor

Ref1 - Java Tutorial #1 - Hello World
Ref2 - Eclipse and Java for Total Beginners
Ref3 - http://eclipsetutorial.sourceforge.net/totalbeginner.html
Ref4 - Eclipse and Java: Using the Debugger
Ref5 - Eclipse and Java: Introducing Persistence
Ref6 - Google I/O 2008 - Effective Java Reloaded
Ref7 - Java 10: Interfaces
Ref8 - Generating Javadoc in Eclipse IDE

Happy Learning!!!

Operators - Revisited

I shifted my focus to Linux-java. I always love SQL Server :). Thanks to Roji & Balmukund my SQL mentors. This blog post is on operators. This is based on my earlier presentation on SQLCommunity site link

Operators

• Fundamental Unit of Execution
• Blocking (Hash, Sort – In memory Operators) & Non-Blocking (Seeks, Scans etc..)
• Common Operators – Seek, SCAN, Bookmark Lookup, JOINs (Nested, Merge, Hash)
• Execution Plan need to be interpreted from right to left
• An operator can have More than One Input and One Output

Following different kinds of operators we will check

•  Join Operators
•  Bookmark lookup operator
•  Seek & Scan Operator
•  Spool Operator
•  Option for (Hint)
•  Fast N (Hint)

Example Walkthru
--STEP 1

CREATE TABLE TSCAN
(
    Col1 INT IDENTITY(1,1),
    Col2 VARCHAR(40)
)

--STEP 2
DECLARE @I INT
SET @I = 1
WHILE 1 = 1
BEGIN
    INSERT INTO TSCAN(Col2)
    VALUES(CONVERT(VARCHAR(20),@I)+'VALUE')
    SET @I = @I + 1
    IF @I > 10000
    BREAK;
END

--STEP 3 (Table SCAN)

SELECT Col1 FROM TSCAN WHERE Col1 = 100

Clustered Index Seek
--STEP 4
--Now Lets Create an Index on Col1
CREATE CLUSTERED INDEX CIX_TSCAN on TSCAN(Col1)

--STEP 5 (Clustered Index Seek)
SELECT Col1 FROM TSCAN WHERE Col1 = 100

--STEP 6 (Clustered Index SCAN)
SELECT Col1, Col2 FROM TSCAN WHERE Col2 = '100Value'

--STEP 7 (Demo Bookmark Lookup)

ALTER TABLE TSCAN
ADD COL3 CHAR(100)
--Populate Data
DECLARE @I INT
SET @I = 1
WHILE 1 = 1
BEGIN
    UPDATE TSCAN
    SET COL3 = (CONVERT(VARCHAR(20),@I)+'COL3')
    WHERE Col1 = @I
    SET @I = @I + 1
    IF @I > 10000
    BREAK;
END

--STEP 8 (Bookmark Lookup)
CREATE INDEX IX_COL3 ON TSCAN (COL3)
SELECT Col1, Col2 FROM TSCAN WHERE Col3 = '100COL3'

--STEP 9 (Resolving Key Lookup)

--Create Index on Col3, Col2 for above query to result in Index Seek
DROP INDEX TSCAN.IX_COL2_COL3
CREATE INDEX IX_COL2_COL3
ON TSCAN(COL3)
INCLUDE (COl2)

SELECT Col1, Col2 FROM TSCAN WHERE Col3 = '100COL3'

(Working with JOINS)

--NESTED LOOPS (Smaller Inner Sets, Join Columns Indexed)
CREATE TABLE NLOOPTable1(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE NLOOPTable2(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

--Populate Data
DECLARE @I INT
DECLARE @J INT
SET @I = 1
SET @J = 1
WHILE 1 = 1
BEGIN
    IF @I < 1000
    INSERT INTO NLOOPTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@I)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    IF @J < 100000
    INSERT INTO NLOOPTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@I)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @I = @I + 1
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--NESTED LOOP
SELECT
N1.Col1, N2.Col2
FROM NLOOPTable1 N1
JOIN NLOOPTable2 N2
ON N1.Col1 = N2.Col1

--Merge JOIN

--TWO Sorted (Indexed) Tables, Large Tables SQL Server Chooses Merge Join when the Join columns are indexed
CREATE TABLE MergeJOINTable1(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE MergeJOINTable2(Col1 INT IDENTITY(1,1) PRIMARY KEY, Col2 VARCHAR(40), Col3 VARCHAR(50))

DECLARE @J INT
SET @J = 1
WHILE 1 = 1
BEGIN
IF @J < 100000
    INSERT INTO MergeJOINTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    INSERT INTO MergeJOINTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--Merge JOIN
SELECT
N1.Col1, N2.Col2
FROM MergeJOINTable1 N1
JOIN MergeJOINTable2 N2
ON N1.Col1 = N2.Col1

 Hash Join
--Two Large Unindexed Tables, No Indexes Defined on Join Columns

CREATE TABLE HashJOINTable1(Col1 INT IDENTITY(1,1) , Col2 VARCHAR(40), Col3 VARCHAR(50))
CREATE TABLE HashJOINTable2(Col1 INT IDENTITY(1,1) , Col2 VARCHAR(40), Col3 VARCHAR(50))

--Populate Data
DECLARE @J INT
SET @J = 1
WHILE 1 = 1
BEGIN
IF @J < 100000
    INSERT INTO HashJOINTable1(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    INSERT INTO MergeJOINTable2(Col2, Col3)
    VALUES((CONVERT(VARCHAR(20),@J)+'VALUE'), (CONVERT(VARCHAR(20),@J)+'VALUE'))
    SET @J = @J + 1
    IF @J > 100000
    BREAK;
END

--Hash JOIN
SELECT
N1.Col1, N2.Col2
FROM HashJOINTable1 N1
JOIN HashJOINTable2 N2
ON N1.Col1 = N2.Col1

Index Recommendations in Green you can find. Happy Reading!!!!

Learning Perl & Linux

Yep I am back to learning Perl. I worked in Perl long back. Posting sample programs from my notes I took earlier...
Perl (Practical Extraction and Report Language) - Interpreted Language. They are not compiled. Programs are directly executed by the CPU. More Details in link

Linux Commands

• rm -i filename - remove file
• mv test test1 - rename file
• cp a.pl b.cp - copy file
• chmod - change the mode
• logname- print name of logged in user
• df - disk free, disk space usage
• grep - globally search for regular expression
• grep print test1.pl - search for print word in test1.pl file
• head -15 printdata.pl - list 15 lines of the file

Example 1 - Multiplication of Two Numbers
I learnt basic vi editor commands as well. :wq - save and quit, :i insert, :q - quit without save, vi filename (open / create file if it does not exists)

#!/usr/bin/perl
print 'Multiplication';
print 'A Number';
$aNumber = <>;
print 'B Number';
$bNumber = <>;
$result = $aNumber*$bNumber;
print 'result = ';
print $result;

Example 2 - Conditional Operators in Perl

#!/usr/bin/perl
$grade = <>;
if($grade < 8)
{
    print " < 8";
}
else
{
    print " > 8";
};
Example 3 - Dealing with text - strings

• ne - not equal
• ge - greater or equal
• gt - greater than
• le - less or equal
• lt - less than
• eq - equal

#!/usr/bin/perl
print 'program begin \n';
if ('a' eq 'a')
{
    print 'R1 - a is equal';
}
else
{
    print 'R1 - a is not equal to a ';
};
if ('a' lt 'b')
{
    print 'R2 - a less than b';
}
else
{
    print 'R2 - a is greater than b';
};
print '\n end of program';
<>;

Example 4 - Arrays in Perl
#!/usr/bin/perl
@array = (1,2,3,4);
$number = 0;
foreach $number (@array)
{
    print "$number\n"
}
<>;

More Reads

Linux Introduction (Commands)

View more presentations from anandvaidya.

50 Most Frequently Used UNIX / Linux Commands (With Examples)

In Linux Terminal

vi ginfo.sh

Insert below contents

echo "Hello" $USER

echo "Today is ";date

exit 0

Save the file

:wq

Provide permissions to execute

chmod 755 ginfo.sh

Run the below command

./ginfo.sh

Thanks to Sarabjit for helping me learn below commands

grep "searchkeyword" filenamecontains* > /tmp/test.op
vi /tmp/test.op
find -name "filenamecontains*" -exec grep "searchkeyword" {} \; > /tmp/test.op

find -name "filenamecontains*" -mmin -60 -exec grep "filenamecontains*" {} \; > /tmp/test.op (Modified last 60 mins)

More ReadsBasic Linux Commands

Happy Learning !!