When developing web applications an infraestructure to access the database is needed. In other words, a piece to connect the bussines logic to the database, to invoke the stored procedures (SP) that works with data. This layer has to be easy to work with, automated at maximum to avoid messing with the boring task of building the parameter list of the SPs by hand (a process also prone to errors). For this reason I've build the clsData.
clsData is the principal class of the data layer, it holds all of the methods to acces the database. It invokes the SPs, but as every SP has different parameters, other clases creates the definition for those parameters and inherite from clsData to add the methods to invoke the SPs.
So, clsData has the methods to acces and other clases that use inheritance has the definition needed to fill the methods of clsData. For this reason clsData has the declaration of the variables that later the other classes will initialize with the correct values. Those variables, in fact, are lists of objects that has information to build a SQL parameter. First of all I use a simple class to store type information (and when I say type I'm refering to database type), the DBType class.
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.
#19/03/2014 16:35 Programming SQLServer Author: Alex Canalda
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
ALTERPROCEDURE [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
ASBEGINSETNOCOUNTON;
--Creation of the first table, used for filteringIF OBJECT_ID('tempdb..#TempTBLDI_EXP_EXPEDIENTARXIU_Filtre') IS NOT NULLDROPTABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
CREATETABLE #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 sortingIF OBJECT_ID('tempdb..#TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio') IS NOT NULLDROPTABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
CREATETABLE #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 resultsSET @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 filtersIF @pPK_ExpedientArxiu IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.PK_ExpedientArxiu=@lPK_ExpedientArxiu'ENDIF @pTitol IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Titol LIKE @lTitol + ''%'''ENDIF @pExped_I IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Exped_I LIKE @lExped_I + ''%'''ENDIF @pAny_Obertura IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura = @lAny_Obertura'ENDIF @pExped_F IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Exped_F LIKE @lExped_F + ''%'''ENDIF @pFK_LLocDiposit IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_SIGTOP.FK_LLocDiposit = @lFK_LLocDiposit'ENDIF @pFK_Classificacio IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Classificacio = @lFK_Classificacio'ENDIF @pFK_Destruccio IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Destruccio = @lFK_Destruccio'ENDIF @pFK_Procedencia IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Procedencia = @lFK_Procedencia'ENDIF @pFK_Especifica IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Especifica = @lFK_Especifica'ENDIF @pAny_Tancament IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament = @lAny_Tancament'ENDIF @pFK_Transferencia IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Transferencia = @lFK_Transferencia'ENDIF @pFK_Estat IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Estat = @lFK_Estat'ENDIF @pFK_Arxiu IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Arxiu = @lFK_Arxiu'ENDIF @pFK_TAAD IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_TAAD_REL.FK_TAAD = @lFK_TAAD'ENDIF @pFK_Uniforme IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.FK_Uniforme = @lFK_Uniforme'ENDIF @pFK_Capsa IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_CAPSA_EXPEDIENT.FK_Capsa = @lFK_Capsa'ENDIF @pAny_OberturaMesGran IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Obertura >= @lAny_OberturaMesGran'ENDIF @pAny_TancamentMenor IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_EXPEDIENTARXIU.Any_Tancament <= @lAny_TancamentMenor'ENDIF @pCapsa IS NOT NULLBEGINSET @strSQL = @strSQL + ' AND dbo.DI_EXP_CAPSES.Capsa LIKE @lCapsa + ''%'''END-- Construction of the parametersSET @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 informedEXECUTE 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 sortINSERTINTO #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
SELECT
COUNT(*) OVER () AS NumRegs,
ROW_NUMBER()
OVER(ORDERBYCASEWHEN @pSortColumn = 'PK_ExpedientArxiuASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.PK_ExpedientArxiu ENDASC,
CASEWHEN @pSortColumn = 'PK_ExpedientArxiuDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.PK_ExpedientArxiu ENDDESC,
CASEWHEN @pSortColumn = 'FK_ProcedenciaASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Procedencia ENDASC,
CASEWHEN @pSortColumn = 'FK_ProcedenciaDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Procedencia ENDDESC,
CASEWHEN @pSortColumn = 'FK_ClassificacioASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Classificacio ENDASC,
CASEWHEN @pSortColumn = 'FK_ClassificacioDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Classificacio ENDDESC,
CASEWHEN @pSortColumn = 'FK_UniformeASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Uniforme ENDASC,
CASEWHEN @pSortColumn = 'FK_UniformeDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Uniforme ENDDESC,
CASEWHEN @pSortColumn = 'FK_EspecificaASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Especifica ENDASC,
CASEWHEN @pSortColumn = 'FK_EspecificaDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.FK_Especifica ENDDESC,
CASEWHEN @pSortColumn = 'TitolASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Titol ENDASC,
CASEWHEN @pSortColumn = 'TitolDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Titol ENDDESC,
CASEWHEN @pSortColumn = 'Exped_IASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Exped_I ENDASC,
CASEWHEN @pSortColumn = 'Exped_IDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Exped_I ENDDESC,
CASEWHEN @pSortColumn = 'Any_OberturaASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Obertura ENDASC,
CASEWHEN @pSortColumn = 'Any_OberturaDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Obertura ENDDESC,
CASEWHEN @pSortColumn = 'Any_TancamentASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Tancament ENDASC,
CASEWHEN @pSortColumn = 'Any_TancamentDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Any_Tancament ENDDESC,
CASEWHEN @pSortColumn = 'U_Instal_IASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_I ENDASC,
CASEWHEN @pSortColumn = 'U_Instal_IDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_I ENDDESC,
CASEWHEN @pSortColumn = 'U_Instal_FASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_F ENDASC,
CASEWHEN @pSortColumn = 'U_Instal_FDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.U_Instal_F ENDDESC,
CASEWHEN @pSortColumn = 'SigTop_IASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_I ENDASC,
CASEWHEN @pSortColumn = 'SigTop_IDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_I ENDDESC,
CASEWHEN @pSortColumn = 'SigTop_FASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_F ENDASC,
CASEWHEN @pSortColumn = 'SigTop_FDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.SigTop_F ENDDESC,
CASEWHEN @pSortColumn = 'CapsaASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Capsa ENDASC,
CASEWHEN @pSortColumn = 'CapsaDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Capsa ENDDESC,
CASEWHEN @pSortColumn = 'SiglaASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Sigla ENDASC,
CASEWHEN @pSortColumn = 'SiglaDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Sigla ENDDESC,
CASEWHEN @pSortColumn = 'CapsaHistASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.CapsaHist ENDASC,
CASEWHEN @pSortColumn = 'CapsaHistDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.CapsaHist ENDDESC,
CASEWHEN @pSortColumn = 'EstatASC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Estat ENDASC,
CASEWHEN @pSortColumn = 'EstatDESC'THEN #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre.Estat ENDDESC
)
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 pageSELECT * FROM #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
WHERE RowNumber >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND RowNumber <= @pPageSize * @pPageNum
ORDERBY RowNumber
--Final cleaningDROPTABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Filtre
DROPTABLE #TempTBLDI_EXP_EXPEDIENTARXIU_Ordenacio
END
#12/03/2014 16:57 Programming SQLServer Author: Alex Canalda
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:
ALTERPROCEDURE [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
ASBEGINWITH PagingCTE-- CTE creation
(
Row_ID,
PK_Classificacio,
Codi_TC,
Observacions,
DataAlta,
DataBaixa,
Any_inici,
Any_final,
IdPare,
Nom,
Nivell_Codi)
AS
(
SELECTDISTINCT--This SELECT uses a DISTINCT to filter out duplicates
DENSE_RANK()--Here DENSE_RANK is used, sintactically is identical to ROW_NUMBEROVER(ORDERBYCASEWHEN @pSortColumn = 'Codi_TCASC'THEN DI_EXP_CLASSIFICACIO.Codi_TC ENDASC,
CASEWHEN @pSortColumn = 'Codi_TCDESC'THEN DI_EXP_CLASSIFICACIO.Codi_TC ENDDESC,
CASEWHEN @pSortColumn = 'NomASC'THEN DI_EXP_CLASSIFICACIO.Nom ENDASC,
CASEWHEN @pSortColumn = 'NomDESC'THEN DI_EXP_CLASSIFICACIO.Nom ENDDESC
)
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 duplicatesLEFTOUTERJOIN DI_EXP_CLASSIFICACIO_ARXIU
ON DI_EXP_CLASSIFICACIO.PK_Classificacio=DI_EXP_CLASSIFICACIO_ARXIU.PK_Classificacio
LEFTOUTERJOIN DI_EXP_TAAD_REL ON DI_EXP_CLASSIFICACIO.PK_Classificacio=DI_EXP_TAAD_REL.FK_Classificacio
WHERE ((@pPK_Classificacio ISNULL) OR (DI_EXP_CLASSIFICACIO.PK_Classificacio=@pPK_Classificacio))
AND ((@pNom ISNULL) OR (DI_EXP_CLASSIFICACIO.Nom LIKE @pNom + '%'))
AND ((@pPK_Arxiu ISNULL) OR (DI_EXP_CLASSIFICACIO_ARXIU.PK_Arxiu = @pPK_Arxiu))
AND ((@pCodi_TC ISNULL) OR (DI_EXP_CLASSIFICACIO.Codi_TC LIKE @pCodi_TC + '%'))
AND ((@pNivell_Codi ISNULL) OR (DI_EXP_CLASSIFICACIO.Nivell_Codi LIKE @pNivell_Codi + '%'))
AND ((@pIdPare ISNULL) OR (DI_EXP_CLASSIFICACIO.IdPare = @pIdPare))
AND ((@pFK_TAAD ISNULL) 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
ORDERBY Row_ID
END
#12/03/2014 16:34 Programming SQLServer Author: Alex Canalda
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.
ALTERPROCEDURE [dbo].[DI_EXP_ORG_QUERY]
@pPK_Org int, @pSortColumn NVARCHAR(50) = null,
@pPageSize INT = 10,
@pPageNum INT = 1
ASBEGINSETNOCOUNTON;
--CTE declarationWITH PagingCTE
(--Columns are only defined by name
Row_ID,
PK_Org,
Nom,
Descripcio)
AS
(--The query is enclosed in parenthesisSELECT
ROW_NUMBER()--Here is used the Row_Number function, and the ORDER BYOVER(ORDERBYCASEWHEN @pSortColumn = 'PK_OrgASC'THEN DI_EXP_ORG.PK_Org ENDASC,
CASEWHEN @pSortColumn = 'PK_OrgDESC'THEN DI_EXP_ORG.PK_Org ENDDESC,
CASEWHEN @pSortColumn = 'NomASC'THEN DI_EXP_ORG.Nom ENDASC,
CASEWHEN @pSortColumn = 'NomDESC'THEN DI_EXP_ORG.Nom ENDDESC,
CASEWHEN @pSortColumn = 'DescripcioASC'THEN DI_EXP_ORG.Descripcio ENDASC,
CASEWHEN @pSortColumn = 'DescripcioDESC'THEN DI_EXP_ORG.Descripcio ENDDESC
)
AS [Row_ID],
DI_EXP_ORG.PK_Org,
DI_EXP_ORG.Nom,
DI_EXP_ORG.Descripcio
FROM DI_EXP_ORG WHERE ((@pPK_Org ISNULL) OR (DI_EXP_ORG.PK_Org=@pPK_Org))
)--Here ends the CTE, ready to use in the following QUERYSELECT
(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 parametersWHERE Row_ID >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND Row_ID <= @pPageSize * @pPageNum
ORDERBY 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.
#12/03/2014 15:58 Programming SQLServer Author: Alex Canalda
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.
#11/03/2014 16:41 Programming SQLServer Author: Alex Canalda
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.
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:
DELETEFROM DI_EXP_DESTRUCCIO
WHERE
((@pPK_Destruccio ISNULL) OR (PK_Destruccio=@pPK_Destruccio)) AND
((@pFK_TAAD ISNULL) OR (DI_EXP_DESTRUCCIO.FK_TAAD = @pFK_TAAD))
AND ((@pFK_Estat ISNULL) OR (DI_EXP_DESTRUCCIO.FK_Estat = @pFK_Estat))
AND ((@pDataInici ISNULL) OR (DI_EXP_DESTRUCCIO.Data_Destruccio BETWEEN @pDataInici AND @pDataFi))
AND ((@pFK_Arxiu ISNULL) OR (DI_EXP_DESTRUCCIO.FK_Arxiu = @pFK_Arxiu))
#03/03/2014 16:06 Programming SQLServer Author: Alex Canalda
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.