Category Archives: SQL Tips and Tricks

by

SQLAuthority News – Top 5 Latest Microsoft Certifications of 2013 – Guest Post

With the IT job market getting more and more competent by the day, certifications are a must for anyone who wishes to get a strong foothold in the industry. Microsoft community comes up with regular updates and enhancements in its existing products to keep up with the rapidly evolving requirements of the ICT industry. We bring you a list of five latest Microsoft certifications that you must consider acquiring this year.

MCSE: SharePoint

Learn all about Windows Server 2012 and Microsoft SharePoint 2013, which brings an advanced set of features to the fore in this latest version. It introduces new capabilities for business intelligence, social media, branding, search, identity management, mobile device among other features. Enjoy a great user experience with sharing and collaboration in community forum, within a pixel-perfect SharePoint website. Data connectivity and business intelligence tools allow users to process and access data, analyze reports, share and collaborate with each other more conveniently.

Microsoft Specialist: Microsoft Project 2013

The only project management system that works seamlessly with other applications and cloud solutions of Microsoft, MS Project 2013 offers more than what meets the eye.  It provides for easier management and monitoring of projects so that users can ensure timely delivery while improving the productivity significantly. So keep all your projects on track and collaborate with your team like never before with this enhanced release! This one’s a must for all project managers.

MCSE Messaging

Another one of Microsoft gems is its messaging environment which has also launched the latest release Microsoft Exchange Server 2013. Messaging administrators can take up this training and validate their expertise in Unified Messaging, Exchange Online, PowerShell and Virtualization strategies, through MCSE Messaging certification in Exchange Server. If you wish to enhance productivity and data security of your organization while being flexible and extremely efficient, this is the right certification for you.

MCSE Communication

An enterprise can function optimally on the strength of its information flow and communication systems. With Lync Server 2013, you can introduce a whole new world of unified communications which consists of audio/video conferencing, dial-in, Persistent Chat, instant chat, and EDGE services in your organization. Utilize IT to serve and support business objectives by mastering this UC technology with this latest MCSE Communication course on using Microsoft Lync Server 2013.

MCSE: SQL Server 2012 BI Platform

The decision making process is largely influenced by underlying enterprise information used by the management for business intelligence. Therefore, a robust business intelligence platform that anchors enterprise IT and transform it to operational efficiencies is the need of the hour. SQL Server 2012 BI Platform certification helps professionals implement, manage and maintain a BI database infrastructure effectively. IT professionals with BI skills are highly sought after these days.

MCSD: Windows Store Apps

A Microsoft Certified Solutions Developer certification in Windows Store Apps validates your potential in designing interactive apps. Learn The Essentials of Developing Windows Store Apps using HTML5 and JavaScript and establish yourself as an ace developer capable of creating fast and fluid Metro style apps for Windows 8 that are accessible on a variety of devices. You can also go ahead and Learn Essentials of Developing Windows Store Apps using C# mode if you’re already familiar and working with C# programming language. Hence the developers are free to choose their own favorite development stream which opens doors for them to get ready for the latest and exciting application development platform called Windows store apps. Software developers with these skills are in great demand in the industry today.

In order to continue being competitive in your respective fields, it is imperative that IT personnel update their knowledge on a regular basis. Certifications are a means to achieve this goal. Not considered to be an optional pre-requisite anymore, major IT certifications such as these are now essential to stay afloat in a cut-throat industry where technologies change on a daily basis.

This blog is written by Aruneet Anand of Koenig Solutions. Koenig Solutions does training for all of the above courses. For more information, visit the website.

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

About these ads
by

SQL SERVER – SQL in Sixty Seconds – Last Three Episodes – Need Your Opinion

I have been blogging for almost 7 years and building video content for around 2 years. After spending so much time on blogging and creating video, I have got the quite a good idea what people would like to read and what people like to watch. However, there is one thing, which I am constantly struggling after almost a year and I would like to get your opinion about it. Though, this may look very simple to you but it is very crucial to me and I would like to know your opinion about it.

I have been building video almost every week for my SQL in Sixty Seconds series and it has been quite popular. So far on my YouTube Channel there are over 2600 subscribers and over 250K views. Here is my problem – there are about 50+ videos on SQL in Sixty Seconds Series but the not every video is popular. There are a few videos which are extremely popular and there are videos which are absolutely struggling to get even single view. I have yet not figured out what people would love to watch on this channel. I noticed lots of people watching various videos but hardly anyone leaving comments or suggestions. At the end of the blog posts associated with the SQL in Sixty Seconds, I always ask which video people would love to watch, but I get a very low response over there too.

