10 things you should know about SQL Server 2005 Management Studio

Takeaway: SQL Server 2005 Management Studio is a phenomenal advance over the tools provided with SQL Server 2000. Check out the improvements in SQL Server Management Studio, which makes it easier to locate errors in your code and allows you to keep tabs on your reports.

By Arthur Fuller and Stephen Giles

Microsoft SQL Server 2005 contains a number of major new features, but the feature that we like the most is the SQL Server Management Studio. This tool is leaps and bounds beyond what was available in earlier versions of SQL Server. Here is a list of the 10 things that we find most useful about SQL Server Management Studio.

1. Combines the best features of many tools
In earlier versions of SQL Server, you had two main tools: a graphical administration tool (Enterprise Manager) and a Transact SQL Editor (Query Analyzer). The problem with this split is that we do development and administration on SQL Server (sometimes at the same time) and often have to flip back and forth between the two. In SQL Server Management Studio, the Enterprise Manager and Query Analyzer are combined into one common interface, allowing you to manage your servers graphically and to write Transact SQL.

SQL Server Management Studio also allows you to access an Object Browser for all registered servers, which combines the features of the Object Browser from Query Analyzer with the Server tree view from Enterprise Manager. In addition, it provides a workspace similar to Query Analyzer, with the expected tools like the Language Parser and the Graphical Show plan. Now you can write queries and scripts and manipulate objects with Wizards and Property sheets in the same tool at the same time.

SQL Server Management Studio's interface has a separate Registered Servers view that allows you to work with multiple servers at the same time. You can do this in Enterprise Manager; however, SQL Server Management Studio allows you to register server instances as well as all Analysis Services, Reporting Services, SQL Server Integration Services, and Mobile SQL instances. Thus, you can obtain an enterprise view or concentrate on the particular instances and objects of interest.

2. Work with projects and solutions
If you have worked with Visual Studio, then you are familiar with the concept of projects and solutions. In a nutshell, projects allow you to group files together and access them as a unit. A solution is a series of projects, enabling you to drill down to projects just as OLAP users can drill down to the data dimension of interest.

A project can contain .sql, .mdx, .xmla, and .dmx scripts. You can also add other files (such as XML or CSV files) to a project. Therefore, the project itself is a drill-down object.

To create a new project, follow these steps:
1. Click File | New | Project.
2. Choose the type of project you want to create (SQL Server Scripts, Analysis Services Scripts, or SQL Mobile Scripts).
3. Give your project and solution a name.
4. Select the path where you want to store the files.
5. Click OK.

Now you can define various data sources (if your project touches more than one database) and add files effortlessly (simply right-click the Scripts folder in the Solution Explorer and select the items to add). You can also import scripts into a project if you have done some work already.

