Is there really a difference of approach?
I have several things to consider in an estimating database. Production, Shipping, Field Work, Field Hardware, etc...
All of the above have account numbers. Now I was wondering, would there be any benifit to having one table or several tables?
One Table Example
Account (PK) | Category | Description
or
Multiple tables
tbProduction
Account (PK) | Description
tbShipping
Account (PK) | Description
tbProducts
Account (PK) | Description
etc....
I like having the tables split up and/or all in one.
Any thoughts, pros / cons?
Mike BIn the one table version, it could be easier to check for duplicate Accounts. The downside is that a user directly accessing the table needs to know about the category (and consider). Besides that, you may want to consider how access to the table(s) is arranged from a security point of view, eventhough both the single table / multiple tables have their own solutions. Next; in future, would there be any other categories to consider?|||In the one table version, it could be easier to check for duplicate Accounts.
Good point
The downside is that a user directly accessing the table needs to know about the category (and consider).
The reason for my questioning.
Besides that, you may want to consider how access to the table(s) is arranged from a security point of view
Basically they are simply lookup tables for the account numbers, so read only for most users and read write for a small group.
When I say look up, in shipping for example you can have three accounts, Trailer Rentals, Services, and Cost Overrun.
Next; in future, would there be any other categories to consider? [/SIZE]
Good point, but if there are, it would probably need further modification to the database and application so adding another table would not make a difference since the only reason I see doing this would be to change the scope of the "estimate".
Mike B
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Is the a similar function to NOW()
Hi,
I just imported a MS Access table to SQL Server via DTC. The table has a date field in it that I would like to populate automatically. In fact the access table used the Now() funtion to do just that. Evertime an insert statement occured the current date and time would be inserted into the u_createDate field.
The now function is not working in MS SQL. Is there a similar funciton that will automatically update the u_createDate field when a record in inserted?
MikeYou can use the GETDATE() function.|||Originally posted by manowar
You can use the GETDATE() function.
Thank you, Mike
I just imported a MS Access table to SQL Server via DTC. The table has a date field in it that I would like to populate automatically. In fact the access table used the Now() funtion to do just that. Evertime an insert statement occured the current date and time would be inserted into the u_createDate field.
The now function is not working in MS SQL. Is there a similar funciton that will automatically update the u_createDate field when a record in inserted?
MikeYou can use the GETDATE() function.|||Originally posted by manowar
You can use the GETDATE() function.
Thank you, Mike
Friday, February 24, 2012
Is possible to concatenate a column?
Hey all
Just like the title asks...Is it possible...I have a table that stores memos but breaks the memo text field up and assigns a key to associate it all as one big text field in the application. What I am needing to do is to create a custom table for a customization to have the memo txt in one row per customer, per memo number. So, is there any way to concatenate memotext into one row per memo?
Thanks
tiborSure you can! Although we need more info regarding your table construction and keys and the like.
To concatinate the memo columns from different rows is easy, in the INSERT, you will just use the concatination operator (i.e., "...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).
The tricky part comes in building the select so that it pulls the memo parts based on the customer number and memo number association, and in the proper order/sequence (which, hopefully, your existing memoparts table provides a mechanism for). There is probably already a memopartnumber sequence construction there (maybe the key that "associates it all as one big text field in the application"?).
We just need to see the DDL on the associated tables.|||OR
You could use some SkullDugery in your Select Statement that will insert data into your New table (or even eliminate the need for a new table) - tehe
SELECT OuterTable.PKey , InnerTab.ConcatResultField
FROM OuterTable
LEFT JOIN(SELECT TabA.PKey,
(SELECT Memo + Memo
FROM OuterTable TabB
WHERE TabB.PKey = TabA.PKey
ORDER BY [Whatever]
FOR XML PATH('')
) ConcatResultField
FROM OuterTable TabA
GROUP BY PKey
) InnerTab ON OuterTable.PKey = InnerTab.PKey
PS. your choice whether to use the FOR XML Path Trickery for the Order By - may not be supported in future versions
GW|||"...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).when i do this with TEXT columns, i get the error message "Operand data type text is invalid for add operator."|||Why isn't this a presentaion issue?
Someone go find the blind dude|||You know, this really sounds more like a presentation issue.|||*rofl*!
Oh and I agree with the blind dude.|||Sure you can! Although we need more info regarding your table construction and keys and the like.
To concatinate the memo columns from different rows is easy, in the INSERT, you will just use the concatination operator (i.e., "...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).
The tricky part comes in building the select so that it pulls the memo parts based on the customer number and memo number association, and in the proper order/sequence (which, hopefully, your existing memoparts table provides a mechanism for). There is probably already a memopartnumber sequence construction there (maybe the key that "associates it all as one big text field in the application"?).
We just need to see the DDL on the associated tables.
Sorry for the delay...
Yes there is a SeqNo field for each memo and a memo number but the problem is there is no key but there is a customer number to play with instead.
thanks for the reply, I just wanted to know if it was even possible and now the problem of creating that SELECT statement ensues, lol.
Thanks
tibor|||You know, this really sounds more like a presentation issue.
thank you
this mess is too shot|||deleted for duplicate post (more or less...)|||when i do this with TEXT columns, i get the error message "Operand data type text is invalid for add operator."Text? What is "text"?? I pretty much always use char() or varchar() and it works peachy for those types.
Text data types are for use by those losers that need well...ummm..."text" data types ;)
Besides, this is really a presentation issue anyway.|||Text? What is "text"?? oh, i dunno, it's just something i saw in post #1 --
... "the memo text field"|||Ok, so I am confused. What exactly is a "presentation issue"?|||oh, i dunno, it's just something i saw in post #1 --
... "the memo text field"Oh for crissake Rudy, you'll find this whole "guru" thing much easier if you don't allow yourself to become involved in the muddled world of technical accuracy and/or functionality details.
It's much more valuable to the newbie to be given only tangentally-accurate information so that he/she can learn by the application of technically flawed advice. This method allow the inquiring person to learn by experience.
So much to teach, so little time ;)|||LOL :cool:|||A presentation issue is an issue regarding how the data is displayed to the user, rather than having to do with the content of the data itself. SQL should not be used to handle presentation issues for several reasons. First, the SQL language was not designed with the intention of formatting data, but is optimized for retrieving and filtering data. Second, the database server should deliver the data in a generic format allowing different applications to display the same dataset in different ways. Third...
...ummm...third...
...thirdly, never talk about Fight Club.|||Ah, ok.
Well it seemed like a valid question as the application itself gives very very little leeway as to what can be done through its customizer unless you tap into the source code which we can not do. Thanks for the help.|||Ah, ok.
Well it seemed like a valid question as the application itself gives very very little leeway as to what can be done through its customizer unless you tap into the source code which we can not do. Thanks for the help.
oh for the love of...
How many "4GL" pieces of garbage are actually out there...
silver bullet my a$$|||if you have nothing nice to say, keep it to yourself...it is that simple. i come here for help, not criticism.|||what are you talking about?
Did you write the app? Sounds like a 3rd party off the shelf application that you are stuck with or did you write, in which case why don't you open up the code.
And if you did not write it, then you shouldn't touch the code because I'm sure it will invalidate any support aggreement
What was that line in the first Lethal weapon when Riggs first meets Roger?|||if you have nothing nice to say, keep it to yourself...it is that simple. i come here for help, not criticism.I don't think Brett was aiming the criticism at you, but rather railing at the plethora, or veritible cornucopia, as it were, of crap applications out there that were initially sold as do-all, cure-all bits-o-bytes.
FYI, seldom on this or any internet bulletin board does saying something like "if you have nothing nice to say, keep it to yourself..." result in that direction being taken.|||ok, i do apologize but it just seemed directed towards me. the 3rd party app is an erp packack and we are upgrading from one tier to the next and the table structures are totally different. when the client says they need this mod, well you cant just say "no its not possible" nor can you just hop into the source so you have to make do with what ya got. in this case, we have to create a custom table to pull the info from the former erp and how it had the info stored.
again, i do apologize.|||Well why don't you post the ddl of the old and new tables and what you have to map|||Ok this is the function that I have now in order to do the deeds:
CREATE FUNCTION dbo.GetMemo(@.MemoID Varchar(15))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SELECT @.Output = COALESCE(@.Output + '', '') + X.MemoText
FROM dbo.ARP_CustomerMemoHeader X
WHERE X.MemoNumber = @.MemoID
ORDER BY X.SeqNumber
RETURN @.Output
END
GO
And when I run this:
SELECT Distinct dbo.GetMemo('0221AD')
FROM ARP_CustomerMemoHeader
Where MemoNumber = '0221AD'
It gives me the results needed.
Now there is the matter of creating an update statement for the custom table that has everything but the memotext field populated and get this into that table.
thanks for the help and I owe a beer to those I got short with.:beer:|||thanks for the help and I owe a beer to those I got short with.:beer:Isn't it amazing that we are such a diverse and widely sprinkled lot, and yet we all really speak the same language where it is most important to do so ;)|||thanks for the help and I owe a beer to those I got short with.:beer:I found your comments deeply offensive. About 12 oz deep, actually.|||tallboy for me please!|||I found your comments deeply offensive. About 12 oz deep, actually.
I thought he was refering to about 2 inches.....and that's what yo uwere offended about
Lion King: Ah yes, the truth hurts|||Tibor: We need DDL|||tallboy for me please!
do what?
Oh, you are talking about glass size...scared me for a minute. ;)
Just like the title asks...Is it possible...I have a table that stores memos but breaks the memo text field up and assigns a key to associate it all as one big text field in the application. What I am needing to do is to create a custom table for a customization to have the memo txt in one row per customer, per memo number. So, is there any way to concatenate memotext into one row per memo?
Thanks
tiborSure you can! Although we need more info regarding your table construction and keys and the like.
To concatinate the memo columns from different rows is easy, in the INSERT, you will just use the concatination operator (i.e., "...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).
The tricky part comes in building the select so that it pulls the memo parts based on the customer number and memo number association, and in the proper order/sequence (which, hopefully, your existing memoparts table provides a mechanism for). There is probably already a memopartnumber sequence construction there (maybe the key that "associates it all as one big text field in the application"?).
We just need to see the DDL on the associated tables.|||OR
You could use some SkullDugery in your Select Statement that will insert data into your New table (or even eliminate the need for a new table) - tehe
SELECT OuterTable.PKey , InnerTab.ConcatResultField
FROM OuterTable
LEFT JOIN(SELECT TabA.PKey,
(SELECT Memo + Memo
FROM OuterTable TabB
WHERE TabB.PKey = TabA.PKey
ORDER BY [Whatever]
FOR XML PATH('')
) ConcatResultField
FROM OuterTable TabA
GROUP BY PKey
) InnerTab ON OuterTable.PKey = InnerTab.PKey
PS. your choice whether to use the FOR XML Path Trickery for the Order By - may not be supported in future versions
GW|||"...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).when i do this with TEXT columns, i get the error message "Operand data type text is invalid for add operator."|||Why isn't this a presentaion issue?
Someone go find the blind dude|||You know, this really sounds more like a presentation issue.|||*rofl*!
Oh and I agree with the blind dude.|||Sure you can! Although we need more info regarding your table construction and keys and the like.
To concatinate the memo columns from different rows is easy, in the INSERT, you will just use the concatination operator (i.e., "...(MemoPart + MemoPart + MemoPart...) AS FullMemo...).
The tricky part comes in building the select so that it pulls the memo parts based on the customer number and memo number association, and in the proper order/sequence (which, hopefully, your existing memoparts table provides a mechanism for). There is probably already a memopartnumber sequence construction there (maybe the key that "associates it all as one big text field in the application"?).
We just need to see the DDL on the associated tables.
Sorry for the delay...
Yes there is a SeqNo field for each memo and a memo number but the problem is there is no key but there is a customer number to play with instead.
thanks for the reply, I just wanted to know if it was even possible and now the problem of creating that SELECT statement ensues, lol.
Thanks
tibor|||You know, this really sounds more like a presentation issue.
thank you
this mess is too shot|||deleted for duplicate post (more or less...)|||when i do this with TEXT columns, i get the error message "Operand data type text is invalid for add operator."Text? What is "text"?? I pretty much always use char() or varchar() and it works peachy for those types.
Text data types are for use by those losers that need well...ummm..."text" data types ;)
Besides, this is really a presentation issue anyway.|||Text? What is "text"?? oh, i dunno, it's just something i saw in post #1 --
... "the memo text field"|||Ok, so I am confused. What exactly is a "presentation issue"?|||oh, i dunno, it's just something i saw in post #1 --
... "the memo text field"Oh for crissake Rudy, you'll find this whole "guru" thing much easier if you don't allow yourself to become involved in the muddled world of technical accuracy and/or functionality details.
It's much more valuable to the newbie to be given only tangentally-accurate information so that he/she can learn by the application of technically flawed advice. This method allow the inquiring person to learn by experience.
So much to teach, so little time ;)|||LOL :cool:|||A presentation issue is an issue regarding how the data is displayed to the user, rather than having to do with the content of the data itself. SQL should not be used to handle presentation issues for several reasons. First, the SQL language was not designed with the intention of formatting data, but is optimized for retrieving and filtering data. Second, the database server should deliver the data in a generic format allowing different applications to display the same dataset in different ways. Third...
...ummm...third...
...thirdly, never talk about Fight Club.|||Ah, ok.
Well it seemed like a valid question as the application itself gives very very little leeway as to what can be done through its customizer unless you tap into the source code which we can not do. Thanks for the help.|||Ah, ok.
Well it seemed like a valid question as the application itself gives very very little leeway as to what can be done through its customizer unless you tap into the source code which we can not do. Thanks for the help.
oh for the love of...
How many "4GL" pieces of garbage are actually out there...
silver bullet my a$$|||if you have nothing nice to say, keep it to yourself...it is that simple. i come here for help, not criticism.|||what are you talking about?
Did you write the app? Sounds like a 3rd party off the shelf application that you are stuck with or did you write, in which case why don't you open up the code.
And if you did not write it, then you shouldn't touch the code because I'm sure it will invalidate any support aggreement
What was that line in the first Lethal weapon when Riggs first meets Roger?|||if you have nothing nice to say, keep it to yourself...it is that simple. i come here for help, not criticism.I don't think Brett was aiming the criticism at you, but rather railing at the plethora, or veritible cornucopia, as it were, of crap applications out there that were initially sold as do-all, cure-all bits-o-bytes.
FYI, seldom on this or any internet bulletin board does saying something like "if you have nothing nice to say, keep it to yourself..." result in that direction being taken.|||ok, i do apologize but it just seemed directed towards me. the 3rd party app is an erp packack and we are upgrading from one tier to the next and the table structures are totally different. when the client says they need this mod, well you cant just say "no its not possible" nor can you just hop into the source so you have to make do with what ya got. in this case, we have to create a custom table to pull the info from the former erp and how it had the info stored.
again, i do apologize.|||Well why don't you post the ddl of the old and new tables and what you have to map|||Ok this is the function that I have now in order to do the deeds:
CREATE FUNCTION dbo.GetMemo(@.MemoID Varchar(15))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SELECT @.Output = COALESCE(@.Output + '', '') + X.MemoText
FROM dbo.ARP_CustomerMemoHeader X
WHERE X.MemoNumber = @.MemoID
ORDER BY X.SeqNumber
RETURN @.Output
END
GO
And when I run this:
SELECT Distinct dbo.GetMemo('0221AD')
FROM ARP_CustomerMemoHeader
Where MemoNumber = '0221AD'
It gives me the results needed.
Now there is the matter of creating an update statement for the custom table that has everything but the memotext field populated and get this into that table.
thanks for the help and I owe a beer to those I got short with.:beer:|||thanks for the help and I owe a beer to those I got short with.:beer:Isn't it amazing that we are such a diverse and widely sprinkled lot, and yet we all really speak the same language where it is most important to do so ;)|||thanks for the help and I owe a beer to those I got short with.:beer:I found your comments deeply offensive. About 12 oz deep, actually.|||tallboy for me please!|||I found your comments deeply offensive. About 12 oz deep, actually.
I thought he was refering to about 2 inches.....and that's what yo uwere offended about
Lion King: Ah yes, the truth hurts|||Tibor: We need DDL|||tallboy for me please!
do what?
Oh, you are talking about glass size...scared me for a minute. ;)
IS Package doesnt ship with password
Hi,
I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank.
I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.
I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank.
I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.
How do I force IS to save password on any ProtectionLevel ?
Thanks,
Fahad
Fahad349 wrote:
Hi,
I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank.
I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.How do I force IS to save password on any ProtectionLevel ?
Thanks,
Fahad
You can't. You'll have to set it through dtexec. SSIS doesn't save passwords. There are plenty of threads in this forum on that topic.|||First, /SET option isnt working
I am trying it this way
U sa /P gama /SET "\Package.Connections[TRIMURTY.TMDB.SA].Properties[ConnectionString]";"Data Source=TRIMURTY;User ID=sa;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;Password=gama"
Second, I have to run this IS Package from another IS Package as well by using Execute Package Task. How would I supply these things on there ?
Please help me
Thanks
Fahad
Phil Brammer wrote:
Fahad349 wrote: Hi,
I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank.
I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.How do I force IS to save password on any ProtectionLevel ?
Thanks,
Fahad
You can't. You'll have to set it through dtexec. SSIS doesn't save passwords. There are plenty of threads in this forum on that topic.
I have read about ServerStorage, It has nothing to do with storing connections' passwords but storing the Package.
No, not true. it means it relies on SQL Server to match up the assigned user to the SQL server user account.
ServerStorage: "Protects the whole package using SQL Server database roles."|||Here I am not interested in package protection or package security. I am interested in saving password with package. or to avoid this error "login failed for user 'sa'"
ProtectionLevel ServerStorage doesnt let me save the DTS Package in designer.|||
Here I am not interested in package protection or package security. I am interested in saving password with package. or to avoid this error "login failed for user 'sa'" ProtectionLevel ServerStorage doesnt let me save the DTS Package in designer.
Right, but again, you cannot save the password with the package. Can't do it. When you save to the server, you can choose SQL Server role security. Or, try EncryptSensitiveWithPassword. Using that though, you'll have to issue the password everytime the package executes.|||
Right, but again, you cannot save the password with the package. Can't do it. When you save to the server, you can choose SQL Server role security. Or, try EncryptSensitiveWithPassword. Using that though, you'll have to issue the
Ok I will issue the password, but that password will be to execute dts and its separate from passwords in connection manager.
And my understanding with this error message is; the Executable is unable to open child package for executing, not connection manager inside this child has error.|||
Phil Brammer wrote: Right, but again, you cannot save the password with the package. Can't do it. When you save to the server, you can choose SQL Server role security. Or, try EncryptSensitiveWithPassword. Using that though, you'll have to issue the
Ok I will issue the password, but that password will be to execute dts and its separate from passwords in connection manager.
And my understanding with this error message is; the Executable is unable to open child package for executing, not connection manager inside this child has error.
Tough to say... I would think it's the connection managers that's throwing the error.|||
Search this forum for configuration files then use these to "freeze" the password.
Philippe
IS Or type check on bit field
I have a table that has a bit field called PHI, the user will specift true
(1) or false (0) in the application, but I want to if it is true return all
1 and 0 marked fields and if false only the 0 bit marked fields. How would I
write a query to include both if true and only false ones if false? thanks!
my wear clause right now looks like this (PHI = @.PHI), which of course only
includes one or the other.PHI <= @.PHI
Jacco Schalkwijk
SQL Server MVP
"Brian Henry" <nospam@.nospam.com> wrote in message
news:%234zfS2qkFHA.3316@.TK2MSFTNGP14.phx.gbl...
>I have a table that has a bit field called PHI, the user will specift true
>(1) or false (0) in the application, but I want to if it is true return all
>1 and 0 marked fields and if false only the 0 bit marked fields. How would
>I write a query to include both if true and only false ones if false?
>thanks!
> my wear clause right now looks like this (PHI = @.PHI), which of course
> only includes one or the other.
>|||oh that was so simple... was thinking of booleans where you cant really say
that in some languages...
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23A2LJ7qkFHA.4000@.TK2MSFTNGP12.phx.gbl...
> PHI <= @.PHI
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:%234zfS2qkFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||The bit datatype is not a Boolean datatype. It is just an integer class data
type with values
restricted to 0, 1 and NULL. The only confusing (and IMO bad) thing about th
is bit datatype is
below:
DECLARE @.b bit
SET @.b = 27
SELECT @.b --returns 1
All non-0 values are converted to 1. IMO, the SET should return a datatype c
onversion error instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Henry" <nospam@.nospam.com> wrote in message news:ejy2HErkFHA.3256@.TK2MSFTNGP12.phx.g
bl...
> oh that was so simple... was thinking of booleans where you cant really sa
y that in some
> languages...
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wro
te in message
> news:%23A2LJ7qkFHA.4000@.TK2MSFTNGP12.phx.gbl...
>
(1) or false (0) in the application, but I want to if it is true return all
1 and 0 marked fields and if false only the 0 bit marked fields. How would I
write a query to include both if true and only false ones if false? thanks!
my wear clause right now looks like this (PHI = @.PHI), which of course only
includes one or the other.PHI <= @.PHI
Jacco Schalkwijk
SQL Server MVP
"Brian Henry" <nospam@.nospam.com> wrote in message
news:%234zfS2qkFHA.3316@.TK2MSFTNGP14.phx.gbl...
>I have a table that has a bit field called PHI, the user will specift true
>(1) or false (0) in the application, but I want to if it is true return all
>1 and 0 marked fields and if false only the 0 bit marked fields. How would
>I write a query to include both if true and only false ones if false?
>thanks!
> my wear clause right now looks like this (PHI = @.PHI), which of course
> only includes one or the other.
>|||oh that was so simple... was thinking of booleans where you cant really say
that in some languages...
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23A2LJ7qkFHA.4000@.TK2MSFTNGP12.phx.gbl...
> PHI <= @.PHI
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:%234zfS2qkFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||The bit datatype is not a Boolean datatype. It is just an integer class data
type with values
restricted to 0, 1 and NULL. The only confusing (and IMO bad) thing about th
is bit datatype is
below:
DECLARE @.b bit
SET @.b = 27
SELECT @.b --returns 1
All non-0 values are converted to 1. IMO, the SET should return a datatype c
onversion error instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Henry" <nospam@.nospam.com> wrote in message news:ejy2HErkFHA.3256@.TK2MSFTNGP12.phx.g
bl...
> oh that was so simple... was thinking of booleans where you cant really sa
y that in some
> languages...
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wro
te in message
> news:%23A2LJ7qkFHA.4000@.TK2MSFTNGP12.phx.gbl...
>
IS NULL query problem
Hi Folks - a little problem thats baffling me.....
Im using SQL 2000 db
Im querying a field using 'IS NULL' as the criteria, if I add data to the field and then delete and then try to query using 'IS NULL' as the criteria then the record will not be returned in my query even though the fields empty.....
Can anyone help please ?
Thanks in advance
DOnaldIs the field really empty (NULL)? Or is there an empty string now? They are different in SQL Server.|||Hi - in the database it shows as an empty string (nothing in it!)
So I need the criteria to query IS NULL and an empty field if that makes sense.....
Any idea's... ?
Thanks for the quick reply !
Donald|||SELECT *
FROM yourTable
WHERE yourField IS NULL OR yourField = ''
should do the trick.|||Many thanks it worked !|||The worked fine in SQL, but not using VB / ASP...
Here's the string of code that im having problems with;
<%If BecamePartAss <> "" or not isnull(BecamePartAss) then%>
The above works fine if the field 'IS NULL', but when an empty string exists then your code does not - as below;
SELECT *
FROM yourTable
WHERE yourField IS NULL OR yourField = ''
Thsi may be out of your realm here, but would like you to take a look if you can ?
Regards
Donald|||I'm sorry; I'm not that good at VBScript.
Hopefully someone else can help you with that.|||how are you connecting exactly?
Try WHERE ISNULL(yourField, "") = ""|||When you want to "empty" a column, can't you just assign it a NULL value instead of using an empty string? That sure sounds like the best long term answer to me.
-PatP|||Hi - that sounds the best course of action, would you have any information on how to do this ?
Thanks
Donald|||Please post the code you're using to assign an empty string, and I'll gleefully modify it to use a NULL instead.
-PatP|||Hi Pat - below is the line of code that has the query in thats not working, I have also attached the page.
The code is at line 225 on teaminfo.asp
<%If BecamePartAss <> " " or not isnull(BecamePartAss) then%>
Its an SQL 2000 db, and the field is varchar 50
This problem has come about I have entered text in the field and then deleted, then when I query looking for nulls it does not see an 'empty string'...
Many thanks for taking time to help.
Donald|||<%If BecamePartAss <> " " or not isnull(BecamePartAss) then%>
This problem has come about I have entered text in the field and then deleted, then when I query looking for nulls it does not see an 'empty string'...
Your code doesn't check for empty string. It checks for a string containing a space.|||Your code doesn't check for empty string. It checks for a string containing a space.
I tried it like tis too;
<%If BecamePartAss <> "" or not isnull(BecamePartAss) then%>
Can you tell me where im going wrong... ?
Donald|||No, I can't, I'm sorry.
I see you use the same code for two other fields. Do they work with empty strings?|||The code snippets that you show test the string. What I need to see and help you fix is where you assign the empty string to the column.
-PatP|||Hi Pat, what info do I need to give you ?
A little lost with this now... ?
DOnald|||The code that you posted is checking the colum to see if it is "empty" (or in your case NULL). What we need to fix is a different piece of code that is making the column empty, in other words where the column value is assigned.
-PatP|||if u enter text, then delete it, it is not null anymore, its an empty string ''. to make it null :
- If directly edited the table : press ctrl+0 in the field
- If use script : update Staff set BecamePartAss=(case when rtrim(newBecamePartAss)='' then null else newBecamePartAss end)
to get / show null or empty string value :
- SQL : select * from Staff where BecamePartAss is null or rtrim(BecamePartAss)=''
- ASP : if isnull(BecamePartAss) or BecamePartAss=""
to get / show the contrary (have value, not null, not empty string) :
- SQL :
select * from Staff where not(BecamePartAss is null or BecamePartAss='')
or
select * from Staff where BecamePartAss is not null and BecamePartAss<>''
- ASP :
if not(isnull(BecamePartAss) or BecamePartAss='')
or
if not isnull(BecamePartAss) and BecamePartAss<>""
hope its the right answer as what u need|||Many thanks JL - that has sorted my problem !!!!!!! :D|||<%If BecamePartAss <> ' ' or not isnull(BecamePartAss) then%>
This is not the same as the negation of BecamePartAss = ' ' or isnull(BecamePartAss)
Try using BecamePartAss <> ' ' AND not isnull(BecamePartAss)|||Your code doesn't check for empty string. It checks for a string containing a space.
The two are actually equivalent provided the column has data type CHAR(n).
Only for VARCHAR, an empty string and a single-space string compare different.
As this might be platform specific, you can play safe and use the conditionRTRIM(BecamePartAss) = ''(but beware that scalar functions in conditions may degrade performance drastically ...)|||The two are actually equivalent provided the column has data type CHAR(n).
The code I was referring to was checking a value in a variable, not a database column.|||And what is the data type of the variable?
Also there, CHAR variables will compare '' and ' ' equal, while VARCHAR will not.|||I understand it's javascript, and afaik, JS's string-type is the same as varchar. Also from what I can see in the code that was attached earlier, there's no explicit variable declaration in JS.
I'm no expert on JS though.
Im using SQL 2000 db
Im querying a field using 'IS NULL' as the criteria, if I add data to the field and then delete and then try to query using 'IS NULL' as the criteria then the record will not be returned in my query even though the fields empty.....
Can anyone help please ?
Thanks in advance
DOnaldIs the field really empty (NULL)? Or is there an empty string now? They are different in SQL Server.|||Hi - in the database it shows as an empty string (nothing in it!)
So I need the criteria to query IS NULL and an empty field if that makes sense.....
Any idea's... ?
Thanks for the quick reply !
Donald|||SELECT *
FROM yourTable
WHERE yourField IS NULL OR yourField = ''
should do the trick.|||Many thanks it worked !|||The worked fine in SQL, but not using VB / ASP...
Here's the string of code that im having problems with;
<%If BecamePartAss <> "" or not isnull(BecamePartAss) then%>
The above works fine if the field 'IS NULL', but when an empty string exists then your code does not - as below;
SELECT *
FROM yourTable
WHERE yourField IS NULL OR yourField = ''
Thsi may be out of your realm here, but would like you to take a look if you can ?
Regards
Donald|||I'm sorry; I'm not that good at VBScript.
Hopefully someone else can help you with that.|||how are you connecting exactly?
Try WHERE ISNULL(yourField, "") = ""|||When you want to "empty" a column, can't you just assign it a NULL value instead of using an empty string? That sure sounds like the best long term answer to me.
-PatP|||Hi - that sounds the best course of action, would you have any information on how to do this ?
Thanks
Donald|||Please post the code you're using to assign an empty string, and I'll gleefully modify it to use a NULL instead.
-PatP|||Hi Pat - below is the line of code that has the query in thats not working, I have also attached the page.
The code is at line 225 on teaminfo.asp
<%If BecamePartAss <> " " or not isnull(BecamePartAss) then%>
Its an SQL 2000 db, and the field is varchar 50
This problem has come about I have entered text in the field and then deleted, then when I query looking for nulls it does not see an 'empty string'...
Many thanks for taking time to help.
Donald|||<%If BecamePartAss <> " " or not isnull(BecamePartAss) then%>
This problem has come about I have entered text in the field and then deleted, then when I query looking for nulls it does not see an 'empty string'...
Your code doesn't check for empty string. It checks for a string containing a space.|||Your code doesn't check for empty string. It checks for a string containing a space.
I tried it like tis too;
<%If BecamePartAss <> "" or not isnull(BecamePartAss) then%>
Can you tell me where im going wrong... ?
Donald|||No, I can't, I'm sorry.
I see you use the same code for two other fields. Do they work with empty strings?|||The code snippets that you show test the string. What I need to see and help you fix is where you assign the empty string to the column.
-PatP|||Hi Pat, what info do I need to give you ?
A little lost with this now... ?
DOnald|||The code that you posted is checking the colum to see if it is "empty" (or in your case NULL). What we need to fix is a different piece of code that is making the column empty, in other words where the column value is assigned.
-PatP|||if u enter text, then delete it, it is not null anymore, its an empty string ''. to make it null :
- If directly edited the table : press ctrl+0 in the field
- If use script : update Staff set BecamePartAss=(case when rtrim(newBecamePartAss)='' then null else newBecamePartAss end)
to get / show null or empty string value :
- SQL : select * from Staff where BecamePartAss is null or rtrim(BecamePartAss)=''
- ASP : if isnull(BecamePartAss) or BecamePartAss=""
to get / show the contrary (have value, not null, not empty string) :
- SQL :
select * from Staff where not(BecamePartAss is null or BecamePartAss='')
or
select * from Staff where BecamePartAss is not null and BecamePartAss<>''
- ASP :
if not(isnull(BecamePartAss) or BecamePartAss='')
or
if not isnull(BecamePartAss) and BecamePartAss<>""
hope its the right answer as what u need|||Many thanks JL - that has sorted my problem !!!!!!! :D|||<%If BecamePartAss <> ' ' or not isnull(BecamePartAss) then%>
This is not the same as the negation of BecamePartAss = ' ' or isnull(BecamePartAss)
Try using BecamePartAss <> ' ' AND not isnull(BecamePartAss)|||Your code doesn't check for empty string. It checks for a string containing a space.
The two are actually equivalent provided the column has data type CHAR(n).
Only for VARCHAR, an empty string and a single-space string compare different.
As this might be platform specific, you can play safe and use the conditionRTRIM(BecamePartAss) = ''(but beware that scalar functions in conditions may degrade performance drastically ...)|||The two are actually equivalent provided the column has data type CHAR(n).
The code I was referring to was checking a value in a variable, not a database column.|||And what is the data type of the variable?
Also there, CHAR variables will compare '' and ' ' equal, while VARCHAR will not.|||I understand it's javascript, and afaik, JS's string-type is the same as varchar. Also from what I can see in the code that was attached earlier, there's no explicit variable declaration in JS.
I'm no expert on JS though.
IS NULL on field is not using index placed on that field
Hi All,
I have the table structure as follows
->Deliveries (table)
|__OrderID (Guid)
|__DeliverDate (DateTime) (ALLOW NULLS)
Index is on DeliverDate (ASC)
60000 records
look at the query plan for the following and is using the proper index:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
If I now change the query to:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
NULL
The query plan does not choose the DeliverDate index anymore?
What is the reason for this?
Regards,
Adrian.Adrian Bezzina wrote:
> Hi All,
> I have the table structure as follows
> ->Deliveries (table)
> |__OrderID (Guid)
> |__DeliverDate (DateTime) (ALLOW NULLS)
> Index is on DeliverDate (ASC)
> 60000 records
> look at the query plan for the following and is using the proper
> index:
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
> If I now change the query to:
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR
> DeliverDate IS NULL
> The query plan does not choose the DeliverDate index anymore?
> What is the reason for this?
> Regards,
> Adrian.
OR statements can sometimes be an index killer. It could also be because
you have a lot of NULL values in the index.
The first thing you should do is never use SELECT *. For one thing, it
almost always returns more column information than required. It also
does not give SQL Server the opportunity to use a covering index (unless
all columns are covered).
OR statements are, many times, not optimizeable. Even if they are, SQL
Server will only use a non-clustered index if the number of rows
returned is low enough that using the index doesn't cause more overhead
than scanning the table. Remember, unless your index is covering, every
index s
requires a bookmark lookup operation (especially if you use
SELECT *).
You could try using:
Select col1, col2
From Deliveries
Where DeliveryDate = '2003-12-14'
UNION ALL
Select col1, col2
From Deliveries
Where DeliveryDate IS NULL
or you could try changing your index to clustered.
David Gugick
Imceda Software
www.imceda.com|||What does it do? A table/clustered index scan? We don't really have
enough info to answer - can you post the execution plans & the DDL
(table schema and schema for all indexes on the table)? To get the
execution plans just turn on showplan_text before the select statements
and off after the select statements. That is,
set showplan_text on
go
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS NU
LL
go
set showplan_text off
go
When I tried it on a tmp table (I had only a nonclustered index on DeliverDa
te) it chose:
a nonclustered index s for:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
a table scan for:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS NU
LL
a nonclustered index s for:
SELECT * FROM Deliveries WHERE DeliverDate IS NULL
I guess the optimiser decided that it was more efficient to scan the table o
nce than to do 2 s passes (+ associated lookups) in the DeliverDate noncl
ustered index for the query with the 2 where criteria. When I added a clust
ered index on OrderID, the
table scan operation turned into a scan on the DeliverDate nonclustered inde
x because the index suddenly became a covering index (since all nonclustered
indexes also contain the clustering key, thereby implicitly making the Deli
verDate index really (Deliv
erDate, OrderID)).
My quick tests only had 10 rows of data. It would make a difference if I wa
s running the SELECT queries against a bigger table.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Adrian Bezzina wrote:
>Hi All,
>I have the table structure as follows
>->Deliveries (table)
> |__OrderID (Guid)
> |__DeliverDate (DateTime) (ALLOW NULLS)
>Index is on DeliverDate (ASC)
>60000 records
>look at the query plan for the following and is using the proper index:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>If I now change the query to:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>The query plan does not choose the DeliverDate index anymore?
>What is the reason for this?
>Regards,
>Adrian.
>
>|||Mike,
Thanks for the prompt reply.
after executing (i know the tables and fields have changed) the query on the
following table:
Consign (table)
|
|_ [UNIQUE] GUID Field - PrimaryKey.
|
|_ ORIGINAL_DATE DateTime Field - Clustered Index on this field alone.
|
|_ DELIVER_DATE DateTime Field - non clustered index on this field.
set showplan_text on
go
SELECT * FROM Consign WHERE Deliver_date = '2004-2-3'
SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
NULL
go
set showplan_text off
go
the plan for the first query is doing a 'Index Scan' on the non-clustered
index on the deliver date field
the plan for the second query is doing a 'Clustered Index Scan' on a
clustered index - original_date and i suppose this is the equivalent to a
table scan as I have moved the clustered index off the primary key to a more
ordered field (in this case the original_date field).
I suppose what I am really asking is:
Are null values placed in an Index ? and if so does that effect the qeury if
IS NULL (or IS NOT NULL) affect the selection of which index to use (sorry
about changing the table structure, but i am showing you the indexes in
question).
Regards,
Adrian.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:e$tD6MWGFHA.1932@.TK2MSFTNGP14.phx.gbl...
What does it do? A table/clustered index scan? We don't really have enough
info to answer - can you post the execution plans & the DDL (table schema
and schema for all indexes on the table)? To get the execution plans just
turn on showplan_text before the select statements and off after the select
statements. That is,
set showplan_text on
go
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
NULL
go
set showplan_text off
go
When I tried it on a tmp table (I had only a nonclustered index on
DeliverDate) it chose:
a nonclustered index s for:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
a table scan for:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
NULL
a nonclustered index s for:
SELECT * FROM Deliveries WHERE DeliverDate IS NULL
I guess the optimiser decided that it was more efficient to scan the table
once than to do 2 s passes (+ associated lookups) in the DeliverDate
nonclustered index for the query with the 2 where criteria. When I added a
clustered index on OrderID, the table scan operation turned into a scan on
the DeliverDate nonclustered index because the index suddenly became a
covering index (since all nonclustered indexes also contain the clustering
key, thereby implicitly making the DeliverDate index really (DeliverDate,
OrderID)).
My quick tests only had 10 rows of data. It would make a difference if I
was running the SELECT queries against a bigger table.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Adrian Bezzina wrote:
Hi All,
I have the table structure as follows
->Deliveries (table)
|__OrderID (Guid)
|__DeliverDate (DateTime) (ALLOW NULLS)
Index is on DeliverDate (ASC)
60000 records
look at the query plan for the following and is using the proper index:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
If I now change the query to:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
NULL
The query plan does not choose the DeliverDate index anymore?
What is the reason for this?
Regards,
Adrian.|||Adrian,
The optimizer estimates the number of rows a query will return
as part of the optimization process. The estimated number of
rows for which (Deliver_date = '2004-2-3') is going to be fewer
than the number of rows for which (Deliver_date = '2004-2-3')
OR (Deliver_date IS NULL). While the nonclustered index
on Deliver_date can identify these rows in either case (yes, the
NULLs are in the index), the problem is that it can only identify
the PrimaryKey values of these rows. The query is SELECT *
so for every one of these rows, it is still necessary to look up
all the other column values.
Once there are a lot of rows in the result (according to the
optimizer estimate), there is no point gathering all the primary
key values and looking each one up in the main table. If you
will end up looking at most of the data pages of the main table
anyway, why not just scan the table and forget about using the index.
In your case, the Consign table has very narrow rows, with
about 24 bytes per row. As a result, about 300 rows fit on
every data page of the table. Even if only 0.5% of the rows
are expected to satisfy the query condition, that's 1 in 200 rows,
or at least one row per data page. The index doesn't help
in this case.
It's possible the index would help, if all those 0.5% of the rows
are all together on a few data pages, but the optimizer doesn't
have this kind of information available when it decides on the
best-estimate plan.
Steve Kass
Drew University
Adrian Bezzina wrote:
>Mike,
>Thanks for the prompt reply.
>after executing (i know the tables and fields have changed) the query on th
e
>following table:
>Consign (table)
> |
> |_ [UNIQUE] GUID Field - PrimaryKey.
> |
> |_ ORIGINAL_DATE DateTime Field - Clustered Index on this field alone.
> |
> |_ DELIVER_DATE DateTime Field - non clustered index on this field.
>set showplan_text on
>go
>SELECT * FROM Consign WHERE Deliver_date = '2004-2-3'
>SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
>NULL
>go
>set showplan_text off
>go
>the plan for the first query is doing a 'Index Scan' on the non-clustered
>index on the deliver date field
>the plan for the second query is doing a 'Clustered Index Scan' on a
>clustered index - original_date and i suppose this is the equivalent to a
>table scan as I have moved the clustered index off the primary key to a mor
e
>ordered field (in this case the original_date field).
>I suppose what I am really asking is:
>Are null values placed in an Index ? and if so does that effect the qeury i
f
>IS NULL (or IS NOT NULL) affect the selection of which index to use (sorry
>about changing the table structure, but i am showing you the indexes in
>question).
>Regards,
>Adrian.
>--
>"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
>news:e$tD6MWGFHA.1932@.TK2MSFTNGP14.phx.gbl...
>What does it do? A table/clustered index scan? We don't really have enoug
h
>info to answer - can you post the execution plans & the DDL (table schema
>and schema for all indexes on the table)? To get the execution plans just
>turn on showplan_text before the select statements and off after the select
>statements. That is,
>set showplan_text on
>go
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>go
>set showplan_text off
>go
>When I tried it on a tmp table (I had only a nonclustered index on
>DeliverDate) it chose:
>a nonclustered index s for:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>a table scan for:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>a nonclustered index s for:
>SELECT * FROM Deliveries WHERE DeliverDate IS NULL
>I guess the optimiser decided that it was more efficient to scan the table
>once than to do 2 s passes (+ associated lookups) in the DeliverDate
>nonclustered index for the query with the 2 where criteria. When I added a
>clustered index on OrderID, the table scan operation turned into a scan on
>the DeliverDate nonclustered index because the index suddenly became a
>covering index (since all nonclustered indexes also contain the clustering
>key, thereby implicitly making the DeliverDate index really (DeliverDate,
>OrderID)).
>My quick tests only had 10 rows of data. It would make a difference if I
>was running the SELECT queries against a bigger table.
>--
>mike hodgson | database administrator | mallesons stephen jaques
>T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
>E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
>
>Adrian Bezzina wrote:
>Hi All,
>I have the table structure as follows
>->Deliveries (table)
> |__OrderID (Guid)
> |__DeliverDate (DateTime) (ALLOW NULLS)
>Index is on DeliverDate (ASC)
>60000 records
>look at the query plan for the following and is using the proper index:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>If I now change the query to:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>The query plan does not choose the DeliverDate index anymore?
>What is the reason for this?
>Regards,
>Adrian.
>
>
>
>|||Guys,
Thanks for the help (I can pass a paremeter as follows):
Instead of:
SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
NULL
I can do this (work nicely):
SELECT * FROM Consign WHERE ISNULL(Deliver_date, '2004-2-3') = '2004-2-3'
This always uses the index on DELIVER_DATE
:)
BTW its actually called as follows:
DECLARE @.deliverDate AS DATETIME
SET @.deliverDate = '2004-2-3'
SELECT * FROM Consign WHERE ISNULL(Deliver_date, @.deliverDate) =
@.deliverDate
Works like a treat.
Thanks
Adrian.
"Adrian Bezzina" <Adrian@.tristalee.com.au> wrote in message
news:OnSmvgWGFHA.1740@.TK2MSFTNGP09.phx.gbl...
> Mike,
> Thanks for the prompt reply.
> after executing (i know the tables and fields have changed) the query on
> the following table:
> Consign (table)
> |
> |_ [UNIQUE] GUID Field - PrimaryKey.
> |
> |_ ORIGINAL_DATE DateTime Field - Clustered Index on this field alone.
> |
> |_ DELIVER_DATE DateTime Field - non clustered index on this field.
> set showplan_text on
> go
> SELECT * FROM Consign WHERE Deliver_date = '2004-2-3'
> SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
> NULL
> go
> set showplan_text off
> go
> the plan for the first query is doing a 'Index Scan' on the non-clustered
> index on the deliver date field
> the plan for the second query is doing a 'Clustered Index Scan' on a
> clustered index - original_date and i suppose this is the equivalent to a
> table scan as I have moved the clustered index off the primary key to a
> more ordered field (in this case the original_date field).
> I suppose what I am really asking is:
> Are null values placed in an Index ? and if so does that effect the qeury
> if IS NULL (or IS NOT NULL) affect the selection of which index to use
> (sorry about changing the table structure, but i am showing you the
> indexes in question).
> Regards,
> Adrian.
> --
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
> news:e$tD6MWGFHA.1932@.TK2MSFTNGP14.phx.gbl...
> What does it do? A table/clustered index scan? We don't really have
> enough info to answer - can you post the execution plans & the DDL (table
> schema and schema for all indexes on the table)? To get the execution
> plans just turn on showplan_text before the select statements and off
> after the select statements. That is,
> set showplan_text on
> go
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
> NULL
> go
> set showplan_text off
> go
> When I tried it on a tmp table (I had only a nonclustered index on
> DeliverDate) it chose:
> a nonclustered index s for:
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
> a table scan for:
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
> NULL
> a nonclustered index s for:
> SELECT * FROM Deliveries WHERE DeliverDate IS NULL
> I guess the optimiser decided that it was more efficient to scan the table
> once than to do 2 s passes (+ associated lookups) in the DeliverDate
> nonclustered index for the query with the 2 where criteria. When I added
> a clustered index on OrderID, the table scan operation turned into a scan
> on the DeliverDate nonclustered index because the index suddenly became a
> covering index (since all nonclustered indexes also contain the clustering
> key, thereby implicitly making the DeliverDate index really (DeliverDate,
> OrderID)).
> My quick tests only had 10 rows of data. It would make a difference if I
> was running the SELECT queries against a bigger table.
> --
> mike hodgson | database administrator | mallesons stephen jaques
> T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
> E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
>
> Adrian Bezzina wrote:
> Hi All,
> I have the table structure as follows
> ->Deliveries (table)
> |__OrderID (Guid)
> |__DeliverDate (DateTime) (ALLOW NULLS)
> Index is on DeliverDate (ASC)
> 60000 records
> look at the query plan for the following and is using the proper index:
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
> If I now change the query to:
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
> NULL
> The query plan does not choose the DeliverDate index anymore?
> What is the reason for this?
> Regards,
> Adrian.
>
>
>|||Indexes (clustered and nonclustered) include nulls (they're just another
value, albeit a special one). But, when you have the 2 criteria in your
WHERE clause the query engine needs to look through 2 separate ranges of
values in the index (the null values and the '2004-2-3' values)...sort
of. So that's 2 s operations (plus the associated lookups into the
clustered index to get the rest of the data you request in your column
list). So that's twice as much work as if you just have the single
condition in your WHERE clause (DeliverDate = '2004-2-3'). If you just
say "WHERE DeliverDate IS NULL" you get a nice quick plan using your
nonclustered index too. When the query optimiser comes up with an
execution plan (in your case) it figures out pretty quickly that it can
get the data much more efficiently by just scanning the whole clustered
index until it's got all the data it needs, rather than flicking through
the nonclustered index (twice) and looking up the matching leaf nodes in
the clustered index as well.
You'd have to test it out to be sure but I suspect the UNION ALL that
David suggested would not really be any more efficient just because on
the surface of it I'm guessing it would do pretty much the same
operations (just in 2 separate SELECT statements) as the single query
with the OR. The OR operator can be nasty and should be avoided if
possible but sometimes it has it's place.
The only way the nulls in the index affect the index selection process
is that they serve as yet another value to sort in the index. At least
I think that's the only affect they have. With the non-null values, the
optimiser can reorder the WHERE clauses so that it doesn't have to keep
reversing direction in the index to find the noncontinuous rows; not so
with the nulls it would seem.
If you have a big enough, fragmented enough index it will get avoided
all together. I repeated the exercise but included a 3rd column
(char(3000)), just to pad out the rows so I'd get lots of page splits &
index fragmentation, and inserted 3600 rows instead of the 10 rows I had
before (the 10 rows would have been on 1 page but the 3600 rows, each
3000+ bytes, would be split over at least 1800 pages - 2 rows per
page). Selectivity was still the same but the optimiser thought (in all
3 cases (where date = x, where date = x or date is null, and where date
is null)) that it would be better to just do a table scan. I even took
out the null values and made the DeliverDate column NOT NULL and it made
no difference.
Sometimes you just have to trust that the optimiser knows best (which it
usually does). Of course, this could all be crap (I'm getting tired)
but it sounds logical to me.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Adrian Bezzina wrote:
>Mike,
>Thanks for the prompt reply.
>after executing (i know the tables and fields have changed) the query on th
e
>following table:
>Consign (table)
> |
> |_ [UNIQUE] GUID Field - PrimaryKey.
> |
> |_ ORIGINAL_DATE DateTime Field - Clustered Index on this field alone.
> |
> |_ DELIVER_DATE DateTime Field - non clustered index on this field.
>set showplan_text on
>go
>SELECT * FROM Consign WHERE Deliver_date = '2004-2-3'
>SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
>NULL
>go
>set showplan_text off
>go
>the plan for the first query is doing a 'Index Scan' on the non-clustered
>index on the deliver date field
>the plan for the second query is doing a 'Clustered Index Scan' on a
>clustered index - original_date and i suppose this is the equivalent to a
>table scan as I have moved the clustered index off the primary key to a mor
e
>ordered field (in this case the original_date field).
>I suppose what I am really asking is:
>Are null values placed in an Index ? and if so does that effect the qeury i
f
>IS NULL (or IS NOT NULL) affect the selection of which index to use (sorry
>about changing the table structure, but i am showing you the indexes in
>question).
>Regards,
>Adrian.
>--
>"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
>news:e$tD6MWGFHA.1932@.TK2MSFTNGP14.phx.gbl...
>What does it do? A table/clustered index scan? We don't really have enoug
h
>info to answer - can you post the execution plans & the DDL (table schema
>and schema for all indexes on the table)? To get the execution plans just
>turn on showplan_text before the select statements and off after the select
>statements. That is,
>set showplan_text on
>go
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>go
>set showplan_text off
>go
>When I tried it on a tmp table (I had only a nonclustered index on
>DeliverDate) it chose:
>a nonclustered index s for:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>a table scan for:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>a nonclustered index s for:
>SELECT * FROM Deliveries WHERE DeliverDate IS NULL
>I guess the optimiser decided that it was more efficient to scan the table
>once than to do 2 s passes (+ associated lookups) in the DeliverDate
>nonclustered index for the query with the 2 where criteria. When I added a
>clustered index on OrderID, the table scan operation turned into a scan on
>the DeliverDate nonclustered index because the index suddenly became a
>covering index (since all nonclustered indexes also contain the clustering
>key, thereby implicitly making the DeliverDate index really (DeliverDate,
>OrderID)).
>My quick tests only had 10 rows of data. It would make a difference if I
>was running the SELECT queries against a bigger table.
>--
>mike hodgson | database administrator | mallesons stephen jaques
>T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
>E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
>
>Adrian Bezzina wrote:
>Hi All,
>I have the table structure as follows
>->Deliveries (table)
> |__OrderID (Guid)
> |__DeliverDate (DateTime) (ALLOW NULLS)
>Index is on DeliverDate (ASC)
>60000 records
>look at the query plan for the following and is using the proper index:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>If I now change the query to:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>The query plan does not choose the DeliverDate index anymore?
>What is the reason for this?
>Regards,
>Adrian.
>
>
>
>|||Again thanks Guys,
But i am still intriugued to know why the first query ( this table has now
been built from scratch) uses the pk or clustered index if there is one:
(In this case its the Clustered Index on OriginalDate)
SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
NULL
And this uses the indexed created on Deliver_date:
SELECT * FROM Consign WHERE ISNULL(Deliver_date, '2004-2-3') = '2004-2-3'
Does the OR make this much of a difference?
Thanks,
Adrian.
"Steve Kass" <skass@.drew.edu> wrote in message
news:OtLB6qWGFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Adrian,
> The optimizer estimates the number of rows a query will return
> as part of the optimization process. The estimated number of
> rows for which (Deliver_date = '2004-2-3') is going to be fewer
> than the number of rows for which (Deliver_date = '2004-2-3')
> OR (Deliver_date IS NULL). While the nonclustered index
> on Deliver_date can identify these rows in either case (yes, the
> NULLs are in the index), the problem is that it can only identify
> the PrimaryKey values of these rows. The query is SELECT *
> so for every one of these rows, it is still necessary to look up
> all the other column values.
> Once there are a lot of rows in the result (according to the
> optimizer estimate), there is no point gathering all the primary
> key values and looking each one up in the main table. If you
> will end up looking at most of the data pages of the main table
> anyway, why not just scan the table and forget about using the index.
> In your case, the Consign table has very narrow rows, with
> about 24 bytes per row. As a result, about 300 rows fit on
> every data page of the table. Even if only 0.5% of the rows
> are expected to satisfy the query condition, that's 1 in 200 rows,
> or at least one row per data page. The index doesn't help
> in this case.
> It's possible the index would help, if all those 0.5% of the rows
> are all together on a few data pages, but the optimizer doesn't
> have this kind of information available when it decides on the
> best-estimate plan.
> Steve Kass
> Drew University
> Adrian Bezzina wrote:
>|||Upon reflection, most of this sounds like crap to me now (not sure what
I was on at the time). As such, disregard most of it. (Not having a
good day.)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mike Hodgson wrote:
> Indexes (clustered and nonclustered) include nulls (they're just
> another value, albeit a special one). But, when you have the 2
> criteria in your WHERE clause the query engine needs to look through 2
> separate ranges of values in the index (the null values and the
> '2004-2-3' values)...sort of. So that's 2 s operations (plus the
> associated lookups into the clustered index to get the rest of the
> data you request in your column list). So that's twice as much work
> as if you just have the single condition in your WHERE clause
> (DeliverDate = '2004-2-3'). If you just say "WHERE DeliverDate IS
> NULL" you get a nice quick plan using your nonclustered index too.
> When the query optimiser comes up with an execution plan (in your
> case) it figures out pretty quickly that it can get the data much more
> efficiently by just scanning the whole clustered index until it's got
> all the data it needs, rather than flicking through the nonclustered
> index (twice) and looking up the matching leaf nodes in the clustered
> index as well.
> You'd have to test it out to be sure but I suspect the UNION ALL that
> David suggested would not really be any more efficient just because on
> the surface of it I'm guessing it would do pretty much the same
> operations (just in 2 separate SELECT statements) as the single query
> with the OR. The OR operator can be nasty and should be avoided if
> possible but sometimes it has it's place.
> The only way the nulls in the index affect the index selection process
> is that they serve as yet another value to sort in the index. At
> least I think that's the only affect they have. With the non-null
> values, the optimiser can reorder the WHERE clauses so that it doesn't
> have to keep reversing direction in the index to find the
> noncontinuous rows; not so with the nulls it would seem.
> If you have a big enough, fragmented enough index it will get avoided
> all together. I repeated the exercise but included a 3rd column
> (char(3000)), just to pad out the rows so I'd get lots of page splits
> & index fragmentation, and inserted 3600 rows instead of the 10 rows I
> had before (the 10 rows would have been on 1 page but the 3600 rows,
> each 3000+ bytes, would be split over at least 1800 pages - 2 rows per
> page). Selectivity was still the same but the optimiser thought (in
> all 3 cases (where date = x, where date = x or date is null, and where
> date is null)) that it would be better to just do a table scan. I
> even took out the null values and made the DeliverDate column NOT NULL
> and it made no difference.
> Sometimes you just have to trust that the optimiser knows best (which
> it usually does). Of course, this could all be crap (I'm getting
> tired) but it sounds logical to me.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Adrian Bezzina wrote:
>|||Can you specify which index operation it is using on the clustered index. Re
member that a clustered
index scan = table scan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Bezzina" <AdrianRB@.bigpond.net.au> wrote in message
news:OqpQViXGFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Again thanks Guys,
> But i am still intriugued to know why the first query ( this table has now
been built from
> scratch) uses the pk or clustered index if there is one:
> (In this case its the Clustered Index on OriginalDate)
> SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS N
ULL
> And this uses the indexed created on Deliver_date:
> SELECT * FROM Consign WHERE ISNULL(Deliver_date, '2004-2-3') = '2004-2-3'
> Does the OR make this much of a difference?
> Thanks,
> Adrian.
> "Steve Kass" <skass@.drew.edu> wrote in message news:OtLB6qWGFHA.3648@.TK2MS
FTNGP09.phx.gbl...
>
I have the table structure as follows
->Deliveries (table)
|__OrderID (Guid)
|__DeliverDate (DateTime) (ALLOW NULLS)
Index is on DeliverDate (ASC)
60000 records
look at the query plan for the following and is using the proper index:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
If I now change the query to:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
NULL
The query plan does not choose the DeliverDate index anymore?
What is the reason for this?
Regards,
Adrian.Adrian Bezzina wrote:
> Hi All,
> I have the table structure as follows
> ->Deliveries (table)
> |__OrderID (Guid)
> |__DeliverDate (DateTime) (ALLOW NULLS)
> Index is on DeliverDate (ASC)
> 60000 records
> look at the query plan for the following and is using the proper
> index:
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
> If I now change the query to:
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR
> DeliverDate IS NULL
> The query plan does not choose the DeliverDate index anymore?
> What is the reason for this?
> Regards,
> Adrian.
OR statements can sometimes be an index killer. It could also be because
you have a lot of NULL values in the index.
The first thing you should do is never use SELECT *. For one thing, it
almost always returns more column information than required. It also
does not give SQL Server the opportunity to use a covering index (unless
all columns are covered).
OR statements are, many times, not optimizeable. Even if they are, SQL
Server will only use a non-clustered index if the number of rows
returned is low enough that using the index doesn't cause more overhead
than scanning the table. Remember, unless your index is covering, every
index s
requires a bookmark lookup operation (especially if you useSELECT *).
You could try using:
Select col1, col2
From Deliveries
Where DeliveryDate = '2003-12-14'
UNION ALL
Select col1, col2
From Deliveries
Where DeliveryDate IS NULL
or you could try changing your index to clustered.
David Gugick
Imceda Software
www.imceda.com|||What does it do? A table/clustered index scan? We don't really have
enough info to answer - can you post the execution plans & the DDL
(table schema and schema for all indexes on the table)? To get the
execution plans just turn on showplan_text before the select statements
and off after the select statements. That is,
set showplan_text on
go
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS NU
LL
go
set showplan_text off
go
When I tried it on a tmp table (I had only a nonclustered index on DeliverDa
te) it chose:
a nonclustered index s
for:SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
a table scan for:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS NU
LL
a nonclustered index s
for:SELECT * FROM Deliveries WHERE DeliverDate IS NULL
I guess the optimiser decided that it was more efficient to scan the table o
nce than to do 2 s
passes (+ associated lookups) in the DeliverDate nonclustered index for the query with the 2 where criteria. When I added a clust
ered index on OrderID, the
table scan operation turned into a scan on the DeliverDate nonclustered inde
x because the index suddenly became a covering index (since all nonclustered
indexes also contain the clustering key, thereby implicitly making the Deli
verDate index really (Deliv
erDate, OrderID)).
My quick tests only had 10 rows of data. It would make a difference if I wa
s running the SELECT queries against a bigger table.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Adrian Bezzina wrote:
>Hi All,
>I have the table structure as follows
>->Deliveries (table)
> |__OrderID (Guid)
> |__DeliverDate (DateTime) (ALLOW NULLS)
>Index is on DeliverDate (ASC)
>60000 records
>look at the query plan for the following and is using the proper index:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>If I now change the query to:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>The query plan does not choose the DeliverDate index anymore?
>What is the reason for this?
>Regards,
>Adrian.
>
>|||Mike,
Thanks for the prompt reply.
after executing (i know the tables and fields have changed) the query on the
following table:
Consign (table)
|
|_ [UNIQUE] GUID Field - PrimaryKey.
|
|_ ORIGINAL_DATE DateTime Field - Clustered Index on this field alone.
|
|_ DELIVER_DATE DateTime Field - non clustered index on this field.
set showplan_text on
go
SELECT * FROM Consign WHERE Deliver_date = '2004-2-3'
SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
NULL
go
set showplan_text off
go
the plan for the first query is doing a 'Index Scan' on the non-clustered
index on the deliver date field
the plan for the second query is doing a 'Clustered Index Scan' on a
clustered index - original_date and i suppose this is the equivalent to a
table scan as I have moved the clustered index off the primary key to a more
ordered field (in this case the original_date field).
I suppose what I am really asking is:
Are null values placed in an Index ? and if so does that effect the qeury if
IS NULL (or IS NOT NULL) affect the selection of which index to use (sorry
about changing the table structure, but i am showing you the indexes in
question).
Regards,
Adrian.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:e$tD6MWGFHA.1932@.TK2MSFTNGP14.phx.gbl...
What does it do? A table/clustered index scan? We don't really have enough
info to answer - can you post the execution plans & the DDL (table schema
and schema for all indexes on the table)? To get the execution plans just
turn on showplan_text before the select statements and off after the select
statements. That is,
set showplan_text on
go
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
NULL
go
set showplan_text off
go
When I tried it on a tmp table (I had only a nonclustered index on
DeliverDate) it chose:
a nonclustered index s
for:SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
a table scan for:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
NULL
a nonclustered index s
for:SELECT * FROM Deliveries WHERE DeliverDate IS NULL
I guess the optimiser decided that it was more efficient to scan the table
once than to do 2 s
passes (+ associated lookups) in the DeliverDatenonclustered index for the query with the 2 where criteria. When I added a
clustered index on OrderID, the table scan operation turned into a scan on
the DeliverDate nonclustered index because the index suddenly became a
covering index (since all nonclustered indexes also contain the clustering
key, thereby implicitly making the DeliverDate index really (DeliverDate,
OrderID)).
My quick tests only had 10 rows of data. It would make a difference if I
was running the SELECT queries against a bigger table.
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
Adrian Bezzina wrote:
Hi All,
I have the table structure as follows
->Deliveries (table)
|__OrderID (Guid)
|__DeliverDate (DateTime) (ALLOW NULLS)
Index is on DeliverDate (ASC)
60000 records
look at the query plan for the following and is using the proper index:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
If I now change the query to:
SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
NULL
The query plan does not choose the DeliverDate index anymore?
What is the reason for this?
Regards,
Adrian.|||Adrian,
The optimizer estimates the number of rows a query will return
as part of the optimization process. The estimated number of
rows for which (Deliver_date = '2004-2-3') is going to be fewer
than the number of rows for which (Deliver_date = '2004-2-3')
OR (Deliver_date IS NULL). While the nonclustered index
on Deliver_date can identify these rows in either case (yes, the
NULLs are in the index), the problem is that it can only identify
the PrimaryKey values of these rows. The query is SELECT *
so for every one of these rows, it is still necessary to look up
all the other column values.
Once there are a lot of rows in the result (according to the
optimizer estimate), there is no point gathering all the primary
key values and looking each one up in the main table. If you
will end up looking at most of the data pages of the main table
anyway, why not just scan the table and forget about using the index.
In your case, the Consign table has very narrow rows, with
about 24 bytes per row. As a result, about 300 rows fit on
every data page of the table. Even if only 0.5% of the rows
are expected to satisfy the query condition, that's 1 in 200 rows,
or at least one row per data page. The index doesn't help
in this case.
It's possible the index would help, if all those 0.5% of the rows
are all together on a few data pages, but the optimizer doesn't
have this kind of information available when it decides on the
best-estimate plan.
Steve Kass
Drew University
Adrian Bezzina wrote:
>Mike,
>Thanks for the prompt reply.
>after executing (i know the tables and fields have changed) the query on th
e
>following table:
>Consign (table)
> |
> |_ [UNIQUE] GUID Field - PrimaryKey.
> |
> |_ ORIGINAL_DATE DateTime Field - Clustered Index on this field alone.
> |
> |_ DELIVER_DATE DateTime Field - non clustered index on this field.
>set showplan_text on
>go
>SELECT * FROM Consign WHERE Deliver_date = '2004-2-3'
>SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
>NULL
>go
>set showplan_text off
>go
>the plan for the first query is doing a 'Index Scan' on the non-clustered
>index on the deliver date field
>the plan for the second query is doing a 'Clustered Index Scan' on a
>clustered index - original_date and i suppose this is the equivalent to a
>table scan as I have moved the clustered index off the primary key to a mor
e
>ordered field (in this case the original_date field).
>I suppose what I am really asking is:
>Are null values placed in an Index ? and if so does that effect the qeury i
f
>IS NULL (or IS NOT NULL) affect the selection of which index to use (sorry
>about changing the table structure, but i am showing you the indexes in
>question).
>Regards,
>Adrian.
>--
>"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
>news:e$tD6MWGFHA.1932@.TK2MSFTNGP14.phx.gbl...
>What does it do? A table/clustered index scan? We don't really have enoug
h
>info to answer - can you post the execution plans & the DDL (table schema
>and schema for all indexes on the table)? To get the execution plans just
>turn on showplan_text before the select statements and off after the select
>statements. That is,
>set showplan_text on
>go
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>go
>set showplan_text off
>go
>When I tried it on a tmp table (I had only a nonclustered index on
>DeliverDate) it chose:
>a nonclustered index s
for:>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>a table scan for:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>a nonclustered index s
for:>SELECT * FROM Deliveries WHERE DeliverDate IS NULL
>I guess the optimiser decided that it was more efficient to scan the table
>once than to do 2 s
passes (+ associated lookups) in the DeliverDate>nonclustered index for the query with the 2 where criteria. When I added a
>clustered index on OrderID, the table scan operation turned into a scan on
>the DeliverDate nonclustered index because the index suddenly became a
>covering index (since all nonclustered indexes also contain the clustering
>key, thereby implicitly making the DeliverDate index really (DeliverDate,
>OrderID)).
>My quick tests only had 10 rows of data. It would make a difference if I
>was running the SELECT queries against a bigger table.
>--
>mike hodgson | database administrator | mallesons stephen jaques
>T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
>E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
>
>Adrian Bezzina wrote:
>Hi All,
>I have the table structure as follows
>->Deliveries (table)
> |__OrderID (Guid)
> |__DeliverDate (DateTime) (ALLOW NULLS)
>Index is on DeliverDate (ASC)
>60000 records
>look at the query plan for the following and is using the proper index:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>If I now change the query to:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>The query plan does not choose the DeliverDate index anymore?
>What is the reason for this?
>Regards,
>Adrian.
>
>
>
>|||Guys,
Thanks for the help (I can pass a paremeter as follows):
Instead of:
SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
NULL
I can do this (work nicely):
SELECT * FROM Consign WHERE ISNULL(Deliver_date, '2004-2-3') = '2004-2-3'
This always uses the index on DELIVER_DATE
:)
BTW its actually called as follows:
DECLARE @.deliverDate AS DATETIME
SET @.deliverDate = '2004-2-3'
SELECT * FROM Consign WHERE ISNULL(Deliver_date, @.deliverDate) =
@.deliverDate
Works like a treat.
Thanks
Adrian.
"Adrian Bezzina" <Adrian@.tristalee.com.au> wrote in message
news:OnSmvgWGFHA.1740@.TK2MSFTNGP09.phx.gbl...
> Mike,
> Thanks for the prompt reply.
> after executing (i know the tables and fields have changed) the query on
> the following table:
> Consign (table)
> |
> |_ [UNIQUE] GUID Field - PrimaryKey.
> |
> |_ ORIGINAL_DATE DateTime Field - Clustered Index on this field alone.
> |
> |_ DELIVER_DATE DateTime Field - non clustered index on this field.
> set showplan_text on
> go
> SELECT * FROM Consign WHERE Deliver_date = '2004-2-3'
> SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
> NULL
> go
> set showplan_text off
> go
> the plan for the first query is doing a 'Index Scan' on the non-clustered
> index on the deliver date field
> the plan for the second query is doing a 'Clustered Index Scan' on a
> clustered index - original_date and i suppose this is the equivalent to a
> table scan as I have moved the clustered index off the primary key to a
> more ordered field (in this case the original_date field).
> I suppose what I am really asking is:
> Are null values placed in an Index ? and if so does that effect the qeury
> if IS NULL (or IS NOT NULL) affect the selection of which index to use
> (sorry about changing the table structure, but i am showing you the
> indexes in question).
> Regards,
> Adrian.
> --
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
> news:e$tD6MWGFHA.1932@.TK2MSFTNGP14.phx.gbl...
> What does it do? A table/clustered index scan? We don't really have
> enough info to answer - can you post the execution plans & the DDL (table
> schema and schema for all indexes on the table)? To get the execution
> plans just turn on showplan_text before the select statements and off
> after the select statements. That is,
> set showplan_text on
> go
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
> NULL
> go
> set showplan_text off
> go
> When I tried it on a tmp table (I had only a nonclustered index on
> DeliverDate) it chose:
> a nonclustered index s
for:> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
> a table scan for:
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
> NULL
> a nonclustered index s
for:> SELECT * FROM Deliveries WHERE DeliverDate IS NULL
> I guess the optimiser decided that it was more efficient to scan the table
> once than to do 2 s
passes (+ associated lookups) in the DeliverDate> nonclustered index for the query with the 2 where criteria. When I added
> a clustered index on OrderID, the table scan operation turned into a scan
> on the DeliverDate nonclustered index because the index suddenly became a
> covering index (since all nonclustered indexes also contain the clustering
> key, thereby implicitly making the DeliverDate index really (DeliverDate,
> OrderID)).
> My quick tests only had 10 rows of data. It would make a difference if I
> was running the SELECT queries against a bigger table.
> --
> mike hodgson | database administrator | mallesons stephen jaques
> T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
> E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
>
> Adrian Bezzina wrote:
> Hi All,
> I have the table structure as follows
> ->Deliveries (table)
> |__OrderID (Guid)
> |__DeliverDate (DateTime) (ALLOW NULLS)
> Index is on DeliverDate (ASC)
> 60000 records
> look at the query plan for the following and is using the proper index:
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
> If I now change the query to:
> SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
> NULL
> The query plan does not choose the DeliverDate index anymore?
> What is the reason for this?
> Regards,
> Adrian.
>
>
>|||Indexes (clustered and nonclustered) include nulls (they're just another
value, albeit a special one). But, when you have the 2 criteria in your
WHERE clause the query engine needs to look through 2 separate ranges of
values in the index (the null values and the '2004-2-3' values)...sort
of. So that's 2 s
operations (plus the associated lookups into theclustered index to get the rest of the data you request in your column
list). So that's twice as much work as if you just have the single
condition in your WHERE clause (DeliverDate = '2004-2-3'). If you just
say "WHERE DeliverDate IS NULL" you get a nice quick plan using your
nonclustered index too. When the query optimiser comes up with an
execution plan (in your case) it figures out pretty quickly that it can
get the data much more efficiently by just scanning the whole clustered
index until it's got all the data it needs, rather than flicking through
the nonclustered index (twice) and looking up the matching leaf nodes in
the clustered index as well.
You'd have to test it out to be sure but I suspect the UNION ALL that
David suggested would not really be any more efficient just because on
the surface of it I'm guessing it would do pretty much the same
operations (just in 2 separate SELECT statements) as the single query
with the OR. The OR operator can be nasty and should be avoided if
possible but sometimes it has it's place.
The only way the nulls in the index affect the index selection process
is that they serve as yet another value to sort in the index. At least
I think that's the only affect they have. With the non-null values, the
optimiser can reorder the WHERE clauses so that it doesn't have to keep
reversing direction in the index to find the noncontinuous rows; not so
with the nulls it would seem.
If you have a big enough, fragmented enough index it will get avoided
all together. I repeated the exercise but included a 3rd column
(char(3000)), just to pad out the rows so I'd get lots of page splits &
index fragmentation, and inserted 3600 rows instead of the 10 rows I had
before (the 10 rows would have been on 1 page but the 3600 rows, each
3000+ bytes, would be split over at least 1800 pages - 2 rows per
page). Selectivity was still the same but the optimiser thought (in all
3 cases (where date = x, where date = x or date is null, and where date
is null)) that it would be better to just do a table scan. I even took
out the null values and made the DeliverDate column NOT NULL and it made
no difference.
Sometimes you just have to trust that the optimiser knows best (which it
usually does). Of course, this could all be crap (I'm getting tired)
but it sounds logical to me.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Adrian Bezzina wrote:
>Mike,
>Thanks for the prompt reply.
>after executing (i know the tables and fields have changed) the query on th
e
>following table:
>Consign (table)
> |
> |_ [UNIQUE] GUID Field - PrimaryKey.
> |
> |_ ORIGINAL_DATE DateTime Field - Clustered Index on this field alone.
> |
> |_ DELIVER_DATE DateTime Field - non clustered index on this field.
>set showplan_text on
>go
>SELECT * FROM Consign WHERE Deliver_date = '2004-2-3'
>SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
>NULL
>go
>set showplan_text off
>go
>the plan for the first query is doing a 'Index Scan' on the non-clustered
>index on the deliver date field
>the plan for the second query is doing a 'Clustered Index Scan' on a
>clustered index - original_date and i suppose this is the equivalent to a
>table scan as I have moved the clustered index off the primary key to a mor
e
>ordered field (in this case the original_date field).
>I suppose what I am really asking is:
>Are null values placed in an Index ? and if so does that effect the qeury i
f
>IS NULL (or IS NOT NULL) affect the selection of which index to use (sorry
>about changing the table structure, but i am showing you the indexes in
>question).
>Regards,
>Adrian.
>--
>"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
>news:e$tD6MWGFHA.1932@.TK2MSFTNGP14.phx.gbl...
>What does it do? A table/clustered index scan? We don't really have enoug
h
>info to answer - can you post the execution plans & the DDL (table schema
>and schema for all indexes on the table)? To get the execution plans just
>turn on showplan_text before the select statements and off after the select
>statements. That is,
>set showplan_text on
>go
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>go
>set showplan_text off
>go
>When I tried it on a tmp table (I had only a nonclustered index on
>DeliverDate) it chose:
>a nonclustered index s
for:>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>a table scan for:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>a nonclustered index s
for:>SELECT * FROM Deliveries WHERE DeliverDate IS NULL
>I guess the optimiser decided that it was more efficient to scan the table
>once than to do 2 s
passes (+ associated lookups) in the DeliverDate>nonclustered index for the query with the 2 where criteria. When I added a
>clustered index on OrderID, the table scan operation turned into a scan on
>the DeliverDate nonclustered index because the index suddenly became a
>covering index (since all nonclustered indexes also contain the clustering
>key, thereby implicitly making the DeliverDate index really (DeliverDate,
>OrderID)).
>My quick tests only had 10 rows of data. It would make a difference if I
>was running the SELECT queries against a bigger table.
>--
>mike hodgson | database administrator | mallesons stephen jaques
>T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
>E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
>
>Adrian Bezzina wrote:
>Hi All,
>I have the table structure as follows
>->Deliveries (table)
> |__OrderID (Guid)
> |__DeliverDate (DateTime) (ALLOW NULLS)
>Index is on DeliverDate (ASC)
>60000 records
>look at the query plan for the following and is using the proper index:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3'
>If I now change the query to:
>SELECT * FROM Deliveries WHERE DeliverDate = '2004-2-3' OR DeliverDate IS
>NULL
>The query plan does not choose the DeliverDate index anymore?
>What is the reason for this?
>Regards,
>Adrian.
>
>
>
>|||Again thanks Guys,
But i am still intriugued to know why the first query ( this table has now
been built from scratch) uses the pk or clustered index if there is one:
(In this case its the Clustered Index on OriginalDate)
SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS
NULL
And this uses the indexed created on Deliver_date:
SELECT * FROM Consign WHERE ISNULL(Deliver_date, '2004-2-3') = '2004-2-3'
Does the OR make this much of a difference?
Thanks,
Adrian.
"Steve Kass" <skass@.drew.edu> wrote in message
news:OtLB6qWGFHA.3648@.TK2MSFTNGP09.phx.gbl...
> Adrian,
> The optimizer estimates the number of rows a query will return
> as part of the optimization process. The estimated number of
> rows for which (Deliver_date = '2004-2-3') is going to be fewer
> than the number of rows for which (Deliver_date = '2004-2-3')
> OR (Deliver_date IS NULL). While the nonclustered index
> on Deliver_date can identify these rows in either case (yes, the
> NULLs are in the index), the problem is that it can only identify
> the PrimaryKey values of these rows. The query is SELECT *
> so for every one of these rows, it is still necessary to look up
> all the other column values.
> Once there are a lot of rows in the result (according to the
> optimizer estimate), there is no point gathering all the primary
> key values and looking each one up in the main table. If you
> will end up looking at most of the data pages of the main table
> anyway, why not just scan the table and forget about using the index.
> In your case, the Consign table has very narrow rows, with
> about 24 bytes per row. As a result, about 300 rows fit on
> every data page of the table. Even if only 0.5% of the rows
> are expected to satisfy the query condition, that's 1 in 200 rows,
> or at least one row per data page. The index doesn't help
> in this case.
> It's possible the index would help, if all those 0.5% of the rows
> are all together on a few data pages, but the optimizer doesn't
> have this kind of information available when it decides on the
> best-estimate plan.
> Steve Kass
> Drew University
> Adrian Bezzina wrote:
>|||Upon reflection, most of this sounds like crap to me now (not sure what
I was on at the time). As such, disregard most of it. (Not having a
good day.)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mike Hodgson wrote:
> Indexes (clustered and nonclustered) include nulls (they're just
> another value, albeit a special one). But, when you have the 2
> criteria in your WHERE clause the query engine needs to look through 2
> separate ranges of values in the index (the null values and the
> '2004-2-3' values)...sort of. So that's 2 s
operations (plus the> associated lookups into the clustered index to get the rest of the
> data you request in your column list). So that's twice as much work
> as if you just have the single condition in your WHERE clause
> (DeliverDate = '2004-2-3'). If you just say "WHERE DeliverDate IS
> NULL" you get a nice quick plan using your nonclustered index too.
> When the query optimiser comes up with an execution plan (in your
> case) it figures out pretty quickly that it can get the data much more
> efficiently by just scanning the whole clustered index until it's got
> all the data it needs, rather than flicking through the nonclustered
> index (twice) and looking up the matching leaf nodes in the clustered
> index as well.
> You'd have to test it out to be sure but I suspect the UNION ALL that
> David suggested would not really be any more efficient just because on
> the surface of it I'm guessing it would do pretty much the same
> operations (just in 2 separate SELECT statements) as the single query
> with the OR. The OR operator can be nasty and should be avoided if
> possible but sometimes it has it's place.
> The only way the nulls in the index affect the index selection process
> is that they serve as yet another value to sort in the index. At
> least I think that's the only affect they have. With the non-null
> values, the optimiser can reorder the WHERE clauses so that it doesn't
> have to keep reversing direction in the index to find the
> noncontinuous rows; not so with the nulls it would seem.
> If you have a big enough, fragmented enough index it will get avoided
> all together. I repeated the exercise but included a 3rd column
> (char(3000)), just to pad out the rows so I'd get lots of page splits
> & index fragmentation, and inserted 3600 rows instead of the 10 rows I
> had before (the 10 rows would have been on 1 page but the 3600 rows,
> each 3000+ bytes, would be split over at least 1800 pages - 2 rows per
> page). Selectivity was still the same but the optimiser thought (in
> all 3 cases (where date = x, where date = x or date is null, and where
> date is null)) that it would be better to just do a table scan. I
> even took out the null values and made the DeliverDate column NOT NULL
> and it made no difference.
> Sometimes you just have to trust that the optimiser knows best (which
> it usually does). Of course, this could all be crap (I'm getting
> tired) but it sounds logical to me.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> Adrian Bezzina wrote:
>|||Can you specify which index operation it is using on the clustered index. Re
member that a clustered
index scan = table scan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian Bezzina" <AdrianRB@.bigpond.net.au> wrote in message
news:OqpQViXGFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Again thanks Guys,
> But i am still intriugued to know why the first query ( this table has now
been built from
> scratch) uses the pk or clustered index if there is one:
> (In this case its the Clustered Index on OriginalDate)
> SELECT * FROM Consign WHERE Deliver_date = '2004-2-3' OR Deliver_date IS N
ULL
> And this uses the indexed created on Deliver_date:
> SELECT * FROM Consign WHERE ISNULL(Deliver_date, '2004-2-3') = '2004-2-3'
> Does the OR make this much of a difference?
> Thanks,
> Adrian.
> "Steve Kass" <skass@.drew.edu> wrote in message news:OtLB6qWGFHA.3648@.TK2MS
FTNGP09.phx.gbl...
>
IS NULL in SQL statement
Hi,
I am trying to update a table where a field is set to null. The query is:
UPDATE U_segment
SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
FROM U_segment
INNER JOIN U_T_STEP02A
ON U_segment.REF = U_T_STEP02A.REF
WHERE U_segment.SEGMENT ISNULL
It's not liking it at all.
Any help would be appreciated
Rob
> WHERE U_segment.SEGMENT ISNULL
Above should be:
WHERE U_segment.SEGMENT IS NULL
Note the space between the words IS and NULL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob
|||"IS NULL" --> "IS[thisisablank]NULL"
ISNULL is a function which has a signature like ISNULL(Expression,
Valueifexpressionisnull).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Robert" <Robert@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob
|||I think you need to have space between IS and NULL -> IS NULL instead of
ISNULL
Regards
Steen
"Robert" <Robert@.discussions.microsoft.com> skrev i en meddelelse
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob
I am trying to update a table where a field is set to null. The query is:
UPDATE U_segment
SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
FROM U_segment
INNER JOIN U_T_STEP02A
ON U_segment.REF = U_T_STEP02A.REF
WHERE U_segment.SEGMENT ISNULL
It's not liking it at all.
Any help would be appreciated
Rob
> WHERE U_segment.SEGMENT ISNULL
Above should be:
WHERE U_segment.SEGMENT IS NULL
Note the space between the words IS and NULL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob
|||"IS NULL" --> "IS[thisisablank]NULL"
ISNULL is a function which has a signature like ISNULL(Expression,
Valueifexpressionisnull).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Robert" <Robert@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob
|||I think you need to have space between IS and NULL -> IS NULL instead of
ISNULL
Regards
Steen
"Robert" <Robert@.discussions.microsoft.com> skrev i en meddelelse
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob
IS NULL in SQL statement
Hi,
I am trying to update a table where a field is set to null. The query is:
UPDATE U_segment
SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
FROM U_segment
INNER JOIN U_T_STEP02A
ON U_segment.REF = U_T_STEP02A.REF
WHERE U_segment.SEGMENT ISNULL
It's not liking it at all.
Any help would be appreciated
Rob> WHERE U_segment.SEGMENT ISNULL
Above should be:
WHERE U_segment.SEGMENT IS NULL
Note the space between the words IS and NULL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob|||"IS NULL" --> "IS[thisisablank]NULL"
ISNULL is a function which has a signature like ISNULL(Expression,
Valueifexpressionisnull).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert" <Robert@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob|||I think you need to have space between IS and NULL -> IS NULL instead of
ISNULL
Regards
Steen
"Robert" <Robert@.discussions.microsoft.com> skrev i en meddelelse
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob
I am trying to update a table where a field is set to null. The query is:
UPDATE U_segment
SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
FROM U_segment
INNER JOIN U_T_STEP02A
ON U_segment.REF = U_T_STEP02A.REF
WHERE U_segment.SEGMENT ISNULL
It's not liking it at all.
Any help would be appreciated
Rob> WHERE U_segment.SEGMENT ISNULL
Above should be:
WHERE U_segment.SEGMENT IS NULL
Note the space between the words IS and NULL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob|||"IS NULL" --> "IS[thisisablank]NULL"
ISNULL is a function which has a signature like ISNULL(Expression,
Valueifexpressionisnull).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert" <Robert@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob|||I think you need to have space between IS and NULL -> IS NULL instead of
ISNULL
Regards
Steen
"Robert" <Robert@.discussions.microsoft.com> skrev i en meddelelse
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob
IS NULL in SQL statement
Hi,
I am trying to update a table where a field is set to null. The query is:
UPDATE U_segment
SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
FROM U_segment
INNER JOIN U_T_STEP02A
ON U_segment.REF = U_T_STEP02A.REF
WHERE U_segment.SEGMENT ISNULL
It's not liking it at all.
Any help would be appreciated
Rob> WHERE U_segment.SEGMENT ISNULL
Above should be:
WHERE U_segment.SEGMENT IS NULL
Note the space between the words IS and NULL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob|||"IS NULL" --> "IS[thisisablank]NULL"
ISNULL is a function which has a signature like ISNULL(Expression,
Valueifexpressionisnull).
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Robert" <Robert@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob|||I think you need to have space between IS and NULL -> IS NULL instead of
ISNULL
Regards
Steen
"Robert" <Robert@.discussions.microsoft.com> skrev i en meddelelse
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob
I am trying to update a table where a field is set to null. The query is:
UPDATE U_segment
SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
FROM U_segment
INNER JOIN U_T_STEP02A
ON U_segment.REF = U_T_STEP02A.REF
WHERE U_segment.SEGMENT ISNULL
It's not liking it at all.
Any help would be appreciated
Rob> WHERE U_segment.SEGMENT ISNULL
Above should be:
WHERE U_segment.SEGMENT IS NULL
Note the space between the words IS and NULL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob|||"IS NULL" --> "IS[thisisablank]NULL"
ISNULL is a function which has a signature like ISNULL(Expression,
Valueifexpressionisnull).
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Robert" <Robert@.discussions.microsoft.com> schrieb im Newsbeitrag
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob|||I think you need to have space between IS and NULL -> IS NULL instead of
ISNULL
Regards
Steen
"Robert" <Robert@.discussions.microsoft.com> skrev i en meddelelse
news:DB39722D-833F-4C99-8F8A-E219E002A6DA@.microsoft.com...
> Hi,
> I am trying to update a table where a field is set to null. The query is:
> UPDATE U_segment
> SET SEGMENT = '2A-Legacy Pledges Committed-MULTI'
> FROM U_segment
> INNER JOIN U_T_STEP02A
> ON U_segment.REF = U_T_STEP02A.REF
> WHERE U_segment.SEGMENT ISNULL
> It's not liking it at all.
> Any help would be appreciated
> Rob
Monday, February 20, 2012
Is not Null clause not working for me
I've got a strange problem with my sql not working. I'm trying to query a
table and have a where clause to return all the values in a field which are
not null(in other words left blank) The query can be seen below.
However this query is still returning all of the records in the table even
though the u_creditor_ref field has mainly null values.
My table looks like the following: -
u_surname u_firstname u_account_name u_creditor_ref
Cairns Stephen
Kelly Michael 456789
McCartney&Son
Walsh John 4565
Has anyone any idea what i'm doing wrong. Im running this query in query
analyzer
Select u_surname, u_firstname, u_account_name, u_creditor_ref
from UserObject
where u_creditor_ref is not nullStephen
I think you have an empty string in the row
Try adding WHERE col <>''
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||Perhaps you don't have NULL for those rows. Perhaps it is a blank or similar
? Can you post a
reproduction script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which ar
e
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||"which are
not null(in other words left blank) "
Keep in mind that "blank" LEN(Column) = 0 , doesnt mean that they are NULL
!
If there is an empty string inserted you have to check for that LEN(
u_creditor_ref) = 0, if you have addtionaly also NULL values in there you
have to add "AND u_creditor_ref IS NULL)
Check the Column for NULL Values With (Select Count(*), CASE u_creditor_ref
WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END From Yourtable Group by CASE
u_creditor_ref WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
> are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||That works cheers
"Uri Dimant" wrote:
> Stephen
> I think you have an empty string in the row
> Try adding WHERE col <>''
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> are
>
>|||Yeah your right. it looked like a null but if i did either of the following
it worked
where u_creditor_ref <>''
or
where u_creditor_ref != ''
which of these is better to use or do they mean the exact same thing?
"Tibor Karaszi" wrote:
> Perhaps you don't have NULL for those rows. Perhaps it is a blank or simil
ar? Can you post a
> reproduction script?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
>
>|||Use <>. They do the same, but <> is ANSI SQL compliant, so you might just wa
nt to get into the
habit...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the followin
g
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>|||Use <> for spec purposes.
Jens Suessmeyer.
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the
> following
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>
table and have a where clause to return all the values in a field which are
not null(in other words left blank) The query can be seen below.
However this query is still returning all of the records in the table even
though the u_creditor_ref field has mainly null values.
My table looks like the following: -
u_surname u_firstname u_account_name u_creditor_ref
Cairns Stephen
Kelly Michael 456789
McCartney&Son
Walsh John 4565
Has anyone any idea what i'm doing wrong. Im running this query in query
analyzer
Select u_surname, u_firstname, u_account_name, u_creditor_ref
from UserObject
where u_creditor_ref is not nullStephen
I think you have an empty string in the row
Try adding WHERE col <>''
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||Perhaps you don't have NULL for those rows. Perhaps it is a blank or similar
? Can you post a
reproduction script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which ar
e
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||"which are
not null(in other words left blank) "
Keep in mind that "blank" LEN(Column) = 0 , doesnt mean that they are NULL
!
If there is an empty string inserted you have to check for that LEN(
u_creditor_ref) = 0, if you have addtionaly also NULL values in there you
have to add "AND u_creditor_ref IS NULL)
Check the Column for NULL Values With (Select Count(*), CASE u_creditor_ref
WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END From Yourtable Group by CASE
u_creditor_ref WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
> are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||That works cheers
"Uri Dimant" wrote:
> Stephen
> I think you have an empty string in the row
> Try adding WHERE col <>''
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> are
>
>|||Yeah your right. it looked like a null but if i did either of the following
it worked
where u_creditor_ref <>''
or
where u_creditor_ref != ''
which of these is better to use or do they mean the exact same thing?
"Tibor Karaszi" wrote:
> Perhaps you don't have NULL for those rows. Perhaps it is a blank or simil
ar? Can you post a
> reproduction script?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
>
>|||Use <>. They do the same, but <> is ANSI SQL compliant, so you might just wa
nt to get into the
habit...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the followin
g
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>|||Use <> for spec purposes.
Jens Suessmeyer.
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the
> following
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>
Subscribe to:
Posts (Atom)