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?
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)
Well I really started to like functions becasue they are reusable. I thought subqueries contribute to performance issues.
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.
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.
[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?
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[]
<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 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] </p><p>Nono... I just didn't think of the bit data type when seeing 0/1. [:$]<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>