What I wonder is that why such a low engagement of viewers/readers on the video blog posts where as the channel is success and lots of people are watching the video? What do you think I should change in my video to increase the engagement?

Here are my last three videos from SQL in Sixty Seconds channel and I would like to know your feedback.

Remove Cached Login from SSMS Connect Dialog – SQL in Sixty Seconds #049

RESEED Identity Column in Database Table – SQL in Sixty Seconds #051

Puzzle SET ANSI_NULLS and Resultset – SQL in Sixty Seconds #052

 The feedback which I will like the most will for sure get a special surprise gift for me.

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

by

SQL SERVER – Script to Update a Specific Column in Entire Database

Last week, I have received a very interesting question and I find in email and I really liked the question as I had to play around with SQL Script for a while to come up with the answer he was looking for. Please read the question and I believe that all of us face this kind of situation.

“Pinal,

In our database we have recently introduced ModifiedDate column in all of the tables. Now onwards any update happens in the row, we are updating current date and time to that field.

Now here is the issue, when we added that field we did not update it with a default value because we were not sure when we will go live with the system so we let it be NULL. Now modification to the application went live yesterday and we are now updating this field.

Here is where I need your help. We need to update all the tables in our database where we have column created ModifiedDate and now want to update with current datetime. As our system is already live since yesterday there are several thousands of the rows which are already updated with real world value so we do not want to update those values. Essentially, in our entire database where ever there is a ModifiedDate column and if it is NULL we want to update that with current date time? 

Do you have a script for it?”

Honestly I did not have such a script. This is very specific required but I was able to come up with two different methods how he can use this method.

Method 1 : Using INFORMATION_SCHEMA

SELECT 'UPDATE ' + T.TABLE_SCHEMA + '.' + T.TABLE_NAME + ' SET ModifiedDate = GETDATE() WHERE ModifiedDate IS NULL;'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
AND c.COLUMN_NAME ='ModifiedDate'
WHERE T.TABLE_TYPE = 'BASE TABLE'
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME;

Method 2: Using DMV

SELECT 'UPDATE ' + SCHEMA_NAME(t.schema_id) + '.' + t.name + ' SET ModifiedDate = GETDATE() WHERE ModifiedDate IS NULL;'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name ='ModifiedDate'
ORDER BY SCHEMA_NAME(t.schema_id), t.name;

Above scripts will create an UPDATE script which will do the task which is asked.

We can pretty much the update script to any other SELECT statement and retrieve any other data as well.

Click to Download Scripts

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

by

SQLAuthority News – Don’t Be Afraid To Fool The World – Video by John Sonmez

Sometime some words and statements grabs your attention and it is hard to stop thinking about that after a while. Something similar happened a few days ago when I read the twitter statement of my friend and Pluralsight author John Sonmez. He twitted few days ago very interesting statement.

“I don’t know a single successful person, who doesn’t deep down think that have the world fooled. #fooltheworld” by John Sonmez.

When I read it, I was extremely intrigued by this statement. I read it many times, I shared with my family and I just could not stop interpreting this statement. It was indeed fun to read it again and again and there are so many different meanings one can take away from the statement. I know John very well, he is a  wonderful person and have very positive energy for the life. I just had to request him to build a video around it. Right after 5 days of my request, John created a wonderful video around this subject. I watched it multiple times as it was a wonderful video.

I am not going to write about what was in the video much as I suggest you to watch the video itself.

Here is one of the personal stories I want to share which is absolutely relevant to this video. I think my story 100% resonant the story of John.

A Real Story from My Past

Three years ago, I submitted a session in one of the SharePoint conference as a SQL Server session. My session was accepted and I prepared it very well. I put more than 2 month’s time to prepare for the session and I was very excited to present the session. I reached to the event place traveling thousands of the miles and I was very much excited to present the session. However, there was a little mixed up in the session. There were multiple session which were similar to my session title. One of the other speakers also had proposed a database related session and was selected. When the material went to print the printing team got confused and by mistake swapped the sessions. The other speaker got Performance with SQL Server session and I had received Performance with SharePoint session. IT was indeed a big mixed up but now that is how it was in the event guide and it was marketed the same way everything in the event.

A Big Mix Up

I had to talk with the event organizer and we come to the conclusion that we all had good intention but things just got mixed up and now was the time when “The show must go on“. I had a great amount of hesitation to go and present the session as I had personally never worked with Sharepoint so close in my life and my session abstracted talked about SharePoint tricks in depth. Two hours before the session I took the help of one of my friend and installed the SharePoint on my box. He showed me a few things here and there but it was never a good enough time to learn everything which I wanted to learn.

The Moments of Confidence

