What is an IP address?

      
Every machine on the Internet­ has a unique identifying number, called an IP Address. A typical IP address looks like this:

• 216.27.61.137

­To make it easier for us humans to remember, IP addresses are normally expressed in decimal format as a "dotted decimal number" like the one above. But computers communicate in binary form. Look at the same IP address in binary:

• 11011000.00011011.00111101.10001001

­­The four numbers in an IP address are called octets, because they each have eight positions when viewed in binary form. If you add all the positions together, you get 32, which is why IP addresses are considered 32-bit numbers. Since each of the eight positions can have two different states (1 or 0) the total number of possible combinations per octet is 2⁸ or 256. So each octet can contain any value between 0 and 255. Combine the four octets and you get 2³² or a possible 4,294,967,296 unique values!
Out of the almost 4.3 billion possible combinations, certain values are restricted from use as typical IP addresses. For example, the IP address 0.0.0.0 is reserved for the default network and the address 255.255.255.255 is used for broadcasts.

The octets serve a purpose other than simply separating the numbers. They are used to create classes of IP addresses that can be assigned to a particular business, government or other entity based on size and need. The octets are split into two sections: Net and Host. The Net section always contains the first octet. It is used to identify the network that a computer belongs to. Host (sometimes referred to as Node) identifies the actual computer on the network. The Host section always contains the last octet. There are five IP classes plus certain special addresses:

• Default Network - The IP address of 0.0.0.0 is used for the default network.
  
• Class A - This class is for very large networks, such as a major international company might have. IP addresses with a first octet from 1 to 126 are part of this class. The other three octets are used to identify each host. This means that there are 126 Class A networks each with 16,777,214 (2²⁴ -2) possible hosts for a total of 2,147,483,648 (2³¹) unique IP addresses. Class A networks account for half of the total available IP addresses. In Class A networks, the high order bit value (the very first binary number) in the first octet is always 0.

Net	Host or Node
115.	24.53.107

• Loopback - The IP address 127.0.0.1 is used as the loopback address. This means that it is used by the host computer to send a message back to itself. It is commonly used for troubleshooting and network testing.

Other IP Classes

• Class B - Class B is used for medium-sized networks. A good example is a large college campus. IP addresses with a first octet from 128 to 191 are part of this class. Class B addresses also include the second octet as part of the Net identifier. The other two octets are used to identify each host. This means that there are 16,384 (2¹⁴) Class B networks each with 65,534 (2¹⁶ -2) possible hosts for a total of 1,073,741,824 (2³⁰) unique IP addresses. Class B networks make up a quarter of the total available IP addresses. Class B networks have a first bit value of 1 and a second bit value of 0 in the first octet.

Net	Host or Node
145.24.	53.107

• Class C - Class C addresses are commonly used for small to mid-size businesses. IP addresses with a first octet from 192 to 223 are part of this class. Class C addresses also include the second and third octets as part of the Net identifier. The last octet is used to identify each host. This means that there are 2,097,152 (2²¹) Class C networks each with 254 (2⁸ -2) possible hosts for a total of 536,870,912 (2²⁹) unique IP addresses. Class C networks make up an eighth of the total available IP addresses. Class C networks have a first bit value of 1, second bit value of 1 and a third bit value of 0 in the first octet.

Net	Host or Node
195.24.53.	107

• Class D - Used for multicasts, Class D is slightly different from the first three classes. It has a first bit value of 1, second bit value of 1, third bit value of 1 and fourth bit value of 0. The other 28 bits are used to identify the group of computers the multicast message is intended for. Class D accounts for 1/16^th (268,435,456 or 2²⁸) of the available IP addresses.

Net	Host or Node
224.	24.53.107

• Class E - Class E is used for experimental purposes only. Like Class D, it is different from the first three classes. It has a first bit value of 1, second bit value of 1, third bit value of 1 and fourth bit value of 1. The other 28 bits are used to identify the group of computers the multicast message is intended for. Class E accounts for 1/16^th (268,435,456 or 2²⁸) of the available IP addresses.

Net	Host or Node
240.	24.53.107

• Broadcast - Messages that are intended for all computers on a network are sent as broadcasts. These messages always use the IP address 255.255.255.255.

IPv6

Internet Protocol Version 6 (IPv6) is a version of the Internet Protocol that is designed to succeed Internet Protocol version 4 (IPv4). IPv4 is the first publicly used Internet Protocol and has been in operation since 1981.

IPv6 is an Internet Layer protocol for packet-switched internetworking and provides end-to-end datagram transmission across multiple IP networks. As a datagram service it does not guarantee reliability, a function provided at the Transport Layer. The main driving force for the redesign of Internet Protocol was the foreseeable IPv4 address exhaustion. IPv6 was developed by the Internet Engineering Task Force (IETF), and is described in Internet standard document RFC 2460, published in December 1998.


IPv6 uses a 128-bit address, whereas IPv4 uses only 32 bits. The new address space supports 2¹²⁸ (about 3.4×1038) addresses. This expansion provides considerable flexibility in allocating addresses and routing traffic. It also eliminates the primary need for network address translation (NAT), which gained widespread deployment as an effort to alleviate IPv4 address exhaustion.

For more information on this, please refer here.
     

How VoIP Works - IV

 

VoIP Call Monitoring

VoIP has its distinct advantages and disadvantages. The greatest advantage of VoIP is price and the greatest disadvantage is call quality. For businesses who deploy VoIP phone networks -- particularly those who operate busy call centers (customer service, tech support, telemarketing, et cetera) -- call quality issues are both inevitable and unacceptable. To analyze and fix call quality issues, most of these businesses use a technique called VoIP call monitoring.

VoIP call monitoring, also known as quality monitoring (QM), uses hardware and software solutions to test, analyze and rate the overall quality of calls made over a VoIP phone network. Call monitoring is a key component of a business's overall quality of service (QoS) plan.
Call monitoring hardware and software uses various mathematical algorithms to measure the quality of a VoIP call and generate a score. The most common score is called the mean opinion score (MOS). The MOS is measured on a scale of one to five, although 4.4 is technically the highest score possible on a VoIP network. An MOS of 3.5 or above is considered a "good call".

To come up with the MOS, call monitoring hardware and software analyzes several different call quality parameters, the most common being:

• Latency -- This is the time delay between two ends of a VoIP phone conversation. It can be measured either one-way or round trip. Round-trip latency contributes to the "talk-over effect" experienced during bad VoIP calls, where people end up talking over each other because they think the other person has stopped speaking. A round-trip latency of over 300 millisecond is considered poor.
• Jitter -- Jitter is latency caused by packets arriving late or in the wrong order. Most VoIP networks try to get rid of jitter with something called a jitter buffer that collects packets in small groups, puts them in the right order and delivers them to the end user all at once. VoIP callers will notice a jitter of 50 msec or greater.
• Packet loss -- Part of the problem with a jitter buffer is that sometimes it gets overloaded and late-arriving packets get "dropped" or lost. Sometimes the packets will get lost sporadically throughout a conversation (random loss) and sometimes whole sentences will get dropped (bursty loss). Packet loss is measured as a percentage of lost packets to received packets.

