Partial searches over a lot of fields

Hi All,
I have the following scenario. I have a table called Invoice. This
has around 30 columns of which i have to do a retrieval based on
filter conditions on 10 columns. These filters need to be partial
searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
'Aaron', now i should be able to search the customer as 'ar' and it
should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
on which this like '%x%' search has to be done and there will
practically be hudreds of thousands of rows. can anybody suggest me to
improve the performance of such a query. Currently what i am thinkin
of is
select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
'%' and etc.
P.S. am using ASP.Net as the front end.ArunPrakash (arunprakashb@.yahoo.com) writes:
> I have the following scenario. I have a table called Invoice. This
> has around 30 columns of which i have to do a retrieval based on
> filter conditions on 10 columns. These filters need to be partial
> searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
> 'Aaron', now i should be able to search the customer as 'ar' and it
> should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
> on which this like '%x%' search has to be done and there will
> practically be hudreds of thousands of rows. can anybody suggest me to
> improve the performance of such a query. Currently what i am thinkin
> of is
> select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
> '%' and etc.

You can use SELECT TOP or SET ROWCOUNT to restrict the number of rows
returned. A good value is probably 2000. If you get 2000 rows, you tell
the user to refine his conditions.

Also keep in mind, that there could be a great difference in performance
when searching for names that start with 'Ar', or have 'ar' anywhere in
the name. If the search column is indexed, that index can be used
for the case "starts with", but not "contains".

There are also a couple of considerations of how to compose the query
to make the search effective. You may be interested in the article
http://www.sommarskog.se/dyn-search.html on my web site.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Parent-Child-Dimension in SQL Srv 2005 Analysis Services

Hi experts,

having a parent-child-table with the columns child_id, child_name, parent_id
in SQL Server 2005 I just cannot create a parent-child dimension in BI Dev
Studio. Can anyone give me some hints? The Dim Build wizard doesn't create
the hierarchies, manually setting "parent" property to parent_id and "key"
to child_id as well as dragging and dropping the stuff into the hierachy
field haven't just led to success. I also tried to right-click both
parent_id and child_id to create a member property. It just never worked
out.

Any help would be greatly appreciated.

Kind regards,

JoergI realized that there is a special NG for this subject:
http://communities.microsoft.com/ne...er2005&slcid=us

Regards,
Joerg

Parent to Child mappings identification in a single query

Hi,

I want to retrive the output in a single SQL query.

I had two columns in a table; I used some values of column1 in column2.
I want to identify all the column1 vlaues in Left Hand side and column2 values in Right Hand side, with a single query.

example:

MasterTable

ParentRow ChildRow
--
A B
C D
E F
G C
H E
M G

Output required:

ParentRow ChildRow
--
A B
C D
E F
G D (C mapped tfrom D)
H F
M D (G mapped from C, and C mapped from D)

To get the above output, I written query using WHILE...[BREAK|CONTINUE] concept until I get @.@.rowcount become zero.

I am looking for better performace and to get the output in a single query, instead of multiple passes.

Can any one suggest good tools and articles on performance tuning techniques?

Thanks
Sreekanth

Waht is your database? SQL Server 2000 or 2005?

|||

If you use SQL Server 2005,

Code Snippet

Create Table #data (

[ParentRow] char ,

[ChildRow] char

);

Insert Into #data Values('A','B');

Insert Into #data Values('C','D');

Insert Into #data Values('E','F');

Insert Into #data Values('G','C');

Insert Into #data Values('H','E');

Insert Into #data Values('M','G');

;WITH TREE

as