I was very scared and nervous to go on the stage as a SharePoint was not something I felt comfortable. However, I decided to go on stage with confidence as a SharePoint expert. Though I did not know SharePoint at the best, I had confidence that whatever I know is correct and I will not misguide people. I had no intention to fool people but I had no intention to accept that I am a fool and you all wasted your time and money to dedicate your time to attend my session. I decided to be honest but at the same time decided to take the session beyond my expertise. The sixty minutes of the session went very fine and I was able to manage all the difficult question at a satisfactory level. When the session was over my feeling was that I would have not presented or talked any different if I had more knowledge of the SharePoint at that time. I think it was one of my best sessions and it was reflected in the session feedback as well. I was the best speaker across all the track and my session had highest ranking. I was delighted and I learned a very valuable lesson.

I must go beyond my limits and knowledge. I must aim higher and work harder. I should not lie but I should have confidence that I have a good heart and I put 100% in my efforts. 

Lessions Learned

Since this incident I have learned a lot about SharePoint and I am now a regular speaker at various SharePoint conferences along with SQL Server sessions. I am motivated and I am not afraid. I know people have lots of expectation from me but I have learned not to judge myself before I do my best. I leave the judgement of my efforts to my audience. I do not take the burden of the feedback on me, even though I know my audience have expected from me. I know what I know and I put my best. I must go out, if I fail, I learn from my mistake but I must keep my progress trajectory very high.

As John said in the video, sometime success is not something we can achieve 100% but we can keep on going near to it. As long as we do not lose our focus from our goal and do not deviate from our progress path, we are doing things right.

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

by

SQLAuthority News – Advantages of Distance Learning

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

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

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

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

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

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

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

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

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

by

SQL SERVER – Weekly Series – Memory Lane – #031

Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.

2007

Find Table without Clustered Index – Find Table with no Primary Key
Clustered index is very important concept for any table. They impact the performance very heavily. Here is a quick script to find tables without a clustered index.

Replace TEXT with VARCHAR(MAX) – Stop using TEXT, NTEXT, IMAGE Data Types
Question: “Is VARCHAR (MAX) big enough to store the TEXT field?”
Answer: “Yes, VARCHAR(MAX) is big enough to accommodate TEXT field. TEXT, NTEXT and IMAGE data types of SQL Server 2000 will be deprecated in a future version of SQL Server, SQL Server 2005 provides backward compatibility to data types but it is recommended to use new data types which are VARHCAR (MAX), NVARCHAR (MAX) and VARBINARY (MAX).”

Limiting Result Sets by Using TABLESAMPLE – Examples
Introduced in SQL Server 2005, TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.

User Defined Functions (UDF) Limitations
UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF can not do and why Stored Procedure are considered as more flexible then UDFs. Stored Procedure are more flexibility then User Defined Functions(UDF). However, this blog post is a good read to know what are the limitations of UDF.

Change Database Compatible Level – Backward Compatibility
For a long time SQL Server stayed on the compatibility level of 80 which is of SQL Server 2000. However, as soon as SQL Server 2005 introduced the issue of compatibility was quite a major issue. Since that time MS has been releasing the versions at every 2-3 years, changing compatibility is a ever popular topic. In this blog post, we learn how we can do the same using T-SQL. We can also do the same using SSMS and here is the blog post for the same: Change Database Compatible Level – Backward Compatibility – Part 2 – Management Studio.

Constraint on VARCHAR(MAX) Field To Limit It Certain Length
How can I limit the VARCHAR(MAX) field with maximum length of 12500 characters only. His Question was valid as our application was allowed 12500 characters. First of all – this requirement is bit strange but if someone wants to do the same, they can do it as described in this blog post.

2008

UNPIVOT Table Example
Understanding UNPIVOT can be very complicated at times. In this blog post, I have attempted to explain the same concept in very simple words.

Create Default Constraint Over Table Column
A simple straight to script blog post – I still use this blog quite many times for my own reference.

UDF – Get the Day of the Week Function
It took me 4 iteration to find this very simple function which can immediately get the day of the week in a single line.

2009

Find Hostname and Current Logged In User Name
There are two tricks listed in this blog post where users can find out the hostname and current logged user name immediately and very easily.

Interesting Observation of Logon Trigger On All Servers
When I was doing a project, I made an interesting observation of executing a logon trigger multiple times. It was absolutely unexpected for me! As I was logging only once, naturally, I was expecting the entry only once. However, it did it multiple times on different threads – indeed an eccentric phenomenon at first sight!

Difference Between Candidate Keys and Primary Key
One needs to be very careful in selecting the Primary Key as an incorrect selection can adversely impact the database architect and future normalization. For a Candidate Key to qualify as a Primary Key, it should be Non-NULL and unique in any domain. I have observed quite often that Primary Keys are seldom changed. I would like to have your feedback on not changing a Primary Key.

