SQL SERVER – Resolve Cannot Resolve Collation Conflict Error – SQL in Sixty Seconds #047

One of the most common errors database developer’s receives when they start working with database where there are different collation used. Collation is a very important concept but it is often ignored. First use the method displayed in this video to resolve your error and right away put your efforts to understand what collation stands for.

Language is the most important part of communication. We all communicate with each other through language which both persons to understand. If we do not talk in the language which the other person cannot understand, the end result is not fruitful. In a similar way, collation is very important to any database. I have often seen situations when the collation changes from case sensitive and case insensitive turning the query result hey-wire and creates chaos on the server.

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

Related Tips in SQL in Sixty Seconds:

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

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

SQL SERVER – Shortcut to SELECT Single Row from Table – SQL in Sixty Seconds #046 – Video

Earlier I have blogged about the same subject and in very short time I received lots of good comments about this blog post as well lots of email from users who faced issues to make this work. Thought, the instructions are very simple in the blog post, every user read it differently and they have a different interpretation. I finally decided to do convert the same blog post in the video. I hope now it will be much easier to understand it.

If you watch any SQL Server Developer, you will notice one particular task them doing every day frequently. It is they select the row from the table to see what are the various kinds of data it contains. It is very cumbersome for developers to continuously write following code to retrieve a single row to see what the table contains. There is a shortcut how we can make our life easier if we use SQL Server Management Studio Shortcut. In this quick video we will see how we can create a shortcut which will select a single row from a table.

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

Related Tips in SQL in Sixty Seconds:

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

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

SQL SERVER – Watch Four Efficiency Tricks in SQL Server In Sixty Seconds – Subscribe for SQL Learning Videos

SQL in Sixty Seconds has been my favorite thing to do every Wednesday. It is indeed truly said that “A picture is worth a thousand words” – it is equally true that “A video is worth a thousand pictures”. Though recording the video is easy the difficult part is to edit it, process it and take it live. As a SQL developer like you I am good with the SQL but I am doing video editing is not my cup of tea. Anyway the final outcome is so good that I feel like doing these videos.

Need Your Help!

After an year and 2000+ subscriber I have yet to understand what users like the most – some of the videos are really doing well and some of the videos are not doing well. I would like to learn from all of you that what is that you really would love to learn from SQL in Sixty Seconds Video. Here I am listing four of my last month’s video and I would like you to vote and suggest which one of the video is your favorite video.

You can subscribe my YouTube SQL in Sixty Series channel here.

Which one of the following is your favorite SQL in Sixty Video?

Generate Random Values – SQL in Sixty Seconds #042
In this video we discuss how in SQL Server we can generate random values.

Get SQL Server Version and Edition Information – SQL in Sixty Seconds #043
In this video we learn how we can get the version information of SQL Server.

Restore SQL Database using SSMS – SQL in Sixty Seconds #044
In this video we learn how to restore existing SQL Server backup in SSMS

Cycle Clipboard Ring in SSMS – SQL in Sixty Seconds #045
In this video we learn how we can cycle through last 10 copied values and paste them in SSMS.

Now here is the quick poll for all of you.

Which one of the following is your favorite SQL in Sixty Video?

#42 – Generate Random Values
#43 – Get SQL Server Version and Edition Information
#44 – Restore SQL Database using SSMS
#45 – Cycle Clipboard Ring in SSMS

Leave a comment with your answer and explain why do you like the video. Two lucky winners will get a subscription to watch my SQL course on Pluralsight. I have much more comprehensive video published on Pluralsight.

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

SQL SERVER – Cycle Clipboard Ring in SSMS – SQL in Sixty Seconds #045 – Video

Copy and Paste! In other words – CTRL + C and CTRL + V – these two are our famous shortcuts for this new age. Remember copy paste is not a bad thing but plagiarism is for sure. I rely on a lot of Copy Paste when I am doing development. There are so many templates, code or name of the objects (tables, stored procedure) etc which we need when we are doing development. If we keep on typing those names, there are chances of making human error which can lead to further problems.

Now the problem with the copy paste is that we can only paste one item which was the last copied. There are often scenarios when we want to paste more than one value without keep on going back again and again to copy them. I was very much delighted when I discovered the shortcut of the Cycle Clipboard Ring and Paste it.  However this shortcut is not very popular among developers – CTRL+SHIFT+V. I have created a short video which describes the same.

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

Related Tips in SQL in Sixty Seconds:

 

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

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

SQL SERVER – Restore SQL Database using SSMS – SQL in Sixty Seconds #044 – Video

“How do I restore my backup?”

I often receive this question from two kinds of users – i) who are panicking as they are not able to restore database ii) developers who have full backup but not sure how to restore it and use it.

In industry everywhere you go, everybody is taking backup but I noticed hardly anybody try to restore it. When users have not restored the backup for a long time, they have either no expertise to restore or have no idea if their backup have an issue while restoring.

Earlier I have created a SQL in Sixty Seconds Video on How to Take Backup of the database, in this video we learn how we can restore the same backup.

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

