SQLCMD

Here is a handy cheat sheet for Microsoft's SQLCMD, when you don't have the luxury of SQL Server Management Studio.

To run some sql and have results sent to "output.txt",on local server:

>SQLCMD -S(local) -U sa -P changeme -q "use WONDERBEAN;select * from orders;" -o output.txt


To run some sql stored in "myScript.sql" and have results logged to "output.txt",
on local server,with instance called "instanceSmedly":

>SQLCMD -S(local)\instanceSmedly -U sa -P changeme -i myScript.sql -o output.txt


Typical use would be to do a restore. Here is a handy example of some restore sql syntax:

alter database WONDERBEAN SET single_user with rollback immediate;
restore database WONDERBEAN from disk = 'C:\Users\Howard\Documents\WONDERBEAN_1_1_2010.bak' with replace,recovery;
alter database WONDERBEAN set multi_user


Virtual Box : Time machine

I've been digging Sun's free VirtualBox over Microsoft's Virtual PC, and not only because of the Snapshot branching functionality. Here is how to disable the time in the guest operating system from syncing with the hosts, which is great for testing various scenarios.... vboxmanage setextradata vmname “VBoxInternal/Devices/VMMDev/0/Config/GetHostTimeDisabled” “1″

Sequence Diagrams from UML

Recently I've been taking another look at UML. Perhaps it was the result of talking to a software ARCHITECT for the first time in a while, or sub-consciously inserted from the news that Visual Studio 2010 will have some important UML features.

Many of the projects I work on involve first familiarizing myself with legacy code. To me this is almost always fascinating, like "This Old House". I have rarely found two programmers who code the same way. If there is similar architecture to something else I've come across, it almost always turns out to be auto-generated from Microsoft.

I have improvised various note-taking schemes over the years, which is great in that it allows me flexibility to deal with the idiosyncracies of the particular project at hand. On the other hand, one does long for some standards to be handed down. This would be all the more important when working on an excavation as a team.

So, I was delighted to become reacquainted with (because I'm sure I saw it somewhere before but didn't appreciate it) Ivar Jacobson's Sequence Diagrams. When dealing with a highly optimized object oriented architecture that lacks any documentation whatsoever, this is exactly the kind of approach you'd want to take in dissecting some codebase. Sure it was intended for design, but for my purposes, it is probably even more useful in reverse engineering.

Yet, as it seems with all UML models, there is an enormous lack of creativity when it comes to the drawing of symbols. I hope to find an implementation of Sequence Diagram models that has something more creative than a stick person.

UPDATE:
Just learned that the UML creation tools in Visual Studio 2010 only come with the Ultimate version, not either the Professional nor the Premium. Bummer.

Immediate Window vs Command Window in Visual Studio

To issue a Visual Studio command in the Immediate window, you must preface the command with a greater than sign (>). To enter multiple commands, switch to the Command window.

The window used to enter the EvaluateStatement command determines whether an equals sign (=) is interpreted as a comparison operator or as an assignment operator.

In the Immediate window, an equals sign (=) is interpreted as an assignment operator.

In the Command window, by contrast, an equals sign (=) is interpreted as a comparison operator. You cannot use assignment operations in the Command window.

Some useful commands:
>StopOutlining
Collapses all collapsible section of code (classes, regions, etc.)

>toolbox
opens the toolbox window

>help whatever
opens up help for the keyword (in this case, whatever)

Did you know...

SQL Server started supporting multiple instances in the 2000 edition.

---

SQL Server Express Editions always install as a Named Instance, even if you only have one. The instance name, by default, is SQLEXPRESS.

The MSDE Edition of SQL Server 2000 installs as a Default Instance.

---

In SQL Server 2000, the TCP/IP port for a named Instance moves to 1434, and SQL Server 2000 directs the client to the proper Instance using SQL Server Resolution Protocol (SSRP).

In SQL Server 2005, Microsoft introduced a new service called the SQL Browser. This service listens on UDP port 1434 and directs the connection to the proper dynamically chosen TCP/IP port.

---

Each named Instance has a different location for program files and data files that is different from that of the other Instances of SQL Server. In SQL Server 2000, the directories look like this:

executable/program files:
\Program Files\Microsoft SQL Server\MSSQL$NameOfInstance\Binn
data files:
\Program Files\Microsoft SQL Server\MSSQL$NameOfInstance\Data

In SQL Server 2005 and higher, the files have a different structure. For the Default Instance:

executable/program files:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
data files:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

---

The maximum number of Instances supported in SQL Server 2000 and 2005 is 16 for the standard editions and lower, and 50 for the Enterprise Editions in 2005 and even more in later versions and higher edition numbers

Annoyances

• Many Microsoft products: having both a "Settings..." and "Options..." choice on the menu
• SQL Server's Mgt Studio: "Modify" and "Open table..." in the context menu (since "Open table..." is frequently used to MODIFY data).  "Modify" should be either "Schema" or "Modify table columns"....

Database DE-normalization and Violating Simplicity Principles

Timothy Claason, at SQL Server Central, writes:

"Denormalization is not a design strategy. It is a design work-around. Well normalized databases represent a good design strategy, but can often lead to a great deal of complexity when it comes to support, maintenance, and new development. A well designed database can mean that, in order to get specific data you need, you need to go through 5, 10, or even more tables which represent the data you're looking for. Though there are many solutions to this dilemma,such as virtual tables (views), programmatic solutions, temporary tables, and more, I think it's important to not discount the value of well-placed denormalization in the database. The intent of this article is to consider some use cases for denormalization, and from those use cases, assert some generalizations about when and why to use denormalization."

Continued