SQL SERVER – An Inspiring Personal Story – Movie from The Book – Video Course – SQL Server Questions and Answers – Pluralsight

Nov 3, 2011 – Visit to Grandma

When our SQL Server Interview Questions and Answers book got published I ran to my grandma with a copy of the book for her blessings. Well, just like every grandma, she loves me, her grandson, unconditionally. She is not into the technology domain (obviously), but she loved the book. She read the first few pages where I was mentioned and read about my co-author Vinod Kumar. After reading the introduction she looked at me and said “When I was young we used to read books, now all those good books are converted into the movies, when do you think this book will be converted into a movie?

Silence. A very long silence.

I just could not answer her right away.  As a matter of fact, I went into a deep thought. I never thought that she could actually ask me such question. It may be a very simple question, but I felt that it was very important to me. I was just stunned and speechless. I returned home from my grandma’s place, with her blessings and an interesting thought of a “Movie from the Book”. I just could not get this out of my mind.

Jan 24, 2012 – Movie from Book

I talked to my grandma very often, but since the last conversation my wife called her often and I did not talk to her at all. There was no reason to talk to her maybe because I constantly felt that she is with me. Her blessings were with me. I could not get her question out of my mind, still. I was extremely motivated and inspired from my last conversation with her. I called her early morning on January 24. Her very first questions were how I was doing and how was her great granddaughter. I told her that regarding our last conversation, there is actually a movie which is inspired from the book. Her very first reaction was that she knew this would happen as she was confident that my book would reach milestones. We talked for long hours.

Today – Fast Forward

Well, the book was an inspiration for the course, but the video course was developed using totally different angle. We made sure that we’d take the broader idea from the book but wanted present something unique and novel. Vinod Kumar and I thought about it for a long time. We wanted to decide on the format of our course. We spent hours talking about the course – how we could make it unique and give a different experience to every attendee. We wanted to keep the subject light enough to digest, yet deep enough to satisfy everyone’s learning eagerness. After spending some days and efforts for the preparation of the unique content and exceptional demos, the course has become a reality for us.

SQL Server Questions and Answers

This course is designed to help you have a better understanding on how to use SQL Server effectively. The course presents many of the common misconceptions about SQL Server, and then carefully debunks those misconceptions with clear explanations and short but compelling demos, showing you how SQL Server really works. This course is for anyone who works in SQL Server databases, and basically for those who want to improve their knowledge and understanding of this complex platform.

In this course, each topic starts with a question and ends with an answer which is supplemented with a demo. Every question has an exercise file and a demo associated with it. Additionally, in the very last session, we have covered lots of SSMS tips and tricks. Here are the topics we have covered in this course:

  • Introduction
  • Retrieving IDENTITY value using @@IDENTITY
  • Concepts Related to Identity Values
  • Difference between WHERE and HAVING
  • Order in WHERE clause
  • Concepts Around Temporary Tables and Table Variables
  • Are stored procedures pre-compiled?
  • UNIQUE INDEX and NULLs problem
  • Locks and Duration of Transactions
  • Nested Transaction and Rollback
  • Understanding Date/Time Datatypes
  • Differences between VARCHAR and NVARCHAR datatypes
  • Precedence of DENY and GRANT security permissions
  • Identify Blocking Process
  • NULLS usage with Dynamic SQL
  • Appendix Tips and Tricks with Tools

Today we are very happy that our course is out. The journey from the book to the Training Course took 80 days and the experience was wonderful. In a future post, I will share the experience of writing book and building the training course.

In the end, we just want to thank YOU, our dear reader, who made this experience possible.

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

SQL SERVER – Puzzle to Win Print Book and Free 30 Days Online Training Material

Yesterday I had asked a simple question SQL SERVER – Puzzle to Win Print Book – Write T-SQL Self Join Without Using LEAD and LAG with keeping two simple intention.

  • We can all learn about new feature of SQL Server 2012
  • We can learn new feature of SQL Server 2012 while practicing on earlier version of SQL Server.

While I was creating question due to copy-paste error the question was not correctly created. In simple word – I made a mistake. This created some confusion and I feel bad about this. Here is what we will do. Please read the question again and attempt to answer the question which I have asked in the blog post.

Yesterday the give away was my SQL Server Interview Questions and Answers book. As the question was corrected after a while, the give away are now got sweeter.

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

SQL SERVER – Learning SSAS (SQL Server Analysis Services) Online in 6 Hours – Top Down Designing and Bottom Up Designing

Those who are following me on Twitter and Facebook know that recently I am reenforcing my own concept for SQL Server Analysis Services (SSAS). Like many of us, I worked with Analysis Services in early years. In an earlier job, I got many projects for relational database performance tuning and over time, I lost touch with SSAS. This does not mean that I forgot all of the concepts but the ‘real’ hands-on experience was gathering dust. Looking back at the last five years, I realized that I have deep experience with relational performance tuning but there are a few new things which I have yet to explore and learn.

My Learning

