SQL SERVER – T-SQL Constructs – *= and += – SQL in Sixty Seconds #009 – Video

60 SQL SERVER   T SQL Constructs   *= and +=   SQL in Sixty Seconds #009   Video

There were plenty of request for Vinod Kumar to come back with SQL in Sixty Seconds with T-SQL constructs after his very first well received construct video T-SQL Constructs – Declaration and Initialization – SQL in Sixty Seconds #003 – Video. Vinod finally comes up with this new episode where he demonstrates how dot net developer can write familiar syntax using T-SQL constructs. T-SQL has many enhancements which are less explored. In this quick video we learn how T-SQL Constructions works. We will explore Declaration and Initialization of T-SQL Constructions. We can indeed improve our efficiency using this kind of simple tricks. I strongly suggest that all of us should keep this kind of tricks in our toolbox.

More on Errors:
Declare and Assign Variable in Single Statement
Declare Multiple Variables in One Statement

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

If we like your idea we promise to share with you educational material.

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

SQL SERVER – Partition Parallelism Support in expressor 3.6

I am very excited to learn that there is a new version of expressor’s data integration platform coming out in March of this year.  It will be version 3.6, and I look forward to using it and telling everyone about it.  Let me describe a little bit more about what will be so great in expressor 3.6:

  • Greatly enhanced user interface
  • Parallel Processing
  • Bulk Artifact Upgrading

The User Interface

ex1 SQL SERVER   Partition Parallelism Support in expressor 3.6First let me cover the most obvious enhancements. The expressor Studio user interface (UI) has had some significant work done. Kudos to the expressor Engineering team; the entire UI is a visual masterpiece that is very responsive and intuitive. The improvements are more than just eye candy; they provide significant productivity gains when developing expressor Dataflows.

Operator shape icons now include a description that identifies the function of each operator, instead of having to guess at the function by the icon.

ex2 SQL SERVER   Partition Parallelism Support in expressor 3.6Operator shapes and highlighting depict the current function and status: Disabled, enabled, complete, incomplete, and error. Each status displays an appropriate message in the message panel with correction suggestions.

ex3 SQL SERVER   Partition Parallelism Support in expressor 3.6Floating or docking property panels provide descriptive tool tips for each property as well as auto resize when adjusting the canvas, without having to search Help or the need to scroll around to get access to the property.

ex4 SQL SERVER   Partition Parallelism Support in expressor 3.6

Progress and status indicators let you know when an operation is working.

ex5 SQL SERVER   Partition Parallelism Support in expressor 3.6“No limit” canvas with snap-to-grid allows automatic sizing and accurate positioning when you have numerous operators in the Dataflow. The inline tool bar offers quick access to pan, zoom, fit and overview functions.

Selecting multiple artifacts with a right click context allows you to easily manage your workspace more efficiently.

Partitioning and Parallel Processing

ex7 SQL SERVER   Partition Parallelism Support in expressor 3.6Partitioning allows each operator to process multiple subsets of records in parallel as opposed to processing all records that flow through that operator in a single sequential set. This capability allows the user to configure the expressor Dataflow to run in a way that most efficiently utilizes the resources of the hardware where the Dataflow is running. Partitions can exist in most individual operators. Using partitions increases the speed of an expressor data integration application, therefore improving performance and load times. With the expressor 3.6 Enterprise Edition, expressor simplifies enabling parallel processing by adding intuitive partition settings that are easy to configure.

Bulk Artifact Upgrading

ex8 SQL SERVER   Partition Parallelism Support in expressor 3.6Bulk Artifact Upgrading sounds a bit intimidating, but it actually is not and it is a welcome addition to expressor Studio. In past releases, users were prompted to confirm that they wanted to upgrade their individual artifacts only when opened. This was a cumbersome and repetitive process. Now with bulk artifact upgrading, a user can easily select what artifact or group of artifacts to upgrade all at once.

As you can see, there are many new features and upgrade options that will prove to make expressor Studio quicker and more efficient.  I hope I’m not the only one who is excited about all these new upgrades, and that I you try expressor and share your experience with me.

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

SQL SERVER – Transcript of Learning SQL Server Performance: Indexing Basics – Interview of Vinod Kumar by Pinal Dave

Recently I just wrote a blog post on about Learning SQL Server Performance: Indexing Basics and I received lots of request that if we can share some insight into the course.

