SQL Indexes

What is index?

Indexes in databases are similar to indexes in books. In a book, an index allows you to find information quickly without reading the entire book.
Also it is a list of words with the page numbers that contain each word.

Similarly in a database, an index allows the database program to find data in a table without scanning the entire table and is a list of values in a table with the storage locations of rows in the table that contain each value.

Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees.
An index contains an entry with one or more columns (the search key) from each row in a table. A B-tree is sorted on the search key, and can be searched efficiently on any leading subset of the search key.
For example, an index on columns A, B, C can be searched efficiently on A, on A, B, and A, B, C.

The employee table shown below has an index on the emp_id column, following illustration shows; how the index stores each emp_id value and points to the rows of data in the table with each value.

When SQL Server executes a select statement in the employee table based on a specified emp_id value, it recognizes the index for the emp_id column and uses the index to find the data.

If the index is not present, it performs a full table scan starting at the beginning of the table and scaning through each row, searching for the specified emp_id value.

There are two types of Indexes

Clustered

Clustered indexes sort and store the data rows in the table based on their key values. Because the data rows are stored in sorted order on the clustered index key. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order.
The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. If a table has no clustered index, its data rows are stored in a heap.

Indexes are organized as B-trees. Each page in an index holds a page header followed by index rows. Each index row contains a key value and a pointer to either a lower-level page or a data row. Each page in an index is called an index node. The top node of the B-tree is called the root node. The bottom layers of nodes in the index are called the leaf nodes. The pages in each level of the index are linked together in a doubly-linked list. In a clustered index, the data pages make up the leaf nodes. Any index levels between the root and the leaves are collectively known as intermediate levels.

For a clustered index, sysindexes.root points to the top of the clustered index. SQL Server navigates down the index to find the row corresponding to a clustered index key. To find a range of keys, SQL Server navigates through the index to find the starting key value in the range, and then scans through the data pages using the previous or next pointers. To find the first page in the chain of data pages, SQL Server follows the leftmost pointers from the root node of the index.

This illustration shows the structure of a clustered index.

Nonclustered

Nonclustered indexes have a structure completely separate from the data rows. The lowest rows of a nonclustered index contain the nonclustered index key values and each key value entry has pointers to the data rows containing the key value. The data rows are not stored in order based on the nonclustered key.

The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or are clustered. For a heap, a row locator is a pointer to the row. For a table with a clustered index, the row locator is the clustered index key.

Nonclustered indexes can be defined on a table with a clustered index, a heap, or an indexed view; the row locators in nonclustered index rows have two forms:

• If the table is a heap (does not have a clustered index), the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The entire pointer is known as a Row ID.
• If the table does have a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server 2000 makes duplicate keys unique by adding an internally generated value. This value is not visible to users; it is used to make the key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.

Because nonclustered indexes store clustered index keys as their row locators, it is important to keep clustered index keys as small as possible. Do not choose large columns as the keys to clustered indexes if a table also has nonclustered indexes.

About Author:
Harshad Pednekar is budding technology geek, who actively contributes to Systems Plus with his creativity and research on technology. To read more interesting articles from him , please follow: http://harshadpednekar.blogspot.in

Bring Your Own Device (BYOD) Management

Bring Your Own Device (BYOD) is a new phrase which has entered the IT lexicon with the advent of Smartphones, PDA’s, Tablets and other mobile computing devices. With the consumerization of IT these devices, which were invented with the aim of targeting the consumer market, have entered into the business world. BYOD is a concept of employees bringing their own devices into the workplace and using their devices to access the corporate network, organization’s applications, emails and other confidential data of the organization. Employees today carry smartphones, personal laptops and tablets to the workplace for personal or professional use and find it extremely difficult to work in an environment where such devices are restricted. Many companies encourage their employees to bring such mobile computing devices to work because it reduces the cost of hardware and the employees find it work friendly environment. However keeping the corporate data confidential and the organization network secure are major security concerns associate with BYOD. Therefore to implement the BYOD program, the organizations need to embrace the BYOD policy.

BYOD Policy should address the following security requirements for mobile computing devices.

