Aquesta modalitat de QUERY és la que suporta més casos, es a dir aguanta de tot. El seu rendiment no és tan bo com les versions en CTE però aguanta situacions on les CTE no funcionen bé. Concretament aguanta consultes contra taules amb milions de registres, amb molts JOINs i molts paràmetres de cerca.

Està dividida en varies parts, primer creació de les taules temporals. Es necessiten dues d'aquestes taules temporals, una per filtrar i una altra per ordenar els resultats i extreure una pàgina. Un cop creades la següent part omple la primera taula. Ho fa amb els registres obtinguts de filtrar la taula original. Per fer-ho munta una consulta de forma dinàmica i afegeix al WHERE només els paràmetres que estan informats. D'aquesta manera encara que hi hagi 19 paràmetres de cerca si només s'informa un no hi ha penalització en el rendiment.

Un cop plena la taula amb els resultats del filtre cal ordenar-los. Llavors es fa servir la funció Row_Number, funcionarà correctament per que durant el filtrat ja es fa un DISTINCT. Dins d'aquesta mateixa sentència d'ordenació ja s'agafa la pàgina que toca amb la mida que es passa per paràmetre.

Aquesta sentència és un xic lenta per taules amb milions de registres, triga uns 16 a 26 segons en una taula amb 1.315.000 registres i un servidor que pel que hem van comentar no era gaire bo. Cal fer un mecanisme de cache per reduir aquest temps. Però la CACHE de consultes ho tractarem en el seu post i està relacionada amb la capa de dades (clsDades) en C#.

Com sempre el codi comentat