There are two different types of call monitoring: active and passive. Active (or subjective) call monitoring happens before a company deploys its VoIP network. Active monitoring is often done by equipment manufacturers and network specialists who use a company's VoIP network exclusively for testing purposes. Active testing can't occur once a VoIP network is deployed and employees are already using the system.
Passive call monitoring analyzes VoIP calls in real-time while they're being made by actual users. Passive call monitoring can detect network traffic problems, buffer overloads and other glitches that network administrators can fix in network down time.

Another method for call monitoring is recording VoIP phone calls for later analysis. This type of analysis is limited, however, to what can be heard during the call, not what's happening on the actual network. This type of monitoring is usually done by human beings, not computers, and is called quality assurance.
    

How VoIP Works - III

 

VoIP: Codecs

A codec, which stands for coder-decoder, converts an audio signal into compressed digital form for transmission and then back into an uncompressed audio signal for replay. It's the essence of VoIP.
Codecs accomplish the conversion by sampling the audio signal several thousand times per second. For instance, a G.711 codec samples the audio at 64,000 times a second. It converts each tiny sample into digitized data and compresses it for transmission. When the 64,000 samples are reassembled, the pieces of audio missing between each sample are so small that to the human ear, it sounds like one continuous second of audio signal. There are different sampling rates in VoIP depending on the codec being used:

• 64,000 times per second
• 32,000 times per second
• 8,000 times per second

A G.729A codec has a sampling rate of 8,000 times per second and is the most commonly used codec in VoIP.
Codecs use advanced algorithms to help sample, sort, compress and packetize audio data. The CS-ACELP algorithm (CS-ACELP = conjugate-structure algebraic-code-excited linear prediction) is one of the most prevalent algorithms in VoIP. CS-ACELP organizes and streamlines the available bandwidth. Annex B is an aspect of CS-ACELP that creates the transmission rule, which basically states "if no one is talking, don't send any data." The efficiency created by this rule is one of the greatest ways in which packet switching is superior to circuit switching. It's Annex B in the CS-ACELP algorithm that's responsible for that aspect of the VoIP call.
The codec works with the algorithm to convert and sort everything out, but it's not any good without knowing where to send the data. In VoIP, that task is handled by soft switches.
E.164 is the name given to the standard for the North American Numbering Plan (NANP). This is the numbering system that phone networks use to know where to route a call based on the dialed numbers. A phone number is like an address:

(313) 555-1212 313 = State 555 = City 1212 = Street address

The switches use "313" to route the phone call to the area code's region. The "555" prefix sends the call to a central office, and the network routes the call using the last four digits, which are associated with a specific location. Based on that system, no matter where you're in the world, the number combination "(313) 555" always puts you in the same central office, which has a switch that knows which phone is associated with "1212."
The challenge with VoIP is that IP-based networks don't read phone numbers based on NANP. They look for IP addresses, which look like this:

192.158.10.7

IP addresses correspond to a particular device on the network like a computer, a router, a switch, a gateway or a telephone. However, IP addresses are not always static. They're assigned by a DHCP server on the network and change with each new connection. VoIP's challenge is translating NANP phone numbers to IP addresses and then finding out the current IP address of the requested number. This mapping process is handled by a central call processor running a soft switch.

The central call processor is hardware that runs a specialized database/mapping program called a soft switch. Think of the user and the phone or computer as one package -- man and machine. That package is called the endpoint. The soft switch connects endpoints.
Soft switches know:

• Where the network's endpoint is
  
• What phone number is associated with that endpoint
• The endpoint's current IP address

VoIP: Soft Switches and Protocols

 The soft switch contains a database of users and phone numbers. If it doesn't have the information it needs, it hands off the request downstream to other soft switches until it finds one that can answer the request. Once it finds the user, it locates the current IP address of the device associated with that user in a similar series of requests. It sends back all the relevant information to the softphone or IP phone, allowing the exchange of data between the two endpoints.

Protocols
As we've seen, on each end of a VoIP call we can have any combination of an analog, soft or IP phone as acting as a user interface, ATAs or client software working with a codec to handle the digital-to-analog conversion, and soft switches mapping the calls. How do you get all of these completely different pieces of hardware and software to communicate efficiently to pull all of this off? The answer is protocols.
There are several protocols currently used for VoIP. These protocols define ways in which devices like codecs connect to each other and to the network using VoIP. They also include specifications for audio codecs. The most widely used protocol is H.323, a standard created by the International Telecommunication Union (ITU). H.323 is a comprehensive and very complex protocol that was originally designed for video conferencing. It provides specifications for real-time, interactive videoconferencing, data sharing and audio applications such as VoIP. Actually a suite of protocols, H.323 incorporates many individual protocols that have been developed for specific applications.

H.323 Protocol Suite
Video	Audio	Data	Transport
H.261 H.263	G.711 G.722 G.723.1 G.728 G.729	T.122 T.124 T.125 T.126 T.127	H.225 H.235 H.245 H.450.1 H.450.2 H.450.3 RTP X.224.0

As you can see, H.323 is a large collection of protocols and specifications. That's what allows it to be used for so many applications. The problem with H.323 is that it's not specifically tailored to VoIP.
An alternative to H.323 emerged with the development of Session Initiation Protocol (SIP). SIP is a more streamlined protocol, developed specifically for VoIP applications. Smaller and more efficient than H.323, SIP takes advantage of existing protocols to handle certain parts of the process. Media Gateway Control Protocol (MGCP) is a third commonly used VoIP protocol that focuses on endpoint control. MGCP is geared toward features like call waiting. You can learn more about the architecture of these protocols at Protocols.com: Voice Over IP.

One of the challenges facing the worldwide use of VoIP is that these three protocols are not always compatible. VoIP calls going between several networks may run into a snag if they hit conflicting protocols. Since VoIP is a relatively new technology, this compatibility issue will continue to be a problem until a governing body creates a standard universal protocol for VoIP.

VoIP is a vast improvement over the current phone system in efficiency, cost and flexibility. Like any emerging technology, VoIP has some challenges to overcome, but it's clear that developers will keep refining this technology until it eventually replaces the current phone system.

On the next article, we'll talk about VoIP call monitoring.
   

How VoIP Works - II

    

VoIP: Packet Switching