• For which job roles will be the BYOD program applicable.
• Specify the BYOD devices that will be permitted in the corporate environment.
• Specify what services should be accessible, which applications are allowed and which are banned depending on the job responsibilities, since it’s just not about covering the hardware devices but also the software used on the devices. On Windows machine the administrator can create an employee account as limited account type by which the employees can access only installed applications but cannot install new hardware or software on their own. Some program can be installed on users machine which will ask for a password each time they are trying to download anything on their machine. For limiting access on unwanted websites, internet content filtering software’s can be installed on user’s machine and it ensures users don’t have access to unwanted websites in workplace.
• Define information security and access management policies for the various devices.
  The policies should mention the user’s devices should have screen lock / password configured, antivirus software should be installed, activation of firewalls, encryption of corporate data on mobile computing devices, updating security patches regularly etc.
• Policy should be defined in case of device is broken or damaged. Regular backup of the corporate data on the device should be done so that there is no loss of data and important emails if any.
• The BYOD policy should be communicated to all the applicable employees and they should be aware of the serious consequence in case of policy violation and security breach.
• Regularly monitoring the devices can help organizations to identify security breach, check policy violation and ensure device conform to the organizations compliance requirements. Monitoring data transfer from internet to computer and from computer to internet can help in keeping a check whether any confidential data is being transferred to unknown and unauthorized person. This can be on windows machine using a utility called “Resource Monitor”.
• Employee exit policy should specify disabling the corporate email id, removing his access from various other organizations applications as well as removing remote and wifi access to corporate network and deleting the corporate confidential data from the employee’s personal device.
• Include locking down mobile devices and wiping the data on the device in cases where the device has been stolen, lost or the employee is absconding with his device. This will prevent unauthorized access to the corporate network and also prevent confidential data loss.

While BYOD creates a work friendly environment, increases productivity and is cost saving for organizations, it has its own security issues for e.g. lack of physical security controls when the user is not in workplace, the user might buy a new device and sell the old one without confirming that there was no sensitive data stored in it, unauthorized access etc. By implementing a strong BYOD policy the organization can secure their IT environment and confidential data. Organizations can also implement Mobile Device Management and Application Management to address the security issues and make the BYOD program work in favor of the organization.

About Author:

Nikhil Vaishnav is a consultant in Systems Plus Pvt. Ltd. Within Systems Plus, he actively contributes to the areas of Technology and Information Security. He can be contacted at nikhil.v@spluspl.com

Risk Areas in Outsourcing

Outsourcing can be defined as giving contracts out of a business process to a third-party. Outsourcing sometimes involves transferring employees and assets from one firm to another. Outsourcing can be either foreign or domestic and sometimes includes offshoring or relocating the businessto some other country.Outsourcing can turn out be an effective cost-saving strategy if used properly. It is sometimes more affordable to purchase a commodity from a different company with comparatively less cost than it costs the firm to produce it internally.

By adopting Outsourcing the organizations has more opportunity to gain efficiencies, improve its performance, lower the costs, and focus on core competencies and functions. But many companies fail to keep a check on the outsourcing front and as a result of the negligence the business may start to suffer losses. Successful outsourcing is no different from any other business relationship — it requires nurturing and management of both the parties. It is important that both the purchaser and the supplier involved in the outsourcing processes understand each other’s expectations and focus on maintaining a strong communication. Regular monitoring and reporting is required. The organization needs to carefully consider the risks involved in the outsourcing engagement and perform necessary up-front planning in advance of vendor selection. The internal auditors play an important role and they make sure that the risks have been addressed to see that the necessary steps have been taken to ensure the outsourcing relationship is successful. The key risk factors are as follows:

Security/Confidentiality of data – The organization should be very careful about the confidentiality and security of the data. The responsible people should take care of some things to ensure the security and confidentiality of data like:

• Access to all information is limited or not
• Is every storage location know if the data is stored at the vendor side
• What controls and security practices does the vendor need to enforce for assurances that critical information is handled appropriately?
• How does the vendor handle sensitive information

Reputation of the Firm- When a problem occurs, it will be the organization and not the outsourced firm who will have to bear the responsibility no matter where that problem lies or who created it. If the organization has staked its reputation on exceptional customer service and satisfaction then it should consider how these areas might be affected when direct control is outside the core business. When providing back-end processing, the provider can access the customer data on the organization’s behalf which can be harmful if not tracked properly. In such cases, the customer agrees to a relationship with the organization and so the reputation is on stake

