Every time that I write a CREATE TABLE SQL command and there is an index in the sentece the same doubt assault me. What is the difference between a clustered index and a non-clustered one?

To understand this first I have to explain what is an index. Indexs are database objects that helps to find records inside the database. As the data in the database tables grows a mechanism is needed to improve the information search performance. Here is when the indexs comes to help in searchs. Indexs have an structure like a tree, instead of doing a sequential read of the table we can jump between nodes in the index to arrive to the desired data. This way there are less reads to disk and the result is obtained faster.

Now that we know what is an index is time to see the diference between them. Let's start with a clustered one. In a clustered index data is stored in the leaves of the tree, so when we arrive at a leave that we are searching for data is already there, available to collect.

On the other hand non-clustered indexs doesn't have the data on the leaves, they have a pointer to the data, then it is necessary to do a bookmark lookup to actually get the data.

Obviously non-clustered indexs are slower than clustered ones, they have to perform an extra step. But in a table you can only have one clustered index (there's only one way to store data) and you can have multiple non-clustered indexs. From SQL Server 2005 onwards theres the possibility to stored certain data columns in a non-clustered indexs avoiding the bookmark lookup, there's an article on the TechNet that explains that.

There are some points to consider regarding indexs:

• Identity columns that are primary key (PK) usually belongs to the clustered index of the table. This is done to avoid page split.
• If certain searches repeat often then is recommended to create a non-clustered index. If the result columns are just a few they can be included in the index.
• If searches are done by multiple columns then a non-clustered index is recommended.

This post is part of the QUERY series.

This type of QUERY is the one that supports more use cases. It's performance is not as good as it's CTE counterparts but allows some use cases that CTE versions don't allow. It supports its use against tables with millions of records and with queries that has JOINs and a ton of filtering parameters.

It is divided in three parts. The first part is the temporal table creation. Two temporal tables are needed, one for filtering and the other for ordering. The ordering table is also used to retrieve a page of rows. Once created the temporal tables the second part fills the first temporal table with records that comply certain criteria (filtering them). To do this, a dynamic query is built in a string, in the WHERE part only are informed the filter parameters that actually have a value. This way although 19 filter parameters are used there's no performance penalty.

Once this first table is full of records is time to order them, so a ROW_NUMBER function is used in the third and last part of the query. It works well because in the first table a DISTINCT is used to avoid duplicate rows. In the WHERE part of this second temporal table a page of rows is obtained.

A stored procedure this long is a bit slow with tables that have 1.6 million of rows, and in a not very good server it can last from 16 to 26 seconds. In order to reduce this time a cache is needed but this will be explained in the C# post of clsData.

As usual the full code of the SP:

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;

--Creation of the first table, used for filtering
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)
)
--Creation of the second temporal table, the one used in sorting
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)

--First query to fill the table with filtered results
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 '

--Dynamic WHERE
--There's a ton of possible filters
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



-- Construction of the parameters
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)'



--Execution with parameter informed
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;

--Insert filtered data applying a parametrized sort
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

--Get the desired page
SELECT * FROM #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
WHERE RowNumber >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND RowNumber <= @pPageSize * @pPageNum
ORDER BY RowNumber

--Final cleaning
DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
DROP TABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
END

This post is part of the QUERY series.

In a similar way that the ROW_NUMBER version works, DENSE_RANK allows us to numerate rows and then obtain a page of rows.

Usually DENSE_RANK is used with a DISTINTC or a GROUP BY. Those functions filter duplicate rows and play nice with DENSE_RANK. Duplicate rows appear in a result table because a JOIN is used. See it in the next example:

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-- CTE creation
(
Row_ID,
PK_Classificacio,
Codi_TC,
Observacions,
DataAlta,
DataBaixa,
Any_inici,
Any_final,
IdPare,
Nom,
Nivell_Codi)
AS
(
SELECT DISTINCT --This SELECT uses a DISTINCT to filter out duplicates
DENSE_RANK()--Here DENSE_RANK is used, sintactically is identical to 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 --Here are the JOINs that generate duplicates
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

This post is part of the QUERY series.

Common table expressions or CTE for short are like temporal tables that are kept in RAM in order to access them very fast. But to not saturate the RAM they are so short lived that they can be only used in the next sentence that generated the CTE.

The sintax of a CTE is simple, they are declared without data types because they get the type from the table that is used in the definition. In the next example we can see a CTE in combination with ROW_NUMBER, both used to get a page of records from a table ordered and filtered.

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;
--CTE declaration
WITH PagingCTE
(--Columns are only defined by name
Row_ID,
PK_Org,
Nom,
Descripcio)
AS
(--The query is enclosed in parenthesis
SELECT
ROW_NUMBER()--Here is used the Row_Number function, and the 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))
)--Here ends the CTE, ready to use in the following QUERY
SELECT
(SELECT COUNT(*) FROM PagingCTE) AS NumRegs,
PK_Org,
Nom,
Descripcio FROM PagingCTE
--In the CTE WHERE part records are filtered.
--In this WHERE a page is selected, calculated from parameters
WHERE Row_ID >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND Row_ID <= @pPageSize * @pPageNum
 ORDER BY Row_ID
