|
|
Posted by Dan Guzman on 01/14/06 19:15
> 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.googlegroups.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
>
Navigation:
[Reply to this message]
|