Open table + NULLs

In SQL Management studio, if I right-click on a table and click "Open table", it brings up the table, similar to return all rows in 2000. But if I go to the last row, and edit data in a cell and the word "NULL" is that cell because there is a Null value there, it actually keeps the word null in there as in "NULLnewdata".

Am I doing something wrong?My first suggestion would be to use an UPDATE statement to modify data, not the resultset. After that, make sure you have highlighted the word "NULL" before you start typing... wrote in message news:1305c385-434d-4f39-8ee2-993fecab5184@.discussions.microsoft.com...
> In SQL Management studio, if I right-click on a table and click "Open
> table", it brings up the table, similar to return all rows in 2000. But
> if I go to the last row, and edit data in a cell and the word "NULL" is
> that cell because there is a Null value there, it actually keeps the
> word null in there as in "NULLnewdata". >
> Am I doing something wrong?
>|||

Those are nice work arounds, but it seems to me like this is not the behavior that should be happening in the UI.

|||Well, I think part of the problem is your method. When I highlight a NULL value in Management Studio, it highlights the word. When I start typing, it replaces the word altogether. I can only reproduce your behavior if I highlight the cell, then click again. And I think had I happened upon that behavior naturally, I would have just remembered to not do it again. Sure, it's not the most intuitive thing in the world. But based on the number of people who are going to do it, my gut tells me that it will stay that way. You should still be using DML statements for modifying data, rather than relying on a GUI that is imperfect. Just because I can ride my tricycle to work doesn't make it the best choice. A wrote in message news:c9dd504e-ee88-4d2e-a773-24ae795050a5@.discussions.microsoft.com...
> Those are nice work arounds, but it seems to me like this is not the
> behavior that should be happening in the UI. >
>|||"Correct" method or not, I am just confirming that this is a bug and someone else is having the same issue, and it was not because I have an old build or something.
(They did have 5 years to test it) :)|||To be quite honest, I'm not sure how much usability testing went into these parts of the tools... most of them are holdovers from Enterprise Manager, with a different look and feel. I was going to say prettier but I think the jury is still out on that one. wrote in message news:bc733a59-8d05-452c-ab75-9b9e0d570fa7@.discussions.microsoft.com...
> "Correct" method or not, I am just confirming that this is a bug and
> someone else is having the same issue, and it was not because I have an
> old build or something.
> (They did have 5 years to test it) :)
>|||

I don't think there is a code defect here. I think it's just the way the grid metaphor works. We tried to make sure our grid controls work the same as other Microsoft grids so that whatever learning people have invested in other Microsoft products applies to SQL Server and Visual Studio. The same thing you're describing here happens to me occasionally in other grid-centric products, such as the grid in Microsoft Excel. I've just had to learn to make sure the cell contents are completely highlighted before I start typing to replace them.

In the case of the open table editor, once you change the cell contents, the open table editor replaces the DBNull value in the data set with whatever the cell contains. The open table editor doesn't know what you mean to have in the cell, so if you edit the cell contents to say "NULLnewdata", then that's what it changes the field data to.

|||

I see that this thread is ancient (in internet time!) but I have a highly similar question: I want to go the other way. That is, I want to NULLify a cell in the grid. Deleting the cell contents doesn't work (unless it's a char field) and typing 'NULL' doesn't work either (incorrect data type, or I get the string 'NULL' stored in the field). Is there any way to have a true NULL 'inserted' into a cell, through the grid control? Or can this only be done through DML?

Thanks,

Tom

|||

Tom,

I don't think you can enter NULL values into a cell by typing something. However, there is a trick. You can either find another NULL value in the grid or go to the very last row of the grid where a bunch of NULLs are listed. Then just hit CTRL+C to copy the value and CTRL+V to paste it to the cell you want to add NULL to.

Thanks!

|||Three things.
1.) Ctrl-0 (read control-zero) = NULL
2.) Saying that that's the expected behavior is a cheap Cop-out. Enterprise Manager 2000 did it just fine. There is NO REASON that the "new" "improved" product can't clear out a cell with a null value as soon as it has the focus. I know it's Friday, but c`mon.
3.) One thing I've noticed is that Management Studio was obviously written *by* and *for* Visual Studio .NET programmers. All other developers (VB6, ASP, C, C++, SQL, MS Access...) got screwed. The one thing enterprise manager 2000 was good at was building queries. It did a good job. SQL Mgmt. Studio sucks at that. It does a horrible job. It takes me 20-30% longer to write queries in the new software. Let me run enterprise manager against SQL Server 2005 to build queries and I'll be a happy man.

Open table + NULLs

