School started for my two university-attending sons, and one of them was ordering his books on Amazon from the Marketplace, which is resellers and Amazon partners, selling both new and used. He ordered the books before classes started, expecting to receive them by the end of the first week of school. On Thursday of the first week, one of the vendors sent him email, saying that they didn't have the book after all, and were refunding his money. He was very frustrated because he had an assignment due the following Monday, for which he needed to use that book. I was really shocked that they would have said they had the book, when they really didn't.

But today, I realized it is not all that uncommon. I was checking on Amazon to see if they had put up any pre-order information for my new book.  Well, I didn't see any news about my next SQL Server book, but I did see this:

http://www.amazon.com/Oracle-10g-Kalen-Delaney/dp/8448149394

I wrote a book on Oracle 10g and I didn't even know it! I even followed the link to buy new or used copies from resellers, the same kind of link my son followed, and saw this:

So the book is apparently available... a used copy from one reseller and a NEW one from another. I'm tempted to order it and see what I said. Because they say it is IN STOCK....

Or if one of you decides to buy it, let me know how you like it!

Thanks

~Kalen

As I am working on my new SQL Server 2008 internals book, I am finding many test situations in which I need to create a table with LOTS of columns. First of all, you're probably aware of the new SPARSE column feature that allows you to have up to 30,000 columns in a table!  I did some testing with sparse columns, but just today I realized I had never actually created a table with more than the old limit of 1024.  In addition, the new row compression had some special tricks for dealing with lots of columns, and groups columns into clusters of 30 columns each. (You'll have to wait for the new book to get the details.)

Yesterday, when writing about row compression, I realized I needed an easier way to create wide tables, so I wrote a script that allowed me to specify the number of columns, and also get a little creative with the datatypes, lengths and properties of the columns I was creating. I'll show you that script in just a minute!

Then just today, I read a blog entry by Simon Sabin, who indicated that even with sparse columns, you could not create a table with more than 1024 columns. In the CREATE TABLE statement, you had to limit yourself, but then could use ALTER TABLE to add more columns. Although I hadn't tested this yet, I was intrigued, and realized I could use my new script to test out this claim.

So here is my original script. You can replace the 100 (for the value of @numcols) by the number of columns you'd like, but the script will create one more, because it starts with a ID int identity column. So the script as is will create a table with 11 columns. In the loop that adds columns to the creation string, I had three different datatypes of columns that I can use. One third of the columns will be type int with a default of 0, one third will be char(5) with a default of 'hello' and one third will be varchar(25) with a default of 'this is a longer message'. You can modify this however you like. You can change the datatypes within the loop, or add more conditionals and change expression to use modulo of some other number.

DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 100;
SELECT @tabname = 'wide' + CONVERT(varchar, @numcols);

SELECT @create = 'CREATE TABLE ' + @tabname +
      ' (ID int IDENTITY, ';

SELECT @col = 1;
WHILE @col < @numcols BEGIN
    IF (@col % 3) = 0
       SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int default 0,';
    IF (@col % 3) = 1    
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' char(5) default ''hello'',';
     IF (@col % 3) = 2     
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(25) default ''this is a longer message'',';
  SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int default 0);'
PRINT @create 
EXECUTE (@create) 
EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');
EXEC ('SELECT * FROM '+ @tabname);

To use this script to test the creation of a table with more than 1024 columns, I knew some (or most?) of the columns would need to be sparse. In addition, I found this Connect entry which indicated that if you do have more than 1024 columns, the table must have a column set, so it was easy enough to make that the last column that is added to the create string after the loop.

DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 1200;
SELECT @tabname = 'wide' + CONVERT(varchar, @numcols);

SELECT @create = 'CREATE TABLE ' + @tabname +
      ' (ID int IDENTITY, ';

SELECT @col = 1;
WHILE @col < @numcols BEGIN
    IF (@col % 3) = 0
       SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int sparse,';
    IF (@col % 3) = 1    
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(5) sparse,';
     IF (@col % 3) = 2     
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(25) sparse,';
       SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' xml column_set for all_sparse_columns);';
PRINT @create 
EXECUTE (@create) 
EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');
EXEC ('SELECT * FROM '+ @tabname);
 

Running this script, I was able to create a table with 1200 columns.

And you can too, if you're using SQL Server 2008.

Have fun!

~Kalen

You're probably aware that the metadata interface changed completely in SQL Server 2005, and we no longer have direct access to the system tables. Instead, Microsoft provided us with two sets of views: the catalog views and the compatibility  views.

