XML definition and basics (01/09/2010)
(...) Nodes are a useful logical construct for working with XML content. A node can be one of the following types: Element nodes-Element nodes consist of markup tags that wrap other nodes and textual data.Attribute nodes-Attribute nodes are name/value pairs associated with element nodes. (...)

What is the prolog of an XML document (01/09/2010)
(...) XML allows you to mark up textual data, but it assigns special meaning to certain characters, such as the greater-than and less-than symbols (< and >). Entitizing is the process of converting these characters that have special meaning in XML to special codes known as entities. The less-than symbol (<), for instance, must be converted to the entity &lt;. (...)

Explanations to the XML data type (01/09/2010)
(...) Entities are expanded to their textual representations. In addition, typed data, such as integers, is stored in an internal binary format. An integer such as 00150 can be stored in a numeric representation, returning the textual representation 150 (creating a typed representation of your XML data requires storing the data in a typed xml instance). (...)

How to understand full text search (01/09/2010)
(...) According to online documentation from Microsoft, text, ntext, and image data types will be deprecated in future versions of SQL Server, so I suggest avoiding these if you can. Char, nchar, varchar, and nvarchar all make sense as candidates for full-text indexing. XML also makes sense, because it's text based, but adds the advantage that markup tags are ignored-only the data is full-text indexed. (...)

How to shred XML data and specify a namespace in XQuery (01/09/2010)
(...) value() method to retrieve scalar values from the Col column of the result set. The xml data type .nodes() method is a fairly simple way to shred your XML data, but there's a second option. (...)

A few words on the simil algorithm (01/09/2010)
(...) Another typical use for Simil is in preventing bad data from entering the database in the first place. Our company has a Sales application with a Companies table. When a salesperson is creating or importing a new company, the application uses Simil to scan for similar company names. (...)

Indexes can help access data quickly and effectively (01/09/2010)
(...) Nowadays it would be electronic, but for the purpose of the analogy, we're going to consider a paper-based system. Suppose that the master list of the telephone number information is a massive folder stored at the telecommunication provider's head office. Suppose also that it's sorted by telephone number. (...)

What is a deadlock error in SQL server (01/09/2010)
(...) It evaluates the locks held and locks requested and, if it finds an existing deadlock condition, it picks one of the deadlocked processes and kills it. The process killed by the deadlock detector is known as the deadlock victim. The selection of the deadlock victim is based on a number of conditions: The deadlock priority, if set The number of data modifications that will need to be rolled back Whether the processes are system processes or user processes In general, the process with the lowest deadlock priority or the one that's least expensive to roll back is the one that will be picked as the deadlock victim. (...)

The tempdb database is used as a shared temporary workspace (01/09/2010)
(...) Unlike other SQL Server databases, the tempdb database is dropped and re-created every time the SQL Server service is stopped and restarted. Here's what happens. When the SQL Server service is started, by default, SQL Server makes a copy of the model database to create a new 8 MB tempdb database, inheriting customizations made to the model database. (...)

Client complaints about inconsistent query results (01/07/2010)
(...) No constraints, no relationships, no nothing. A quick query later and you find that orphaned rows in a child table show up in one query but not another. At this point it's clear that almost all of the data in these tables is suspect because if you can't trust that a simple relationship works, can you trust that the child rows that exist are for the proper parent? Sadly, the answer is no, because the software may have created a new parent row that reused a key value. (...)

How to ensure data quality (01/07/2010)
(...) As an example, consider storing a GUID value in a varchar column. Using the uniqueidentifier data type, you can store the 36-character value in 16 bytes. Put it in a varchar column and you'll need 36 bytes, or 72 if the column is nvarchar. (...)

How to use a foreign key constraint (01/07/2010)
(...) invoiceId that aren't in the invoice table. These values would be excluded in the second query, causing the user to have to think about why this is true. Simply adding these values could prevent a lot of heartache trying to decipher the random bits and bytes from the meaningful data. (...)

Triggers in SQL Server programming (01/07/2010)
(...) There are two different types of triggers. First there are AFTER triggers, which execute after the rows are in the table and after constraints have been applied. These triggers are most often used to validate some condition or to cause some cascading operation that can't be done declaratively using a CHECK constraint. (...)

How to apply the tools to common situations (01/07/2010)
(...) Limiting NULLs can help the optimizer not consider NULL as a possibility, and make comparisons easier. Uniqueness constraints can help the optimizer know that only one value can match for a query. Using proper indexes (including unique indexes) generally helps performance unless grossly overused, even ones on a view or filtered index (with a WHERE clause). (...)

Communication between domain experts and data modelers (01/07/2010)
(...) And questions asked by the data modeler are often so abstract that the domain expert might misunderstand them, unless she has lots of experience with data modeling herself. To minimize the chance of misunderstandings, I use a technique that's not used often in interviews, but has proven its worth many times: I use concrete examples (preferably in a notation familiar to the domain expert) instead of asking abstract questions. To illustrate the benefits of this interview technique, let's briefly assume that you, as a domain expert in the field of relational databases, are being interviewed by someone who has to make a data model for relational databases. (...)