In SQL Management studio, if I right-click on a table and click "Open table", it brings up the table, similar to return all rows in 2000. But if I go to the last row, and edit data in a cell and the word "NULL" is that cell because there is a Null value there, it actually keeps the word null in there as in "NULLnewdata".

Am I doing something wrong?My first suggestion would be to use an UPDATE statement to modify data, not

the resultset.

After that, make sure you have highlighted the word "NULL" before you start

typing...

wrote in message

news:1305c385-434d-4f39-8ee2-993fecab5184@.discussions.microsoft.com...

> In SQL Management studio, if I right-click on a table and click "Open

> table", it brings up the table, similar to return all rows in 2000. But

> if I go to the last row, and edit data in a cell and the word "NULL" is

> that cell because there is a Null value there, it actually keeps the

> word null in there as in "NULLnewdata".

>

> Am I doing something wrong?

>|||

Those are nice work arounds, but it seems to me like this is not the behavior that should be happening in the UI.

|||Well, I think part of the problem is your method. When I highlight a NULL

value in Management Studio, it highlights the word. When I start typing, it

replaces the word altogether. I can only reproduce your behavior if I

highlight the cell, then click again. And I think had I happened upon that

behavior naturally, I would have just remembered to not do it again.

Sure, it's not the most intuitive thing in the world. But based on the

number of people who are going to do it, my gut tells me that it will stay

that way.

You should still be using DML statements for modifying data, rather than

relying on a GUI that is imperfect. Just because I can ride my tricycle to

work doesn't make it the best choice.

A

wrote in message

news:c9dd504e-ee88-4d2e-a773-24ae795050a5@.discussions.microsoft.com...

> Those are nice work arounds, but it seems to me like this is not the

> behavior that should be happening in the UI.

>

>|||"Correct" method or not, I am just confirming that this is a bug and someone else is having the same issue, and it was not because I have an old build or something.
(They did have 5 years to test it) :)|||To be quite honest, I'm not sure how much usability testing went into these

parts of the tools... most of them are holdovers from Enterprise Manager,

with a different look and feel. I was going to say prettier but I think the

jury is still out on that one.

wrote in message

news:bc733a59-8d05-452c-ab75-9b9e0d570fa7@.discussions.microsoft.com...

> "Correct" method or not, I am just confirming that this is a bug and

> someone else is having the same issue, and it was not because I have an

> old build or something.

> (They did have 5 years to test it) :)

>|||

I don't think there is a code defect here. I think it's just the way the grid metaphor works. We tried to make sure our grid controls work the same as other Microsoft grids so that whatever learning people have invested in other Microsoft products applies to SQL Server and Visual Studio. The same thing you're describing here happens to me occasionally in other grid-centric products, such as the grid in Microsoft Excel. I've just had to learn to make sure the cell contents are completely highlighted before I start typing to replace them.

In the case of the open table editor, once you change the cell contents, the open table editor replaces the DBNull value in the data set with whatever the cell contains. The open table editor doesn't know what you mean to have in the cell, so if you edit the cell contents to say "NULLnewdata", then that's what it changes the field data to.

|||

I see that this thread is ancient (in internet time!) but I have a highly similar question: I want to go the other way. That is, I want to NULLify a cell in the grid. Deleting the cell contents doesn't work (unless it's a char field) and typing 'NULL' doesn't work either (incorrect data type, or I get the string 'NULL' stored in the field). Is there any way to have a true NULL 'inserted' into a cell, through the grid control? Or can this only be done through DML?

Thanks,

Tom

|||

Tom,

I don't think you can enter NULL values into a cell by typing something. However, there is a trick. You can either find another NULL value in the grid or go to the very last row of the grid where a bunch of NULLs are listed. Then just hit CTRL+C to copy the value and CTRL+V to paste it to the cell you want to add NULL to.

Thanks!

|||Three things.

1.) Ctrl-0 (read control-zero) = NULL

2.) Saying that that's the expected behavior is a cheap Cop-out.

Enterprise Manager 2000 did it just fine. There is NO REASON that

the "new" "improved" product can't clear out a cell with a null value

as soon as it has the focus. I know it's Friday, but c`mon.

3.) One thing I've noticed is that Management Studio was obviously

written *by* and *for* Visual Studio .NET programmers. All other

developers (VB6, ASP, C, C++, SQL, MS Access...) got screwed. The

one thing enterprise manager 2000 was good at was building

queries. It did a good job. SQL Mgmt. Studio sucks at

that. It does a horrible job. It takes me 20-30% longer to

write queries in the new software. Let me run enterprise manager

against SQL Server 2005 to build queries and I'll be a happy man.sql

OO Database Design performance question