One weekend I decided to learn SQL Server Analysis Services. I woke up early in the morning at 6 AM and by 7 AM I was sitting with coffee at my dual screen monitor computer. I had a free subscription to Pluralsight so on one screen I turned on their Analysis Services Fundamentals course. This course is well organized and I attended first six modules by 1 PM. After having a quick lunch with my family, I reviewed all the tasks and lessons given in the course. As it was the weekend and I had properly digested all the concepts, I decided to continue the remaining four modules the next day. The next day, the same routine: I followed and finished all the remaining modules along with the exercises. At the end of six hours of video learning and practicing for more than 8 hours, I felt very comfortable with the subject. I re-discovered my knowledge of SSAS which I used to practice a few years ago, before I opted for performance tuning.

Course Details

You can read the official details of the course here yourself. Here is my view on this course. This course is very well structured. I have found many books and attended SSAS training where the training starts with Bottom Up Designing methodology. I have never had any course where the instructor started from Top Down Designing methodology. This course really got my attention as it covers the all the core concepts in a very reasonable time with crisp and clear instructions. After that it jumps to very easy to understand and follow-along demos where one can practice their skills. I am personally a big fan of the AdventureWorks database as that makes our life much easier and everybody has access to that database for free. Thanks to the instructor, Stacia, for using the same database for every single demonstration and exercise.

I have seen many demonstrations where someone takes an already-built cube and imports that into the system and starts explaining various concepts of the cube. I personally do the same when I have to demonstrate some cool new feature of the SSAS in a one hour presentation. However for in-depth learning, the whole game is different and I prefer to learn from the beginning. I prefer to learn how dimensions are built, how measures are categorized and how cubes are built from nothing. One always learns properly when the lesson is explained in structured manner. In this course Stacia has done the same thing – starting from no elements on screen and very quickly building all the modules from scratch. A perfect experience for beginners and experts.

Course Content

This course covers various topics and you can read all the documentations on the official course page. However, as an outline, here are the module names. I am also attaching the image which demonstrates that I have attended every single module of the course. If you stop the course any place during the lesson, when you come back the course content page tracks your progress; this is very convenient.

  • Introduction to Analysis Services
  • Dimensional Model Development
  • Dimension Development I
  • Dimension Development 2
  • Cube Development I
  • Cube Development 2
  • Calculations 1
  • Calculations 2
  • Database Processing
  • Security

Future Expectations

The title of the course says this is an Introduction to Analysis Services. However, I thought this was quite a comprehensive course. I am assuming that there will be another version of this course. Stacia  also hinted couple of times during the course what will be in the advanced course. Well, what I expect in the advanced course is that the quality and presentation stays the same.

One Last Thing

Well, when I watched this course – I only had an annual subscription. Since then I have upgraded to the Pluralsight Annual Plus subscription, which gives me complete access to all the files, exercises and downloadable videos. I just wish I had changed before I started this course as it changes the experience.

Win A 30 Day Subscription

I have one 30 day Pluralsight OnDemand learning subscription to give away. To win you just need to leave a comment to this blog post suggesting which course you want to take and why. One person will be randomly picked as a winner for this 30 day subscription.

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

SQLAuthority News – Pluralsight On-Demand FREE for SQL Server Course

The Moral of Story

You can watch the most popular SQL Server – TSQL course on Pluralsight On-Demand for FREE for the next 48 hours. It starts NOW!

The Story

Learning is always difficult. After learning how to apply your knowledge, learning in real life is even more difficult. Technology is moving faster than the speed of light and new technologies are always emerging – this is now the reality of the new technology world. Between all of this, I personally have very little time to learn new technology. I do not like eBooks (this statement warrants a whole new blog posts – some other time), I prefer regular books. It is becoming more and more difficult to carry around the books and read them with passion. It happened to me once that by the time I finished the book, there was a new version of the product out in the market.

Recently I noticed that I am reading more blogs than books. Blogs are great but they are not structured. Structured learning is the key when one wants to understand technology properly. From experience I have learned that watching videos at my convenience (on-demand) and following the labs along with the video is way easier. A good teacher cannot be replaced by 1,000 books. I find that I can learn fastest using online tutorial videos that are well structured.

All the attendees of the technology mega-event in India got free passes from Pluralsight On-Demand training. I watched two Pluralsight courses online and I found they are well structured and have lots of supporting material (lab, scripts, ppts). When I reported my good feedback to Pluralsight, they offered 48 hours free access to their MOST popular SQL Server – TSQL course, exclusively for SQLAuthority.com readers. I cannot let this offer pass when it can help so many of my readers.

The Beginning of the Real World

Try out the SQL Server – TSQL course right away as it is freely available for the next 48 hours only. Let me know your feedback about the course. This course is for every developer who uses SQL Server in their applications. It covers the fundamentals of using SQL Server from T-SQL to the CLR, to automating processing with SQL Server Integration services.

UPDATE: You do not need login or create account to watch T-SQL course. Just go to SQL Server – TSQL and click on links.

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

SQL SERVER – Public Training and Private Training – Differences and Similarities – Public Training vs Private Training

