SQL SERVER – Learn SQL Server 2014 Online in a Day – My Latest Pluralsight Course

Click here watch SQL Server 2014 Administration New Features

SQL Server 2014 was released earlier this year and it has been extremely popular in Microsoft world. Here is the announcement for everyone, who have been asking me to build a tutorial around SQL Server 2014. I have authored latest Pluralsight courses on the subject of SQL Server 2014. This course is 4 hours and 17 minutes long, but the best part is that this course contains all the latest features of SQL Server 2014. I have build this course with the assumption that DBA is familiar with earlier versions of SQL Server and wants to explore and learn new features of SQL Server 2014.

The Challenge I Faced

The biggest challenge I faced was how to come up with the outline for the course. The reason is that there are so many different features introduced in SQL Server 2014 that is will be difficult to cover each of the features in a single course. I wanted to cover the topics which are the most relevant and useful to developers, but in addition I also wanted to cover the topics which may be useful to develop if they know that they exists in the product. I finally decided to depend on blog readers and few of the SQL Experts. I reached out to selected 20 people via email and gave them a list of the topics which I should be covering in this course. They all work in different organizations and have a good understanding about the need of the DBA and Developers.

Based on their feedback, I was able to come up with a very good outline which is currently very popular with Pluralsight library. Lots of people have asked me how was I able to come up with a course content outline so accurately. The credit for the same goes to the developers and DBA, who have voted in the topics and have helped me to build a very solid outline for the course.

Outline of the Course

Here is a quick outline for the course:

  • Introduction
  • Backup Enhancements
  • Security Enhancements
  • Columnstore Enhancements
  • Online Data Operations Enhancements
  • Enhancements with Microsoft Azure
  • SSD Buffer Pool Extensions
  • Resource Governor IO
  • Miscellaneous Features
    • Online Index Rebuilding
    • Live Plans for Long Running Queries
    • Transaction Durability
    • Cardinality Estimation
    • In Memory OLTP Optimization

Well, I had a great fun working on the topics which I have mentioned in the outline. I am very confident that once you start with the course, you will indeed understand how each of the topics builds and presented. I have made sure that each of the topic has a vivid and clear story to begin with. I first explain the story and right after that I explain the concept.

Who Should Attend This Course

Everyone who has basic knowledge of SQL Server and wants to update themselves with SQL Server 2014. They should attend this course. One thing I have made sure that this course is easy to understand and I have decided complex subject into multiple parts. This way the learning is progressive and anyone with a poor knowledge of the subject can have enough time to understand the presented concept.

Screenshot of the Course

Here are few of the screenshot of the courses.

sqlserver2014 SQL SERVER   Learn SQL Server 2014 Online in a Day   My Latest Pluralsight Course

How to Watch Video Course

This course is available at Pluralsight, and you will need a valid login to Pluralsight. If you do not have Pluralsight login, you can quickly sign up for the FREE Trial.

Click here watch SQL Server 2014 Administration New Features

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

SQL Authority News – Play by Play with Pinal Dave – A Birthday Gift

Today is my birthday.

Personal Note

When I was young, I was always looking forward to my birthday as on this day, I used to get gifts from everybody.

Now when I am getting old on each of my birthday, I have almost same feeling but the direction is different. Now on each of my birthday, I feel like giving gifts to everybody. I have received lots of support, love and respect from everybody; and now I must return it back.Well, on this birthday, I have very unique gifts for everybody – my latest course on SQL Server.

How I Tune Performance

I often get questions where I am asked how do I work on a normal day. I am often asked that how do I work when I have performance tuning project is assigned to me. Lots of people have expressed their desire that they want me to explain and demonstrate my own method of solving performance problem when I am facing real world problem. It is a pretty difficult task as in the real world, nothing goes as planned and usually planned demonstrations have no place there.

The real world, demands real solutions and in a timely fashion. If a consultant goes to industry and does not demonstrate his/her capabilities in very first few minutes, it does not matter how much fame he/she is, the door is shown to them eventually. It is true and in my early career, I have faced it quite commonly. I have learned the trick to be honest from the start and request absolutely transparent communication from the organization where I am to consult.

Play by Play

