SQL SERVER – Presenting 4 Technology Sessions at Great Indian Developer 2014 – Contest

The Great Indian Developer Conference (GIDS) is one of the most popular annual event held in Bangalore. This year GIDS is scheduled on April 22, 25. I will be presenting total four sessions at this event and each session is very different from each other.

Event Location: J. N. Tata Auditorium
National Science Symposium Complex (NSSC)
Sir C.V.Raman Avenue, Bangalore, India

Event Date: April 22-25, 2014 (Tuesday-Friday)

Website: http://www.developermarch.com/developersummit/ (You can find registration information over here).

I will be presenting at total 4 sessions during this event.

Indexes, the Unsung Hero

April 22, 2014 – Time:13:35-14:35 | 60 mins | GIDS.Net | Conference

Slow Running Queries are the most common problem that developers face while working with SQL Server. While it is easy to blame SQL Server for unsatisfactory performance, the issue often persists with the way queries have been written, and how Indexes has been set up. The session will focus on the ways of identifying problems that slow down SQL Server, and Indexing tricks to fix them. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session.
Indexes are the most crucial objects of the database. They are the first stop for any DBA and Developer when it is about performance tuning. There is a good side as well evil side to indexes. To master the art of performance tuning one has to understand the fundamentals of indexes and the best practices associated with the same. We will cover various aspects of Indexing such as Duplicate Index, Redundant Index, Missing Index as well as best practices around Indexes.

SQL Server Performance Troubleshooting: Ancient Problems and Modern Solutions

April 22, 2014 – Time:15:55-16:55 | 60 mins | GIDS.Net | Conference

Many believe Performance Tuning and Troubleshooting is an art which has been lost in time. However, truth is that art has evolved with time and there are more tools and techniques to overcome ancient troublesome scenarios. There are three major resources that when bottlenecked creates performance problems: CPU, IO, and Memory. In this session we will focus on High CPU scenarios detection and their resolutions. If time permits we will cover other performance related tips and tricks.
At the end of this session, attendees will have a clear idea as well as action items regarding what to do when facing any of the above resource intensive scenarios. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. To master the art of performance tuning one has to understand the fundamentals of performance, tuning and the best practices associated with the same. We will discuss about performance tuning in this session with the help of Demos.

MySQL Performance Tuning – Unexplored Territory

April 25, 2014 – Time:10:45-11:30 |45 mins | GIDS.Data | Conference

Performance is one of the most essential aspects of any application. Everyone wants their server to perform optimally and at the best efficiency. However, not many people talk about MySQL and Performance Tuning as it is an extremely unexplored territory. In this session, we will talk about how we can tune MySQL Performance. We will also try and cover other performance related tips and tricks. At the end of this session, attendees will not only have a clear idea, but also carry home action items regarding what to do when facing any of the above resource intensive scenarios. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. To master the art of performance tuning one has to understand the fundamentals of performance, tuning and the best practices associated with the same. You will also witness some impressive performance tuning demos in this session.

Hidden Secrets and Gems of SQL Server We Bet You Never Knew

April 25, 2014 – Time:15:00-17:45 | 150 mins | GIDS.Tutorials | Conference

It really amazes us every time when someone says SQL Server is an easy tool to handle and work with. Microsoft has done an amazing work in making working with complex relational database a breeze for developers and administrators alike. Though it looks like child’s play for some, the realities are far away from this notion. The basics and fundamentals though are simple and uniform across databases, the behavior and understanding the nuts and bolts of SQL Server is something we need to master over a period of time.

With a collective experience of more than 30+ years amongst the speakers on databases, we will try to take a unique tour of various aspects of SQL Server and bring to you life lessons learnt from working with SQL Server. We will share some of the trade secrets of performance, configuration, new features, tuning, behaviors, T-SQL practices, common pitfalls, productivity tips on tools and more.

This is a highly demo filled session for practical use if you are a SQL Server developer or an Administrator. The speakers will be able to stump you and give you answers on almost everything inside the Relational database called SQL Server.

Surprise Gift

If you attend my session, I will be asking one question at the end of the each of my sessions. If you get it right, you can win something surprise gift from me.

Must Attend Sessions

Besides above four sessions of mine, I will be attending the following sessions while I am at GIDS. Read the blog post of Vinod Kumar for additional information about his session.

April 22, 2014 – Time:10:35-11:35
SQL Server Management Studio – Tips and Tricks ~ Vinod Kumar

April 22, 2014 – Time:11:45-12:45
Architecting SQL Server HA and DR Solutions on Windows Azure  ~ Vinod Kumar

April 22, 2014 – Time:14:45-15:45
Understanding Windows Better Using SysInternals ~ Vinod Kumar

April 25, 2014 – Time:14:05-14:50
Introduction to Microsoft Power BI ~ Vinod Kumar

April 25, 2014 – Time:14:05-14:50
Lazy Commit Like NoSQL with SQL Server ~ Balmukund Lakhani