Earlier this year, I was on Road SQL Server Seminars. I did many SQL Server Performance Trainings and SQL Server Performance Consultations throughout the year but I feel the most rewarding exercise is always the one when instructor learns something from students, too.

I was just talking to my wife, Nupur – she manages my logistics and administration related activities – and she pointed out that this year I have done 62% consultations and 38% trainings. I was bit surprised as I thought the numbers would be reversed. Every time I review the year, I think of training done at organizations. Well, I cannot argue with reality, I have done more consultations (some would call them projects) than training.

I told my wife that I enjoy consultations more than training. She promptly asked me a question which was not directly related but made me think for long time, and in the end resulted in this blog post. Nupur asked me: what do I enjoy the most, public training or private training? I had a long conversation with her on this subject.

I am not going to write long blog post which can change your life here. This is rather a small post condensing my one hour discussion into 200 words.

Public Training is fun because…

  • There are lots of different kinds of attendees
  • There are always vivid questions
  • Lots of questions on questions
  • Less interest in theory and more interest in demos
  • Good opportunity of future business

Private Training is fun because…

  • There is a focused interest
  • One question is discussed deeply because of existing company issues
  • More interest in “how it happened” concepts – under the hood operations
  • Good connection with attendees
  • This is also a good opportunity of future business

Here I will stop my monologue and I want to open up this question to all of you:

Question to Attendees -

Which one do you enjoy the most – Public Training or Private Training?

Question to Trainers -

What do you enjoy the most – Public Training or Private Training?

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

SQL SERVER – Index Created on View not Used Often – Limitation of the View 12

I have previously written on the subject SQL SERVER – The Limitations of the Views – Eleven and more…. This was indeed a very popular series and I had received lots of feedback on that topic. Today we are going to discuss something very interesting as well.

During my recent performance tuning seminar in Hyderabad, I presented on the subject of Views. During the seminar, one of the attendees asked a question:

We create a table and create a View on the top of it. On the same view, if we create Index, when querying View, will that index be used?

The answer is NOT Always!

(There is only one specific condition when it will be used. We will write about that later in the next post).

Let us see the test case for the same. In our script we will do following:

USE tempdb
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
-- Create SampleTable
CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable (ID1,ID2,SomeData)
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
-- Create View
FROM dbo.mySampleTable
-- Create Index on View
-- Select from view
FROM SampleView

Let us check the execution plan for the last SELECT statement.

You can see from the execution plan. That even though we are querying View and the View has index, it is not really using that index.

In the next post, we will see the significance of this View and where it can be helpful.

Meanwhile, I encourage you to read my View series: SQL SERVER – The Limitations of the Views – Eleven and more….

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

SQLAuthority News – A Successful Performance Tuning Seminar at Pune – Dec 4-5, 2010

This is report to my third of very successful seminar event on SQL Server Performance Tuning.

SQL Server Performance Tuning Seminar in Colombo was oversubscribed with total of 35 attendees. You can read the details over hereSQLAuthority News – SQL Server Performance Optimizations Seminar – Grand Success – Colombo, Sri Lanka – Oct 4 – 5, 2010.

SQL Server Performance Tuning Seminar in Hyderabad was oversubscribed with total of 25 attendees. You can read the details over here SQL SERVER – A Successful Performance Tuning Seminar – Hyderabad – Nov 27-28, 2010.

The same Seminar was offered in Pune on December 4,-5, 2010. We had another successful seminar with lots of performance talk. This seminar was attended by 30 attendees.

The best part of the seminar was that along with the our agenda, we have talked about following very interesting concepts.

  • Deadlocks Detection and Removal
  • Dynamic SQL and Inline Code SQL Optimizations
  • Multiple OR conditions and performance tuning
  • Dynamic Search Condition Building and Improvement
  • Memory Cache and Improvement
  • Bottleneck Detections – Memory, CPU and IO
  • Beginning Performance Tuning on Production
  • Parametrization
  • Improving already Super Fast Queries
  • Convenience vs. Performance
  • Proper way to create Indexes
  • Hints and Disadvantages

I had great time doing the seminar and sharing my performance tricks with all. The highlight of this seminar was I have explained the attendees, how I begin doing performance tuning when I go for Performance Tuning Consultations.

Pinal Dave at SQL Performance Tuning Seminar

Pinal Dave at SQL Performance Tuning Seminar

SQL Server Performance Tuning Seminar

SQL Server Performance Tuning Seminar

Pinal Dave at SQL Performance Tuning Seminar

Pinal Dave at SQL Performance Tuning Seminar

Pinal Dave at SQL Performance Tuning Seminar

Pinal Dave at SQL Performance Tuning Seminar

SQL Server Performance Tuning Seminar

SQL Server Performance Tuning Seminar

SQL Server Performance Tuning Seminar

SQL Server Performance Tuning Seminar

This seminar series are 100% demo oriented and no usual PowerPoint talk. They are created from my experiences of various organizations for performance tuning.

I am not planning any more seminar this year as it was great but I am booked currently for next 60 days at various performance tuning engagements.

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