USE [DI_REAL]
GO
/****** Object:  StoredProcedure [dbo].[DI_EXP_EXPEDIENTARXIU_QUERY]    Script Date: 11/27/2013 17:09:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DI_EXP_EXPEDIENTARXIU_QUERY]
@pPK_ExpedientArxiu int,
 @pTitol varchar(255),
 @pExped_I varchar(50),
 @pAny_Obertura int,
 @pExped_F varchar(50),
 @pFK_LLocDiposit int,
 @pFK_Classificacio int,
 @pFK_Destruccio int,
 @pFK_Procedencia int,
 @pFK_Especifica int,
 @pAny_Tancament int,
 @pFK_Transferencia int,
 @pFK_Estat int,
 @pFK_Arxiu int,
 @pFK_TAAD int,
 @pFK_Uniforme int,
 @pFK_Capsa int,
 @pAny_OberturaMesGran int,
 @pAny_TancamentMenor int,
 @pCapsa varchar(30),
 @pSortColumn NVARCHAR(50) = null,
@pPageSize INT = 10,
@pPageNum INT = 1
AS
BEGIN
SET NOCOUNT ON;

--Creació de les taules temporals, FILTRE
IF OBJECT_ID('tempdb..#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre') IS NOT NULL
	DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
CREATE TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
(
	PK_ExpedientArxiu int,
	FK_Procedencia int,
	FK_Classificacio int,
	FK_Uniforme int,
	FK_Especifica int,
	FK_LLocDiposit int,
	FK_Estat int,
	Titol varchar(255),
	Exped_I varchar(50),
	Exped_F varchar(50),
	Any_Obertura int,
	Any_Tancament int,
	U_Instal_I varchar(30),
	U_Instal_F varchar(30),
	Volum int,
	Adreca varchar(250),
	SigTop_I varchar(30),
	SigTop_F varchar(30),
	Codi_TC nvarchar(5),
	Uniforme_TC nvarchar(5),
	Especifica_TC nvarchar(10),
	Codi_Procedencia nvarchar(5),
	N_NivellP nvarchar(200),
	Diposit nvarchar(200),
	Capsa varchar(30),
	Sigla varchar(6),
	CapsaHist varchar(50),
	Estat varchar(50)
)
--Creació de la taula temporal ORDENACIO
IF OBJECT_ID('tempdb..#TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio') IS NOT NULL
	DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
CREATE TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
(
	NumRegs bigint,
	RowNumber bigint,
	PK_ExpedientArxiu int,
	FK_Procedencia int,
	FK_Classificacio int,
	FK_Uniforme int,
	FK_Especifica int,
	FK_LLocDiposit int,
	FK_Estat int,
	Titol varchar(255),
	Exped_I varchar(50),
	Exped_F varchar(50),
	Any_Obertura int,
	Any_Tancament int,
	U_Instal_I varchar(30),
	U_Instal_F varchar(30),
	Volum int,
	Adreca varchar(250),
	SigTop_I varchar(30),
	SigTop_F varchar(30),
	Codi_TC nvarchar(5),
	Uniforme_TC nvarchar(5),
	Especifica_TC nvarchar(10),
	Codi_Procedencia nvarchar(5),
	N_NivellP nvarchar(200),
	Diposit nvarchar(200),
	Capsa varchar(30),
	Sigla varchar(6),
	CapsaHist varchar(50),
	Estat varchar(50)
)

DECLARE @strSQL nvarchar(MAX)
DECLARE @strPARAMS nvarchar(MAX)

--Construcció del la consulta per omplir la taula filtrada
SET @strSQL = 'INSERT INTO #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
 SELECT DISTINCT dbo.DI_EXP_EXPEDIENTARXIU.PK_ExpedientArxiu,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Procedencia,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Classificacio,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Uniforme,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Especifica,
dbo.DI_EXP_EXPEDIENTARXIU.FK_LLocDiposit,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Estat,
dbo.DI_EXP_EXPEDIENTARXIU.Titol,
dbo.DI_EXP_EXPEDIENTARXIU.Exped_I,
dbo.DI_EXP_EXPEDIENTARXIU.Exped_F,
dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura,
dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament,
dbo.DI_EXP_EXPEDIENTARXIU.U_Instal_I,
dbo.DI_EXP_EXPEDIENTARXIU.U_Instal_F,
dbo.DI_EXP_EXPEDIENTARXIU.Volum,
dbo.DI_EXP_EXPEDIENTARXIU.Adreca,
dbo.DI_EXP_EXPEDIENTARXIU.SigTop_I,
dbo.DI_EXP_EXPEDIENTARXIU.SigTop_F,
dbo.DI_EXP_CLASSIFICACIO.Codi_TC,
dbo.DI_EXP_UNIFORME.Uniforme_TC,
dbo.DI_EXP_ESPECIFICA.Especifica_TC,
dbo.DI_EXP_PROCEDENCIA.Codi_TC AS Codi_Procedencia,
dbo.DI_EXP_PROCEDENCIA.N_NivellP,
dbo.DI_EXP_LLOCDIPOSIT.Nom AS Diposit,
dbo.DI_EXP_CAPSES.Capsa,
dbo.DI_EXP_LLOCDIPOSIT.Sigla,
dbo.DI_EXP_EXPEDIENTARXIU.CapsaHist,
dbo.DI_EXP_ESTAT.Nom AS Estat
 FROM DI_EXP_EXPEDIENTARXIU INNER JOIN DI_EXP_CLASSIFICACIO
  ON DI_EXP_EXPEDIENTARXIU.FK_Classificacio=DI_EXP_CLASSIFICACIO.PK_Classificacio 
  LEFT OUTER JOIN DI_EXP_ESPECIFICA 
  ON DI_EXP_EXPEDIENTARXIU.FK_Especifica=DI_EXP_ESPECIFICA.PK_Especifica 
  LEFT OUTER JOIN DI_EXP_UNIFORME 
  ON DI_EXP_EXPEDIENTARXIU.FK_Uniforme=DI_EXP_UNIFORME.PK_Uniforme 
  INNER JOIN DI_EXP_PROCEDENCIA 
  ON DI_EXP_EXPEDIENTARXIU.FK_Procedencia=DI_EXP_PROCEDENCIA.PK_Procedencia 
  LEFT OUTER JOIN DI_EXP_TAAD_REL 
  ON DI_EXP_CLASSIFICACIO.PK_Classificacio=DI_EXP_TAAD_REL.FK_Classificacio 
  LEFT OUTER JOIN DI_EXP_CAPSA_EXPEDIENT 
  ON DI_EXP_EXPEDIENTARXIU.PK_ExpedientArxiu=DI_EXP_CAPSA_EXPEDIENT.FK_ExpedientArxiu 
  LEFT OUTER JOIN DI_EXP_CAPSES 
  ON DI_EXP_CAPSA_EXPEDIENT.FK_Capsa=DI_EXP_CAPSES.PK_Capsa 
  LEFT OUTER JOIN DI_EXP_SIGTOP ON DI_EXP_CAPSES.PK_Capsa=DI_EXP_SIGTOP.FK_Capsa 
  LEFT OUTER JOIN DI_EXP_LLOCDIPOSIT 
  ON DI_EXP_SIGTOP.FK_LlocDiposit=DI_EXP_LLOCDIPOSIT.PK_LLocDiposit 
  INNER JOIN DI_EXP_ESTAT ON DI_EXP_EXPEDIENTARXIU.FK_Estat=DI_EXP_ESTAT.PK_Estat 
  WHERE 1=1 '

--Construcció del WHERE només els paràmetres amb valor aniran al WHERE.
--Hi un munt!
IF @pPK_ExpedientArxiu IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.PK_ExpedientArxiu=@lPK_ExpedientArxiu'
END

IF @pTitol IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Titol LIKE @lTitol + ''%'''
END

IF @pExped_I IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Exped_I LIKE @lExped_I + ''%'''
END

IF @pAny_Obertura IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura = @lAny_Obertura'
END

IF @pExped_F IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Exped_F LIKE @lExped_F + ''%'''
END

IF @pFK_LLocDiposit IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_SIGTOP.FK_LLocDiposit = @lFK_LLocDiposit'
END

IF @pFK_Classificacio IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Classificacio = @lFK_Classificacio'
END

IF @pFK_Destruccio IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Destruccio = @lFK_Destruccio'
END

IF @pFK_Procedencia IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Procedencia = @lFK_Procedencia'
END

IF @pFK_Especifica IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Especifica = @lFK_Especifica'
END

IF @pAny_Tancament IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament = @lAny_Tancament'
END

IF @pFK_Transferencia IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Transferencia = @lFK_Transferencia'
END

IF @pFK_Estat IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Estat = @lFK_Estat'
END

IF @pFK_Arxiu IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Arxiu = @lFK_Arxiu'
END

IF @pFK_TAAD IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_TAAD_REL.FK_TAAD = @lFK_TAAD'
END

IF @pFK_Uniforme IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Uniforme = @lFK_Uniforme'
END

IF @pFK_Capsa IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_CAPSA_EXPEDIENT.FK_Capsa = @lFK_Capsa'
END

IF @pAny_OberturaMesGran IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura >= @lAny_OberturaMesGran'
END

IF @pAny_TancamentMenor IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament <= @lAny_TancamentMenor'
END

IF @pCapsa IS NOT NULL
BEGIN
	SET @strSQL = @strSQL + ' AND dbo.DI_EXP_CAPSES.Capsa LIKE @lCapsa + ''%'''
END



-- Construcció dels paràmetres que van a la crida del SQL dinàmic
SET @strPARAMS = '@lPK_ExpedientArxiu int, 
@lTitol varchar(255), 
@lExped_I varchar(50), 
@lAny_Obertura int, 
@lExped_F varchar(50), 
@lFK_LLocDiposit int, 
@lFK_Classificacio int, 
@lFK_Destruccio int, 
@lFK_Procedencia int, 
@lFK_Especifica int, 
@lAny_Tancament int, 
@lFK_Transferencia int, 
@lFK_Estat int, 
@lFK_Arxiu int, 
@lFK_TAAD int, 
@lFK_Uniforme int, 
@lFK_Capsa int, 
@lAny_OberturaMesGran int, 
@lAny_TancamentMenor int, 
@lCapsa varchar(30)'



--Execució amb els paràmtres informats
EXECUTE sp_executesql @strSQL, @strPARAMS, 
@pPK_ExpedientArxiu, 
@pTitol, 
@pExped_I, 
@pAny_Obertura, 
@pExped_F, 
@pFK_LLocDiposit, 
@pFK_Classificacio, 
@pFK_Destruccio, 
@pFK_Procedencia, 
@pFK_Especifica, 
@pAny_Tancament, 
@pFK_Transferencia, 
@pFK_Estat, 
@pFK_Arxiu, 
@pFK_TAAD, 
@pFK_Uniforme, 
@pFK_Capsa, 
@pAny_OberturaMesGran, 
@pAny_TancamentMenor, 
@pCapsa;

--Inserim les dades filtrades aplicant un ordre a la taula d'ordenació
INSERT INTO #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
SELECT
COUNT(*) OVER () AS NumRegs,
ROW_NUMBER()
OVER(ORDER BY
CASE WHEN @pSortColumn = 'PK_ExpedientArxiuASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.PK_ExpedientArxiu END ASC,
CASE WHEN @pSortColumn = 'PK_ExpedientArxiuDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.PK_ExpedientArxiu END DESC,
CASE WHEN @pSortColumn = 'FK_ProcedenciaASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Procedencia END ASC,
CASE WHEN @pSortColumn = 'FK_ProcedenciaDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Procedencia END DESC,
CASE WHEN @pSortColumn = 'FK_ClassificacioASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Classificacio END ASC,
CASE WHEN @pSortColumn = 'FK_ClassificacioDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Classificacio END DESC,
CASE WHEN @pSortColumn = 'FK_UniformeASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Uniforme END ASC,
CASE WHEN @pSortColumn = 'FK_UniformeDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Uniforme END DESC,
CASE WHEN @pSortColumn = 'FK_EspecificaASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Especifica END ASC,
CASE WHEN @pSortColumn = 'FK_EspecificaDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Especifica END DESC,
CASE WHEN @pSortColumn = 'TitolASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Titol END ASC,
CASE WHEN @pSortColumn = 'TitolDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Titol END DESC,
CASE WHEN @pSortColumn = 'Exped_IASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Exped_I END ASC,
CASE WHEN @pSortColumn = 'Exped_IDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Exped_I END DESC,
CASE WHEN @pSortColumn = 'Any_OberturaASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Obertura END ASC,
CASE WHEN @pSortColumn = 'Any_OberturaDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Obertura END DESC,
CASE WHEN @pSortColumn = 'Any_TancamentASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Tancament END ASC,
CASE WHEN @pSortColumn = 'Any_TancamentDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Tancament END DESC,
CASE WHEN @pSortColumn = 'U_Instal_IASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_I END ASC,
CASE WHEN @pSortColumn = 'U_Instal_IDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_I END DESC,
CASE WHEN @pSortColumn = 'U_Instal_FASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_F END ASC,
CASE WHEN @pSortColumn = 'U_Instal_FDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_F END DESC,
CASE WHEN @pSortColumn = 'SigTop_IASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_I END ASC,
CASE WHEN @pSortColumn = 'SigTop_IDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_I END DESC,
CASE WHEN @pSortColumn = 'SigTop_FASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_F END ASC,
CASE WHEN @pSortColumn = 'SigTop_FDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_F END DESC,
CASE WHEN @pSortColumn = 'CapsaASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Capsa END ASC,
CASE WHEN @pSortColumn = 'CapsaDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Capsa END DESC,
CASE WHEN @pSortColumn = 'SiglaASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Sigla END ASC,
CASE WHEN @pSortColumn = 'SiglaDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Sigla END DESC,
CASE WHEN @pSortColumn = 'CapsaHistASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.CapsaHist END ASC,
CASE WHEN @pSortColumn = 'CapsaHistDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.CapsaHist END DESC,
CASE WHEN @pSortColumn = 'EstatASC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Estat END ASC,
CASE WHEN @pSortColumn = 'EstatDESC' THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Estat END DESC
)
AS RowNumber,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.PK_ExpedientArxiu,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Procedencia,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Classificacio,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Uniforme,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Especifica,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_LLocDiposit,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Estat,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Titol,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Exped_I,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Exped_F,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Obertura,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Tancament,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_I,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_F,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Volum,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Adreca,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_I,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_F,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Codi_TC,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Uniforme_TC,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Especifica_TC,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Codi_Procedencia,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.N_NivellP,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Diposit,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Capsa,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Sigla,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.CapsaHist,
#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Estat
 FROM #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre

--Obtenim la pàgina desitjada dins dels resultats
SELECT * FROM #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
WHERE RowNumber >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND RowNumber <= @pPageSize * @pPageNum
ORDER BY RowNumber

--Neteja final
DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
END

Aquest és el segon llibre d'aquesta sèrie de l'Imperi Malazà. Tracta la guàrdia Carmesí, que és un exèrcit mercenari que s'oposa a la dominació dels Malazans comandada pel duc Kazz d'Avore.

L'acció es situa després del sisè (6) llibre dels Malazan Book of the Fallen, els Bonehunters. Aquesta guàrdia Carmesí ataca a l'Imperi, posant setge a ciutats. En una d'aquestes ciutats es veuen obligats a alliberar una bèstia perque ataqui als assetjadors, però finalment ataca a tothom.

També hi ha una batalla força èpica on l'Skinner (un membre de la guàrdia Carmesí que després renega i fuig) liquida a l'Emperatriu de Malaz (i ràpidament posen a un altra al càrrec), el Kazz també aconsegueix fugir. Passen altres coses, surten el Kyle i el Greymane, hi ha altres linies argumentals... Per exemple el High-Mage Tayschrenn cau per un vortex que porta al Caos mentre l'intenta tancar, tot lluitant amb un altra mag que l'està obrint.

El llibre m'ha agradat molt.

De forma similar al ROW_NUMBER i fent servir un CTE, el DENSE_RANK fa la mateixa funció. Ens permet numerar els registres per després poder obtenir una pàgina dins dels resultats.

Normalment el DENSE_RANK es fa servir en conjunció amb un DISTINCT o un GROUP BY ja que aquestes funcions d'eliminació de duplicats funcionen bé amb aquest funció. Els duplicats normalment venen donats per que a la consulta hi ha un JOIN. Veiem un codi d'exemple:

ALTER PROCEDURE [dbo].[DI_EXP_CLASSIFICACIO_QUERY]
@pPK_Classificacio int,
 @pNom nvarchar(200),
 @pPK_Arxiu int,
 @pCodi_TC nvarchar(5),
 @pNivell_Codi nvarchar(100),
 @pIdPare int,
 @pFK_TAAD int,
 @pSortColumn NVARCHAR(50) = null,
@pPageSize INT = 10,
@pPageNum INT = 1
AS
BEGIN
WITH PagingCTE-- Creació del CTE
(
Row_ID,
PK_Classificacio,
Codi_TC,
Observacions,
DataAlta,
DataBaixa,
Any_inici,
Any_final,
IdPare,
Nom,
Nivell_Codi)
AS
(
SELECT DISTINCT --Aquesta select porta un DISTINCT per eliminar duplicats
DENSE_RANK()--Fem servir la funció Dense_rank, funciona sintàcticament igual que el Row_Number
OVER(ORDER BY
CASE WHEN @pSortColumn = 'Codi_TCASC' THEN DI_EXP_CLASSIFICACIO.Codi_TC END ASC,
CASE WHEN @pSortColumn = 'Codi_TCDESC' THEN DI_EXP_CLASSIFICACIO.Codi_TC END DESC,
CASE WHEN @pSortColumn = 'NomASC' THEN DI_EXP_CLASSIFICACIO.Nom END ASC,
CASE WHEN @pSortColumn = 'NomDESC' THEN DI_EXP_CLASSIFICACIO.Nom END DESC
)
AS [Row_ID],
DI_EXP_CLASSIFICACIO.PK_Classificacio,
DI_EXP_CLASSIFICACIO.Codi_TC,
DI_EXP_CLASSIFICACIO.Observacions,
DI_EXP_CLASSIFICACIO.DataAlta,
DI_EXP_CLASSIFICACIO.DataBaixa,
DI_EXP_CLASSIFICACIO.Any_inici,
DI_EXP_CLASSIFICACIO.Any_final,
DI_EXP_CLASSIFICACIO.IdPare,
DI_EXP_CLASSIFICACIO.Nom,
DI_EXP_CLASSIFICACIO.Nivell_Codi
 FROM DI_EXP_CLASSIFICACIO --Aqui estan els JOINS que causen duplicats
LEFT OUTER JOIN DI_EXP_CLASSIFICACIO_ARXIU 
ON DI_EXP_CLASSIFICACIO.PK_Classificacio=DI_EXP_CLASSIFICACIO_ARXIU.PK_Classificacio  
LEFT OUTER JOIN DI_EXP_TAAD_REL ON DI_EXP_CLASSIFICACIO.PK_Classificacio=DI_EXP_TAAD_REL.FK_Classificacio 
 WHERE ((@pPK_Classificacio IS NULL) OR (DI_EXP_CLASSIFICACIO.PK_Classificacio=@pPK_Classificacio))
 AND ((@pNom IS NULL) OR (DI_EXP_CLASSIFICACIO.Nom LIKE @pNom + '%'))
 AND ((@pPK_Arxiu IS NULL) OR (DI_EXP_CLASSIFICACIO_ARXIU.PK_Arxiu = @pPK_Arxiu))
 AND ((@pCodi_TC IS NULL) OR (DI_EXP_CLASSIFICACIO.Codi_TC LIKE @pCodi_TC + '%'))
 AND ((@pNivell_Codi IS NULL) OR (DI_EXP_CLASSIFICACIO.Nivell_Codi LIKE @pNivell_Codi + '%'))
 AND ((@pIdPare IS NULL) OR (DI_EXP_CLASSIFICACIO.IdPare = @pIdPare))
 AND ((@pFK_TAAD IS NULL) OR (DI_EXP_TAAD_REL.FK_TAAD = @pFK_TAAD))
)
SELECT
PK_Classificacio,
Codi_TC,
Observacions,
DataAlta,
DataBaixa,
Any_inici,
Any_final,
IdPare,
Nom,
Nivell_Codi FROM PagingCTE
WHERE Row_ID >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND Row_ID <= @pPageSize * @pPageNum
ORDER BY Row_ID
END

Stonewielder és la tercera novel·la d'aquesta serie ambientada a l'Imperi Malazà

Tracta de el ex-HighFist Greymane, que sembla que viu tranquil·lament a Falar on ha muntat una escola d'esgrima juntament amb en Kyle. Però l'Imperi no oblida, l'expedició que ha enviat a Korel ha fracassat i troba que la solució és enviar al Stonewielder que no és un altra que el Greymane, així que el restitueixen al càrrec, li donen un exèrcit i l'envien a Korel.

Allí, a Korel, està el Mur que protegeix les terres de la invasió dels StormRiders, el Mur el defensen la StormWard que perque així ho han promés a la "Blessed Lady" per la que senten un fervor religiós fora de mida.

Al Mur hi serveix gent per obligació, el campió del Mur també, que és Iron Bars, que forma part de la "Guardia Carmesí", hi ha tota una línia argumental destinada al seu rescat.

A Korel hi ha la força expedicionària Malazana que ha renegat de l'Imperi i que lluitarà contra els fidels a l'Imperi del StoneWielder, per poder practicar la màgia dels Warrens sense interferència de la "Blessed Lady" un mag dels malazans renegats ha de fer sacrificis humans. A més a més per liar la troca hi ha una colla d'arreplegats que reneguen de la fe en la "Blessed Lady" i que volen alliberar-se del seu fervor religiós.

El llibre està força bé, i m'ha agradat molt.

Les common table expression són taules temporals residents en memòria enlloc de la tempdb. Això fa que el seu rendiment sigui molt bo.

De fet són tan temporals que només es poden fer servir en la sentència SQL següent a on es declaren. La sintaxi de la declaració és senzilla ja que només es posa el nom de les columnes sense tipus (ja l'agafen del resultat de la consulta en la que es basa). En el següent exemple es pot veure l'ús d'un CTE en combinació amb la funció ROW_NUMBER, ordenació, i posterior obtenció d'una pàgina dels resultats.

ALTER PROCEDURE [dbo].[DI_EXP_ORG_QUERY]
@pPK_Org int, @pSortColumn NVARCHAR(50) = null,
@pPageSize INT = 10,
@pPageNum INT = 1
AS
BEGIN
SET NOCOUNT ON;
--Declaració del CTE
WITH PagingCTE
(--Les columnes van sense tipus, només el nom
Row_ID,
PK_Org,
Nom,
Descripcio)
AS
(--La consulta en la que es basa va entre parentesis
SELECT
ROW_NUMBER()--Aqui es fa servir la funció Row_Number, i s'especifica aqui el ORDER BY
OVER(ORDER BY
CASE WHEN @pSortColumn = 'PK_OrgASC' THEN DI_EXP_ORG.PK_Org END ASC,
CASE WHEN @pSortColumn = 'PK_OrgDESC' THEN DI_EXP_ORG.PK_Org END DESC,
CASE WHEN @pSortColumn = 'NomASC' THEN DI_EXP_ORG.Nom END ASC,
CASE WHEN @pSortColumn = 'NomDESC' THEN DI_EXP_ORG.Nom END DESC,
CASE WHEN @pSortColumn = 'DescripcioASC' THEN DI_EXP_ORG.Descripcio END ASC,
CASE WHEN @pSortColumn = 'DescripcioDESC' THEN DI_EXP_ORG.Descripcio END DESC
)
AS [Row_ID],
DI_EXP_ORG.PK_Org,
DI_EXP_ORG.Nom,
DI_EXP_ORG.Descripcio
 FROM DI_EXP_ORG WHERE ((@pPK_Org IS NULL) OR (DI_EXP_ORG.PK_Org=@pPK_Org))
)--Aqui acaba el CTE, es fa servir just en la consulta següent
SELECT
(SELECT COUNT(*) FROM PagingCTE) AS NumRegs,
PK_Org,
Nom,
Descripcio FROM PagingCTE
--En el CTE ja s'ha fet el filtrat de registres, en el seu WHERE,
--En aquest altra WHERE el que es fa és obtenir un pàgina dels registres (la mida de la pag. ve donada per el pagesize)
WHERE Row_ID >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND Row_ID <= @pPageSize * @pPageNum
 ORDER BY Row_ID
END

Com es pot veure la consulta és senzilla, no té cap JOIN, la taula té pocs registres i el WHERE és limitat. Per tant es pot fer servir la combinació CTE-ROW_NUMBER sense problema.

Aquestes funcions d'SQL Server serveixen per numerar registres per tal de posteriorment extreure'ls paginats. Funcionen de forma similar, però tenen una subtil diferència que fa que a vegades haguem de fer servir una o l'altra. El primer de tot és entendre el seu funcionament, comencem per la Row_Number.

El Row_Number numera els registres consecutivament, 1, 2, 3, 4... Si els registres són iguals (mateixos valors) el seu Row_Number és diferent. Es poden donar registres amb els mateixos valors si, per exemple, la consulta té un JOIN. Llavors el problema es dóna quan es fa un GROUP BY o un DISTINCT per eliminar els duplicats, com el ROW_NUMBER és diferent per tots els registres aleshores el GROUP BY/DISTINCT no elimina duplicats.

Per altra banda tenim el Dense_Rank. Requereix més càlculs (és més costos) ja que només assigna un número nou a un registre si té valors diferents. Per tant encara que hagi duplicats, al tenir el mateix valor de Dense_Rank un GROUP BY/DISTINCT posterior eliminarà aquests registres sense problema. També té un altra problema (al menys el tenia quan el vaig fer servir) que amb taules amb molts registres (>500.000) no calculava bé el seu valor.

Ja estic cansat de que el Windows m'avisi de que obrir uns fitxers ubicats en una unitat de xarxa és algo perillós. Es a dir aquest missatge:

La solució que m'ha funcionat en Windows 7 i 8.1 és la següent:

1. Clic Inici -> Panell de control -> Opcions d'Internet
2. Anar al tab de seguretat
3. Clic a Intranet Local
4. Clic al botó de Llocs
5. Clic al botó d'Avançat
6. Posar l'adreça IP del servidor on està la unitat compartida i clic a Afegir
7. Aceptar, tancar, etc...

És curiós com els SSD a pesar de patir desgast durant les escriptures aguanten un munt, cosa que al meu parer els fa més segurs que els discs durs magnètics.

Per veure que tal aguanten a TechReport han fet una anàlisis de diferents models de SSD on hi graven un munt de dades no comprimibles per després anar monitoritzant les dades SMART.

Els SSDs van reallotjant sectors desgastats sobre de nous i tenen algun que altra daltabaix. Els TLC de Samsung són els que moriran abans, els PRO darrera, els intel tampoc surten molt ben parats. De moment els que més aguanten són els Kingston i Corsair. En la següent taula es mostra el resultat del porcentatge de salut restat a la unitat després d'escriure 100, 200, 300TB d'informació.

Aquest post forma part d'un conjunt de posts que defineixen la infraestructura SQLServer de la classe d'accés a dades.

Fins ara totes les stored procedures han estat senzilles. Amb el Query arriba la complexitat. Aquesta stored és l'encarregada d'obtenir N registres, corresponents a una pàgina concreta, i quan la taula està ordenada d'una determinada manera (per un camp concret i amb un sentit (Asc/Desc).

Com que hi ha varies operacions es poden dur a terme de diferents formes. Aquestes diferents formes de fer el mateix depèn del tipus de consulta (amb o sense JOIN), del volum de dades (hi ha funcions d'SQLServer com el DenseRank que té un número de files a partir de la que no funciona bé), de la quantitat de paràmetres del WHERE. Millor una imatge.

Entrarem amb més detall en els diferents tipus de la stored QUERY en altres posts: CTE Row_Number, CTE Dense_Rank, Taula temporal/subquery, Taula temporal dinàmic.

Aquest post forma part d'un conjunt de posts que defineixen la infraestructura SQLServer de la classe d'accés a dades.

Per fer el CRUD sencer aquesta també és necessària. El DEL fa servir els mateixos paràmetres de sel·lecció de registres que el GET, sempre i quan pertanyin a la taula sobre la que es fa l'operació. D'aquesta manera es cobreixen més operacions que la simple eliminació per PK (encara que també es fa servir).

I com sempre l'exemple del codi:

DELETE FROM DI_EXP_DESTRUCCIO
 WHERE
 ((@pPK_Destruccio IS NULL) OR (PK_Destruccio=@pPK_Destruccio)) AND
 ((@pFK_TAAD IS NULL) OR (DI_EXP_DESTRUCCIO.FK_TAAD = @pFK_TAAD))
 AND ((@pFK_Estat IS NULL) OR (DI_EXP_DESTRUCCIO.FK_Estat = @pFK_Estat))
 AND ((@pDataInici IS NULL) OR (DI_EXP_DESTRUCCIO.Data_Destruccio BETWEEN @pDataInici AND @pDataFi))
 AND ((@pFK_Arxiu IS NULL) OR (DI_EXP_DESTRUCCIO.FK_Arxiu = @pFK_Arxiu))