SQL Server and MS BI With a Geek Touch

Query to track all the indexes with fragmentation in a database.

SELECT 

OBJECT_NAME(object_id) AS TablelName

,(SELECT name FROM sys.indexes WHERE object_id = a.object_id and index_id = a.index_id) IndexName

,avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(‘<<DatabaseName>>’), NULL, NULL, NULL, NULL) a

WHERE avg_fragmentation_in_percent > 40

AND index_type_desc IN(‘CLUSTERED INDEX’, ‘NONCLUSTERED INDEX’)

ORDER BY avg_fragmentation_in_percent DESC

The <<DatabaseName>> needs to be filled with the database of your choice.

There was a time when we all wished SQL Server supports compound operators just like any other programming languages. Wouldn’t it be nice to have a shorter syntax for assigning the result of an arithmetic operator?  Well, SQL server 2008 introduced Compound operators that allow you to perform several arithmetic operations using an operand along with equal to (=) operand.

 For example:

Declare @var1  int

Set @var1 = 150

Set @var1 += 100

Read more…

SQL server usually selects the best query plan required to execute a T-SQL query. However, there might be cases where the plans selected are not the best. In such situations, you can use the query hints to suggest the best possible plan/method of executing a query. The Query hints can be applied on all operators in a query. These hints apply to SELECT, DELETE, INSERT, UPDATE and MERGE. You can use the Query hints in the OPTION clause after the query.

Few commonly used Query hints with examples are as below.

Read more…

Everyone reading this must have come across these datatypes, but have you ever thought what are the differences and advantages between these two?

The obvious difference that comes to our mind is related to the length. Yes! You are right. The varchar supports variable length and Char supports fixed length. This is the main advantage varchar datatype. for example,  let us consider varchar(10) and char(10) . To store “GEEKEPISODE”, both the datatypes will use the complete length allocated and to store “GEEK”, CHAR datatype will use the complete 10 characters  allocated, but VARCHAR will use only 4.

From the above examples it might seem that VARCHAR is better than CHAR all the time. But remember, CHAR was included as a separate datatype because it has certain advantages over VARCHAR.

VARCHAR uses additional space to store the length of the string stored in it and to mark the end of the string. Whereas, CHAR datatype doesn’t use this additional space.  This certainly is the advantage of CHAR datatype.

Summary:

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

In this blog, I will walk you through the creation of excel files ( xls / Excel 2003)  dynamically through SSIS.

Scenario: every day one of my process runs to pull data from SQL server table to excel file. I need to use unique file everyday with name as “taxonomy_<date>.xls” , for example on 14^th of January 2011 the file name should be “Taxonomy_01142011.xls” with the excel sheet name “TaxonomyValues”.

It is pretty simple to create this file dynamically. At first we need to set up an excel connection manager pointing to the file. The connection manager needs to be dynamically configured to point to the correct file everyday, in our case “Taxonomy_01142011.xls” on 14^th Jan. To do this,

1. go to properties window of the excel connection manager
2. click on expression and the browse ( … symbol) and choose “excel File Path” property. ( please refer the pictures below)
3. Copy and paste the expression given below or develop similar expression. Click on evaluate expression and it will display the file path as evaluated value.

“C:\\Taxonomy_”+ (MONTH( GETUTCDATE()  ) < 10? “0 “+(DT_WSTR,2) MONTH( GETUTCDATE()  ) DT_WSTR,2)MONTH( GETUTCDATE()  ))

+(DAY( GETUTCDATE()  ) < 10? “0 “+(DT_WSTR,2) DAY( GETUTCDATE()  ) DT_WSTR,2)DAY( GETUTCDATE()  ))

+(DT_WSTR,4)YEAR( GETUTCDATE()  ) +”.xls”

Also you need to use the settings as below for connection manager ( for .xls files)

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…

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…

According to me Excel is one of the best tools ever produced by Microsoft. In this post , I will talk to you on a new feature from Excel.

Every time you try to change a cell in excel pivot tables you will come across this error.

Writeback is a new feature available in excel to overcome this. The  new feature seems to unlock the final goal for writeback by providing a desktop user the ability to change specific values. The feature also helps adding the changes the to the cube quickly doesn’t require the cube to be processed. Once the changes are committed by a desktop user, the changes will be visible to other users on refresh.

 Note: Before enabling the writeback functionality from excel, remember to do the necessary changes in cubes (using partitions) to support writeback functionality.

The steps and screenshots below will help you in configuring writeback functionality from excel pivot tables.

Step 1:

Click on value columns in the pivot table generated from cubes. Navigate to menu PivotTable-> Options and click on “what if Analysis” as shown in the screen shot below.

Read more…