If you have been following this blog series about starting and maintaining a blog, you might be excited to go start blogging right now.  But if you have a few more questions, let me get them out of the way right now with some Frequently Asked Questions. Here are few of the questions which I discuss over here.

Should I start Blogging?

Of course!  It’s fun, easy (if you love what you do, it will be easy), it is rewarding, and you could even earn money.

What if I get bored?

One of the biggest problems with starting a blog is losing interest after a few weeks, never posting, and slowly your blog disappears into obscurity.  Here are some ways to always feel fresh at blogging: change up your schedule.  If you find yourself getting bored, try writing at different times of the day, or even sitting at a different desk.

Some people burn out because their set their expectations too high.  If you think you’ll be able to post multiple posts every day, and write extra posts so you’ll always have a queue, you’ll probably lose interesting in working so hard and stop altogether.  Set a goal for yourself: one post a day, or one post a week with one extra as buffer.  Make a to-do list and cross off things as you accomplish them – even small victories will feel like big motivators.

How much do I need to spend?

You don’t need to spend any money if you don’t want to!  There are so many free blog hosts that spending any money on a venture you’re not sure you’ll like is a waste.  However, if you feel very passionate about your subject, invest $10 in a domain name so that you’ll have the best head start on internet stardom possible.  If you don’t want to invest any, or if you’re saving your money – spend what you can afford, even if it’s $0.

What do I write?

So you’ve started a blog and now you want to know what to write.  I’m afraid you might have done things a little backward – it is best to choose a topic for your blog, and THEN start the blog.  If it’s too late for that – try not to overthink it.  Write about whatever you want, it’s YOUR blog.  Don’t stress too much about making that first post perfect – you should write about five or ten posts and post them all at once, so no one is going to go back to your first post and think “pffft, what a silly idea!”

He are few of the question which I discuss in depth in the Pluralsight course.

There are so many more questions out there, I know.

If you’re still curious about blogs and blogging, check out my course on Pluralsight!

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

To have a successful blog, your posts should be interesting, you probably would like to have a large audience, and you want to provide quality content to the subject.  However, to be truly successful, you also ought to follow a few ethical rules, as well – your blog might be popular, but if you are just plagiarizing from a less well-known source, your success is not honestly won, and not truly your own.

Legal Issues

One of the biggest issues that bloggers ought to keep in mind is copyright law.  It can be very easy when blogging to get excited about an article, idea, or photo, and immediately post a glowing review of it to your blog – only to get in trouble when the author or owner stumbles across it.  Shouldn’t they be happy with your free advertisement?  Shouldn’t they appreciate the business you are sending your way?  Unfortunately, discriminating between a blogger who is trying to help and one who is trying to pass off this cool new information as their own can be difficult to determine.  If you want to post something someone else created – text, photos, videos, music – CITE YOUR SOURCES!  Provide links, and ask permission – they may be able to provide a copy of that photo you love, but with their personal watermark.

Right now it might be feeling like posting anything on your blog can be a dangerous proposition.  How can anyone post a picture on their blog without getting sued?  This problem has a flip side – freelance photographers will also have trouble getting their work out there.  That is where Creative Commons stepped in.  It is a non-profit organization that is dedicated to providing licenses and legal protections to artists’ work – any element, which includes designs, photographs, logos, you name it.  There are six different kinds of licenses, and they protect and restrict in different ways.  There are now hundreds of websites that provide Creative Commons images for free or a small fee, and they do the legal footwork for the bloggers.  Everyone wins!

Privacy

User privacy is a huge issue.  With the abundance of spam and spammers out there on the internet, all users are rightfully careful about protecting their private information.  Gathering private information about your readers without their knowledge is strictly prohibited, and you could be prosecuted to the full extent of the law.  Spreading or, worse, selling information given to you willingly – such as a form filled out to become a commenter – without the commenter’s knowledge or permission, is also strictly illegal.  Even if you are gathering e-mail addresses from readers to send out a weekly newsletter, please be sure to provide an opt-in or opt-out box, so that there is no question of whether your readers were spammed by you or received a desired e-mail from you.

Ethics and Etiquette

This is my favorite subject and there are so many to discuss. There are many does and don’ts to do for blogging. Online behavior of any person represents a lot about them. There are other rules to keep in mind while blogging, especially my “grandma rule”. It will be not possible to write about everything which I discuss in the course over here so I suggest you head over to Pluralsight course and read them in detail.

If you’re still curious about blogs and blogging, check out my course on Pluralsight!

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

Getting Started

Is writing an interesting blog so simple that I can just TELL you how to do it right now?  Almost!  I’m sure you started your blog because you had something you felt you had to write about.  But now you’ve created the site and are staring at a blank page that represents all the millions of different directions you can go with this new idea.  Just remember as you write – if you wanted to write about it, there are certainly a few people who want to read it.  Here are a few ideas to get you started.

Write what you would want to read.  You started this blog because you probably felt that there was something more you could personally add the subject, or that wasn’t being addressed.  There is surely a subject that gets you really hot under the collar (in a good way or a bad way).  This will always make an exciting blog post.  It’s something you wanted to read about and couldn’t find – so write about that!

Create a picture in your mind of who you think is reading your blog – or who you’re like to read your blog.  You can even come up with a cast of characters who you think would be interested.  Each one will be different and have different interests – and then you can write posts to these imaginary readers.  The larger the cast, the more ideas who will be able to generate.

Writing an Interesting Post

Some of the best orators start out with a story, and then link back to the original topic in a unique or interesting way.  This captures the audience’s attention at the very beginning, and then they are along for the whole ride.  Here is how to capture this same magic in your blog posts.

Ask a question at the very beginning.  Here is an example: how can you write the most amazing blog post of all time?  Well, this question certainly is leading the readers to think they are about to find out!  Don’t overuse this technique, it can seem hackneyed and old, but used in the right context it can be very effective.

Here are two tricks of many I discuss in my course.

Start with action – A lone blogger, in front of a flickering computer screen, with an unknown fate – to prevail in the face of writing a blog post, or to crumble under the pressure.  This is obviously a little bit too melodramatic for an average blog post, but you get the idea.

Paint a Picture – I don’t mean literally, of course.  But if you can paint a picture with words: the dark night sky was sprinkled with tiny stars, but I knew I could see dawn on the dusky horizon.  Very soon, my blog post would be due.  Long descriptions may not work for the average technical blog, but there are certainly ways to work them in.

If you are really stuck and are staring at a blank screen because you can’t think of a catchy intro, just start writing and worry about it later.  You can even write your last paragraph, and work backwards from there.  Eventually you will get into the swing of writing and the introduction will come naturally.

Make Your Blog Scannable

