SQL SERVER – 3 Online SQL Courses at Pluralsight and Free Learning Resources

Usain Bolt is an inspiration for all. He broke his own record multiple times because he wanted to do better! Read more about him on wikipedia. He is great and indeed fastest man on the planet.

Usain Bolt - World’s Fastest Man

Usain Bolt – World’s Fastest Man

“Can you teach me SQL Server Performance Tuning?”

This is one of the most popular questions which I receive all the time. The answer is YES.

I would love to do performance tuning training for anyone, anywhere.  It is my favorite thing to do, and it is my favorite thing to train others in.  If possible, I would love to do training 24 hours a day, 7 days a week, 365 days a year.  To me, it doesn’t feel like a job.

Of course, as much as I would love to do performance tuning 24/7/365, obviously I am just one human being and can only be in one place at one time.  It is also very difficult to train more than one person at a time, and it is difficult to train two or more people at a time, especially when the two people are at different levels.  I am also limited by geography.  I live in India, and adjust to my own time zone.  Trying to teach a live course from India to someone whose time zone is 12 or more hours off of mine is very difficult.  If I am trying to teach at 2 am, I am sure I am not at my best!

There was only one solution to scale – Online Trainings. I have built 3 different courses on SQL Server Performance Tuning with Pluralsight. Now I have no problem – I am 100% scalable and available 24/7 and 365. You can make me say the same things again and again till you find it right. I am in your mobile, PC as well as on XBOX. This is why I am such a big fan of online courses.  I have recorded many performance tuning classes and you can easily access them online, at your own time.  And don’t think that just because these aren’t live classes you won’t be able to get any feedback from me.  I encourage all my viewers to go ahead and ask me questions by e-mail, Twitter, Facebook, or whatever way you can get a hold of me.

Here are details of three of my courses with Pluralsight. I suggest you go over the description of the course. As an author of the course, I have few FREE codes for watching the free courses. Please leave a comment with your valid email address, I will send a few of them to random winners.

SQL Server Performance: Introduction to Query Tuning 

SQL Server performance tuning is an art to master – for developers and DBAs alike. This course takes a systematic approach to planning, analyzing, debugging and troubleshooting common query-related performance problems. This includes an introduction to understanding execution plans inside SQL Server.

In this almost four hour course we cover following important concepts.

  • Introduction 10:22
  • Execution Plan Basics 45:59
  • Essential Indexing Techniques 20:19
  • Query Design for Performance 50:16
  • Performance Tuning Tools 01:15:14
  • Tips and Tricks 25:53
  • Checklist: Performance Tuning 07:13

The duration of each module is mentioned besides the name of the module.

SQL Server Performance: Indexing Basics

This course teaches you how to master the art of performance tuning SQL Server by better understanding indexes.

In this almost two hour course we cover following important concepts.

  • Introduction 02:03
  • Fundamentals of Indexing 22:21
  • Practical Indexing Implementation Techniques 37:25
  • Index Maintenance 16:33
  • Introduction to ColumnstoreIndex 08:06
  • Indexing Practical Performance Tips and Tricks 24:56
  • Checklist : Index and Performance 07:29

The duration of each module is mentioned besides the name of the module.

SQL Server Questions and Answers

This course is designed to help you better understand 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.

In this almost 2 hours and 15 minutes course we cover following important concepts.

  • Introduction 00:54
  • Retrieving IDENTITY value using @@IDENTITY 08:38
  • Concepts Related to Identity Values 04:15
  • Difference between WHERE and HAVING 05:52
  • Order in WHERE clause 07:29
  • Concepts Around Temporary Tables and Table Variables 09:03
  • Are stored procedures pre-compiled? 05:09
  • UNIQUE INDEX and NULLs problem 06:40
  • Locks and Duration of Transactions 15:11
  • Nested Transaction and Rollback 09:16
  • Understanding Date/Time Datatypes 07:40
  • Differences between VARCHAR and NVARCHAR datatypes 06:38
  • Precedence of DENY and GRANT security permissions 05:29
  • Identify Blocking Process 06:37
  • NULLS usage with Dynamic SQL 08:03
  • Appendix Tips and Tricks with Tools 20:44

The duration of each module is mentioned besides the name of the module.

SQL in Sixty Seconds

You will have to login and to get subscribed to the courses to view them. Here are my free video learning resources SQL in Sixty Seconds. These are 60 second video which I have built on various subjects related to SQL Server. Do let me know what you think about them?

Here are three of my latest videos:

