WCF

SATMAP

SATMAP, an acronym for SATisfaction MAPping, is an artificial intelligence application for matching callers to agents in a contact center, based on a real-time analysis of diverse personality attributes. It is a neural-network based call-mapping engine that connects customers to the contact center agents by taking into account key psychographic and demographic factors. SATMAP is the first ever intelligent call routing system available for contact centers that significantly and quantitatively improves customer satisfaction, reduces average call handle time and increases sales. It is a registered trademark owned by The Resource Group (TRG).

History

SATMAP was founded in 2004 as a wholly owned subsidiary of The Resource Group, a global business process outsourcing and technology company. After providing audited performance improvements to the global operations of TRG, SATMAP has increasingly provided technology solutions to the broader contact center industry since 2007. With SATMAP, the calls no longer need to be routed to the best performing or the longest waiting agent. Instead they can now be routed to the agent that has the best personality match for each caller. Skill Based Routing (SBR), a traditional call routing methodology, has been implemented extensively in the contact center industry. Over the years, skill based routing has proved to be a widely implemented solution for contact centers. Some of the global telecommunication companies following the SBR call routing methodology include Avaya, Nortel, and Cisco etc. SATMAP strives to add intelligence to the existing call routing systems by selecting the best agent from the agent pool based on the demographic and psychographic attributes of both the caller and contact center agents. SATMAP makes the ultimate decision as to which individual agent is assigned to a particular caller, based on complex but highly intuitive personality fits.

SATMAP Solutions

SATMAP has numerous call routing solutions which are based on different optimization metrics, as discussed below:

SATMAP Enterprise (SME) customers are given up to eight different optimization metrics, including the traditional metrics of revenue optimization, cost reduction, and customer satisfaction. With five additional slots, SME customers can optimize diverse metrics including insurance claims pay put, customer collections, medical diagnosis accuracy, and other industry-specific metrics.

Performa enables a proactive approach to contact center management by facilitating dynamic control of operations based on rapidly changing calling conditions. Performa is an entry-level system that is upgradable to SATMAP Enterprise.

TS is a neural-networks- based call routing system tuned specifically to optimize technical support functions at contact centers. TS customers are given options to customize their optimization metrics including those that are critical to determining the success of technical support objectives: truck rolls, equipment replacements and call escalations.

Revmax is used purely as a revenue-enhancement application for contact center environments. It analyzes detailed caller and agent attributes in real-time to determine which caller-agent combination will result in the highest likelihood of a revenue outcome.

L2 is configured to dynamically manage contact center call queues in order to optimize the result of every customer-agent interaction. L2 tracks every call's unique characteristics and detailed caller attributes to create a holistic view of incoming calls. A neural-network algorithm is used to match these incoming calls with relevant agents, based on performance and personality fit ensuring that agents are connected to the calls they can most likely resolve successfully.

SATMAP Features

Connecting caller with the right agent

In contact center environments, purchase behavior is driven by the degree to which agents build empathy and rapport with a caller. This, in turn, is a direct function of the degree to which the personality of agents and callers fit together. By connecting the caller with the right agent, SATMAP caters three different attributes related to client’s business preferences. These goals which are concentrated upon by the SATMAP team are:

• Customer Satisfaction
  In a competitive marketplace where businesses compete for customers, customer satisfaction is seen as a key differentiator and has increasingly become a key element of business strategy. SATMAP reaches the customer satisfaction level by connecting a caller to an agent who has the potential of handling the call in an effective manner that leaves the caller satisfied. It also maintains data on satisfaction score of the agent by getting a feedback from the customers. So in this case the factor that is taken into consideration for the decision making is the Historical Satisfaction Score of the Agent.