A scannable blog is a readable blog.  It does not mean that readers will stop by, scan for 15 seconds and leave – it means that readers will stop by, scan for 15 seconds, easily find a topic of interest, and stay – maybe becoming permanent readers!  You have to remember – people tend to read blogs in-between doing other things.  Blogs have to be quick and easy to read so that they can get back to work, minding their children, or cooking dinner.  Having an easily scannable blog will make your blog more likely to become a popular stopping place for readers.  So what makes a blog scannable? Well, that is the secret I talk about in my course.

In my course on Pluralsight, I also include information about writing a post from beginning to end, and overcoming the biggest of all bloggers fears: blogger’s block.

If you’re still curious about blogs and blogging, check out my course on Pluralsight!

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

Choosing a Blog Host

If you’ve recently decided to start blogging, the very first thing you have to decide on is which hosting service to use.  There are many blog hosts: Blogger, WordPress, Typepad, to name just a few.  With so many options, how can you choose?

There are a few criteria to keep in mind when choosing a blog host. What is the purpose of this blog?  If it a personal website, you don’t need anything too technical or one that is uniquely designed for you.  However, if you are starting a blog for a business, you will want to make your blog look unique and stand out from the crowd – and you don’t want to run into trademark issues later.

Customizability and technical abilities are also something to think about.  If you want your blog to have the perfect shade of blue and a special font, you will want a blog host with those options – but if you don’t feel comfortable coding in HTML, make sure your blog host takes care of those technical details for you!  Your budget will play into these considerations.  Many blog hosts are completely free – but these are often the kind of “cookie cutter” blogs that all look identical.  If you want something tailored to you, you can expect a monthly fee.

Choosing a Domain Name

Choosing a domain name is no easy task.  Some people might say its the hardest part of starting a blog or website (and I do not recommend thinking up a name and then choosing what to blog about).  However, if the millions upon billions of websites already on the internet are any indication, you will be able to think of a domain name.  Here are some general rules to stick with:

Use keywords.  Search engines use keywords to find your site and bring it up during searches.  These keywords are generally found in the content of your site – but these search programs check your domain name, too!  Use a “.com” address at all costs.  If your only option with a certain name is to choose .net or .biz, scrap the name and start over.  These suffixes scream “I don’t know what I’m doing!”

Make it easy for your readers to find you again – make your domain name obvious.  It should be easy to spell, because 90% of individuals are terrible spellers, and avoid unnecessary additions at all costs.  These additions include “z” instead of “s”, hyphens (cool-website-look-here.com is terrible!), numbers instead of letters (especially the letter 0, which is easily confused with “o”), and slang or purposely misspelled words.

Design Elements

Lets talk about the real details of blog design.

As I mentioned, a dark background and light font almost never looks good.  Start off with basic colors at first, and then branch out.  A good rule of thumb is that your blog should have no more than three colors, and they should blend well together.  A white background, black text, and a bright color for headlines, titles, etc will look very nice and can easily be customized down the road.

Be just as careful when choosing a font.  When you’re designing your blog, the thought of using Times New Roman or Arial might seem so boring you just want to scream.  But those fonts are universal for a reason – they are easy to read.  If your font is so unique that it is distracting, your content is not what people will remember.  Unless you are writing a blog about funny fonts, stick with something simple.

Be sure to leave a decent amount of space between posts, or add a divider between posts so that readers won’t get confused.  This kind of ritual will actually make readers feel more comfortable – it is a steady, comforting presence.  Menus and links will help keep your blog organized and navigable.  They can reassure readers that your blog has a strong history of posts, and is not just another newcomer that will be here today but gone tomorrow.

Well, there are few more interesting tips as well, which I have discussed in depth in my Pluralsight Course.

Comments – Yea or Nay?

Most blog hosts allow commenting on individual posts or the blog as a whole.  Some will also provide the option to allow comments at all.  If you have ever stumbled into a nasty blog fight, it might give you pause to allow this kind of risk on your own blog, but there are so many benefits to allowing comments.  First of all, it allows you to build a community of like-minded individuals.  Commenters will interact with your material as well as each other.

Of course, the downside of comments is the spam.  It can be mean commenters, people trying to stir up trouble, or just your average spammer trying to get their links clicked.  Because spam is such a widespread problem, any blog host that allows comments will also have a way to deal with the spam.  It can mean screening all comments, or simply keeping on top of comments and deleting the spam.

If you’re still curious about blogs and blogging, check out my course on Pluralsight!

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

Thinking about starting a blog? Confused about the entire idea of blogging?  I have created a course on Pluralsight relating to this very issue.  Let’s start with the easy questions: what is the difference between a blog and a website?  Websites are static sites with a single purpose.  A blog is a type of website – one that is dynamic and always changing when you post and update.  Websites and blogs are similar because they are both addresses on the internet, and a way to connect to the public.

Website or Blogs

Websites and blogs have different purposes, uses, and audiences.  Websites are good for businesses or individuals with a product to sell.  They act as a store front – they welcome the public, and stay the same over time so that their products are easy to find.  Blogs are more dynamic.  You can update a blog every day, or multiple times a week, to keep in touch with your audience.  This is like having a conversation with interested parties, and form a network or community of like-minded individuals.

Deciding to blog can be a difficult decision – there are many pros and cons.  Blogs can be a challenge to set-up, and require maintenance.  But for me, the pros far outweigh the cons!  You get to talk about something you love, meet people with similar interests, and be in charge of your own “brand.”

Different Types of Blogs

There are a huge variety of blogs, and many different types of bloggers.  Blogs fall into a few main categories: technology, personal, photography, automobile, and corporate.  I consider technology blogs to be anyone, professional or amateur, who talks about technology – hardware, software, highly specialized, or very general.

Personal blogs are the kind that are about people’s personal interests.  This can be children, religion, crafting, or even just day-to-day updates.  Photography blogs can cover a wide variety (even personal and technology), but tend to be mainly high quality photographs taken by professional who want to share their work, or amateurs who are trying to get better and would like feedback.

Automobile blogs can cover any sort of vehicle or interest.  They are often highly technical about one specific type of car, truck, boat, or sport.  Corporate blogs are professional blogs, run by companies.  They often have multiple authors, and are updated multiple times a day – or at least when breaking news about the company or product becomes available.

As many different types of blogs as there are, there are an equal number of types of bloggers.   I like to divide them into four easy categories (often with a lot of cross-over).

Different Types of Blogger

Professional: these are bloggers who blog for a living.  Maybe they work for a company and run the corporate blog.  Maybe they created their own personal blog that became extremely popular and generates a lot of income.  No matter how they got there, they are extremely successful and have found the magic button for becoming great bloggers.

Amateurs: this obviously covers most blogs.  These people blog for the sake of blogging, because they love a topic or want to share their expertise.  Everybody starts an amateur, and can progress to any of these other types of bloggers.