(

Select

[ParentRow],

[ChildRow],

[ChildRow] as [Alternate],

Cast('*' + '\' + '*' as varchar(max)) Path

from #Data

Union ALL

Select

data.[ParentRow],

data.[ChildRow],

Tree.[Alternate],

Cast(Path + '\' + '*' as varchar(max)) Path

from #Data as data

Join Tree On Data.[ChildRow] = Tree.[ParentRow]

)

,Tree2 as

(

Select

[ParentRow],

[ChildRow],

[Alternate],

Len(path) as PathLen,

Max(Len(path)) Over(Partition BY [ParentRow], [ChildRow]) MaxLen

from Tree

)

select

[ParentRow],

[Alternate]

from

Tree2

Where

PathLen=MaxLen

Drop table #data

|||

If you use SQL server 2000,

Code Snippet

create table datatable (

[parentrow] char ,

[childrow] char

);

insert into datatable values('A','B');

insert into datatable values('C','D');

insert into datatable values('E','F');

insert into datatable values('G','C');

insert into datatable values('H','E');

insert into datatable values('M','G');

go

create function findalternate(@.childvalue char)

returns char

as

begin

declare @.return as char;

select @.return = childrow from datatable where parentrow=@.childvalue;

if @.return is null

return @.childvalue

else

return dbo.findalternate(@.return)

return null;

end

go

select *, dbo.findalternate(childrow) from datatable

|||

I am using SQL 2005 Server.

Thank you for providing the T-SQL.

Partitioning Question

Hi All,

When we are partitioning a table in SQL Server 2005 , we get the following error.

Any help appreciated

Partition columns for a unique index must be a subset of the index key.

Thanks

Manish

Got the answer. We have to have the partitioning key also as a part of the primary key for the table, if it is not already the primary key

Partitioning on Multiple Columns

Has anyone successfully implement partitioning on multiple columns? My
attempts produce the following error:
Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
updatable because a partitioning column was not found.
For example:
CREATE TABLE [dbo].[Test_200312_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200312_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1016)
GO
ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1016)
GO
CREATE VIEW [dbo].[Test_pvw]
AS
SELECT * FROM [dbo].[Test_200312_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200312_1016_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1016_ptb]
INSERT INTO [dbo].[Test_pvw](
Period,
BusinessUnit,
Amount)
VALUES(
'200312',
1015,
100.00)
DROP VIEW [dbo].[Test_pvw]
GO
DROP TABLE [dbo].[Test_200312_1015_ptb]
GO
DROP TABLE [dbo].[Test_200312_1016_ptb]
GO
DROP TABLE [dbo].[Test_200401_1015_ptb]
GO
DROP TABLE [dbo].[Test_200401_1016_ptb]
GOPlease don't start new posts on issues that are being answered. Please
continue with the thread you already started in .programming.
SK
Robert S. Wallace wrote:

>Has anyone successfully implement partitioning on multiple columns? My
>attempts produce the following error:
>Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is no
t
>updatable because a partitioning column was not found.
>
>For example:
>CREATE TABLE [dbo].[Test_200312_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200312_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
> CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
> CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1016)
>GO
>ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
> CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
> CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1016)
>GO
>CREATE VIEW [dbo].[Test_pvw]
>AS
> SELECT * FROM [dbo].[Test_200312_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200312_1016_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1016_ptb]
>INSERT INTO [dbo].[Test_pvw](
> Period,
> BusinessUnit,
> Amount)
>VALUES(
> '200312',
> 1015,
> 100.00)
>DROP VIEW [dbo].[Test_pvw]
>GO
>DROP TABLE [dbo].[Test_200312_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200312_1016_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1016_ptb]
>GO
>
>

Partitioning on Multiple Columns