The catalog views are the preferred interface going forward, as they are fully documented and the naming conventions are much more user friendly. There are also no bitstrings, like the status fields that need to be decoded. Each attribute stored in the view is available in a separate column.

The compatibility views are there to let you use the same code that you used in SQL Server 2000, hopefully temporarily, while you convert your code to the new metadata using the catalog views. The compatibility views have the same names as the system tables in SQL Server 2000, and the column names are the same.

You might think that writing a query against the compatibility views would then allow you to continue to write metadata queries that work with either SQL 2000 or SQL 2005. This might be true to some extent, but we might have to define what we mean by 'work'. Another change that happened in SQL 2005 was the separation of users and schemas.  Users can have permissions granted to them, including permissions to create objects, but the objects exist in containers called schemas.

In SQL 2000, users and schemas are treated interchangeably. If you create a user sue, SQL Server automatically creates a schema sue which is the user sue's default schema. When the user sue selects from an unqualified object t1, SQL Server 2000 assumes she is selecting from an object sue.t1. (If there is no object sue.t1, SQL Server then looks for dbo.t1.)

In SQL 2005, a user sue can have any schema as her default schema and there may or may not be a schema named sue.  When accessing an object that is not in  your default schema or in the dbo schema, you must qualify the object with the schema name, not the owner  name.

So what's my point here?

If you're looking up information about objects in aSQL 2005 database, would you rather know the owner of your objects or the schema they are contained in? I suggest that it is more often the case that you want to know the schema, so that you can then access the objects you are exploring.  And here's where the incompatibility of the compatibility views comes in.

In SQL 2000, the following code will give you the owner and name of all your user tables, and you can then use the information returned to access the objects. I wrote this query this morning in answer to a newsgroup posting asking for information about getting owner and object information from both SQL 2000 and 2005.

SELECT u.name as [user],
       o.NAME as [object]
FROM  sysobjects o
         INNER JOIN sysusers u
           ON o.uid = u.uid
WHERE   type = 'U';

In SQL 2005, the same code will give you the owner name, but that is NOT the name needed to access the objects. Here's an example that uses a test database, and creates a login sue, a user sue, and a schema called sue_schema. The user sue is given permission to create tables and to alter the sue_schema schema.  The user sue then creates a table called sue_table.

USE testdb;
GO
CREATE LOGIN sue
    WITH PASSWORD = 'sue_password';
CREATE USER sue FOR LOGIN sue;
GO
CREATE SCHEMA sue_schema;
GO
GRANT CREATE TABLE TO sue;
GO
GRANT ALTER ON schema::sue_schema TO sue;
GO
EXECUTE AS user='sue';
GO
CREATE TABLE sue_schema.sue_table (a int);

We can run the metadata query above, and add a filter to look for sue-type objects:

SELECT u.name as [user],
       o.name as [object]
FROM  sysobjects o
         INNER JOIN sysusers u
           ON o.uid = u.uid
WHERE   type = 'U' AND o.name like '%sue%';

I get these results:

user    object
------- ---------
sue     sue_table

However, for table access, this information is not helpful. I cannot execute the following statement, even as dbo with full privileges:

REVERT; -- so I no longer execute as user 'sue'
SELECT * FROM sue.sue_table;

So is the metadata query really compatible? It may be compatible in the facts it gives back, but in function, it is not. For SQL Server 2005, the query does not tell us what we need to know in order to access object. Instead, we'll need a catalog view sys.schemas, which has no equivalent in SQL 2000.

SELECT s.name as [user],
       o.name as [object]
FROM  sysobjects o
         INNER JOIN sys.schemas s
           ON o.uid = s.schema_id
  WHERE   type = 'U' AND o.name like '%sue%';

So because of the split between users and schemas, we can't really write one query that gives us information about how to access objects in both SQL 2000 and SQL 2005.

~Kalen

If you check my schedule, you'll see my summer and early Fall are spent mostly close to home. This is a good thing, because I have a book to write!

But starting in October, I'll be gone at least every other week, with two trips to Europe and one to the East Coast of the US.

In early October, I'll be presenting at a multi-city Summit for the Stockholm-based training company, Cornerstone.  Although I've been to Stockholm several times, the Summit will take me to 3 other Swedish cities which I have never visited. And of course, one of the best parts of the trip is that I'll get to spend time with my good friend Tibor.

Following that is a private class in Connecticut, where I have several very good friends, including MVP Roy Harvey.

