10 reasons why SQL Server 2008 is going to rock

Just like its predecessor, SQL Server 2008 is taking its sweet time to actually ship.  However, unlike its predecessor, it won't just be a "worthwhile upgrade".  It will kick ass.

Here are the top 10 reasons why.

10.  Plug-in model for SSMS.   SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind.  Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom. 

9.  Inline variable assignment.  I often wondered why, as a language, SQL languishes behind the times.  I mean, it has barely any modern syntactic sugar.  Well, in this version, they are at least scratching the the tip of the iceberg. 

Instead of:

DECLARE @myVar int   SET @myVar = 5

you can do it in one line:

DECLARE @myVar int = 5

Sweet.

8.  C like math syntax.  SET @i += 5.  Enough said.  They finally let a C# developer on the SQL team. 

7.  Auditing.  It's a 10 dollar word for storing changes to your data for later review, debugging or in response to regulatory laws.  It's a thankless and a mundane task and no one is ever excited by the prospect of writing triggers to handle it.  SQL Server 2008 introduces automatic auditing, so we can now check one thing off our to do list.

6.  Compression.  You may think that this feature is a waste of time, but it's not what it sounds like.  The release will offer row-level and page-level compression.  The compression mostly takes place on the metadata.  For instance, page compression will store common data for affected rows in a single place. 

The metadata storage for variable length fields is going to be completely crazy: they are pushing things into bits (instead of bytes).  For instance, length of the varchar will be stored in 3 bits. 

Anyway, I don't really care about space savings - storage is cheap.  What I do care about is that the feature promised (key word here "promises") to reduce I/O and RAM utilization, while increasing CPU utilization.  Every single performance problem I ever dealt with had to do with I/O overloading.  Will see how this plays out.  I am skeptical until I see some real world production benchmarks.

5.  Filtered Indexes.  This is another feature that sounds great - will have to see how it plays out.  Anyway, it allows you to create an index while specifying what rows are not to be in the index.  For example, index all rows where Status != null.  Theoretically, it'll get rid of all the dead weight in the index, allowing for faster queries. 