END

As you can see the QUERY is pretty straight forward, there are no JOIN, the number of records is low and the filter parameters are just a few. So we can use CTE - ROW_NUMBER to get an excelent performance.

This two SQLServer functions are used to number datatable rows in order to obtain a page of these rows. Both functions are very simmilar but they have a key difference that obligues us to use one or the other.

Row_number returns the sequential number of a row within a partition of a result set, starting at 1 for the first row.

On the other hand Dense_rank returns the number of different rows in a table. Or in other words, returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

Dense_rank function must be used when there's a JOIN and a GROUP BY / DISTINCT in a query. A JOIN can cause the apperance of duplicate rows in a datatable, and then when a GROUP BY / DISTINCT is performed over that datable if Row_number is used the duplicates will not be removed because all rows are different (have a different row_number).

Dense_rank has it's own drawbacks. It's more resource intensive than row_number and when a certain number of rows is reached (> 500.000) it stops working properly (at least in SQLServer 2008 R2). So with this in mind we will need to adapt to different scenarios: absence/presence of JOINs and number of rows, in order to choose one technique or other one.

This post is part of a series dedicated to database access. Until now all stored procedures (SP) have been pretty easy to understand and to build. This SP is the complex one of the lot. Its tasks are to obtain a page of records (rows) from a datatable ordered by a column (ascending or descending), and filter them by some criteria. All of these tasks have to be done maintaining good or decent performance.

As you can see in the SP QUERY there are different operations hence these operations can be done in different ways. Choosing one way or another one depends on the query type (with or without JOINS), the number of rows in the datatable (hundreds, thousands, millions...) and the number of parameters that can filter the datable (you can filter by one, two (not many) or you can filter a lot (by nineteen columns for example). Depending on these criteria we have to use one kind of SP QUERY or another.

Maybe this is a little confusing, but in future posts I will deal with these different QUERY methods, their meanings and their motivations: CTE RowNumber, CTE DenseRank, Temporal Table with dynamic WHERE.

This post is part of a series dedicated to database access. This is the stored procedure (SP) that deletes rows in the database.

DEL SP has the same parameters as the GET SP, with the restriction that the parameters have to belong to the affected datatable. With these parameters the DEL operation is more flexible because it can affect not only the row affected by the PK but other ones.

As usual, a code example:

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))

This post is part of a series dedicated to database access. This is the stored procedure (SP) that updates a row in the database.

It's fairly straightforward to do this SP. It receives the value of all columns of the datatable that it has to update. The program knows which ones are pure data and which column is the primary key (PK). The PK has to go in the WHERE part of the SQL sentence in order to affect only one row of the datatable. In this SP the PK is always an input parameter, in the INS SP it was an output parameter.

Code looks like this:

ALTER PROCEDURE [dbo].[DI_EXP_DESTRUCCIO_UPD]
 @pPK_Destruccio int,
 @pFK_TAAD int,
 @pFK_Arxiu int,
 @pAny_obertura int,
 @pAny_tancament int,
 @pMetres decimal(5, 1),
 @pData_Destruccio datetime,
 @pObservacions varchar(2000),
 @pbCataleg nchar(20),
 @pSerie_documental varchar(500),
 @pMK_UsuariAlta varchar(50),
 @pMK_DataAlta datetime,
 @pMK_UsuariModif varchar(50),
 @pMK_DataModif datetime, @pFK_Estat int,
 @pNumExpedients int,
 @pFK_Classificacio int,
 @pFK_Especifica int
 AS BEGIN
 UPDATE DI_EXP_DESTRUCCIO SET