A packet-switched phone network is the alternative to circuit switching. It works like this: While you're talking, the other party is listening, which means that only half of the connection is in use at any given time. Based on that, we can surmise that we could cut the file in half, down to about 4.7 MB, for efficiency. Plus, a significant amount of the time in most conversations is dead air -- for seconds at a time, neither party is talking. If we could remove these silent intervals, the file would be even smaller. Then, instead of sending a continuous stream of bytes (both silent and noisy), what if we sent just the packets of noisy bytes when you created them?
Data networks do not use circuit switching. Your Internet connection would be a lot slower if it maintained a constant connection to the Web page you were viewing at any given time. Instead, data networks simply send and retrieve data as you need it. And, instead of routing the data over a dedicated line, the data packets flow through a chaotic network along thousands of possible paths. This is called packet switching.
While circuit switching keeps the connection open and constant, packet switching opens a brief connection -- just long enough to send a small chunk of data, called a packet, from one system to another. It works like this:

• The sending computer chops data into small packets, with an address on each one telling the network devices where to send them.
• Inside of each packet is a payload. The payload is a piece of the e-mail, a music file or whatever type of file is being transmitted inside the packet.
• The sending computer sends the packet to a nearby router and forgets about it. The nearby router send the packet to another router that is closer to the recipient computer. That router sends the packet along to another, even closer router, and so on.
• When the receiving computer finally gets the packets (which may have all taken completely different paths to get there), it uses instructions contained within the packets to reassemble the data into its original state.

Packet switching is very efficient. It lets the network route the packets along the least congested and cheapest lines. It also frees up the two computers communicating with each other so that they can accept information from other computers, as well.

Advantages of Using VoIP

­
Click "Play" to see how packet switching works.

VoIP technology uses the Internet's packet-switching capabilities to provide phone service. VoIP has several advantages over circuit switching. For example, packet switching allows several telephone calls to occupy the amount of space occupied by only one in a circuit-switched network. Using PSTN, that 10-minute phone call we talked about earlier consumed 10 full minutes of transmission time at a cost of 128 Kbps. With VoIP, that same call may have occupied only 3.5 minutes of transmission time at a cost of 64 Kbps, leaving another 64 Kbps free for that 3.5 minutes, plus an additional 128 Kbps for the remaining 6.5 minutes. Based on this simple estimate, another three or four calls could easily fit into the space used by a single call under the conventional system. And this example doesn't even factor in the use of data compression, which further reduces the size of each call.
Let's say that you and your friend both have service through a VoIP provider. You both have your analog phones hooked up to the service-provided ATAs. Let's take another look at that typical telephone call, but this time using VoIP over a packet-switched network:

1. You pick up the receiver, which sends a signal to the ATA.
2. The ATA receives the signal and sends a dial tone. This lets you know that you have a connection to the Internet.
3. You dial the phone number of the party you wish to talk to. The tones are converted by the ATA into digital data and temporarily stored.
4. The phone number data is sent in the form of a request to your VoIP company's call processor. The call processor checks it to ensure that it's in a valid format.
5. The call processor determines to whom to map the phone number. In mapping, the phone number is translated to an IP address (more on this later). The soft switch connects the two devices on either end of the call. On the other end, a signal is sent to your friend's ATA, telling it to ask the connected phone to ring.
6. Once your friend picks up the phone, a session is established between your computer and your friend's computer. This means that each system knows to expect packets of data from the other system. In the middle, the normal Internet infrastructure handles the call as if it were e-mail or a Web page. Each system must use the same protocol to communicate. The systems implement two channels, one for each direction, as part of the session.
7. You talk for a period of time. During the conversation, your system and your friend's system transmit packets back and forth when there is data to be sent. The ATAs at each end translate these packets as they are received and convert them to the analog audio signal that you hear. Your ATA also keeps the circuit open between itself and your analog phone while it forwards packets to and from the IP host at the other end.
8. You finish talking and hang up the receiver.
9. When you hang up, the circuit is closed between your phone and the ATA.
10. The ATA sends a signal to the soft switch connecting the call, terminating the session. 

Probably one of the most compelling advantages of packet switching is that data networks already understand the technology. By migrating to this technology, telephone networks immediately gain the ability to communicate the way computers do.
It will still be at least a decade before communications companies can make the full switch over to VoIP. As with all emerging technologies, there are certain hurdles that have to be overcome.

Disadvantages of Using VoIP

The current Public Switched Telephone Network is a robust and fairly bulletproof system for delivering phone calls. Phones just work, and we've all come to depend on that. On the other hand, computers, e-mail and other related devices are still kind of flaky. Let's face it -- few people really panic when their e-mail goes down for 30 minutes. It's expected from time to time. On the other hand, a half hour of no dial tone can easily send people into a panic. So what the PSTN may lack in efficiency it more than makes up for in reliability. But the network that makes up the Internet is far more complex and therefore functions within a far greater margin of error. What this all adds up to is one of the major flaws in VoIP: reliability.

• First of all, VoIP is dependent on wall power. Your current phone runs on phantom power that is provided over the line from the central office. Even if your power goes out, your phone (unless it is a cordless) still works. With VoIP, no power means no phone. A stable power source must be created for VoIP.
• Another consideration is that many other systems in your home may be integrated into the phone line. Digital video recorders, digital subscription TV services and home security systems all use a standard phone line to do their thing. There's currently no way to integrate these products with VoIP. The related industries are going to have to get together to make this work.
• Emergency 911 calls also become a challenge with VoIP. As stated before, VoIP uses IP-addressed phone numbers, not NANP phone numbers. There's no way to associate a geographic location with an IP address. So if the caller can't tell the 911 operator where he is located, then there's no way to know which call center to route the emergency call to and which EMS should respond. To fix this, perhaps geographical information could somehow be integrated into the packets.
• Because VoIP uses an Internet connection, it's susceptible to all the hiccups normally associated with home broadband services. All of these factors affect call quality:

                                    - Latency
                                    - Jitter
                                    - Packet loss  

• Phone conversations can become distorted, garbled or lost because of transmission errors. Some kind of stability in Internet data transfer needs to be guaranteed before VoIP could truly replace traditional phones.
  
• VoIP is susceptible to worms, viruses and hacking, although this is very rare and VoIP developers are working on VoIP encryption to counter this.
• Another issue associated with VoIP is having a phone system dependent on individual PCs of varying specifications and power. A call can be affected by processor drain. Let's say you are chatting away on your softphone, and you decide to open a program that saps your processor. Quality loss will become immediately evident. In a worst case scenario, your system could crash in the middle of an important call. In VoIP, all phone calls are subject to the limitations of normal computer issues.

One of the hurdles that was overcome some time ago was the conversion of the analog audio signal your phone receives into packets of data. How it is that analog audio is turned into packets for VoIP transmission? The answer is codecs.


