SQL Server Performance

y or n or 1 or 0? or t or f?

Discussion in 'SQL Server 2005 General Developer Questions' started by kingofSQL, Mar 3, 2008.

  1. kingofSQL New Member

    I have a function that evaluates if a record exist. I return a char(1) y or n. The function works fine but what is the general rule of thumb when returning true/false values? Should I really be returning a bit? (0 or 1) or an int? (0 or 1)?
    if I use 0 or 1 what is the industry definition of 0 or 1 (true/false succeed/fail, yes/no)?
    0 = f, "no", fail?
    1 = t, yes, succeed?
  2. Adriaan New Member

    Why not use the proper EXISTS syntax, either as a control-of-flow element:

    IF EXISTS (SELECT * FROM table WHERE criteria)
    BEGIN
    ...........................
    END
    ... or as an in-line subquery:

    WHERE EXISTS (SELECT * FROM tableY WHERE criteria)
  3. kingofSQL New Member

    Well I really started to like functions becasue they are reusable. I thought subqueries contribute to performance issues.
  4. Adriaan New Member

    Standard syntax is the ultimate reusable code.[;)]
    In general, I think you should be more concerned that your T-SQL code is to-the-point. using set-based logic rather than function-based logic.
    One of the most common performance issues is with UDFs in WHERE clauses, which can cause SQL to totally ignore indexes.
    Note that the EXISTS syntax is a special variation of a subquery, where the db engine actually stops evaluating after the first match is found. If you're checking against a table with multiple matches per lookup value, then most of the time EXISTS will perform better than other subquery formats.
  5. ranjitjain New Member

    Hi,
    To add to Adriaan's Suggestion, if it comes to storing the Flag value in a table, its better to store 0/1 instead of Y/N.
  6. FrankKalis Moderator

    [quote user="ranjitjain"]
    To add to Adriaan's Suggestion, if it comes to storing the Flag value in a table, its better to store 0/1 instead of Y/N.
    [/quote]
    Why?
  7. ranjitjain New Member

    Hi Frank,
    As you already know, its always good to use smallest datatype to store data and here its bit vs Byte. So less I/O for read and write operations and performance boost while this column is searched in where clause.
    EDIT: Please correct me if I'm wrong here[:)]
  8. FrankKalis Moderator

    <p>[quote user="ranjitjain"]</p><p>As you already know, its always good to use smallest datatype to store data and here its bit vs Byte. So less I/O for read and write operations and performance boost while&nbsp;this column is searched in where clause. </p><p>EDIT: Please correct me if I'm wrong here<img src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif" alt="Smile">[/quote]&nbsp;</p><p>Nono... I just didn't think of the bit data type when seeing 0/1.&nbsp;[:$]<br> </p><p>I thought of some int type. Anyway, as long as there is only one bit column in the table, it really shouldn't matter, since one bit column consumes as much space as a char(1) column. If you however have more than one bit column, they are packed together in same byte, thus occupying less space. <br></p>
  9. kingofSQL New Member

    excellent!
    thanks all for your input [H][Y]

Share This Page