An easier Table Design View in Sql Server Management Studio

January 25, 2008 • from Maui, Hawaii • 14 comments

On this page:

One thing that bugs me to no end is the SQL Server table designer and its default view for creating and editing columns. Entering column properties is pure mousercise as you have to jump back and forth between different input panes. I don't know about you but this is one of the most user hostile user interfaces I can imagine. The original view looks like this:

I find myself constantly bumping back and forth between the top pane and the column properties view which usually has to be scrolled to get to common values I'd like to enter. It's a painful UI experience. Oddly you also can't control what columns display in this funky view.

I've used this for ages and cursed it ever since. This goes way back - the SQL Management Console pretty much had the same sort of view if I remember right.

Well, it turns out that you CAN get a different view if you use the Database Diagram Designer. In the diagram designer you can get a view with more information by creating a custom view like this:

What's cool is that you can create a new custom view, save it and then switch to it as needed in the designer. It sucks that you have to go to the Diagram Designer to get this view - it would be much cleaner if you could get this same conformability in the standard table editor, but this is better than nothing.

Using this layout it's much more efficient to create new tables and fill in relevant column info with one simple pass rather than having to jump around all over the place and it saves a shitload of time especially when first setting up a new database.

I am embarrased to say that I didn't find this until recently when I stumbled into the Table View menu in the diagram designer (which I rarely used before, but now use quite frequently <g>).

It's not very discoverable feature, so hopefully this gives a some of you an "aha" moment like it did for me...

The Voices of Reason

Dennis
January 25, 2008

# re: An easier Table Design View in Sql Server Management Studio

Aha! *g* - Exactliy the same with me, Rick!
If you know someone at the SQL-Server team, I would suggest to let them know.
There's no point in providing different UI's for the same thing. And I can't imagine any database designer that disagrees with you on this point.

Greets from Germany,
Dennis

Steve from Pleasant Hill
January 25, 2008

# re: An easier Table Design View in Sql Server Management Studio

Aha, but you're a day late.

I had to design out 8 or 9 tables yesterday.

One thing I did do to build a few tables that had many of the same fields: after the first table, select, right-mouse, and script the "create" sql into a new script pane. Edit as needed and then run it to build the 2nd table. Hi-tech, no?

John B
January 25, 2008

# re: An easier Table Design View in Sql Server Management Studio

Thanks for the tip!

Another way to avoid mouse aeorbics is to use the F6 key. That will toggle between the top/bottom panes. An old Access trick that still works.

yaip
January 25, 2008

# re: An easier Table Design View in Sql Server Management Studio

than you! thank you! thank you!

Vasim
January 25, 2008

# re: An easier Table Design View in Sql Server Management Studio

SQL Server table designer is such a pain, what drives me absolutely crazy is to constantly expand the (Tables And Columns Specification) when in Manage Relationship Dialog! Ahhhrg!

.Net Adventures
January 26, 2008

# re: An easier Table Design View in Sql Server Management Studio

Thanks. Nice tip ! Now the SQL Managment Studio looks more user friendly .

Mitch
January 28, 2008

# re: An easier Table Design View in Sql Server Management Studio

Man! I couldn't agree more. I hate the table designer!!!!!!! Nice trick.
Thanks John for the F6 bit.

Doug Osborne
January 29, 2008

# re: An easier Table Design View in Sql Server Management Studio

Pretty neat trick Rick - any way to make that the default view when designing a table in the Tables view ( Dare to dream...)?

AC
January 31, 2008

# re: An easier Table Design View in Sql Server Management Studio

What's wrong with, heck, just writing Sql? I haven't used designers in forever, and truthfully I'd rather just stick to plain old (scriptable) sql. I guess I just never got past the clunky UI's they give you.

I'm not just being a Nancy either. How do you work on a big team and coordinate db changes without having an update script? What about when you need to restore the db? How would you possible remember all the things you did?

We script everything so that nightly builds can take a copy of a production database and apply all the change scripts. No surprises come testing, deployment, etc. Your iron-fist DBA's can QA your work more easily, etc.

Reluctant DBA
February 01, 2008

# re: An easier Table Design View in Sql Server Management Studio

I second the 'just write sql' approach. I'm an old foxpro programmer and prefer just issuing commands to a low-frills command line processor rather than to a fancy/slow gui anyway. MS is good at GUIs but is not perfect. it will take me 3 minutes to create table(pk int primary key identity(1,1)), crapdata varchar(255)) in the GUI, with 2 minutes waiting for it to paint. Takes me 10 seconds to write and exec.

Reluctant DBA
February 01, 2008

# re: An easier Table Design View in Sql Server Management Studio

ok 20 seconds with corrections:
create table mytable(pk int primary key identity(1,1), crapdata varchar(255))

Rick Strahl
February 01, 2008

# re: An easier Table Design View in Sql Server Management Studio

Ah but that's the point isn't it? Sure you can type a simple table definition by hand, but if you something a little more complex - default values, nullables, precision, identity maybe a field description - it's quite easy to make a mistake and then it takes considerably longer most likely to debug your text. Using the designer you pretty much assured you get your syntax right.

To each his own, but if I have a validating tool at hand I'll pretty much prefer using it over hand typing even if it's slower. I don't write enough SQL table definitions to be a quick typist of this stuff in the first place...

techvslife
May 27, 2008

# re: An easier Table Design View in Sql Server Management Studio

I can only get this in Database Diagram section after creating a custom view there; I can't get this view to appear in table design, even when selecting "Custom" from Tools, Options, Designers, Table and Database Designers, default table view option.
There are also some funky bugs, in the February ctp on this; for example, I sometimes get japanase characters appearing for the u and e in Default Value.

Joshua
February 25, 2014

# re: An easier Table Design View in Sql Server Management Studio

Another way to do something like this is add the column Description property to the table designer view. Then you always have the description handy when editing your table...

Look here for how to do it:

http://codebyjoshua.blogspot.com/2014/02/add-column-description-property-to.html

Rick Strahl's Weblog