October 30, 2015 at 9:09 am
Bill Talada (10/30/2015)
I have replaced all chars in my databases with varchars because of the weird object-like method handling they have:
-- funky handling of chars
DECLARE @C char(5);
SET @C = 'ab ';
SELECT charindex(' ',@c) AS 'charindex', LEN(@c) AS 'len()', dataLENgth(@c) AS 'datalength()', 'x'+@c+'y' AS 'concat';
Not sure what your point is there - if you change char(5) to varchar(5), you still get exactly the same results.
John
October 30, 2015 at 9:54 am
When I know a null is a possibility I always use "when isnull(x,'') = ''"
October 30, 2015 at 9:57 am
When I know a null is a possibility I always use "where isnull(x,'') = ''"
October 30, 2015 at 10:03 am
Steve Holle (10/30/2015)
When I know a null is a possibility I always use "where isnull(x,'') = ''"
I very rarely use it since it's a non-SARGable predicate and could destroy performance, whereas the longhand version WHERE x IS NULL OR x = '' is SARGable.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 30, 2015 at 10:26 am
John Mitchell-245523 (10/30/2015)
Bill Talada (10/30/2015)
I have replaced all chars in my databases with varchars because of the weird object-like method handling they have:
-- funky handling of chars
DECLARE @C char(5);
SET @C = 'ab ';
SELECT charindex(' ',@c) AS 'charindex', LEN(@c) AS 'len()', dataLENgth(@c) AS 'datalength()', 'x'+@c+'y' AS 'concat';
Not sure what your point is there - if you change char(5) to varchar(5), you still get exactly the same results.
John
Oops, I'll try again:
-- funky handling of chars
DECLARE @C char(5);
SET @C = 'ab';
SELECT @C+@c, charindex(' ',@c) AS 'charindex', LEN(@c) AS 'len()', dataLENgth(@c) AS 'datalength()', 'x'+@c+'y' AS 'concat';
GO
DECLARE @C varchar(5);
SET @C = 'ab';
SELECT @C+@c, charindex(' ',@c) AS 'charindex', LEN(@c) AS 'len()', dataLENgth(@c) AS 'datalength()', 'x'+@c+'y' AS 'concat';
GO
It is just simpler for developers to get used to varchar handling. With chars, one must remember the declared length when doing concatenation.
October 30, 2015 at 10:45 am
Matt Miller (#4) (10/30/2015)
drew.allen (10/29/2015)
Sean Lange (10/29/2015)
g.britton (10/29/2015)
For that matter I wish we had true Boolean variables. But I dream.We do.
create table MyTrueBoolean
(
TrueBool bit not null
)
If you have a bit column and no value is provided would have it be True or False??? That is why the bit datatype allows NULL. With no known value it can't logically be either.
No we don't. Bit expressions cannot be used in place of Boolean expressions. Try the following:
DECLARE @MyPseudoBoolean BIT = 1;
SELECT GETDATE()
WHERE @MyPseudoBoolean
And you will get an error "An expression of non-boolean type specified in a context where a condition is expected, near '@MyPseudoBoolean'." Here the non-boolean type is bit, so a bit is not a boolean in T-SQL.
Drew
Neither is an "expression" - those are data types. The fact that other languages allow for you to use types and expressions interchangeably is frankly a defect or sloppiness on their end. (Note: I will fully admit that I've used it when it's there, but still - that's a notational thing that is fairly sloppy.
Is a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression.
Note that it specifically states a variable is a simple expression. A variable HAS a data type, but it is not itself a data type, just as the entities referenced in the error message are expressions that have data types.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 30, 2015 at 4:52 pm
drew.allen (10/30/2015)
Matt Miller (#4) (10/30/2015)
drew.allen (10/29/2015)
Sean Lange (10/29/2015)
g.britton (10/29/2015)
For that matter I wish we had true Boolean variables. But I dream.We do.
create table MyTrueBoolean
(
TrueBool bit not null
)
If you have a bit column and no value is provided would have it be True or False??? That is why the bit datatype allows NULL. With no known value it can't logically be either.
No we don't. Bit expressions cannot be used in place of Boolean expressions. Try the following:
DECLARE @MyPseudoBoolean BIT = 1;
SELECT GETDATE()
WHERE @MyPseudoBoolean
And you will get an error "An expression of non-boolean type specified in a context where a condition is expected, near '@MyPseudoBoolean'." Here the non-boolean type is bit, so a bit is not a boolean in T-SQL.
Drew
Neither is an "expression" - those are data types. The fact that other languages allow for you to use types and expressions interchangeably is frankly a defect or sloppiness on their end. (Note: I will fully admit that I've used it when it's there, but still - that's a notational thing that is fairly sloppy.
Is a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression.Note that it specifically states a variable is a simple expression. A variable HAS a data type, but it is not itself a data type, just as the entities referenced in the error message are expressions that have data types.
Drew
Fair enough - I can see your point about simple expressions. That said - where is it notated that you could use a single expression in a WHERE clause? Every expression here is a compound one (i.e. SQL doesn't acknowledge a single variable as a valid Boolean construct)
According to this - even if you could have a single variable with a Boolean in it - even that isn't valid syntax.
(from BOL).
<search_condition> ::=
{ [ NOT ] <predicate> | ( <search_condition> ) }
[ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ]
[ ,...n ]
<predicate> ::=
{ expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression
| string_expression [ NOT ] LIKE string_expression
[ ESCAPE 'escape_character' ]
| expression [ NOT ] BETWEEN expression AND expression
| expression IS [ NOT ] NULL
| CONTAINS
( { column | * } , '<contains_search_condition>' )
| FREETEXT ( { column | * } , 'freetext_string' )
| expression [ NOT ] IN ( subquery | expression [ ,...n ] )
| expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }
{ ALL | SOME | ANY} ( subquery )
| EXISTS ( subquery ) }
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 2, 2015 at 7:17 am
Matt Miller (#4) (10/30/2015)
Fair enough - I can see your point about simple expressions. That said - where is it notated that you could use a single expression in a WHERE clause? Every expression here is a compound one (i.e. SQL doesn't acknowledge a single variable as a valid Boolean construct)
Yes, that is exactly my point. Someone asked for a TRUE Boolean data type. Someone else said that we already had one in the form of BIT. My example was to prove that we don't. If T-SQL had a TRUE Boolean data type then that expression would be perfectly valid and the documentation would support that. Since we don't have a TRUE Boolean data type, that expression is invalid, so the documentation does not support it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply