Tag Archives: Ritesh Shah

Be selective while creating an Index in SQL Server

I have just received a question in my “ASK Profile” in BeyondRelational.com. Poster asking me that he has big table with hundreds of thousands of rows in one table and there is PK on fields so it suppose to have clustered index by its own on that field so it is working fast when we keep that PK field in WHERE clause but working very slow when they keep other than that PK field in WHERE clause. He wants to make as many as possible index on his table so it works fast with any of the fields in WHERE clause.
This seems interesting case to me so I am writing this blog post, actually I have seen this kind of confusion and concerns in many other developers too. Let us make it clear.
Till SQL Server 2005, there was a limit of 249 nonclustered index on one table and 1 clustered index on the same table but nonclustered index limit was increased from 249 to 999 in SQL Server 2008 version so people might think that Microsoft has given us facility to create so many indexes on one table, why shouldn’t we use it?
Keep one thing in mind that, Index may boost up performance of you SELECT statement but it puts overhead in Insert/Delete DML commands as while manipulating records in the table, it has to go to each nonclustered index and add/remove records in each index which simply decrease the performance of your Insert/Delete statement if you have unnecessary indexes.
Apart from the reason given above, each index needs disk space to store its data and cache memory to load all indexes which simply affect the performance again due to high IO hits.
There is one more reason, if you have so many indexes; your SQL Server optimizer might get confused regarding what index to go for while executing the query so it evaluates all indexes to find the best suited index to run for, which is simply waste of time and resources especially if you are having less cache.
So keeping long story short, you should have to be selective while creating index. You shouldn’t have to create nonclustered index on every fields but find out the best selectivity in your table and create index on that only.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile”.
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

ASK Expert in BeyondRelational.com

SQL & XML GURU Mr. Jacob Sebastian has real passion to reach more and more IT person to help them in Microsoft technology. I always appreciate him for his endeavor efforts to help community.  Jacob has started new forum “ASK” under BeyondRelational.com  where you can ask question directly to the industry experts.
There are many experts available there for various subjects like TSQL, XML, ETL, SSIS, SQL Server, .NET, DBA, performance tuning and many more.
If you want to ask me any SQL Server related question, feel free to ask at following link.
You may find many forums regarding all topics I have mentioned above, the main advantage here is, you can directly ask to experts rather than raising question in other forums and wait for somebody to look at your question and answer it.
So, what are you waiting for? Register in beyondrelational.com and ask question which is giving you sleepless night.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Life savior Dedicated Administrator Connection (DAC) in SQL Server 2008

DAC could become a real life savior for SQL Server DBA in some extreme critical situation when you are not able to connect to your SQL Server instance by any other way or you found you instance totally unresponsive, you can use DAC connection.
You shouldn’t practice to login via DAC connection in normal situation as it is special connection used to use as a rescue commando only. It intends to diagnose the issue, troubleshoot it and hopefully resolve it when you are not able to make connection to instance vial any other way.
BTW, you couldn’t make more than one connection with DAC at the same time and doing so will be resulted in an error. 
Before we see how to login with DAC, I would like to tell you few points which you should keep in mind before you try out DAC.
–) you must have sysadmin server role with your login to make DAC connection
–) there are few restrictions while you make connection with DAC, you can’t Backup/Restore database. Anyhow it is advisable not to use any heavy task while you are connected via DAC as it is designed to diagnose the problem and fix it only so you might want to look around to some catalog view or DMVs.
–) Generally DAC is available locally only by default, if you try to login via DAC from remote computer, you might see following error (however, you can enable DAC from remote computer too).

There are two ways to get connected with DAC. 1.) SQLCMD 2.) SSMS. All you need to do is, specify ADMIN: as a prefix of your SQL Server instance. Suppose my instance name is “SQLHub”, I should use “Admin:SQLHub”. Suppose my instance name is “SQLHub\SQL2K8R2”, I should use “Admin:SQLHub\SQL2K8R2”.
From SQLCMD, you can connect to DAC by specifying -A flag. Now, let us see, how we can do it via SSMS.
While opening SSMS, you will see login prompt “Connect to Server”, if you will give you credential of sysadmin server role along with admin:servername(instance), you will be greeted with an error given below.

