SQL Troubles

Window Functions – Part I: The Case for Window Functions

2011-12-02T20:49:00.001-08:00

Introduction In the past, in the absence or in parallel with other techniques, aggregate functions proved to be quite useful in order to solve several types of problems that involve the retrieval of first/last record or the display of details together with averages and other aggregates. Typically their use involves two or more joins between a dataset and an aggregation based on the same

What’s New #4: SQL Server 2012 is almost here

2011-11-11T16:34:00.001-08:00

I was quite quiet for the past 3-4 months, and this not because of the lack of blogging material, but lack of time. Instead of writing I preferred reading, diving in some special topics related to SQL Server (e.g. tempdb and security), in the near future following to post some of my notes. For short time I was busy learning for ITIL® v3 Foundation Certification, the topics on Knowledge

MS Access vs. LightSwitch: About Starts and Ends of Software Products

2011-08-04T13:39:00.001-07:00

Introduction When an important software product or technology is released on the market, it brings with it dooming prophecies about the end/death of a competing or related product or technology. Even if maybe it catches the attention, the approach became a stereotype leading to other futile fights between adepts, some food for thought and a pile of words for search engines. As LightSwitch

Could not load file or assembly Microsoft.MSXML…

2011-07-16T14:24:00.001-07:00

I’m not programming anymore as I used to do, though from time to time I still fancy some .Net programming. It’s not much, small applications or CLR-based libraries for SQL Server. Quite often, when I return to programming after a long pause it happens that I run into problems, finding that something that was working some time ago isn’t working anymore. This time I couldn’t load anymore one of

Pulling the Strings of SQL Server–Part X: Dynamic Queries

2011-06-17T18:12:00.001-07:00