Strategy for Outsourcing – The outsourcing process should meet the company’s objective for the process to be successful. The process should comprise of back-office activities and be performed by a vendor with greater expertise and resources, so the outsourcing may allow the organization to focus on its core strengths. If it involves a key business process, those responsible for the decision need to understand how outsourcing fits with company strategy. The organization needs to determine the level of training and management that may be required to make sure the outsourced process works effectively and continues to support the strategy.

Organizational Structure and Composition – The organization’s structure, size, and staffing play a critical role in the success of an outsourcing relationship. We should find a vendor who can react to critical situation quickly. However, this same nimbleness of response can be detrimental if solid controls and activity reporting are required for regulatory purposes, as the vendor may compromise in these areas to achieve efficiency. In addition, differences in internal reporting structures between the company and the outsourcing provider can lead to long-term conflicts if not recognized and addressed early in the process.

Key Processes for Outsourcing process – A key process is one which is central to the services for all business delivers and that has a direct effect on organization’s success or failure, such as transaction or claims processing. There are many potential advantages if we outsource to a BPO, like improving client service, turnaround time of tasks, and profitability. Outsourcing helps the company to focus on more strategic objectives and allow the BPO to handle day-to-day processes. The potential risks is when outsourcing a key process are greatly increased because the outsourcing company has a significant amount of control over how that service is to be delivered to the customers.

About Author:
Shailesh Nambair  is consultant and part of Systems Plus Pvt. Ltd. think tank. He actively contributes to information technology and VMO. He can be contacted at: shailesh.n@spluspl.com

How Transition Affects Overall Outsourcing Relationship

Successful transition is probably the most challenging phase in an outsourcing relationship. Before transition is initiated both vendor / client are planning, estimating and assuming. However, once transition kicks in and actual work starts, both parties begin to realize gaps in their plans, estimations and assumptions. Few important aspects to watch out for during transition are:

• During contract negotiation, transition team (from vendor side and client side) is fully focused on the outsourcing initiative. However, once contract is signed and SLAs finalized important stakeholders may shift their focus on other items on their task list. This may sometimes cause problems when transition starts.
• Once the contract is signed and actual work begins the complexity of transition phase becomes evident. More personnel from vendor and client side join in the daily transition work; this actually tests the robustness of the communication and governance plan. Stress level on both side rises, milestones are missed and deadlines crossed. Client starts to realize the gap between what they envisioned and what vendor understood. On the other hand vendor is constantly struggling to reach steady state as per timeline and also meet the expectations of evolving business. This is the most crucial phase that decides success and failure of transition and over all outsourcing relationship.
• By the time transition is over, client form their opinions regarding vendor. This opinion is completely dependent on how transition was planned and executed. This opinion / view point is difficult to change and sometime reflects throughout the engagement relationship.

Outsourcing relationship which has gone sour during transition can be re-build by initiating dialogues between both parties, involving management teams from both sides, hiring a their party consultant to analyze problems and suggest recommendation. The last resort is to replace entire transition management team from both sides.

About Author:

Tina Nebhnani is consultant and part of Systems Plus Pvt. Ltd. She is a part of consulting team that delivers Sourcing and Vendor Management Office projects. She can be contacted at: tina.n@spluspl.com

Create Cursor using Dynamic SQL Query

As we know, Cursors are a mechanism to explicitly enumerate through the rows of a result set, rather than retrieving it as such. Even though we are advised not to use or to minimize the usage of cursors in SQL, there are times which, it’s the only available option, depending on the task and the situation. But there are times you are required to build the cursor using a dynamic SQL select statement.

Here is how we create Cursor using dynamic SQL Query

Note: While creating a cursor using dynamic query, initially the cursor should be declared and passed on to the dynamic query execution.

/* VARIABLE DECLARATION */

DECLARE @ObjCursor      AS CURSOR

DECLARE @SqlQuery       AS NVARCHAR(MAX)

DECLARE @DQuery         AS NVARCHAR(MAX)

DECLARE @Value_1        AS VARCHAR(50)

DECLARE @Value_2        AS VARCHAR(50)

.

.

.

DECLARE @Value_N        AS VARCHAR(50)

/* CREATE DYNAMIC SQL QUERY */

SET @DQuery       = 'SELECT <Column_1>,<Column2_>, ... <Column_N> FROM <TABLE_NAME>'

SET @SqlQuery     = 'SET @CURSOR = CURSOR FORWARD_ONLY STATIC FOR ' + @DQuery + ' OPEN @CURSOR;'