In the next article, we will see more about codecs.
   

How VoIP Works - I

          
If you've never heard of VoIP, get ready to change the way you think about long-distance phone calls. VoIP, or Voice over Internet Protocol, is a method for taking analog audio signals, like the kind you hear when you talk on the phone, and turning them into digital data that can be transmitted over the Internet.
How is this useful? VoIP can turn a standard Internet connection into a way to place free phone calls. The practical upshot of this is that by using some of the free VoIP software that is available to make Internet phone calls, you're bypassing the phone company (and its charges) entirely.

VoIP is a revolutionary technology that has the potential to completely rework the world's phone systems. VoIP providers like Vonage have already been around for a while and are growing steadily. Major carriers like AT&T are already setting up VoIP calling plans in several markets around the United States, and the FCC is looking seriously at the potential ramifications of VoIP service.
Above all else, VoIP is basically a clever "reinvention of the wheel." In this article, we'll explore the principles behind VoIP, its applications and the potential of this emerging technology, which will more than likely one day replace the traditional phone system entirely.
The interesting thing about VoIP is that there is not just one way to place a call. There are three different "flavors" of VoIP service in common use today:

• ATA -- The simplest and most common way is through the use of a device called an ATA (analog telephone adaptor). The ATA allows you to connect a standard phone to your computer or your Internet connection for use with VoIP. The ATA is an analog-to-digital converter. It takes the analog signal from your traditional phone and converts it into digital data for transmission over the Internet. Providers like Vonage and AT&T CallVantage are bundling ATAs free with their service. You simply crack the ATA out of the box, plug the cable from your phone that would normally go in the wall socket into the ATA, and you're ready to make VoIP calls. Some ATAs may ship with additional software that is loaded onto the host computer to configure it; but in any case, it's a very straightforward setup.
  
• IP Phones -- These specialized phones look just like normal phones with a handset, cradle and buttons. But instead of having the standard RJ-11 phone connectors, IP phones have an RJ-45 Ethernet connector. IP phones connect directly to your router and have all the hardware and software necessary right onboard to handle the IP call. Wi-Fi phones allow subscribing callers to make VoIP calls from any Wi-Fi hot spot.
  
• Computer-to-computer -- This is certainly the easiest way to use VoIP. You don't even have to pay for long-distance calls. There are several companies offering free or very low-cost software that you can use for this type of VoIP. All you need is the software, a microphone, speakers, a sound card and an Internet connection, preferably a fast one like you would get through a cable or DSL modem. Except for your normal monthly ISP fee, there is usually no charge for computer-to-computer calls, no matter the distance.

If you're interested in trying VoIP, then you should check out some of the free VoIP software available on the Internet. You should be able to download and set it up in about three to five minutes. Get a friend to download the software, too, and you can start tinkering with VoIP to get a feel for how it works.

Using VoIP:

Chances are good you're already making VoIP calls any time you place a long-distance call. Phone companies use VoIP to streamline their networks. By routing thousands of phone calls through a circuit switch and into an IP gateway, they can seriously reduce the bandwidth they're using for the long haul. Once the call is received by a gateway on the other side of the call, it's decompressed, reassembled and routed to a local circuit switch.
Although it will take some time, you can be sure that eventually all of the current circuit-switched networks will be replaced with packet-switching technology (more on packet switching and circuit switching later). IP telephony just makes sense, in terms of both economics and infrastructure requirements. More and more businesses are installing VoIP systems, and the technology will continue to grow in popularity as it makes its way into our homes. Perhaps the biggest draws to VoIP for the home users that are making the switch are price and flexibility.

With VoIP, you can make a call from anywhere you have broadband connectivity. Since the IP phones or ATAs broadcast their info over the Internet, they can be administered by the provider anywhere there's a connection. So business travelers can take their phones or ATA's with them on trips and always have access to their home phone. Another alternative is the softphone. A softphone is client software that loads the VoIP service onto your desktop or laptop. The Vonage softphone has an interface on your screen that looks like a traditional telephone. As long as you have a headset/microphone, you can place calls from your laptop anywhere in the broadband-connected world.
Most VoIP companies are offering minute-rate plans structured like cell phone bills for as little as $30 per month. On the higher end, some offer unlimited plans for $79. With the elimination of unregulated charges and the suite of free features that are included with these plans, it can be quite a savings.
Most VoIP companies provide the features that normal phone companies charge extra for when they are added to your service plan. VoIP includes:

• Caller ID
• Call waiting
• Call transfer
• Repeat dial
• Return call
• Three-way calling

There are also advanced call-filtering options available from some carriers. These features use caller ID information to allow you make a choice about how calls from a particular number are handled. You can:

• Forward the call to a particular number
• Send the call directly to voice mail
• Give the caller a busy signal
• Play a "not-in-service" message
• Send the caller to a funny rejection hotline

With many VoIP services, you can also check voice mail via the Web or attach messages to an e-mail that is sent to your computer or handheld. Not all VoIP services offer all of the features above. Prices and services vary, so if you're interested, it's best to do a little shopping.
Now that we've looked at VoIP in a general sense, let's look more closely at the components that make the system work. To understand how VoIP really works and why it's an improvement over the traditional phone system, it helps to first understand how a traditional phone system works.

VoIP: Circuit Switching

Existing phone systems are driven by a very reliable but somewhat inefficient method for connecting calls called circuit switching.Circuit switching is a very basic concept that has been used by telephone networks for more than 100 years. When a call is made between two parties, the connection is maintained for the duration of the call. Because you're connecting two points in both directions, the connection is called a circuit. This is the foundation of the Public Switched Telephone Network (PSTN).

Here's how a typical telephone call works:

1. You pick up the receiver and listen for a dial tone. This lets you know that you have a connection to the local office of your telephone carrier.
2. You dial the number of the party you wish to talk to.
3. The call is routed through the switch at your local carrier to the party you are calling.
4. A connection is made between your telephone and the other party's line using several interconnected switches along the way.
5. The phone at the other end rings, and someone answers the call.
6. The connection opens the circuit.
7. You talk for a period of time and then hang up the receiver.
8. When you hang up, the circuit is closed, freeing your line and all the lines in between.