4.  Resource governor.  All I can say is FINALLY.  Sybase has had it since version 12 (that's last millennium, people).  Basically it allows the DBA to specify how much resources (e.g. CPU/RAM) each user is entitled to.  At the very least, it'll prevent people, with sparse SQL knowledge from shooting off a query with a Cartesian product and bringing down the box.

Actually Sybase is still ahead of MS on this feature.  Its ASE server allows you to prioritize one user over another - a feature that I found immensely useful.

3.  Plan freezing.  This is a solution to my personal pet peeve. Sometimes SQL Server decides to change its plan on you (in response to data changes, etc...).  If you've achieved your optimal query plan, now you can stick with it.  Yeah, I know, hints are evil, but there are situations when you want to take a hammer to SQL Server - well, this is the chill pill.

2.  Processing of delimited strings.   This is awesome and I could have used this feature...well, always.  Currently, we pass in delimited strings in the following manner:

exec sp_MySproc 'murphy,35;galen,31;samuels,27;colton,42'

Then the stored proc needs to parse the string into a usable form - a mindless task.

In 2008, Microsoft introduced Table Value Parameters (TVP). 

CREATE TYPE PeepsType AS TABLE (Name varchar(20), Age int)   DECLARE @myPeeps PeepsType   INSERT @myPeeps SELECT 'murphy', 35   INSERT @myPeeps SELECT 'galen', 31   INSERT @myPeeps SELECT 'samuels', 27   INSERT @myPeeps SELECT 'colton', 42    exec sp_MySproc2 @myPeeps 

And the sproc would look like this:

CREATE PROCEDURE sp_MySproc2(@myPeeps PeepsType READONLY) ...

The advantage here is that you can treat the Table Type as a regular table, use it in joins, etc.  Say goodbye to all those string parsing routines.

1. Intellisense in the SQL Server Management Studio (SSMS).  This has been previously possible in SQL Server 2000 and 2005 with use of 3rd party add-ins like SQL Prompt ($195).  But these tools are a horrible hack at best (e.g. they hook into the editor window and try to interpret what the application is doing). 

Built-in intellisense is huge - it means new people can easily learn the database schema as they go.

There are a ton of other great features - most of them small, but hugely useful.  There is a lot of polishing all over the place, like server resource monitoring right in SSMS, a la Vista. 

Ref.:http://angryhacker.com/blog/archive/2008/06/20/10-reasons-why-sql-server-2008-is-going-to-rock.aspx

 

10 ways to speed up Windows 7

By Scott Lowe

July 16, 2012, 5:26 AM PDT

Takeaway: You don't have to live with a Windows 7 machine that's becoming unbearably slow. Here are 10 basic steps that will optimize system performance.

With Windows 7, Microsoft did a really good job making the system perform well. However, over time, Windows 7 systems can slow down and need some care and feeding to regain their former glory. Further, some Windows 7 features can be leveraged to improve overall system performance. In this article, I will outline 10 steps you can take to boost the performance of your Windows 7 systems.

1: Disable unnecessary services

Not every system service that is running on a stock Windows 7 machine is necessary. A number of services can either be disabled or modified to run only when needed. Once you make these changes, the service no longer has to consume system resources and the system no longer has to spend time starting the service. Earlier this year, I wrote an article here at TechRepublic titled 10+ Windows 7 services you may not need.

2: Reduce the number of startup items

Windows 7 systems eventually begin to suffer under the weight of software that is installed in the normal course of business. Many software titles install more than is necessary and include helper applications designed solely to make the software start up more quickly or facilitate other communication (e.g., iTunes helper). And new software installations might add a permanent presence to the system tray, even if it's not absolutely necessary for the system to function (Steam games, for example).

You could go through your system tool by tool and remove the offending software, but you might want to keep the underlying tool around and just prevent the helper from loading. This and more can be accomplished through the use of MSconfig, a tool that has long been a part of Windows. MSconfig allows you to selectively disable startup items and take other steps toward improving overall system performance.

To use MSconfig, go to Start and in the search box, type MSconfig. From the Startup tab (Figure A), you can disable items. Just be careful about what you choose.

Figure A

Disable items to improve overall system performance.

3: Remove the bloatware installed by vendors

I've long felt that Microsoft's OEMs sometimes actively work against the Redmond behemoth and sully the company's name. Nowhere is this more evident than in the case of what has become known as "bloatware." In the never-ending race to the bottom of the PC market, lower cost PCs have had their profit margins bolstered by OEMs through the inclusion of mostly junk software - short-term trials and the like — that does nothing but add a few dollars of profit while bringing performance to a crawl. Frankly, this is one of the reasons that I believe that Microsoft's Surface announcement, in which Microsoft noted that it would make its own device, is brilliant. The company needs to start with a clean slate in some ways (no pun intended).

If your PC shipped with a bunch of stuff you'll never use, get rid of that software. Generally, you can go to Start | Control Panel | Programs And Features (Figure B) and remove software you no longer plan to use. If the software adds items to the startup process, getting rid of it will make the PC start faster and, ultimately, perform better overall.

Figure B

Use Programs And Features to remove unwanted software.

4: Keep viruses and spyware off your system

If you're running Windows, you need to be running an anti-malware program to keep viruses and spyware off your system. Nothing will ruin good performance like a boatload of spyware. My personal favorite (and free!) tool for combating malware is Microsoft Security Essentials. In my experience, it's been successful in catching bad stuff while not significantly degrading system performance itself.

5: Check your memory

How much RAM do you have? Is your system consuming all or most of your RAM? Does the system page out to disk? If so, you're suffering a massive performance hit, which can be solved by adding more memory to your PC. Greg Shultz explains how to do this by using Resource Monitor.

6: Go solid state

Solid state is all the rage these days, and with good reason. It's fast! More and more laptops and even desktops are moving to the technology because of the performance benefits. Solid state disks use memory cells from which data can be read very quickly, as opposed to the relatively plodding nature of rotational storage. By moving to SSD, you can give your Windows 7 system renewed life — and give yourself a whole new user experience.

But SSDs can be expensive, so you need to be smart about how to use them in the most cost-effective way. See ZDnet's Windows 7 and SSDs: Trimming the fat from your system drive for tips on manking good decisions about how to implement SSD.

7: Ensure that power settings favor performance

This one is easy! When you're plugged in, configure Windows 7's power plans to favor performance over power savings. When you choose to use Windows 7′ high performance power plan, you might increase the computer's performance in some (but not all) circumstances. It really depends on the kind of work you're doing and how often you allow the computer to sit idle.

To change power plans, go to Start | Control Panel | Power Options and choose your power plan settings (Figure C). For more about how these plans operate, see Evaluate the efficiency of Windows 7 power plan settings.

Figure C

Go to Power Options to choose Windows 7 power plan settings.

8: Keep your system defragmented (unless you've followed item 6)

If you're using a traditional spinning disk in your Windows 7 system, you can keep your system operating at peak efficiency by periodically defragmenting the hard drive. If, however, you've opted to go with SSD-based storage, don't do this. First, you won't get any performance benefit and second, you'll significantly reduce the life of that expensive SSD.

Disk defragmentation is scheduled to take place once per week, but you can change this by going to Start | Accessories | System Tools | Disk Defragmenter (Figure D). In addition to changing the schedule, you can run an on-demand defrag from here. You can also run a defrag from the command line instead of from a GUI. Bill Detwiler's video explains how.

Figure D

You can schedule a defrag in the Disk Defragmenter dialog box.

9: Disable or tune search indexing

Windows 7's search is good, but it can also affect system performance. If you really need to run a tool at full tilt, you can disable indexing altogether. Or you can tune the indexer to meet your specific needs, possibly reducing its overall impact on system performance.

10: Use ReadyBoost

Perhaps you don't want to jump into the solid-state game right away but would like some of the benefit that can be had from flash-based storage. Using nothing more than a USB stick, you can do so through a Windows 7 feature known as ReadyBoost. (Note that if you're already using an SSD as your system drive, ReadyBoost won't be available, since there would be no performance gain.)

ReadyBoost allows the system to make use of one of these speedy storage devices as a cache, improving overall performance of the system. The flash storage device that you choose to use for ReadyBoost should meet the following specifications set by Microsoft:

• Capacity of at least 256 MB, with at least 64 kilobytes (KB) of free space
• At least a 2.5 MB/sec throughput for 4-KB random reads
• At least a 1.75 MB/sec throughput for 1MB random writes

Here's another nice feature: If Windows doesn't think ReadyBoost will provide a performance gain, it will tell you and won't let you enable it. In Figure E, you can see that I've opened the properties for a portable USB stick, which I've added to my Windows 7 system. However, Windows knows that the system disk is already fast enough, so ReadyBoost isn't available as an option. If you want to learn more about how to enable and configure ReadyBoost in your system, see Take a closer look at ReadyBoost features in Windows 7.

Figure E

ReadyBoost isn't needed for this system.

Google presentations wants to be your slide making tool of choice

By Mark Kaelin

Takeaway: Google has recently released a new version of Google presentations for their Google Apps suite of office tools that emphasizes collaboration.

Like it or not, the presentation is a necessary form of business communication that just about everyone has to create and deliver at least a few times during their career. The default tool used to create those presentations has for many years been Microsoft PowerPoint. Well, not to be outdone, Google has recently released a new version of Google presentations for their Google Apps suite of office tools.

New features

The primary new features added to Google presentations include:

• Character-by-character collaboration
• Drawing on canvas
• Transitions
• Shape linking
• Better animations

Like all of the Google Apps, the presentations tool takes a minimalist approach to the user interface. When you click Create a new presentation you are presented with the basic slide creation view shown in Figure A.

Figure A

The basic slide creation screen

Just like PowerPoint presentations, themes are often where your slide building starts, and Google presentations has several basic themes to choose from (Figure B), although you can see there are not nearly as many as there are in PowerPoint. (I'll leave it to you to determine if that is a good thing or a bad thing.)

Figure B

There are several basic themes available in Google presentations

Bottom line

Google presentations, even with the new features, is still a fairly basic tool for creating slides. However, it does have one advantage over PowerPoint that could make it more valuable to you and/or your organization and that is the collaboration features. The ability to share your presentation and collaborate on it simultaneously can be a real time saver and productivity booster.

Perhaps Google presentations is worth a try as an alternative to PowerPoint.

How Microsoft could (but probably won't) take over the world again

By Debra Littlejohn Shinder

Takeaway: Microsoft could be uniquely positioned to take over a number of market segments if they properly leveraged some of their existing product lines.

Once upon a time, it was popular to joke (or rant) about how Microsoft was bent on total world domination. Today, that is not so much the case. To the contrary, as I've noted here before, now it seems to be a popular misconception that Microsoft is a failing company.

Despite that belief, Microsoft could be uniquely positioned to take over a number of market segments — if they properly leveraged some of their existing product lines and recent acquisitions. Will they do that? Well, I'm not holding my breath, but I do think they might get it right with one or two of those markets. I guess I'm not the only one who thinks so, since many investment advisers are currently saying to buy Microsoft stock.

Microsoft has its fingers in many pies — some say too many — so there is potential for big success (or a big flop) on a number of different fronts. Here are some areas where I think the company could come to dominate (or continue to do so), if they play their cards right. I said last week that I don't think Microsoft should try to do this by emulating Apple. This week, I'll talk about what I think they should do to achieve domination in at least some parts of the technology world.

Tablets

Microsoft was pushing tablet PCs way back in the early 2000s, and a portable computer that could be operated easily when you weren't sitting at a desk was one of Bill Gates' dreams. They tried, with heavy, expensive slates and convertible laptops, then later with smaller Ultra Mobile PC (UMPCs), and the devices became quite popular in some vertical markets, but never quite caught on in the broader business world or with the general public.

Apple gets the credit for bringing tablets to the masses (on their third try, after the failure of the Newton and the Modbook). The iPad currently enjoys 73.4 percent of the tablet market share, but there are rumblings. Many users don't like the locked-down nature of Apple products. They want to know why they have to buy a whole new device to increase their storage capacity, instead of just popping in a flash card as they can do with most Android tablets. And they'd like it even better if they could connect their tablets to an external USB hard drive as they can do with their Windows laptops.

In fact, it seems that there are a lot of people out there who really, really want Windows on their tablets. That's the conclusion you have to draw from the result of recent surveys by Boston Consulting Group and Forrester, with percentages of those who prefer Windows ranging from 42 to 46 percent, iOS coming in at 16 to 27 percent, and Android at 9 to 20 percent.

That seems to indicate that, if Microsoft does tablets right with Windows 8, they could climb to the top of that market over the next several years. It won't happen overnight, but in typical Microsoft "slow-and-steady" fashion, they could overtake the mighty iPad. This is particularly true with Steve Jobs no longer there to lead Apple.

Phones

We already know that Gartner thinks Windows Phone has a good chance of overtaking the iPhone by 2015. Microsoft's "do over" of its mobile operating system got off to a slow start — in part because of bad experiences with Windows Mobile, in part because many consumers are wary of a first-generation anything, and in part because Windows Phone 7 was lacking many essential features, such as tethering and multitasking. These features (although they were also missing in the first versions of other operating systems) were standard on Android and/or iOS at the time WP7 hit the market. There were also rumors that salespeople with the major cellular carriers were actively discouraging customers from buying Windows phones.

But I think another important reason for slow sales among those "in the know" (who are also influential in what technology their less tech-savvy friends and relatives buy) has been the uncertainty surrounding the Windows Phone 7.x operating system.

There has been a general consensus that, with Windows 8, Microsoft's objective is "one operating system to rule them all" (more on that later). Does that mean the next version of Windows Phone will run Windows 8, and will it be backward-compatible with Windows Phone 7.x? Then there was the Nokia partnership. Will the Windows phones that come out of that be far better than the ones that are available today? I think many folks who are interested are taking a "wait-and-see" approach.

With the Mango (WP 7.5) update, though, Microsoft has proven that they're serious about making Windows Phone a contender. With more than 500 improvements, including the longed-for Wi-Fi tethering and multitasking capabilities, it might just be the beginning of the long road to world phone domination.

Gaming

The Xbox 360 has been on the top of the gaming console heap for the past many months, coming in at 42 percent in September, and the gaming business was credited with being an important factor in the record revenues that Microsoft posted for the most recent quarter. Although the total gaming market has been on the decline since 2008, it's still big business — a $43-billion business in 2010, to be exact. Market forecasts expect it to hold steady and increase slightly over the coming years.

The gaming market is dominated by software sales, and Microsoft is the software company with more experience than almost anyone. A recent trend has been toward games marketed as smartphone and tablet apps (e.g., Angry Birds), an area where Microsoft hasn't had much of a presence.

But with Windows 8 on the horizon and the new push to court Metro developers, that could change. If Microsoft makes Metro a platform that's friendly to game developers, we could see some exciting new low-cost game apps for casual game players, along with the more sophisticated console games that serious gamers prefer.

The integration of Xbox Live on Windows Phone is also a step in the right direction if Microsoft wants to dominate the gaming market. The ability to use your same avatars and play the same games from the phone would certainly attract hard-core gamers. In fact, the idea of using the same "virtual you" (avatar) across all your applications, not just games, is something that might just catch on.

I don't play games, but one of the most interesting and fun things that I did with my Windows Phone was create an avatar that I thought nicely represented my personality and general look. I would love to be able to use it in Internet communications where live video isn't possible or isn't desirable but where some sort of visual presence would add to the interaction.

TV and home entertainment

I've been a big fan of Windows Media Center since the original Windows XP WMC Edition. I was thrilled when Microsoft included it as a standard part of (some editions of) Vista and then Windows 7. I've tried a number of DVRs and PVR software, and I think WMC is the best.

We have a computer with four tuners running WMC on Windows 7 attached to our 65-inch Sharp Aquos in our media room and use it for all our TV recording and a Media Center Extender attached to the bedroom TV so that we can also watch our recorded programs there. We just recently invested in a HD Homerun Prime Ethernet network tuning device so that we can use a CableCARD to view and record premium HD channels on our Windows Media Center PCs.

I've been saddened by rumors that WMC either wouldn't be included in Windows 8 (since dispelled by Steven Sinofsky) or is not the focus of any new development (still up in the air). My hope is that Microsoft is not getting ready to ditch WMC entirely, but perhaps has other plans for it, such as incorporating it more fully into Xbox.

The Xbox can currently function as a Media Center Extender. How about making it a full-fledged Media Center itself that can record programs without being connected to a PC and then stream them (and your music, photos, home videos, Internet TV programs, and movies) to the other TVs in your home via DLNA?

If Microsoft turns the Xbox into much more than just a gaming console (which is certainly the direction they seem to be headed) and makes it a true home entertainment hub that can take the place of your TiVo or cable company-provided DVR, Windows Media Center might finally get the respect it deserves and Microsoft might take over the TV/home entertainment market. Neither Apple TV nor Google TV seems to have made much of a splash in the marketplace, so it's a segment that's there for the taking.

One OS to rule them all?

With the right strategy, Microsoft could pull this off and gain dominance in all these markets, along with the (shrinking but not going away anytime soon) desktop and laptop computing markets. Will Windows 8 be the linchpin that ties it all together? The same Windows that works across all your computing platforms would be a wonderful thing — if it works well on all of them. Microsoft would be positioned to integrate its products so tightly that it would seem silly to buy anything else.

It would also put Microsoft in a position where perhaps they could consider some really innovative ideas. Here's one that's really "out there": What about a smartphone that doesn't depend on the wireless carriers?

Most of the people I talk to hate their carriers. They feel they're overcharged for basic service and nickeled and dimed to death for extras. They hate the way they're locked into contracts in order to get affordable devices. They hate the way the carriers cripple features that are built in to the devices by the vendors or charge extra for you to use them (e.g., Wi-Fi tethering). They hate the new tiered data plans.

If Microsoft put a fully functional Skype on its Windows Phones and perhaps invested in helping to build out ubiquitous low-cost Wi-Fi coverage in all major cities and sold the devices outside the wireless carrier channels, how many people could and would dump their plans with AT&T or Verizon or Sprint? Sure, there would be some technological and regulatory hurdles to overcome, but if we're dreaming about world domination, what better way than to do an end run around the telcos?

The 20 most useful Android smartphone apps of 2011

By Jason Hiner

Takeaway: Jason Hiner provides his list of the top 20 tried-and-true Android smartphone apps that are worth your time to download.

The Android Market may not have as many apps as the iPhone App Store yet, but there are still more than enough to be overwhelmed, and it continues to grow at a breakneck pace. To help you sort through them all, here is my latest list of the 20 most useful Android apps (this is an update of my 2010 Android list). I've also recently updated my list of the most useful iPhone apps and you'll notice several of the same apps on both lists.

Remember that I primarily had business professionals in mind when making this list and also keep in mind that this is a snapshot in time. The Android platform is developing so quickly that I guarantee my home screen will look different a month from now.

Still, here's my list of tried-and-true Android apps that I can highly recommend.

1. Google Voice

Google Voice is a service that is so useful I consider it one of the top benefits of Android itself. The service gives you a phone number that can ring to multiple places or devices and it allows you to access all of your voicemail and text messages from the Web. The Android app integrates even deeper. It can make outgoing calls look like they're coming from your Google Voice number so that you can keep your real mobile number private.

2. Advanced Task Killer

One of the realities of having a multitasking mobile OS is that you have to manage your apps so that they don't hurt performance or battery life. Advanced Task Killer (ATK) is my favorite on Android. It even comes with a widget that you can tap once to kill all open apps and you can also set up ATK to kill all apps at periodic intervals. Some people will argue that task managers are irrelevant and unneeded in Android, but I still prefer to use ATK.

3. Dropbox

Dropbox is a great cloud service that automatically syncs a folder of files between multiple computers (Windows, Mac, or Linux). This app extends Dropbox to Android and interacts with other apps (such as Documents To Go) to open the files. It allows you to access PDFs, image files, and business documents by simply dragging them to a folder on your computer and then you immediately have access to them from your mobile phone, once you have this app installed.

4. Evernote

Once you get used to typing on a virtual keyboard (and it honestly took me over a year to do it), then these devices are great for note-taking, and Evernote is a great note-taking app. It is similar to Dropbox in that it saves data locally but syncs it across all your machines and devices.

5. Taskos

There are plenty of to-do apps to choose from on Android but I now prefer Taskos because of the clean, easy, Android-friendly user experience. It also has a few extras that give it an advantage over apps. The biggest one is voice recognition, which lets you speak a task that the app turns into a to-do item (you might have to correct a word or two).

6. DroidAnalytics

For some reason Google doesn't have an official app for Google Analytics (for either Android or iPhone). The best one I've found on Android is DroidAnalytics. Another good one is mAnalytics.

7. Documents To Go

The free version of Documents To Go offers a great little reader for Microsof Word and Excel files. You can upgrade to the full version (for $15) if you want to be able to create and edit files and add PowerPoint files to the mix. If you do want editing capability, I'd also recommend taking a look at QuickOffice.

8. Google Docs

If you mostly work with Google Docs (including uploading Microsoft Office files to your Google Docs repository) then the only app you'll really need is the Google Docs app. It's a nice mobile implementation of document management, although the one annoyance is that always open up files in a web browser rather than within the app itself, which would be a little smoother.

9. Tripit

I dig Tripit. It is by far the best app I've found for keeping track of all my travel itineraries. It runs on some great backend systems. You simply forward your confirmation emails for your flights, hotels, rental cars, and more to Tripit and it automatically organizes them into trips with all your details and confirmation numbers. Or, if you use Gmail, you can even use a plugin to automatically catch confirmation emails and turn them into Tripit trips.

10. Places

This is an awesome app for finding shops and services near your current location. From restaurants to medical facilities to taxis, this app is very accurate and takes advantage of the business information from Google Local. This app is better than the info you get from a GPS unit (or app) and better than any of the similar apps available on the iPhone. It's also integrated into Google Maps.

11. Astro File Manager

Another one of the great things about Android (if you're a geek or a tinkerer) is that you have lower-level access to the system itself. Astro is an app that lets you navigate the Android file system, which is mostly just interesting, but can be handy once in a while.

12. Speed Test

I'm obsessed with running speed tests to check my bandwidth in various places, both to see 3G/4G fluctuations and to check the quality of Wi-Fi. There are a number of really good speed test apps, but my favorite is the Speedtest.net app. It's generally consistent and it has some of the best graphics and options.

13. Amazon Kindle

I've never completely warmed up to the Amazon Kindle e-reader, but I'm a big fan of the Kindle mobile app. Since it was released I've read a lot more books simply because my smartphone is always with me and I can pull it out and read a few pages anytime I've got a couple minutes free.

14. Google+

I've written a lot about Google+ since it launched in July and I'm pretty active over there (+Jason Hiner). One of the great things that Google did was to release a Google+ Android app at the same time it launched the service as a beta. And, surprisingly, the app was actually pretty good and has been improved since. It immediately became one of my most used mobile apps and definitely stole some of my time away from Android's Twitter app, mostly because Google+ is a little more interactive.

15. TED Air

The TED conference features a meeting of the minds of some of society's most influential thinkers. You'll disagree with some of them since there's a large diversity of viewpoints, but many talks are worth listening to in order to catch the latest creative thinking on society's biggest challenges. The cool thing is that they've taken the videos from the conference and made them freely available on the Web. The TED Air app provides a great way to access the videos on a mobile device. I hope more conferences follow TED's lead on this.

16. Google Goggles

This is a fun app that is a little bit ahead of its time. It does visual searches. You can take pictures of things and then the app tries to tell you what they are. It's limited in its scope but it is pretty cool, and it's definitely a peek into the future. One of the coolest features is the ability to take pictures of text in a foreign language and let the app translate it for you. In a foreign country, this can help you read street signs and avoid going into the wrong bathroom. :-) On a more practical level, Goggles is a QR code reader.

17. Photoshop Express

Photoshop is, of course, the best known photo editor in the world and its mobile app doesn't do anything to hurt that reputation. But while the desktop version is known for having a zillion features, the mobile app is distinguished by its simplicity. It's the best Android (and iPhone) photo editing app for simple crops, brightness adjustments, and sharpens, for example.

18. Audible

As much as I like the Kindle ebooks, I actually consume more books as audiobooks via Audible. With the Audible app you can connect to your Audible library and download over the air. The app also gives you a self-contained player optimized for audiobooks, with a skip-back-30-seconds button and the opportunity to make notes and bookmarks (although I wish the app would store these online so that they could be accessed from the Audible site).

19. Shazam

If you want to impress your friends with a mobile app, show them Shazam. Ever hear a song being played at a store or on the radio and ask yourself, "Oh, what song is that?" That's where Shazam comes in. Just hit the button and let it listen for 15 seconds, query its database, and then return the name of artist and the song. It has about an 80% success rate. This one isn't particularly productive, but it is really cool. (You have to live a little, every once in a while.)

20. Google Finance

This is a great little app that regularly gets overlooked. It connects to your Google Finance account, where you can set up a list of stocks and companies to follow and sort them into groups (portfolios). The app provides three simple tabs — a look at the market, a look at your portfolios, and the latest market news. It even does real-time updates when you have the app open.

Debug and interact with Web apps via Opera Dragonfly

By Tony Patton

Takeaway: Tony Patton provides an overview of Opera Dragonfly's features and explains why it replaced Firebug as his Web development tool of choice.

I have been a big fan of Firebug for quite some time, but I have quickly fallen in love with the Opera alternative Dragonfly. The comprehensive set of Web development tools are finally out of beta and ready for prime time. This article provides an introduction and overview of the tool with a focus on specific features in future articles.

Let's go to the Opera

I liked Opera, but I stopped using the browser when they started charging for it. That strategy was quickly dropped, and the free Opera browser currently sits at version 11.51 and is available for Windows, Mac, and Linux, as well as mobile and tablet platforms.

While it only commands a small percentage of the browser market, Opera offers a lightweight browser with a slick user interface. In addition to the developer toolset, it provides hardware acceleration, stacked tabs, and mouse gestures; it also works great on mobile platforms and claims the fastest JavaScript engine called Carakan. The latest version is now my preferred browser on both my Windows and Mac laptops.

What more do you need?

Opera Dragonfly is installed with the browser; it is available from the Tools | Advanced dropdown menu, a keyboard shortcut (on Windows, it's [Ctrl][Shift][I]), or by right-clicking an element within a Web page and selecting Inspect Element from the popup menu.

Figure A

The Opera Dragonfly toolset is accessible via the Opera Tools dropdown menu. (Click the image to enlarge.)

A quick survey of Opera Dragonfly's features makes you wonder what else a Web developer could possibly need to thoroughly develop, test, and debug Web applications. The following list provides a sampling of these features, which are accessible via the main toolbar in the Opera Dragonfly interface (Figure B).

Figure B

The Opera Dragonfly main toolbar provides access to the main features. (Click the image to enlarge.)

You see all of the features discussed below in Figure C.

• DOM Inspector: The feature is available via the Documents panel in the toolbar. DOM Inspector displays the DOM tree for the current document as interpreted by the Opera browser. The Opera site has a great description of this feature, which they call "View Source on steroids." The option's Style Inspector feature, which is displayed in the lower right window, shows the corresponding style for an element selected in the left window (DOM Inspector).
• JavaScript Debugger: The full-featured debugger is available via the Scripts panel. It provides access to JavaScript for the current Web page and allows you to control flow via breakpoints and so forth. The source code is displayed in the left portion of the window; the right side provides features such as breakpoints, state (watches, call stack, etc.) and allows you to easily search the code.
• Network Inspector: The Network panel opens this feature, which is described as an HTTP dashboard. Network Inspector allows you to view and inspect all HTTP requests associated with the page, as well as a visualization of download times/duration for all resources used by the page; you can view all of this via the Network Log tab. A neat feature is the ability to send your own HTTP requests via the Make Request tab.
• Resources Inspector: The Resources panel selection provides access to this feature. Resources Inspector displays all resource requests initiated by the Web page. It displays the host for the resource and full URI along with type and size. This can be used along with the Network Inspector to see the large resources and their associated download times, allowing you to pinpoint issues.
• Storage Inspector: This feature provides an overview of the client-side storage utilized by the page. This includes cookies and HTML 5 Web storage.
• Error Log: The Errors panel provides access to the Error Log, which is one of the more important features for working with your own Web applications. The panel header includes a number of the total number of errors returned. It displays the error and its source. You can configure the Error Log via Settings to not display certain CSS errors, and the Filter box can be used to refine the error list.
• Console: The console allows you to evaluate JavaScript statements on the fly and inspect objects and properties.
• Remote Debugging: This cool feature allows you to debug separate instances of Opera on the same or other computer, mobile devices, or a television. When used, Remote Debugging listens for connections to a specified IP address and port and connects and passes debugging information across the connection. It debugs remotely just like a local instance.

Figure C

The DOM Inspector opened for the TechRepublic home page. (Click the image to enlarge.)

This list just scratches the surface at what is included in the Opera Dragonfly toolset. Each feature has its own set of functionality. Opera Dragonfly is completely open source, and there is a lot of good documentation for the tools available online.

Feature overload

Opera Dragonfly seems to offer all of the tools necessary to properly debug and interact with Web applications. It follows the path blazed by Firebug and offers even more features, which is why Opera Dragonfly has become my preferred tool. In future posts, I'll cover specific features of Opera Dragonfly in more detail. In the meantime, if you haven't already, I encourage you to install Opera and try Dragonfly.

Five reasons Android is superior to the iPhone

By Jack Wallen

Takeaway: Jack Wallen lists the top five reasons he thinks the Android platform is better than the iPhone.

While on vacation, I was reminded (yet again) how superior the Android platform is to the iPhone. How this happened was simple — during my large family gathering, I had to work on several iPhones but no Android phones. As I worked on the iPhones, it hit me just how inflexible the iPhone platform is and in how many ways the Android mobile is superior.

As with any listing of this nature, every point here could be argued, but I wanted to try to keep this particular listing to facts and not just opinion. So, let's dig in and see how that goes.

1. Application options. With the Android platform, all you have to do is tap the Menu button to get to the application options, even while the application is running. This is very much in line with how PC applications work. However, on the iPhone, you have to go through Settings to get to the application options. Sometimes this is circumvented when iPhone app designers add an Options button within an application — but this leads to inconsistency, because not all applications have options.
2. Updates. For me, this one is the deal breaker for the iPhone. In order to get updates (especially firmware updates) the iPhone must be connected to iTunes. Android, on the other hand, offers two ways to get updates: OTA (Over The Air) or using a third-party tool that allows the upgrade to be installed via a PC. The third-party tool will depend upon the maker of the handset, but most of them work very well. The OTA updates also work almost flawlessly. The only bad experience I've had with OTA updates is with AT&T, because they only allow you to check for updates every 24 hours. This can cause some serious frustration when you KNOW there's an update available, but your handset has yet to see it. But generally speaking, when your provider makes the update available, it will appear and be ready for installation.
3. No iTunes. I have always been very vocal about this. I have a great dislike for iTunes, and there are a lot of reasons for this. It's not intuitive, it's a resource hog, and it's forced upon you by Apple, when you want to fully manage your iPhone. With the Android platform, there is not one single application required to manage your device. In fact, a single Android device can be managed by multiple applications and in multiple platforms.
4. Multitasking. That's right, the Android platform does true multitasking. Sure, Apple claims the iPhone does, but I challenge you to actually manage multitasking on the iPhone platform. With Android, all you have to do is hold down the Home button to see a list of your running applications. From that list, you can switch between apps at any time. There are even third-party Android applications (such as Power Strip) that can be installed that make the task of multitasking even more powerful.
5. Application installation. With the Android platform, you can install apps from the Android Market, from your PC by simply copying the file to the mobile, or even transferring via Bluetooth. In fact, you can install home-grown applications just as easy as "official" applications created by Android developers. For developers, this is a real boon, because you don't have to rely solely on an emulator to make sure your application works properly. Some people argue that this opens up Android for possible malware, but if done carefully, it won't be an issue.

There are many reasons why I personally feel that Android is superior to the iPhone, but the above entries tend to float to the top for me. Yes, there are aspects of mobile life that the iPhone does quite well, but for anyone that prefers a sense of true freedom with their devices, Android is — far and away — the platform of choice.

Four steps for selecting your development technologies

Author: Justin James

One my friends is thinking about starting a programming project, and he asked me "how do I pick my technologies?" with an eye towards performance as well as getting the project done. This person has been writing code on and off for a while, but he hasn't worked on any substantial projects. He isn't wedded to any particular technology, and he has the luxury of a clean slate for this project. While he does have a preference for deploying to Linux due to his experience with it, this is not a final decision.

In this column, I share the advice I gave him. I am not going to recommend any particular technologies, but rather show you my approach for making technology and architectural decisions.

Step 1: Loosely design your application

Agile methodologies have become very popular, and some folks believe that means you don't try to think beyond a two week timeline. But there is a lot of value in preparing a loose design of your project. All you need to do is use a tool that has flowchart capabilities (such as Visio) to provide a high level overview of the logic. You do not need to get bogged down in details like "validate that this field contains at least five characters" or data layouts. But you do need a general idea of what parts of the application will be handling what responsibilities.

Some examples of things that should appear in this diagram include:

• Any major batch processing tasks.
• Where data is stored (files, databases, "the cloud," etc.) and which components retrieve it and expose it to the rest of the application.
• Where significant processing occurs (in the database, in a business logic layer, a Web service, the client, and so on) and what it does.

Step 2: Identify resource usage and characteristics

Once your diagram is complete, we can use it to identify resource usage. The resources that you want to be aware of are CPU, RAM, drive space, and bandwidth. Are you transferring a large amount of data to or from an external Web service? That is going to be a bandwidth resource on the connection between your application and that service. Perhaps you are doing an intense calculation within your database — that would be CPU use within the database. And so on.

Step 3: Determine performance critical areas

Once you know where the resources will be used, you have found your potential performance bottlenecks. These are the places where your technology choices will have the biggest impact. If it turns out that you are storing very little in a database, you have more options for the database. Perhaps you will be performing CPU heavy algorithms in the business logic layer, which points out that you will need a language and platform that supports high-speed calculations. This is a chart I've made which will help you see how this affects your decision making.

	CPU	RAM	Disk space	Bandwidth
Database	DB must be a high performance system like PostgreSQL or Oracle.	All major databases should be able to work with lots of RAM.	Skip the low-end DBs with size limits, like SQL Server Express.	Locate your application in the same server room as the database.
Business layer	Language must be fast, and may need excellent multithreading support.	N/A	Locate the application in the same server room as the disks.	Locate your application in the same server room as the database.
API that you expose to clients	Language must be fast, and may need excellent multithreading support.	N/A	Reconsider your architecture.	Reconsider your strategy.
Client-side software	Language must be fast, and may need excellent multithreading support.	Carefully consider your target market and their client capabilities.	Carefully consider your target market and their client capabilities.	Ensure that target market has the bandwidth (don't sell to consumers in rural areas, for example).
Third-party service	Pick vendor carefully.	N/A	Choose a vendor with low cost storage.	Reconsider your strategy.

Step 4: Scale your needs

Another thing that you can learn from your diagram is where your application needs to scale. If the bulk of your processing needs occur in the client piece of the application, your server architecture can be much more modest, for example.

You will also be able to see what kind of scaling you need. Most databases have clustering capabilities, so if you have a choice, it is often easier and better to push things that need to scale (especially if they require a shared state between requests) into the database where scaling is already handled, or to consider technologies higher up in the stack that also have clustering or scaling built in.

Conclusion

By starting your development process with a lightweight sketch of the application's logic, you will be on the right path to select the best technologies for your needs. There are lots of non-technical considerations (such as your budget, experience in particular technologies, and so on), but you need to start somewhere, and this decision making process will help you narrow down your choices and highlight any problem areas before they come up.

I'd love to hear from you in the comments section below to get your experiences with these kinds of issues.

J.Ja

Additional T-SQL operations in SQL Server 2008

Scalar Operators

Scalar operators are used for operations with scalar values. Transact-SQL supports numeric and Boolean operators as well as concatenation.

There are unary and binary arithmetic operators. Unary operators are + and – (as signs). Binary arithmetic operators are +, –, *, /, and %. (The first four binary operators have their respective mathematical meanings, whereas % is the modulo operator.)

You are reading part 4 from "The basic objects of T-SQL in SQL Server 2008," excerpted from Microsoft SQL Server 2008: A Beginner's Guide, by Dusan Petkovic, copyright 2008, printed with permission from McGraw-Hill Osborne Media.

Boolean operators have two different notations depending on whether they are applied to bit strings or to other data types. The operators NOT, AND, and OR are applied to all data types (except BIT). They are described in detail in Chapter 6.

The bitwise operators for manipulating bit strings are listed here, and Example 4.8 shows how they are used:

• ~ Complement (i.e., NOT)
• & Conjunction of bit strings (i.e., AND)
• | Disjunction of bit strings (i.e., OR)
• ^ Exclusive disjunction (i.e., XOR or Exclusive OR)

Example 4.8
~(1001001) = (0110110)
(11001001) | (10101101) = (11101101)
(11001001) & (10101101) = (10001001)
(11001001) ^ (10101101) = (01100100)

The concatenation operator + can be used to concatenate two character strings or bit strings.

Global Variables

Global variables are special system variables that can be used as if they were scalar constants. Transact-SQL supports many global variables, which have to be preceded by the prefix @@. The following table describes several global variables. (For the complete list of all global variables, see Books Online.)

Variable	Explanation
@@CONNECTIONS	Returns the number of login attempts since starting the system.
@@CPU_BUSY	Returns the total CPU time (in units of milliseconds) used since starting the system.
@@ERROR	Returns the information about the return value of the last executed Transact-SQL statement.
@@IDENTITY	Returns the last inserted value for the column with the IDENTITY property (see Chapter 6).
@@LANGID	Returns the identifier of the language that is currently used by the database system.
@@LANGUAGE	Returns the name of the language that is currently used by the database system.
@@MAX_CONNECTIONS	Returns the maximum number of actual connections to the system.
@@PROCID	Returns the identifier for the stored procedure currently being executed.
@@ROWCOUNT	Returns the number of rows that have been affected by the last Transact-SQL statement executed by the system.
@@SERVERNAME	Retrieves the information concerning the local database server. This information contains, among other things, the name of the server and the name of the instance.
@@SPID	Returns the identifier of the server process.
@@VERSION	Returns the current version of the database system software.

NULL Values

A NULL value is a special value that may be assigned to a column. This value is normally used when information in a column is unknown or not applicable. For example, in the case of an unknown home telephone number for a company's employee, it is recommended that the NULL value be assigned to the home_telephone column.

Any arithmetic expression results in a NULL if any operand of that expression is itself a NULL value. Therefore, in unary arithmetic expressions (if A is an expression with a NULL value), both +A and –A return NULL. In binary expressions, if one (or both) of the operands A or B has the NULL value, A + B, A – B, A * B, A / B, and A % B also result in a NULL. (The operands A and B have to be numerical expressions.)

If an expression contains a relational operation and one (or both) of the operands has (have) the NULL value, the result of this operation will be NULL. Hence, each of the expressions A = B, A <> B, A < B, and A > B also returns NULL.

In the Boolean AND, OR, and NOT, the behavior of the NULL values is specified by the following truth tables, where T stands for true, U for unknown (NULL), and F for false. In these tables, follow the row and column represented by the values of the Boolean expressions that the operator works on, and the value where they intersect represents the resulting value.

AND	T	U	F		OR	T	U	F		NOT
T	T	U	F		T	T	T	T		T	F
U	U	U	F		U	T	U	U		U	U
F	F	F	F		F	T	U	F		F	T

Any NULL value in the argument of aggregate functions AVG, SUM, MAX, MIN, and COUNT is eliminated before the respective function is calculated (except for the function COUNT(*)). If a column contains only NULL values, the function returns NULL. The aggregate function COUNT(*) handles all NULL values the same as non-NULL values. If the column contains only NULL values, the result of the function COUNT(DISTINCT column_name) is 0.

A NULL value has to be different from all other values. For numeric data types, there is a distinction between the value zero and NULL. The same is true for the empty string and NULL for character data types.

A column of a table allows NULL values if its definition explicitly contains NULL. On the other hand, NULL values are not permitted if the definition of a column explicitly contains NOT NULL. If the user does not specify NULL or NOT NULL for a column with a data type (except TIMESTAMP), the following values are assigned:

• NULL - If the ANSI_NULL_DFLT_ON option of the SET statement is set to ON
• NOT NULL - If the ANSI_NULL_DFLT_OFF option of the SET statement is set to ON

If the SET statement isn't activated, a column will contain the value NOT NULL by default. (The columns of TIMESTAMP data type can only be declared as NOT NULL columns.)

There is also another option of the SET statement: CONCAT_NULL_YIELDS_ NULL. This option influences the concatenation operation with a NULL value so that anything you concatenate to a NULL value will yield NULL again. For instance:

'San Francisco' + NULL = NULL

Conclusion

The basic features of Transact-SQL consist of data types, predicates, and functions. Data types comply with data types of the ANSI SQL92 standard. Transact-SQL supports a variety of useful system functions.

The next chapter introduces you to Transact-SQL statements in relation to SQL's data definition language. This part of Transact-SQL comprises all the statements needed for creating, altering, and removing database objects.

Exercises

E.4.1
What is the difference between the numeric data types INT, SMALLINT, and TINYINT?

E.4.2
What is the difference between the data types CHAR and VARCHAR? When should you use the latter (instead of the former) and vice versa?

E.4.3
How can you set the format of a column with the DATE data type so that its values can be entered in the form 'yyyy/mm/dd'?

In the following two exercises, use the SELECT statement in the Query Editor component window of SQL Server Management Studio to display the result of all system functions and global variables. (For instance, SELECT host_id() displays the ID number of the current host.)

E.4.4
Using system functions, find the ID number of the test database (Exercise 2.1).

E.4.5
Using the system variables, display the current version of the database system software and the language that is used by this software.

E.4.6
Using the bitwise operators &, |, and ^, calculate the following operations with the bit strings:
(11100101) & (01010111)
(10011011) | (11001001)
(10110111) ^ (10110001)

E.4.7
What is the result of the following expressions? (A is a numerical and B a logical expression.)
A + NULL
NULL = NULL
B OR NULL
B AND NULL

E.4.8
When can you use both single and double quotation marks to define string and temporal constants?

E.4.9
What is a delimited identifier and when do you need it?

SQL Server 2008 function types in T-SQL

Transact-SQL Functions

Transact-SQL functions can be either aggregate functions or scalar functions. The following sections describe these function types.

Aggregate functions

Aggregate functions are applied to a group of data values from a column. Aggregate functions always return a single value. Transact-SQL supports several groups of aggregate functions:

You are reading part 3 from "The basic objects of T-SQL in SQL Server 2008," excerpted from Microsoft SQL Server 2008: A Beginner's Guide, by Dusan Petkovic, copyright 2008, printed with permission from McGraw-Hill Osborne Media.
• Convenient aggregate functions
• Statistical aggregate functions
• User-defined aggregate functions
• Analytic aggregate functions

Statistical and analytic aggregates are discussed in Chapter 24. User-defined aggregates are beyond the scope of this book. That leaves the convenient aggregate functions, described next:

• AVG - Calculates the arithmetic mean (average) of the data values contained within a column. The column must contain numeric values.
• MAX and MIN - Calculate the maximum and minimum data value of the column, respectively. The column can contain numeric, string, and date/time values.
• SUM - Calculates the total of all data values in a column. The column must contain numeric values.
• COUNT - Calculates the number of (non-null) data values in a column. The only aggregate function not being applied to columns is COUNT(*). This function returns the number of rows (whether or not particular columns have NULL values).
• COUNT_BIG - Analogous to COUNT, the only difference being that COUNT_BIG returns a value of the BIGINT data type.

The use of convenient aggregate functions with the SELECT statement are described in detail in Chapter 6.

Scalar functions

In addition to aggregate functions, Transact-SQL provides several scalar functions that are used in the construction of scalar expressions. (A scalar function operates on a single value or list of values, as opposed to aggregate functions, which operate on the data from multiple rows.) Scalar functions can be categorized as follows:

• Numeric functions
• Date functions
• String functions
• System functions
• Metadata functions

The following sections describe these function types.

Numeric functions

Numeric functions within Transact-SQL are mathematical functions for modifying numeric values. The following numeric functions are available:

Function	Explanation
ABS(n)	Returns the absolute value (i.e., negative values are returned as positive) of the numeric expression n. Example: SELECT ABS(–5.767) = 5.767, SELECT ABS(6.384) = 6.384
ACOS(n)	Calculates arc cosine of n. n and the resulting value belong to the FLOAT data type.
ASIN(n)	Calculates the arc sine of n. n and the resulting value belong to the FLOAT data type.
ATAN(n)	Calculates the arc tangent of n. n and the resulting value belong to the FLOAT data type.
ATN2(n,m)	Calculates the arc tangent of n/m. n, m, and the resulting value belong to the FLOAT data type.
CEILING(n)	Returns the smallest integer value greater or equal to the specified parameter. Examples: SELECT CEILING(4.88) = 5 SELECT CEILING(–4.88) = –4
COS(n)	Calculates the cosine of n. n and the resulting value belong to the FLOAT data type.
COT(n)	Calculates the cotangent of n. n and the resulting value belong to the FLOAT data type.
DEGREES(n)	Converts radians to degrees. Examples: SELECT DEGREES(PI()/2) = 90.0 SELECT DEGREES(0.75) = 42.97
EXP(n)	Calculates the value e^n. Example: SELECT EXP(1) = 2.7183
FLOOR(n)	Calculates the largest integer value less than or equal to the specified value n. Example: SELECT FLOOR(4.88) = 4
LOG(n)	Calculates the natural (i.e., base e) logarithm of n. Examples: SELECT LOG(4.67) = 1.54 SELECT LOG(0.12) = –2.12
LOG10(n)	Calculates the logarithm (base 10) for n. Examples: SELECT LOG10(4.67) = 0.67 SELECT LOG10(0.12) = –0.92
PI()	Returns the value of the number pi (3.14).
POWER(x,y)	Calculates the value x^y. Examples: SELECT POWER(3.12,5) = 295.65 SELECT POWER(81,0.5) = 9
RADIANS(n)	Converts degrees to radians. Examples: SELECT RADIANS(90.0) = 1.57 SELECT RADIANS(42.97) = 0.75
RAND	Returns a random number between 0 and 1 with a FLOAT data type.
ROUND(n, p,[t])	Rounds the value of the number n by using the precision p. Use positive values of p to round on the right side of the decimal point and use negative values to round on the left side. An optional parameter t causes n to be truncated. Examples: SELECT ROUND(5.4567,3) = 5.4570 SELECT ROUND(345.4567,–1) = 350.0000 SELECT ROUND(345.4567,–1,1) = 340.0000
ROWCOUNT_BIG	Returns the number of rows that have been affected by the last Transact-SQL statement executed by the system. The return value of this function has the BIGINT data type.
SIGN(n)	Returns the sign of the value n as a number (+1 for positive, –1 for negative, and 0 for zero). Example: SELECT SIGN(0.88) = 1
SIN(n)	Calculates the sine of n. n and the resulting value belong to the FLOAT data type.
SQRT(n)	Calculates the square root of n. Example: SELECT SQRT(9) = 3
SQUARE(n)	Returns the square of the given expression. Example: SELECT SQUARE(9) = 81
TAN(n)	Calculates the tangent of n. n and the resulting value belong to the FLOAT data type.

Date Functions

Date functions calculate the respective date or time portion of an expression or return the value from a time interval. Transact-SQL supports the following date functions:

Function	Explanation
GETDATE()	Returns the current system date and time. Example: SELECT GETDATE() = 2008-01-01 13:03:31.390
DATEPART(item,date)	Returns the specified part item of a date date as an integer. Examples: SELECT DATEPART(month, '01.01.2005') = 1 (1 = January) SELECT DATEPART(weekday, '01.01.2005') = 7 (7 = Sunday)
DATENAME(item,date)	Returns the specified part item of the date date as a character string. Example: SELECT DATENAME(weekday, '01.01.2005') = Saturday
DATEDIFF(item,dat1,dat2)	Calculates the difference between the two date parts dat1 and dat2 and returns the result as an integer in units specified by the value item. Example: SELECT DATEDIFF(year, BirthDate, GETDATE()) AS age FROM employee; -> returns the age of each employee.
DATEADD(i,n,d)	Adds the number n of units specified by the value i to the given date d. Example: SELECT DATEADD(DAY,3,HireDate) AS age FROM employee; -> adds three days to the starting date of employment of every employee (see the sample database).

String Functions

String functions are used to manipulate data values in a column, usually of a character data type. Transact-SQL supports the following string functions:

Function	Explanation
ASCII(character)	Converts the specified character to the equivalent integer (ASCII) code. Returns an integer. Example: SELECT ASCII('A') = 65
CHAR(integer)	Converts the ASCII code to the equivalent character. Example: SELECT CHAR(65) = 'A'.
CHARINDEX(z1,z2)	Returns the starting position where the partial string z1 first occurs in the string z2. Returns 0 if z1 does not occur in z2. Example: SELECT CHARINDEX('bl', 'table') = 3.
DIFFERENCE(z1,z2)	Returns an integer, 0 through 4, that is the difference of SOUNDEX values of two strings z1 and z2. (SOUNDEX returns a number that specifies the sound of a string. With this method, strings with similar sounds can be determined.) Example: SELECT DIFFERENCE('spelling', 'telling') = 2 (sounds a little bit similar, 0 = doesn't sound similar)
LEFT(z, length)	Returns the first length characters from the string z.
LEN(z)	Returns the number of characters, instead of the number of bytes, of the specified string expression, excluding trailing blanks.
LOWER(z1)	Converts all uppercase letters of the string z1 to lowercase letters. Lowercase letters and numbers, and other characters, do not change. Example: SELECT LOWER('BiG') = 'big'
LTRIM(z)	Removes leading blanks in the string z. Example: SELECT LTRIM(' String') = 'String'
NCHAR(i)	Returns the Unicode character with the specified integer code, as defined by the Unicode standard.
QUOTENAME(char_string)	Returns a Unicode string with the delimiters added to make the input string a valid delimited identifier.
PATINDEX(%p%,expr)	Returns the starting position of the first occurrence of a pattern p in a specified expression expr, or zeros if the pattern is not found. Examples: 1) SELECT PATINDEX('%gs%', 'longstring') = 4; 2) SELECT RIGHT(ContactName, LEN(ContactName)-PATINDEX('% %',ContactName)) AS First_name FROM Customers; (The second query returns all first names from the customers column.)
REPLACE(str1,str2,str3)	Replaces all occurrences of the str2 in the str1 with the str3. Example: SELECT REPLACE('shave' , 's' , 'be') = behave
REPLICATE(z,i)	Repeats string z i times. Example: SELECT REPLICATE('a',10) = 'aaaaaaaaaa'
REVERSE(z)	Displays the string z in the reverse order. Example: SELECT REVERSE('calculate') = 'etaluclac'
RIGHT(z,length)	Returns the last length characters from the string z. Example: SELECT RIGHT('Notebook',4) = 'book'
RTRIM(z)	Removes trailing blanks of the string z. Example: SELECT RTRIM('Notebook ') = 'Notebook'
SOUNDEX(a)	Returns a four-character SOUNDEX code to determine the similarity between two strings. Example: SELECT SOUNDEX('spelling') = S145
SPACE(length)	Returns a string with spaces of length specified by length. Example: SELECT SPACE = ' '
STR(f,[len [,d]])	Converts the specified float expression f into a string. len is the length of the string including decimal point, sign, digits, and spaces (10 by default), and d is the number of digits to the right of the decimal point to be returned. Example: SELECT STR(3.45678,4,2) = '3.46'
STUFF(z1,a,length,z2)	Replaces the partial string z1 with the partial string z2 starting at position a, replacing length characters of z1. Examples: SELECT STUFF('Notebook',5,0, ' in a ') = 'Note in a book' SELECT STUFF('Notebook',1,4, 'Hand') = 'Handbook'
SUBSTRING(z,a,length)	Creates a partial string from string z starting at the position a with a length of length. Example: SELECT SUBSTRING('wardrobe',1,4) = 'ward'
UNICODE	Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
UPPER(z)	Converts all lowercase letters of string z to uppercase letters. Uppercase letters and numbers do not change. Example: SELECT UPPER('loWer') = 'LOWER'

System Functions

System functions of Transact-SQL provide extensive information about database objects. Most system functions use an internal numeric identifier (ID), which is assigned to each database object by the system at its creation. Using this identifier, the system can uniquely identify each database object. System functions provide information about the database system. The following table describes several system functions. (For the complete list of all system functions, please see Books Online.)

Function	Explanation
CAST(a AS type [(length)]	Converts an expression a into the specified data type type (if possible). a could be any valid expression. Example: SELECT CAST(3000000000 AS BIGINT) = 3000000000
COALESCE(a1,a2,…)	Returns for a given list of expressions a1, a2,... the value of the first expression that is not NULL.
COL_LENGTH(obj,col)	Returns the length of the column col belonging to the database object (table or view) obj. Example: SELECT COL_LENGTH('customers', 'cust_ID') = 10
CONVERT(type[(length)],a)	Equivalent to CAST, but the arguments are specified differently. CONVERT can be used with any data type.
CURRENT_TIMESTAMP	Returns the current date and time. Example: SELECT CURRENT_TIMESTAMP = '2008-01-01 17:22:55.670'
CURRENT_USER	Returns the name of the current user.
DATALENGTH(z)	Calculates the length (in bytes) of the result of the expression z. Example: SELECT DATALENGTH(ProductName) FROM products. (This query returns the length of each field.)
GETANSINULL('dbname')	Returns 1 if the use of NULL values in the database dbname complies with the ANSI SQL standard. (See also the explanation of NULL values at the end of this chapter.) Example: SELECT GETANSINULL('AdventureWorks') = 1
ISNULL(expr, value)	Returns the value of expr if that value is not null; otherwise, it returns value (see Example 5.22).
ISNUMERIC(expression)	Determines whether an expression is a valid numeric type.
NEWID()	Creates a unique ID number that consists of a 16-byte binary string intended to store values of the UNIQUEIDENTIFIER data type.
NEWSEQUENTIALID()	Creates a GUID that is greater than any GUID previously generated by this function on a specified computer. (This function can only be used as a default value for a column.)
NULLIF(expr1,expr2)	Returns the NULL value if the expressions expr1 and expr2 are equal. Example: SELECT NULLIF(project_no, 'p1') FROM projects. (The query returns NULL for the project with the project_no = 'p1').
SERVERPROPERTY(propertyname)	Returns the property information about the database server.
SYSTEM_USER	Returns the login ID of the current user. Example: SELECT SYSTEM_USER = LTB13942dusan
USER_ID([user_name])	Returns the identifier of the user user_name. If no name is specified, the identifier of the current user is retrieved. Example: SELECT USER_ID('guest') = 2
USER_NAME([id])	Returns the name of the user with the identifier id. If no name is specified, the name of the current user is retrieved. Example: SELECT USER_NAME = 'guest'

All string functions can be nested in any order; for example, REVERSE(CURRENT_USER).

Metadata Functions

Generally, metadata functions return information concerning the specified database and database objects. The following table describes several metadata functions. (For the complete list of all metadata functions, please see Books Online.)

Function	Explanation
COL_NAME(tab_id, col_id)	Returns the name of a column belonging to the table with the ID tab_id and column ID col_id. Example: SELECT COL_NAME(OBJECT_ID('employee') , 3) = 'emp_lname'
COLUMNPROPERTY(id, col, property)	Returns the information about the specified column. Example: SELECT COLUMNPROPERTY(object_id('project'), 'project_no', 'PRECISION') = 4
DATABASEPROPERTY(database, property)	Returns the named database property value for the specified database and property. Example: SELECT DATABASEPROPERTY('sample', 'IsNullConcat') = 0. (The IsNullConcat property corresponds to the option CONCAT_NULL_YIELDS_NULL, which is described at the end of this chapter.)
DB_ID([db_name])	Returns the identifier of the database db_name. If no name is specified, the identifier of the current database is returned. Example: SELECT DB_ID('AdventureWorks') = 6
DB_NAME([db_id])	Returns the name of the database with the identifier db_id. If no identifier is specified, the name of the current database is displayed. Example: SELECT DB_NAME(6) = 'AdventureWorks'
INDEX_COL(table, i, no)	Returns the name of the indexed column in the table table, defined by the index identifier i and the position no of the column in the index.
INDEXPROPERTY(obj_id, index_name, property)	Returns the named index or statistics property value of a specified table identification number, index or statistics name, and property name.
OBJECT_NAME(obj_id)	Returns the name of the database object with the identifier obj_id. Example: SELECT OBJECT_NAME(453576654) = 'products'
OBJECT_ID(obj_name)	Returns the identifier of the database object obj_name. Example: SELECT OBJECT_ID('products') = 453576654
OBJECTPROPERTY(obj_id,property)	Returns the information about the objects from the current database.

Using T-SQL data types in SQL Server 2008

Data Types

All the data values of a column must be of the same data type. (The only exception specifies the values of the SQL_VARIANT data type.) Transact-SQL uses different data types, which can be categorized as follows:

• Numeric data types
• Character data types
• Temporal (date and/or time) data types
• Miscellaneous data types
• DECIMAL with VARDECIMAL storage type

The following sections describe all these categories.

Numeric Data Types

Numeric data types are used to represent numbers. The following table shows the list of all numeric data types:

Data Type	Explanation
INTEGER	Represents integer values that can be stored in 4 bytes. The range of values is –2,147,483,648 to 2,147,483,647. INT is the short form for INTEGER.
SMALLINT	Represents integer values that can be stored in 2 bytes. The range of values is –32768 to 32767.
TINYINT	Represents nonnegative integer values that can be stored in 1 byte. The range of values is 0 to 255.
BIGINT	Represents integer values that can be stored in 8 bytes. The range of values is –2^63 to 2^63 – 1.
DECIMAL(p,[s])	Describes fixed-point values. The argument p (precision) specifies the total number of digits with assumed decimal point s (scale) digits from the right. DECIMAL values are stored, depending on the value of p, in 5 to 17 bytes. DEC is the short form for DECIMAL.
NUMERIC(p,[s])	Synonym for DECIMAL.
REAL	Used for floating-point values. The range of positive values is approximately 2.23E – 308 to 1.79E +308, and the range of negative values is approximately –1.18E – 38 to –1.18E + 38 (the value zero can also be stored).
FLOAT[(p)]	Represents floating-point values, like REAL. p defines the precision with p < 25 as single precision (4 byte) and p >= 25 as double precision (8 byte).
MONEY	Used for representing monetary values. MONEY values correspond to 8-byte DECIMAL values and are rounded to four digits after the decimal point.
SMALLMONEY	Corresponds to the data type MONEY but is stored in 4 bytes.

Character Data Types

There are two general forms of character data types. They can be strings of single-byte characters or strings of Unicode characters. (Unicode uses several bytes to specify one character.) Further, strings can have fixed or variable length. The following character data types are used:

Data Type	Explanation
CHAR[(n)]	Represents a fixed-length string of single-byte characters, where n is the number of characters inside the string. The maximum value of n is 8000. CHARACTER(n) is an additional equivalent form for CHAR(n). If n is omitted, the length of the string is assumed to be 1.
VARCHAR[(n)]	Describes a variable-length string of single-byte characters (0 < n ≤ 8000). In contrast to the CHAR data type, the values for the VARCHAR data type are stored in their actual length. This data type has two synonyms: CHAR VARYING and CHARACTER VARYING.
NCHAR[(n)]	Stores fixed-length strings of Unicode characters. The main difference between the CHAR and NCHAR data types is that each character of the NCHAR data type is stored in 2 bytes, while each character of the CHAR data type uses 1 byte of storage space. Therefore, the maximum number of characters in a column of the NCHAR data type is 4000.
NVARCHAR[(n)]	Stores variable-length strings of Unicode characters. The main difference between the VARCHAR and the NVARCHAR data types is that each NVARCHAR character is stored in 2 bytes, while each VARCHAR character uses 1 byte of storage space. The maximum number of characters in a column of the NVARCHAR data type is 4000.

 

Note: The VARCHAR data type is identical to the CHAR data type except for one difference: if the content of a CHAR(n) string is shorter than n characters, the rest of the string is padded with blanks. (A value of the VARCHAR data type is always stored in its actual length.)

Temporal Data Types

Transact-SQL supports the following temporal data types:

• DATETIME
• SMALLDATETIME
• DATE
• TIME
• DATETIME2
• DATETIMEOFFSET

The DATETIME and SMALLDATETIME data types specify a date and time, with each value being stored as an integer value in 4 bytes or 2 bytes, respectively. Values of DATETIME and SMALLDATETIME are stored internally as two separate numeric values. The date value of DATETIME is stored in the range 01/01/1753 to 12/31/9999. The analog value of SMALLDATETIME is stored in the range 01/01/1900 to 06/06/2079. The time component is stored in the second 4-byte (or 2-byte for SMALLDATETIME) field as the number of three-hundredths of a second (DATETIME) or minutes (SMALLDATETIME) that have passed since midnight.

The use of DATETIME and SMALLDATETIME is rather inconvenient if you want to store only the date part or time part. For this reason, SQL Server 2008 introduces the new data types DATE and TIME, which store just the DATE or TIME component of a DATETIME, respectively. The DATE data type is stored in 3 bytes and has the range 01/01/0001 to 12/31/9999. The TIME data type is stored in 3–5 bytes and has an accuracy of 100 nanoseconds (ns).

The DATETIME2 data type is also a new data type that stores high-precision date and time data. The data type can be defined for variable lengths depending on the requirement. (The storage size is 6–8 bytes). The accuracy of the time part is 100 ns. This data type isn't aware of Daylight Saving Time.

All the temporal data types described thus far don't have support for the time zone. The new data type called DATETIMEOFFSET has the time zone offset portion. For this reason, it is stored in 6–8 bytes. (All other properties of this data type are analogous to the corresponding properties of DATETIME2.)

The date value in Transact-SQL is by default specified as a string in a format like 'mmm dd yyyy' (e.g., 'Jan 10 1993') inside two single quotation marks or double quotation marks. (Note that the relative order of month, day, and year can be controlled by the SET DATEFORMAT statement. Additionally, the system recognizes numeric month values with delimiters of / or –.) Similarly, the time value is specified in the format 'hh:mm' and Database Engine uses 24-hour time (23:24, for instance).

 

Note: Transact-SQL supports a variety of input formats for DATETIME values. As you already know, both objects are identified separately; thus, date and time values can be specified in any order or alone. If one of the values is omitted, the system uses the default values. (The default value for time is 12:00 AM.)

Examples 4.4 and 4.5 show the different ways, how date or time values can be written using the different formats.

Example 4.4
The following date descriptions can be used:

'28/5/1959' (with SET DATEFORMAT dmy)
'May 28, 1959'
'1959 MAY 28'

Example 4.5
The following time descriptions can be used:

'8:45 AM'
'4 pm'

Miscellaneous Data Types

Transact-SQL supports several data types that do not belong to any of the data type groups described previously:

• Binary data types
• BIT
• Large object data types
• CURSOR (discussed in Chapter 8)
• UNIQUEIDENTIFIER
• SQL_VARIANT
• TABLE (discussed in Chapter 8)
• XML (discussed in Chapter 28)
• Spatial (e.g., GEOGRAPHY and GEOMETRY) data types (discussed in Chapter 29 )
• HIERARCHYID
• TIMESTAMP data type
• User-defined data types (discussed in Chapter 5)

The following sections describe each of these data types (other than those designated as being discussed in another chapter).

Binary and BIT Data Types

BINARY and VARBINARY are the two binary data types. They describe data objects being represented in the internal format of the system. They are used to store bit strings. For this reason, the values are entered using hexadecimal numbers.

The values of the BIT data type are stored in a single bit. Therefore, up to 8 bit columns are stored in 1 byte. The following table summarizes the properties of these data types:

Data Type	Explanation
BINARY[(n)]	Specifies a bit string of fixed length with exactly n bytes (0 < n ≤ 8000).
VARBINARY[(n)]	Specifies a bit string of variable length with up to n bytes (0 < n ≤ 8000).
BIT	Used for specifying the Boolean data type with three possible values: FALSE, TRUE, and NULL.

Large Object Data Types

Large objects (LOBs) are data objects with the maximum length of 2GB. These objects are generally used to store large text data and to load modules and audio/video files. Transact-SQL supports two different ways to specify and access LOBs:

• Use the data types VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX)
• Use the so-called text/image data type

The following subsections describe the two forms of LOBs.

The MAX Specifier Starting with SQL Server 2005, you can use the same programming model to access values of standard data types and LOBs. In other words, you can use convenient system functions and string operators to work with LOBs.

Database Engine uses the MAX specifier with the data types VARCHAR, NVARCHAR, and VARBINARY to define variable-length columns. When you use MAX by default (instead of an explicit value), the system analyzes the length of the particular string and decides whether to store the string as a convenient value or as a LOB. The MAX specifier indicates that the size of column values can reach the maximum LOB size of the current system. (In a future version of SQL Server, it is possible that MAX will have a higher maximum value for strings.)

Although the database system decides how a LOB will be stored, you can override this default specification using the sp_tableoption system procedure with the LARGE_ VALUE_TYPES_OUT_OF_ROW option. If the option's value is set to 1, the data in columns declared using the MAX specifier will be stored separately from all other data. If this option is set to 0, Database Engine stores all values for the row size < 8060 bytes as regular row data.

In SQL Server 2008, you can apply the new FILESTREAM attribute to a VARBINARY(MAX) column to store large binary data directly in an NTFS file system. The main advantage of this attribute is that the size of the corresponding LOB is limited only by the file system volume size.

TEXT, NTEXT, and IMAGE Data Types The data types TEXT, NTEXT, and IMAGE constitute the so-called text/image data types. Data objects of the type IMAGE can contain any kind of data (load modules, audio/video), while data objects of the data types TEXT and NTEXT can contain any text data (that is, printable data).

The text/image data types are stored by default separately from all other values of a database using a B-tree structure that points to the fragments of that data. (A B-tree structure is a treelike data structure in which all of the bottom nodes are the same number of levels away from the root of the tree.) For columns of a text /image data type, Database Engine stores a 16-byte pointer in the data row that specifies where the data can be found.

If the amount of text/image data is less than 32KB, the pointer points to the root node of the B-tree structure, which is 84 bytes long. The root node points to the physical blocks of the data. If the amount of the data is greater than 32KB, Database Engine builds intermediate nodes between the data blocks and the root node.

For each table that contains more than one column with such data, all values of the columns are stored together. However, one physical page can hold only text/image data from a single table.

Although text/image data is stored separately from all other data, you can modify this using the sp_tableoption system procedure with the TEXT_IN_ROW option. Using this option, you can specify the maximum number of bytes, which are stored together with the regular data.

The text/image data types discussed this far have several limitations. You can't use them as local variables (in stored procedures or in groups of Transact-SQL statements). Also, they can't be a part of an index or can't be used in the following clauses of the SELECT statement: WHERE, ORDER BY, and GROUP BY. The biggest problem concerning all text/image data types is that you have to use special operators (READTEXT, WRITETEXT, and UPDATETEXT) to work with such data.

 

Note: The text/image data types are marked as a deprecated feature and will be removed in a future version of Database Engine. Use VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) instead.

UNIQUEIDENTIFIER Data Type

As its name implies, a value of the UNIQUEIDENTIFIER data type is a unique identification number stored as a 16-byte binary string. This data type is closely related to the globally unique identifier (GUID), which guarantees uniqueness worldwide. Hence, using this data type, you can uniquely identify data and objects in distributed systems.

The initialization of a column or a variable of the UNIQUEIDENTIFIER type can be provided using the functions NEWID and NEWSEQUENTIALID, as well as with a string constant written in a special form using hexadecimal digits and hyphens. (The functions NEWID and NEWSEQUENTIALID are described in the section "System Functions" later in this chapter.)

A column of the UNIQUEIDENTIFIER data type can be referenced using the keyword ROWGUIDCOL in a query to specify that the column contains ID values. (This keyword does not generate any values.) A table can have several columns of the UNIQUEIDENTIFIER type, but only one of them can have the ROWGUIDCOL keyword.

SQL_VARIANT Data Type

The SQL_VARIANT data type can be used to store values of various data types at the same time, such as numeric values, strings, and date values. (The only types of values that cannot be stored are TIMESTAMP values.) Each value of an SQL_VARIANT column has two parts: the data value and the information that describes the value. (This information contains all properties of the actual data type of the value, such as length, scale, and precision.)

Transact-SQL supports the SQL_VARIANT_PROPERTY function, which displays the attached information for each value of an SQL_VARIANT column. For the use of the SQL_VARIANT data type, see Example 5.5 in Chapter 5.

 

Note: Declare a column of a table using the SQL_VARIANT data type only if it is really necessary. A column should have this data type if its values may be of different types or if it is not possible to determine the type of a column during the database design process.

HIERARCHYID Data Type

The HIERARCHYID data type is used to store an entire hierarchy. It is implemented as a Common Language Runtime (CLR) user-defined type that comprises several system functions for creating and operating on hierarchy nodes. The following functions, among others, belong to the methods of this data type: GetAncestor(), GetDescendant(), Read(), and Write(). (The detailed description of this data type is outside the scope of this book.)

TIMESTAMP Data Type

The TIMESTAMP data type specifies a column being defined as VARBINARY(8) or BINARY(8), depending on nullability of the column. The system maintains a current value (not a date or time) for each database, which it increments whenever any row with a TIMESTAMP column is inserted or updated and sets the TIMESTAMP column to that value. Thus, TIMESTAMP columns can be used to determine the relative time when rows were last changed. (ROWVERSION is a synonym for TIMESTAMP.)

 

Note: The value stored in a TIMESTAMP column isn't important by itself. This column is usually used to detect whether a specific row has been changed since the last time it was accessed.

DECIMAL with VARDECIMAL Storage Format

The DECIMAL data type is typically stored on the disk as fixed-length data. Since SQL Server 2005 SP2, this data type can be stored as a variable-length column by using the new storage format called VARDECIMAL. Using VARDECIMAL, you can significantly reduce the storage space for a DECIMAL column in which values have significant difference in their lengths.

 

Note: VARDECIMAL is a storage format and not a data type.

The VARDECIMAL storage format is helpful when you have to specify the largest possible value for a field in which the stored values usually are much smaller. Table 4-1 shows this.

 

Note: The VARDECIMAL storage format works the same way for the DECIMAL data type as the VARCHAR data type works for alphanumerical data.

Precision	No. of Bytes: VARDECIMAL	No. of Bytes: Fixed Length
0 or NULL	2	5
1	4	5
20	12	13
30	16	17
38	20	17

Table 4–1 Number of Bytes for Storing VARDECIMAL and Fixed Length

To enable the VARDECIMAL storage format, you first have to enable it for the database and then enable it for the particular table of that database. The sp_db_ vardecimal_storage_format system procedure is used for the first step, as Example 4.6 shows.

Example 4.6
EXEC sp_db_vardecimal_storage_format 'sample', 'ON';

The VARDECIMAL STORAGE FORMAT option of the sp_table option system procedure is used to turn on this storage for the table. Example 4.7 turns on the VARDECIMAL storage format for the project table.

Example 4.7
EXEC sp_tableoption 'project', 'vardecimal storage format', 1

As you already know, the main reason to use the VARDECIMAL storage format is to reduce the storage size of the data. If you want to test how much storage space could be gained by using this storage format, use the dynamic management view called sys.sp_estimated_rowsize_reduction_for_vardecimal. This dynamic management view gives you a detailed estimate for the particular table.