|
Hi,
the first query selects all rows where tblField1 holds a string with a first character equal to a space.
the other two require the entire tblField1 to equal something that probably gets trimmed down to an empty string, hence returning all rows with that field empty. That obviously is quite a different query.
IMO the more elegant query would use
...WHERE tblField1 LIKE ' %'
using a single space and the wildcard character %.
|
|
|
|
|
So the = operator does not check for exact string equality... I knew before that = is case insensitive but to think that it also trims the string!
Is LIKE the more preferable conditional operator then?
Is there a performance difference between = and LIKE?
Say for example WHERE tblField1 = 'something' vs. WHERE tblField1 LIKE 'something'
Rafferty
|
|
|
|
|
1.
I'm not sure, I think it stripped the spaces, got an empty constant, and returned all records.
2.
LIKE makes most sense when it contains a wildcard, and = does not accept wildcards; without wildcards databases can take big advantage of indexing, with wildcards they can't, so most often LIKE is much slower.
|
|
|
|
|
So using LIKE will not use the database indexing even if there are no wildcards in the passed string?
Rafferty
|
|
|
|
|
I don't know what goes on inside a specific database, that is up to the guys implementing all of it; it may or may not be documented, and it might depend on circumstances, such as available memory and disk space. The simple rule is: use LIKE if you need a wildcard, and expect it to be slower.
If you need more, look for the documentation and perform your own experiments (on sufficiently large data sets).
|
|
|
|
|
hm... maybe I'll do some experimenting when I find the time.
Thanks for your help!
Rafferty
|
|
|
|
|
You're welcome.
|
|
|
|
|
Microsoft SQLServer will use indexes (if available) when you use the like statement without wildcards. Even if you do use wildcards, SQL will use indexes if possible. if you compare to a string witch starts with a wildcard, SQL will NOT use indexes.
Wout Louwers
|
|
|
|
|
Wow this is very useful!
Thanks a lot! 
Rafferty
|
|
|
|
|
Something is wrong...in ansi sql databases:
The rows returned in the first query will be equal to or greater than the second.
All of the rows in the second will be included in the first.
The rows returned in the third query will never be in the first or second.
Need to figure this out before thinking about indexes and wildcards.
I would change the second qry as such: WHERE tblField1 = char(32)
just to be sure because-
select ascii(' ') --this equals 9
select ascii(' ') --this equals 32
Create and put data in your Table1 and test if you are not convinced.
|
|
|
|
|
Hi, I am trying to construct a query statement that allows me to retrieve what’s known as “Bullish Divergence” in stock investing signals. It occurs when stock price reaches to a new high level, surpassing a previous price peak, while a certain strength indicator on the same date fails to surpass the strength indicator of the previous peak. This would suggest the stock price is over-valued.
The logic is to first find all the stocks whose prices have reached new highs on 11/24/2009 within the past 10 days, and if their RSI figures fail to be greater than the RSI figures of the previous peaks, then we have a bullish divergence.
I currently have the following query statement, which returns an error message saying “Subquery returned more than 1 value and cannot be used when subquery follows <, >, >=, <=, =”. Could anyone please help me with this?  I would be very thankful as I’ve spent hours trying to get it to work. Thanks!!
SELECT Date, Ticker, Price, RSI
FROM Table
WHERE (Price =
(SELECT MAX(Price) AS Expr1
FROM Table AS t2
WHERE (Table.Ticker = Ticker) AND (Date BETWEEN DATEADD(day, - 10, '11/24/2009')
AND '11/24/2009'))) AND (Date = '11/24/2009') AND
(RSI <
(SELECT RSI
FROM Table AS t3
WHERE (Price =
(SELECT MAX(Price) AS Expr1
FROM Table AS t4
WHERE (t3.Ticker = Ticker) AND (Date BETWEEN DATEADD(day,
-10, '11/23/2009') AND '11/23/2009')))))
modified on Tuesday, December 29, 2009 8:38 PM
|
|
|
|
|
You are trying to get value with subquery which return more then one row and that is why you get error,but to solve this try to use TOP 1 or post your query to see where is problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Unfortunately I was not able to post my query without failing to submit a new thread/post, not sure why. That's why I've post my query in another forum and I've put a link in the first post. Could you please help me out? Thanks!!
|
|
|
|
|
Can anyone please help me? I still can't figure out how to write this....
|
|
|
|
|
Hi
I have problem re-installing SQL 2008 Express. The problem I am getting is even though I can double click on the icon, but when I check the install, I see that there is an error message on the register keys. The error message says that a "prior installations register keys cannot be modified, please check installation to fix error". Unfortunately, I cannot find on MSDN's website on where to find the appropriate fix. Yes, I did have a prior installation, but I removed it Add and Remove Programs, thinking this would give me a clean removal in order to fix another problem.
Can anyone, please advise me on this problem in order that I may install SQL 2008 Express?
Thanks
|
|
|
|
|
I ran into the problem trying to install it the first time; apparently it didn't like having a preious version on the machine (2005). I tried several times a week for a few months to solve it, but never found a solution. After removing the 2005 version, it started whining about corrupted performance counters. And though there is a link to a manual (and extremely tedious) method of cleaning this up for the 2005 version, no such article existed at the time that had a procedure that works for the 2008 version. Hopefully that has been corrected by now, but I doubt it.
I eventually gave up trying, installed it on my server instead, and with help from several kind CPians managed to make the server installation visible to the client.
Another problem that doesn't sound like it applies to this situation exactly, but may bite you later, is that a number of users have found it impossible to install SS 2008 after installing Visual Studio. The reported fix for that was to install the SQL Server first, then install Visual Studio.
If you do find a solution, please write up a procedure and publish it as an article. I'm sure a lot of CP members will find it useful!
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Hi
I have a solution - if you download Windows Installer Clean Up Utility run it this will remove all of the rubbish that SQL puts in.
I did this and ran SQL Express Installer for 2008 and it works.
The solution I found on MSDN forum for 2005 and found it works for 2008.
|
|
|
|
|
Thanks! I found it using Google and grabbed a copy. 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Hi.
I have been working on GIS projects from early 90's.
I have a very big archive of GIS data like photos,ESRI shape files etc.In this moments all this data is organized in folders that keep the name of the project.Our company needs an archive to keep all this data organized in some way they can be searched or used easily.From your experience which is the best way to manage this large amount of information?I repeat that this data are of different formats.
Best regards
|
|
|
|
|
When you talk about searching do you mean to search into the files (such as searching keywords in some of the files)? If so, you need to first create a keyword file for each GIS project. Then use Windows Desktop Search or Google Desktop to index the files. The GIS projects will still be stored in their original folder structures so they can be used easily.
You can also create your own database tables and save the keywords and projects' locations into the database. You may need to develop a certain interface to query the database and launch GIS projects from it.
|
|
|
|
|
I mean not to search by projects but developing a regular archive where u can search the same as you do when u enter at Google maps or maybe by some other criteria I don't know.
I know that my question is some-kind very general but I need some opinions.
Best regards
|
|
|
|
|
I use ArcInfo at work, and I know the nightmare you're talking about. We intially paid a consulting company to map our electrical utility system and provide us with a geodatabase and supporting shapefiles. Since then the tribe has created a GIS office to provide mapping services, and they've been very helpful by providing files to add to our system. But finding those files and organizing them in some logical fashion that makes it easy to locate specific feature sets is a hellish task.
Re-organizing them is out of the question; if I move a file it will break every map ever created that depends on it. There is no tool I know of that will automatically revise .mxd files with new locations for the files that move. The file names that the GIS group uses don't in any way indicate what the files may contain, so a simple search isn't very helpful.
I like the idea of creating an index that can be searched; the keys have to be meaningful, though, and should be accompanied by a detailed description. The keys should not be arranged by project. They have to include keywords that a user would select to describe a particular feature set - "power poles in Section 10" for instance.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Dear all,
I have issue when i creat folder/condition with other foldername with space.
In code bellow is i create foldername that with space
Declare @ComMkDir varchar(100) (eg. C:\ComName\Test Folder
Set @ComMkDir = 'mkDir ' + @ComComp
EXEC master.dbo.xp_cmdshell @ComMkDir
After create is just create "Test" folder.
Thanks for your help...
VB.Net
|
|
|
|
|
This gets the result you want, change to suit:
DECLARE @ComMkDir varchar(100)
DECLARE @mkDir varchar(50) = 'C:\ComName\Test'
DECLARE @ComComp varchar(50) = 'Folder'
SET @ComMkDir = @mkDir + ' ' + @ComComp
Print @ComMkDir
RESULT:
---------------------
C:\ComName\Test Folder
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
modified on Friday, December 25, 2009 9:46 PM
|
|
|
|
|
ohh maybe you don't undestand what i posted.
I want to create folder not just print.
you can test it. It is not acept with space.
VB.Net
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