How to model the sales order (01/07/2010)
(...) The procedure for finding all functional dependencies is easier to perform if you first lay out all attributes in a simple tabular format, with the attribute names as column headers and some sample data below them. You need to start with at least one row of data, and you're well advised to keep the number of rows low. A functional dependency can be single-attribute (when a single attribute determines the dependent attribute) or multiattribute (when two or more attributes have to be combined to determine the dependent attribute). (...)

Where does an attribute depend on one of the other attributes (01/07/2010)
(...) Not moving these attributes to their own tables now will make this step unnecessarily complex. The key to reducing the number of possible combinations is to observe that at this point, you can only have three kinds of attributes in the table: a single-attribute candidate key (or more in the case of a mutual dependency), one or more attributes that depend on the candidate key, and one or more attributes that don't depend on the candidate key, or on any other attribute (as we tested all single-attribute dependencies). Because we already moved attributes that depend on an attribute other than the candidate key, these are the only three kinds of attributes we have to deal with. (...)

A few lines about database development (01/07/2010)
(...) When reading SQL Server newsgroups or blogs, you could easily get the impression that there are two ways to manipulate data: declarative (set-based) or iterative (cursor-based). And that iterative code is always bad and should be avoided like the plague. Those impressions are both wrong. (...)

Iterative code and set based iteration (01/07/2010)
(...) In some cases (such as the running totals already mentioned), this can result in faster-running code. By writing iterative code, you're crippling SQL Server's performance in two ways at the same time. You not only work around all the optimizations SQL Server has for fast setbased processing, you also effectively prevent the query optimizer from coming up with a faster way to achieve the same results. (...)

Associative arrays in Python are dictionaries (01/05/2010)
(...) Values stored in a dictionary aren't implicitly ordered relative to one another because dictionary keys aren't just numbers. Note that if you're using a dictionary, you can define an ordering on the items in a dictionary by using another data structure (often a list) to store such an ordering explicitly; this doesn't change the fact that dictionaries have no implicit (built-in) ordering. In spite of the differences between them, use of dictionaries and lists often appears alike. (...)

What can be used as a key in Python (01/05/2010)
(...) Unfortunately, the requirement that keys be immutable and hashable means that lists can't be used as dictionary keys. But there are many instances when it would be convenient to have a listlike key. For example, it's convenient to store information about a person under a key consisting of both their first and last names, which could be easily done if we could use a two-element list as a key. (...)

Definitions and uses of functions in Python (01/05/2010)
(...) This line shows the value after the return is sent back to the code calling the function. In some languages, a function that doesn't return a value is called a procedure. Although you can (and will) write functions that don't have a return statement, they aren't really procedures. (...)

Python functions handle variable numbers of arguments (01/05/2010)
(...) An arbitrary number of keyword arguments can also be handled. If the final parameter in the parameter list is prefixed with **, it will collect all excess keyword-passed arguments into a dictionary. The index for each entry in the dictionary will be the keyword (parameter name) for the excess argument. (...)

Lambda expressions and generator functions in Python (01/05/2010)
(...) Local variables in a generator function are saved from one call to the next, unlike in normal functions. Note that this generator function has a while loop that limits the number of times the generator will execute. Depending on how it's used, a generator that doesn't have some condition to halt it could cause an endless loop when called. (...)

How to create a basic program in Python (01/05/2010)
(...) In its most basic form, this is a simple matter of creating a controlling function in a file and calling that function. In a script, main is our controlling-and only-function. It's first defined, and then it's called. (...)

How to make Python script execution in UNIX and Mac OS X and in Windows (01/05/2010)
(...) stat call. In many ways, Python scripts on Mac OS X behave the same way as they do on Linux/ UNIX. You can run Python scripts from a terminal window exactly the same way as on any UNIX box. (...)

The difference between scripts on Windows scripts on UNIX (01/05/2010)
(...) The closest you can come is to use the command prompt window: place the script in the desired directory, navigate into the directory, and then type. But using the glob.glob function, you can obtain the wildcard character functionality. (...)

Python applications are distributed as source files (01/05/2010)
(...) The heart of a distutils package is a setup.py file that you create, basically a Python program that controls the installation. distutils has a number of options and lets you create built distributions tailored for Windows and several UNIX platforms. (...)

How to use the Python file system (01/05/2010)
(...) This article discusses these details. Pathname semantics across different operating systems are very similar, because the filesystem on almost all operating systems is modeled as a tree structure, with a disk being the root, and folders, subfolders, and so forth being branches, subbranches, and so on. This means that most operating systems refer to a specific file in fundamentally the same manner: with a pathname that specifies the path to follow from the root of the filesystem tree (the disk) to the file in question. (...)