In early November, I am very excited to be presenting 3 seminars in the UK for SQLKnowHow. This is a collaboration of UK-based SQL Server consultants and trainers who are putting together a whole suite of events. I'll be presenting two one-day seminars and one three-day seminar, and getting to work with my friend Tony, as well as his partners Simon Sabin, Allan Mitchell  Darren Green.

I'll get back from the UK in time to go to Colorado Springs to help my mother celebrate her 90th birthday.

Near the end of November is the big PASS conference, at which I'll be delivering a pre-conference seminar, as well as a spotlight session.

In early December, I finish out my year's travels with a 5-day  public class at my oldest partner, SQL Soft+. It's in Portland, Oregon, which is about a 3.5 hour drive for me, so I can take my own car, and not worry about those silly airline baggage restrictions!

I hope to see many of you at some point in my travels!

~Kalen

Sorry, I'm not actually going to tell you how to read the log. I'm just going to talk about it... and whether it's a good thing to be able to do or not, or whether it's an absolutely crucial feature that MS needs to provide for us immediately, in a hotfix, if not sooner. Forget about fixing bugs, I want to read the log because I forgot to set up a trace beforehand....

You may have noticed that my blogging frequency has fallen off. One or two of you also noticed that I am no longer writing a regular article each month for SQL Server Magazine. Those two facts are related. I have cut back on non-essential activities to try to get my next book out as soon as possible.  It looks like I might even finish in time to get the book out on the shelves by early next year. Stay tuned...

Since I couldn't bear to not do anything for SQL Server Magazine, I started writing the commentary in the weekly email newsletter.  Actually, I do it every week but the fourth week of the month. If you like, you can sign up for this free newsletter here.

My commentary last Thursday seemed to have rattled some cages. Before I even woke up Thursday morning, there were already two comments on the site, and someone sent me a personal email about what I wrote.  By now, there are quite a few more comments. I basically wrote about the need for a log reader tool. It wasn't deeply technical; it's just a commentary after all. You can read it here:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=100076

But boy, did people get upset. They called me bad names... well, if 'mediocre' can be considered a bad name...

So I responded as follows:

Wow... I have never gotten so many comments so quickly about one of my articles. I must really have touched a nerve here!

There is a difference between the actual data rows referenced by the log, and the log format. It's the log format, and giving people full details about what is in the log, that is propriatary information. There is nothing specifically bad about giving people that information. However, calling me names because I don't stand up on a soapbox and DEMAND that MS add this functionality seems a little extreme. There are plenty of other things MS could do with the product and providing a log reader tool is way down on the list.

Yes I realize it is important to some people, but there are many other ways to get this information through tracing etc. If the developer resources are limited at MS, I would much prefer they spend their time on more important stuff. MS knows it's important that people have this information, that's why they added a great deal of additional tracing capabilities in SQL Server 2008.

Also, keep in mind that a log reader tool wouldn't help you debug problems with logic, or with bad reports due to faulty SELECTs. If your WHERE clause was written badly, a log reader tool could tell you which rows were affected, but not WHY. You'd need a tracing tool for that. Vogelm's comment that a log reader tool would help troubleshoot bad queries from 3rd party apps is not true; you need to see the statements for that, not just the affected data.

I do appreciate kbreneman's comment that the real problem is one of perception. MS should make clear that the transaction log is not an audit tool; if you want auditing, you need to set it up on your own, because you're the only one who knows what's important for you to capture.

(The only way to respond to comments is to write a comment of my own, and then the form insists that I rate the article I am responding to. I always feel a bit weird having to rate my own articles.)

Since I wrote the article, I have found out that Lumigent does have a log reader tool for SQL Server 2005, but I have heard less than stellar reviews about its ability to capture some of the more interesting datatype activities that are now possible in SQL Server 2005. And their website still doesn't list any version numbers.

I can't stop thinking about this, so I thought I would open up the issue to a wider audience.

How important do you think it is that Microsoft provide a log reader tool for us?

Thanks!

~Kalen

I can't help it; I'm a mother. I am so excited about what my son is doing that I just have to share it. My #2 son  is a budding actor and vocalist, and to earn money this summer he is working as a clown for a large organization that provides clowns, rides, games, balloon artists and magicians to local events. I found out several months ago that he was going to be working at the Microsoft Picnic this summer, and I started wondering how I might go about getting in to see him in action. But today, as he was walking out the door in full clown makeup and regalia, I found out he was working at a private picnic for the Gates Foundation! At this very moment, he could be making balloon butterflies, or unicorns, or puppies, or bears, or swords, for Bill and Melinda's kids! So far, he hasn't allowed us to take a picture of him in his makeup, but maybe he'll relax about it and I can get one posted.

