Create a clustered index and a primary key on existing tables

Snow_Flakes

Your data: unique little snowflakes

It’s the simple things in life that can trip people up. I find that creating primary keys and clustered indexes on existing tables is one that newish DBAs tend to trip up on, and so (like I do), I shall blog about it so there’s a nice, clear, explicit reference available.

Yes, Virginia, you most certainly CAN add a clustered index to a heap without explicitly dropping and recreating the table. Same thing for primary key constraints.

IMPORTANT: Creating a clustered index is a big operation, for large tables. This isn’t something you want to do without planning, on a production server. For more information, see http://sqlmag.com/blog/what-happens-if-i-drop-clustered-index

Requirements

Requirements for creating a clustered index on an existing table:

  • The table can’t have an existing clustered index.

That’s it. There’s no requirements for uniqueness or NULLability, or NULLs. Note, however, that after a column is part of a clustered index, you can’t alter the properties of that column (so, for example, you couldn’t then make the column NOT NULL).

Requirements for creating a primary key constraint on an existing table:

  • The table can’t have an existing PK constraint.
  • The column can’t have any NULL values.
  • The column must be defined as NOT NULL.

Demo time! Clustered Primary Key

So, let’s take a simple example, where we want a single-column, clustered primary key (on column [id]):

CREATE TABLE t1 ( id INT, val INT );
INSERT INTO t1 VALUES (1, 1), (2,2), (3,3);

The table meets 2 of the 3 requirements for a PK constraint, but [id] is a NULLable field. Let’s fix that:

ALTER TABLE t1 ALTER COLUMN id INT NOT NULL;

After that, adding a primary key constraint to an existing table is easy. While we could run a CREATE CLUSTERED INDEX statement first and then the PK, why don’t we just run a single statement and do both at once:

ALTER TABLE t1 ADD CONSTRAINT pk_t1
 PRIMARY KEY CLUSTERED (id);

That’s it!

Demo time! Nonclustered Primary Key

Not all primary keys are clustered. In this example, [id] is our primary key, and [val] is our clustered index:

CREATE TABLE t2 ( id INT, val INT );
INSERT INTO t2 VALUES (1, 1), (2,2), (3,3);

Again, we have to make sure the PK column is NOT NULLable:

ALTER TABLE t2 ALTER COLUMN id INT NOT NULL;

Now we can create the primary key constraint and the clustered index:

ALTER TABLE t2 ADD CONSTRAINT pk_t1
PRIMARY KEY NONCLUSTERED (id);
CREATE CLUSTERED INDEX ix1 ON t2 (val);

Ta-dah!

Reference

Remember kids, a clustered index is not the same thing as a primary key.

Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.

The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

-http://msdn.microsoft.com/en-us/library/ms190457.aspx

Whereas…

A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. Because primary key constraints guarantee unique data, they are frequently defined on an identity column.

When you specify a primary key constraint for a table, the Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. If a primary key constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the primary key constraint definition must be unique.

-http://msdn.microsoft.com/en-us/library/ms179610.aspx

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

Have you checked out Minion Reindex yet? It’s our free, easy, comprehensive index maintenance solution. You’ll love it, seriously.

Allow_Page_Locks for Reorgs

minion reindex-01This is a cross-post from MidnightSQL.com and Sean’s blog, DBARant.

There are many settings that get set one way or another in DBs and in tables. Allow_Page_Locks is one of them that you may not be able to do anything about because your vendor may require it and your situation may require it as well. Normally it’s set to true, but it does get set to false and when it does, it typically needs to stay that way.
The problem is that when you reorganize these indexes that have allow_page_locks = false, then the reorg will fail. But the problem is that if you change it, you may see increased blocking issues. So what is there to do?

Well, the answer is Minion Reindex. We allow you to define pre and post code at the table-level that you can use to switch this option on and then off again when the table is through. And better yet, we even give you the code to discover all of these issues in your database and fix them. In the Minion Reindex download folder you’ll find a Queries folder. This folder has a sql file that you can run and it will insert the table-level exceptions with the proper pre/post code. The precode sets allow_page_locks = true and the postcode sets allow_page_locks = false. This way you can still have the setting the way you need it, and perform your index maintenance too.

And of course, Minion Reindex is completely free so download it now and you won’t be sorry.

Just an idea: Bare Bones SQL

I’m reading Andy Warren’s excellent essay “Thoughts on raising funds and spending funds for PASS chapters”, and it’s bringing together a bunch of long-stewed thoughts. Simply:

What if there’s room for “Bare Bones” SQL user groups?

The bare bones basics of Bare Bones SQL