Related Tips in SQL in Sixty Seconds:

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

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

SQL SERVER – Get SQL Server Version and Edition Information – SQL in Sixty Seconds #043 – Video

What do consultants do when they come across any new instance of SQL Server? Well, their very first question is what version of SQL Server is it? The reason is simple – SQL Server is a very vast product and each version of the product have new features released and old features deprecated. Many consultant even remembers service pack and features released in it.

Well, there are multiple ways to know the version numbers of the SQL Server. In this sixty second video we will see a neat trick where we will quickly find the version number of SQL Server.

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

Related Tips in SQL in Sixty Seconds:

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

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

SQL SERVER – Generate Random Values – SQL in Sixty Seconds #042 – Video

Though it looks simple it is very difficult to generate random numbers which one can’t guess. There are many different ways to generate random values in SQL Server. I have previously blogged about it over here where I have demonstrated five different methods to generate random values in SQL Server.

SQL SERVER – Random Number Generator Script – SQL Query

In this sixty second video we will see a neat trick where we will generate Random value between specified two numbers.

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

Related Tips in SQL in Sixty Seconds:

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

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

SQL SERVER – Autocomplete and Code Formatting Tool – SQL in Sixty Seconds #041 – Video

I love to write code, and I love well-written code.  When I am working with clients, and I find people whose code have not been written properly, I feel a little uncomfortable.  It is difficult to deal with code that is in the wrong case, with no line breaks, no white spaces, improper indents, and no text wrapping.  The worst thing to encounter is code that goes all the way to the right side, and you have to scroll a million times because there are no breaks or indents.

Developers are often very busy in writing code and building application rapidly while racing against strict deadline. Developers want a tool which can help them aid them to write faster code along with a tool which auto format the code, so when they visit the code again they can read it. I came across a similar tool which can help achieve both the goals.

You can download the Devart SQL Complete demonstrated in this video for free.

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

Let me know what you think of this efficiency tool.

Related Tips in SQL in Sixty Seconds:

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

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

SQL SERVER – TRIM Function to Remove Leading and Trailing Spaces of String – SQL in Sixty Seconds #040 – Video

Trim is one of the most frequently used operation over String data types. A developer often come across a scenario where they have the string with leading and trailing spaces around string. If your business logic suggests that the logs around the spaces are not useful they should be trimmed. However, in SQL Server there is no TRIM function. When a TRIM function is used it will throw an error.

For example, here is the script when executed it will throw an error.

-- The following will throw an error
DECLARE @String1 NVARCHAR(MAX)
SET @String1 = ' String '
SELECT TRIM(@String1) TrimmedValue
GO

The above script will return following error:

Msg 195, Level 15, State 10, Line 4
‘TRIM’ is not a recognized built-in function name.

Let us not everything why this simple function is not implemented but try to resolve how we can achieve the result of the same function. SQL Server has two functions which when nested can give us the same result as a TRIM function.

1) RTRIM – Removes the Spaces on the right side (or leading spaces) of the string

2) LTRIM – Removes the Spaces on the left side (or trailing spaces) of the string

We can combine them as following and it will not throw an error.

-- The following will work
DECLARE @String1 NVARCHAR(MAX)
SET @String1 = ' String '
SELECT @String1 OriginalString, RTRIM(LTRIM(@String1)) TrimmedValue
GO

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

Additionally, if you want you can create a user defined function which is using RTRIM and LTRIM and can use the function when trim functionality is required.

-- Create Function
CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN
LTRIM(RTRIM(@string))
END
GO
SELECT dbo.TRIM(' String ')
GO

Now when we are on the topic of the TRIM function, let me remind you one very important impact of this function if used in the WHERE clause. If any function is used in the WHERE clause, it will negatively impact on the performance of the query. SQL Server has to process the function on whole column leading Table Scan or Index Scan instead of Index Seek. This will increase the resource utilization and lead to poor performance. However, using this function in SELECT statement does not degrade performance much. In simple words – please be mindful of using any functions. Use the functions when you absolutely need it or enforcing business needs.

Related Tips in SQL in Sixty Seconds:

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

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

SQL SERVER – Send Email From SQL Server – Configure Database Mail – SQL in Sixty Seconds #039 – Video

Let me start this blog post with negative note: SQL Server is not mass mailing software. If you are thinking of sending emails using SQL Server instead of your mail server – I suggest you stop doing that NOW! Whenever, I see any application using SQL Server as a mail server – I always vote against it. Well, if this is so bad, then why is it possible to send email through SQL Server. The reason is simple – there are many SQL Server Administrative scenarios where we need SQL Server to send emails, e.g. Maintenance task status, job failure messages, operators alerts etc. I suggest to use Database mail option during this situation.

In this SQL in Sixty Seconds I explain how one can configure database mail quickly and send emails to users. If you can’t watch this video – I suggest you to read this blog post which describes the same using images – Configure Database Mail – Send Email From SQL Database.

Let us see how to take backup in this SQL in Sixty Seconds:

Related Tips in SQL in Sixty Seconds:

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

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