Let's say you talk for 10 minutes. During this time, the circuit is continuously open between the two phones. In the early phone system, up until 1960 or so, every call had to have a dedicated wire stretching from one end of the call to the other for the duration of the call. So if you were in New York and you wanted to call Los Angeles, the switches between New York and Los Angeles would connect pieces of copper wire all the way across the United States. You would use all those pieces of wire just for your call for the full 10 minutes. You paid a lot for the call, because you actually owned a 3,000-mile-long copper wire for 10 minutes.
Telephone conversations over today's traditional phone network are somewhat more efficient and they cost a lot less. Your voice is digitized, and your voice along with thousands of others can be combined onto a single fiber optic cable for much of the journey (there's still a dedicated piece of copper wire going into your house, though). These calls are transmitted at a fixed rate of 64 kilobits per second (Kbps) in each direction, for a total transmission rate of 128 Kbps. Since there are 8 kilobits (Kb) in a kilobyte (KB), this translates to a transmission of 16 KB each second the circuit is open, and 960 KB every minute it's open. In a 10-minute conversation, the total transmission is 9,600 KB, which is roughly equal to 10 megabytes (check out How Bits and Bytes Work to learn about these conversions). If you look at a typical phone conversation, much of this transmitted data is wasted.

On the next article, we'll talk about packet switching.
     

What is a packet?

      
It turns out that everything you do on the Internet involves packets. For example, every Web page that you receive comes as a series of packets, and every e-mail you send leaves as a series of packets. Networks that ship data around in small packets are called packet switched networks.

On the Internet, the network breaks an e-mail message into parts of a certain size in bytes. These are the packets. Each packet carries the information that will help it get to its destination -- the sender's IP address, the intended receiver's IP address, something that tells the network how many packets this e-mail message has been broken into and the number of this particular packet. The packets carry the data in the protocols that the Internet uses: Transmission Control Protocol/Internet Protocol (TCP/IP). Each packet contains part of the body of your message. A typical packet contains perhaps 1,000 or 1,500 bytes.
­ Each packet is then sent off to its destination by the best available route -- a route that might be taken by all the other packets in the message or by none of the other packets in the message. This makes the network more efficient. First, the network can balance the load across various pieces of equipment on a millisecond-by-millisecond basis. Second, if there is a problem with one piece of equipment in the network while a message is being transferred, packets can be routed around the problem, ensuring the delivery of the entire message.
Depending on the type of network, packets may be referred to by another name:

• frame
• block
• cell
• segment 

Network Packet Structure

Most network packets are split into three parts:

• header - The header contains instructions about the data carried by the packet. These instructions may include:
  • Length of packet (some networks have fixed-length packets, while others rely on the header to contain this information)
  • Synchronization (a few bits that help the packet match up to the network)
  • Packet number (which packet this is in a sequence of packets)
  • Protocol (on networks that carry multiple types of information, the protocol defines what type of packet is being transmitted: e-mail, Web page, streaming video)
  • Destination address (where the packet is going)
  • Originating address (where the packet came from)
• payload - Also called the body or data of a packet. This is the actual data that the packet is delivering to the destination. If a packet is fixed-length, then the payload may be padded with blank information to make it the right size.
• trailer - The trailer, sometimes called the footer, typically contains a couple of bits that tell the receiving device that it has reached the end of the packet. It may also have some type of error checking. The most common error checking used in packets is Cyclic Redundancy Check (CRC). CRC is pretty neat. Here is how it works in certain computer networks: It takes the sum of all the 1s in the payload and adds them together. The result is stored as a hexadecimal value in the trailer. The receiving device adds up the 1s in the payload and compares the result to the value stored in the trailer. If the values match, the packet is good. But if the values do not match, the receiving device sends a request to the originating device to resend the packet. 

As an example, let's look at how an e-mail message might get broken into packets. Let's say that you send an e-mail to a friend. The e-mail is about 3,500 bits (3.5 kilobits) in size. The network you send it over uses fixed-length packets of 1,024 bits (1 kilobit). The header of each packet is 96 bits long and the trailer is 32 bits long, leaving 896 bits for the payload. To break the 3,500 bits of message into packets, you will need four packets (divide 3,500 by 896). Three packets will contain 896 bits of payload and the fourth will have 812 bits. Here is what one of the four packets would contain:

Each packet's header will contain the proper protocols, the originating address (the IP address of your computer), the destination address (the IP address of the computer where you are sending the e-mail) and the packet number (1, 2, 3 or 4 since there are 4 packets). Routers in the network will look at the destination address in the header and compare it to their lookup table to find out where to send the packet. Once the packet arrives at its destination, your friend's computer will strip the header and trailer off each packet and reassemble the e-mail based on the numbered sequence of the packets.
     

VPN - Tunneling

     
Most ­VPNs rely on tunneling to create a private network that reaches across the Internet. Essentially, tunneling is the process of placing an entire packet within another packet and sending it over a network. The protocol of the outer packet is understood by the network and both points, called tunnel interfaces, where the packet enters and exits the network.
Tunneling requires three different protocols:

• Carrier protocol - The protocol used by the network that the information is traveling over
• Encapsulating protocol - The protocol (GRE, IPSec, L2F, PPTP, L2TP) that is wrapped around the original data
• Passenger protocol - The original data (IPX, NetBeui, IP) being carried

Tunneling has amazing implications for VPNs. For example, you can place a packet that uses a protocol not supported on the Internet (such as NetBeui) inside an IP packet and send it safely over the Internet. Or you could put a packet that uses a private (non-routable) IP address inside a packet that uses a globally unique IP address to extend a private network over the Internet.

Tunneling: Site-to-Site

­In a site-to-site VPN, GRE (Generic Routing Encapsulation) is normally the e­ncapsulating protocol that provides the framework for how to package the passenger protocol for transport over the carrier protocol, which is typically IP-based. This includes information on what type of packet you are encapsulating and information about the connection between the client and server. Instead of GRE, IPSec in tunnel mode is sometimes used as the encapsulating protocol. IPSec works well on both remote-access and site-to-site VPN's. IPSec must be supported at both tunnel interfaces to use.

Tunneling: Remote-Access

­In a r­emote-access VPN, tunneling normally takes place using PPP. Part of the TCP/IP stack, PPP is the carrier for other IP protocols when communicating over the network between the host computer and a remote system. Remote-access VPN tunneling relies on PPP.
Each of the protocols listed below were built using the basic structure of PPP and are used by remote-access VPN's.

• L2F (Layer 2 Forwarding) - Developed by Cisco, L2F will use any authentication scheme supported by PPP.
• PPTP (Point-to-Point Tunneling Protocol) - PPTP was created by the PPTP Forum, a consortium which includes US Robotics, Microsoft, 3COM, Ascend and ECI Telematics. PPTP supports 40-bit and 128-bit encryption and will use any authentication scheme supported by PPP.
• L2TP (Layer 2 Tunneling Protocol) - L2TP is the product of a partnership between the members of the PPTP Forum, Cisco and the IETF (Internet Engineering Task Force). Combining features of both PPTP and L2F, L2TP also fully supports IPSec.

L2TP can be used as a tunneling protocol for site-to-site VPN's as well as remote-access VPN's. In fact, L2TP can create a tunnel between:

