Database

6-Nov-03 4:04

I have a table and everything works out okay if I log in as sa, naturally.

If I run:
SELECT TOP 1000 * FROM [site] WHERE [date]>='2000-10-01' And [date]<='2000-10-07 23:59:00'

select count(*) from site

select * from site

I get back everything I expect.

However if I log is as another user, the first SQL results in zero rows (as sa it was ~700), the second statement reports there are ~5000 in the table, and the third returns all ~5000 rows. Weird!

I have a feeling I am missing something totally obvious, but I can't figure it out, especially as the statment without the WHERE clause works fine.

Any Ideas?

--Colin Mackay--

"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)

(Actually it wasn't)

Colin Angus Mackay6-Nov-03 5:33

6-Nov-03 5:33

It was a date thing. At some point a particular user got set to "British English" rather than just "English"???? And it flipped the month and days around.

Now... (perhaps I should go off to the soapbox at this point) WHY THE $&*% is American localisation formats called "English" and British localisation formats called "British English". If someone said this date is in "English" format I'd expect the format to be the one used in England. Makes Sense? Doesn't it?! But oooohhh! noooooo! in SQL Server 2000 "English" format dates mean the format of dates used in the United States of America! (which is about 3000 miles from England).

Aaaarrrrrrggghhhh!

--Colin Mackay--

"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)

Michael Dunn6-Nov-03 13:01

Michael Dunn

6-Nov-03 13:01

What country is Microsoft located in? US. Thus English is US English.

Actually, if I saw "date format: English" I'd immediately be suspicious because I know that different English-speaking countries use different date formats.

--Mike--
Ericahist [updated Oct 26] | CP SearchBar v2.0.2 | Homepage | RightClick-Encrypt | 1ClickPicGrabber
#include "witty-quote.h"

Jörgen Sigvardsson6-Nov-03 13:58

Jörgen Sigvardsson

6-Nov-03 13:58

I colleague of mine is having similar problems to what you're describing.

<rant>What's amazing to me is why the hell localization is done on this level. Why not stick with ISO dates and let applications worry about localization and presentation. Same thing goes for decimal points. The DBMS vendors have gotten it all wrong if you ask me. A database is seldom accessed by end users with a console anyway. A DBA should be able to handle ISO-dates.</rant>

--
The moment of terror is the beginning of life.

Colin Angus Mackay7-Nov-03 1:37

7-Nov-03 1:37

I totally agree - The RDMS should not be handling presentation layer issues like date formats.

However some localisation issues may still be important - For example, collation order - It is much more efficient if SQL Server handles localised collation sequences. For example, take the values:

Lladró
London

is correct if you are dealing with the English language. But if you select Traditional Spanish as the locale in SQL Server 2000 you get the order:

London
Lladró

because in Traditional Spanish "LL" is considered a letter in its own right that is placed after "L" in the alphabet.

And if the database is localised to non-latin character sets how would the ORDER BY work without locale specific collation rules.

--Colin Mackay--

"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)

Re: Must be a permissions thing, but what?

Jörgen Sigvardsson7-Nov-03 3:11

Jörgen Sigvardsson

7-Nov-03 3:11

Ah yes, but the sort order of things falls outside the scope of abstract datatypes. A date is an abstract datatype, while order is just.. well order! Smile | :)

A chosen few should sit down and rethink SQL...

--
The moment of terror is the beginning of life.

Giles7-Nov-03 3:49

Giles

7-Nov-03 3:49

Its interesting what you say about it being a localisation issue, as thats an ISO format, so it should not matter. Access will identify it as ISO always yyyy-mm-dd, but when you have 01-10-2000 it will then use the local version.

Iteresting that SQL Server tries to read it as yyyy-mm-dd or yyyy-dd-mm depending on where it thinks it is.

"Je pense, donc je mange." - Rene Descartes 1689 - Just before his mother put his tea on the table.

Shameless Plug - Distributed Database Transactions in .NET using COM+

Re: Must be a permissions thing, but what?

Colin Angus Mackay7-Nov-03 5:10

7-Nov-03 5:10

Yes, that's what I thought - All the code uses ISO date strings except on the UI (where normal british dd/mm/yyyy is accepted also). That's why it took so long to discover the fault. And because it only happened to one user I was sure it was some kind of strange permissions thing.

--Colin Mackay--

"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)

date

Anonymous6-Nov-03 2:26

Anonymous

6-Nov-03 2:26

I have 8 fields in my table:
century1,year1,month1,day1,century2,year2,month2,day2
20 01 12 01 Null Null Null Null
19 98 01 01 Null Null Null Null
I need to set century2,century2,year2,month2,day2 to the next day
after century1,year1,month1,day1, so my date will look like this:
century1,year1,month1,day1,century2,year2,month2,day2
20 01 12 01 20 01 12 02
19 98 12 31 19 99 01 01
How do I do this?

Re: date

Colin Angus Mackay6-Nov-03 3:59

6-Nov-03 3:59

Why don't you use a datetime field or smalldatetime field? Then you can use the DATEADD() SQL function. to Add one day.

--Colin Mackay--

"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)

Re: date

Anonymous6-Nov-03 4:36

Anonymous

6-Nov-03 4:36

I can do this, but What should I use do get after to the 4 field again?

Re: date

Colin Angus Mackay6-Nov-03 4:49

6-Nov-03 4:49