/* HERE YOU CAN PASS DYNAMIC QUERY TO CURSORS */

EXEC SYS.SP_EXECUTESQL @SqlQuery ,N'@CURSOR CURSOR OUTPUT',@ObjCursor OUTPUT

FETCH NEXT FROM @ObjCursor INTO @Value_1,@Value_2,...,@Value_N

WHILE (@@FETCH_STATUS = 0)

BEGIN

      /*HERE YOU CAN DO ACTUALLY CODING */

     

PRINT  @Value_1,@Value_2,...,@Value_N

   

      FETCH NEXT FROM @ObjCursor INTO @Value_1,@Value_2,...,@Value_N

END

CLOSE       @ObjCursor

DEALLOCATE  @ObjCursor

About Author:

Vishal Kudale  is enthusiast .net developer who works as associate consultant with Systems Plus Pvt. Ltd. He in free time reads and write on various web technologies. He can be contacted at: vishal.kudale@spluspl.com

Vendor / Contract Management

Contract Management is a process of maintaining contracts with customers, vendors, partners or employees. This process involves the negotiation of the terms and conditions and ensuring its compliance. It can be summarized as the process of systematically and efficiently managing contract creation, execution, and analysis. This in turn helps or results in maximizing financial and operational performance and minimizing the risk.

Vendors play a very important role in any kind of organization. The amount of time and money spent on maintaining a vendor will help in the firm’s future success by increasing the amount of satisfied customers reduces the cost and betters the quality. Although vendor/contract management have their stories of prior failed attempts by clients they are vastly different in range and scope, they are all remarkably common in one area – they all became overly complicated.

The – start simple and stay simple approach always works best for all the organizations:

• Centralized view of the vendor contracts – There should always be a centralized view of the contracts with the vendors. Instead of having a file maintained manually at both the ends this works better for both the parties. It helps each of them get better access to the data as and when required according to the needs. Also the other contract details like the documents, warranties, modification history, key dates, and payment information can be accessed and updated directly by authorized users.

• Provide and actively manage vendor insurance certificates – This provision helps in a lot of ways. As the documents are uploaded on the centralized system it can automatically evaluate the expiration date of the contracts.

• Keeping track of invoicing and payment details – The information about the payment details and the invoices in the central system helps the finance department rather the accounts payable and contract admin’s to know financial details of the contract and keep a check on it.

• Managing vendor performance assessment information – Many companies have reported having a bad experience with a vendor only to have another department or office use the same vendor again. Maintaining performance ratings and comments for each contract helps in utilizing this information in sourcing decisions to avoid the poor performing suppliers.

• Alertness is required for the key contracts – The schedule of the various contracts and its milestones or events are registered in the central system. To add to these features there should be a provision to set up custom fields for the important contracts and their alerts like the expiry date, its renewal, warranty expiry and deadlines for exercising contact options.

All the above steps help any organization maintain a good and healthy relation with their vendor and also help in a successful vendor /contract management process thus resulting into the success of the organization.

About Author:
Mihir Sakhle is consultant and part of Systems Plus Pvt. Ltd. He is a part of consulting team that delivers Sourcing and Vendor Managementg Office projects. He can be contacted at: mihir.s@spluspl.com

Lambda Expression in C#

A lambda expression is an anonymous function and it is mostly used to create delegates in LINQ. Simply put, it's a method without a declaration, i.e., access modifier, return value declaration, and name.

Why do we need lambda expressions?

It's shorthand that allows you to write a method in the same place you are going to use it. Especially useful in places where a method is being used only once and the method definition are short. It saves you the effort of declaring and writing a separate method to the containing class.

Benefits:

• Reduced typing. No need to specify the name of the function, its return type, and its access modifier.
• When reading the code you don't need to look elsewhere for the method's definition.

Lambda Expression Syntax:

(Parameters)  =>  Expression (or) statement block

=> separates the parameters and statement body of the anonymous function

Type of Lambda Expression:

There are two type of Lambda expression. They are as follows

Statement Lambda

Statement lambda has a statement block on the right side of the lambda operator "=>"

For an Example:

              y => {return y * y }

Expression Lambda

Expression lambda has only an expression (no return statement or curly braces), on the right side of the lambda operator "=>"

For an Example:

            y => y * y

Features of Lambda Expression:

Lambda expressions themselves do not have type. In fact, there is no concept of a lambda expression in the CLR.