Artists: these bloggers can be amateurs or professionals.  They might be photographers, or simply good at painting a picture with their words.

Multi-author blogs: any type of blog can be a multi-author blog.  Some blogs start out with more than one author, because they want multiple opinions, an individual can’t keep up with the amount of writing required, or because a group of friends has a similar interest and wants to start a blog.  More commonly, a blog explodes in popularity and needs a team of writers to keep up with the traffic.

If you’re still curious about blogs and blogging, check out my course on Pluralsight!

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

During this journey, one of the most common suggestion I received was that I should write about how to build a successful blog. I have been delaying to write this subject for a long time, the reason was simple – I still see that I have a long way to go before I call myself successful.

Short Life Span

However, every single day I see new blogs coming up and some of them are so good that I immediately subscribe to them. Today when I go back to the my subscription list, I see out of the big list there are very few blogs are active. Some of the blogs are deserted and some of them are just dead. There are few blogs so good that I really wish the author was still engaged on the blog and writing the interesting content everyday.

Due to any reason I see not all great blog continuing their journey. The most common reason I see is that blog author has lost motivation to write due to lack of interest or getting busy in their other daily activities. Here is my question to you – if we can clean our teeth everyday, why can’t we blog everyday?

Recent Motivation

In the month of February I attended Pluralsight Author Summit. On the very last day at the event, I went to Snow Basin for skiing. Well, I do not know skiing but it is indeed fun to hang around with friends. During the  event, Pluralsight CEO Aaron Skonnard suggested that I should write a blog about how to build a successful blog as it many would be interested in reading about the secret behind building such a large audience and interesting stories. I absolutely see reason and logic behind what Aaron suggested and I decided to build a video course over this subject.

Building a Successful Blog

Here is the link to Pluralsight Course Building a Successful Blog. There are a total of six modules in this course. I have carefully build each module keeping beginners and experts both in mind. Each module attempts to answers questions for beginners as well as expert in this field. Blogging is such a wide area that it is hard to know everything for everybody. During my journey of over six years I believe there are so many lessons that I have just learned in my recent years. My goal is that I give everything I know to the community and later community can build their own successful blog on the lessons and learning I have shared to them.

Here are modules of the Pluralsight Course Building a Successful Blog

• Introduction
• Blogging Concepts, Ideas and Motives
• Getting Started with Blogging
• Writing an Interesting Blog
• Blogging Rules, Ethics and Etiquette
• Frequently Asked Questions
• Checklist for Building successful Blog

In the next week, I will be discussing writing one blog post for each of the subject to touch base about them. I will give a quick insight about the content which I have covered in the course. I will be linking each of the above topics as blog posts are published on the blog.

If you’re still curious about blogs and blogging, check out my course on Pluralsight!

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

2007

List all the database
A Simple script which list all the database from the server.

Function to Parse AlphaNumeric Characters from String
Following function keeps only Alphanumeric characters in string and removes all the other character from the string. This is a very handy function when working with Alphanumeric String only. I have used this many times.

Query to Find First and Last Day of Current Month
The blog contains the query, which will run respective on today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month.

Binary Sequence Generator – Truth Table Generator
Run script from the blog in the query editor to generate truth table with its decimal value and binary sequence. The truth table is 512 rows long. This can be extended or reduced by adding or removing cross joins respectively.

Disable Index – Enable Index – ALTER Index
A straight to script blog where I demonstrate how to enable and disable index.

2008

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable – Part 2
There is lots of confusing going on about difference between TempTable and TempVariable. In this blog I take another attempt about how to find difference between TempTable and TempVariable.

SQL SERVER Database Administrator Job Description
I have received quite a lot of requests to update it or post something similar. Writing SQL Articles are easier than writing Job description for DBA. I have read many job description and job posting at Best SQL Jobs and found the following job description.

2009

This year, I have attended my very first TechEd and presented very first session ever. Here is a photo with my wife Nupur at this event.

How to Drop Temp Table – Check Existence of Temp Table
A quick direct to script blog post.

2010

Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index
The difference between ALTER INDEX ALL REBUILD and ALTER INDEX REBUILD along with the disabled clustered index. Instead of writing a big theory, we will go over the demo right away. Here are the steps that we intend to follow.

1) Create Clustered and Non-clustered Index
2) Disable Clustered and Non-clustered Index
3) Enable – a) All Indexes, b) Clustered Index

Find Most Expensive Queries Using DMV
I was asked in recent query tuning consultation project, if I can share my script which I use to figure out which is the most expensive queries are running on SQL Server. This script is very basic and very simple, there are many different versions are available online.

List All the DMV and DMF on Server
“How many DMVs and DVFs are there in SQL Server 2008?” – this question was asked to me in one of the recent SQL Server Trainings. The answer is very simple in this blog post.

2011

Import CSV File into Database Table Using SSIS
It is a very frequent request to upload CSV file to database or Import CSV file into a database. I have previously written an article how one can do this using T-SQL over here  Import CSV File Into SQL Server Using Bulk Insert. One of the request which I quite often encounter is how I can do the same using SSIS package and automate it. Today we will understand the same using images.

Restart Remote Computer – Shutdown Remote Computer
Here is a quick trick which I use almost everyday. I have more than one computer at my desk and I manage multiple instances from a single monitor. Some of my computers are in a different location and I have to physically walk there. In this blog post, I demonstrated a quick method where I can use CMD prompt to restart and shutdown remote computer.

2012

Columnstore Index and sys.dm_db_index_usage_stats
Regular SQL Server DMV does not give information for usage stats for columnstore indexes and for that one has to use different DMV. In this blog post I explain the same in detail with DMV script. I also describe my journey how I come across this DMV.

Get Schema Name from Object ID using OBJECT_SCHEMA_NAME
Sometime a simple solution have even simpler solutions but we often do not practice it as we do not see the value in it or find it useful. Well, today’s blog post is also about something which I have seen not practiced much in codes. We are so much comfortable with an alternative usage that we do not feel like switching how we query the data.

SQL in Sixty Seconds – 5 Videos from Joes 2 Pros Series
Here are the five videos which Rick and I have built for Joes 2 Pros series.

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

Just quick to summarize the puzzle. Here is the quick recap of the same.

Now let us run following script.

DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
SET @FirstString = REPLICATE('A',4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE('B',8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE('C',11000)
SELECT LEN(@ThirdString) LenThirdString;

The script above will return following result:

Quiz 1:

Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

Answer 1:

The reason for the same is that if the first parameter of the REPLICATE function is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, the first parameter must be explicitly cast to the appropriate varchar(max) or nvarchar(max).

Quiz 2:

What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

Answer 2:

To return the result as 11000, one has to just CAST or CONVERT the first parameters to VARCHAR(MAX) or NVARCHAR(MAX). Here is the example of the same.

DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),'C'),11000)
SELECT LEN(@ThirdString) LenThirdString;

