Author: LizetP

I just ordered this book…

This is exactly the the feeling I got when I took a peek at the database I’m working with.
I thought, oh gosh I made a mistake when I chose this work…then i thought, well, look at the positive side,
there are lots of room for improvement here. To be honest I cant wait to make some database refactoring, if they allow my hands into it.

Whenever you mention to do database refactoring, you cant help by noticing chicken skin on some project leaders.
It is like mentioning a curse, we are all so much afraid of Murphy’s Law and the main engineering principle:
“If it’s working, don’t touch it!” that we endure whatever we have for the sake of safety.

As usual I will keep this on my blog as a reminder for future use.
It’s taken from Scott Ambler’s site http://www.agiledata.org/essays/databaseRefactoringSmells.html

1. Multi-purpose column. If a column is being used for several purposes it is very likely that extra code exists to ensure that the source data is being used the “right way”, often by checking the values of one or more other columns. An example is a column used to store either someone’s birth date if they’re a customer or their start date if they’re an employee. Worse yet, you are very likely constrained in the functionality that you can now support, for example, how would you store the birth date of an employee?
2. Multi-purpose table. Similarly, when a table is being used to store several types of entities there is likely a design flaw. An example would be a generic Customer table that is used to store information about both people and corporations. The problem with this approach is that data structures for people and corporations are different – people have a first, middle, and last name for example whereas a corporation simply has a legal name. A generic Customer table would have columns which are NULL for some kinds of customers but not others.
3. Redundant data. Redundant data is one of many serious problems in operational databases because when data is stored in several places the opportunity for inconsistency occurs. For example, it is quite common to discover that customer information is stored in many different places within your organization, in fact many companies are unable to put together an accurate list of who their customers actually are. The problem is that in one table John Smith lives at 123 Main Street and in another table at 456 Elm Street. In this case this is actually one person who used to live at 123 Main Street but who moved last year, unfortunately John didn’t submit two change of address forms to your company, one for each application which new about him.
4. Tables with many columns. When a table has many columns it is indicative that the table lacks cohesion, which it’s trying to store data from several entities. Perhaps your Customer table contains columns to store three different addresses (shipping, billing, seasonal) or several phone numbers (home, work, cell, …). You likely need to normalize this structure by adding Address and PhoneNumber tables.
5. Tables with many rows. Large tables are indicative of performance problems, for example it’s very time consuming to search a table with millions of rows. You may want to split the table vertically by moving some columns into another table, or split it horizontally by moving some rows into another table. Both strategies reduces the size of the table, potentially improving performance.
6. “Smart” columns. A “smart column” is one in which different positions within the data represent different concepts. For example, if the first four digits of the client ID indicate the client’s home branch, then client ID is a smart column because you can parse it to discover more granular information (e.g. home branch ID). Another example includes a text column used to store XML data structures; clearly you can parse the XML data structure for smaller data fields. Smart columns often need to be reorganized into their constituent data fields at some point so that the database can easily deal with them as separate elements.
7. Fear of change. If you’re afraid to change your database schema because you’re afraid to break something, for example the fifty applications which access it, then that’s the surest sign that you need to refactor your schema. Fear of change is a very good indication that you have a serious technical risk on your hands, one that will only get worse over time. My advice is to embrace change.

“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.”

– John Woods

ROFL.

Hi again, last March 26th I flew to Saskatoon to give the first conference of two for the INETA Launch. This event is part of the Microsoft Ready to Launch Tour. I had splitted the content in two conferences, one for developing the web application and the other one for the smart client and a bit of the data platform.
As you can imagine I was pretty nervous on this first conference. The first three demos worked fine but the one regarding Data Access failed, I was 10 minutes over the scheduled time and apologized to the audience telling them i would look into what had happened.
ASP.NET 2.0 makes the data binding quite easy, no need to write a line of code to pull out data from your sql server. Unfortunately, the easiest demo failed.
When I tried to reproduce the demo at the airport it failed again, it only worked once I was at home and had recovered from the 2 hours of sleep from the day before…
What the heck happened then? I’m a technician and certainly don’t believe in magic… I searched for user permissions, thinking perhaps the user I had used had no permissions in the database, I studied deeper ADO.NET 2.0, googled about the problem, read the reference to the point of exhaustion et-cetera.
Anyways, on my trip to Cuba this weekend I commented this issue to one of my ex-coworkers as he asked about the conference. He said, did you have both your Wi-Fi and your Ethernet card dead? I said, yes, I was completely offline when I was giving the conference…He said, well, you had no TCP/IP enabled and you certainly didn’t have the SQL Server Personal Edition, or configured your server to use something else…
OH NO! Whatta a gotcha! Damned!
He said, even if you put a network cable, as long as your network card sends packets, it should work…
Ok, I hope I’m able to make the trick this May 6th on the second conference, something like, now it works, now it doesnt, with my network cable on one hand 😉 , it’ll be fun…

