Monthly Archives: March 2012

Rocking “Catalog Views” in SQL Server 2012

Khilitchandra's Business Intelligence Blog Posted on by

Hi All, Today I came across an article on MSDN regarding the meta-data of SQL Server objects which led me to the “Catalog Views“. What is it? “Catalog View” provides the information which is used by SQL Server Database Engine. So one thing is – “you can definitely trust this method“. Moreover, Microsoft recommends this method for information retrieval because it is the most efficient way to obtain, transform, and present customized forms of this information. To make the information retrieval easier, Microsoft has categorized almost every object available in SQL Server in following catalog categories. (Pls see below). You … Continue reading →

SQL Server Database Keys: PrimaryKey, NaturalKey, ForeignKey, UniqueKey, CandidateKey, AlternateKey, CompositeKey, SuperKey, SurrogateKey

Khilitchandra's Business Intelligence Blog Posted on by

Hi Guys, How are you doing? Many a times during interviews I notice that candidates know lots of things but they really hesitate while talking about “SQL Server Database Keys“. And even if they know about keys, they fail to provide proper answers with examples. So, today I would like to summarize few definitions which every database developer should be aware of. I am not going to explain each in details but this will sure give you enough confidence to generate your own examples during interviews 🙂 I am going to use the following table for example reference in below … Continue reading →

What ?? There is a “Ghost” in SQL Server !!!

Khilitchandra's Business Intelligence Blog Posted on by

Yes, it’s a cleanup process available as a part of SQL Server Storage Engine. It runs in background to cleanup ghost records. If you can remember … “When we create a clustered index then it stores actual data/records at the leaf level of the index.” And SQL Server marks/declares a record as a ghost records when they are deleted from leaf level of index. Why just mark and not to delete at once even though SQL Server knows that user does not need that record anymore ?? Well, to achieve better performance SQL Server runs “Ghost Cleanup Task” asynchronously to … Continue reading →

What happens to IDENTITY Column Value When SQL Transaction Fails??

Khilitchandra's Business Intelligence Blog Posted on by

Hi All, I would like to share a simple interview question. This is very simple yet many developers overlooks or not taking care of it. Do you know what happens to identity value if transaction fails? Do you think that identity value of table is unchanged after failure? Before looking at the solution below, try to memorize ACID properties of transaction and answer it. Check-out the POC shown below & wobble your mind 🙂

Continue reading →

Best Practices : Writing Stored Procedures

Khilitchandra's Business Intelligence Blog Posted on by

Hi All, I would like to share a handy list which I found today from my old collection during files clean-up. These are few quick tips that every developer should bear in mind while writing T-SQL codes particularly Stored Procedures. It’s always hard to find the best version of such list but we can definitely make a better version, so you are welcome to make this as a better-list by adding/editing below tips.

Continue reading →

Passing Multiple Parameter Values from Parent Package to Child Package

Khilitchandra's Business Intelligence Blog Posted on by

Hi All, I would like to bring to your attention a potential improvement in SSIS. When we need to pass parameter value from parent package to child package, we mostly use “Parent package variable” configuration type in child package. But when we need to read multiple values from parent package, we need to create multiple “Parent package variable” configuration type in child package. There are few work-arounds in order to pass multiple parameter values from parent package to child package without creating multiple “Parent package variable” configurations in child package. But we do need a good method of implementing this … Continue reading →

Line chart + Bar chart in SSRS Report

Khilitchandra's Business Intelligence Blog Posted on by

Hi All, “Language is the key for communication“. Having said that “Language” could be anything with a clear meaning of message/information to be delivered. Over the generations, we have one language which is never changed for communication among humans i.e. audio-visual communication. Developing charts for an organization for getting to know “how it’s team or organization is doing?” has been always a key requirement for managers or top-heads in organizations. But for developer it’s merely a representation of values in pretty charts. But every developer has to bear in mind that it’s always bonded with business meanings. And moreover, the … Continue reading →

Ways to execute SSIS package

Khilitchandra's Business Intelligence Blog Posted on by

Hi All, There are lots of methods documented and available on internet. But if you think about the execution of anything you may find more options. Particularly for SSIS packages, there is a golden rule to be bear in mind when planning for executing ssis packages, i.e. you will need SSIS RunTime. Following is the excerpt from my previous post “http://bigator.com/2012/02/17/the-task-scr-get-task-start-time-cannot-run-on-this-edition-of-integration-services-it-requires-a-higher-level-edition-ssis-2005-dtexec-execution-result/”

Continue reading →

Dazzle your SSRS Report with SparkLines

Khilitchandra's Business Intelligence Blog Posted on by

Hi All, “SparkLines” is a new feature introduced in SQL Server Reporting Services 2008 R2. Fast Facts about SparkLines: •    It is the simplest way to make your data/values intelligent by showing them in visual way. •    Because it’s a tiny-chart representation, makes report elegant, meaningful and non-messy. •    Report users can choose to dig in to values if they find SparkLines chart more appealing. (E.g. By seeing SparkLine chart, you can quickly determine  the fluctuation of values for the listed items rather than reading & analyzing values) •    It’s more suitable to use SparkLine rather than big charts if … Continue reading →