• Revenue
  A company’s performance is measured by the extent to which its revenues compare with its expenses, thereby making revenue boost a critical business objective. Revenue can be optimized with more n` more calls resulting in sales i.e. a customer ends up buying a product as a result of successful communication with the agent. Keeping this concept in mind SATMAP considers the following factors when creating agent and caller mapping: historical agent data, background of the agent, customer’s buying propensity, agent mood.
• Cost
  Cost can be reduced by connecting an incoming call to an agent that takes less time in handling the call; ultimately saving agent’s time as well as the cost incurred on the call. Apart from the ability to handle calls in lesser time, an agent who shares a similar background with the customer in terms of language, ethnicity or same age group stands a better chance of conveying the right message in short time. SATMAP mainly focuses on the historical handle time of the agent and agent’s background for cost reduction.

Support for customized algorithm implementation

SATMAP can customize the algorithms that help in decision making based on client’s requirements and preferences. The changes in the algorithm also vary due to the SATMAP solution used to optimize the caller-agent pairings. Thus the algorithms used by SATMAP for the purpose of decision making can easily adapt to the particular choices laid out by the client.

Self–learning Capability

SATMAP is capable of updating and refining itself based on the machine learning algorithm selected for a certain client. This is performed through an integral component which keeps on executing updating cycles after regular intervals. During the course of an update cycle, the performance of an implemented algorithm is analyzed and based on the results, the algorithm can be replaced if required. This self-learning capability serves as a means of better optimization of the overall working of the system.

Support for multifarious switches and related software products

SATMAP works natively with most commercial skill based routing and cloud-routing systems and integrates seamlessly with most major third party technology platforms. SATMAP functions on many different platforms which include switches and other related products.

• Aspect Call Center ACD
• Aspect Rockwell Spectrum
• Asterisk
• Avaya
• Cisco UCCE
• Genesys
• Interactive Intelligence
• Nortel
• Stratadial
• Sytel Softdial Contact Center
• TeleDirect Liberation 6000 Dialer
• trg Dialer

Seamless integration of SATMAP in a contact center

SATMAP integration is a low-impact process and does not require changes to recruitment, training, compensation or other existing business processes of the contact center. SATMAP only utilizes the existing resources that the client has invested in the contact center.

SATMAP failsafe feature

In case SATMAP is unable to cater the call route request, the call continues the normal course which results in the call being sent to a skill queue and assigned to an agent by the PBX. In this way SATMAP executes a failsafe approach for handling calls and ensures that the caller receives the same treatment when waiting for an agent, regardless whether SATMAP is running or not.

SATMAP Architecture

SATMAP interfaces with multiple modern and legacy PBX switches. The communication between switch and SATMAP is managed through a switch interface, responsible for providing a translation between the switch and internal SATMAP events. The data for decision making is continuously refined by SATMAP through the use of several algorithms which assist in filtering calls and agents for SATMAP’s decision making. Some additional information in the form of customer and agent demographic as well as psychographic attributes aids this decision making process. The output i.e. the optimal caller-agent pair is sent back to the switch interface which in turn sends it to PBX for routing. In order to perform its integral functionalities, SATMAP interacts with a number of external systems that support the overall operations of the application. These external systems include:

Agent Information Management System (AIMS)

The data pertaining to the agents is managed by the Agent Information Management System. AIMS is a key component of SATMAP deployment as it maintains the agent survey application which the agents fill out at the onset of their employment in a SATMAP-enabled contact center. This survey becomes the source of the agent’s demographic and psychographic information which is then used to generate optimal caller-agent mappings. Demographic data are the characteristics of a population, like sex, race, age, income, location, disabilities, mobility, educational attainment, home ownership, employment status. Psychographic data, on the other hand, encompass attributes pertaining to lifestyle, attitudes, beliefs, values and personality. The contact center agent enters the data into AIMS by completing a survey consisting of approximately 150 questions. To ensure the accuracy of the data, every 30 days the agent is asked to complete a refresh survey. Both the initial survey and the refresh survey can be customized for specific applications.

SATMAP Portal

The SATMAP Portal is a web based application which provides comprehensive turnkey reporting and allows the management to monitor the real-time performance of a campaign. Reports are available on:

• Agent and customer demographics
• Agent performance
• Call center and campaign performance

Call History Server

The Call History Server is a logical server used to collect and collate call history from one or more sources. To collect the historical call data, the Call History Server uses different data access methods.

Acxiom

Acxiom, a global interactive marketing services company, provides information on the caller demographics and psychographics.

Science Behind SATMAP

A typical contact center consists of a number of human agents, each assigned to a telecommunication device, such as a phone or a computer for conducting email or Internet chat sessions, that is in turn connected to a central switch. Using these devices, the agents are generally used to provide sales, customer service, or technical support to the customers or prospective customers of a contact center or a contact center's clients. Industry research consistently shows that the central driver of customer satisfaction is the degree to which agent and client established a rapport over the course of their interaction. In turn what drives rapport and engagement is the degree to which agent and customer personalities coincide. Through SATMAP intelligent call routing system; this concept is incorporated to deliver best services to the valued customers. SATMAP optimizes the routing of callers to agents in a contact center. In general, contact routings are optimized by routing contacts such that callers are matched with and connected to particular agents in a manner that increases the chances of an optimal interaction that is deemed beneficial to a contact center. This optimal interaction is improved by grading agents and matching a graded agent with a caller. SATMAP increases the chance of an optimal interaction by matching a caller to an agent using a computer model derived from data describing demographic, psychographic, past purchase behavior, or other business-relevant information about a caller, together with data describing demographic, psychographic, or historical performance about an agent. Agent and caller demographic data can comprise any of: gender, race, age, education, accent, income, nationality, ethnicity, area code, zip code, marital status, job status, and credit score. Caller demographic and psychographic data can be retrieved from available databases by using the caller's contact information as an index. Available databases include, but are not limited to, those that are publicly available, or those that are commercially available, or those created by a contact center or a contact center client. Once agent data and caller data have been collected, this data is passed to a computational system. The computational system then, in turn, uses this data in a pattern matching algorithm to create a computer model that matches each agent with each caller and estimates the probable outcome of each matching along a number of optimal interactions, such as the generation of a sale, the duration of contact, or the likelihood of generating an interaction that a customer finds satisfying. The pattern matching algorithm can comprise any correlation algorithm, such as a neural network algorithm or a genetic algorithm. To generally train or otherwise refine the algorithm, actual contact results (as measured for an optimal interaction) are compared against the actual agent and caller data for each contact that occurred. The pattern matching algorithm can then learn, or improve its learning of, how matching certain callers with certain agents will change the chance of an optimal interaction. In this manner, the pattern matching algorithm can then be used to predict the chance of an optimal interaction in the context of matching a caller with a particular set of caller data, with an agent of a particular set of agent data.

Avaya UCID - Demystified

Avaya UCID consists of three parts:

1) Node ID (N)

2) UCID - Sequence number (S)

3) Timestamp (T)

Node ID (N): A number between 1 and 32767. It is a unique number to identify the switch in the network that generated the call.

UCID - Sequence number (S): A number between 1 and 65535. This number uniquely identifies the call in a given node.

Timestamp (T): This time is the number of seconds since epoch (midnight 1 Jan, 1970) at the time of call origination.

UCID Format

Format of UCID is : NNNNNSSSSSTTTTTTTTTT

Where N stands for Node ID, S for UCID-Sequence number and T for timestamp.

CM generated UCID for all calls originating at the switch. If UCID exists for an incoming call, CM stores the UCID without any modification. For incoming calls without UCID, the switch generate a UCID for the call. For calls spanning multiple switches, the UCID is sent along with call information during call setup scross the network (which would imply PRI with QSIG).

Example: Assume a call has the following information stored in its call record

Node ID: 21; UCID - Sequence Number: 5; Timestamp: 946085673

The generated UCID in ASCII format will be: 00021000050946085673

TAPI vs. TSAPI

TAPI is a telephony API developed by Microsoft, TSAPI is a telephony API developed by the company that eventually became Avaya (AT&T -> Lucent -> Avaya). TAPI's design was oriented torward first party call control (i.e. phones), while TSAPI was oriented torward third party call call control (i.e. PBXes and central office switches). TAPI does have some limited support for dealing with the call audio, TSAPI has none. I'd be very surprised if you could find a TAPI TSP for Avaya AES, and you won't be getting it from Avaya if you do.

If all you're interested in is call events, then you can do the same things with both APIs, but the code will be totally different. Most companies that have products that have to talk to both Avaya AES and Cisco Call Manager have created an generic CTI interface for their product and then write code modules (aka drivers) that translate the TSAPI and TAPI call events to their generic event format. In your case, depending on your OS, you can move your TAPI code into a separate DLL/shared library, then create a separate DLL/shared library that implements the same functions but using TSAPI instead of TAPI.Frotunately, a lot of the smaller PBX manufacturers use either TAPI or TSAPI based APIs for their CTI interfaces, so if you have to connect to a third PBX, chances are good that you can reuse one of the two modules.

The 3 Laws of Robotics

When I think of robots or androids, only one stands out in my mind; Data from Star trek Next Generation. Data exhibited humane behavior and, as per Asimov’s definition, was a ‘positronic’ robot. He latently followed the three laws of robotics, penned by Asimov many decades ago. These laws were,

1. A robot may not injure a human being or, through inaction, allow a human being to come to harm.
2. A robot must obey any orders given to it by human beings, with the exception of orders conflicting with the First Law.
3. A robot must protect its own existence as long as such protection does not conflict with the First or Second Law.


Most people are familiar with these laws but what many do not know is that, in Asimov’s own words these laws were analogous to the design of any valuable tool. He postulated,

1. A tool must be safe to use.
2. A tool must perform its function efficiently unless this would harm the user.
3. A tool must remain intact during its use unless its destruction is required for its use or for safety.


In one of his short stories (Evidence), Asimov explained the moral grounding behind these laws. He stated that human beings are generally expected to refrain from harming other human beings (except in times of extreme duress; like war, or to save a greater number). This is equivalent to a robot's First Law. Likewise, society expects individuals to obey instructions from recognized authorities (such as doctors), which equals the Second Law. Finally, humans are generally expected to avoid harming themselves, which is the Third Law.

In the recent decade, technology has come a long way and scientists are in the process of actually setting forth a Robot Ethics Charter. This document would be heavily influenced by the three laws but there is sufficient criticism for them out there to think twice.
Modern experts tend to agree that the Laws are perfect for a good story line but present practical problems. According to them the first law is flawed as it states that a robot cannot 'through inaction, allow a human to come to harm'; it should be noted that a robot has finite knowledge and if anything which harms a human is not in its data bank it will invariably end up harming a human. Further when humans harm humans (e.g. in case of war or accidents); the laws would imply that robots end up taking charge of humanity, in an effort to prevent humanity from harming itself.

The above criticisms aside, robots are a very distinct possibility of the future and there are those who feel some ground rules should be set. In the July/August 2009 issue of IEEE Intelligent Systems, Robin Murphy and David D. Woods proposed "The Three Laws of Responsible Robotics":

1. A human may not deploy a robot without the human-robot work system meeting the highest legal and professional standards of safety and ethics.
2. A robot must respond to humans as appropriate for their roles.
3. A robot must be endowed with sufficient situated autonomy to protect its own existence as long as such protection provides smooth transfer of control, which does not conflict with the First and Second Laws.


The above three laws are also suggestions. No matter what ground rules are set, they will have to be updated and improved constantly as technology progresses. Some day we may have creations which follow the idealistic 3 Laws of Robotics, but for now we will have to contend with the practicalities of the philosophy and the limitations of our technology.

User Experience Design Patterns

User Experience has a lot of impact in the overall usage and eventually the success of any software but practically it is one of the most ignored components in any software development activity. The software engineers out there tend to engineer very sophisticated but complex interfaces without keeping in view the overall usage of it. They tend to come up with a geeky look which the end users don’t really understand.

So considering this importance and need, there have been quite a few efforts to establish Design Patterns related with user experience, but the latest one initiated by Infragistics folks outstands all. Infragistics has launched a User Experience Explorer in the name of Quince. It’s a Silverlight based web application (you need Silverlight plug-in for your web browser and its around 4.5 MB installation package). It provides a very interactive environment to explore the different design patterns related to user experience. You can browse them via tags, user actions, free text search etc. Each of these design patterns is elaborated with text and pictorial examples and that too in an interactive way.

Moreover, you can contribute to all this by sharing more examples for an existing pattern or you can even propose a new design pattern that hasn’t already been listed there.

It’s a must see web app; have a look at it in your free time to get some inspirations and food for thought!

Engineering Enterprise Level Applications using Microsoft .NET Framework

If you have been into engineering medium sized business applications using Microsoft .NET Framework (or even any other framework/language), you must be familiar with ‘n-tier application’ and ‘service oriented architecture’ but the terms ‘dynamic clustering’, ‘horizontal scaling’, ‘fail over clusters’ and ‘interoperability’ might sound a bit fishy. These are the technologies and techniques involved in the engineering of fairly sophisticated, enterprise level, high performance, fault tolerant and horizontally scalable applications.

Tech geeks at Microsoft .NET Framework Developer Center have come up with a great end-to-end sample application in the name of ‘.NET StockTrader’ in order to demonstrate how to engineer enterprise level applications using Microsoft .NET Framework. Some of the distinguishing features of this sample application include

• Load Balancing and Failover clustering
  
• Centralized configuration management of clustered service nodes 
  
• Horizontally scalable via dynamic clustering 
  
• Interoperability with Java and PHP services 
  
• Service host failure detection and automatic restarts 
  
• Full interoperability with J2EE and IBM WebSphere's Trade 6.1 
  

The ‘.NET StockTrader’ sample application has two parts; the core ‘.NET StockTrader’ application and the ‘Configuration Service’ implemented by the ‘.NET StockTrader’ components (web app; business services, and order processing service). The ‘Configuration Service’ is engineered to provide dynamic clustering of service nodes, fail over and load balancing, replication of updates in configuration to allow adding new service nodes on the fly as well as providing the centralized management of configuration data through a single web based interface for the core ‘.NET Stock Trader’ application.

You can download the complete sample application with source code and technical documentation from the official website of ‘.NET Stock Trader’ Sample Application. Although it’s a fairly complex application and does need quite some time to completely absorb and understand it but it’s a great learning experience. I would highly recommend you to start experimenting with it and read and learn more and more about it in order to broaden your tech vision and have insight into engineering sophisticated applications using .NET Framework.

HTTP Compression?

Almost every reader of this blog must be familiar with the term ‘Compression’, but the term ‘HTTP Compression’, might sound a bit strange for quite a few of you.

Today, with the ever increasing tendency of companies providing more and more services over the web; the page load time and the overall responsiveness of the web application, are becoming more and more critical in the adaptability of that web application.

All the web engineers out there must have wondered that despite of their strive to improve the response time of their web application, using techniques like data caching, output caching, minimizing number of web requests, moving JavaScript and CSS in external files, placing JavaScript at bottom and CSS at top and what not; but still there is some thing missing and that’s hindering the overall load time of their web application and affecting the end user’s experience.

Why your web application’s load time is not comparable to the load time of Yahoo and MSN home pages, even though their home pages are showing a lot of dynamic contents? It’s true that their solution is exploiting the full capabilities of hardware too, along with the software but still, there is some thing which even you can configure at the software level to improve the overall page load time of your application in order to improve not only the performance of your web application but to have an improved end user’s experience as well. That software level tweak is called the ‘HTTP Compression’; something similar to zip or rar, but for HTTP responses.

‘HTTP Compression’ is a web standard in which GZip (GNU Zip) or Deflate encoding is applied to the overall payload of the HTTP response by the web server, and at the client end i.e. web browser, the HTTP response is decompressed and rendered to the end user. This result in a significant decrease in the overall network traffic and improves the over all load time of your web application to a very significant extent, with an additional advantage of saving the costs associated with the network bandwidth consumption.

‘HTTP Compression’ is supported by all the modern web browsers and web servers. You can not only compress the static contents (css, js …) but the dynamic contents (.aspx, .asp, .php …) as well. Here is a walkthrough on How to enable HTTP Compression on IIS 6.0 for ASP.NET based web applications. You can definitely Google for enabling this very feature on other web servers as well.

Here are links to the analysis reports, showing the percentage decrease in payload and the improvements in the overall load time for MSN, Yahoo, Facebook and trg tech @ blogspot. Do have a look at these in order to actually appreciate the overall performance gain achieved because of ‘HTTP Compression’ by these web giants.

Interop Forms 2.0

Did you know that Microsoft released the Interop Forms Toolkit 2.0. This toolkit allows developers to create Activex controls that can be hosted in VB as well as forms that can be called from VB. But this toolkit is not limited to VB alone. It can easily be used to create Forms and controls for use in VFP

To download the kit, please visit: Microsoft Interop Forms Toolkit 2.0

For a walkthrough of using Interop 2.0 with VFP9, please visit: Using the Interop Forms Toolkit 2.0 in VFP9 - A Walkthrough


Submitted by Kashif Pervaiz

Extension Methods in VS 2008 (C#)

Extension methods allow adding new methods to the public contract of an existing CLR type, without having to sub-class it or recompiling the original type. VB also supports extension methods.

A simple example would be to check for valid email address. We would probably create a static function and pass the input string

using System.Text.RegularExpressions;

namespace Master
{
public class GenericFunctions
{
public static boolean IsValidEmail(string strIn)
{
return Regex.IsMatch(strIn, "^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$");
}
}
}

//Add the reference
using Master.GenericFunctions;
...

string strEmail = Request.QueryString[“email”];

if (!GenericFunctions.IsValidEmail(strEmail))
MessageBox.Show(“Invalid Email Address”)

Now with the help of extension methods, we can do this…

string strEmail = Request.QueryString[“email”];

if (strEmail.IsValidEmail())
MessageBox.Show(“Invalid Email Address”)

By make the following changes in the GenericFunctions class

using System.Text.RegularExpressions;

public static class GenericFunctions
{
public static boolean IsValidEmail(this string strIn)
{
return Regex.IsMatch(strIn, "^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$");
}
}

The static method has a "this" keyword before the first parameter argument of type string. This tells the compiler that this particular Extension Method should be added to objects of type "string". Within the IsValidEmail () method, we can access all of the public properties/methods/events of the actual string instance that the method is being called on, and return true/false depending on whether it is a valid email or not. Next time we would explore extension methods provided within the System.Linq namespace. Happy journey!


Submitted by Kashif Pervaiz

Meet Windows Presentation Foundation (WPF)

"A new and elegant look for the applications."

Formerly known as Avalon, Windows Presentation Foundation (WPF) is the new graphical subsystem in Windows Vista that provides a holistic means for combining user interface, 2D and 3D graphics, documents, and digital media. Built on the .NET Framework, WPF provides a managed environment for development with the Windows operating system. This takes advantage of the existing investment made by Microsoft in the .NET Framework, and allows developers familiar with .NET technologies to rapidly begin developing applications that leverage WPF.

WPF introduces a new XML-based language to represent UI and user interaction, known as XAML (eXtensible Application Markup Language—pronounced “zammel”). Similar to Macromedia’s MXML specification, within XAML elements from the UI are represented as XML tags. Thus, XAML allows applications to dynamically parse and manipulate UI elements at either compile-time or runtime, providing a flexible model for UI composition.

WPF applications can be deployed as standalone applications or as web-based applications hosted in Internet Explorer. As with smart client applications, web-based WPF applications operate in a partial trust sandbox, which protects the client computer against applications with malicious purpose.
Furthermore, WPF applications hosted in Internet Explorer can exploit the capabilities of local client hardware, providing a rich web experience with 3D, digital media, and more, which is the best argument for web-based applications available today.

Submitted by Mohammed Atif

Have you heard about 'NHibernate'?

NHibernate is an Object-relational mapping (ORM) solution for the Microsoft .NET platform: it provides an easy to use framework for mapping an object-oriented domain model to a traditional relational database. Its purpose is to relieve the developer from a significant amount of relational data persistence-related programming tasks.

NHibernate's primary feature is mapping from .NET classes to database tables (and from CLR data types to SQL data types). NHibernate also provides data query and retrieval facilities. NHibernate generates the SQL commands and relieves the developer from manual data set handling and object conversion, keeping the application portable to most SQL databases, with database portability delivered at very little performance overhead.


NHibernate provides transparent persistence for Plain Old CLR Objects (POCOs). The only strict requirement for a persistent class is a no-argument constructor, which does not have to be public. (Proper behavior in some applications also requires special attention to the Equals() and GetHashCode() methods.)

NHibernate is free as open source software that is distributed under the GNU Lesser General Public License.

What's new in Sql Server 2008 (A string of articles by Irfan AQ)

Table Value Parameters

Another exciting feature of Sql Server 2008 is passing a parameter of table type to a stored procedure or function. Note that it’s parameter of table type, which means that first you have to create the table type and then pass it to the Sproc or function.

Below is a simple example which will create a table type for dventureWorks.HumanResources.Department, and we will create a Sproc which accepts only one parameter of table type and then execute it by passing a variable of new table type:


/* Creating reusable table type */

CREATE TYPE DepartmentType AS TABLE
(
Name VARCHAR (50) NOT NULL,
GroupName VARCHAR (50) NOT NULL
)
GO

/* Creating s Sproc to accept table valued parameter. This will print all the data in the parameter table, but many other operations can also be performed. However, do note that the parameter table cannot be modified */

CREATE PROCEDURE usp_TestTableType
@TableType DepartmentType READONLY
AS
BEGIN
SELECT * FROM @TableType;
END
GO

/* Creating a table variable and inserting four records in it */

DECLARE @TableVariable AS DepartmentType
INSERT INTO @TableVariable (Name, GroupName)
VALUES
('Dept1', 'Group ABC'),
('Dept2', 'Group ABC'),
('Dept3', 'Group XYZ'),
('Dept4', 'Group ABC')

/* Executing stored procedure */

EXEC usp_TestTableType @TableVariable

/* Droping the newly created objects */

DROP PROC usp_TestTableType
DROP TYPE departmenttype


Note that in this Sproc, there are some limitations; first we cannot modify the data in the parameter table henceforth it has to be created as READONLY every time. Secondly, table valued parameters cannot be of output type. Also, these table valued parameters are destroyed as soon as the flow goes out of current scope, therefore, all the insertion or updates on table-valued parameters have to be made in a single batch before passing it to the stored procedure or function.
A good thing is that these table types parameters are not handled in memory. Rather, these are created (materialized) in TEMPDB. This enables us to pass large amount of data as table valued parameters.

What's new in Sql Server 2008 (A string of articles by Irfan AQ)

Declaring and Initializing Multiple Variables

Initializing a variable at declaration time was not possible in SQL 2k5. But this functionality is now available in Sql Server 2k8. We can declare and initialize variables with any constant value or with any expression;

DECLARE
@a int = 777,
@b bit = 0,
@c VARCHAR (10) = 'trg tech',
@d CHAR (3) = Substring('trg tech', 1, 3)

PRINT @a
PRINT @b
PRINT @c
PRINT @d

They indeed are making our lives easy!

Web service VS Remoting

A Web service is equivalent to a static function library. If a client creates an instance of Web service and a member variable of the object with one method call, then attempts to read the value from another method call, the value will be the default value for the variable, not the new value. That's because a Web service doesn't maintain session state without considerable hacking outside the "SOAP" standard.

Remoting feature of .Net however, works with remote objects. If you need the ability to use the remote objects rather than just remote methods, Remoting is the way to go!

Can you identify a pro for Web service when measured against .Net remoting?

Re: What are we aiming to accomplish with this code?

Usama did it guys! We indeed are removing duplicate entries from a sorted array of integers!

Well done Monsiuer :-)

Pivot Table

This seems to be a popular question and quite a few chaps have recently asked me about this.

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table. Here is an example;

USE AdventureWorks
GO
SELECT [CA], [AZ], [TX]
FROM
(
SELECT sp.StateProvinceCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
) p
PIVOT
(
COUNT (StateProvinceCode)
FOR StateProvinceCode
IN ([CA], [AZ], [TX])
) AS Pvt

What's new in Sql Server 2008? (A string of articles by Irfan AQ)

Filtered Indexes (My Favourite!)

As the name shows, filtered indexes are criteria-based indexes. We specify the criteria as a WHERE clause similar to the WHERE clause in a simple SELECT statement. In fact a filtered index is an optimized non-clustered index. Also, clustered indexes cannot have a filter. If properly designed, filtered indexes enhance query performance to great extent. Randy Dyess (Introducing Filtered Indexes) has some interesting demonstration on how filtered indexes can make database developer’s life easy. I was reading another article on filtered indexes in which author was saying that with filtered indexes, days of over-indexing will be over.

Here is a simple example on creating filtered index:

USE AdventureWorks

CREATE NONCLUSTERED INDEX MyFilteredIndex
ON Sales.SalesOrderDetail
(
SalesOrderID ASC,
ProductID ASC
)
WHERE (ModifiedDate < '03/01/2004')

What's new in Sql Server 2008? (A string of articles by Irfan AQ)

Trusted, Productive, and Intelligent; this is what Microsoft claims about SQL Server 2008. The latest release of SQL Server 2008 was in February 2008, which is still a CTP (Community Technology Preview) and it has most certainly caught the attention of database developers. There are a lot of promising features in 2008 release of SQL Server and in this string of articles, I would discuss them briefly to introduce you to this fascinating upgrade!

Inserting Multiple Rows in Single Insert Statement!

With SQL Server 2005, you could not insert multiple rows in a table in a single insert statement (very annoying!). If there are 5 rows to be inserted, for example, you had to write 5 insert statements. This is not a restriction with 2008. We can insert multiple rows in a single statement. The multiple rows are enclosed in parenthesis and separated by commas. This is the same syntax which is available in MySQL. Below is the example:

INSERT INTO
AdventureWorks.HumanResources.Department
(Name
,GroupName
,ModifiedDate)
VALUES
('Software Development', 'Research and Development', GETDATE()),
('trg tech', 'Research and Development', GETDATE()),
('Software Marketing', 'Sales and Marketing', GETDATE())