You can watch and learn at your own pace.  Then you can easily ask me any questions you have.  E-mail is easiest, but for really tough questions I’m willing to talk on Skype, Gtalk, or even Facebook chat.  Please do watch and then talk with me, I am always available on the internet!

Here is the video of the world’s fastest man.Usain St. Leo Bolt inspires us that we all do better than best. We can go the next level of our own record. We all can improve if we have a will and dedication.  Watch the video from 5:00 mark.


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

SQL SERVER – Beginning of SQL Server Architecture – Terminology – Guest Post

SQL Server Architecture is a very deep subject. Covering it in a single post is an almost impossible task. However, this subject is very popular topic among beginners and advanced users.  I have requested my friend Anil Kumar who is expert in SQL Domain to help me write  a simple post about Beginning SQL Server Architecture. As stated earlier this subject is very deep subject and in this first article series he has covered basic terminologies. In future article he will explore the subject further down. Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

In this Article we will discuss about MS SQL Server architecture.

The major components of SQL Server are:

  1. Relational Engine
  2. Storage Engine
  3. SQL OS

Now we will discuss and understand each one of them.

1) Relational Engine: Also called as the query processor, Relational Engine includes the components of SQL Server that determine what your query exactly needs to do and the best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.

Different Tasks of Relational Engine:

  1. Query Processing
  2. Memory Management
  3. Thread and Task Management
  4. Buffer Management
  5. Distributed Query Processing

2) Storage Engine: Storage Engine is responsible for storage and retrieval of the data on to the storage system (Disk, SAN etc.). to understand more, let’s focus on the concepts.

When we talk about any database in SQL server, there are 2 types of files that are created at the disk level – Data file and Log file. Data file physically stores the data in data pages. Log files that are also known as write ahead logs, are used for storing transactions performed on the database.

Let’s understand data file and log file in more details:

Data FileData File stores data in the form of Data Page (8KB) and these data pages are logically organized in extents.

Extents: Extents are logical units in the database. They are a combination of 8 data pages i.e. 64 KB forms an extent. Extents can be of two types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc (multiple objects). On the other hand, Uniform extents are dedicated to only one type (object).

Pages: As we should know what type of data pages can be stored in SQL Server, below mentioned are some of them:

  • Data Page: It holds the data entered by the user but not the data which is of type text, ntext, nvarchar(max), varchar(max), varbinary(max), image and xml data.
  • Index: It stores the index entries.
  • Text/Image: It stores LOB ( Large Object data) like text, ntext, varchar(max), nvarchar(max),  varbinary(max), image and xml data.
  • GAM & SGAM (Global Allocation Map & Shared Global Allocation Map): They are used for saving information related to the allocation of extents.
  • PFS (Page Free Space): Information related to page allocation and unused space available on pages.
  • IAM (Index Allocation Map): Information pertaining to extents that are used by a table or index per allocation unit.
  • BCM (Bulk Changed Map): Keeps information about the extents changed in a Bulk Operation.
  • DCM (Differential Change Map): This is the information of extents that have modified since the last BACKUP DATABASE statement as per allocation unit.

Log File: It also known as write ahead log. It stores modification to the database (DML and DDL).

  • Sufficient information is logged to be able to:
    • Roll back transactions if requested
    • Recover the database in case of failure
    • Write Ahead Logging is used to create log entries
      • Transaction logs are written in chronological order in a circular way
      • Truncation policy for logs is based on the recovery model

SQL OS: This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by SQL OS. It is a highly configurable operating system with powerful API (application programming interface), enabling automatic locality and advanced parallelism. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and deadlock detection using the blocking and locking structure. Other services include exception handling, hosting for external components like Common Language Runtime, CLR etc.

I guess this brief article gives you an idea about the various terminologies used related to SQL Server Architecture. In future articles we will explore them further.

Guest Author 

The author of the article is Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.

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

SQLAuthority News – Technical Review of Learning at Koenig Solutions

Yesterday I finished my 3 days fast track in person learning of course End to End SQL Server Business Intelligence at Koenig Solutions. You can read my previous article over here regarding why am I learning SQL Server. Yesterday I blogged about my experience of arriving to Training Center and my induction with the center.

The Training Days

I had enrolled for three days training so my routine each of the three days was very much same. However, the content every day was different as I was learning something new every day. Let me describe a few of the interesting details of my daily routine.

A Single Student Batch