// ERROR: Operator '.' cannot be applied to

// operand of type 'lambda expression'

Type type = ((int x) => x).ToString();

A lambda expression cannot be assigned to an implicitly typed local variable since the lambda expressions do not have type.

// ERROR: Cannot assign lambda expression to an

// implicitly typed local variable

var thing = (x => x);

Jump statements (breaks, goto, continue) are not allowed within anonymous method/lambda expression. Similarly, you cannot jump into the lambda expression/ anonymous method from outside.

Variables defined within a lambda expression are accessible only within the scope of the lambda expression body.

Lambda expressions are used generally with the Func and Action delegates.

Func sqr = x => x * x;

Example Program that uses Lambda Expression:

using System;

class Program

{

    static void Main()

    {

      // Use implicitly typed lambda expression.

      // ... Assign it to a Func instance.

      //

      Func<int, int> func1 = x => x + 1;

      //

      // Use lambda expression with statement body.

      //

      Func<int, int> func2 = x => { return x + 1; };

      //

      // Use formal parameters with expression body.

      //

      Func<int, int> func3 = (int x) => x + 1;

      //

      // Use parameters with a statement body.

      //

      Func<int, int> func4 = (int x) => { return x + 1; };

      //

      // Use multiple parameters.

      //

      Func<int, int, int> func5 = (x, y) => x * y;

      //

      // Use no parameters in a lambda expression.

      //

      Action func6 = () => Console.WriteLine();

      //

      // Use delegate method expression.

      //

      Func<int, int> func7 = delegate(int x) { return x + 1; };

      //

      // Use delegate expression with no parameter list.

      //

      Func<int> func8 = delegate { return 1 + 1; };

      //

// Invoke each of the lambda expressions and delegates we created.

      // ... The methods above are executed.

      //

      Console.WriteLine(func1.Invoke(1));

      Console.WriteLine(func2.Invoke(1));

      Console.WriteLine(func3.Invoke(1));

      Console.WriteLine(func4.Invoke(1));

      Console.WriteLine(func5.Invoke(2, 2));

      func6.Invoke();

      Console.WriteLine(func7.Invoke(1));

      Console.WriteLine(func8.Invoke());

    }

}

Output

2

2

2

2

4

2

 2

In the example, we see the => syntax. This can be read as "goes to." It separates the arguments from the method body of a lambda expression. It is not a comparison operator. The => syntax separates the left from the right.

Left side: This is an empty parameter list, a formal parameter list, or an implicit parameter list from the body.

Right side: This can be a statement list inside curly brackets with a return statement, or an expression.

Func1 through func8 denote anonymous function instances. The Func<TResult> type is anonymous function with one result value and no parameter. The Func<T, TResult> type is a function with one parameter and one result value.

The delegate keyword is used to denote an anonymous function in the C# language. After the delegate keyword, you can use a formal parameter list or even omit the list if there are no parameters.

Finally, the program calls the methods that were all specified inside the method body. The Func generic type and the Action type have an instance method called Invoke. It receives a number of parameters that depends on the type.

Lambda Expression as an Event Handler:

Designer Page (aspx)

<form id="form1" runat="server"><div align="center"><h2>Anonymous Method Example</h2><br /><asp:Label ID="lblmsg" runat="server" ForeColor="Green" Font-Bold="true"></asp:Label><br /><br /><asp:Button ID="btnReset" runat="server" Text="Reset" />  <asp:Button ID="btnSubmit" runat="server" Text="Submit" />
<asp:Button ID="btnCancel" runat="server" Text="Cancel" /></div>
</form>

Code behind (C#)

protected void Page_Load(object sender, EventArgs e)

{

// Click Event handler using Regular method

btnReset.Click += ClickEvent;

// Click Event handler using Anonymous method

btnSubmit.Click += delegate { lblmsg.Text="Submit Button clicked using Anonymous method"; };

// Click Event handler using Lamda expression

btnCancel.Click += (senderobj, eventobj) => { lblmsg.Text = "Cancel Button clicked using Lamda expression"; };

}

protected void ClickEvent(object sender, EventArgs e)

{

    lblmsg.Text="Reset Button clicked using Regular method";

}

Output

About Author:
Anand Sahayaraj is technology lover and is important part of Systems Plus technology Think Tank. He works in Systems Plus Pvt. Ltd. and actively contributes to technology. He can be contacted at: anand.s@spluspl.com