A dynamic query is a query constructed at runtime, techniques often indispensable in many situations that require a certain flexibility in query’s creation. The creation of a dynamic query is nothing but a set of operations with strings, many of the techniques mentioned before becoming handy. SQL Server provides two functions for the execution of dynamic queries, namely EXECUTE statements (or

Pulling the Strings of SQL Server - Part VII: List of Values

2011-06-17T14:05:00.001-07:00

Lists are one of the basic structures in Mathematics, the term referring to an (ordered) set of elements separated by comma, space or any other delimiter (e.g. “:”, “;”). The elements of a list can be numbers, words, functions, or any other type of objects. In the world of databases, a list is typically formed out of the values of a given column or a given record, however it could span also a

SSRS: Wizarding a Report - First Magic Class

2011-04-22T15:22:00.001-07:00

One year back I started a set of posts on SSIS showing how to create a package with ‘SQL Server Import and Export Wizard’ using as source a SQL Server, respectively Oracle database, in a third post showing how to use the Data Flow Task. The “wizarding” thematic was based on the fact the respective posts were showing how to make use of the wizards built within SQL Server and Visual Studio in

Searching a Value within a Database

2011-04-08T17:44:00.001-07:00

Some years back, in a Management meeting related to one of the projects I was working on, one of the Managers made a quite interesting statement, something like: “it should be easy to pool in an Excel sheet from the whole database all the information I am interested in”. I kind of smiled, because the information he was requiring was spanning within different modules, having different levels

Table Value Constructors at Work

2011-04-08T15:31:00.001-07:00

Paradoxically, the first new SQL Server 2008 feature I learned, soon after the first CTP was made available, was the use of table value constructors, however I managed all this time without using it. This happened because I was either working on previous SQL Server versions or because other techniques were more appropriate to the problems I had to solve. So, a few days ago, the use of table

Approaching a Query

2011-03-25T13:36:00.001-07:00

You received a (long) query for troubleshooting, reviewing, conversion or any similar tasks. In addition, you don’t know much about the underlying table structure or business logic. So, what do you do then? For sure two things are intuitively clear: you don’t need to panic and, understanding the query may help you in your task. Understanding the query, it seems such a simple statement, though

Pulling the Strings of SQL Server – Part VIII: Insertions, Deletions and Replacements

2011-03-10T15:38:00.001-08:00

Until now, the operations with strings resumed to concatenation and its reverse operation(s) - extracting a substring or splitting a string into substrings. It was just the warm up! There are several other important operations that involve the internal manipulation of strings – insertion, deletion and replacement of a substring in a given string, operations performed using the Replace and

Pulling the Strings of SQL Server – Part VI: Subparts of a String

2011-02-24T12:07:00.001-08:00

No matter how normalized a database is, there will always be the need to encode multiple semantic entities in a string, needing thus to extract them later. For example data like the Street Name and Number, or the Country Phone Prefix and Phone Number, the First and the Last Name, etc. Another frequent scenario is the misuse of a long-sized string data types to store multiple delimited

Pulling the Strings of SQL Server – TOC

2011-02-21T14:57:00.001-08:00

There are many books out there dealing with SQL Server string data types, basic operations performed with them, the use of various string related functions, and most of the books stop there. I learned many things from such books, though, as I had to “discover” myself some of the tricks of working with strings, I’m trying in a set of posts entitled “Pulling the Strings of SQL Server” to treat

Deleting Sequential Data From a Table

2011-02-04T17:05:00.001-08:00

Last week I run into an interesting solution to a simple problem, problem I dealt with in several occasions too: how to delete (efficiently) sequential data, or how LuborK calls it in his post, “a problem of efficient ordered delete”. In this case he’s talking about the deletion of the first n records from a dataset of sequential data. It sounds like a simple problem considering that we can

What’s New #3: SQL Server Resources

2011-01-30T09:13:00.001-08:00

During the past weeks I found several interesting learning resources on SQL Server 2008 topics, so here they are: For those interest to upgrade their knowledge to SQL Server 2008 R2 Edition check the Microsoft SQL Server 2008 R2 Update for Developers Training Course videos on Channel9. They cover topics like Location Awareness, CLR Integration, StreamInsight, Reporting Services,

Pulling the Strings of SQL Server–Part IV: Spaces, Trimming, Length and Comparisons

2011-01-07T11:39:00.001-08:00

In the previous post on concatenation, I was talking about the importance of spaces and other delimiters in making concatenations’ output more “readable”. Excepting their importance in natural language, the spaces have some further implication in the way strings are stored and processed. As remarked in the introductory post from this topic, there are two types of spaces that stand out in the

Pulling the Strings of SQL Server–Part V: Character Indexes

2011-01-03T14:20:00.001-08:00

A string is in fact a concatenation or chain of characters, each character in the string having a position referred as character’s index. In programming it’s useful to identify the first occurrence of a character or string in forward or backwards order, with a few simple tricks being possible to identify all the occurrences of the characters or whole strings in a string. Such functions are

Book Review: Head First SQL

2011-01-01T14:23:00.001-08:00

I love the Head First O’Reilly book series which excepting the book on SQL, have acquired over its short existence several other titles like the ones on Data Analysis, Statistics and Excel, topics strongly correlated with the thematic of the current blog, but also related tangential topics like Project Management, Design Patterns, Object-Oriented Analysis and Design, Software Development, Web

SQL Troubles - Past, Present and Future

2010-12-31T10:13:00.001-08:00

Introduction Even if I started to blog 3-4 years ago, only this year (still 2010) I started to allocate more time for blogging, having two blogs on which I try to post something periodically: SQL Troubles and The Web of Knowledge plus a homonym Facebook supporting group (for the later blog). As a parenthesis, the two blogs are approaching related topics from different perspective, the first

Pulling the Strings of SQL Server – Part III: Concatenation

2010-12-14T12:20:00.001-08:00

Typically a database in general, and a table in particular, that follows the normalization rules, is designed to have the columns contain the smallest semantic chunks of data, it could be a Street, a Zip Code, City, a person’s First or Last Name, but also a large chunk of text like a Description or a Comment. No matter how well designed is a database, there will always be the need to do

Pulling the Strings of SQL Server–Part II: Creation and Selection

2010-12-10T15:47:00.001-08:00

It doesn’t make sense to talk about the creation of string data types without talking about their “selection” as people typically want to see also examples at work, and not only learn about theoretical facts. I’m saying that because often programming seems to be like putting together the pieces of a puzzle without knowing how the final image would look like. Anyway, coming back to the topic,

Pulling the Strings of SQL Server–Part IX: Special Characters

2010-12-08T14:55:00.001-08:00

Under special characters denomination are categorized typically the characters that don’t belong to the alphabet of a given language, typically English (a-z, A-Z), or the numeric digits (0-9). Characters like umlauts (e.g. ä, ë, ö, ü, ÿ), accents (e.g. é, í, ó, ú) and other type of characters used in specific languages like German, French, Turkish, Hungarian, Romanian, Dutch or Swedish,

Pulling the Strings of SQL Server–Part I: An Introduction

2010-12-07T14:04:00.001-08:00

The (character) string or simply the character data type, how is named by the MSDN documentation, is one of the primary data types available in a database and probably the most complex given the fact that it can encompass any combination of numeric, literal, date or any other primary data type. In fact it could include any type of chunk of text that could be written in any language as SQL

A Look into Reverse

2010-12-04T16:55:00.001-08:00

Some time back I was mentioning Stuff as one of the functions rarely used in SQL Server. Here’s another one: Reverse. I remember I used such a function in other programming languages and maybe 1-2 times in SQL Server, and I can’t say I seen it in action in other posts. As it name states and the MSDN documentation confirms, Reverse returns the reverse of a string value. The function takes as

How Many Reporting Systems You Need?

2010-11-29T15:19:00.001-08:00

I hope nobody’s questioning the fact that an organization needs at least a reporting system in order to take advantage of the huge amount of data it has collected over time, in order to have an overview on what’s happening in the organization, take better decision supported by data, etc. In one way or another many organizations arrive to have in place two or more reporting systems and all the

Why I (dis)like MS Access

2010-10-12T12:01:00.001-07:00

In the previous post, “The Limitations of MS Access Database”, I highlighted a few of the limitations of MS Access as database, ignoring the other two or three important aspects – Access as development, reporting, respectively data analysis platform. In this post I’ll retake the topic from a general and personal perspective, considering some of the features that I like and makes from Access a

The Limitations of MS Access Database

2010-10-05T14:38:00.001-07:00

In the previous post I was highlighting some general considerations on the use of MS Access and Excel as frameworks for building applications. I left many things out from the lack of time and space, therefore, as the title reveals, in this post I will focus simply on the limitations of MS Access considered as Database. I considered then that Access is a fairly good as database, recommending

Is MS Access or MS Excel the Answer to your Problem?

2010-10-02T05:00:00.001-07:00

Introduction That’s one of the questions that followed me for years, quite often being asked by customers to provide a MS Access or MS Excel solution as an answer to a business need. The beauty of this question is that there is no right answer and, as I stressed out in several occasions, there is not always a straightforward answer to such a question in IT, the feasibility of an IT solution

Some Thoughts on Performance Troubleshooting

2010-09-29T11:35:00.001-07:00

I checked ReplTalk’s post on “waiting” statements, realizing that I had to write a similar query when troubleshooting performance of running queries in general, respectively blocking issues in particular. The novelty resides in Adam Machanic’s comment to the respective post, the link leading me to sp_whoisactive stored procedure which provides a collection of wait stats, lock information,

SQL Injection – Part I: An Introduction

2010-08-15T12:20:00.001-07:00

Introduction If you are working in IT, most probably you’ve heard already about SQL Injection, if not then might be it’s a good idea to ask your colleagues and eventually your IT manager if your company has any policies related to it. If you are working for a software vendor or a consultancy company then SQL Injection countermeasure techniques might be quite well positioned in the list of

Temporary Tables vs. Table Variables – Stored in TempDB

2010-08-12T15:32:00.001-07:00

Yesterday I started to read Ken Henderson’s book, SQL Server 2005 Practical Troubleshooting: The Database Engine, diving directly into tempdb topic (Chapter 9, Tempdb issues). He mentions that metadata are created in system tables when a temporary table is created (see p.415). This means that when a temporary table is created, a record must be created in tempdb’s sys.tables and sys.columns

SQL Server 2008 Information Schema Views

2010-08-09T15:33:00.001-07:00

In the last post, Database Object Dependencies – An introduction, I was talking about metadata, in common understanding defined as “data about data”, and its meaning in the context of databases as “information (data) stored about data, the structures or objects related to data”. The SQL92, SQL99, SQL2003, and I suppose also SQL2008 and the next coming ISO standard document versions, define an

Database Object Dependencies – Part I: An introduction

2010-08-07T07:47:00.001-07:00

Introduction Around the various data islands existing in the blue and the models that support them are created a whole range of database objects (views, stored procedures, user-defined functions) and other type of non-database objects (classes, strong-typed datasets, reports, ad-hoc queries, etc.) With each reference to a database object is created a database dependency, or simply dependency

SQL Server Troubleshooting Resources – Part I

2010-08-01T14:05:00.001-07:00

Last week the SQL Server troubleshooting-related topics came again on my table, and it’s actually quite an important topic. As I have disparate links I thought it makes sense to bring the links together in a post. When searching for information it’s always a good idea to start with the documentation or the support site, in what concerns SQL Server troubleshooting Microsoft has several

Self-Joins and Denormalized Data Loading in Normalized Models

2010-07-31T12:55:00.001-07:00

One of the scenarios in which I often make use of self-joins is when needing to load denormalized data into a normalized data model. A characteristic of the not-normalized or denormalized data is that there are repeating data, typically the so called header data, which need to be handled specifically. Note: Header data it’s improper said because an entity could contain more than 2

Believe and Not Doubt?!

2010-07-30T15:35:00.001-07:00

Long time ago while reviewing a PowerPoint presentation on Oracle performance troubleshooting, on one of the pages was written with big letters something like “Believe and not doubt!”, this in the context of the knowledge transmitted from guru to other professionals. I do not believe in the approach of following blindly the techniques and knowledge of other professionals, and the professional

Self-Join in Update Query - Update

2010-07-30T11:16:00.001-07:00

In yesterday’s post on the same topic I tried to exemplify how a self-join update query could be written in order to avoid an error raised by the database engine. Even if this type of query is normally written for hierarchical structures consisting of one-level parent-child relations stored in the same table, my example was using a simple join based directly on the primary key. During the day

Self-Join in Update Query

2010-07-28T13:55:00.001-07:00

While reading R. Scheldon’s article on “UPDATE Basics in SQL Server” I remembered about a problem I had long time ago while attempting to do a self-join in an Update query. Such a query is quite useful in hierarchical structures consisting of one-level parent-child relations stored in the same table, when needed to update the parent based on child information, or vice-versa. The problem I had

Porting 32 bit CLR UDFs on 64 bit Platforms

2010-07-26T13:49:00.001-07:00

Today I tried to port on a 64 bit platform a few of the CLR UDFs created in the previous posts, this time being constrained to use Visual Basic Studio 2010 Express to create and build the assembly on a x86 platform, and install the assembly on a x64 SQL Server box. From the previous troubleshooting experience between the two platforms, I knew that there will be some challenges, fortunately

Football and Software Development

2010-07-24T14:25:00.001-07:00

I wanted to write this post during the South Africa World Cup 2010 though because of the lack of time and because I was waiting for some statistics I could use, here I am, two weeks after the final whistle of the game for the first place. Football and Software Development, two domains that seems to have nothing in common, even if many software developers like to play football, and many

Some Notes on Dates

2010-07-24T07:33:00.001-07:00

The Date is one of the most difficult data types to handle, given the fact that it might take different formats (e.g. DD/MM/YYYY, MM/DD/YYYY, DD/MM/YY, DD-MON-YYYY, etc.), and even if the various formats make a limited list, the fact that each database, machine or application could work with a different date format or set of date formats, this makes developers’ life a nightmare. The

Over-Educated, Yet Under-Qualified?

2010-07-24T05:24:00.001-07:00

I know this blog is attempting to treat subjects related to data and databases, though the database developers, same as other type of developers and other professionals, and the various aspects of their professional life are part of the landscape too. Alfred Thompson, in his post “Over-Educated, Yet Under-Qualified?”, approaches quite an important topic for many graduates, but especially the

Running a Statement for Each Database – The CLR Version

2010-07-22T13:22:00.001-07:00

I continue the series of posts on the use of the dbo.ExecuteScalarTo CLR functions created in a previous post, this time, as is mentioned in the title, by running a statement for each database. The old-fashion method of a statement for each database is with the help of undocumented stored procedure sp_MSforeachdb, see for example ReplTalk’s post found recently. Actually the

Number of Records – The DMV Approach

2010-07-17T05:03:00.001-07:00

In SQL Server 2000 the safest way to return the total number of records from a table was to do a simple COUNT. I’m say the safest way because (for big tables) there was a faster but approximate way to obtain the same information by using sysindexes table, which, as its name denotes, was storing one record for each index and table in a given database. When I started to use SQL Server 2005 and

CRUD Stored Procedures from Metadata – Part II: The Get, Dropdown and Delete

2010-07-12T15:21:00.001-07:00

If in the previous post on CRUD Stored Procedures from Metadata I shown how could be created automatically a stored procedure for Insert/Update, in this post I’ll show how a similar template could be created in order to create a Get, Dropdown or Delete stored procedure. The so called “Get” stored procedure is used in order to retrieve a record for view/update, and here’s the template for it:

CRUD Stored Procedures from Metadata – Part I: The Insert/Update

2010-07-12T13:47:00.001-07:00

In Saving Data With Stored Procedures post I shown my favorite technique of creating a new record, respectively updating an existing record, by using a stored procedure, while in Creating a Stored Procedure from Table’s Metadata post I shown how a macro could be created in a semiautomatic manner in order to create the same type of stored procedure using table’s metadata. The Excel approach,

Evaluating Textual Expressions with CLR UDFs

2010-07-11T06:42:00.001-07:00

The developers having experience with scripting languages like VBScript or JavaScript are most probably used with Eval (VBScript/JavaScript) or Execute (VBScript) functions, functions that parse a string evaluating its content to an expression or evaluating a given set of instructions, allowing thus to create and execute code at runtime. In several occasions I needed similar functionality

Data Profiling – Part II: Using CLR functions

2010-07-04T09:56:00.001-07:00

In the previous post on Data Profiling I introduced basic data profiling techniques, the queries used for exemplification being quite simple. Probably many people will ask themselves on whether is possible to automate data profiling for a whole range of tables/datasets and the attributes they contain. The answer is simple, this relies entirely on databases’ capabilities of running dynamic

Data Profiling – Part I: An Introduction

2010-07-03T12:29:00.001-07:00

Some Generalities J.E. Olson, in his book Data Quality: The Accuracy Dimension defines data profiling as "the use of analytical techniques to discover the true content, structure, and quality of data" [1]. Understanding the structure of data is quintessential for database developers in order to provide accurate technical solutions (reports, database objects for various purposes), though

Number of Records – The CLR Version

2010-06-29T13:55:00.001-07:00

While scanning rapidly the daily posts from MSDN blogs, I stopped on Chris Skorlinski’s “Example code executing TSQL for each Table in a database” post, wondering then on whether he’s using other techniques than I used in the past. I run into this type of need several times, especially in cases in which I wanted to report in an automated manner the number of records for a given set of tables,

What’s New #2

2010-06-27T13:47:00.001-07:00

Microsoft Office - Cloud Computing is the Word Two weeks ago, on 15th of June 2010, Microsoft Office was shipped together with Visio and Project 2010, closing the cycle of releases started with SQL Server 2008 R2, Visual Studio 2010, Sharepoint 2010 (all 3 shipped in April 2010) and Windows Azure (available also in April). The words that describe/unite at best these software tools is

Trivial Equalities in Queries

2010-06-25T14:31:00.001-07:00

If I remember correctly from the Math literature, equalities of the type 0=0, 1=1 or more general n=n could be referred also as trivial equalities. I tried to find, without success though, an exact definition of what is intended by trivial equality, the closest I could get is the Wikipedia’s content on the use of trivial adjective in Mathematics for objects that have a simple structure. As

SSIS and Oracle

2010-06-20T09:55:00.001-07:00

Making SSIS work with Oracle doesn’t seem to be a complicated task especially after several years of experience in doing that, though, as usual, something new appears in the landscape – new software versions, new requirements, an environment with its own particularities, etc. In general, when an application needs a connection to an Oracle server, is needed to install on the client computer

Troubleshooting – Part III: Troubleshooting query issues: Logical errors

2010-06-20T04:55:00.001-07:00

In last post I provided some general guidelines on how to troubleshoot query issues related to errors thrown by the database engine. That’s a fortunate case, because engine’s validation shows that something went wrong. I’m saying fortunate because there are also cases in which there are also logical errors that result in unexpected output, and the errors could remain even for years

Troubleshooting – Part II: Troubleshooting query issues: Errors

2010-06-20T03:06:00.001-07:00

Some weeks ago I wrote a post on troubleshooting, looking at the topic from problem solving perspective. In what concerns the troubleshooting of SQL scripts, now it depends also on the type of DML statement used or on the type of issues the developer deals with – errors thrown by the database engine, wrong number of records returned by a query, performance, accessibility or reusability

Reports’ Creation - Part I: Addressable Questions

2010-06-03T12:52:00.001-07:00

The creation of a database-based report of any type involves most of the times several iterations until all the requirements are gathered, the query and eventually the final report are created, the reports gets tested, the issues related to deliveries and requirements mitigated, etc. Typically the steps involved in the creation of a report are straightforward for the developer, though for the

What's New #1

2010-06-03T07:38:00.001-07:00

Instead of Introduction May is gone and it was quite a stressful month because given the fact that I had to start with a new job, a new position I will have to grow in, I had also to find a new apartment, move, install myself, so I barely had time for my blogs, the little time I had spending it reading stuff related to the new requirements, trying to improve my German, etc. Even so, I

Troubleshooting – Part I: The Problem Solving Approach

2010-04-24T02:45:00.001-07:00

Introduction In several occasions I observed that there are SQL and non-SQL developers who don’t know how to troubleshoot a programming problem in general, respectively a SQL-related issue in particular, and I’m not referring here to the complex problems that typically require the expertise of a specialist, but simple day to day situations: troubleshooting an error thrown by the database

To get or not Certified?!

2010-04-21T05:51:00.001-07:00

To get or not certified?! That’s a question I asked myself several times along the years, and frankly it doesn’t have an easy answer because there are many aspects that need to be considered: previous education, targeted certification, availability of time, financial resources or learning material, required software, hand-on experience, certification’s costs, duration/frequency, objectives,

Learning SQL

2010-04-17T14:42:00.001-07:00

Introduction One of the topics that appears in many database related groups or forums is from where and how new comers should start to learn SQL (Structured Query Language) in general, and specific vendor SQL features in particular. Two years back, in Learning something new post from my programming-grasps blog attempt, I wrote down my thoughts on how to approach learning new subjects in

The Power of Joins – Part VI: Not-Equal Joins

2010-04-05T09:05:00.001-07:00

SQL Server 2008 documentation mentions the not-equal joins without giving an explicit definition, the term appearing within the context of joining values in two columns that are not equal. On the other side the not-equal join denomination could be used to refer more generally to the joins whose join constraints involve comparison operators other than equal (“=”), for example <, >, <>, BETWEEN

The Power of Joins – Part V: Semi-Joins and Anti-Joins

2010-04-03T11:31:00.001-07:00

An incursion in the world of joins is incomplete without approaching two other important techniques for writing queries: semi-joins and anti-joins, some of the techniques specific to them allowing to speed up queries and reduce queries’ complexity. An anti-join returns rows from the left table where no matches is found in the right table, while an semi-join returns returns rows from the left

The Power of Joins – Part IV: Multi-Joins Under Control

2010-04-02T15:10:00.001-07:00

Few are the queries based only on the join between two tables, each additional table added to the query involving an additional join with one or more of the already considered tables. Such cases in which more than one join is involved in a query are referred as multi-joins or multiple joins, the joins could involve the same join operator or it can be a mixture of the horizontal or vertical

The Power of Joins – Part III: Vertical Joins

2010-04-02T06:58:00.001-07:00

Introduction A vertical join allows the possibility of retrieving data from multiple datasets by combining the attributes from one or more datasets on the same row, being possible to include complex expressions using any combination of attributes. The join within a vertical join is based only on the attributes specified in the join constraint(s), typically each constraint taking the form

The Power of Joins – Part II: Horizontal Joins

2010-04-01T13:56:00.001-07:00

Introduction A horizontal join allows retrieving data from two or more datasets by merging/comparing the rows from each involved dataset, the join of rows being done on vertical, thus a row from the final result dataset containing only the attributes from one of the input datasets, the attributes being matched based on their order in the dataset (first attribute with first attribute from

The Power of Joins – Part I: Introduction

2010-03-29T15:32:00.001-07:00

Joins are the heart of RDBMS, they allowing to retrieve data (bring data together in a single result dataset) from multiple (related) table-like objects (tables, views, user-defined functions) by defining how and which records are selected from such objects. In order to reduce the complexity of such a topic I will introduce the joins with regard to tables, the concepts applying also to

A Reporting Guy’s Issues

2010-03-25T03:02:00.001-07:00

For more than 6 years, between many other tasks, I was also the “reporting guy”, taking care of the ad-hoc reporting requirements, building one or two data warehouses and even two reporting frameworks in order to fulfill the reporting requirements within the enterprise I was working for. 99% of the reports were based on the two ERP systems (Oracle e-Business Suite & IFS-iV) the customer had

The Data Warehouse - Part I: An Unorthodox Introduction

2010-03-24T17:00:00.001-07:00

Any discussion on data warehousing topics, even unconventional, can’t avoid to mention the two most widely adopted concepts in data warehousing, B. Inmon vs. R. Kimball’s methodologies. There is lot of ink consumed already on this topic and is difficult to come with something new, however I can insert in between my experience and personal views on the topic. From the beginning I have to state

Defining Concepts: The Stored Procedure Case

2010-03-24T13:20:00.001-07:00

I just started to read the SQL Server Programmers Blog’s post on Stored Procedures and I’m kind of having a problem with the first sentence: "A stored procedure is a group of Transact-Sql statements compiled into a single execution plan". Not sure if the respective statement was supposed to define what a stored procedure is or highlight its most important characteristics; if it's the first

Preventing the Failure of ERP Implementations

2010-03-17T12:45:00.001-07:00

Answering to Why many ERP implementations fall short of expectations and how to prevent failure? discussion in LinkedIn’s Oracle ERP group, discussion based on a Webcast on the same topic, I tried to list a few of the possible reasons for failure as follows: - not understanding what an ERP is about - functionality and intrinsic requirements; - not evaluating/assessing ERP's functionality

Excel for SQL Developers – Part IV: Differences Between Two Datasets

2010-03-16T08:59:00.001-07:00

One of the tasks which appears from time to time on my table is to determine the differences between two datasets coming from different systems or from the same system/source but taken at different points in time. Even if it’s fairly simple to write such a query, especially when the number of attributes is quite small, there is lot of repetitive work that needs to be done because typically

Excel for SQL Developers – Part III: Creating a Stored Procedure from Table’s Metadata

2010-03-15T12:55:00.001-07:00

In a previous post I was showing a simple technique for using stored procedures for inserting/updating data within SQL Server 2005+. From a rough estimation I think that about 80% of the stored procedures built for this purpose could use the same template, without including additional business logic. Given the fact that databases store metadata about their objects, table’s metadata could be

Saving Data With Stored Procedures

2010-03-13T16:41:00.001-08:00

Without going too much into details on the benefits of stored procedures, in this post I will show a simple technique I usually like to use for database updates. It’s not rocket science, I bet many of the developers use similar approaches, and many more don’t! Stored procedures are the ideal place for storing your business logic, including insert/updates, in addition stored procedures being

Excel for SQL Developers – Part II: Insert Statements

2010-03-13T09:17:00.001-08:00

From time to time is required to load a bulk of data into a table, and even if SQL Server and Oracle provides tools for this purpose, there are also occasions when this needs to be done with statements, an insert statement needing to be prepared for each record from the dataset. It sounds like a task that could be easily done in Excel, isn’t it? For this purpose in order to concatenate the

Excel for SQL Developers – Part I: Range to Delimited List of Values

2010-03-13T07:49:00.001-08:00

For many SQL developers Excel could prove to be a useful tool in what concerns not only data analysis and manipulation but also in using its functionality for handling repetitive tasks. One of the simplest such examples I may thing of is the formatting of a list of values for use within a query. Typically users are requesting to provide a report based on a list of values provided in

SSIS 2008: Percentage Sampling Data Flow Transformation

2010-03-13T06:33:00.001-08:00

One of the problems relatively difficult to address in the past was the random sampling within a SQL Server data set. Of course there is always Excel that could be used for this purpose just with a few tricks, though what do you do when working with huge source dataset? As we will see below SSIS 2008’s Percentage Sampling Data Flow Transformation allows easily addressing this problem.

SSIS: Aggregate Data Flow Transformation

2010-03-13T04:55:00.001-08:00

At a first look the SSIS Aggregate Data Flow Transformation doesn’t seem to be so useful given the fact that the same functionality could be easily obtained with an aggregate query, which actually has more flexibility in what concerns data manipulation, however must be not forgotten that the power of transformations reside in the way they are combined in order to solve a problem and not in

SSIS: The Union All Data Flow Transformation

2010-03-12T12:46:00.001-08:00

In the yesterday’s post I was showing how to use the Conditional Split Data Flow Transformation in order to vertically partition a dataset based on a set of constraints, allowing thus to dump the data from each partition to its corresponding table. The inverse problem considers merging together the distributed partitions of the same dataset or distinct similar datasets into a common data set.

SSIS: The Conditional Split Data Flow Transformation

2010-03-11T17:45:00.001-08:00

In Third Magic Class I was showing how to create a Data Flow Task using the SQL Server 2008 Business Intelligence Development Studio (BIDS), the respective SSIS package being based only on Products with the Product Category having the value ‘Bikes’. What if would be needed to create a table for ‘Components’ Product Category, and another one for ‘Clothes’ and ‘Accessories’? Normally this would

Oracle vs. SQL Server: Averages – Part I

2010-03-11T14:16:00.001-08:00

For many people working with averages is not a complicated topic at all, and that’s quite true, though as always it depends on how much you want to complicate everything. The average or the arithmetic mean is defined as the middle value of the values within a data set calculated as the sum of the values divided by the number of values: RDBMS vendors provide similar functionality

SSIS Data Flow Task – Third Magic Class

2010-03-11T11:22:00.001-08:00

Independently of the data sources or destinations used, all the ETL tasks have as cornerstone the simple creation of a Data Flow Task. If in the previous two tutorials I was showing how to export data from an Excel file (see First Magic Class), respectively an Oracle database (see Second Magic Class), using the SQL Server Import and Export Wizard, in this tutorial I will show how you can

TSQL Challenge 24

2010-03-08T13:00:00.001-08:00

Beyond Relational came up with an interesting set of TSQL Challenges contest that reached already its 25th round. Here’s my answer for TSQL Challenge 24, this being the first time I’ve attempted to come with a solution: WITH CTE(LogID, EmpName, Activity, StartTime, TopStartTime, NoMinutes, StartDuration, DiffDuration, HasParentFlag, HasChildFlag, Level) AS ( -- initialization query

Oracle vs. SQL Server: Running Totals

2010-03-06T14:03:00.001-08:00

One of the SQL Server 2005+ functionalities I kind of ignored in the past years is the CROSS APPLY operator that allows returning rows from a table-valued function of a correlated query, allowing for example to calculate running totals, cumulated amounts from the previous records and the current record in a data set. Before the introduction of CROSS APPLY operator such a query could be

More Date-related Functionality – Part II

2010-02-21T09:52:00.001-08:00

Arithmetic Progression Sets In yesterday’s post on the same topic I was showing how to create a sequence of dates on the fly with the help of common table expressions and multi-statement table-valued functions. I was mentioning that similar functionality could be obtained using a numeric sequence (interval), following to create the dates when calling the function. Here’s the function that

More Date-related Functionality – Part I

2010-02-20T09:35:00.001-08:00

Date Intervals Generation In reports is sometimes requested to aggregate transactional data at day level, showing all existing data for a given time interval. This can be easily solved by limiting the time interval in the WHERE clause, though the problem is that it might happen that there are no data in the system for one or more dates and the users want to see that in the report. The

Visual Studio 2010 CR doesn’t support SSIS Projects

2010-02-19T14:30:00.001-08:00

I’ve installed today Visual Studio 2010 CR (Candidate Release) without any problems - nice look and feel, new types of projects targeting several new Microsoft solutions (SharePoint 2010, MS Office 2010, Windows Azure, ASP.NET MVC 2, Silverlight 3), everything looked nice until I wanted to create a SSIS project – unfortunately no support for SSIS 2005/2008 within VS 2010. As it seems I’m not

Just in CASE – Part V: Dynamic Queries (Update)

2010-02-18T09:12:00.001-08:00

In the previous post on the same topic: Just in CASE - Part V: Dynamic queries I shown how the dynamic queries built inside of a stored procedure could be used in order to handle multiple parameters requests combinations. Such technique could be useful also when dealing with more example queries, for example when needed to show the same data but at different levels of detail, or when needed

Just in CASE – Part V: Dynamic Queries

2010-02-18T06:39:00.001-08:00

Stored procedures allows not only to encapsulate logic, enhance queries’ performance by cashing query plans or reduce the risks of injection by using parameters, they allow also altering the structure of a query at runtime, modifying for example the query based on the provided parameters. Such behavior is ideal when needed to base the logic of a query on a list of filters (filter controls) in

Oracle vs. SQL Server: Handling Missing Dates

2010-02-15T14:31:00.001-08:00

What do we do with missing values? How do they influence the data analysis? Two questions each developer should ask users when creating/modifying a report. In general for numbers things are pretty simple, just replace the NULL values with a 0, this impacting the report minimally. With Dates is a little more complicated because maybe is needed to remove records having a certain date null,

Oracle vs. SQL Server: Date Conversion

2010-02-14T16:53:00.001-08:00

During data conversions, data migrations or also during simple processing of data is requested to format dates to a given format, extract a given time unit or convert a string to a date data type. Even if Oracle and SQL Server provides several functions for this purpose, there are small techniques that could help make things easier. In SQL Server the DatePart and DateName functions can

Oracle vs. SQL Server: Date Validation

2010-02-14T07:00:00.001-08:00

When loading data into a table or even when performing date operations with text attributes that store date values, it’s mandatory to check whether the values represent a valid date, otherwise an error will be thrown, fact that could have negative impact on your logic. SQL Server provides the IsDate function for this purpose, the function returning a 1 if the value is a date, and 0 otherwise,

Oracle vs. SQL Server: Date Operations

2010-02-13T20:35:00.001-08:00

Difference between two dates Often is needed to calculate the difference in days (or in any other time unit) between two dates, for example in order to determine the cycle/lead time, further analysis being done based on such data. In SQL Server the difference between two dates can be obtained using the DateDiff function which allows specifying the time unit used. Here’s the use for most

Reports - Ways of Looking at Data – Part IV: Hierarchies - Update

2010-02-12T09:12:00.001-08:00

While commenting on a post on User Defined Functions from SQL Server Programmers Blog I realized that I could use a multi-statement table-valued function in order to simulate recursive processing for ‘Identifying the top-most parent’ problem presented in Reports - Ways of Looking at Data – Part IV: Hierarchies. Some years back, I needed to create the BOM structure for a report based on

Hierarchies – The Oracle Solution

2010-02-09T17:49:00.000-08:00

In yesterday’s post on hierarchy-based reports I was showing, using a BOM example, how hierarchy-related problems could be approached using common table expressions introduced starting with SQL Server 2005. Oracle’s approach to hierarchical queries is based on CONNECT BY clause that allows to specify the join constraint between the table(s) used in the recursive logic and previous output. If

Reports - Ways of Looking at Data – Part IV: Hierarchies

2010-02-08T18:12:00.000-08:00

There is a special type of entities that when related to each other, the relations put together form a hierarchy. It’s the case of assemblies and the components they are made of, a component could be at its turn an assembly formed from smaller components, resulting thus a structure known as Bill of Materials (BoM). Take for example a laptop made of parts like the keyboard, modem, hard disk,

SQL Server to Oracle Data Export – Second Magic Class

2010-02-07T09:16:00.000-08:00

In a previous post I described the steps needed to be followed in order to export the data from SQL Server to Excel, this time I will describe the steps needed in order to export the data from SQL Server to Oracle. For this is requested to have Oracle database product installed on your computer, or in case you are having already an Oracle database server on the network then you’ll need only

The Data-Driven Enterprise

2010-02-06T19:39:00.000-08:00

I read today ‘The Data-Driven Enterprise’ White Paper from Informatica, quite useful paper, especially when it comes from one of the leaders in integration software and services. In this paper the term data-driven enterprise refers to the organizations that are “able to take advantage of their data assets to work faster, better and smarter” [1], in order to achieve this state of art being

Wizarding an SSIS Package – First Magic Class

2010-02-05T13:35:00.000-08:00

Sorry to disappoint you, this it’s not another story about Harry Potter, it’s just a post on how to create an SSIS package without any knowledge about SSIS or the components of an SSIS Package with the help of ‘SQL Server Import and Export Wizard’ available from the SQL Server 2008 Management Studio. Here are the steps needed to be followed in order to create a Package that exports the data

Just in CASE – Part IV: Other Scenarios

2010-02-05T05:34:00.000-08:00

Two days ago I was looking on the Web on random posts to see whether I missed something important related to CASE function and I stumble over an old post of Craig S. Mullins, in one of the examples (See SQL Statement #5) there is a query similar with the one below: As can be seen on more than one branch of a CASE there is a correlated sub-query based on the same table, the aggregated

Just In CASE – Part III: Quest for Performance

2010-02-04T07:46:00.000-08:00

Arranging branches Given the fact that the CASE function stops after the first match, in case there are no two expressions that evaluate to true for any same value, the CASE could be optimized for the best performance by arranging the branches in the order of the highest number of matches. For example taking the logic from the 3rd example from Part I and checking the number of occurrences