Another reminder to myself, never say never. When I met .NET I swore to myself, I’m not going back, well, never say never.

Visual Basic 6.0

In Visual Basic 6.0, if you do not specify ByVal or ByRef for a procedure parameter, the passing mechanism defaults to ByRef. This allows the variable passed into the procedure to be modified in the calling program.

Visual Basic 2005

When you declare a procedure in Visual Basic 2005, the passing mechanism defaults to ByVal for every parameter. This protects arguments against modification. The declaration in the preceding example can be rewritten as follows.

Following the trend of my previous post I found this explanation on Kimberly Tripp’s blog and it sure cleared out a few things for me:

…understanding [of] TempDB – what goes there?

• Internal temporary objects needed by SQL Server in the midst of other complex operations. For example, worktables created by a hash aggregate will be stored in TempDB or interim tables uses in hash joins (almost anything that shows as “hash” something in your query plan output is likely to go to tempdb).
• User objects created with either # (for local temporary objects), ## (globabl temporary objects) or @ (table variables)
  • # = Local temporary object
    Local temp objects are objects accessible ONLY in the session that created it. These objects are also removed automatically when the session that created it ends (unless manually dropped).
  • ## = Globabl temporary object
    Global temporary objects are objects that are accessible to ANYONE who can login to your SQL Server. They will only persist as long as the user that created it lasts (unless manually dropped) but anyone who logs in during that time can directly query, modify or drop these temporary objects. These objects are also removed automatically when the session that created it ends (unless manually dropped) OR if being used by another session when the session that created it ends, when the session using it finishes using it (and it’s only as long as any locks are held). If other sessions need more permanent use of a temporary object you should consider creating a permanent objects and dropping it manually.
  • @ = User-defined Table Variable
    User-defined Table Variables were introduced in SQL Server 2000 (or, wow – was it 7.0?) and provide an alternative to temporary tables by allowing you to create a variable defined as type TABLE and then you can populate and use it in a variety of ways. There has been A LOT of debate over whether or not you should always use table variables or always use temp tables. My response is that I ALWAYS avoid the word always! My point is that table variables are NOT always better nor are temp tables always better. There are key uses to each. I tend to like temp tables in scenarios where the object is used over a longer period of time – I can create non-key indexes on it and it’s more flexible to create to begin with (SELECT INTO can be used to create the temp table). I also have the ability to use the temporary table in nested subprocedures because it’s not local to the procedure in which it was created. However, if you don’t need any of those things then a table variable might be better. When it is likely to be better – when you have smaller objects that don’t need to be accessed outside of the procedure in which it was created and when you only need KEY indexes (a table variable ONLY supports the indexes created by a create table statement – meaning PRIMARY KEY and UNIQUE KEY).

The origininal post is here
I’ve been following up the series SQLSkills is publishing on Sql Server 2005…
More will come up soon folks!

I promised myself that i would be blogging about CTEs in a near future.

Well, here I am, once again leaving a trace of what I’ve learned so far so I can use it as reference later.
Unfortunately for the T-SQL lovers that come from the Oracle world, the T-SQL enhancements
in SQL Server 2005 do not include arrays among the data structures, the workaround for array use is
to get hands on the SQLCLR and use the .NET framework…
Ok, but this post was about Common Table Expressions, what does arrays have to do with it?
Most of the time, T-SQL developers would use temporary tables whenever they could have used arrays,
to somehow keep a set of values for future use inside the batch.
The way of building temporary structures, name it tables, in your sprocs has evolved from sql server 7.0.
Initially you had the temporary tables such as #people:

Local Temporary Tables

CREATE TABLE #people
(
id INT,
name VARCHAR(32)
)