And what is his poor mother doing while he is out entertaining the hoi polloi? I'm working on my next book. I spent much of yesterday researching the internal storage format of the new date and time data types, and today I started writing about them. It's actually pretty exciting and I'll blog a bit about it in the next day or two. Even though I'm not at the party, I still love what I do.

Have fun!

~Kalen

I was just tagged by Andy Leonard, and truth to tell, I don't read Andy's blog all that often because he talks about really developer oriented stuff, which is a little outside my focus area now.  But I was intrigued by the concept of a techblog meme, which I'd never seen before. I'd only seen memes discussed on my daughter's food blog, but at least I knew what they were because of that. You can read hers here, here and here. And then I realized I shouldn't have been quite so curious, because he tagged me! I saw today that Denis already responded, and tagged a whole bunch of people. I thought there was supposed to some sort of limit on the number of people you could tag; otherwise all the good tagees would get tagged too quickly. :-)

Although the meme is about software development, which I don't really do, I figured I could answer most of the questions, but for some of the answers I substitute 'working with computers' for 'programming'.

How old were you when you first started programming?

I was 16 years old and a Junior in High School. 

How did you get started in programming?

I took a night class at a local Community College.

What was your first language?

Fortran... and it was mostly boring programs, but the 'idea' of programming fascinated me!

What was the first real program you wrote?

Hmm, what counts as 'real'? Commercially viable? Or just one that other people wanted to use? My first was a game I wrote for the Atari 800 that helped preschoolers to memorize their phone number. (My daughter was a pre-schooler at the time.)  The teacher wanted a copy, then all the other teachers, then friends of the teachers, etc, etc....

What languages have you used since you started programming?

Fortran, Basic, PDP Assembly Language, Logo, C, Pascal, Lisp, Sequel, SQL, SQL, SQL, SQL.....
(...and probably a few I've forgotten)

What was your first professional programming gig?

I've actually never had a 'programming gig'. My first paid job with computers was being a teaching assistant for Computer Science classes at UC Berkeley.

If you knew then what you know now, would you have started programming?

Too hard of a question. I absolutely love what I do, but I can see lots of other paths my life could have taken, if back when I was 16 I knew everything that I know today. :-)

If there is one thing you learned along the way that you would tell new developers, what would it be?

Have fun!

Who are you calling out?

Lara Rubbelke
Tibor Karaszi
Kimberley Tripp
Linchi Shea

I've said many times that my favorite new feature in SQL Server 2005 is the new metadata, in particular the new Dynamic Management Objects. When I have to do troubleshooting on a SQL Server 2000 system, it is worse than painful, not to have my favorite tools like sys.dm_tran_locks, sys.dm_exec_cached_plans and
sys.dm_exec_query_plan. By now, on the eve of the release of the version AFTER SQL Server 2005, my transition to the new metadata is almost complete.

With one notable exception...

I have always used sysprocesses constantly for troubleshooting information. Although most of the useful information that allows me to see what sessions are using lots of memory, or performing lots of I/O, can found in sys.dm_exec_sessions, there is one piece of information that isn't there. Sysprocesses contains a columns called open_tran which reflects the transaction  nesting of each session. If a session issues four BEGIN TRAN commands, with no COMMITs or ROLLBACKs, their session will have an open_tran value in sysprocesses of 4. Any open_tran value greater than 0 might mean that a transaction is holding locks and blocking other processes, or it might be keeping the transaction log from being cleared. If you ever notice open_tran values in higher than 2 or 3, it's a pretty good indication that a developer doesn't know much about SQL Server transaction management. I use this value all the time, and once had to troubleshoot a system where dozens of connections had open_tran values in the 20s and 30s! (For more details about nesting transactions, see my earlier post.

So imagine my surprise when I discovered that the sys.dm_exec_sessions view, which is supposed to 'replace' sysprocesses in SQL Server 2005, has no column to provide this information!  Another view, sys.dm_exec_requests, has a column called open_transaction_count, which you might think would be the same thing. And it actually is the same information, but the sys.dm_exec_requests view only returns rows for sessions that are currently active, i.e. currently running a query. The need for examining the open_tran value is greatest when looking at those sessions that aren't doing anything, but are just sitting there with an open transaction. Those are the processes that you need to troubleshoot. I had felt that the omission of open_tran (or open_transaction_count) from sys.dm_exec_sessions was just an oversight, and it would be 'fixed' in the next release. But it appears I am to be disappointed. I just checked sys.dm_exec_sessions in RC0, and there is still no open_transaction_count column.

So long live sysprocesses!

~Kalen