• Client and router
• NAS and router
• Router and router

Think of tunneling as having a computer delivered to you by UPS. The vendor packs the computer (passenger protocol) into a box (encapsulating protocol) which is then put on a UPS truck (carrier protocol) at the vendor's warehouse (entry tunnel interface). The truck (carrier protocol) travels over the highways (Internet) to your home (exit tunnel interface) and delivers the computer. You open the box (encapsulating protocol) and remove the computer (passenger protocol). Tunneling is just that simple!

As you can see, VPNs are a great way for a company to keep its employees and partners connected no matter where they are.
     

VPN Security

       
A well-des­igned VPN uses several methods for keeping your connection and data secure.

• Firewalls
• Encryption
• IPSec
• AAA Server

­ In the following sections, we'll discuss each of these security methods. We'll start with the firewall.

1. Firewalls:

A firewall provides a strong barrier between your private network and the Internet. You can set firewalls to restrict the number of open ports, what type of packets are passed through and which protocols are allowed through. Some VPN products, such as Cisco's 1700 routers, can be upgraded to include firewall capabilities by running the appropriate Cisco IOS on them. You should already have a good firewall in place before you implement a VPN, but a firewall can also be used to terminate the VPN sessions.

2. Encryption:

­Encry­ption is the process of taking all the data that one computer is sending to another and encoding it into a form that only the other computer will be able to decode. Most computer encryption systems belong in one of two categories:

• Symmetric-key encryption
• Public-key encryption

In symmetric-key encryption, each computer has a secret key (code) that it can use to encrypt a packet of information before it is sent over the network to another computer. Symmetric-key requires that you know which computers will be talking to each other so you can install the key on each one. Symmetric-key encryption is essentially the same as a secret code that each of the two computers must know in order to decode the information. The code provides the key to decoding the message. Think of it like this: You create a coded message to send to a friend in which each letter is substituted with the letter that is two down from it in the alphabet. So "A" becomes "C," and "B" becomes "D". You have already told a trusted friend that the code is "Shift by 2". Your friend gets the message and decodes it. Anyone else who sees the message will see only nonsense.

The sending computer encrypts the document with a symmetric key, then encrypts the symmetric key with the public key of the receiving computer. The receiving computer uses its private key to decode the symmetric key. It then uses the symmetric key to decode the document.

Public-key encryption uses a combination of a private key and a public key. The private key is known only to your computer, while the public key is given by your computer to any computer that wants to communicate securely with it. To decode an encrypted message, a computer must use the public key, provided by the originating computer, and its own private key. A very popular public-key encryption utility is called Pretty Good Privacy (PGP), which allows you to encrypt almost anything. You can find out more about PGP at the PGP site.

3. IPSec: 

Internet Protocol Security Protocol (IPSec) provides enhanced security feat­ures such as better encryption algorithms and more comprehensive authentication. 

 

IPSec has two encryption modes: tunnel and transport. Tunnel encrypts the header and the payload of each packet while transport only encrypts the payload. Only systems that are IPSec compliant can take advantage of this protocol. Also, all devices must use a common key and the firewalls of each network must have very similar security policies set up. IPSec can encrypt data between various devices, such as:

• Router to router
• Firewall to router
• PC to router
• PC to server

 4. AAA Servers:

AAA (authentication, authorization and accounting) servers are used for more secure access in a remote-access VPN environment. When a request to establish a session c­omes in from a dial-up client, the request is proxied to the AAA server. AAA then checks the following:

• Who you are (authentication)
• What you are allowed to do (authorization)
• What you actually do (accounting)

The accounting information is especially useful for tracking client use for security auditing, billing or reporting purposes.

     
  

How Virtual Private Networks(VPN) Work

    
The world has changed a lot in the last couple of decades. Instead of simply dealing with local or regional concerns, many businesses now have to think about global markets and logistics. Many companies have facilities spread out across the country or around the world, and there is one thing that all of them need: A way to maintain fast, secure and reliable communications wherever their offices are.

Until fairly recently, this has meant the use of leased lines to maintain a wide area network (WAN). Leased lines, ranging from ISDN(integrated services digital network, 128 Kbps) to OC3 (Optical Carrier-3, 155 Mbps) fiber, provided a company with a way to expand its private network beyond its immediate geographic area. A WAN had obvious advantages over a public network like the Internet when it came to reliability, performance and security. But maintaining a WAN, particularly when using leased lines, can become quite expensive and often rises in cost as the distance between the offices increases.

As the popularity of the Internet grew, businesses turned to it as a means of extending their own networks. First came intranets, which are password-protected sites designed for use only by company employees. Now, many companies are creating their own VPN (virtual private network) to accommodate the needs of remote employees and distant offices.

­B­asically, a VPN is a private network that uses a public network (usually the Internet) to connect remote sites or users together. Instead of using a dedicated, real-world connection such as leased line, a VPN uses "virtual" connections routed through the Internet from the company's private network to the remote site or employee. In this article, you will gain a fundamental understanding of VPN's, and learn about basic VPN components, technologies, tunneling and security.

What Makes a VPN?

A well-designed VPN can greatly benefit a company. For example, it can:

• Extend geographic connectivity
• Improve security
• Reduce operational costs versus traditional WAN
• Reduce transit time and transportation costs for remote users
• Improve productivity
• Simplify network topology
• Provide global networking opportunities
• Provide telecommuter support
• Provide broadband networking compatibility
• Provide faster ROI (return on investment) than traditional WAN

What features are needed in a well-designed VPN? It should incorporate:

• Security
• Reliability
• Scalability
• Network management
• Policy management

Remote-Access VPN

­ Ther­e are two common types of VPN. Remote-access, also called a virtual private dial-up network (VPDN), is a user-to-LAN connection used by a company that has employees who need to connect to the private network from various remote locations. Typically, a corporation that wishes to set up a large remote-access VPN will outsource to an enterprise service provider (ESP). The ESP sets up a network access server (NAS) and provides the remote users with desktop client software for their computers. The telecommuters can then dial a toll-free number to reach the NAS and use their VPN client software to access the corporate network.
A good example of a company that needs a remote-access VPN would be a large firm with hundreds of sales people in the field. Remote-access VPNs permit secure, encrypted connections between a company's private network and remote users through a third-party service provider.

Site-to-Site VPN

Through the use of dedicated equipment and large-scale encryption, a company can connect multiple fixed sites over a public network such as the Internet. Site-to-site VPN's can be one of two types:

• Intranet-based - If a company has one or more remote locations that they wish to join in a single private network, they can create an intranet VPN to connect LAN to LAN.
• Extranet-based - When a company has a close relationship with another company (for example, a partner, supplier or customer), they can build an extranet VPN that connects LAN to LAN, and that allows all of the various companies to work in a shared environment.