Play by Play is a very unique setup. It is not planned and it is a step by step course. It is like a reality show – a very real encounter to the problem and real problem solving approach. I had a great time doing this course. Geoffrey Grosenbach (VP of Pluralsight) sits down with me to see what a SQL Server Admin does in the real world. This Play-by-Play focuses on SQL Server performance tuning and I go over optimizing queries and fine-tuning the server.

The table of content of this course is very simple.


In the introduction I explained my basic strategies when I am approached by a customer for performance tuning.

p2p SQL Authority News   Play by Play with Pinal Dave   A Birthday Gift

Basic Information Gathering

In this module I explain how I do gather various information for performance tuning project. It is very crucial to demonstrate to customers for consultant his capability of solving problem. I attempt to resolve a small problem which gives a big positive impact on performance, consultant have to gather proper information from the start. I demonstrate in this module, how one can collect all the important performance tuning metrics.

Removing Performance Bottleneck

In this module, I build upon the previous module’s statistics collected. I analysis various performance tuning measures and immediately start implementing various tweaks on the performance, which will start improving the performance of my server. This is a very effective method and it gives immediate return of efforts.

Index Optimization

Indexes are considered as a silver bullet for performance tuning. However, it is not true always there are plenty of examples where indexes even performs worst after implemented. The key is to understand a few of the basic properties of the index and implement the right things at the right time. In this module, I describe in detail how to do index optimizations and what are right and wrong with Index. If you are a DBA or developer, and if your application is running slow – this is must attend module for you. I have some really interesting stories to tell as well.

Optimize Query with Rewrite

Every problem has more than one solution, in this module we will see another very famous, but hard to master skills for performance tuning – Query Rewrite. There are few do’s and don’ts for any query rewrites. I take a very simple example and demonstrate how query rewrite can improve the performance of the query at many folds. I also share some real world funny stories in this module.

This course is hosted at Pluralsight. You will need a valid login for Pluralsight to watch  Play by Play: Pinal Dave course. You can also sign up for FREE Trial of Pluralsight to watch this course.

As today is my birthday – I will give 10 people (randomly) who will express their desire to learn this course, a free code. Please leave your comment and I will send you free code to watch this course for free.

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

MySQL – MySQL Query Optimization and Performance Tuning – Online Video Courses

In year 2013, I had focused on MySQL along with SQL Server in database products. As I learn more and more MySQL, I quickly realized that there is no proper course or structured information for MySQL Performance Tuning.  I had decided to build a course around common MySQL Performance Problems. I have decided to build the performance tuning course in two parts. 1) MySQL Indexing for Performance and 2) MySQL Query Optimization and Performance Tuning. I had released MySQL Indexing for Performance course earlier this year and right before the end of the year 2013 the second course MySQL Query Optimization and Performance Tuning has released as well.

performance MySQL   MySQL Query Optimization and Performance Tuning   Online Video Courses

MySQL Query Optimization and Performance Tuning

Performance is one of the most essential aspects of any application. Everyone wants their server to perform optimally and at the best efficiency. In this course we will understand the basics of query optimization and look at practical tips and tricks for performance tuning.

Here is the broad outline of the course MySQL Query Optimization and Performance Tuning.


In this module we go over why MySQL Performance Tuning is different from other database products. We also explained with the help of few interesting analogies where exactly we should look into when there is a problem with MySQL Performance.

Optimizing Data Access

We cannot directly jump into performance tuning before taking care of a few of the best practices around MySQL. In this module we discuss few of the best practices which one must implement various techniques explained in this course.

In this module we will see the following topics in depth:

  • Understanding Data Needs
  • Demo: Setup
  • Demo: Best Practices for Data Access
  • Demo: Why SELECT Star (*) is a Bad Idea

Understanding MySQL Query Optimization

It is very important to understand the internals of the car if we are going to participate in a car race. Similarly it is important to understand what is under MySQL Query Engine if we are going to do query optimization with MySQL.

In this module we will see the following topics in depth:

  • Execution Path of a Query
  • Client Protocols
  • Query Cache
  • Parser
  • Preprocessor
  • Query Optimizer
  • Query Optimizer Responsibilities
  • Query Optimizer Limitations
  • Query Execution Engine and Storage
  • Returning Result to Client
  • Additional Notes on Query Optimizer
  • Maximizing Query Optimizer Performance
  • Understanding Query States
  • Demo: Show Full Processlist
  • Understanding Explain Command
  • Demo: Explain Command
  • Demo: Explain Extended Command

