I have a simple table containing adresses.
A sample view of the table is
id name city
----------
100 Meier New York
101 Meier Tokyo
110 Olson Amsterdam
110 Olson Dublin
111 Paul Berlin
...
The view is ordered by (name,city)
Now my problem:
I want to see only the second half of the view
starting from "Olson" in "Dublin"
Using the constraint
where (name>="Olson") and (city>="Dublin")
does not the right thing. It eliminates i.e. row 111.
The only idea I have is to concatenate the fields to
simulate the compound index to be able to do
where name+"~"+city>="Olson~Dublin"
not to forget to struggle with null fields to get
the right results. This slows down the simple query
dramatically.
This seems to be a complicated solution for a simple
problem, given the fact, that the index is already
available at the server.
Does anyone has an idea or suggestion?
-Hubert> This seems to be a complicated solution for a simple
> problem, given the fact, that the index is already
> available at the server.
What index? Please post table DDL and INSERT statements.
> The view is ordered by (name,city)
Is name/city unique? If not, you should add ID to the ORDER BY and your
criteria so that you can skip rows with identical values in those columns.
> not to forget to struggle with null fields to get
> the right results. This slows down the simple query
> dramatically.
The example below should perform well with proper indexing. The column
value concatenation method prevents the efficient use of indexes.
CREATE TABLE Addresses
(
ID int NOT NULL
CONSTRAINT PK_Addresses PRIMARY KEY ,
Name varchar(20) NULL,
City varchar(20) NULL
)
ALTER TABLE Addresses
ADD CONSTRAINT UQ_Addresses UNIQUE (Name, City)
INSERT INTO Addresses SELECT 100, 'Meier', 'New York'
UNION ALL SELECT 101, 'Meier', 'Tokyo'
UNION ALL SELECT 110, 'Olson', 'Amsterdam'
UNION ALL SELECT 110, 'Olson', 'Dublin'
UNION ALL SELECT 111, 'Paul', 'Berlin'
UNION ALL SELECT 200, NULL, NULL
UNION ALL SELECT 201, 'n', NULL
UNION ALL SELECT 202, NULL, 'b'
GO
DECLARE @.Name varchar(20)
DECLARE @.City varchar(20)
SET @.Name = 'Olson'
SET @.City = 'Dublin'
SELECT ID, Name, Address
FROM Addresses
WHERE
(Name > @.Name OR (@.Name IS NULL AND Name IS NOT NULL)) OR
((Name = @.Name OR (Name IS NULL AND @.Name IS NULL)) AND
(City >= @.City OR
(@.City IS NULL AND City IS NULL) OR
(@.City IS NULL AND City IS NOT NULL)))
ORDER BY Name, City
--
Hope this helps.
Dan Guzman
SQL Server MVP
<hubmei75@.web.de> wrote in message
news:1137254101.865303.137270@.g44g2000cwa.googlegr oups.com...
> Hello,
> I have a simple table containing adresses.
> A sample view of the table is
> id name city
> ----------
> 100 Meier New York
> 101 Meier Tokyo
> 110 Olson Amsterdam
> 110 Olson Dublin
> 111 Paul Berlin
> ...
> The view is ordered by (name,city)
> Now my problem:
> I want to see only the second half of the view
> starting from "Olson" in "Dublin"
> Using the constraint
> where (name>="Olson") and (city>="Dublin")
> does not the right thing. It eliminates i.e. row 111.
> The only idea I have is to concatenate the fields to
> simulate the compound index to be able to do
> where name+"~"+city>="Olson~Dublin"
> not to forget to struggle with null fields to get
> the right results. This slows down the simple query
> dramatically.
> This seems to be a complicated solution for a simple
> problem, given the fact, that the index is already
> available at the server.
> Does anyone has an idea or suggestion?
> -Hubert|||(comp.databases.mysql removed from groups list)
On 14 Jan 2006 07:55:01 -0800, hubmei75@.web.de wrote:
(snip)
>The only idea I have is to concatenate the fields to
>simulate the compound index to be able to do
> where name+"~"+city>="Olson~Dublin"
>not to forget to struggle with null fields to get
>the right results. This slows down the simple query
>dramatically.
>This seems to be a complicated solution for a simple
>problem, given the fact, that the index is already
>available at the server.
>Does anyone has an idea or suggestion?
Hi Hubert,
Here's a version that makes optimal use of existing indexes on name or
name + city columns:
WHERE name >= 'Olson'
AND ( name > 'Olson OR city > 'Dublin' )
--
Hugo Kornelis, SQL Server MVP|||hubmei75@.web.de (hubmei75@.web.de) writes:
> The view is ordered by (name,city)
Views are by definition unordered. Yes, you can add TOP 100 PERCENT
and ORDER BY, but it does not really mean anything.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hubmei75@.web.de wrote:
> Hello,
> I have a simple table containing adresses.
> A sample view of the table is
> id name city
> ----------
> 100 Meier New York
> 101 Meier Tokyo
> 110 Olson Amsterdam
> 110 Olson Dublin
> 111 Paul Berlin
> ...
> The view is ordered by (name,city)
> Now my problem:
> I want to see only the second half of the view
> starting from "Olson" in "Dublin"
> Using the constraint
> where (name>="Olson") and (city>="Dublin")
> does not the right thing. It eliminates i.e. row 111.
> The only idea I have is to concatenate the fields to
> simulate the compound index to be able to do
> where name+"~"+city>="Olson~Dublin"
> not to forget to struggle with null fields to get
> the right results. This slows down the simple query
> dramatically.
> This seems to be a complicated solution for a simple
> problem, given the fact, that the index is already
> available at the server.
> Does anyone has an idea or suggestion?
> -Hubert
WHERE (name = 'Olson' AND city >= 'Dublin') OR name > 'Olson'
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@.attglobal.net
==================|||Thanks to all! You helped me very much.
Sometimes I am thinking too complicated
to see the straight solutions :)
-Hubert