In the next article, we well see several methods used by a well designed VPN for keeping your connection and data secure.
     

SQL SERVER - Understanding and Using APPLY - II

   

Introduction

This is the second, and concluding part, of a series of articles looking at the APPLY operator. The first part examined the basic operation of APPLY, discussed query design, worked through a detailed example, and looked briefly at table-valued functions. This part compares APPLY with the familiar JOIN operator, examines the operation of APPLY in more detail, and presents some more complex examples.
One quick note: As explained in Part 1 of this series of articles, the function side of the APPLY can be a user-defined table-valued function or just about any expression that returns rows and columns. For the sake of brevity, all possible forms will be referred to simply as a function throughout this article.

Comparing APPLY with JOIN

Logical comparison

The JOIN operator works by matching rows in two sets. The result of the JOIN contains the columns from both sets. APPLY calls a function for each member of a set, building up the output in stages. The result of the APPLY contains columns from both the input set and the function. There is a logical left-to-right order of processing with APPLY, from the input set to the function. By contrast, a JOIN can be processed in any order by SQL Server.

CROSS and OUTER APPLY

Some readers might have wondered what happens if the function does not produce any rows for a particular input. The answer depends on which form of APPLY is used.
CROSS APPLY behaves similarly to an INNER JOIN: it excludes rows from the input set where the function fails to produce a result.
OUTER APPLY behaves more like a LEFT JOIN. Where the function does not return a row, the input row is still included in the final output, with NULLs in the columns provided by the function.
An example may help to illustrate the difference. Say we are asked to produce a list of students and scores, where the score was at least 80. Thinking about an APPLY solution; it is apparent that the input set is the set of all students, and the function needs to return scores over 80 for a given student.
The solution using CROSS APPLY looks like this:

The above query produces the following results:

There is no result for student #2, because she did not score 80 or above in any subject. If we change the query to use OUTER APPLY, we get this:

The above query produces the following results:

The function still returns no rows for student #2, but this time the final output does include a row, with NULL inserted into any columns contributed by the function.

Implementation and Optimization

SQL Server will always use the Nested Loops logical operator to implement an APPLY operation. This is a consequence of left-to-right processing, and use of column values from the input set within the function. Hash and Merge operators do not support the behaviour required to work with APPLY.
The exception to this rule is if the APPLY is logically equivalent to a JOIN. The query optimizer in SQL Server is clever enough to recognise those occasions when we use the APPLY keyword (perhaps to make the query design easier), but the logical effect of the query is a type of JOIN.
If an APPLY query can be safely transformed to a JOIN, the optimizer might be able to consider a wider range of options to perform the logical operation. (Recall that the APPLY operator is limited to Nested Loops, whereas other JOIN types might use a Hash or Merge operation).
If the optimizer does transform the query, the execution plan produced will be identical to (or trivially different from) the execution plan produced for the equivalent query written as a JOIN.
This is an important point to recognise when working with SQL Server. It is frequently possible to write precisely the same logical query using different syntax. The query optimizer does not normally care too much about the exact keywords we use; the logic of the request determines the execution plan produced.
We might choose to write our query using the APPLY keyword, but SQL Server is still free to transform our request in any way that still produces a provably correct result.

Equivalent Expressions Using APPLY and JOIN

It is frequently possible to write the same logical query using either an APPLY or a JOIN. This section describes two simple examples of this equivalence.

CROSS JOIN

Say we are asked to list every possible combination of student and subject names. The natural way to write the query is with a CROSS JOIN:

The same logical requirement can be expressed as an APPLY:

The input set here is the set of all students. The function returns the set of all subjects. There is no link (correlation) between the function and the input set; if we were to write the function explicitly using the CREATE FUNCTION statement, no parameters would be needed.
This is a perfectly valid query; it just happens to be logically identical to a CROSS JOIN operation. SQL Server produces an identical execution plan for both forms.

LEFT JOIN

In the next example, we are asked to produce a list of student names, and the average score for each student (across all subjects). Here is one way to write a solution using a JOIN:

When constructing the APPLY version, we start with the set of all students, and apply a function that calculates the average grade score for a particular student:

 The execution plans produced by the JOIN and the APPLY are only trivially different. This is a good example of being able to write the same logical requirement in different ways.

Extra Credit

Some readers might have noticed that the last example used a CROSS APPLY, whereas the join used a LEFT JOIN. Why was an OUTER APPLY not required?
The answer is reasonably subtle. Consider what happens with APPLY when we call the function for a student that has no grade scores. What is the average value of no scores? It turns out that the answer is NULL.
The AVG aggregate function returns a NULL if no data is passed to it, and this is the NULL returned by the function. Since the function does return a row (containing the NULL), there is no need for an OUTER APPLY.
The SQL Server query optimizer also knows that AVG will return a NULL if it is presented with no data to aggregate, and chooses to implement the wider operation as a Nested Loops operator, running in left outer join mode:

This is just another example of the optimizer transforming a query request into its logical equivalent.

Tricks and Tips

This last section demonstrates some creative uses of APPLY. There are additional details on each of the queries presented here, in the Resources section at the end of the article.

Expression Aliases

We are now asked to find the average grade score, grouped by the first letter of each student's name. Using a JOIN, you might write something like this:

Notice how we can reference the column alias initial directly in the ORDER BY clause, but we are required to repeat the original expression in the GROUP BY clause.
You have probably written queries like this in the past, and perhaps felt frustrated by the need to repeat expressions in this way. It has always been possible to work around this, using a derived table (or, more recently, with a common-table expression):

We will now write an equivalent query with APPLY. The input set is the set of student names and average scores, and the function calculates the first letter of each student's name:

The expression is only written once (inside the function), and can be referred to by its column alias in the SELECT and GROUP BY clauses.
This example also demonstrates the use of column aliases with APPLY. The function itself always has to have a table alias (iTVF in this case). Columns returned by the function can be named either inside the function, or by using the column alias syntax illustrated above. If both are specified, the outside column alias takes precedence.
For all three implementations, the execution plans are identical.

String Concatenation

We are asked to produce a comma-separated list of all the grade scores for each student. Within each list, the scores should be sorted in descending order.
The input set is the set of all student ids. The function is more complex: It needs to find the grade scores for each student, arrange them in descending order, separate each one with a comma, and finally present the result as a string.
The function achieves all that using a trick with FOR XML PATH:

This is the output:

The first APPLY does all the hard work, returning an XML representation of the ordered comma-separated values, with an extra leading comma.
Rather than convert the XML to text and remove the leading comma in the SELECT clause, a second APPLY is used to perform the expression alias trick described previously.
The input set to the second APPLY is the XML result from the previous APPLY. The function part of the second APPLY converts the XML to a string, and removes the leading comma. Notice that the written order of the APPLY clauses is significant.

