SQL Server and MS BI With a Geek Touch

 By using “Merge”, you can perform insert, delete and update on a table in a single SQL statement. This helps in synchronizing the tables as well.

However while using merge there are several points that you need to keep in mind. Few

• In a “Merge” statement, a “When Matched” clause with a search condition cannot appear after a ‘When Matched’ clause with no search condition.
• A “Merge” statement must be terminated by a semi-colon (;).
• An action of type ‘DELETE’ is not allowed in the ‘WHEN NOT MATCHED’ clause of a MERGE statement.
• At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.
• Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints.

Few examples of merge are as given below.

Read more…

I often create batch files to execute sql scripts ( specially SPs) and then schedule it using SQL agent job/Task Scheduler. In this post , I will show you how to create a batch file with sql commands.

First of all, The command that is used to execute SQL queries from commnd prompt is “SQLCMD“. The general syntax of this command is available in the link. I will go with a simple one as below

sqlcmd –E –S Servername –Q Query -o “Output Path”

C:\Users\geekEpisodes>  sqlcmd -E -S “Local-SRV01″ -Q “Select top 10 * from MyTestDatabase..[ForecastHistory]” -o “C:\New Folder\Output.txt”

All the commands that you execute in the command prompt can be executed from batch/command files. to create a batch file with SQL commands follow the steps below

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).

It is often necessary to change the database to single user mode, especially if you are a DBA. A simple example would be to change the collation settings or any DB settings. The single user will allow only one  user ( usually DBA) to access the database. Hence it will be easy to make changes without the worry of deadlocks or any other type of contention for DB and also without affecting the users.

It is very easy to change the database to Single user mode; in fact, it is just an execution of the script away. Use the script below to change the mode.

ALTER  DATABASE <<Database Name>>  SET SINGLE_USER  WITH NO_WAIT

Read more…

What is the difference between a “where” clause and a “having” clause?

 ”Where” is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is used before result retrieving.

Ex:

Select * from EmployeeTable

Where EmpName = ‘Smith’

“Having” clause is used after Group by clause. It works the same way as the where clause on the results after applying group by.

Ex:

Select EmpName, Sum(salary)

        From SalaryTable

Where SalaryDate >’01/01/2010’

Group By EmpName

Having Sum(Salary) > ‘200000’

What is the basic form of a SQL statement to read data out of a table?

The basic form to read data out of table is “SELECT * FROM table_name;”

What is a “constraint”?

 A constraint allows you to apply simple integrity checks on a table. The primary types of constraints that are currently supported by SQL Server are :

PRIMARY/UNIQUE – enforces uniqueness of a particular table column.

FOREIGN KEY – validates that every value in a referenced column exists in a primary column of another table.

Read more…

Many times , it may be necessary to remove Not Null constraint used for a column in  a table. It is usually necessary to overcome unexpected integrity constraints ( rarely occurs, if designed well) against a table. We can remove the Not Null constaint by using  ALTER TABLE …. ALTER COLUMN … option.

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…