--All datatable columns except the PK
 FK_TAAD=@pFK_TAAD, FK_Arxiu=@pFK_Arxiu,
 Any_obertura=@pAny_obertura, Any_tancament=@pAny_tancament,
 Metres=@pMetres, Data_Destruccio=@pData_Destruccio,
 Observacions=@pObservacions, bCataleg=@pbCataleg,
 Serie_documental=@pSerie_documental,
 MK_UsuariAlta=@pMK_UsuariAlta, MK_DataAlta=@pMK_DataAlta,
 MK_UsuariModif=@pMK_UsuariModif, MK_DataModif=@pMK_DataModif,
 FK_Estat=@pFK_Estat, NumExpedients=@pNumExpedients,
 FK_Classificacio=@pFK_Classificacio, FK_Especifica=@pFK_Especifica
--Here is the PK
 WHERE (PK_Destruccio=@pPK_Destruccio)
END

This post is part of a series dedicated to database access. This is the stored procedure that creates new rows in the database.

It is very simple; it has all the columns of the table to which it belongs as input parameters. If the primary key is an identity it is converted to an output parameter in order to send the new created key to the calling layer for future use.

In order to retrieve the value of the primary key I use the IDENT_CURRENT function.

Here is an example of how it works:

ALTER PROCEDURE [dbo].[DI_EXP_DESTRUCCIO_INS]
--PK as OUTPUT
@pPK_Destruccio int OUTPUT,
@pFK_TAAD int, 
@pFK_Arxiu int, 
@pAny_obertura int, 
@pAny_tancament int, 
@pMetres decimal(5, 1), @pData_Destruccio datetime, 
@pObservacions varchar(2000), 
@pbCataleg nchar(20), 
@pSerie_documental varchar(500), 
@pMK_UsuariAlta varchar(50), 
@pMK_DataAlta datetime, @pMK_UsuariModif varchar(50), 
@pMK_DataModif datetime, @pFK_Estat int, 
@pNumExpedients int, 
@pFK_Classificacio int, 
@pFK_Especifica int AS
BEGIN
 INSERT INTO DI_EXP_DESTRUCCIO (
 FK_TAAD, 
 FK_Arxiu, 
 Any_obertura, 
 Any_tancament, 
 Metres, 
 Data_Destruccio, 
 Observacions, 
 bCataleg, 
 Serie_documental, 
 MK_UsuariAlta, 
 MK_DataAlta, 
 MK_UsuariModif, 
 MK_DataModif, 
 FK_Estat, 
 NumExpedients, 
 FK_Classificacio, 
 FK_Especifica
 ) VALUES (
--PK is not included
 @pFK_TAAD, 
 @pFK_Arxiu, 
 @pAny_obertura, 
 @pAny_tancament, 
 @pMetres, 
 @pData_Destruccio, 
 @pObservacions, 
 @pbCataleg, 
 @pSerie_documental, 
 @pMK_UsuariAlta, 
 @pMK_DataAlta, 
 @pMK_UsuariModif, 
 @pMK_DataModif, 
 @pFK_Estat, 
 @pNumExpedients, 
 @pFK_Classificacio, 
 @pFK_Especifica
 );
--PK retrieval
SET @pPK_Destruccio = IDENT_CURRENT('DI_EXP_DESTRUCCIO')
END

This post is part of a series dedicated to database access. Every table has different stored procedures (SP) to access or modify it. One of these SP is the GET SP, so we will have the SP called TABLENAME_GET. This SP performs SELECT operations against the datable, so it GETS records.

The GET SP usually has the primary key (PK) as an input parameter. Optionally it has more parameters depending of the bussiness logic. For each extra parameter an operator is selected, it can be '=', '>=', etc... and in nvarchar/nchar columns you can choose LIKE operator. The interesting part of building a SP with parameters is that you can choose to inform them or not, in other words, all parameters are optional. If you don't inform any parameter you'll get all records in the datatable. Doing a ton of parameter checking has its impact on performance, so when you reach 8-10 optional parameter treshold then it is advisable to switch to dynamic SQL. The dynamic SQL building uses the 'sp_executesql' instead of EXEC. It's better the sp_executesql because it will cache the execution plan and will check the parameters type.

Here is an example of a simple SQL GET SP.

ALTER PROCEDURE [dbo].[T_MUNICIPI_GET] @pIDMUNICIPI bigint , @pIDCOMARCA int, @pNOMMUNICIPI nvarchar(50) AS BEGIN
 SELECT T_MUNICIPI.IDMUNICIPI,