Has anyone successfully implement partitioning on multiple columns? My
attempts produce the following error:
Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
updatable because a partitioning column was not found.
For example:
CREATE TABLE [dbo].[Test_200312_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200312_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1015_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Test_200401_1016_ptb] (
[Period] [char] (6) NOT NULL ,
[BusinessUnit] [int] NOT NULL ,
[Amount] [money] NOT NULL
CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
(
[Period],
[BusinessUnit]
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
[BusinessUnit] = 1016)
GO
ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1015)
GO
ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
[BusinessUnit] = 1016)
GO
CREATE VIEW [dbo].[Test_pvw]
AS
SELECT * FROM [dbo].[Test_200312_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200312_1016_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1015_ptb]
UNION ALL
SELECT * FROM [dbo].[Test_200401_1016_ptb]
INSERT INTO [dbo].[Test_pvw](
Period,
BusinessUnit,
Amount)
VALUES(
'200312',
1015,
100.00)
DROP VIEW [dbo].[Test_pvw]
GO
DROP TABLE [dbo].[Test_200312_1015_ptb]
GO
DROP TABLE [dbo].[Test_200312_1016_ptb]
GO
DROP TABLE [dbo].[Test_200401_1015_ptb]
GO
DROP TABLE [dbo].[Test_200401_1016_ptb]
GOPlease don't start new posts on issues that are being answered. Please
continue with the thread you already started in .programming.
SK
Robert S. Wallace wrote:
>Has anyone successfully implement partitioning on multiple columns? My
>attempts produce the following error:
>Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Test_pvw' is not
>updatable because a partitioning column was not found.
>
>For example:
>CREATE TABLE [dbo].[Test_200312_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200312_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200312_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1015_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1015_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>CREATE TABLE [dbo].[Test_200401_1016_ptb] (
> [Period] [char] (6) NOT NULL ,
> [BusinessUnit] [int] NOT NULL ,
> [Amount] [money] NOT NULL
> CONSTRAINT [PK_Test_200401_1016_ptb] PRIMARY KEY CLUSTERED
> (
> [Period],
> [BusinessUnit]
> ) ON [PRIMARY]
>) ON [PRIMARY]
>GO
>ALTER TABLE [dbo].[Test_200312_1015_ptb] ADD
> CONSTRAINT [CK_Test_200312_1015_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200312_1016_ptb] ADD
> CONSTRAINT [CK_Test_200312_1016_ptb] CHECK ([Period] = 200312 and
>[BusinessUnit] = 1016)
>GO
>ALTER TABLE [dbo].[Test_200401_1015_ptb] ADD
> CONSTRAINT [CK_Test_200401_1015_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1015)
>GO
>ALTER TABLE [dbo].[Test_200401_1016_ptb] ADD
> CONSTRAINT [CK_Test_200401_1016_ptb] CHECK ([Period] = 200401 and
>[BusinessUnit] = 1016)
>GO
>CREATE VIEW [dbo].[Test_pvw]
>AS
> SELECT * FROM [dbo].[Test_200312_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200312_1016_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1015_ptb]
> UNION ALL
> SELECT * FROM [dbo].[Test_200401_1016_ptb]
>INSERT INTO [dbo].[Test_pvw](
> Period,
> BusinessUnit,
> Amount)
>VALUES(
> '200312',
> 1015,
> 100.00)
>DROP VIEW [dbo].[Test_pvw]
>GO
>DROP TABLE [dbo].[Test_200312_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200312_1016_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1015_ptb]
>GO
>DROP TABLE [dbo].[Test_200401_1016_ptb]
>GO
>
>

Parse comma separated string into individual rows

All,

Can anyone help me in this task? I have a table named 'Activity' which has two columns: ActivityID and ActivityDetail. Sample rows look like this:

ActivityID ActivityDetail
1 'Football','Basket ball', Tennis'
2 'Basket ball', 'Volley ball'

I would like to have a stored procedure which parses the data into a new table (e.g. named as 'Activity_breadkdown' and with an identity column named 'DetailID') and looks like this:

DetailID ActivityID ActivityDetail
1 1 Football
2 1 Basket ball
3 1 Tennis
4 2 Basket ball
5 2 Volley ball

Thanks in advance
AlYou can modify the code posted in this thread...

http://www.dbforums.com/t974750.html

Parse Array Data Type to Rows

Hi,

We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.

Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.

Any Ideas?

Thanks

Emilio

In a data flow, use a script task to parse and pivot the array values, then send them to a asynchronous output that writes to the destination table.|||

Hi,

Thank you for the reply.

Do you know of any samples that I can have a look at to get me going in the right direction?

Warm Regards

Emilio

|||

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/17/dynamically-pivoting-columns-to-rows.aspx

Hopefully that will provide a good starting point.

|||

Excellent!!!

I can say that this is the answer to handling those db's that have an array datatype!

Parameterizing Allow Nulls Columns

Here's a question I though would be common but can't find an answer to!

My select statement, which pulls from SQL Server tables, has a column which allows nulls. When I try to add a parameter to this column, it no longer returns rows that have null in that column when I test it with the default value of %. I want it to return all the records.

SELECT Jobs.JobID, Jobs.JobName, Engineers.Engineer FROM Jobs LEFT OUTER JOIN Engineers ON Jobs.AccountManager = Engineers.ID WHERE (Engineers.Engineer = @.Engineer)

Parameter is;
ConvertEmptyStringToNull = True
DefaultValue = %
Direction = Input
Name = Engineer
QueryStringField = Engineer
Size = 0
Type = Empty

When I run this I get no records! Isn't % suppose to return anything including nulls?

Isn't % suppose to return anything including nulls?

No. % is only valid for the LIKE operator, and even then it will not return nulls.

SELECT Jobs.JobID, Jobs.JobName, Engineers.Engineer FROM Jobs LEFT OUTER JOIN Engineers ON Jobs.AccountManager = Engineers.ID WHERE (Engineers.Engineer = @.Engineer) OR (@.Engineer='%')

|||

This works and I thank you much but the thickness of my skull is prohibiting me from absorbing why it work.

WHERE (Engineers.Engineer = @.Engineer)
if a parameter is not provided - the default of % is used and this returns every value accept for nulls. How does it treat zero length strings?
if a parameter is provided - it returns the rows that match the parameter

OR (@.Engineer='%')
if a parameter is not provided - ?
if a parameter is provided - ?

I don't understand how this connects to Engineers.Engineer.

|||

WHERE (Engineers.Engineer = @.Engineer)
if a parameter is not provided, the default of % is used and only rows in which engineer contains EXACTLY % will be returned. I assume you have no engineers that contain exactly %, so no rows will be returned.

If a parameter is provided, it returns the rows that match the parameter.

OR (@.Engineer='%')
if a parameter is not provided, the default of % is used and % always is equal to %. So it will match on every row.

if a parameter is provided (Assuming of course the parameter isn't %), then it will never match.

Recap

WHERE (Engineers.Engineer = @.Engineer) OR (@.Engineer='%')

No parameter: WHERE (FALSE) OR (TRUE)

simplified: WHERE TRUE

Parameter: WHERE (Possibly TRUE -- if they match) OR (FALSE)

simplified: WHERE (Possibly TRUE -- if they match)

Make any sense now?

|||

Perfect Sense. Thank you! So I'm guessing the use of a wildcard really doesn't work in ASP.NET or Sql Server? In Dreamweaver I can provide a string parameter of '%' and it will return all rows including nulls. The WHERE clause is Field LIKE '%'.

|||

Yes, the like operator accepts wildcards, but the equals operator does not.

WHERE Field LIKE '%' will find all fields that have 0 or more characters (NULL is not 0 or more characters, it's NULL).

WHERE Field='%' will find all fields that contain one character, and that one character must be the percent symbol.

|||LIKE '%' returns NULL fields in SQL Server and in my Dreamweaver application but not in my asp.net application. I just posted a different thread asking why it doesn't work in asp.net.