Here is 200 seconds interview of Vinod Kumar I took right after completing the course. We have few free codes to watch the course, please your comment at http://facebook.com/SQLAuth and we will few of first ones, we will send the code.

There are many people who said they would like to read the transcript of the video. Here I have generated the same.

Pinal: Vinod, we recently released this course, SQL Server Indexing. It is about performance tuning. So tell me – how do indexes help performance?

Vinod: I think what happens in the industry when it comes to performance is that developers and DBAs look at indexes first.  So that’s the first step for any performance tuning exercise, indexing is one of the most critical aspects and it is important to learn it the right way.

Pinal: Correct. So what you mean to say is that if you know indexing you can pretty much tune any server and query.

Vinod: So I might contradict my false statement now. Indexing is usually a stepping stone but it does not lead you to the end. But it’s good to start with indexing and there are lots of nuances to indexing that you need to understand, like how SQL uses indexing and how performance can improve because of the strategies that you have made.

Pinal: But now I’m confused. First you said indexes are good, and then you said that indexes can degrade your performance.  So what is this course about?  I mean how does this course really make an impact?

Vinod: Ok -so from the course perspective, what we are trying to do is give you a capsule which gives you a good start. Every journey needs a beginning, you need that first step.  This course is that first step in understanding.

This is the most basic, fundamental course that we have tried to attack. This is the fundamentals of indexing, some of the key things that you must know about indexing.   Some of the basics of indexing are lesser known and so I think this course is geared towards each and every one of you out there who wants to understand little bit more about indexing.

Pinal: So what I understand is that if I enrolled in this course I will have a minimum understanding about indexing when dealing with performance tuning.  Right?

Vinod: Exactly. In this course is we have tried to give you a nice summary. We are talking about clustered indexing, non clustered indexing, too many indexes, too few indexes, over indexing, under indexing, duplicate indexing, columns tune indexing, with SQL Server 2012. There’s lot’s to learn.