April 25, 2014 – Time:15:00-17:45
Hidden Secrets and Gems of SQL Server We Bet You Never Knew ~ Balmukund Lakhani, Pinal Dave, Vinod Kumar

NOTE: If you are not going to attend this event, sign up for the newsletter over here: http://bit.ly/sqllearn. I will be sharing my code, demo, slides and all the relevant information in the newsletter.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

Developers – Drive by Daniel Pink – Book Review

I have been an avid reader of books. I read pretty much one or two books every week. I believe reading helps me a lot in my day job as well as well writing blog post. I am an evangelist and my primary responsibility is to spread the love for the technology. When I read more, I learn more and that effectively leads me to communicate better with my audience. Here is one of the recent book which I read and I loved it. This book is about what motivates us and what is the best job for us. I have read this book a second time and I think finally I have digested the meaning of this book. I love every bit of it and if you do not have it, you should get it.

I am going to give 10 Drive physical books to 10 people who register for my newsletter over here.

If you want to own the book, you can get it from Amazon |Flipkart.


Here are essential few quotes from the book.

The joy of the task was its own reward.

When money is used as an external reward for some activity, the subjects lose intrinsic interest for the activity.

Rewards can deliver a short-term boost – just as a  jolt of caffeine can keep you cranking for a few more hours. But the effects wears off – and, worse, can reduce a person’s longer term motivation to continue the project.

Enjoyment-based intrinsic motivation, namely how creative a person feels when working on the project, is the strongest and most pervasive driver.

Intrinsic motivation is of great importance for all economic activities. It is inconceivable that people are motivated solely or even mainly by external incentives.

Intrinsic motivation is conductive to creativity; controlling extrinsic motivation is detrimental to creativity.

It is those who are least motivated to pursue extrinsic rewards who eventually receive them.

Greatness and nearsightedness are incompatible. Meaningful achievement depends on lifting one’s sights and pushing toward the horizon.

Any extrinsic reward should be unexpected and offered only after the task is complete.

Type Intrisic behavior emerges when people have autonomy over the four T’s: their task, their time, their technique, and their team.

If you want to work with more type Intresic’s the strategy is to become one yourself. Automony, it turns out, can be contagious.

There is complexity, autonomy, and a relationship between effort and reward in doing creative work, and that’s worth more to most of us than money.

Carrots & sticks are so last century. Drive says for 21st century work, we need to upgrade to autonomy, mastery & purpose.

This new approach has three essential elements: (1) Autonomy—the desire to direct our own lives; (2) Mastery—the urge to get better and better at something that matters; and (3) Purpose—the yearning to do what we do in the service of something larger than ourselves.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

MySQL – Finding First day and Last day of a Month

MySQL supports a lot of DATE and TIME related functions. If you want to find out last day of a month, you can make use of an inbuilt function named LAST_DAY.

SET @date:='2012-07-11';
SELECT LAST_DAY(@date) AS last_day;

The above code returns the value 2012-07-31

However, there is no inbuilt function to find out first day for a month. We will see two methods to find out the first day.

Method 1 : Use DATE_ADD and LAST_DAY functions

SET @date:='2012-07-11';
SELECT date_add(date_add(LAST_DAY(@date),interval 1 DAY),interval -1 MONTH) AS first_day;

Result is

first_day
 ----------
 2012-07-01

The logic is to find last day of a month using LAST_DAY function; Add 1 day to it using DATE_ADD function so that you will get first day of next month; Subtract 1 month from the result so that you will get first day of the current month

Method 2 : Use DATE_ADD and DAY functions

SET @date:='2012-07-11';
SELECT date_add(@date,interval -DAY(@date)+1 DAY) AS first_day;

Result is

first_day
 ----------
 2012-07-01

The logic is to find the day part of date; add 1 to it and subtract it from the date. The result is the first day of the month.

So you can effectively make use these functions to perform various datetime related logics in MySQL.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

MySQL – Generate Script for a Table Using SQL

In SQL Server, to generate the CREATE TABLE script for a table, you need to rely on the SQL Server Management Studio (SSMS) tool and there is no inbuilt function supported to do this using SQL. However, in MySQL you can generate the script for a table using SQL.

Let us create the following table

CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));

Now to view the script of the table sales, you can make use of SHOW CREATE TABLE statement. This statement accepts table name as parameter and returns the CREATE TABLE script for that table.

Run the following code

SHOW CREATE TABLE sales;

The resultset has two columns where the second column displays the following script.

CREATE TABLE 'sales' (
'sales_id' INT(11) NOT NULL AUTO_INCREMENT,
'item_id' INT(11) DEFAULT NULL,
'sales_date' DATETIME DEFAULT NULL,
'sales_amount' DECIMAL(12,2) DEFAULT NULL,
PRIMARY KEY ('sales_id')
)
ENGINE=InnoDB DEFAULT CHARSET=latin1;