I am hoping someone has had a similar table design scenario and has a slick solution that i am not thinking of.

Basically I have the following tables (kind of OO based design in this case).

Create Table Entity (EntityID int, EntityTypeID)

Create Table Company (EntityID, CompanyName)

Create Table Contact (EntityID, FirstName, LastName)

Create Table Group (EntityID, GroupName)

Basically an Entity can be a Company, a Contact or a Group based on the EntityTypeID

The trick becomes when I get an EntityID is how to get the name of the Entity in an efficient manner.

You can make a view that does a Union to all three tables like such...

select CompanyName as Name from Company

union all

Select FirstName + LastName as Name from Contact

union all

Select GroupName as Name from Group

Perfomance wise this stinks. I have also tried a left join to all the tables and that performs better, but still not great when you are joining a bunch of records (such as Orders back to get the name of the Entity on the order).

I was thinking of an indexed view but it doesn't let you do a left join or a union. A trigger also comes to mind but I have other triggers on those tables and I really don't want to create any more. I could also add all the different name columns to the entity table but then I end up with a bunch of null values.

Any creative ideas on how to create a function, view, indexed view, etc that will make this efficient?

Thanks!

OO designs often fail to translate well to a high-performance transactional database. Subtypes are especially troublesome in this regard.

The problem here is one of abstraction. The more you abstract something, the more it becomes difficult to query in a practical way. Some package software takes this to the extreme with the concept of the "thing" table. works great on the whiteboard but kills performance as tables get large.

A function is probably going to be your best option. Write a T-SQL function that chooses the table for the lookup based on the type column. You can either pass in the type as a parameter or look it up in the function.

something like:

if @.type = 1 then
select @.retval = companyname from Company

if @.type = 2
select @.retval = firstname+lastname from Contact

if @.type = 3
select @.retval = groupname from group

|||

The key here is that if you are going to use subtypes, (and I am a big fan) you must keep everything that is common between them in the same table, otherwise you are just asking for trouble. The problem here is that you have to be careful with subtypes not to overdo them, and in ths case I think you may have done that. In SQL, if you are going to do a subtype, the supertype really needs be compellingly similar and have a similar purpose.

For example, a vehicle type might be proper for an auto shop, because all vehicles are tracked the same, then each subtype has some differences. Or you might have a patient supertype (records, names bills, etc) and then subtypes for male patients and female ones.

In your case, the subtypes have nothing to do with one another so there is no point and all you are doing is forcing this OO design into a database. You could fix your performance problems by adding a Name column to the Entity table and update it using a trigger on each of the supertypes, but unless there really is some compelling similarities that merit a supertype, you should rethink your design.

|||Take a look at the AdventureWorks sample database. IMHO, it has a very good normalized representaion of Contacts/People/Customers, etc.|||I agree that a function will work however when it comes to searching based on name the function is going to scan which obviously wouldn't be good.|||

i see your point however I argue that it does need an OO design. For example a Customer that orders can be a contact or a company. This really is only an issue when dealing with searching. A contact for name has a Salutation, FirstName, LastName, MiddleName, Suffix while a company might have CompanyName and DBAName. Both are customers however their "name" structure is entirely different. Would you then put all of those fields on the Entity table and leave some null depending on the EntityTypeID? I can see going with a Name1 and Name2 column on the Entity table and those fields have different meanings based on the EntityTypeID. Thoughts?

|||

It is not really an OO design, it is a common database thing. In your case, if you do mean a customer, which can be a person or a company, then yes, this is a valid thing to do. However, I am not so sure I wouldn't make the user choose the subtype before doing a search, since it is pretty unlikely that they wouldn't know if they were looking for one or the other.

I might also do the search in each table seperate from one another, perhaps in different connections from your UI. Either way I would certainly not create generic columns for names, as that would make the design harder to use, and I wouldn't put the names in the customer table with nulls where values don't quite work because then the searching issues are exactly the same (perhaps worse) than with two tables.

Otherwise, like I said before, I would add a name column to the customer table that was managed by a trigger on the child tables. So I would populate the customer.name column with person.firstName + ' ' + person.lastName (or something like it) for persons, and company.name for the companies, if you really need to search both in a single statement.

|||

Stark77 wrote:

I agree that a function will work however when it comes to searching based on name the function is going to scan which obviously wouldn't be good.

I don't think SQL Server has function-based indexes like Oracle, however you might be able to create an indexed view that includes the function as a column.

if you schemabind your function and your view (the view will have to be pretty simple) then this may allow you to create an indexed view, which is similar to a materialized view in Oracle, and that may help your performance.

worth a try if you haven't thought of this option.

Another option would be to use a trigger to denormalize the correct name into the parent table.