Pinal: You can see the URL [http://bit.ly/sql-index] of the course on the screen. Go ahead, attend, and let us know what you think about it. Thank you.

Vinod: Thank you.

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

SQL SERVER – Using MAXDOP 1 for Single Processor Query – SQL in Sixty Seconds #008 – Video

60 SQL SERVER   Using MAXDOP 1 for Single Processor Query   SQL in Sixty Seconds #008   VideoToday’s SQL in Sixty Seconds video is inspired from my presentation at TechEd India 2012 on Speed up! – Parallel Processes and Unparalleled Performance.

There are always special cases when it is about SQL Server. There are always few queries which gives optimal performance when they are executed on single processor and there are always queries which gives optimal performance when they are executed on multiple processors. I will be presenting the how to identify such queries as well what are the best practices related to the same.

In this quick video I am going to demonstrate if the query is giving optimal performance when running on single CPU how one can restrict queries to single CPU by using hint OPTION (MAXDOP 1).

More on Errors:
Difference Temp Table and Table Variable – Effect of Transaction
Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

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

SQL SERVER – #TechEdIn – Presenting Tomorrow on Speed Up! – Parallel Processes and Unparalleled Performance at TechEd India 2012

Performance tuning is always a very hot topic when it is about SQL Server. SQL Server Performance Tuning is a very challenging subject that requires expertise in Database Administration and Database Development. I always have enjoyed talking about SQL Server Performance tuning subject. However, in India, it’s actually the very first time someone is presenting on this interesting subject, so this time I had the biggest challenge to present this session.

Frequently enough, we get these two kind of questions:

  1. How to turn off parallelism as it is reducing performance?
  2. How to turn on parallelism as I want more performance?

The reality is that not everyone knows what exactly is needed by their system. In this session, I have attempted to answer this very question. I’ve decided to provide a balanced view but stay away from theory, which leads us to say “It depends”. The session will have a clear message about this towards its end.

Deck Details

  • Slides: 45+
  • Demos: 7+ Bonus
  • Quiz: 5
  • Images: 10+
  • Session delivery time: 52 Mins + 8 Mins of Q & A

I have presented this session a couple of times to my friends and so far have received good feedback. Oftentimes, when people hear that I am going to present 45 slides, they all say it is too much to cover. However, when I am done with the session the usual reaction is that I truly gave justice to those slides.

Action Item

Here are a few of the action items for all of those who are going to attend this session:

If you want to attend the session, just come early. There’s a good chance that you may not get a seat because right before me, there is a session from SQL Guru Vinod Kumar. He performs a powerful delivery of million concepts in just a little time.

Quiz. I will be asking few questions during the session as well as before the session starts. If you get the correct answer, I will give unique learning material for you. You may not want to miss this learning opportunity at any cosst.

Session Details

Title: Speed Up! – Parallel Processes and Unparalleled Performance (Add to Calendar)

Abstract: “More CPU, More Performance” – A  very common understanding is that usage of multiple CPUs can improve the performance of the query. To get a maximum performance out of any query, one has to master various aspects of the parallel processes. In this deep-dive session, we will explore this complex subject with a very simple interactive demo. Attendees will walk away with proper understanding of CX_PACKET wait types, MAXDOP, parallelism threshold and various other concepts.

Date and Time: March 23, 2012, 12:15 to 13:15

Location: Hotel Lalit Ashok – Kumara Krupa High Grounds, Bengaluru – 560001, Karnataka, India.

Add to Calendar

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

SQL SERVER – Table Variables and Transactions – SQL in Sixty Seconds #007 – Video

60 SQL SERVER   Table Variables and Transactions   SQL in Sixty Seconds #007   VideoToday’s SQL in Sixty Seconds video is inspired from my presentation at TechEd India 2012 on Misconception and Resolution.

Quite often I have seen people getting confused with certain behavior of the T-SQL. They expect SQL to behave certain way and SQL Server behave differently. This kind of issue often creates confusion and frustration. Sometime I have seen them also confusing it with bug and submitting the bug, where reality is totally different. Similar concept which are going to see today. I have seen quite commonly developer assuming that table various will be rolled back when transaction is rolled back. This sixty seconds video describes that table various are not rolled back when transactions are rolled back.

More on Errors:
Difference Temp Table and Table Variable – Effect of Transaction
Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

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

SQL SERVER – #TechEdIn – Presenting Tomorrow on SQL Server Misconception and Resolution with Vinod Kumar at TechEd India 2012

I am excited AND nervous at the same time. I am going to present a very interesting topic tomorrow at an SQL Server track in India. This will be my fourth time presenting at TechEd India. So far, I have received so much feedback about this one session. It seems like every single person out there has their own wishes and requests. I am sure that it is going to very challenging experience to satisfy everyone who attends the event through my presentation.

Surprise Element

Here is the good news: I am going to co-present this session with Vinod Kumar, my long time friend and co-worker. We have known each other for almost four years now, but this is the very first time that we are going to present together on the big stage of TechEd.  When there are more than two presenters, the usual trick is to practice the session multiple times and know exactly what each other is going to present and talk about. However, there’s a catch – we decided to make it different this time and have shared nothing to each other regarding what exactly we are going to present. This makes everything extremely interesting as each of us will be as clueless as the audience when other person is going to talk.

Action Item

Here are a few of the action items for all of those who are going to attend this session. Vinod and I will be present at the venue 15 minutes before the session. Do come in early and talk with us. We would be glad to talk with you and see if either of us can accommodate your suggestion in our session. If we do, we will give a surprise gift for you.

As discussed, this session is going to be a unique two-presenter session. You will have chance to take a side with one speaker and stump the other speaker. Come early to decide which speaker you want to cheer during the session.

Quiz and Goodies

By now, you must have figured out that this session is going to be an extremely interactive session. We need your support through your active participation. We will have some really brain-twisting quiz line up just for you. You will have to take part and win surprises from us! Trust me. If you get it right, we will give you something which can help you learn more!

We will have a quiz on Twitter as well. We will ask a question in person and you will be able to participate on Twitter.

10 – Demos

As I said, both of us do not know what each other is going to present, but there are few things which we know very well. We have 10 demos and 6 slides. I think this is going to be an exciting demo marathon. Trust me, you will love it and the taste of this session will be in your mouth till the next TechEd.

Session Details

Title: SQL Server Misconceptions and Resolution – A Practical Perspective (Add to Calendar)


“The earth is flat”! – An ancient common misconception, which has been proven incorrect as we progressed in modern times. In this session, we will see various database misconceptions prevailing and their resolutions with the aid of the demos. In this unique session, the audience will be a part of the conversation and resolution.

Date and Time: March 21, 2012, 15:15 to 16:15

 Hotel Lalit Ashok – Kumara Krupa High Grounds, Bengaluru – 560001, Karnataka, India.

Add to Calendar

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

SQLAuthority News – #TechEDIn – TechEd India 2012 – Things to Do and Explore for SQL Enthusiast

TechEd India 2012 is just 48 hours away and I have been receiving lots of requests regarding how SQL enthusiasts can maximize their time they’ll be spending at TechEd India 2012. Trust me – TechEd is the biggest Tech Event in India and it is much larger in magnitude than we can imagine. There are plenty of tracks there and lots of things to do. Honestly, we need clone ourselves multiple times to completely cover the event.

However, I am going to talk about SQL enthusiasts only right now. In this post, I’ll share a few things they can do in this big event. But before I start talking about specific things, there is one thing which is a must – Keynote. There are amazing Keynotes planned every single day at TechEd India 2012. One should not miss them at all.

Social Media

I am a big believer of the social media. I am everywhere – TwitterFacebookLinkedIn and GPlus. I suggest you follow the tag #TechEdIn as well as contribute at the healthy conversation going on right now. You may want to follow a few of the SQL Server enthusiasts who are also attending events like TechEd India. This way, you will know where they are and you can contribute along with them. For a good start, you can follow all the speakers who are presenting at the event. I have linked all the speakers’ names with their respective Twitter accounts.


Do not stop meeting new people. Introduce yourself. Catch the speakers after their sessions. Meet other SQL experts and discuss SQL as well as life aside SQL. The best way to start the communication is to talk about something new. Here are a few lines I usually use when I have to break the ice:

  • SQL Server 2012 is just released and I have installed it.
  • How many SQL Server sessions are you going to attend? I am going to attend _________
  • I am a big fan of SQL Server.


Agenda Day 1

  • T-SQL Rediscovered with SQL Server 2012 – Jacob Sebastian
  • Catapult your data with SQL Server 2012 integration services – Praveen Srivatsa
  • Processing Big Data with SQL Server 2012 and Hadoop  – Stephan Forte
  • SQL Server Misconceptions and Resolution – A Practical Perspective – Pinal Dave and Vinod Kumar
  • Securing with ContainedDB in SQL Server 2012  – Pranab Majumdar

Agenda Day 2

  • Hand-on-Lab – Exploring Power View with SQL Server 2012 – Ravi S. Maniam
  • Hand-on-Lab – SQL Server 2012 – AlwaysOn Availability Groups  – Amit Ganguli

Agenda Day 3

  • Peeling SQL Server like an Onion: Internals Debunked  – Vinod Kumar
  • Speed Up! – Parallel Processes and Unparalleled Performance  – Pinal Dave
  • Keeping Your Database Available – ‘AlwaysOn’  – Balmukund Lakhani
  • Lesser Known Facts of SQL Server Backup and Restore  – Amit Banerjee
  • Top five reasons why you want SQL Server 2012 BI – Praveen Srivatsa

Product Booth and Event Partners

There will be a dedicated SQL Server booth at the event. I suggest you stop by there and do communication with SQL Server Experts. Additionally there will be booths of various event partners. Stop by their booth and see if they have a product which can help your career. I know that Pluralsight has recently released my course on their online learning site and if that interests you, you can talk about the subject with them.

Bring Your Camera

Make a list of the people you want to meet. Follow them on Twitter or send them an email and know their location. Introduce yourself, meet them and have your conversation. Do not forget to take a photo with them and later on, share the photo on social media. It would be nice to send an email to everyone with attached high resolution images if you have their email address.

After-hours parties

After-hours parties are not always about eating and meeting friends but sometimes, they are very informative. Last time I ended up meeting an SQL expert, and we end up talking for long hours on various aspects of SQL Server. After 4 hours, we figured out that he stays in the same apartment complex as mine and since we have had an excellent friendship, he has then become our family friend. So, my advice is that you start to seek out who is meeting where in the evening and see if you can get invited to the parties. Make new friends but never lose mutual respect by doing something silly.

Meet Me

I will be at the event for three days straight. I will be around the SQL tracks. Please stop by and introduce yourself. I would like to meet you and talk to you. Meeting folks from the Community is very important as we all speak the same language at the end of the day – SQL Server.

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

SQL SERVER – TechEd India 2012 – Content, Speakers and a Lots of Fun

teched2012 SQL SERVER   TechEd India 2012   Content, Speakers and a Lots of FunTechEd is one event which every developers and IT professionals are looking forward to attend. It is opportunity of life time and no matter how many time one gets chance to engage with it, it is never enough. I still remember every single moment of every TechEd I have attended so far. We are less than 100 hours away from TechEd India 2012 event.This event is the one must attend event for every Technology Enthusiast. Fourth time in the row I am going to attend this event and I am equally excited as the first time of the event.

There are going to be two very solid SQL Server track this time and I will be attending end of the end both the tracks. Here is my view on each of the 10 sessions. Each session is carefully crafted and leading exeprts from industry will present it.

Day 1, March 21, 2012

  1. T-SQL Rediscovered with SQL Server 2012 – This session is going to bring some of the lesser known enhancements that were brought with SQL Server 2012. When I learned that Jacob Sebastian is going to do this session my reaction to this is DEMO, DEMO and DEMO! Jacob spends hours and hours of his time preparing his session and this will be one of those session that I am confident will be delivered over and over through out the next many events.
  2. Catapult your data with SQL Server 2012 Integration Services – Praveen is expert story teller and one of the wizard when it is about SQL Server and business intelligence. He is surely going to mesmerize you with some interesting insights on SSIS performance too.
  3. Processing Big Data with SQL Server 2012 and Hadoop – There are three sessions on Big Data at TechEd India 2012. Stephen is going to deliver one of the session. Watching Stephen present is always joy and quite entertaining. He shares knowledge with his typical humor which captures ones attention. I wrote about what is BIG DATA in a blog post.
  4. SQL Server Misconceptions and Resolutions – I will be presenting this Session along with Vinod Kumar. READ MORE HERE.
  5. Securing with ContainedDB in SQL Server 2012 – Pranab is expert when it is about SQL Server and Security. I have seen him presenting and he is indeed very pleasant to watch. A dry subject like security, he makes it much lively. A Contained Database is a database which contains all the necessary settings and metadata, making database easily portable to another server. This database will contain all the necessary details and will not have to depend on any server where it is installed for anything. You can take this database and move it to another server without having any worries.

Day 3, March 23, 2012

  1. Peeling SQL Server like an Onion: Internals Demystified – Vinod Kumar has been writing about this extensively on his other blog post. In recent conversation he suggested that he will be creating very exclusive content for this presentation. I know Vinod for long time and have worked with him along many community activities. I am going to pay special attention to the details. I know Vinod has few give-away planned now for attending the session now only if he shares with us.
  2. Speed Up – Parallel Processes and unparalleled Performance – Performance tuning is my favorite subject. I will be discussing effect of parallelism on performance in this session. Here me out, there will be lots of quiz questions during this session and if you get the answers correct – you can win some really cool goodies – I Promise! READ MORE HERE.
  3. Keep your database available – AlwaysOn – Balmukund is like an army man. He is always ready to show and prove that he has coolest toys in terms of SQL Server and he knows how to keep them running AlwaysON. Availability groups, Listener, Clustering, Failover, Read-Only replica etc all will be demo’ed in this session. This is really heavy but very interesting content not to be missed. He blogged it over here.
  4. Lesser known facts about SQL Server Backup and Restore – Amit Banerjee – this name is known internationally for solving SQL Server problems in 140 characters. He has already blogged about this and this topic is going to be interesting. A successful restore strategy for applications is as good as their last good known backup. I have few difficult questions to ask to Amit and I am very sure that his unique style will entertain people. By the way, his one of the slide may give few in audience a funny heart attack.
  5. Top 5 reasons why you want SQL Server 2012 BI – Praveen plans to take a tour of some of the BI enhancements introduced in the new version. Business Insights with SQL Server is a critical building block and this version of SQL Server is no exception. For the matter of the fact, when I saw the demos he was going to show during this session, I felt like that I wish I can set up all of this on my machine. If you miss this session – you will miss one of the most informative session of the day.

Also TechEd India 2012 has a Live streaming of some content and this can be watched here. The TechEd Team is planning to have some really good exclusive content in this channel as well.

If you spot me, just do not hesitate to come by me and introduce yourself, I want to remember you!

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

SQL SERVER – Install Samples Database Adventure Works for SQL Server 2012

AdventureWorks is a Sample Database shipped with SQL Server and it can be downloaded from CodePlex site. AdventureWorks has replaced Northwind and Pubs from the sample database in SQL Server 2005.The Microsoft team keeps updating the sample database as they release new versions.

For SQL Server 2012 RTM Samples AdventureWorks Database is released:

You can download either of the datafile and create database using the same. Here is the script which demonstrates how to create sample database in SQL Server 2012.

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'D:\AdventureWorks2012_Data.mdf')

Please specify your filepath in the filename variable. Here is the link for additional downloads.

Watch a quick video on the same subject.

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