CREATE TABLE [dbo].[my_date_table] (
[date1] [smalldatetime],
[date2] [smalldatetime] NULL,
[day1] AS (datepart(day, date1),
[month1] AS (datepart(month, date1),
[year1] AS (datepart(year, date1)/100),
[century1] AS (datepart(year, date1)%100)
[day2] AS (datepart(day, date2),
[month2] AS (datepart(month, date2),
[year2] AS (datepart(year, date2)/100),
[century2] AS (datepart(year, date2)%100)) ON [PRIMARY]

The SQL Server documentation has a lot of good information about manipulating dates. You can find more information there.

--Colin Mackay--

"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)

SQL Statement question.

theJazzyBrain6-Nov-03 1:25

6-Nov-03 1:25

I have a table in SQL Server 2000 as such:

categoryId
categoryParentId
categoryName

I want the categories in hierarchical structure thats why I have the parentId. All top level categories have categoryParentId equal to 0. All other subcategories have a categoryParentId that is equal to the categoryId of their parent category.

I have the following statement:

<br />
SELECT categoryId, categoryParentId, categoryName<br />
FROM categories<br />
WHERE categoryParentId=" & parentId & " OR categoryId=" & parentId<br />

The OR categoryId= will also return the parent category along with its children.

The problem that I am facing is:
I need to modify the statement in such way that it will also provide me with a TRUE or FALSE value for each category that will let me know if the category has children. I cannot change the table at all on the database. I need to do this from within the SQL statement.

Any ideas?

Thank you

theJazzyBrain
Wise is he who asks good questions, not he who gives good answers

Roger Wright6-Nov-03 3:24

Roger Wright

6-Nov-03 3:24

I'm not more than slightly familiar with SQL Server yet, but isn't there a recordcount property you can read? Assuming that each query will return at least one result (the categoryID = parentID case), any value greater than one would indicate that the recordset contains child records.

"Your village called -
They're missing their idiot."

theJazzyBrain6-Nov-03 5:53

6-Nov-03 5:53

I made this and it works but I dont know if there is a better way to do it.

<br />
SELECT a.categoryId, a.categoryParentId, a.categoryName, MAX(ISNULL(b.categoryId,0)) AS categoryHasChildren<br />
FROM categories a LEFT JOIN categories b ON a.categoryId = b.categoryParentId<br />
WHERE a.categoryParentID=2 OR a.categoryID=2<br />
GROUP BY a.categoryId, a.categoryParentId, a.categoryName<br />

theJazzyBrain
Wise is he who asks good questions, not he who gives good answers

Jeff Varszegi6-Nov-03 6:57

Jeff Varszegi

6-Nov-03 6:57

What about using EXISTS with a CASE statement? One of the nice things about EXISTS is that it only walks the index-- it doesn't have to retrieve a result set. In this case, it sounds like you are just wondering whether a child record exists or not.

SQL

SELECT 
   a.categoryId, 
   a.categoryParentId, 
   a.categoryName,
   (
      CASE 
         WHEN EXISTS(SELECT * FROM categories b WHERE b.categoryParentID=a.categoryID)
         THEN 1
         ELSE 0
      END
   ) AS categoryHasChildren
FROM categories a 
WHERE 
   a.categoryParentID=2 
   OR 
   a.categoryID=2

Especially if you are often-reading and seldom-updating the number of children, you may want to consider adding an extra column to each record containing the number of children. Then you could use triggers to update that column on the parent record when you add or remove a child.

Thank you.

Jeff Varszegi

theJazzyBrain6-Nov-03 7:36

6-Nov-03 7:36

This seems to be a good and fast solution.

Thanks for your feedback.
Wink | ;)

theJazzyBrain
Wise is he who asks good questions, not he who gives good answers

Jeff Varszegi6-Nov-03 7:39

Jeff Varszegi

6-Nov-03 7:39

You're welcome!

Jeff Varszegi

theJazzyBrain6-Nov-03 21:48

6-Nov-03 21:48

I might be wrong but my SQL statement does only one SELECT but yours does two SELECT.

I have no idea how SQL server handles that, but is there a posibility that my version is faster than yours?

I am not sure about this...

theJazzyBrain
Wise is he who asks good questions, not he who gives good answers

Jeff Varszegi7-Nov-03 2:43

Jeff Varszegi

7-Nov-03 2:43

Like I said, an EXISTS statement is special. It's not really a SELECT because it doesn't return a result set.

In general, you need to worry about table accesses (which may lock a table), use of indexes, doing extra work etc. when you worry about query performance. In this case, you cannot escape the fact that you have to do a subquery of some type in order to look for child records! My way actually is faster because not only does it not return a result set, but it will stop looking after the first record; your original solution (I'm not slamming it, but just explaining) looks up all the child records by joining this potentially-large table to itself and doing an expensive GROUP operation.

I wasn't sure whether or not to leave in the extra WHERE clause to retrieve the children or not, so I left it in.

I recommend pulling up the query plan for each query so you can see what's going on; this should show you a pretty good breakdown of the work done by the server. After you get done with that, you should realize that the proof is in the pudding; if you're curious about the real-world difference, do a microbenchmark!

This is as simple as making a Transact-SQL script that calls GETDATE(), loops through a certain amount of times calling a script, calls GETDATE() again, and does math to figure out the approximate time for each operation. Test a suitably large number of operations to give decent precision, and also put more than one call in each loop to minimize looping overhead. Lastly, select the results into variables, instead of returning a result set to the browser (due to inefficiencies in Query Analyzer). I usually make a stored procedure to perform a microbenchmark if I'm that curious.

Now that I think of it, one way to really speed up your performance (and provide a nice layer of abstraction from the database in the bargain) is to wrap your query in a stored procedure. This greatly reduces the parse/compile burden on the server.

It's great that you don't just take my word for it! I'm curious to hear of your results.

Regards,

Jeff Varszegi

theJazzyBrain7-Nov-03 4:04