The best part of my training was that in my training batch, I am single student. Koenig is known to smaller batches and often they have single student batches as well. I was very much delighted to know that I will have dedicated access and attention from my trainer in my batch as I will be single student in my batch. In most of the labs I have observed there are no more than 4 students at any time.

Prakash and Pinal

Prakash and Pinal

7:30 AM Breakfast Talk

We all students gather at 7:30 in breakfast area. The best time of the day. I was the only Indian student in the group. The other students were from USA, Canada, Nigeria, Bhutan, Tanzania, and a few others from other countries. I immediately become the source of information and reference manual. Though the distance between Delhi and Bangalore is 2000+ KM I was considered as a local guy.

8:30 AMHeading to Training Center

Every day without fail at 8:30 the van started from our accommodation to the training center. As mentioned in an earlier blog post the distance is about 5 minutes and we were able to reach at the location before 8:45. This gave us some time settle in before our class starts at 9:00 AM.

9:00 AM Order Lunch Food

Well it may sound funny that we just had breakfast 30 minutes but the first thing everybody has to do is to order lunch as soon as the class starts. There is an online training portal to order food for the day. Everybody has to place their order early during the day so the food arrives on time during lunch time. Everybody can order whatever they want to order using an online ordering system. The options are plenty and everybody can order what they like.

9:05 AM Learning Starts

After deciding the lunch we started the learning. I was very fortunate to have a very experienced trainer – Prakash Chheatry. Though I have never met him before I have heard a lot about Prakash. He is known as the top most SQL Server Trainer in India. His student list contains some of the very well known SQL Server Experts of the world and few of SQL Server “best seller” book authors.

Learning continues till 1:00 PM with one tea-coffee break in between.

1:00 PM Lunch

The lunch time is again the fun time. We all students get together in the afternoon and tell the stories of the world. Indeed the best part of the day beside learning new stuff.

4:55 PM Ready to Return

We stop at 4:55 as at precisely 5:00 PM the van stops by the institute which takes us back to our accommodation. Trust me seriously long long day always but the amount of the learning is the win of the day.

7:30 PM Dinner Time

After coming back to the accommodation I study till 7:30 and then rush for dinner. Dinner is world cuisine and deserts are really delicious. After dinner every day I have written a blog and retired early as the next day is always going to be busier than the present day.

What did I learn

As I mentioned earlier I know SQL Server fairly well. I had expressed the same in my conversation as well. This is the reason I was assigned a fairly senior trainer and we learned everything quite quickly. As I know quite a few things we went pretty fast in many topics. There were a few things, I wanted to learn in detail as well practice on the labs. We slowed down where we wanted and rush through the concepts where I was very comfortable.

Here is the list of the things which we covered in action pack three days.

  • Introduction to Business Intelligence (Intro)
  • SQL Server Analysis Service (Theory and Lab)
  • SQL Server Integration Service  (Theory and Lab)
  • SQL Server Reporting Service  (Theory and Lab)
  • SQL Server PowerPivot (Lab)
  • UDM (Theory)
  • SharePoint Concepts (Theory)
  • Power View (Demo)
  • Business Intelligence and Security (Discussion)

Well, I was delighted that I was able to refresh lots of concepts during these three days. Thanks to my trainer and my friend who helped me to have a good learning experience. I believe all the learning  will help me in my growth and future career.

With this I end my this experience. I am planning to have another online learning experience later this month. I will blog about my experience as I begin it.

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

SQLAuthority News – Learning Trip – Traveling to Learn SQL Server

I am currently traveling to Delhi to learn SQL Server in person from my friend. You can read more details about why am I learning SQL Server.  I have signed up for the course End to End SQL Server Business Intelligence at Koenig Solutions. Yesterday I blogged about my registration experience and today I am going to write about my  experience once I arrived at Delhi.

From Ahmedabad to Delhi

I stay with my wife and daughter in Bangalore (IT Hub of India), my hometown is Ahmedabad. My parents stay in city nearby Ahmedabad. I decided to spend few days with my folks before I sign up for 3 days of solid learning. I had selected an early morning flight to Delhi. I landed at 8:30 AM in Delhi.

As soon as I checked email in my mobile I was really glad that I had received details of my pick up vehicle from Koenig. I walked out of the airport and I noticed that a driver was waiting with a placard with my name and photo associated with it. He was in Koenig uniform so there was no chance to make mistakes. In minutes of landing in Delhi I was in my transport heading to the Koenig Training Center. After the quick introduction driver handed me a bag (to be precise Eco friendly bag).

