SQL Server and MS BI With a Geek Touch

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…

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…

What are the advantages and disadvantages of Surrogate Key ?

Pros:

1. Business Logic is not in the keys.
2. Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).
3. Joins are very fast.
4. No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached – very scalable.

Cons:

1. An additional index is needed.  In SQL Server, the PK constraint will always creates a unique index, in Oracle, if an index already exists, PK creation will use that index for uniqueness enforcement (not a con in Oracle).
2. Cannot be used as a search key.
3. If it is database controlled, for products that support multiple databases, different implementations are needed, example: identity in SS2k, before triggers and sequences in Oracle, identity/sequence in DB2 UDB.
4. Always requires a join when browsing the child table(s).

  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…

DBCC is one of the most commonly used commands by DBAs across the globe. Today I will introduce a simple way to reindex a specific or all indexes in a table using DBCC.

The command used for this is

DBCC DBREINDEX

General Sytax

DBCC DBREINDEX

(

table_name

[ , index_name [ , fillfactor ] ]

)

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…

Today, I came across a requirement, where the source excel sheet had data starting from row number 6 and column B. This was due to formating in the excel, which was also used as a report by many stake holders.

Although at first it might seem like there are no options in excel source to skip certain rows from the beginning of the sheet, it is not true. Fortunately, excel source provides the feature to select only required records from any sheet.  the OpenRowset property on the Excel Datasource component  allows you to specify the range to be considered by the datasource.

Read more…

The GUID (uniqueidentifier) data type is one of the most interesting data types available. uniqueidentifier column holds a GUI, a string of 32 random hexa-decimal characters (0-9, A-F) in blocks separated by hyphens.  A GUID will look like

5166AA1D-A18F-4D3C-A7CA-3F3CBF8CEE2B 

EDCF4B8A-4154-449B-972A-2224F8450BF1

CEA4C383-51FD-4B99-A67D-F5C34D5F1013     

5D9FDC84-632D-4450-B183-04CBBEE87DC6

GUIDs have both Pros and cons. GUIDs are pretty big in size and require 16 bytes of storage. This usually makes it slow during joins of large dataset.

Now you might think, What is so great about GUID? Although it occupies lot of space there are several advantages. First, a GUID will be a completely unique value and no other GUID in the world will share the same string. This means that you can use GUIDs as PKs on your tables if you will be moving data between databases. This technique prevents duplicate PKs when you actually copy data. GUIDs are widely used in Microsoft products to identify interfaces, replica sets, records, and other objects.

In Sql Server, you can create a new GUID by using the function NEWID(). This function is a non-deterministic function. It generates a new value every time you execute function .

Select NEWID()

 the datatype is named in SQL as “uniqueidentifier”