Performance Optimization by Practical Query Tuning

In the real world we get thousands of the different scenario where the performance of our query is not upto the mark. In this module we will discuss the most common query patterns which reduces the performance of any query. In this demo oriented session we will also understand how we can tune the performance of bad query with the help of query rewrite and by implementing performance best practices.

In this module we will see the following topics in depth:

  • Demo: Index Used for SELECT clause
  • Demo: One Complex Query vs Multiple Simple Queries
  • Demo: One Complex Query vs Multiple Simple Queries – Part 2
  • Demo: One Complex Query vs Multiple Simple Queries with Index
  • Demo: Table Order in Join Clause – INNER JOIN
  • Demo: Table Order in Join Clause – OUTER JOIN
  • Demo: Most Optimal Choice – Subquery vs Exists vs Joins
  • Demo: Most Optimal Choice – Subquery vs Exists vs Joins – Part 2
  • Demo: Tuning Aggregate Function
  • Demo: Optimizing Group By Clause
  • Demo: Optimizing Paging with LIMIT Clause
  • Demo: Impact on Performance of UNION and UNION ALL
  • Demo: Index and Not Equal to Operator

Best Practices

In this final module we sum up the course in the format of Best Practices. Though the length of this module is short, it contains some really good gem inside it.

You need a valid Pluralsight subscription to watch the courses. You can sign up for free trial as well.

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

Developer Training – 6 Online Courses to Learn SQL Server, MySQL and Technology

Video courses are the next big thing and I am so happy that I have so far authored 6 different video courses with Pluralsight. Here is the list of the courses. I have listed all of my video courses over here.

Note: If you click on the courses and it does not open, you need to login to Pluralsight with a valid username and password or sign up for a FREE trial.

Please leave a comment with your favorite course in the comment section. Random 10 winners will get surprise gift via email. Bonus: If you list your favorite module from the course site.

SQL Server Performance: Introduction to Query Tuning

SQL Server performance tuning is an in-depth topic, and an art to master. A key component of overall application performance tuning is query tuning. Writing queries in an efficient manner, and making sure they execute in the most optimal way possible, is always a challenge. The basics revolve around the details of how SQL Server carries out query execution, so the optimizations explored in this course follow along the same lines.
Click to View Course

SQL Server Performance: Indexing Basics

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 of the indexes. To master the art of performance tuning one has to understand the fundamentals of the indexes and the best practices associated with the same. This course is for every DBA and Developer who deals with performance tuning and wants to use indexes to improve the performance of the server.
Click to View Course

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. This course is for anyone working with SQL Server databases who wants to improve her knowledge and understanding of this complex platform.
Click to View Course

MySQL Fundamentals

MySQL is a popular choice of database for use in web applications, and is a central component of the widely used LAMP open source web application software stack. This course covers the fundamentals of MySQL, including how to install MySQL as well as written basic data retrieval and data modification queries.
Click to View Course

Building a Successful Blog

Expressing yourself is the most common behavior of humans. Blogging has made easy to express yourself. Just like a letter or book has a structure and formula, blogging also has structure and formula. In this introductory course on blogging we will go over a few of the basics of blogging and show the way to get started with blogging immediately. If you already have a blog, this course will be even more relevant as this will discuss many of the common questions and issue you face in your blogging routine.
Click to View Course

Introduction to ColdFusion

ColdFusion is rapid web application development platform. In this course you will learn the basics of how to use ColdFusion platform and rapidly develop web sites. The course begins with learning basics of ColdFusion Markup Language and moves to common development language practices. From there we move to frequent database operations and advanced concepts of Forms, Sessions and Cookies. The last module sums up all the concepts covered in the course with sample application.
Click to View Course

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

SQLAuthority News – Advantages of Distance Learning

Distance education is extremely popular – almost overnight, it seems.  Almost everyone has taken an online course, or knows someone who has, or is considering joining an online school.  There are many advantages and disadvantages to attending an online school – but the same can be said of attending a physical school!  Let’s take a look at the top reasons to use distance education.

