• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:

  • You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
  • You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
  • If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

Previously on "SQL Help, working out if a column is all of one value"

Collapse

  • minestrone
    replied
    Originally posted by DimPrawn View Post
    Although what you say is technically correct at the database level, many modern OO applications have to deal with multiple users and multiple objects than need a unique identifier before the object is persisted to the database. In this case, assigning a GUID to each object in the middle layer is the best solution.

    The days of client-server apps is gone and relying on the database to assign a unique id is not feasable or desirable.

    Also, a few bytes is not really an issue. Other fields in the same table may have many hundreds or thousands of bytes, so the PK does not make the storage any bigger in relation.

    A good pros/cons article:

    http://databases.aspfaq.com/database...imary-key.html
    Aye, it's an OO ORM app I am working on, needs a small stored proc though.

    Also, Sir_Edward, the tables were just dummy things, I don't want to start printing off real schema details to the web.

    Leave a comment:


  • DimPrawn
    replied
    I should also add that another one of my gripes is the use of "uniqueidentifier" for the primary key, even when there is no replication involved (this is almost always the case, IME.) There is no need for this and from a performance perspective is slower than using an integer identity. A uniqueidentifier is 16 bytes whereas an int is 4 bytes. Now think of clustered keys and fragmented indexes and I am sure you get the picture. Oh, IIRC joins on uniqueidentifiers mean that the database engine does a character by character match prior to the join.
    Although what you say is technically correct at the database level, many modern OO applications have to deal with multiple users and multiple objects than need a unique identifier before the object is persisted to the database. In this case, assigning a GUID to each object in the middle layer is the best solution.

    The days of client-server apps is gone and relying on the database to assign a unique id is not feasable or desirable.

    Also, a few bytes is not really an issue. Other fields in the same table may have many hundreds or thousands of bytes, so the PK does not make the storage any bigger in relation.

    A good pros/cons article:

    http://databases.aspfaq.com/database...imary-key.html
    Last edited by DimPrawn; 27 March 2009, 10:37.

    Leave a comment:


  • Sir_Edward_Matheson
    replied
    Originally posted by minestrone View Post
    It I have 2 tables which model a one to many relationship.

    table bag ( id , status )

    table item( id , bag_id , status )

    I want a query that will see if all the status for the item table are all '1' and update bag status to '1', they have to be all '1' though.

    Is there some way to do this?

    Thanks
    I realise this is nothing to do with your question and that some of it applies to SQL Server specifically, but here goes...

    The primary keys should be of the format <tablename>id so that the name matches the foreign key. This follows from the argument of those of us that say primary keys must not only be unique across a database but they must also be uniquely named so as not to break 1NF. I firmly advise against ever naming primary keys id or using reserved words as name, status and so on for field names. Having had to deal with over 400 tables with id as the primary key in each table, it becomes very difficult to work out which id somebody is talking about and identifying joins becomes really difficult.

    Furthermore, pretty much every tool I have come across for setting up a schema defaults to matching columns with the same name.

    The only argument for using id as the primary key is that there is less typing required to write employee.id as opposed to employee.employeeid. There are two major issues with this argument:
    1. Less typing is more important than long term maintainability. I am fairly sure I do not need to explain why this is wrong.
    2. The assumption that somebody would ever write employee.id = employee.employeeid. There may well be people who do this but I much prefer a.employeeid = b.employeeid which as you can see renders the argument of less typing to be practically irrelevant.

    I should also add that another one of my gripes is the use of "uniqueidentifier" for the primary key, even when there is no replication involved (this is almost always the case, IME.) There is no need for this and from a performance perspective is slower than using an integer identity. A uniqueidentifier is 16 bytes whereas an int is 4 bytes. Now think of clustered keys and fragmented indexes and I am sure you get the picture. Oh, IIRC joins on uniqueidentifiers mean that the database engine does a character by character match prior to the join.

    Leave a comment:


  • minestrone
    replied
    Cheers lads!!

    Leave a comment:


  • crack_ho
    replied
    Something like this...

    Code:
    UPDATE bag
    SET status = 1
    WHERE id in (
    	SELECT bag_id FROM
    	ITEM
    	GROUP BY bag_id
    	HAVING min(isnull(status,0)) = 1 AND max(isnull(status,0)) = 1
    )
    Last edited by crack_ho; 26 March 2009, 17:27.

    Leave a comment:


  • DimPrawn
    replied
    Originally posted by minestrone View Post
    It I have 2 tables which model a one to many relationship.

    table bag ( id , status )

    table item( id , bag_id , status )

    I want a query that will see if all the status for the item table are all '1' and update bag status to '1', they have to be all '1' though.

    Is there some way to do this?

    Thanks
    UPDATE bag
    SET status = 1
    WHERE EXISTS (SELECT 'All ones' FROM item INNER JOIN bag ON id = bag_id
    GROUP BY bag_id HAVING COUNT(*) = (SELECT COUNT(*) FROM item X WHERE X.bag_id = bag.bag_id AND status = 1))

    Off the top of my head.

    Leave a comment:


  • SQL Help, working out if a column is all of one value

    It I have 2 tables which model a one to many relationship.

    table bag ( id , status )

    table item( id , bag_id , status )

    I want a query that will see if all the status for the item table are all '1' and update bag status to '1', they have to be all '1' though.

    Is there some way to do this?

    Thanks
Working...
X