Now let us see the result set.

DB Optimizer

One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.

If you notice that this was not very difficult puzzle but it was interesting for sure. There are so many valid answers that it will be not possible to name every single person. I strongly encourage all of you to go over the original blog post and read all the comments. Though all the comments are very similar there are so many new information there that I will say wealth of information just right there in the comments area.

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

When I started this blog I had no idea that I will be blogging after more than 6 years and I will ever reach to 2500th blog post. It was all just started as my personal bookmark. I was expecting that I will be a single reader of this blog and had no idea for a long time that even other people can read my blog as well. Once the very first comment appeared everything was just new world for me.

I never thought I would be able to engage with so many people from all over the world. I initially thought my blog will be majorly read by Indians as India has largest population but I am delighted to see that there is a balance among continents. As of today, this blog is the most read in USA and second place is India. Thank you readers from all over the world to support me  in spreading the knowledge.

Click to Enlarge

Another interesting and important detail is that I have been receiving over 2,000,000 views per month consistently every month. I never thought so many IT Professional would be reading this blog. When I see this 2 million number, I wonder often about readers.

If you are my blog reader, I would like to know you, would you please tell me who you are and why do you visit this blog? I encourage all you to leave a comment in the comments section. On this 2500th blog post, I will select random 10 readers and will send them surprise educational gift.

Online Course on how to Build a Successful Blog

I am often asked what are the best practices to build a successful blog. I have built a video course on the topic of How to build a successful blog. 

Watch the teaser of this course.

Here are few of the topics which I am covering in the course:

• Blogging – Concepts, Ideas And Motives
• Getting Started With Blogging
• Writing An Interesting Blog
• Blogging Rules, Ethics And Etiquette
• Frequently Asked Questions
• Checklist For Building Successful Blog

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

First run following script:

SET ANSI_NULLS ON;
-- Query1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

You will get following result:

You can clearly see that in the first case we are getting different results. Here are the questions you need to answer to win the Amazon Gift Cards and Bubble Copter R/C.

There are two steps to take participate in the contest:

Step 1: Answer the Question

Question: Why do Query 1 return results but Query 2 does not return any result?

Answer this question in comments area along with the question in Step2.

Step 2: Identify File Size

Question: What is the size of the DevArt Schema Compare installation file in KB? Please leave a note in the comment area.

Please note the size of the file should be KB and not in MB. You can download the file from here.

Giveaway:

• 2 lucky winners will get USD 25 worth Amazon Gift Card (Open worldwide, Total giveaway value USD 250)
• One lucky winner from USA will get  Bubble Copter R/C (Shipping from USA to other countries is not possible)

Rules and Conditions:

• Contest open till May 25 12:00 GMT. 
• Please leave your answer in the comment area in following format:
  • Answer to Q1:
  • Answer to Q2:
• Please note that winner will be selected after May 25th by random selection and will be posted as a comment to this blog.
• The answers will be kept hidden till the winner will be announced to have fair competition.

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

Microsoft documentations suggest that this extended stored procedure reads data from the string into the argument locations specified by each format argument. I still do not get it. I know it is very similar to C function but again I am not sure where in the real world I will use this function. Here is the demonstration of how this function works.

Following example is an enhanced version of the example provided on MSDN.

DECLARE @valueb VARCHAR(20), @filename VARCHAR(20), @message VARCHAR(20)
EXEC xp_sscanf 'sync -b123 -fauthors10.tmp -rrandom', 'sync -b%s -f%s -r%s',
@valueb OUTPUT, @filename OUTPUT, @message OUTPUT
SELECT @valueb, @filename, @message

The above query will return us following result:

-------------------- -------------------- --------------------
123                  authors10.tmp        random

Here is the result set.

You can see how xp_sscanf retrieves the parameters from the string and returns as an output. However, I am still not sure where I will use this feature in real world scenarios. Any insight and or guidance will be helpful.

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

SQL Server have REPLICATE function which will replicate the string passed as many as times as the second parameter. For example execute following string.

SELECT 'Ha'+REPLICATE('ha',20)

The script above will return following result:

