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)

About these ads

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)

SQL SERVER – Wrap SQL Code in SSMS – SQL in Sixty Seconds #038 – Video

Every developer has a different habit. Some like to format the functions in upper case and some wants it in lower case. I often see developers listing the columns in SELECT clause in a different way. I have my own preference but I do respect the other developer’s preference as well. I do not advise to change anybodies habit but there is one thing which I strongly prefer to do on the client side when I am editing code.

WRAP THE CODE!

Well, it is indeed very difficult to read the code when users have to horizontally scroll the code. Now when I am working on the file originally created by another developer, I do not like to change their formatting. I have a very wide screen on my personal desktop computer but when I see the same on my 12″ laptop, I see the horizontal scrolling. There can’t be a single solution in the case of the wrapping the code, which fits all the screens.

Fortunately, there is a solution which will fit all the screens.

SQL Server Management Studio has a setting which will wrap the code displayed on the screen. Even if you resize the screen the code wraps. It will be very difficult to explain that in words – I suggest you watch following SQL in Sixty Seconds Video on this subject.

You can access the settings from SSMS >> Tools >> Options >> Text Editor >> Transact-SQL. It will show the following screen.

Let us see the same concept 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)