TITLE: Connect to Server
——————————

Cannot connect to admin:WIN-7XRT6YL02S0.

——————————
ADDITIONAL INFORMATION:
Dedicated administrator connections are not supported. (ObjectExplorer)

——————————
BUTTONS:

OK
——————————

 

The reason is, only one DAC connection is possible as I have already written above, if you want to access DAC connection via SSMS, don’t try to login via object explorer, rather cancel that window and follow the given steps below.
File Menu->New->Database Engine Query
Give you credential of sysadmin account, keep admin: before your server name and you will be able to connect to DAC.

WIN-7XRT6YL02S0 is my server name of SQL Server 2008, you can see “Admin:” before my server name. I am going to use my windows administrator account as it has sysadmin server role, however you can use any of your login which has sysadmin server role permission.
Keep this information ready as you never know when you need it?
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Understanding basic tools of SQL Server 2008/2005

One of my friends has just updated his live database from SQL Server 2000 to SQL Server 2008. You may know that we had direct access of system table in SQL Server 2000 which is not directly possible in SQL Server 2008/2005. He has developed many scripts in last 8 years which was directly accessing many of the system tables to generate certain kind of report, out of such scripts, few was not working. 
In recent SQL Server (2005/2008) version, we do have those “System Base tables” but it is not directly visible as well as accessible unless you are making dedicated administrator connection (DAC).  You have to access those information stored in system base table via “Compatibility View” or “Catalog View”.
“Compatibility View” is there to support your old scripts (though few very specific won’t work) which was using direct system tables in SQL Server 2000. For example we had sysobjects, sysindexes, sysusers etc. tables in older version but now we have views with the same name so your scripts of older version would works fine. Though it is “Compatibility View” intend to provide backward compatibility only, it doesn’t consist many columns which provides information about specific features of newer SQL Server version so it is recommended to use “Catalog View”.
Right from SQL Server 2005+ we are having “Catalog View” which was reading may useful information from system base tables and provide us. “Catalog View” along with “Dynamic Management View (DMV)” is under sys schema. I would prefer to go with “Catalog View” rather than “Compatibility View”. Because “Catalog View” is much richer than “Compatibility View”, you can run following query and see result by your own.
–Compatability View
select * from sysdatabases
–Catalog View
select * from sys.databases
Apart from these Views, there is one more powerful tool to read metadata of SQL Server 2008/2005 and internal information about SQL Server is Dynamic Management View (DMV) and Dynamic Management Functions (DMF). DMV and DMF generally starts with “dm_” and it resides in “sys” database so it would always starts with prefixes “sys.dm_” for example:
–return information about authenticated session in SQL Server
select * from sys.dm_exec_sessions
–returns info about currently locked object.
select * from sys.dm_tran_locks
–this is DMF and retun I/O stat for MDF and LDF file.
select * from sys.dm_io_virtual_file_stats(DB_ID(N’master’), 2);
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Sharpen your .NET Skills with .NET Quiz 2011 at BeyondRelational.com

SQL Server & XML Guru Mr. Jacob Sebastian (SQL Server MVP) has recently completed one grand online event for SQL Server along with other 30 SQL Server MVP/Blogger, winners are already declared for that, find more details here.
After completing this grand international online event, he has now started .NET Quiz 2011 on BeyondRelational.Com with help of  Hima Bindu Vejella who is Quiz Manager for “.NET Quiz 2011”.
This event has already started from 1st January 2011 for 31 days. Each day one .NET quiz master will ask one question and s/he will moderate the discussion and answer of that question. Finally Quiz Master will rate your answer between rank 1 and 10. Score of all 31 questions will be summed up to identify the winner of the competition after 31st January 2011.
You don’t have only chance to share/enhance your knowledge but have chance to win some of exciting prices like Apple iPad and free license of some cool .NET tools.
So what are you waiting for??? 
Just grab the opportunity. If you are still not registered member of http://beyondrelational.com . Register now!!!. 
Here is the official home page for .NET Quiz 2011.
BTW, I am too one of the quiz master and my question is going to be enabled by today 2nd January 2011. Here is the link for the same.
Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Experiencing installation of SQL Server Denali in Windows Server 2008

