SQL Server Journey with SQL Authority

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:

• Get Server Version and Additional Info
• Get All the Information of Database using sys.databases
• Script to Find SQL Server on Network
• Version Information and Additional Information – Extended Stored Procedure xp_msver

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

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

Read Full Post »

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:

• SQL SERVER – Random Number Generator Script – SQL Query
• Generate A Single Random Number for Range of Rows of Any Table – Very interesting Question from Reader
• Retrieving Random Rows from Table Using NEWID()

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

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

Read Full Post »

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:

• Auto Complete and Format T-SQL Code – Devart SQL Complete

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

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

Read Full Post »

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:

• SQL SERVER – TRIM() Function – UDF TRIM()
• TRIM() Function – User Defined Function
• Enhenced TRIM() Function – Remove Trailing Spaces, Leading Spaces, White Space, Tabs, Carriage Returns, Line Feeds

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

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

Read Full Post »