T_MUNICIPI.IDCOMARCA,
T_MUNICIPI.IDDIPUTACIO,
T_MUNICIPI.NOMMUNICIPI,
T_MUNICIPI.CODMUNICIPI,
T_MUNICIPI.CODPOSTAL,
T_MUNICIPI.NOMCURTMUN,
T_MUNICIPI.NOMALT1MUN,
T_MUNICIPI.NOMALT2MUN,
T_MUNICIPI.NOMALT3MUN,
T_MUNICIPI.CAPITALCOM,
T_MUNICIPI.CAPITALDIP,
T_MUNICIPI.CODIMUN6,
T_COMARCA.NOMCOMARCA
 FROM T_MUNICIPI  LEFT OUTER JOIN T_COMARCA ON T_MUNICIPI.IDCOMARCA=T_COMARCA.IDCOMARCA
 WHERE ((@pIDMUNICIPI IS NULL) OR (IDMUNICIPI=@pIDMUNICIPI))
 AND ((@pIDCOMARCA IS NULL) OR (T_COMARCA.IDCOMARCA=@pIDCOMARCA))
 AND ((@pNOMMUNICIPI IS NULL) OR (T_MUNICIPI.NOMMUNICIPI LIKE @pNOMMUNICIPI + '%'))
 
ORDER BY T_MUNICIPI.NOMMUNICIPI
 
END

And here an example of a complex one.

ALTER PROCEDURE [dbo].[DI_EXP_EXPEDIENTARXIU_GET] 
--This SELECT has a ton of parameters to use as a filter, so its
build dynamically
@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) AS
 BEGIN
SET NOCOUNT ON
DECLARE @strSQL nvarchar(MAX)
DECLARE @strPARAMS nvarchar(MAX)

SET @strSQL = '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_Transferencia,
dbo.DI_EXP_EXPEDIENTARXIU.FK_LLocDiposit,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Municipi,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Estat,
dbo.DI_EXP_EXPEDIENTARXIU.FK_TransferenciaAH,
dbo.DI_EXP_EXPEDIENTARXIU.Titol,
dbo.DI_EXP_EXPEDIENTARXIU.Exped_I,
dbo.DI_EXP_EXPEDIENTARXIU.Exped_F,
dbo.DI_EXP_EXPEDIENTARXIU.Dia_Obertura,
dbo.DI_EXP_EXPEDIENTARXIU.Mes_Obertura,
dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura,
dbo.DI_EXP_EXPEDIENTARXIU.Dia_Tancament,
dbo.DI_EXP_EXPEDIENTARXIU.Mes_Tancament,
dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament,
dbo.DI_EXP_EXPEDIENTARXIU.U_Instal_I,
dbo.DI_EXP_EXPEDIENTARXIU.U_Instal_F,
dbo.DI_EXP_EXPEDIENTARXIU.Observacions_Unitat,
dbo.DI_EXP_EXPEDIENTARXIU.Volum,
dbo.DI_EXP_EXPEDIENTARXIU.Descripcio_AC,
dbo.DI_EXP_EXPEDIENTARXIU.Observacions_AC,
dbo.DI_EXP_EXPEDIENTARXIU.CodiTransferencia,
dbo.DI_EXP_EXPEDIENTARXIU.Adreca,
dbo.DI_EXP_EXPEDIENTARXIU.CP,
dbo.DI_EXP_EXPEDIENTARXIU.C,
dbo.DI_EXP_EXPEDIENTARXIU.MK_UsuariAlta,
dbo.DI_EXP_EXPEDIENTARXIU.MK_DataAlta,
dbo.DI_EXP_EXPEDIENTARXIU.MK_UsuariModif,
dbo.DI_EXP_EXPEDIENTARXIU.MK_DataModif,
dbo.DI_EXP_EXPEDIENTARXIU.SigTop_I,
dbo.DI_EXP_EXPEDIENTARXIU.SigTop_F,
dbo.DI_EXP_EXPEDIENTARXIU.FK_Destruccio,
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_EXPEDIENTARXIU.FK_Arxiu,
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 '
--Depending if the parameter is informed it is attached to the WHERE statement or not
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

SET @strSQL = @strSQL + ' ORDER BY DI_EXP_CLASSIFICACIO.Codi_TC, Exped_I, Any_Obertura'


--PARAMS--
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)'



--EXECUTE--
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;
END