A temporary table is created and populated on disk, in the system database tempdb — with a
session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables
created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope
(usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when
the current procedure goes out of scope, but you should manually clean up the data when you’re done with it:

DROP TABLE #people

Or the

Global Temporary Tables

CREATE TABLE ##people
(
id INT,
name VARCHAR(32)
)

Global temporary tables operate much like local temporary tables; they are created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all sessions, until the creating session goes out of scope (and the global ##temp table is no longer being referenced by other sessions). If two different sessions try the above code, if the first is still active, the second will receive the following:

Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named ‘##people’ in the database.

I have yet to see a valid justification for the use of a global ##temp table. If the data needs to persist to multiple users, then it makes much more sense, at least to me, to use a permanent table. You can make a global ##temp table slightly more permanent by creating it in an autostart procedure, but I still fail to see how this is advantageous over a permanent table. With a permanent table, you can deny permissions; you cannot deny users from a global ##temp table.

In sql server 2000 it was introduced the concept of table variables:

Table Variables

DECLARE @people TABLE
(
id INT,
name VARCHAR(32)
)

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

Table variables are automatically cleared when the procedure or function goes out of scope, so you don’t have to remember to drop or clear the data (which can be a good thing or a bad thing; remember “release early”?). The tempdb transaction log is less impacted than with #temp tables; table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.

Then why not using always tale variables… Performance is the answer. I’m not going to go deep into this now but just post the url for the official white paper:
Limitations of table variables http://support.microsoft.com/default.aspx/kb/305977

And finally SQL Server 2005 Common Table Expressions, I guess I could call them the light weight temporary tables. their syntax is even lighter than
the syntax in table variables.

Pseudocode and Semantics

The recursive CTE structure must contain at least one anchor member and one recursive member. The following pseudocode shows the components of a simple recursive CTE that contains a single anchor member and single recursive member.

WITH cte_name ( column_name [,…n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

— Statement using the CTE

SELECT *

FROM cte_name

The semantics of the recursive execution is as follows:

1. Split the CTE expression into anchor and recursive members.
2. Run the anchor member(s) creating the first invocation or base result set (T0).
3. Run the recursive member(s) with Ti as an input and Ti+1 as an output.
4. Repeat step 3 until an empty set is returned.
5. Return the result set. This is a UNION ALL of T0 to Tn.

A simple CTE declaration:

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
SELECT ManagerID, COUNT(*)
FROM HumanResources.Employee AS e
WHERE ManagerID IS NOT NULL
GROUP BY ManagerID
)

GO

An even simpler use:

USE AdventureWorks;
GO
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO

So with CTEs there is no need of formally declaring your column types, only the column names, as the types are assumed from the
query definition. The syntax is for sure lighter. I’m not aware of performance issues related to CTEs, probably some kind soul could ad a comment here and let me know.

We so far cannot pass table variables and CTEs as parameters in stored procedures, this is definitely something that should come in future versions.

Bye for now.

A few years ago, there was a Mensa convention in San Francisco and a bunch of Mensa members were lunching at a local café. They discovered that their salt shaker contained pepper and their pepper shaker was full of salt. How could they swap the contents of the bottles without spilling, and using only the implements at hand? Clearly this was a job for Mensa! The group debated and presented ideas, and finally came up with a brilliant solution involving a napkin, a straw, and an empty saucer. They called the waitress over to dazzle her with their solution.

“Ma’am,” they said, “We couldn’t help but notice that pepper shaker contains salt and the salt shaker–”

“Oh,” the waitress interrupted. “Sorry about that.” She unscrewed the caps of both bottles, switched them, and said, “Will that be one check or separate?”

(I had this post as a draft since January, it’s time to get over the blogging lazyness)

I remember when sql server 2k came out that the main ad campaigns were about it’s xml support. I remember that, at the time, I didn’t know what the heck xml was, and why all the buzz about it.
I corrected part of my ignorance right after, note, part of my ignorance…
Anyways, apart from retrieving resulsets in xml format, with who knows what schema, I hadn’t used xml in sql server much, and thought that *that* xml support was all a programmer gal could wish for.
Oh, I was wrong!
My last project involved a great deal of importing xml into sql server 2000 and it was indeed interesting.
I noticed a few things I would like to blog about so I could keep a reminder for future projects.

1. sql server 2000 won’t import xml using a DTS package unless you have the “proper mapping” of your xml schema and your database schema. I was never able to tickle the proper mapping… My first idea was infering an xsd file out of the xml (I created that with a visual studio console app, loading the xml into a dataset and infering schema, but that didn’t seem to work as the proper mapping) See this article Importing XML into sql server 2000.
   
2. what about Excel? you theoretically can import xml into Excel, associate an xsd file to your spreadsheet and have it all imported into sql server 2000. Voila! it should be just a couple of clicks. Don’t run that fast. Excel won’t import complex xml, this is an xml with a complex schema, where there are several child nodes with more child nodes (complex nested types indicating a one to many relationships). I even tried to separate the xml for importing one complex type or table at a time and realized I had to save all of my .xls files as csv (comma separated text) to have them imported properly.

Ok, now that we ended up writing a a dot net utility to use ADO.NET, now that we load the XML and synchronize the dataset with the table in sql server 2k, lets check if I can retrieve the xml out of the database to be able to work with smaller chunks of data in xml format.

1. This part of the project was also fun indeed. The T-SQL clause for XML does not seem to work when some of the columns in your table are defined as text and will truncate the resulset over a maximun length.
2. SQL server 2000 is unable to retrive the xml with the schema you would like. Apart from the data truncation, you should make deep xslt with the data afterwards to have it properly complying with the schema you want.

Hopefully sql server 2005 will answer some of our prayers. There is a xml data type, so worse case scenario, you can load the whole document into a column in a temporary table(I know this is not a good practice to have a huge document in a table column), apply XQuery to it and be able to retrieve the chunk of information you do want to import into your tables. DTS packages for importing xml should be better, old sql server 2000 relied on scripting language for everything with the known drawback that scripting languages are typeless.

Said all this, I might blog about sql server 2k5 in a next post (I really like the CTE Common table expressions), …if I get over this blogging lazyness.
That’s all folks!