For those who don’t want to read a huge essay, the essentials:

  • There’s room for a vastly simplified SQL educational forum in the pantheon.
  • Something locally run by one or two people. No central governance.
  • Without ANY money attached.
  • Without any frills.
  • With a firm focus on education.
  • Naturally small groups are perfectly fine. No emphasis on large growth.
  • When it’s time, pass it on or let it go. There’s nothing wrong with impermanence.

What we have

The current spectrum of user group offerings (in the SQL Server/PASS sphere) holds everything from incorporated UGs with sponsored food, to small low-fund UGs, to the (basically) cash-free virtual UGs. There’s room for all of those, demand for them.

But I personally don’t tend to make time for the virtual UGs – it’s not much of a networking event, and the classes themselves are only sometimes enough to prompt me to sign on. I don’t habitually go to small UGs, though I’ve been known to make out of town trips to speak at them. And I don’t go to my local, large, incorporated UG, because I’ve run into too much frustration with some of the politics.

There’s room for all of these, there’s demand for all of these. But I’ve talked to plenty of people who might be interested in a more bare bones approach to user groups.

What we could do

Because his article is what got me on this train of thought, I’m going to talk about the idea of Bare Bones SQL (BBSQL) as it relates to the points in Andy’s article.

Introductory ideas:

  • “chapters work hardest to find speakers and sponsors” – It would be nice to eliminate the sponsor aspect of this, cutting your work (as a UG leader) in half.
  • “a reluctance to manage (or even have) a checking account or money” – I can see that. I wouldn’t want to either. Bare bones SQL eliminates that hassle.

What funds are for:

  • “food for attendees” – If you could find a venue that allows it, BBSQL meetings could be bring your own dinner events. This doesn’t appeal to everybody, but it would to me. (I HATE generic pizza month after month.)
  • “Facility fee.” – Yeah, I’m thinking maybe libraries or universities, if the local MS shop tries charging a fee. (Some of them have, for security guards or other reasons.) Finding a free venue is historically a challenge, but we knew getting into this idea that BBSQL wouldn’t be easy; it just presents a different set of challenges.
  • “SWAG” – Nice at some events. “Bare bones SQL” gets the idea of “no swag” across with the name, so that’s nice.
  • “Speaker gifts.” – See above.
  • “SQL Family events.” – A very nice thing to do. BBSQL could take up a donation, if we really wanted to, for such things. Or just send a bunch of nice messages.
  • “Support items” like projectors, etc. – Again, nice, but a luxury in the BBSQL philosophy. Perhaps the founder and chair could pony up if something is really needed, or one could, again, take up a collection, but in all I’m thinking “Bare bones” is in the name. I bet the library has a projector, and all of us have laptops. (For those of you objecting about the founder coming up with the money, I say well then, don’t do that. Also, Andy mentions: “It’s worth noting that it’s not uncommon for the chapter leader/team to pay out of pocket for food/expenses because they didn’t have time to find a sponsor.”)
  • “Banking/account fees” – No money on hand, no problem.
  • “…SQL Saturday” – I don’t yet completely envision what, if any, large events BBSQL would do, but true to the name, I think they should be bare bones event. Sean and I have pictured putting together simple boot camps, either at home or at a venue, with only a couple of tracks and maybe 3-4 speakers per track. Small events.
  • “Guarantee speaker travel costs” – This is difficult for even medium to large PASS user groups to do on any kind of regular basis. For BBSQL, one would rely on catching talent as it comes into town – this might make a somewhat flexible meeting schedule imperative – and/or relying on local talent, and/or getting remote presentations (as many smaller UGs already do).

SQL community as a business

What comes out of all of this: When you make SQL education into a business of any kind, even if it’s putting back money for the next meeting or event, you start to think in terms of growth. We’ve seen some discomfort in the SQL community with the ever-expanding PASS business model (e.g., the “Professional Association of SQL Server” isn’t really JUST for SQL Server any more, which has made a good many people feel a little disenfranchised).

Businesses want growth, because the bigger you are (the philosophy goes), the harder it is to fall apart. But let’s turn the underlying assumption on its head: why shouldn’t BBSQL groups come and go as interest waxes and wanes? What’s wrong with a group lasting a handful of years, or 8, or 10, and then fading? It’s perfectly possible to help a double handful of people with their careers, book club-style, and then go on about other things.

Permanence is tied to money, and money is tied to permanence. In short: permanence isn’t a goal of BBSQL.

SQL community and politics

Here’s a sticky bit to the idea of BBSQL: I think they should be small autocracies. An autocracy turns out TERRIBLY when it’s large and/or compulsory, but is absolutely perfect for small models like this.