Note : You can use the same SHOW CREATE TABLE statement to view the script for a VIEW although there is a seperate SHOW CREATE VIEW statement that accepts view name as a parameter.

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – How to Connect to MySQL Server on Command Prompt

I have so far authored 5 courses on MySQL at Pluralsight, and one of the most popular question, I keep on getting is how to connect to MySQL with Command Prompt. You may find it funny enough that people who are learning MySQL would know how to connect to command prompt, but trust me, we all start from somewhere and we do not have to be an expert at everything. I respect every single question and I would like to answer this very question over here.

Step 1: Go to the folder where the MySQL exe file is located

For example, in my machine the location of the MySQL execution file is at “C:\Program Files\MySQL\MySQL Server 5.6\bin”. I changed the context of the command prompt to the folder where the MySQL exe file is located.

Step 2: Enter following commands in the command prompt

mysql -h nameofthehost -u username -p

In the above command, replace nameofthehost with actual host name and username with actual username.  Now click on enter.

When clicked enter, it will ask for the password. Enter the password and click on enter.

It will connect you with MySQL Server.

Well – that’s it! You are connected to MySQL Server. Once you connect to MySQL Server, do not forget to continue watching MySQL Basics and Performance Tuning courses.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Professional Development – Dr W. Edwards Deming’s 14 Principles on Total Quality Management

I was just reading Dr. W. Edwards Demings 14 principles of Total Quality Management. It is indeed very impressive and interesting. I have tried to collect a few of the important resources related to the same over here.

Dr. Demings’s 14 principles

  1. Create a constant purpose toward improvement
  2. Adopt the new philosophy
  3. Cease dependence on mass inspection
  4. Use a single supplier for any one item
  5. Improve every process
  6. Create training on the job
  7. Adopt and institute leadership aimed at helping people do a better job
  8. Drive out fear
  9. Break down barriers between departments
  10. Get rid of unclear slogans
  11. Eliminate arbitrary numerical targets
  12. Permit pride of workmanship
  13. Implement education and self-improvement
  14. Make transformation everyone’s job

Here are few other interesting resources related to Dr. W Edwards Demings

  • Wikipedia page (Link)
  • Original White Paper with 14 Key Principles (Link)
  • Original Website of Demings Institute (Link)
  • SlideShare PPT (Link)

Here is the Official YouTube channel of Demings Institute. They have excellent videos and I strongly encourage everyone to view them.  Additionally, here is the famous Deming’s Red Bead Experiment Video.

Before I end the post I would like to include the video of Daniel Pink who has authored books Drive, which is very motivational book and often time I felt it resonated with the same message as Dr. Demings. This video is from TED presentation of Dan where he discussed The Puzzle of Motivation.

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

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – Search For Values Within A Comma Separated Values – FIND_IN_SET

MySQL has an inbuilt function called FIND_IN_SET which will search for values within a comma separated values. It basically returns the index position of the first parameter within the second parameter. This can be alternatively used to replace the IN clause.

Let us create the following tables

CREATE TABLE items(item_id INT, item_description VARCHAR(100));
CREATE TABLE sales(sales_id INT auto_increment KEY,item_id INT, sales_date DATETIME, sales_amount DECIMAL(12,2));
INSERT INTO items VALUES (1,'Television');
INSERT INTO items VALUES (2,'Mobile');
INSERT INTO items VALUES (3,'laptop');
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-01-01',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-01-02',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-09',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-01-29',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-11',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-16',1200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-16',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-20',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (2,'2014-02-22',200);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (3,'2014-02-24',1700);
INSERT INTO sales(item_id,sales_date,sales_amount) VALUES (1,'2014-02-24',1200);

Suppose you want to find out all sales made by Mobile and laptop. We can use IN clause as below
SELECT * FROM items NATURAL JOIN sales
WHERE item_description IN ('Mobile','laptop');

However you can also use FIND_IN_SET function to acheive the same

SELECT * FROM items NATURAL JOIN sales
WHERE FIND_IN_SET (item_description,'Mobile,laptop');

FIND_IN_SET accepts two parameters (first is column and second is column values seperated by commas).

Both the queries produce the following result

2 Mobile 2 2014-01-02 00:00:00 200.00
 3 laptop 3 2014-01-09 00:00:00 1700.00
 3 laptop 4 2014-01-29 00:00:00 1700.00
 3 laptop 5 2014-02-11 00:00:00 1700.00
 2 Mobile 7 2014-02-16 00:00:00 200.00
 2 Mobile 8 2014-02-20 00:00:00 200.00
 2 Mobile 9 2014-02-20 00:00:00 200.00
 2 Mobile 10 2014-02-22 00:00:00 200.00
 3 laptop 11 2014-02-24 00:00:00 1700.00

Well, technically it does not matter which option we use both the solution produces almost same results and performance, it is just an alternate way to search for values within a comma separated values.

Reference: Pinal Dave (http://blog.sqlauthority.com)