Clarification on NULL values in the records.

Hi All
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>
>.
>|||Answered in .programming.
Please don't multi-post.
David Portas
SQL Server MVP
--

Clarification on NULL values in the records.

Hi All
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>> Hi All
>> Please write me the reason for
>> SQL:
>> --
>> select * from table1
>> where Required <> 'Y'
>> the result set is empty.
>> Table Schema is:
>> --
>> CREATE TABLE [dbo].[Table1] (
>> [Names] [nvarchar] (50) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Required] [nvarchar] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>> Insert Statement is is:
>> --
>> insert into table1 ([Names],Required)
>> values('Test1',null)
>> insert into table1 ([Names],Required)
>> values('Test2','Y')
>> insert into table1 ([Names],Required)
>> values('Test3',null)
>> insert into table1 ([Names],Required)
>> values('Test4','Y')
>> insert into table1 ([Names],Required)
>> values('Test5',null)
>> Is there any option I need to get the appropriate result
>> set?
>> B'coz my result set expected is
>> Test1 NULL
>> Test3 NULL
>> Test5 NULL
>
>.
>|||Answered in .programming.
Please don't multi-post.
--
David Portas
SQL Server MVP
--sqlsql

choosing a collation in SQL 2005

I have been searching for advice on the 'right' collation to choose in SQL 2005. I am US based and can assume any language required not supported by Latin1_General will be in unicode. I have SQL 2000 servers using SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP850_CI_AS. The ones using CP850 will be converted to whatever collation is chosen in SQL 2005. I do not know the implications of choosing the default SQL collation as opposed to choosing Latin1_General_CI_AS. I have seen warning messages when installing analysis services if the SQL_Latin1_General_CP1_CI_AS is chosen saying that analysis services will use the colaltion Latin1_General_... . I also use non-updating subscribers in transactional replication currently in teh SQL 2000 environment and will continue to use replication in SQL 2005.

What are the implications of choosing the windows vs. SQL collation if the choice is between SQL_Latin1_General_CP1_CI_AS and Latin1_Gernal_CI_AS ? I do not want a collation mixture like I currently have in SQL 2000.

There's a helpful topic in Books Online that addresses this:

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Buck Woody

|||Thank you very much. This is the information I needed to see. I am not sure why searching BOL myself did not turn up this information.|||

If I am doing a fresh installation of SQL 2005 for a new application which will utilize databse engine, report server and analysis services is my best choice Latin_General_BIN2?

Thank you,
Alex Ivanoff

|||My understanding is binary collations do not sort in order you may expect. I'm not sure it's the best unless you know you need it. But, I'm not expert and am interested in an experts opinion.

choosing a collation in SQL 2005

I have been searching for advice on the 'right' collation to choose in SQL 2005. I am US based and can assume any language required not supported by Latin1_General will be in unicode. I have SQL 2000 servers using SQL_Latin1_General_CP1_CI_AS and SQL_Latin1_General_CP850_CI_AS. The ones using CP850 will be converted to whatever collation is chosen in SQL 2005. I do not know the implications of choosing the default SQL collation as opposed to choosing Latin1_General_CI_AS. I have seen warning messages when installing analysis services if the SQL_Latin1_General_CP1_CI_AS is chosen saying that analysis services will use the colaltion Latin1_General_... . I also use non-updating subscribers in transactional replication currently in teh SQL 2000 environment and will continue to use replication in SQL 2005.

What are the implications of choosing the windows vs. SQL collation if the choice is between SQL_Latin1_General_CP1_CI_AS and Latin1_Gernal_CI_AS ? I do not want a collation mixture like I currently have in SQL 2000.

There's a helpful topic in Books Online that addresses this:

http://msdn2.microsoft.com/en-us/library/ms144260.aspx

Buck Woody

|||Thank you very much. This is the information I needed to see. I am not sure why searching BOL myself did not turn up this information.|||

If I am doing a fresh installation of SQL 2005 for a new application which will utilize databse engine, report server and analysis services is my best choice Latin_General_BIN2?

Thank you,
Alex Ivanoff

|||My understanding is binary collations do not sort in order you may expect. I'm not sure it's the best unless you know you need it. But, I'm not expert and am interested in an experts opinion.