Before we move further for technical discussion, want to say:
Happy New Year to all reader!!!
May GOD bless you with full of joy, health, wealth and prosperity.
BTW, I have been inactive in blogging since last few months so I am making New Year resolution that I keep writing more and more article and try to help community as much as possible.
Let us now begin journey of new powerful “Denali” in the starting of New Year. Microsoft has released SQL Server Denali CTP1 sometime back only. 
I am very much excited to make grip over it so I have prepared one Virtual PC with Windows Server 2008 SP1 as I had paper license of the same. As soon as I have installed Windows Server 2008 with SP1, I have downloaded Denali and started installing it and greeted with one message which was bit annoying to me. 
Error was “The operating system on this computer doesn’t meet the minimum requirement for SQL Server “Denali” CTP1”
Actually this is CTP1 only and doesn’t install required software by its own so we have to meet basic hardware and software requirement prior to install “Denali”.
I have installed following SP and software to make my system ready for “Denali”.
After installing all above stuff, I was able to successfully install SQL Server “Denali”.
Here is the splash screen of the same:
After looking at the new SSMS of SQL Server “Denali”, I got the feeling of Visual Studio 2010 as I have installed Visual Studio 2010 few weeks back only and getting same feeling due to changed UI of SSMS and blue back color.
 
Happy Coding!!!
At the end of this article, Wish you a great year ahead. Happy New Year 2011 Once again.
Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

For XML PATH ROOT in SQL Server 2008

Now and Then I used to observe that when it comes to XML, .NET and SQL Developer start fearing. This is not because of It is VERY DIFFICULT but because of unawareness. Today I am going to show you the use of  “FOR XML PATH”  clause in SQL Server.
Suppose we have ID, FirstName and LastName columns in one of our SQL Server table and we want output something like this:
<Employees>
  <Employee>
    <ID value=1 />
    <FirstName value=Ritesh />
    <LastName value=Shah />
  Employee>
  <Employee>
    <ID value=2 />
    <FirstName value=Rajan />
    <LastName value=Jain />
  Employee>
Employees>
Let me give you TSQL to generate table in SQL Server and insert records in that.
Create Table EmployeeData
(
      ID INT Identity(1,1),
      FirstName varchar(10),
      LastName varchar(10)
)
insert into EmployeeData
select ‘Ritesh’,‘Shah’ UNION ALL
select ‘Rajan’,‘Jain’
GO
So now, here is the use of “FOR XML PATH ROOT” clause which will make our life easier in this kind of situation.
select ID as “ID/@value”
     , FirstName as “FirstName/@value”
     , LastName as “LastName/@value”
from (
   Select * from EmployeeData
) as t
for xml path(‘Employee’), root(‘Employees’);
Isn’t it easy to use?
BTW, below given are some links which will redirect you to my some of the past articles on XML subject.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Character Map Transformation task in SSIS 2008

When it comes to string formatting in SSIS, “Character Map” transformation task is one of the very useful tasks. It generally used to convert data to Uppercase, lowercase, byte reversal etc. let us see one small demo for the same.
Before we start our package development, let us first create one small table in SQL Server and insert some data into it by following TSQL Script.
create table Employee
(
      FirstName varchar(10),
      LastName varchar(10)
)
insert into Employee
select ‘rITesH’,‘ShAh’ union all
select ‘Rajan’,‘SHAH’
GO
Now, create one new project in BIDS (Business Intelligence Development Studio) for SSIS package. Drag “Data Flow” task on your “Control Flow” tab and double click on that “Data Flow” to configure it which will forward you to “Data Flow” tab.
Drag “Ado Net Source” from tool box and drop it into your “Data Flow” tab.  Double click on “Ado Net Source” to configure it and select your Server, database and table, “Employee” table in our case.
Now, drag “Character Map” transformation task to your work area and join it with extended green arrow coming from “Ado Net Source”. Now double click on “Character Map” transformation task to configure it. We are going to convert FirstName column to UPPERCASE and LastName column to LOWERCASE. Look at screen capture below for more information.

Once you finish configuring “Character Map” transformation task, drag “Ado Net Destination” from tool box to right below “Character Map” transformation task and join it with extended green arrow coming from “Character Map” transformation task.
Double click on “Ado Net Destination” to configure it and select your SQL server, Database and table (we are going to use same Employee table as destination too) .
Once you finish all these steps, you are ready to launch your package. Hit F5 to run the package and see EMPLOYEE table in SQL Server whether it has two more rows with different case of alphabet.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Fuzzy lookup in SSIS 2008 to keep data integrity

“Human makes mistakes” which is quite obvious. While making a data entry it is possible to make typo but as a database professional, it is our duty to keep data consistent.  Fuzzy Lookup is helpful in this case. Before we start making package in SSIS, let us have some pre-preparation for that. We are going to create one source table (it could be any source like excel, csv file etc. but we are making it in SLQ Server), one reference table which is guaranteed to have proper data. Here is the TSQL to create, source and reference, table and insert some dummy data.
create table fuzzyLookupSource
(
      firstName varchar(10),
      LastName varchar(10),
      BirthDate datetime
)
insert into fuzzyLookupSource
select ‘Rites’,‘Shah’,’02/07/1980′ union all
select ‘Rajen’,‘Shah’,’03/31/1983′ union all
select ‘Dharmesh’,‘Kalaria’,’04/09/1980′  union all
select ‘Jesica’,‘Cruize’,’05/05/1980′  union all
select ‘Roger’,‘Moore’,’04/15/1980′
GO
create table fuzzyLookupReference
(
      firstName varchar(10),
      LastName varchar(10),
      BirthDate datetime
)
insert into fuzzyLookupReference
select ‘Ritesh’,‘Shah’,’02/07/1980′ union all
select ‘Rajan’,‘Shah’,’03/31/1983′ union all
select ‘Jessica’,‘Cruise’,’06/05/1980′  union all
select ‘Dharmesh’,‘Kalaria’,’04/09/1980′
GO
Observe the data in both table, in first, source, table, there are some typos which you can compare with your second, reference, tables and get the purified data.
Anyway, once you are ready with both the tables , create one new project in BIDS (Business Intelligence Development Studio) and drag one “DataFlow” task from tool box to your “Control Flow” tab. Double click on “DataFlow” task to configure it so that it would redirect you to “DataFlow” tab.
Now, create one “Ado Net Source” which will refer our “fuzzyLookupSource” table in sql server database. Double click on “Ado Net Source” to configure it and look at below image to have crystal clear idea about its configuration.

Now,  drag “fuzzy lookup” transformation task below your “Ado Net Source” and connect extended green arrow from “Ado Net Source” to your fuzzy lookup. Double click on “Fuzzy Lookup” task to configure it.

In “Reference Table” tab, give reference of your database and our reference table which is “FuzzyLookupReference” in our case. Look at image below for more idea.

Click on “Columns” tab to configure which column to check with reference from source table and select “firstName” and “lastName” column and connect it so that our fuzzy lookup task will compare these two fields from source to reference table.

Once you configure “columns”, you have to click on “Advanced” tab, you can set “Similarity Threshold” which will give you how much identical both fields are…. If it is 1 than it is perfect match, if it is 0 than no match or data not present in reference table so more near to 1, good match it is. We are not going to take any decision like if it is greater than .50 then do this otherwise do that so it would be ok if you don’t change “Similarity Threshold”.

Now, drag “SQL Server Destination” task so that this matched and unmatched data could fall in SQL Server table, though we have not created any SQL Server table for this so far. Connect green extended arrow from “Fuzzy Lookup” transformation task to “SQL Server Destination” task. Before we configure “SQL Server Destination” we would like to do one more thing. Double click on GREEN arrow between Fuzzy Lookup task and SQL Server destination task.

We would like to see data in grid while running this package and before it fall into our destination table, we are going to specify this only now.
As soon as you click on green arrow, it will open “Data Flow Path Editor”, click on “Data Viewer” tab and click on “Add” button to add “Grid”.

Now, double click on “SQL Server Destination” task to configure it. Give details of your SQL Server and database into “Connection Manager” name. since we don’t have destination table already created for our data, we are going to click on “New” button besides “Use a table or view” property which will create one destination table in our SQL Server.