Shredding XML

The final example here is one seen quite often on the forums. We have received some new data for the school database, in XML format. The task is to extract the new data to a relational form. This is the new data:

We can use XML data type methods to extract the data. The nodes() method is used to shred the XML to relational form, and the value() method is used to convert individual values to SQL Server data types.

The input set for the first APPLY is the set of XML nodes at the student level. The function returns a set of nodes at the subject level.
The output from the first APPLY is again the input set for the second. The function in the second APPLY extracts column values for use in the SELECT and ORDER BY clauses, using the expression alias trick.
This is the output:

Conclusion

There is some overlap between the APPLY and JOIN operators, and some real differences too. APPLY lends itself particularly well to a divide-and-conquer approach to designing query solutions.
APPLY enables the SQL Server developer to take full advantage of the benefits of in-line table-valued functions. Creating a library of such functions can do much to promote logical encapsulation, consistency, and code-reuse, while giving SQL Server maximum flexibility in searching for an efficient query plan.
  

SQL SERVER - Understanding and Using APPLY - I

   

Introduction

This is the first of a two-part series of articles looking at the APPLY operator. This part sets out to give the reader a good understanding of how to design queries using the APPLY operator, using a detailed example to cover the new ideas presented. There is also a brief look at user-defined table-valued functions; since the concepts involved are central to using APPLY effectively.
Next week, the second part will compare APPLY with the familiar JOIN operator, examine APPLY in more detail, and present more examples.

Understanding APPLY

Overview

APPLY encourages us to take a divide and conquer approach to query writing; it works by building a solution in stages. The method might be familiar to those who have worked with procedural languages like those in the .NET family. As we will see, APPLY works by calling a routine for each member of an input set.

The details

APPLY is named after the process of applying a set of input rows to a table-valued function. In this context, a table-valued function can refer to just about anything that produces rows and columns as its output - so we are not just referring to a user-defined table-valued function here.
APPLY calls the function once for each row from the input. Column values from the input row are available for the function to use. The function returns a table on each call, which always has the same set of columns. The number of rows returned may vary between calls, however. The table produced by the function is appended to the input row. If the function produces more than one row, the input row is duplicated to match. Each member of the input set produces part of the final output; these partial results are combined by APPLY to produce the eventual full result.
If that seems all a bit complex and abstract, do not worry; it is a lot simpler in practice, as shown later in an example.

Query design

APPLY encourages the designer to think in terms of applying a common function to each row of an input set, with intermediate results combining to form the final output. With one exception (which we will come to next), this difference in logical design is largely a convenience feature. Most queries written using APPLY can also be expressed using a JOIN. Part two of this series will examine that statement in more detail.
The exception is where the function used is a user-defined table-valued function (defined using the CREATE FUNCTION statement), which uses values from the input set in its parameter list. This powerful ability is unique to the APPLY operator.
In the next section, we will take a simple problem and solve it using the divide and conquer approach promoted by APPLY. Incidentally, the problem is easily solved using joins, but the point is to emphasise the design approach taken when using APPLY.

Using APPLY

A simple problem

Imagine you are the person responsible for the database at a school. You are asked to produce a report showing grade scores for each male student, in each subject. The following illustration shows the three relevant tables, together with a sample of the data in each.

Writing the function

Frequently, the natural-language description of the problem makes it clear what the input set should be, and what the function should do.
In this case, the input set is the set of all male students. The function is required to list the subjects and scores for one particular student. By applying each member of the input set to the function, the desired result will be built up in stages.
We start by writing a query to return subjects and scores for one student. Notice that instead of embedding a particular value in the query, a variable is used.

Though not strictly necessary (as we will see later on), our next step is to encapsulate this logic in a user-defined table-valued function. The parts carried over from the original query are highlighted in blue.

Notice how easy that is to do - the variable becomes the parameter, and the body of the SELECT statement becomes the definition of the function. We can use the following query to test our new function with a single student id.

 This produces a table with three rows and two columns, as shown below. This is one of the partial results that will later be combined by APPLY to form the final output.

The input set

Now that we have the function, we can move on to produce the input set, which will drive the APPLY. The query to list all male students is trivial:

 

Writing the APPLY

The final step is to APPLY the input set to the function. The APPLY slots into the FROM clause of the input set query, and the function sits to the right of the APPLY operator.

The input set passes student ids, one at a time, to the function through its parameter. Since we are using APPLY, we can pass this value as a direct column reference.
The result of executing that query is:

APPLY and User-Defined Functions

As was mentioned in the section on query design, APPLY is not limited to using user-defined functions created using the CREATE FUNCTION statement. In the previous example, we could have omitted the user-defined function completely, and written the query like this:

Instead of passing the student id to the function as a parameter, the inner WHERE clause now contains a direct reference to a column from the input set. This connection between input set and function is known as a correlation. Some people find it useful to think of this usage of APPLY as a correlated join.
You might be wondering why we went to the trouble of creating a user-defined function in the previous section's example. We will discuss those reasons next.

The advantages of in-line user-defined functions

There are two types of user-defined table-valued function: multi-statement and in-line. For brevity, we will refer to the in-line variety as an iTVF from now on.
An iTVF consists of a single SELECT statement, which can include references to the function's parameters. See CREATE FUNCTION in the SQL Server documentation for syntax details, more examples, and the list of restrictions. You might find it useful to think of an iTVF as a view that accepts parameters. Just as for views, SQL Server expands the definition of an iTVF directly into the query plan of an enclosing query, before optimization is performed.
The effect is that SQL Server is able to apply its full range of optimizations, considering the query as a whole. It is just as if you had written the expanded query out by hand. This makes iTVFs a great way to:

1. Encapsulate logic;
2. Produce compact and readable code;
3. Promote code-reuse;
4. Improve consistency; and
5. Encourage a modular programming style

These advantages can help to reduce the number of bugs, shorten development time, and make training new staff a quicker and easier process. Many professionals maintain a library of iTVFs, for precisely these reasons.

 

Multi-statement functions

A multi-statement user-defined function works quite differently. In particular, it does not share the view-like property of being expanded into the containing query before optimization. For this reason, multi-statement functions tend to perform much less well than iTVFs. If you decide to write a multi-statement function, be sure to test it thoroughly if performance is an important consideration.

End of Part One

As you may have noticed from the preceding code examples, the APPLY operator cannot be used by itself in the FROM clause. There are also two forms of APPLY, CROSS and OUTER. We will discuss these in Part II of this series.
The Resources section below contains heavily annotated versions of all the code featured in this part of the article, together with a script to create the sample data used. It also includes an extra example, showing how to return the TOP N scores for each student.