Create Multiple Filegroup For Single Database
Why should one create multiple file group for any database and what are the advantages of the same. In this blog post, I explain the same in detail.

List All Objects Created on All Filegroups in Database
In this blog post we discuss the essential question – “How can I find which object belongs to which filegroup. Is there any way to know this?”

2010

DATE and TIME in SQL Server 2008
When DATE is converted to DATETIME it adds the of midnight. When TIME is converted to DATETIME it adds the date of 1900 and it is something one wants to consider if you are going to run scripts from SQL Server 2008 to earlier version with CONVERT.

Disabled Index and Update Statistics
If you do not need a nonclustered index, I suggest you to drop it as keeping them disabled is an overhead on your system. This is because every time the statistics are updated for system all the statistics for disabled indexes are also updated.

Precision of SMALLDATETIME – A 1 Minute Precision
The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero.

2011

Getting Columns Headers without Result Data – SET FMTONLY ON
SET FMTONLY ON returns only metadata to the client. It can be used to test the format of the response without actually running the query. When this setting is ON the resultset only have headers of the results but no data.

Copy Database from Instance to Another Instance – Copy Paste in SQL Server
SQL Server has a feature which copy database from one database to another database and it can be automated as well using SSIS. Make sure you have SQL Server Agent Turned on as this feature will create a job.

Puzzle – SELECT * vs SELECT COUNT(*)
If you have ever wondered SELECT * gives error when executed alone but SELECT COUNT(*) does not. Why? in that case, you should read this blog post.

Creating All New Database with Full Recovery Model
This blog post is very based on very interesting story where the user wants to do something by default for every single new database created. Model database is a secret weapon which should be used very carefully and with proper evalution. If used carefully this can be a very much beneficiary when we need a newly created database behave in certain fashion.

2012

In year 2012 I had two interesting series ran on the blog. If there is no fun in learning, the learning becomes a burden. For the same reason, I had decided to build a three part quiz around SEQUENCE. The quiz was to identify the next value of the sequence. I encourage all of you to take part in this fun quiz.

Can anyone remember their final day of schooling?  This is probably a silly question because – of course you can!  Many people mark this as the most exciting, happiest day of their life.  It marks the end of testing, the end of following rules set by teachers, and the beginning of finally being able to earn money and work in your chosen field.

Read five part series on developer training subject

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

by

SQLAuthority News – New Theme of SQLAuthority and Video Courses

I have been blogging for almost 7 years now. Recently I reached a very important milestone for my blog. I completed over 2500 blog posts as well have been receiving consistently over 2 Million Views every month. It has been a fantastic ride and I am planning to continue contributing to blog and community. However, just like everything else the blog was in need of the fresh look for a long time.

I recently updated entire theme of SQLAuthority.com and have given clear fresh look to it. Here are few of the details of enhancements:

  • Changed width from 600 px to 840 px
  • Changed theme which is responsive and mobile friendly
  • Increased font size and changed background color
  • Increased space between lines as well cleaned up overall theme
  • Cleaned up right side banner
  • Bigger images for community initiatives
  • Added new page of Video Courses
  • Changed the header of the blog with new heading

Well, if you are reading this blog post in email, you will be not able to see any changes over here. I suggest you check the
http://blog.sqlauthority.com
 in a regular browser.

Here is another news! 

I enjoy writing on blog and I will continue to write the blog as long as I can write. However, earlier this year, I have done a year long experiment of video blogging. I blogged about SQL Server in Sixty Seconds. The series I have put on YouTube and it has been extremely successful series. You can see SQL in Sixty Seconds Series over here. The series is extremely successful as lots of people are asking me to produce more video content. I promise that I will continue to do build the SQL in Sixty Seconds episodes.

Till today I have so far created six Pluralsight Video. I am extremely happy that I have built online courses. Blog has its own place and and video learning has its own place. Technology is such that it is very difficult to convey sometimes in text mode. Building video is fun and it is scalable as well.

I plan to build a few more interesting video courses in near future. Here are few of the  existing courses which I have build for Pluralsight.

Video Courses

SQL Server Performance: Introduction to Query Tuning

Click to View Course

SQL Server Performance: Indexing Basics

Click to View Course

SQL Server Questions and Answers

Click to View Course

MySQL Fundamentals

Click to View Course

Building a Successful Blog

Click to View Course

Introduction to ColdFusion

Click to View Course

You will have to login into Pluralsight portal to watch the courses. You can watch the courses for free by signing up for a free trial. Do let me know which one of the above is your favorite course.

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