How do I make an offset in this SQL Server 2000 query?

Question

I wanna do an offset like: from 0 to 10000 records, from 10000 to 20000 records and so on. How do I modify this query to add an offset? Also, how can I improve this query for performance?

SELECT
  CASE
    WHEN c.DataHoraUltimaAtualizacaoILR >= e.DataHoraUltimaAtualizacaoILR AND c.DataHoraUltimaAtualizacaoILR >= t.DataHoraUltimaAtualizacaoILR THEN c.DataHoraUltimaAtualizacaoILR
    WHEN e.DataHoraUltimaAtualizacaoILR >= c.DataHoraUltimaAtualizacaoILR AND e.DataHoraUltimaAtualizacaoILR >= t.DataHoraUltimaAtualizacaoILR THEN e.DataHoraUltimaAtualizacaoILR
    WHEN t.DataHoraUltimaAtualizacaoILR >= c.DataHoraUltimaAtualizacaoILR AND t.DataHoraUltimaAtualizacaoILR >= e.DataHoraUltimaAtualizacaoILR THEN t.DataHoraUltimaAtualizacaoILR
    ELSE c.DataHoraUltimaAtualizacaoILR
  END AS 'updated_at',
  p.Email,
  c.ID_Cliente,
  p.Nome, 
  p.DataHoraCadastro, 
  p.Sexo, 
  p.EstadoCivil, 
  p.DataNascimento, 
  getdate() as [today],
  datediff (yy,p.DataNascimento,getdate()) as 'Idade',
  datepart(month,p.DataNascimento) as 'MesAniversario',
  e.Bairro,
  e.Cidade, 
  e.UF, 
  c.CodLoja as codloja_cadastro,
  t.DDD,
  t.Numero
FROM
  PessoaFisica p
LEFT JOIN
  Cliente c ON (c.ID_Pessoa = p.ID_PessoaFisica)
LEFT JOIN 
  Loja l ON (CAST(l.CodLoja AS integer) = CAST(c.CodLoja AS integer))
LEFT JOIN
  PessoaEndereco pe ON (pe.ID_Pessoa = p.ID_PessoaFisica)
LEFT JOIN 
  Endereco e ON (e.ID_Endereco = pe.ID_Endereco)
LEFT JOIN 
  PessoaTelefone pt ON (pt.ID_Pessoa = p.ID_PessoaFisica)
LEFT JOIN 
  Telefone t ON (t.ID_Telefone = pt.ID_Telefone)
WHERE 
   p.Email IS NOT NULL 
   AND p.Email <> '' 
   --and p.Email = '[email protected]'
GROUP BY 
   p.Email, c.ID_Cliente, p.Nome, p.EstadoCivil, p.DataHoraCadastro, 
   c.CodLoja, p.Sexo, e.Bairro, p.DataNascimento, e.Cidade, e.UF, 
   t.DDD, t.Numero, c.DataHoraUltimaAtualizacaoILR, e.DataHoraUltimaAtualizacaoILR, 
   t.DataHoraUltimaAtualizacaoILR
ORDER BY 
   updated_at DESC

Answer 1

Overall Process

If you have access to a more modern SQL Server version, then you could setup a process to copy the raw data to a new database on a daily basis. This might initially be an exact copy of the source database, just for staging the data. Then build an transformation process, using stored procedures or perhaps SSIS for high performance. That process would transform your data into your desired end state, and load it into the final database.

The copy process could be replication, but if your staging database is SQL Server 2005 or above, then you could also build a simple SSIS job to perform the copy. Run that job in a schedule task (SQL Agent) on a daily basis. You could combine the two - load data, then transform - but if using SSIS, then I recommend keeping these as separate SSIS packages, which will help with debugging problems. In the scheduled task you could run the two packages back-to-back.

---

Performance

You'll need good indexing on the table, but indexing alone is not sufficient. Casting CodLoja as an integer will prevent you from using indexes on that field. If you need to store those as strings for some other reason, then consider adding calculated columns,

ALTER TABLE xyz Add CodLojaAsInt as (CAST(CodLoja as int))

Then place an index on that new calculated column. The problem is that any function call in a ON or WHERE clause will cause SQL Server to scan the entire and convert every single row, instead of peaking into an index.

Answer 2

After searching and looking over my problem again, @sfuqua helped me with this solution. Basically I'll create some more organized tables in my local DB and get all the abstract/ugly data from the remote DB and process it locally to new tables.

I'm gonna use Elasticsearch to speed up the indexing and queries.

Answer 3

It sounds like you're trying to emulate MySQL's SELECT ... LIMIT X,Y feature. SQL Server doesn't have that. In SQL Server 2005+, you can use ROW_NUMBER() in a subquery. Since you're on 2000, however, you're going to have to do it one of the hard ways.

The way I've always done it is like this:

SELECT ... FROM Table WHERE PK IN 
    (SELECT TOP @PageSize PK FROM Table WHERE PK NOT IN
        (SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)
    ORDER BY SortColumn)
ORDER BY SortColumn

Although I recommend rewriting it to use EXISTS instead of IN and seeing which works better. You'll have to use EXISTS if you have compound primary keys.

That code and the other solutions are here.