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)

About these ads

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 – A List of Various SQL Server RTM and Service Pack Number

A common question I receive is that how do user know which version user is using and what is the latest version available for the product. Here is something at this beginning of the year, check with your production server. See if you are using the latest version of the product or if you need to upgrade. If you need to upgrade you might want to plan it with your entire team. As I get this question often here is something I have already built and kept it with me. As soon as I receive an email with the question about the version, I right away send it to them.

Now onwards as I have blogged about this subject, in future question, I will like to this blog post. Please feel free to add more details if I have missed any.

Here are two of the methods, you can use to identify your SQL Server version:

Open SSMS and in a New Query Window run following T-SQL code.

1) SELECT@@version

2) SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The above query will return the version details of installed SQL Server.

Here is the list of the all the available RTM and Service Pack Numbers.

SQL Server 2012 Versions

Release Product Version
SQL Server 2012 Service Pack 1 11.00.3000.00
SQL Server 2012 RTM 11.00.2100.60

SQL Server 2008 R2 Versions

Release Product Version
SQL Server 2008 R2 Service Pack 1 10.50.2500.0
SQL Server 2008 R2 RTM 10.50.1600.1

SQL Server 2008 Versions

Release Product Version
SQL Server 2008 Service Pack 3 10.00.5500.00
SQL Server 2008 Service Pack 2 10.00.4000.00
SQL Server 2008 Service Pack 1 10.00.2531.00
SQL Server 2008 RTM 10.00.1600.22

SQL Server 2005 Versions

Release Product version
SQL Server 2005 Service Pack 4 9.00.5000.00
SQL Server 2005 Service Pack 3 9.00.4035
SQL Server 2005 Service Pack 2 9.00.3042
SQL Server 2005 Service Pack 1 9.00.2047
SQL Server 2005 RTM 9.00.1399

SQL Server 2000 Versions

Release Product version
SQL Server 2000 Service Pack 4 8.00.2039
SQL Server 2000 Service Pack 3 8.00.760
SQL Server 2000 Service Pack 3 8.00.760
SQL Server 2000 Service Pack 2 8.00.534
SQL Server 2000 Service Pack 1 8.00.384
SQL Server 2000 RTM 8.00.194

This method will help us find edition of the relational database. In future blog posts we will see the methods to identify the version of the Business Intelligence Services.

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 – How to Hide Yourself from SQL Server? – Guest Post by Balmukund Lakhani

Balmukund Lakhani (Blog | Twitter | Site) is currently working as Technical Lead in SQL Support team with Microsoft India GTSC. In past 7+ years with Microsoft he was also a part of the Premier Field Engineering Team for 18 months. During that time he was a part of rapid on-site support (ROSS) team. Prior to joining Microsoft in 2005, he worked as SQL developer, SQL DBA and also got a chance to wear his other hat as an ERP Consultant.

Balmukund is a great friend and one of the finest SQL Server Expert I know. When I requested him for Guest Post, he has indeed come up with a fantastic blog post and very interesting title of the blog post. I am sure many wants to learn the trick to hide from SQL Server, so here it goes in his own words.

Important: It is Balmukund’s Birthday Today – please wish him all the best for exciting SQL filled New Year! Wish him on Twitter or leave a comment on his Blog.


I am sure that all of us have used SQL Server Management Studio countless times. It’s the common tool used by all DBA and developers. Most of the time we give server name, user name and password (if needed) and hit connect. Right? Have you ever clicked on “Options >>” button and made some changes in connection properties? Pinal wrote about color coding of servers, using “use custom color” option, here and posted a video here which is available in connection properties tab.

In this blog, we are going to talk about the third tab “Additional Connections Parameters”. When we want to test connecting string and those options are not available in earlier two tabs, then we can provide them in this tab (available in SQL 2008 onwards). The parameters should be separated by semicolons (;). There are a few parameters in connection string which can be spoofed. Let us start from hiding from SQL Server.

Attempt 1: Hide from SQL Server

Enter following parameters in the Additional Connection Parameter and click on Connect.
Application Name=Pinal-SSMS;Workstation ID=Pinal-PC

Once we connect and try to check our connection properties, the result of this would be as follows:

SELECT HOST_NAME, program_name
FROM sys.dm_exec_sessions
WHERE session_id = @@spid

If any DBA sees this, they would think that Pinal is working but it’s Balmukund in reality. And, I have hidden myself.

Attempt 2: Hide SQL Server from User (Deceive User)

Now we have hidden ourselves from SQL Server – let us do something crazy. Let us hide the SQL Server from the user. This can be extremely confusing so I suggest to use it carefully or never use it at all. If I can, I would have put red flashing lights around previous statement.

Now let us change the Data Source property in Additional Connection Parameters place. Let us confuse user that they are connecting to instance SQLServer2000 even though they are really connecting to instance BIG. My localhost is named as BIG and in following connection I am providing a incorrect server name Big\SQLServer2000 in the Server Name. In normal case it will give us the error. However, in this case we will provide an incorrect name in the Login >> ServerName connection.

We will right away click on “Additional Connection Parameters” and will enter following text in the string Parameters:  Data Source=localhost

Now let us hit connect and you will see following situation.

Well, as warned earlier, SSMS would override the value and hence we are connected to a server which is in additional connection parameters. Everywhere in SSMS, we would see instance SQLServer2000 which is in login tab, but actual server is the SQL Server 2012. Now imagine a drop database executed in SSMS, thinking that this was ainstance SQLServer2000 but they end up on instance BIG. It is quite possible some of the commands will now return error if they are deprecated in instance SQLServer2000. This will be fun prank – which you should not play with anyone.

Moral of the story: Be careful while using this tab. Use this only for those settings which are not available in management studio by default.

Hope you have learned something new today.


Thanks Balmukund Twitter for this wonderful guest post.

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)