(If you don't see the Solution Explorer in your SQL Server Management Studio, select View | Solution Explorer or hit [Ctrl][Alt]L.)

3. The tool is a data analyst's best friend
Thanks to the integration of the OLAP tools, SQL Server Management Studio is a great tool for working with your cubes. The object browser allows you to access Analysis Services objects to graphically manage your cubes. It also lets you write and execute MDX and DMX and XMLA statements from within the editor window, allowing you to run both OLTP and OLAP queries from the same tool and even from within the same project.

4. You can display line numbers

How many times within Query Analyzer have you had to walk down the lines to count up to the line flagged with an error? With SQL Server Management Studio, you can display line numbers in the code editor, which is extremely helpful when you're parsing code to locate the problem line and inevitable typos.

This feature is not turned on by default. Here's how to enable line numbers:
1. Select Tool | Options.
2. Expand Text Editor in the Options Tree and select All Languages.
3. In the property pane on the right, select the Line Numbers check box under the Display heading.

There is one "feature" with line numbering. If you have batch statements in your script (Begin...End or Go statements), the results page will recalculate line numbering within the block (i.e., it will start counting from line 1).

5. It's easier to find errors
SQL Server Management Studio has retained one of our favorite features of Query Analysis: linking to errors in the body of your script from the error message in the Message pane. Note that the line number referenced in the error message may not correspond to the line numbering if a script contains multiple batches. You can, however, find the line causing the error simply by double-clicking the error (the red text) in the Message pane. This action will highlight the offending line in the body of the script. (You may want to use the pre-parse function with this feature to clean up all syntax errors before running a script.)

6. Get started faster with an expanded Template Explorer
Transact SQL is the language of SQL Server, and (as with other versions of SQL Server) you can perform all tasks from queries to object creation through Transact SQL. We like scripting objects primarily because it allows us to have absolute control over what we create, and we can save scripts to document objects and move them easily from a test to a production environment.

However, new features mean new syntax, and thus much more to remember. To make life easier, the SQL Server Management Studio includes an updated Template Explorer (View | Template Explorer or [Ctrl][Alt]T) that lays out the structure of more than 100 objects and tasks in Transact SQL, including administrative tasks like backing up and restoring databases.

Due to the integration of formerly disparate tools, the Template Explorer now includes templates for both Analysis Services and SQL Mobile commands. This means that you can drive DMX, MDX, and XMLA expressions through script templates the same way you could create objects in earlier versions of Query Analyzer.

7. It (sort of) plays well with previous versions
SQL Server Management Studio can run through the SQL Server Distributed Management Objects (DMO) as well as the SQL Management Objects (SMO), which are the preferred management objects for SQL Server 2005. This means that you can administer SQL Server 2000 and MSDE databases using the SQL Server Management Studio. This allows you to keep your databases in previous versions while administering them from SQL Server 2005. One caveat is that SQL Server Management Studio's version of DMO will not allow you to administer SQL Server 7.0 servers. You should consider this yet another good reason to upgrade.

8. Name that registered server
When registering our databases (both in Enterprise Manager and SQL Server Management Studio), we like to use an IP address rather than a server name; this approach facilitates remote connections across a VPN, and name resolution can sometimes be a problem. In Enterprise Manager, we had to remember the IP address for each particular server for which we work. For this purpose, we kept a text file listing all the connection parameters.

In SQL Server Management Studio, you can register by IP address but still give the computer a more recognizable name and even add a description of the server. The name and description will show up on the Registered Servers pane (View | Registered Servers or press [Ctrl][Alt]G), so you always know which server you are working on.

9. Manage your SQL Server Integration Services pages
Microsoft intended for SQL Server Management Studio to enable you to manage all services in one consistent UI, which is the case with the SQL Server Integration Services (SSIS). SSIS is the replacement for DTS in earlier versions of SQL Server and is also utilized by the new Maintenance Plan Wizard.

In SQL Server Management Studio, you can view all packages on a server and see which packages are currently running. You can also Import and Export packages using this tool (which is something that was not easy to do in earlier versions of SQL Server), and run packages from within the SQL Server Management Studio. To access SSIS through SQL Server Management Studio, follow these steps:
1. Register a server through the Integration Services tab in Registered Servers.
2. Right-click the Server and select Connect | Object Explorer.
3. Manage your packages through the tree that appears in the Object Explorer.

10. Keep tabs on your reports
As with Analysis Services and SSIS, you can use SQL Server Management Studio to manage your Reporting Services. In the SQL Server 2000 version of Reporting Services, all administration was carried out through a Web-based administrator that was installed as part of Reporting Services. If you managed several report servers, you had to manage each one through separate admin sites.

In SQL Server Management Studio, you can register all of your Reporting Services and administer them through the Object Explorer. You can also perform all tasks that were available on the Reporting Services admin site through the Object Explorer.

To access Reporting Services through SQL Server Management Studio, follow these steps:
1. Register a server through the Reporting Services tab in Registered Servers.
2. Right-click the Server and select Connect | Object Explorer.
3. Manage your packages through the tree that appears in the Object Explorer.

Conclusion
SQL Server 2005 Management Studio is a phenomenal advance over the tools provided with SQL Server 2000. We have gone so far as to remove the SQL Server 2000 tools, and we now do everything in SQL Server 2005. This version of SQL Server provides great leaps forward plus backward compatibility, which in our book is a winning combination.

12 common mistakes done by programmers

Most often, software developers seem locked into certain failure modes that can't be avoided and such is the frequency with which they fall prey to a particular poor programming practice.Peter Wayner of Computerworld writes about twelve most common programming mistakes, each of which is accompanied by its opposing pair. Below are the twelve programming pitfalls developers should stay away from.

Playing it fast and loose
Failing to prop up the basics is the easiest way to make errors in coding. There are a lot of small places where a developer may make a mistake which causes software to fail. And the worst part about sloppy programming is that advances in language design aimed to fix these problems don't do their job. There have been improvements in syntax in programming languages. For instance, the latest version of Java tries to make null-pointer checking easier by offering shorthand syntax for the endless pointer testing. But such syntax improvements can only prevent code from crashing. They don't eliminate the root of the problem: the proliferation of null values due to fast and loose programming.

Overcommitting to details
On the flip side, overly buttoned-up software can slow to a crawl. Relentless devotion to detail can even lock up software if the obsessive checking requires communicating with a distant website over the network. Here, the challenge is to design the layers of code to check the data when it first appears, which is much easier said than done.

Not simplifying control
Not simplifying control over tasks in their code may invite disaster for developers. The software assumes that if someone creates an object of type Name with two fields first and last, then it should immediately create a database table called Name with two columns, first and last. The names are specified in only one place, avoiding any problems that might come if someone fails to keep all of the layers of configuration in sync.

Delegating too much to frameworks
Sometimes the magic tools lead only to confusion. By abstracting functionality and assuming what we want, frameworks can all too often leave developers at a loss for what's gone wrong in their code. The rules are, while quite reasonable, not entirely trivial. As the app grows, it depends on more and more of these almost-trivial bits of external knowledge.

Trusting the client
Many of the worst security bugs appear when developers assume the client device will do the right thing. For example, code written to run in a browser can be rewritten by the browser to execute any arbitrary action. If the developer doesn't double-check all of the data coming back, anything can go wrong.

Not trusting the client enough
Sometimes too much security can lead paradoxically to gaping holes. Because of this, many Web developers are looking to reduce security as much as possible, not only to make it easy for people to engage with their products but also to save them the trouble of defending more than the minimum amount of data necessary to set up an account.

Relying too heavily on magic boxes
Many programmers assume they can link in the encryption library, push a button, and have iron-clad security. But many of these magic algorithms have subtle weaknesses, and avoiding these weaknesses requires learning more than what's in the Quick Start section of the manual.

Reinventing the wheel
Then again, writing your own libraries just because you think you know a better way to code can come back to haunt you. But grow-your-own cryptography is a welcome sight to attackers. Many libraries don't need to be perfect, so grabbing a magic box is more likely to be better than the code you write yourself.

Opening up too much to the user
Placing the onus on users to customize functionality they do not fully understand can invite disaster in the form of inadvertent security holes and privacy violations. When making purchasing decisions, most users can't handle the breadth of features offered by any given piece of software.

Overdetermining the user experience
Some developers decide to avoid the trouble of too many features by offering exactly one solution. But if users don't like the idea, they will look for ways to work around these limitations, and it will lead to an outcome that could translate into security vulnerabilities.

Closing the source
The decision to not distribute code works against the integrity of that code and it can discourage innovation and fixing bugs. Just opening up the code forces you to make the info more accessible, understandable, and thus better.

Assuming openness is a cure-all
While openness can make it possible for others to pitch in and, thus, improve your code, the mere fact that it's open won't do much unless there's another incentive for outside contributors to put in the work. Opening up a project can also add new overhead for communications and documentation. Moreover, a good open source project comes with extensive documentation of the API and road maps for future development.

10 mistakes every programmer makes

By Julian M Bucknall

When you start programming, you get disillusioned quickly. No longer is the computer the allinfallible perfect machine – "do as I mean, not as I say" becomes a frequent cry.

At night, when the blasted hobgoblins finally go to bed, you lie there and ruminate on the errors you made that day, and they're worse than any horror movie. So when the editor of PC Plus asked me to write this article, I reacted with both fear and knowing obedience.

I was confident that I could dash this off in a couple of hours and nip down to the pub without the usual resultant night terrors. The problem with such a request is, well, which language are we talking about?

I can't just trot out the top 10 mistakes you could make in C#, Delphi, JavaScript or whatever – somehow my top ten list has to encompass every language. Suddenly, the task seemed more difficult. The hobgoblins started cackling in my head. Nevertheless, here goes…

1. Writing for the compiler, not for people

When they use a compiler to create their applications, people tend to forget that the verbose grammar and syntax required to make programming easier is tossed aside in the process of converting prose to machine code.

A compiler doesn't care if you use a single-letter identifier or a more human-readable one. The compiler doesn't care if you write optimised expressions or whether you envelop sub-expressions with parentheses. It takes your human-readable code, parses it into abstract syntax trees and converts those trees into machine code, or some kind of intermediate language. Your names are by then history.

So why not use more readable or semantically significant identifiers than just i, j or x? These days, the extra time you would spend waiting for the compiler to complete translating longer identifiers is minuscule. However, the much-reduced time it takes you or another programmer to read your source code when the code is expressly written to be self-explanatory, to be more easily understandable, is quite remarkable.

Another similar point: you may have memorised the operator precedence to such a level that you can omit needless parentheses in your expressions, but consider the next programmer to look at your code. Does he? Will he know the precedence of operators in some other language better than this one and thereby misread your code and make invalid assumptions about how it works?

Personally, I assume that everyone knows that multiplication (or division) is done before addition and subtraction, but that's about it. Anything else in an expression and I throw in parentheses to make sure that I'm writing what I intend to write, and that other people will read what I intended to say.

The compiler just doesn't care. Studies have shown that the proportion of some code's lifecycle spent being maintained is easily five times more than was spent initially writing it. It makes sense to write your code for someone else to read and understand.

2. Writing big routines

Back when I was starting out, there was a rule of thumb where I worked that routines should never be longer than one printed page of fan-fold paper – and that included the comment box at the top that was fashionable back then. Since then, and especially in the past few years, methods tend to be much smaller – merely a few lines of code.

In essence, just enough code that you can grasp its significance and understand it in a short time. Long methods are frowned upon and tend to be broken up.

The reason is extremely simple: long methods are hard to understand and therefore hard to maintain. They're also hard to test properly. If you consider that testing is a function of the number of possible paths through a method, the longer the method, the more tests you'll have to write and the more involved those tests will have to be.

There's actually a pretty good measurement you can make of your code that indicates how complex it is, and therefore how probable it is to have bugs – the cyclomatic complexity.

Developed by Thomas J. McCabe Sr in 1976, cyclomatic complexity has a big equation linked to it if you're going to run through it properly, but there's an easy, basic method you can use on the fly. Just count the number of 'if' statements and loops in your code. Add 1 and this is the CC value of the method.

It's a rough count of the number of execution paths through the code. If your method has a value greater than 10, I'd recommend you rewrite it.

3. Premature optimisation

This one's simple. When we write code, sometimes we have a niggling devil on our shoulder pointing out that this clever code would be a bit faster than the code you just wrote. Ignore the fact that the clever code is harder to read or harder to comprehend; you're shaving off milliseconds from this loop. This is known as premature optimisation.

The famous computer scientist Donald Knuth said, "We should forget about small efficiencies, say about 97 per cent of the time: premature optimisation is the root of all evil."

In other words: write your code clearly and cleanly, then profile to find out where the real bottlenecks are and optimise them. Don't try to guess beforehand.

4. Using global variables

Back when I started, lots of languages had no concept of local variables at all and so I was forced to use global variables. Subroutines were available and encouraged but you couldn't declare a variable just for use within that routine – you had to use one that was visible from all your code. Still, they're so enticing, you almost feel as if you're being green and environmentally conscious by using them. You only declare them once, and use them all over the place, so it seems you're saving all that precious memory.

But it's that "using all over the place" that trips you up. The great thing about global variables is that they're visible everywhere. This is also the worst thing about global variables: you have no way of controlling who changes it or when the variable is accessed. Assume a global has a particular value before a call to a routine and it may be different after you get control back and you don't notice.

Of course, once people had worked out that globals were bad, something came along with a different name that was really a global variable in a different guise. This was the singleton, an object that's supposed to represent something of which there can only be one in a given program.

A classic example, perhaps, is an object that contains information about your program's window, its position on the screen, its size, its caption and the like. The main problem with the singleton object is testability. Because they are global objects, they're created when first used, and destroyed only when the program itself terminates. This persistence makes them extremely difficult to test.

Later tests will be written implicitly assuming that previous tests have been run, which set up the internal state of the singleton. Another problem is that a singleton is a complex global object, a reference to which is passed around your program's code. Your code is now dependent on some other class.

Worse than that, it's coupled to that singleton. In testing, you would have to use that singleton. Your tests would then become dependent on its state, much as the problem you had in testing the singleton in the first place. So, don't use globals and avoid singletons.

5. Not making estimates

You're just about to write an application. You're so excited about it that you just go ahead and start designing and writing it. You release and suddenly you're beset with performance issues, or out-of-memory problems.

Further investigations show that, although your design works well with small number of users, or records, or items, it does not scale – think of the early days of Twitter for a good example. Or it works great on your super-duper developer 3GHz PC with 8GB of RAM and an SSD, but on a run-of-the-mill PC, it's slower than a Greenland glacier in January.

Part of your design process should have been some estimates, some back-back-of- the-envelope calculations. How many simultaneous users are you going to cater for? How many records? What response time are you targeting?

Try to provide estimates to these types of questions and you'll be able to make further decisions about techniques you can build into your application, such as different algorithms or caching. Don't run pell-mell into development – take some time to estimate your goals.

6. Off by one

This mistake is made by everyone, regularly, all the time. It's writing a loop with an index in such a way that the index incremented once too often or once too little. Consequently, the loop is traversed an incorrect number of times.

If the code in the loop is visiting elements of an array one by one, a non-existent element of the array may be accessed – or, worse, written to – or an element may be missed altogether. One reason why you might get an off-by one error is forgetting whether indexes for array elements are zero-based or one-based.

Some languages even have cases where some object is zero-based and others where the assumption is one-based. There are so many variants of this kind of error that modern languages or their runtimes have features such as 'foreach loops' to avoid the need to count through elements of an array or list.

Others use functional programming techniques called map, reduce and filter to avoid the need to iterate over collections. Use modern 'functional' loops rather than iterative loops.

7. Suppressing exceptions

Modern languages use an exception system as an error-reporting technique, rather than the old traditional passing and checking of error numbers. The language incorporates new keywords to dispatch and trap exceptions, using names such as throw, try, finally and catch.

The remarkable thing about exceptions is their ability to unwind the stack, automatically returning from nested routines until the exception is trapped and dealt with. No longer do you have to check for error conditions, making your code into a morass of error tests.

All in all, exceptions make for more robust software, providing that they're used properly. Catch is the interesting one: it allows you to trap an exception that was thrown and perform some kind of action based upon the type of the exception.

The biggest mistakes programmers make with exceptions are twofold. The first is that the programmer is not specific enough in the type of exception they catch. Catching too general an exception type means that they may be inadvertently dealing with particular exceptions that would be best left to other code, higher up the call chain. Those exceptions would, in effect, be suppressed and possibly lost.

The second mistake is more pernicious: the programmer doesn't want any exceptions leaving their code and so catches them all and ignores them. This is known as the empty catch block. They may think, for example, that only certain types of exceptions might be thrown in his code; ones that they could justifiably ignore.

In reality, other deadly runtime exceptions could happen – things such as out-of-memory exceptions, invalid code exceptions and the like, for which the program shouldn't continue running at all. Tune your exception catch blocks to be as specific as possible.

8. Storing secrets in plain text

A long time ago, I worked in a bank. We purchased a new computer system for the back office to manage some kind of workflow dealing with bond settlements. Part of my job was to check this system to see whether it worked as described and whether it was foolproof. After all, it dealt with millions of pounds daily and then, as now, a company is more likely to be defrauded by an employee than an outsider.

After 15 minutes with a rudimentary hex editor, I'd found the administrator's password stored in plain text. Data security is one of those topics that deserves more coverage than I can justifiably provide here, but you should never, ever store passwords in plain text.

The standard for passwords is to store the salted hash of the original password, and then do the same salting and hashing of an entered password to see if they match.

Here's a handy hint: if a website promises to email you your original password should you forget it, walk away from the site. This is a huge security issue. One day that site will be hacked. You'll read about how many logins were compromised, and you'll swallow hard and feel the panic rising. Don't be one of the people whose information has been compromised and, equally, don't store passwords or other 'secrets' in plain text in your apps.

9. Not validating user input

In the good old days, our programs were run by individuals, one at a time. We grew complacent about user input: after all, if the program crashed, only one person would be inconvenienced – the one user of the program at that time. Our input validation was limited to number validation, or date checking, or other kinds of verification of input.

Text input tended not to be validated particularly. Then came the web. Suddenly your program is being used all over the world and you've lost that connection with the user. Malicious users could be entering data into your program with the express intent of trying to take over your application or your servers.

A whole crop of devious new attacks were devised that took advantage of the lack of checking of user input. The most famous one is SQL injection, although unsanitised user input could precipitate an XSS attack (crosssite scripting) through markup injection.

Both types rely on the user providing, as part of normal form input, some text that contains either SQL or HTML fragments. If the application does not validate the user input, it may just use it as is and either cause some hacked SQl to execute, or some hacked HTML/JavaScript to be produced.

This in turn could crash the app or allow it to be taken over by the hacker. So, always assume the user is a hacker trying to crash or take over your application and validate or sanitise user input.

10. Not being up to date

All of the previous mistakes have been covered in depth online and in various books. I haven't discovered anything new – they and others have been known for years. These days you have to work pretty hard to avoid coming into contact with various modern design and programming techniques.

I'd say that not spending enough time becoming knowledgeable about programming – and maintaining that expertise – is in fact the biggest mistake that programmers make. They should be learning about techniques such as TDD or BDD, about what SLAP or SOLID means, about various agile techniques.

These skills are of equal or greater importance than understanding how a loop is written in your language of choice. So don't be like them: read McConnell and Beck and Martin and Jeffries and the Gang of Four and Fowler and Hunt & Thomas and so on. Make sure you stay up to date with the art and practice of programming.

And that concludes my top 10 list of mistakes programmers make, no matter what their language stripe. There are others, to be sure, perhaps more disastrous than mine, but I would say that their degree of dread is proportional to the consequences of making them.

All of the above were pretty dire for me the last time I made them. If you have further suggestions or calamities of your own, don't hesitate to contact me and let me know.