The bag contained following items:

  • My registration form
  • All necessary documents in print which I had received earlier
  • A Printed Book of the course next day
  • INR 1000 (What?)

I was glad to receive the bag but I was very confused with the Rs 1000. I decided to figure this out once I reach to the training center.

Arriving at Koenig Inn Deluxe

Koenig registration fees include all the stay and meals. I had opted for Koenig Inn Deluxe as my stay as it was recommended by my friend as well it was the right economical choice for me. When I reached to my accommodation, they were well aware of my arrival and was immediately led to my spacious room. The room is well equipped with all the amenities (hot water, air condition, coffee table, munching snacks,  and free internet) and the staff is very friendly. I immediately got ready as I had to go to Koenig Training Center to meet Center Head for a quick introduction.

Koenig Inn Delux

Koenig Inn Delux

Koenig Training Center

The training center is within five minutes of distance from the accommodation. I was lead to center head right away and had a very meaningful conversation with Ms Hema regarding my learning goals. She gave me a quick tour of the training center. I was amazed with the numbers of lab rooms they have in the center. The labs are spacious and give the most needed hand’s on experience to the users. I was led to the lab where I was suppose to learn my class the very next day as well I was provided my trainer’s profile.

Mystery of Rs 1000

Well, after all this I have still not forgotten why I was provided Rs 1000 when arrived at the airport. When I asked about that I was told that because many students comes from foreign places and they may not have Indian Currency when they land at airport. This was for their immediate consumption till they arrive at the training center. Later on they can get their currency converted to local currency at Koenig Travel Desk. My curiosity was satisfied but I had not expected this answer. I am amazed at the attention to the details.

Koenig Travel Desk

When I heard about Koenig Travel Desk, I remembered that I have few friends in Delhi and Gurgaon. I had completed all of the formalities so I had reset of the day on my hand. I requested the travel desk if they can arrange a day cab for me so I can visit my friends in Guragon. Within 10 minutes I was on my way to Gurgaon.

Telerik India Office Visit

What did I do in Guragaon? I met my friends Abhishek Kant, Dhananjay Kumar and Amit Chowdhary. I visited Telerik India office and we had an excellent conversation on various aspects of technology and community. The Telerik India office is very spacious and Abhishek Kant (Telerik India Country Manager) gave us a quick tour of the office. We had an excellent lunch and dinner. One thing is for sure – the day was well spent.

Pinal Dave, Dhananjay Kumar and Abhishek Kant

Pinal Dave, Dhananjay Kumar and Abhishek Kant

Later evening I returned to my accommodation and decided to read up a few of the topics which I was going to learn next day. In tomorrow’s blog post I will discuss about my learning experience.

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

SQLAuthority News – Getting Ready to Learn SQL Server

If you have read my earlier blog post you must be aware of how I am always eager to learn new things. I have signed up for three days learning course at Koenig Solutions for End to End SQL Server Business Intelligence. You may wonder why I sign up for the course on SQL Server when it seems that I know a lot about it.

Well, the belief is incorrect that I know a lot. I think there are plenty of things which I have been dreaming to learn.

Why am I learning SQL Server?

First of all – I do not know everything and second it is always a good idea to learn more. No matter how old we get or how much we think we know – there are always details which we can learn and refresh few concepts.

Learning is never ending process philosophically but it is true as well in reality. SQL Server 2012 is already released earlier this year and there are plenty of enhancements released. Recently I was going over the list of the all the new feature and enhancement and I realized that there few things about SQL Server 2008 R2 I never got a chance to have a hand’s on experience and we have entered into the era of SQL Server 2012. I feel a bit bad about it and I decided to make it a priority for me to learn all the missing experiences.

Quick Action – Registration

The very same day I called up my friend who owns Koenig Solution and expressed my concern and requested his help. During my early career when I was a SQL Server Trainer, we had some good synergy between us and now they are very successful offshore training company by having a physical location in Delhi,  Goa, Dahradun, Shimla, Goa and Bangalore. I quickly visited their Bangalore Center and paid my fees for learning SQL Server Business Intelligence course. Very next second I got call from my friend suggesting that I learn this course from Delhi instead of Bangalore. As per him I should travel to Delhi and learn the course how other students are learning “Away from Home”. This made sense as I stay in Bangalore and if I return home after a long day of learning, I will be not able to practice for the next day as there will be “sweet distraction” of the family. Well I opted for Delhi.

What Registration Fees Included