`

You can notice that it has returned a string ha about 20 times after first Ha.

Now let us run following script.

DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
SET @FirstString = REPLICATE('A',4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE('B',8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE('C',11000)
SELECT LEN(@ThirdString) LenThirdString;

The script above will return following result:

Quiz 1:

Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

Here is the second part of the Quiz.

Quiz 2:

What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

DB Optimizer

One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.

Please leave a comment with your answer. I will publish the answer to this puzzle on coming Friday’s blog post and will mention the names of the all the valid users.

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

Before we start please note that as per SQL Server official documentation CONCAT_NULL_YIELDS_NULL will be always ON in the future so avoid setting it off. What this function essentially does is that when it is on it will return a null value when any other value is connected it with using + operator. If CONCAT_NULL_YIELDS_NULL is set to OFF it will return the original value instead of NULL.

Let us see a simple example of the same.

-- Before SQL Server 2012
-- SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT 'Some Value' + NULL AS EarlierVersion;
GO
-- SET CONCAT_NULL_YIELDS_NULL OFF
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'Some Value' + NULL AS EarlierVersion;
GO


However, in SQL Server 2012 the behavior of the ‘+’ operator remains the same but the behavior of function CONCAT is bit different and it totally ignores this value. Here is the example of it.

--- SQL Server 2012 and onwards
-- SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT CONCAT('Some Value', NULL) AS SS2012;
GO
-- SET CONCAT_NULL_YIELDS_NULL OFF
SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT CONCAT('Some Value', NULL) AS SS2012;
GO

I assume this may be intentional as in future the value of CONCAT_NULL_YIELDS_NULL will be always ON. Irespectively I find it very interesting how CONCAT function works with NULLs.

Read more about CONCAT function over here as well you can watch the video about how the concrete function works with SQL Server 2012.

String Function – CONCAT() – A Quick Introduction

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

2007

UDF – Function to Convert List to Table
Article contains UDF written for SQL SERVER 2005. It will also work well with the very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR(MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR. It will convert the list to table.

Creating Comma Separate Values List from Table – UDF – SP
Script contained in the blog will create common separate values (CSV) or common separate list from tables, convert list to table. Following script is written for SQL SERVER 2005. It will also work well with the very big TEXT field. If you want to use this on SQL SERVER 2000 replace VARCHAR (MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR.

Examples and Explanation for GOTO
GOTO statement can be used anywhere within a procedure, batch, or function. GOTO can be nested as well. GOTO can be executed by any valid user on SQL SERVER. GOTO can co-exists with other control of flow statements (IF…ELSE, WHILE). GOTO can only go(jump) to label in the same batch, it can not go to label outside of the batch.

Index Optimization CheckList
Index optimization is always interesting subject to me. Every time I receive request to help optimize query or query on any specific table. I always ask Jr.DBA to go over following list first before I take a look at it. Most of the time the Query Speed is optimized just following basic rules mentioned below. Once following checklist applied interesting optimization part begins which only experiment and experience can resolve.

Explanation SQL Command GO
GO is not a Transact-SQL statement; it is often used in T-SQL code. Go causes all statements from the beginning of the script or the last GO statement (whichever is closer) to be compiled into one execution plan and sent to the server independent of any other batches. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

2008

Here are two straight to script blog posts where I explain how to do something quickly and easily.

• Find Table in Every Database of SQL Server
• Find Tables With Foreign Key Constraint in Database

2009

Find Last Date Time Updated for Any Table
If a user wants to finds out when was the last table updated he can query dynamic management view (dmv) – sys. dm_db_index_usage_stats and easily figure out when the table was updated last. Let us comprehend this example by creating a table and updating it. We can use dmv to determine when it was updated last.

Questions and Answers with Database Administrators
Interesting conversation – I recommend everyone to read this.

Q. According to you what goes into making the best Database Administrator?

A. The primary job of DBAs is to secure the data. They should be able to keep it safe as well as reproduce it efficiently, whenever required. So as per my view, a Database Administrator who can fulfill the requirements of Securing Data and Retrieving Data is the best DBA.

When I hire a DBA I always ask them questions about backup strategies and efficient restoring methodologies.

2010

Get Latest SQL Query for Sessions – DMV
A straight to script blog which is my favorite as well.

Size of Index Table for Each Index – Solution 2
Another blog post where I explain how to find size of index using T-SQL script.

Spatial Database Queries – What About BLOB
In this blog post I explain how to find distance between two cities using Spatial Database.

Understanding ALTER INDEX ALL REBUILD with Disabled Clustered Index
Not everyone knows the difference between ALTER INDEX ALL REBUILD and disabled Clustered index. In this blog post, I have tried to explain the same with example.

2011

Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
One of the most popular blog post because it is the basic requirement of the developer. Developers like to do things their own way on their database. This blog post explains how developers can absolutely create another replica of the database and later do practice over it.

What Kind of Lock WITH (NOLOCK) Hint Takes on Object?
Do we know what kind of lock WITH(NOLOCK) hint takes on object. Answer is Schema Lock. Yes, WITH(NOLOCK) hint takes Schema Lock on the object which is accessed. In this blog post we have working example where I demonstrate the same.

Resource Database ID – 32767
The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

2012

In year 2012 in a single week, I had come up with the Quiz based on the Joes 2 Pros Book and here are the five questions asked in the quiz. Just like every other quiz, this quiz also had a video hints. Something very unique and never been attempted.

• Quiz and Video – Introduction to SQL Server Security
• Quiz and Video – Introduction to Hierarchical Query using a Recursive CTE
• Quiz and Video – Introduction to Basics of a Query Hint
• Quiz and Video – Introduction to SQL Error Actions
• Quiz and Video – Introduction to Discovering XML Data Type Methods

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

In following example we will first create a sample table and later we will add a column which will be defaulted to the current date time when any new record is inserted. The only drawback of this method is that if there is any existing row in your table it will be automatically have the current date time when the column is created. Honestly I do not see any solution to this issue as this is related to design of the database. If you know what was the datetime when rows were created you can update those rows with those value otherwise, just have any values stored there.

Let us see our solution. Let us first create a table which does not have column with current datetime. In our case we will assume that there are only two rows in the table.

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (ID INT, Col1 VARCHAR(100));
-- Insert Values
INSERT INTO TestTable (ID, Col1)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second';
-- Select from table
SELECT *
FROM TestTable
GO

Now let us add a column to this table with default value as a current datetime. You will notice that the two rows which are inserted into the table have current datetime.

-- Add Column with Default Current Date Time
ALTER TABLE TestTable
ADD DateInserted DATETIME NOT NULL DEFAULT (GETDATE());
-- Select from table
SELECT *
FROM TestTable
GO

As a third step let us enter the a new row. Make sure that you do not insert any value in the newly created column where you have default value as a current date time.

-- Now Insert New Rows
INSERT INTO TestTable (ID, Col1)
SELECT 3, 'Third';
INSERT INTO TestTable (ID, Col1)
SELECT 4, 'Fourth';
GO
-- Select from table
SELECT *
FROM TestTable
GO

You will notice in the result set that the new column will contain current date time of the row created. This way you can get the value when the row was created.

Now you can clean up the resultset.

-- Clean up
DROP TABLE TestTable
GO

Here is the question back to you – “It is simple to create a column where we have default daytime value to know when the row was created. Is there any way to know when the row was updated without explicitly updating any column with datetime?”

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

For those of you who haven’t had a chance to read my previous article, NuoDB is a distributed cloud database that supports SQL and ACID transactions. A single logical NuoDB database can be deployed on one or many cloud machines to support the scale-out model without the need to partition, shard, or replicate.

The NuoDB distributed architecture enables you to scale computational and storage resources independently. Meaning, if your application demands higher transactional throughput, simply add more transaction engines, and if you need a highly available redundant database, add more storage managers.

A typical scale-out architecture might look like this:

Each process in the NuoDB architecture represents a virtual or physical cloud resource but the database as a whole continues to represent itself as a single logical database to any client that’s connected to it.

Many of you have been developing, deploying and maintaining .NET applications using SQL Server and might be familiar with setting up a server farm or application cluster to improve performance. As you know, this is no easy task, from both a development and deployment perspective.  I’m not advocating that you eliminate SQL Server from your stack, but let’s entertain the idea of using NuoDB as the primary database for your application backend. Since it presents itself as a single logical database to your application regardless of how many machines it might be running on, you’re able to simplify your code and your scale-out strategy. The transaction engine layer of NuoDB uses a dynamic caching mechanism to eliminate the need to shard or federate your data. Transaction engines connect directly to client applications and are able to process all transaction types (CRUD) and maintain data consistency across the entire deployment environment. This opens up the opportunity to write applications specifically designed for a scale-up architecture and leverage NuoDB to provide scale-out capabilities. Below is an example of co-locating transaction engines on each node in a server farm.

 The genius of this system is that co-located transaction engines should be the most responsive to the application. However, in the event that one or more transaction engines get tied up, the broker process automatically connects applications to the next most responsive. There is no prerequisite for the data to actually be present on the transaction engine. NuoDB uses a built-in asynchronous replication technique to get data directly from other transaction engines or storage managers.

I have been following NuoDB for some time now and with each release the technology becomes more exciting. Click here if you would like to try out the latest 64-bit version of NuoDB that includes an ADO.NET driver for Visual Studio integration. Comments welcome!

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

In the real world, developers sometimes delete few rows from the table and due to any reasons they want to reseed the identity value to a lower value, this feature can be very much useful. However, there is one question which keeps on ringing in my mind that is why would anyone need to reseed identity. Anyway here is the SQL in Sixty Seconds Video which explains how we can reseed identity value.

Let us see the same concept in following SQL in Sixty Seconds Video:

Here is the script used in this episode:

USE tempdb
GO
-- Create Table
CREATE TABLE TestTable (ID INT IDENTITY(1,1), Col1 VARCHAR(100));
-- Insert Table
INSERT INTO TestTable (Col1)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth';
-- Select data
SELECT *
FROM TestTable
GO
-- Returns current Identity Value
DBCC CHECKIDENT ('TestTable', NORESEED);
-- Resets the current Identity value to maximum value
DBCC CHECKIDENT ('TestTable', RESEED, 11);
-- Insert Table
INSERT INTO TestTable (Col1)
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth';
-- Select Table
SELECT *
FROM TestTable
ORDER BY ID;
-- Drop Table
DROP TABLE TestTable;

Related Tips in SQL in Sixty Seconds:

• DELETE, TRUNCATE and RESEED Identity
• Reseting Identity Values for All Tables
• Value of Identity Column after TRUNCATE command
• Identifying Column Data Type of uniqueidentifier without Querying System Tables
• Understanding Identity Beyond its Every Increasing Nature
• Negative Identity Seed Value and Negative Increment Interval

What would you like to see in the next SQL in Sixty Seconds video?

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

Each NuoDB database consists of at least three or more processes that enable a single database to run across multiple hosts. These processes include a Broker, a Transaction Engine and a Storage Manager.  Brokers are responsible for connecting client applications to Transaction Engines and maintain a global view of the network to keep track of the multiple Transaction Engines available at any time. Transaction Engines are in-memory processes that client applications connect to for processing SQL transactions. Storage Managers are responsible for persisting data to disk and serving up records to the Transaction Managers if they don’t exist in memory.

Here is my question to you all – do you ever face a situation of sharding of your database is required? When is sharding required not a good solution?

I have downloaded NuoDB today and going to build a small application which I can play with to build a scenario where sharding is no longer required. Would you be interesting in joining me with this exercise? I would like to work along with you to build such a scenario. Download NuoDB and let us start building something interesting.

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

Once  SQLBackupAndFTP with Azure  is installed, click at “Connect to SQL Server / Azure” button and specify connection properties for your Azure SQL Databases:

Then click “Run Now” to backup your Azure SQL Database(s):

Scheduling backups is also very simple – just check “Schedule this job on the main form” to run once daily or go to Advanced Settings for more options

Sounds simple? There are just a couple more things you need for this to work: you should know your Azure Server Name and Azure should allow your IP to connect. Let me explain how to do it.

How to find your Azure SQL Databases Server Name/Connection String

If you do not know a server name of Azure SQL Database, you can find this out on  Windows Azure Management site.  Sign in with your Microsoft account and click the “SQL DATABASES” menu item, then click your database name

Then click “Show connection strings” link in the bottom right corner:

You will see the connection strings for many platforms. Just copy to clipboard “Server” property value of “ADO.NET” connection like in the screenshot below:

And paste it into the “Server Name” field of the “Connect to SQL Server / Azure” window in SQLBackupAndFTP:

Allow your IP address to connect  to your Azure SQL Databases server

For SQLBackupAndFTP to connect to Azure SQL Database, you need to configure Azure’s firewall. Otherwise you will be getting the error: Cannot open [server] requested by the login. Client with IP address [ip-address] is not allowed to access to the server…

Login to Windows Azure Management site with your Microsoft account, click “SQL DATABASES” menu  item, then click your database:

Then click “Manage allowed IP addresses” link in the bottom right corner::

You will see a page where you can specify an allowed IP addresses for your databases:

On this page you can configure your firewall to allow the machine where SQLBackupAndFTP is running access to Azure. After adding the proper IP you should have no problem connecting to Azure.

Summary

With  SQLBackupAndFTP with Azure it is very simple to take backups of Azure SQL databases as well as of regular SQL Server.

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

The reason I ended up on this DMV was because I was asked a question what are the different reasons any statement can be recompiled. I knew few of the reasons why would any statement recompile but I was not aware of all the reasons for any statement recompilation. After doing some search on the internet and my older archived I figured out the reasons for Statement Recompilation using sails. dm_xe_map_values.

If you have collected trace using either SQL Server Profiler or an Extended Events session, you can capture SP:Recompile event in your trace. When you look at the event it displays the reason for recompiling the statement. However, when you run following statement in SQL Server Management Studio, you can find all the possible reasons for statement recompilation.

SELECT dxmv.name,
dxmv.map_key,
dxmv.map_value
FROM sys.dm_xe_map_values AS dxmv
WHERE dxmv.name = N'statement_recompile_cause'
ORDER BY dxmv.map_key;

When I ran this query, I got following 14 different reasons for statement recompilation. I personally was not aware of all the 14 and it was interesting learning for me.

map_key map_value
1 Schema changed
2 Statistics changed
3 Deferred compile
4 Set option change
5 Temp table changed
6 Remote rowset changed
7 For browse permissions changed
8 Query notification environment changed
9 PartitionView changed
10 Cursor options changed
11 Option (recompile) requested
12 Parameterized plan flushed
13 Test plan linearization
14 Plan affecting database version changed

It will be interesting to build a test case for each of the compilation reason, as it will give a great learning experience.

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

2007

Good, Better and Best Programming Techniques
Well, here is my note which I prepared to discuss in my earlier meeting. This is not complete and is not in very details. This note contains what I think is best programming technique in SQL. There are lots to add here and many opinion are very generic to SQL and other programming languages.

Explanation SQL SERVER Merge Join
Merge join itself is very fast, but it can be an expensive choice if sort operations are required. However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.

Random Number Generator Script – SQL Query
One of the most popular blog post where I demonstrate how many different ways one can generate random numbers. I have also built a SQL in Sixty Seconds Video on this subject as well.

SET ROWCOUNT – Retrieving or Limiting the First N Records from a SQL Query
SET ROWCOUNT option is ignored for INSERT, UPDATE, and DELETE statements against remote tables and local, remote partitioned views and when an explicit TOP expression is used in the same statement. This includes statements in which INSERT is followed by a SELECT clause. SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

Collate – Case Sensitive SQL Query Search
Here is a quick script which does case sensitive SQL Query Search in the database.

Copy Column Headers in Query Analyzers in Result Set
Another very popular request which I transformed into SQL in Sixty Seconds Video.

User Defined Functions (UDF) to Reverse String – UDF_ReverseString
UDF_ReverseString User Defined Functions returns the Reversed String starting from certain positions. First parameters take the string to be reversed. Second parameters take the position from where the string starts reversing.

TOP Improvements/EnhancementsSQL Server 2005 introduces two enhancements to the TOP clause.
1) User can specify an expression as an input to the TOP keyword.
2) User can use TOP in modification statements (INSERT, UPDATE, and DELETE).

Enable Login – Disable Login using ALTER LOGIC – Change name of the ‘SA’
One of the most important but ignored SQL Security Tip. A must read for everyone!

2008

Find Table in Every Database of SQL Server
This is one of the most asked T-SQL Script where user want to know where the table is used in every single database on the server. It is a two part story. Do not miss to read Part 1 and Part 2.

Delete Duplicate Records – Rows – Readers Contribution
A quick script from reader very important article but very less explored by users. Let me know your thoughts about this one.

2009

Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script
SQL Server 2005 and later versions provide encryption as a new feature to protect data against hackers’ attacks. Hackers might be able to penetrate the database or tables, but owing to encryption they would not be able to understand the data or make use of it. Nowadays, it has become imperative to encrypt crucial security-related data while storing in the database as well as during transmission across a network between the client and the server.

Solution to Puzzle – Shortest Code to Perform SSN Validation
Write the shortest T-SQL Code that removes invalid SSN values and returns a result set with only valid SSN values. This blog post was winner of the T-SQL Challenges on the same subject. If will be interesting to see if you can come up with a better optimized solution.

Example of DDL, DML, DCL and TCL Commands
Data Manipulation Language, Data Definition Language, Data Control Language, Transactional Control Language explained in simple words.

Add or Remove Identity Property on Column
How difficult is it to add an Identity property to an existing column in a table? Is there any T-SQL that can perform this action? For most, the answer to the above two questions is an absolute NO! There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an Identity Property to an existing column in a table. However, there is an easy way to accomplish this action. It can be done through SSMS.

Roadmap of Microsoft Certifications – SQL Server Certifications
There are several benefits you can gain after clearing certification exams. After passing the first exam, developers earn credential of Microsoft Certified Professional commonly known as MCP. Once credentials are achieved developers get access to an official transcript, logos and certificates, and have their own landing page on Microsoft.com! According to Microsoft’s official site, real MCPs get real benefits of  Reward, Respect and Recognition.

2010

GUID vs INT – Your Opinion
This is an age old problem and I want to compile the list stating the advantages and disadvantages of using GUID and INT as a Primary Key or Clustered Index or Both (the usual case). The epic intense debate is happening on this particular topic till today after 3 years. You can see there is a wealth of information on this blog which one can just learn reading the original blog post as well as comments associated with the blog post.

Disable Clustered Index and Data Insert
Should we disable the clustered index while we insert the large data or we should drop the clustered index while we insert large data. Honestly, the answer is very simple, drop the clustered index. If we disable clustered index the impact of it is much different than we expect. Check out this blog post which describes the same issue with working demo.

Here is one of my old photo of TechEd 2010 – I had great time presenting on the stage in year 2010.

What is AdventureWorks?
This question is always there in my mailbox. Till today, everyday multiple times. Here is the blog post where I describe where one can get AdventureWorks as well I explain how they can install it as well.

SHRINKFILE and TRUNCATE Log File in SQL Server 2008
There are few absolute No-No’s in SQL Server environment. There should be no need to shrink and truncate log files in daily routine. Though this blog post was written keeping SQL Server 2008 in mind, this is also valid in SQL Server 2008 R2 and SQL Server 2012. If you are going to shrink your database, I strongly suggest that you read this blog post before it.

2011

Executing Stored Procedure with Result Sets

Q: Why are you using User Defined Function instead of Stored Procedure?

A: I cannot SELECT from SP, but I can from UDF.

The SQL Server 2012 version has come up with a very interesting feature called WITH RESULT SET.

Read here the follow up article.

CTAS – Create Table As SELECT – What is CTAS?
CTAS stands for ‘Create Table As Select’. This method is used when table was not created earlier and needs to be created when data from one table needs to be inserted into a newly created table from another table. The new table is created with the same data types as that of the selected columns.

Performance Improvement with of Executing Stored Procedure with Result Sets in SQL Server 2012
This is one of the article which really explains why one should use any feature for example. One should use the feature of Executing Stored Procedure with Resulset because it gives better performance over traditional methods.

2012

Microsoft Certification – SQL Server 2012
Read this blog post which describes how new Microsoft Certification have changed with the release of SQL Server 2012.

Video – Step by Step Installation of SQL Server 2012
Here is a video tutorial for how to install SQL Server 2012.

A Tricky Question and Even Trickier Answer – Index Intersection – Partition Function
Write a select statement using a single table, using single table single time only without using join keywords, which generate an execution plan with 2 join operators. Use AdventureWorks as a sample database.

Identify Columnstore Index Usage from Execution Plan
How do I know if columnstore index is used by query through execution plan? Here is the detail blog post about how to identify if columnstore index is used in the query or not.

Here are the steps which are to be followed:

• Create a sample table
• Insert some data
• Create clustered index on it
• Create nonclustered Columnstore Index on it
• Enable execution plan in SSMS
• Run two SELECT statement together with using clustered index and columnstore index (use hint if needed)

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

It is totally possible to use JOIN and multiple tables in the DELETE statement. Let us use the same table structure which we had used previously.

Let us see the following example. We have two tables Table 1 and Table 2.

-- Create table1
CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table1 (Col1, Col2, Col3)
SELECT 1, 11, 'First'
UNION ALL
SELECT 11, 12, 'Second'
UNION ALL
SELECT 21, 13, 'Third'
UNION ALL
SELECT 31, 14, 'Fourth'
GO
-- Create table2
CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table2 (Col1, Col2, Col3)
SELECT 1, 21, 'Two-One'
UNION ALL
SELECT 11, 22, 'Two-Two'
UNION ALL
SELECT 21, 23, 'Two-Three'
UNION ALL
SELECT 31, 24, 'Two-Four'
GO


Now let us check the content in the table.

SELECT *
FROM Table1
SELECT *
FROM Table2
GO

Now pay attention to following diagram. Here we have two tables Table1 and Table 2. Our requirement is that we want to delete those two records from Table1 where Table2 Col3 values are “Two-Three” and “Two-Four” and Col1 in both the tables are the same.

I have explained the above statement very easily in following diagram.

When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. However, the easiest and the most clean way is to use JOIN clause in the DELETE statement and use multiple tables in the DELETE statement and do the task.

-- Delete data from Table1
DELETE Table1
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t2.Col3 IN ('Two-Three','Two-Four')
GO

Now let us select the data from these tables.

-- Check the content of the table
SELECT *
FROM Table1
SELECT *
FROM Table2
GO


As you can see that using JOIN clause in DELETE statement it makes it very easy to update data in one table from another table. You can additionally use MERGE statement to do the same as well, however I personally prefer this method. Let us clean up the clause by dropping the tables which we have created.

DROP TABLE Table1
DROP TABLE Table2
GO

Do let me know if you use any other trick in similar situations. If you do, I would like to learn more about it.

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

• Each session will have 30% theory and 70% demonstrations
• Attendees will have access to scripts presented in the session
• Location to review the videos and free learning material associated with the session
• Practical Performance Tuning Tips to tune your server

Attendees will be able to learn the knowledge which they will be able to apply as soon as they walk out of the session. Here is the details about the session:

Tips and Tricks for Blazing Fast SQL Server Performance

12:00 PM | May 7, 2013 | Main Hall
60 mins | GIDS.NET | Session

Slow Running Queries are the most common problem that developers face while working with SQL Server. While it is easy to blame the SQL Server for unsatisfactory performance, however the issue often persists with the way queries have been written, and how SQL Server has been set up. The session will focus on the ways of identifying problems that slow down SQL Servers, and tricks to fix them. In this session we will cover the common issues related to the Indexes, Statistics, Joins, Conditions as well cover a few of the mistakes developers do when they are rushing to meet deadlines. We will quickly cover SQL Server Worst Practices as well have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. Additionally all attendees of the session will have access to learning material presented in the session.

Performance in 60 Seconds – Database Tricks Every SharePoint Developer MUST Know

14:00 PM| May 7, 2013 | SD Hall
60 mins | GIDS.NET | Session

Data and Database is a very important aspect of SharePoint implementations. SharePoint Developers often come across situations where they face a slow server response, even though their hardware specifications are above par. This session is for all the SharePoint Developers who want their server to perform at blazing fast speed but want to invest very little time to make it happen. We will go over various database tricks which require absolutely no time to master and require practically no SQL coding at all. After attending this session, Developers will only need 60 seconds to improve performance of their database server in their SharePoint implementation. We will have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. Additionally all attendees of the session will have access to learning material presented in the session.

Secrets of SQL Server: Database Worst Practices

15:10 PM| May 7, 2013 | SD Hall
60 mins | GIDS.NET | Session

“Oh my God! What did I do?” Chances are you have heard, or even uttered, this expression. This demo-oriented session will show many examples where database professionals were dumbfounded by their own mistakes, and could even bring back memories of your own early DBA days. The goal of this session is to expose the small details that can be dangerous to the production environment and SQL Server as a whole, as well as talk about worst practices and how to avoid them. Shedding light on some of these perils and the tricks to avoid them may even save your current job. After attending this session, Developers will only need 60 seconds to improve performance of their database server in their SharePoint implementation. We will have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. Additionally all attendees of the session will have access to learning material presented in the session.

If you are going to be at the event, do not hesitate to stop by Pluralsight booth. I will have something unique for you – do not forget to ask for it.

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

• Do you know anything besides SQL Server?
• So how does it feel when the only thing which you know is SQL Server?
• Have you worked in the past with any other programming language?

Actually, I find these questions very interesting as I do work with other technologies and I still do work with many other technologies besides SQL Server. Recently I got the opportunity to work with MySQL and have also built quite a lot of knowledge about the application as well. I found MySQL very interesting and easy to learn. It is the most popular database technology since the year 2008 and used by most numbers of database professionals in the world (including me). The latest version of MySQL is very popular and it is very easy to install on Windows Platform. One just has to download MySQL Installer and execute it. Just click next, next and you are done!

Though, MySQL is very easy I have noticed that not everyone knows it well. The pace of the software industry has changed and people change their expertise quite frequently based on the project they are assigned. There are two kinds of beginners in the industry 1) Those who have very little experience in the software industry and just beginning their career in this field. 2) Those who have plenty of the years of the experience but beginning their career in the particular technology. When you look at both the kinds of the people they both need training but they have different level of understanding of the subject matter.

Looking at this, I decided to build a beginners course on MySQL which addresses both the kinds of the people 1) Beginners with IT field and 2) Beginners with MySQL technology. I have build following beginners course on MySQL Technology. This is part 1 of the course and for anyone who wants to learn MySQL at express speed. The length of the course is 2 hours and 37 minutes, this means anyone can learn the fundaments of the MySQL in a very short period of the time.

You can watch this entire course of Fundamental of MySQL at Pluralsight. To watch the course, you will have to subscribe to Pluralsight library. If you do not have a subscription you can opt for a FREE Trial. If you go to the page and do not see any link enable, please login to Pluralsight and you should be able to watch this course.

Here is the video teaser which I have build for this course. Let me know what you think about it. Here is the content of the course.

• Introduction to MySQL
• Getting Started with MySQL and GUI Tool
• Fundamentals of RDBMS and Database Designs
• Introduction to MySQL Workbench
• Data Retrieval Techniques
• Data Modification Techniques
• Summary and Resources

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

Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, Technology, Video Tagged: MySQL

Let us see the following example. We have two tables Table 1 and Table 2.

-- Create table1
CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table1 (Col1, Col2, Col3)
SELECT 1, 11, 'First'
UNION ALL
SELECT 11, 12, 'Second'
UNION ALL
SELECT 21, 13, 'Third'
UNION ALL
SELECT 31, 14, 'Fourth'
GO
-- Create table2
CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table2 (Col1, Col2, Col3)
SELECT 1, 21, 'Two-One'
UNION ALL
SELECT 11, 22, 'Two-Two'
UNION ALL
SELECT 21, 23, 'Two-Three'
UNION ALL
SELECT 31, 24, 'Two-Four'
GO


Now let us check the content in the table.

SELECT *
FROM Table1
SELECT *
FROM Table2
GO

Now let us see the following image. Our requirement is that we have Table2 which has two rows where Col1 is 21 and 31. We want to update the value from Table2 to Table1 for the rows where Col1 is 21 and 31. Additionally, we want to update the values of Col2 and Col3 only.

When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task.

UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)
GO

Now let us select the data from these tables.

-- Check the content of the table
SELECT *
FROM Table1
SELECT *
FROM Table2
GO


As you can see that using JOIN clause in UPDATE statement it makes it very easy to update data in one table from another table. You can additionally use MERGE statement to do the same as well, however I personally prefer this method. Let us clean up the clause by dropping the tables which we have created.

DROP TABLE Table1
DROP TABLE Table2
GO

Do let me know if you use any other trick in similar situations. If you do, I would like to learn more about it.

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