1) Flexibility.  Physical universities are usually willing to make some concessions to student – like night classes, study hours, and online networks.  However, nothing is going to beat the flexibility of distance education.  You can attend classes and take notes anytime, anywhere, wearing anything you’d like!

2) Affordability.  We don’t need to get into hard numbers to understand how an expensive university can be.  Students are taking on more and more debt just to get an education.  Many of these fees pay for room, board, and facilities.   Distance education cuts out all these costs, and makes attending school much more affordable for the average student.

3) Try before you buy.  Did you know that the average college student changes his or her major 10 times before they graduate?  You can imagine that this kind of indecision plays a huge part in WHEN you graduate – not being able to make up your mind can cost you big bucks if you have to stay in school for extra years!  Distance education allows you to take different classes from a wide range of disciplines.  Do you want to study forensic science or English literature?  Now you don’t have to pay for classes you can’t afford just to find out.

4) Pace yourself.  Some students struggle in a traditional classroom setting – classes can be taught too fast, too slow, or there are too many distractions.  Distance education allows mature students to set the pace themselves.  They can rewatch lectures they didn’t catch the first time, or go through classes quickly if they are already familiar with the material – cutting out the chance of burning out or getting bored.

5) Lifelong learning.  Maybe you already have a degree, but would like to learn more about your field, or a related field, or maybe even about something completely unrelated – just because you are curious!  Distance education allows you to learn whatever you want ,whenever you want (and yes, wearing anything you’d like!).

6) Attend whatever college you want.  Because of the popularity of distance education, physical campuses are getting in on the game by offering online courses – often just uploaded versions of classes already taught at their campus.  Ever wanted to attend Harvard, but knew you couldn’t get in?  Take a class online!  Of course, you probably should not attempt to lie and say you have a Harvard degree, but Ivy League colleges are prestigious because they are the best in their field – take advantage of the best by taking an online course!

I am a big believer in continuing education, whether it is online courses, returning to school, or even take informal classes online.  Distance education can be a great way to accomplish these goals and become a lifelong learner. My friends at provides training through virtual classrooms for students who want to avoid travelling. Distance learning course allows IT aspirants to connect with trainers using the internet.  I encourage everyone to check it out!

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

SQLAuthority News – Koenig Solutions Invests into World’s Largest IT Training Center

Here is very interesting news from my friends at Koenig Solutions. They have opened a new IT training center which is indeed the state of the art facility. Here is the quick note of what I have observed about their new facility. This is excellent news for me as the leadership team of the Koenig and I go a long way back. It is fantastic to see your friends growing and becoming the largest IT Training Center.

koenigtrainingcentre 1 SQLAuthority News   Koenig Solutions Invests into Worlds Largest IT Training Center

Koenig Solutions houses the world’s largest IT training center in New Delhi. It comprises over 50 classrooms for imparting training as well as more than 26 hi-tech testing stations. There are also four other campuses throughout India, as well as a center in Dubai, to best suit all the students, no matter where they are.  Every campus boasts the same high standards, facilities, and amenities.  Their original facility in New Delhi remains the most popular classroom for “destination learning.”

koenigtrainingcentre 2 SQLAuthority News   Koenig Solutions Invests into Worlds Largest IT Training Center

This modern facility also boasts of an in-house coffee shop and travel desk to cater to all possible needs of our students, in addition to their educational requirements.  The facility doubles as a classroom and as a world-class hotel, with lodging for 25 students.  Every room features hot and cold running water, satellite television, and LCD screens.  There are also free laundry services, a free gymnasium, and free yoga classes.  At Koenig you can exercise your mind and your body!

koenigtrainingcentre 3 SQLAuthority News   Koenig Solutions Invests into Worlds Largest IT Training Center

Koenig’s Delhi Centre also garners huge credibility based on the fact that it is the largest Prometric and Vue testing center in India.  Having both certification tests available in one spot is extremely helpful because the two providers offer certifications in different technology companies.  For example, Microsoft uses only Prometric, while Cisco uses only VUE.  If you prefer one certification or another, or would like to try both, the Dehli Center is equipped to meet all your needs.

koenigtrainingcentre 5 SQLAuthority News   Koenig Solutions Invests into Worlds Largest IT Training Center