I learned from registration processes that the following were included in the fees.

  • 3 meals every day (hearty breakfast, lunch from premium restaurants and home cooked like dinner)
  • Airport pick up and drop
  • Hotel Stay
  • Internet at hotel and at learning institute
  • Unlimited coffee and snacks at learning institute
  • Printed Learning Material
  • Certification Fees (if applicable)
  • Learning material
  • … And of course classroom training

I thought registration process was over when I paid fees. Well, I was in for a very nice surprise.

Registration Experience – Bliss!

Within few hours I received emails from Center Manager of Delhi with all the necessary details I need to know about my learning experience. The email contained following information in detail and it blew me away.

  • Details of the pick up from airport – driver information
  • Details of Delhi and important information
  • List of all the important people and emergency contact details
  • Internet connection details
  • Detail of the trainer and all the training details
  • and lots of other relevant information

Well so far everything looks great. Tomorrow I will reach to Delhi and I will share how things go on. Any suggestion for things to do in Delhi?

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

SQL SERVER – Weekend Project – Visiting Friend’s Company – Koenig Solutions

I have decided to do some interesting experiments every weekend and share it next week as a weekend project on the blog. Many times our business lives and personal lives are very separate, however this post will talk about one instance where my two lives connect.

This weekend I visited my friend’s company. My friend owns Koenig, so of course I am very interested so see that they are doing well.  I have been very impressed this year, as they have expanded into multiple cities and are offering more and more classes about Business Intelligence, Project Management, networking, and much more. Koenig Solutions originally were a company that focused on training IT professionals – from topics like databases and even English language courses.  As the company grew more popular, Koenig began their blog to keep fans updated, and gradually have added more and more courses.

I am very happy for my friend’s success, but as a technology enthusiast I am also pleased with Koenig Solutions’ success.  Whenever anyone in our field improves, the field as a whole does better.  Koenig offers high quality classes on a variety of topics at a variety of levels, so anyone can benefit from browsing this blog. I am a big fan of technology (obviously), and I feel blessed to have gotten in on the “ground floor,” even though there are some people out there who think technology has advanced as far as possible – I believe they will be proven wrong.  And that is why I think companies like Koenig Solutions are so important – they are providing training and support in a quickly growing field, and providing job skills in this tough economy.

I believe this particular post really highlights how I, and Koenig, feel about the IT industry.  It is quickly expanding, and job opportunities are sure to abound – but how can the average person get started in this exciting field?  This post emphasizes that knowledge is power – know what interests you in the IT field, get an education, and continue your training even after you’ve gotten your foot in the door.

Koenig Solutions provides what I feel is one of the most important services in the modern world – in person training.  They obviously offer many online courses, but you can also set up physical, face-to-face training through their website.  As I mentioned before, they offer a wide variety of classes that cater to nearly every IT skill you can think of.  If you have more specific needs, they also offer one of the best English language training courses.  English is turning into the language of technology, so these courses can ensure that you are keeping up the pace. Koenig Solutions and I agree about how important training can be, and even better – they provide some of the best training around.  We share ideals and I am very happy see the success of my friend.

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

SQL SERVER – Four Tutorial for SQL Server 2012 New Features

One of the very common question I receive on my facebook is that if there is any tutorial for SQL Server 2012 new enhanced features and solutions. I see this demand a bit increasing as the SQL Server 2012 is more and more being adopted. Here is the list of four tutorial which is specifically created for SQL Server 2012 by Microsoft.

Multidimensional Modeling (Adventure Works Tutorial)
This tutorial teaches you how to develop and deploy an Analysis Services project that enables the employees of Adventure Works Cycles to analyze various aspects of their business.

Tabular Modeling (Adventure Works Tutorial)
This tutorial teaches you how to create a SQL Server 2012 Analysis Services tabular model that enable sales and marketing teams to easily analyze internet sales data in the AdventureWorksDW2012 data warehouse. You will build the tabular model in SQL Server Data Tools.

Tutorials and Demos for Power View
Create Power View reports and explore Power View features. View demos, videos, and tutorials that help you get started quickly with Power View and successfully build reports with interactive filters and visualizations such as bubble charts, tiles, and cards.

Tutorial: Using the hierarchyid Data Type
This tutorial is intended for users who are experienced with Transact-SQL, but are new to the hierarchyid data type. In this tutorial, you convert an existing table to a hierarchical structure, and you also create a new table to store and manage hierarchical data efficiently.

Note: The description of the course is taken from the original course description. You will need to install SQL Server 2012 AdventureWorks for all this tutorial.

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