We have on one hand Conventional UG (CUG), with a board and maybe some finances and plans. And we have a BBSQL. The CUG meets, discusses, argues, can have dissention, gets flak from the user base about decisions made – all the standard things that happen when you have a group of people running things. This isn’t always a bad thing – after all, everybody needs someone to balance them out, regulate extremism, and all that. But I have found that I’m the type of person that runs like anything to get away from the kind of conflict that arises out of board maneuvering* and the often-resultant politics.

And so I’m drawn to the idea of the BBSQL. I found it, I run it, I advertise to people. Maybe in my first year it’s just me, Sean, and two or three friends. Maybe the next year we get a dozen more, maybe not. But it’s mine, and I run it the way I want to. If I run it in a way people don’t like, they’re not going to stay. That’s the joy of the small, non-compulsory autocracy; people “vote with their feet”. Pure simplicity, ish.

Diversity in groups

This leads us to another aspect of Bare Bones SQL: it’s not homogeneous. Not all chapters would look the same. Indeed, some BBSQL groups might look completely foreign to other groups. Maybe this one decides it does want to incorporate some conventional aspects to UGs. Maybe that one has a code of conduct. Maybe a third is dedicated to educating college students. Maybe a fourth is for “earthed” DBAs only, who knows. What’s wrong with that? All groups are by nature exclusionary, in that they focus on a specific area of interest. And in general, what’s wrong with diversity?

As long as the entire playing field is equal and open to anyone willing to start a group, I think that’s fine. Maybe this idea takes off, and next year there are a few dozen BBSQL groups dedicated to the enthusiastic overuse of SQL Profiler. Well, they’re your groups, and that’s fine by me. (You’re wrong in your content, of course, but it’s a free idea, so have at it.)

I can’t stress this enough: part of the nature of BBSQL is that there is as much room for differences of opinion as there are people who agree with you. (There’s a far better way to say that, but I can’t think of it just now…)

Bare bones conferences

“But what about conferences?” I think conferences can fit in the BBSQL philosophy, sure. But it’s not going to be the kind of conference you’re used to. Even SQL Saturdays, which definitely have the right idea, still have sponsors and swag and speaker gifts and shirts and paid venues, and so on and on.

A BBSQL conference would need to be a joint venture between several groups. It would probably need money, of course – you can’t often get even a medium venue for free – so that means a charge, and most likely sponsors as well. The trick is finding the balance between size (I doubt any BBSQL conference would be in the thousands of attendees), cost, and usefulness to attendees and sponsors.

In short, I imagine a SQL Saturday, with way fewer trappings. Maybe I’m thinking too small, maybe not.

Objections

There will be quite a few objections, and many of them will be valid. There are problems with venues, with dedication to the idea of frugality, with attracting members, with the very small nature of these groups. Again, we’re trading one set of problems for another. It’s a matter of which set is less burdensome to the organizers and members.

A very large number of you will have absolutely no use for this idea. That’s perfectly fine, too. I’m not proposing the replacement of the existing structure. I just think there’s room for another kind of group, and I wanted to identify what that would look like.

How do we get started?

Frankly, I’d like to put one of these together. The initial plan would look something like this:

  1. Set up a central website. This is one of the few organizational tools that I think is necessary.
  2. Find a place and a more or less regular time.
  3. Line up speakers. (Between me, Sean, and a handful of friends, we have enough speakers for a year or more.)
  4. Announce on Twitter, blogs, etc.
  5. And, go.

It’s really not that hard, and that’s the point. It shouldn’t be hard to learn, to network, to further IT education in the community.

BBSQL central tenants, as I see them:

  • A vastly simplified SQL educational forum.
  • Run by one or two people. No central governance.
  • No money attached.
  • No frills: venue and little else.
  • Firm focus on education.
  • Naturally small groups are perfectly fine. No emphasis on large growth.
  • When it’s time, pass it on or let it go. There’s nothing wrong with impermanence.

Your thoughts?

I want to know your objections. I want to know what I’ve overlooked, or forgotten. I want to hear arguments and agreements and complaints. What’s working already? What’s not?

And, I leave you with this last quote from Andy’s excellent article:

It’s entirely possible to run a chapter without ever touching cash or having a sponsor. I think it’s limiting, but it’s possible. If that’s your choice, I’m good with that. The goal is, after all, to connect/share/learn. However you get there!

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

While you’re here, go check out our new, free reindex solution Minion Reindex! Minion Reindex is easy to install, and features Live Insight, several levels of ordering and configuration.

*Manoeuvring.

Real news, real tech.