All these benefits make our Delhi Center irresistibly attractive to those who are looking for great quality training coupled with excellent infrastructure.  Additionally, fun day trips are within easy reach via this campus.  Visit Agra, location of the Taj Mahal, one your time off.  Combine business with pleasure, and learn while exploring amazing historical sites.

Other facilities:

Library: Access to a vast and rich study material for students who constantly like to expand their knowledge base.  Traditional text books are obviously available in the library, as well as online resources, so that you can better research any topic that catches your attention.

koenigtrainingcentre 4 SQLAuthority News   Koenig Solutions Invests into Worlds Largest IT Training Center

Prayer Room: Meditate and find peace in the prayer room whenever you feel like reconnecting with your Higher Self.  It has been repeatedly proven that after a day filled with study, exercise, and physical or mental exertion, the best way to both recover and recall the material is to spend some quiet time with yourself – not necessarily thinking about the subject, but letting your subconscious do the work for you.  Give the Prayer Room a shot.  You are guaranteed to emerge more relaxed.

koenigtrainingcentre 6 SQLAuthority News   Koenig Solutions Invests into Worlds Largest IT Training Center

Reflexology: Give those aching muscles some rest with a reflexology session and get back to study lessons feeling rejuvenated.  Most exercise programs recommend a full-body workout – it is pointless to exercise only the arms, or only the legs.  It is the same with learning.  Exercising the muscle that is your brain will leave the rest of your body “left out.”  We make sure to provide opportunities for our students to make the most of their training.

If you are traveling to Delhi – I suggest to stop by Koenig Solutions‘s IT center and check out their ultra modern facilities.

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

SQLAuthority News – Excellent Learning Experience at SQLskills Immersion Events

As many of you know, I attended the Immersion Event at SQLskills learning center in early Feb. I consider myself a SQL professional, but there is no age limit for being a student. One can learn forever, there is always something new to pick up – particularly when you go to a SQLskills event. They are going to teach you something fantastic, something you didn’t know before. It is quite possible to know a lot about a concept, but when we learn about the right ways and wrong ways to do things, and see all the mistakes we made without knowing it, we will learn how to do our jobs better.

The quality of learning at a SQLskills event is amazing. The instructors for Immersion 1 were Paul and Kimberly, and for Immersion 2 there was Paul, Kimberly, Jonathan and Joseph. They take turns teaching, and at every interval there is a new instructor with a new style and new material. It is both stimulating and relaxing, because you are constantly learning, constantly challenged, and there is a new instructor always coming up next. You never get bored. It was also highly interactive with the audience as well.

At both Immersions, questions are encouraged, and the instructors are not shy about answering any and all questions. They encourage feedback and engage with the students. There is always plenty of time allowed for questions and answers, so the sessions are not rushed. The instructor comes at breakfast and stays the whole day, even through lunch. They make sure everyone has had a chance to ask questions and get answers, and they don’t leave the room until the job is done.

The attention to detail is amazing. Each session is right on time, nothing is delayed, nothing is late, there are frequent breaks and meals are right on time. You are totally immersed in learning. They even pay special attention to dietary restrictions. For example, I am a vegetarian and they always made sure I was provided with the correct meals and that those around me with other dietary requirements were also served probably. I was especially a big fan of the desserts, which were served every day at 4 o’clock. They were served closer to dinner time because lunches were so filling no one had room for desert! There were also plenty of drinks, munchies, and coffee available at all times, so that if your stomach growling was getting in the way of your learning, you didn’t have to worry, there was a snack available.

SQLskills award SQLAuthority News   Excellent Learning Experience at SQLskills Immersion Events

Immersion Event Completion Certification

The best part was that the instructors never tried to hide their information. Sometimes at training events the trainer is reluctant to share slides or give handouts. However, at SQLskills, every slide, demo and diagram were delivered to the students. Before you leave you are sent an email with all the details. I had a great time, those 15 days were tremendous. I will never forget them. If you love SQL you need to attend an event at least once – you can’t afford NOT to.

Additionally, if you are a Pluralsight subscriber, you can also watch the SQL courses authored by SQLskills trainers.

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

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.

 SQLAuthority News   Technical Review of Learning at Koenig Solutions

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)