SQL Server and MS BI With a Geek Touch

Rank() function is used to give ranking to the records chosen through a select clause.  Rank function is very commonly used in the industry for many purposes.

The general syntax of rank function is

RANK ( )  OVER ( [  partition by < column_list > ]  order by  <column_list> )

The PARTITION BY clause divides the result set produced by the FROM clause into separate partitions. Rank is applied to each of these partion.

The ORDER BY  clause determines the order in which the RANK values are applied to the records in a partition.

Read more…

The Soundex code was developed to help negate the effects of all the spelling variations that can occur for similar sounding names ( Smith, Smithe, Smythe, etc.).  This way, users can index records based on a Soundex code based on the sound  and “not” by how it is spelled.

The Soundex system is not infallible – the surnames Gough and Goff sound the same (Goff), but a different code is formed for these two names.  Soundex can however be used to a great extent to get a desired result ( atleast closer to a desired result) This is a commonly used algorithm by many researchers.

In SQL a string function is available for this and is usually comes handy.  The function is SOUNDEX().

It accepts a string (name ) as parameter and returns a 4 character code. This code is known as the soundex code of the name/string passed as parameter.

example:

Select Soundex(‘Smith’)   O/P: S580

Select Soundex(‘Smithe’)   O/P: S580

SELECT  SOUNDEX(‘GREEN’)   O/P: G652

Another use of this is against an user entered field where you need to look for a specific word, but the words might be mispelled( although it may not yield 100% result).

RAISERROR statement is used to return error messages to the business applications that executes SQL statements. The usual errors returned by the SQL server may not make much sense to the business applications users hence we overwrite it by using RAISERROR to display meaningful messages. The statement uses same format as a system error or warning messages generated by SQL server.

You can return a user-defined error message by using RAISERROR.  The general syntax is as below

RAISERROR ( { msg_id | msg_str | @local_variable }

    { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

ExamplesRAISERROR (‘Error raised in TRY block.’, — Message text.

Read more…

A TRY ….CATCH construct includes a TRY block followed by a CATCH block. The TRY block contains a set of transact SQL statements. If an error occurs in any statements of the TRY block, the control is passed to the CATCH block, which contains another set of SQL statements.

The CATCH block contains SQL statements that executes only during errors in TRY block. The general syntax of the TRY…CATCH block is as shown below

BEGIN TRY

                <SQL Statements…..>

                <SQL Statements…..>

END TRY

BEGIN CATCH

                <SQL Statements…..>

                <SQL Statements…..>

END CATCH

  Read more…

You can use the string functions to manipulate the string values in the result set. String functions are used with both char and nchar data types. The list below contains the set of string functions available in SQL Server 2005.

Read more…

You can use the datetime functions in SQL Server to manipulate the datetime values. You can either use arithmetic operations on datetime or parse datetime values.  The following list contains the datetimefunctions in SQL Server with examples

SQL server provides the following list of abbreviations for datepart functions

Read more…

Encrypting a stored procedure will hide the definition of the procedure from users and it definition will not be displayed in the activity monitor.

The procedures can be encrypted by using “WITH ENCRYPTION” clause after the create statement.

ex: Read more…

Although these functions are not commonly used, these are really powerful in searching a pattern and returning the location of text/ string/ pattern. By understanding the functionality of both the string functions, we will be able to understand the difference as well.

PATINDEX()

The function searches for a pattern in a string / expression and returns the starting position of the first occurrence of the pattern. It works similar to the LIKE operator in any query.

Read more…

NULLIF( ) : Returns a null value if the two expressions specified in the function are equal.

The syntax used is

NULLIF ( expression1 ,expression2 )

If expression1 = expression2, then the function will return NULL.

ISNULL( ): The function replaces a null expressions with a specified value.

The syntax used is

Read more…