Now you are ready to run your package, hit F5 to run it, when it crosses Fuzzy Lookup Task, it will show you data in grid, check it and click on “Green Arrow” above the grid in same dialog box so that data falls into our SQL Server table.

you can later on check the same data into SQL Server by executing TSQL Query, while generating new table in SQL Server, if you didn’t have rename the table, it would be by default [SQL Server Destination]. So you can execute something like

SELECT * FROM [SQL Server Destination]
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Merge Join Transformation Task in SSIS 2008

SSIS is the very crucial tool for Data Warehouse and collecting data from different sources and merge it into single master source is the main task in Data Warehouse.  Merge Join Transformation task is one of the useful tool in SSIS to achieve this. Let us see how it works.
Well, before we start developing package, let us create two different data sources where data come from in single source. We are also going to create one blank table which will hold the data come from two different sources.
Our first source will be SQL Server table.
Our second source will be Flat file which is comma separated.
And the destination of the both sources would be in again SQL Server single table.
Script for First Source:
Create Table OrderMaster
(
      OrderID Varchar(5),
      ClientCode Varchar(10),
      ProjectName varchar(25),
      OrderDate DateTime
)
INSERT INTO OrderMaster
SELECT ‘A1001’,‘CHEM02’,‘NY WaterField’,GETDATE() UNION ALL
SELECT ‘A1002’,‘ACCU01’,‘Plainfield Soil’,GETDATE()
GO
Create table in SQL Server database and insert data in that table via above given script.
Second Source:
Create one text file in D drive with name OrderDetails.txt with following data.
OrderID,SampleNumber,Matrix
A1001,A1001-01,Water
A1001,A1001-02,Soil
A1002,A1002-01,Water
A1002,A1002-02,Water
First row in the above data is column name.
Now, let us create destination table.
Create Table OrderMasterAndDetail
(
OrderID Varchar(5),
ClientCode Varchar(10),
SampleNumber Varchar(10),
Matrix Varchar(10)
)
GO
Ok, now we are ready to start developing SSIS package. Open new project in BIDS (Business Intelligence Development Studio). Drag “Data Flow” task from tool box to your “Control Flow” tab and double click on it to configure. As soon as you will double click on that, you will be redirected to the “Data Flow” tab. Now, drag  “ADO Net Source” and “Flat File Source” task to your “Data Flow”. Let us now configure them.
Double click on “Ado Net Source” to configure it and select your database and table to configure it. Look at the below given Image for more details.
Now, double click on “Flat File Source” to configure it. From the “Flat File Source Editor” please click on “New” button to create “Flat File Connection Manager”. Now from “Flat File Connection Manager Editor” give name “OrderDetails” in “Connection Manger Name” property. By clicking on “Browse” button, select your “OrderDetails.txt” file from D drive. Don’t forget to check on the CheckBox “Column name in first data row” as our first row in text file is our column header.  For more details, look at image below.
Now drag “Sort Transformation” task from tool box to your data flow tab and repeat this step to have two “Sort Transformation” task. One is for “Ado Net Source” and second one is for “Flat File Source”. Join both sort task with appropriate green arrow and double click on it one by one to configure it and make ascending sort on “OrderID” field in both the sort task.
Now, drag the merge join task from tool box and drop it below both sort tasks. Connect green arrow from both sort task to “Merge Join” task and double click on “Merge Join Transformation” task to configure it. “OrderID” column in both the source should be work as JOIN key.  Select “OrderID”, “ClientCode” from first sort and “SampleNumber” and “Matrix” from second sort as shown in screen capture below.
Now, you are having data from both the source to your “Merge Join”. You have to send that data to specified location, in our case it is one SQL Server table we have already created with the name “OrderMasterAndDetail”. Since we want to send our merged data to SQL Server, let us now drag “Ado Net Destination” task to our dataflow and connect it to our merge join. Now, this is the time to configure “Ado Net Destination” so double click on it and select your database connection and table named “OrderMasterAndDetail”, look at the screen shot below for more information.
So, finally you are ready to run the package, hit F5 to run that and if every task seems green as below screen capture than you are all set and you have already received data in your destination table.
Check your “OrderMasterAndDetail” table in SQL Server whether you have actually received any data there or not.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah