Blog - SQLServer (3)

Una mica de tot i a vegades més

	RSS
	English please

Categories

Contacte: Àlex

Com fer una consulta SQL insensible als accents

Hi ha moltes maneres de fer això, la normal és fer servir una collation (ordre alfabètic) diferent al de la BBDD i l'altra fent servir "[" i "]" (expressions regulars).

La collation fa que les consultes quedin algo com:


SELECT *
FROM Begudes
WHERE
   Name COLLATE Latin1_general_CI_AI Like '%cafe%' COLLATE Latin1_general_CI_AI

Això trobarà "cafè", "café"... Però a mi no m'agrada gaire. Prefereixo un altra mètode.

Es tracta de substituir totes les vocals de la cerca per [aàáAÀÁ], [eèéEÈÉ]... quedaria:


SELECT *
FROM Begudes
WHERE
   Name Like '%c[aàáAÀÁ]f[eèéEÈÉ]%'

No he trobat dades de rendiment ni recomanacions, tampoc en els llocs on ho he necessitat he vist que funcionés malament. El que sí he necessitat és una funció que em substitueixi les vocals per l'expressió regular. L'he afegit a la clsDades.


/// <summary>
/// Donat un string que formarà part d'un LIKE el torna ACCENT INSENSITIVE
/// </summary>
/// <param name="Valor">L'string a convertir</param>
/// <returns>L'string convertit</returns>
private string FerAccentInsesitive(string Valor)
{
    string Result = Valor;
    string a = "[aàáAÀÁ]";
    string e = "[eèéEÈÉ]";
    string i = "[iìíIÌÍïÏ]";
    string o = "[oòóOÒÓ]";
    string u = "[uùúUÙÚüÜ]";

    Result = Result.Replace("a", "_a_");
    Result = Result.Replace("e", "_e_");
    Result = Result.Replace("i", "_i_");
    Result = Result.Replace("o", "_o_");
    Result = Result.Replace("u", "_u_");

    Result = Result.Replace("à", "_à_");
    Result = Result.Replace("è", "_è_");
    Result = Result.Replace("ì", "_ì_");
    Result = Result.Replace("ò", "_ò_");
    Result = Result.Replace("ù", "_ù_");

    Result = Result.Replace("á", "_á_");
    Result = Result.Replace("é", "_é_");
    Result = Result.Replace("í", "_í_");
    Result = Result.Replace("ó", "_ó_");
    Result = Result.Replace("ú", "_ú_");

    Result = Result.Replace("A", "_A_");
    Result = Result.Replace("E", "_E_");
    Result = Result.Replace("I", "_I_");
    Result = Result.Replace("O", "_O_");
    Result = Result.Replace("U", "_U_");

    Result = Result.Replace("À", "_À_");
    Result = Result.Replace("È", "_È_");
    Result = Result.Replace("Ì", "_Ì_");
    Result = Result.Replace("Ò", "_Ò_");
    Result = Result.Replace("Ù", "_Ù_");

    Result = Result.Replace("Á", "_Á_");
    Result = Result.Replace("É", "_É_");
    Result = Result.Replace("Í", "_Í_");
    Result = Result.Replace("Ó", "_Ó_");
    Result = Result.Replace("Ú", "_Ú_");

    //----------------------------------------

    Result = Result.Replace("_a_", a);
    Result = Result.Replace("_e_", e);
    Result = Result.Replace("_i_", i);
    Result = Result.Replace("_o_", o);
    Result = Result.Replace("_u_", u);

    Result = Result.Replace("_à_", a);
    Result = Result.Replace("_è_", e);
    Result = Result.Replace("_ì_", i);
    Result = Result.Replace("_ò_", o);
    Result = Result.Replace("_ù_", u);

    Result = Result.Replace("_á_", a);
    Result = Result.Replace("_é_", e);
    Result = Result.Replace("_í_", i);
    Result = Result.Replace("_ó_", o);
    Result = Result.Replace("_ú_", u);

    Result = Result.Replace("_A_", a);
    Result = Result.Replace("_E_", e);
    Result = Result.Replace("_I_", i);
    Result = Result.Replace("_O_", o);
    Result = Result.Replace("_U_", u);

    Result = Result.Replace("_À_", a);
    Result = Result.Replace("_È_", e);
    Result = Result.Replace("_Ì_", i);
    Result = Result.Replace("_Ò_", o);
    Result = Result.Replace("_Ù_", u);

    Result = Result.Replace("_Á_", a);
    Result = Result.Replace("_É_", e);
    Result = Result.Replace("_Í_", i);
    Result = Result.Replace("_Ó_", o);
    Result = Result.Replace("_Ú_", u);

    return Result;
}

#03/03/2014 11:27
Programació
C#
SQLServer
Autor: Alex Canalda

Si no hi ha stored procedures, DEL

Aquesta és fàcil, el DEL és una sentència curta. Es tracta de construir un DELETE amb la clau primària informada (PK). El codi és força curt, encara que hi ha una part algo llarga per les taules que no fan servir identitats com PK i potser tenen una PK composta per alguns camps. Disponible en la clsDades.


public string DEL_Builder(Dictionary<string, string> DR)
{
    DateTime ValorDateTime;
    StringBuilder SB = new StringBuilder();
    bool Primer = true;

    SB.AppendFormat("DELETE FROM {0} WHERE ", NomTaula);

    if (TeIdentitat)
    {
	SB.AppendFormat("{0}={1}", Identitat, DR[Identitat]);
    }
    else
    {
	foreach (clsCamp Camp in CampsResultat)
	{
	    if (Camp.PK)
	    {
		if (!Primer) SB.Append(" AND ");
		else Primer = false;

		switch (Camp.Tipus)
		{
		    case Tipus.nchar:
		    case Tipus.nvarchar:
		    case Tipus.chr:
		    case Tipus.varchar:
			SB.AppendFormat("{0}='{1}'", Camp.NomCamp, DR[Camp.NomCamp].Replace("'", "''"));
			break;
		    case Tipus.datetime:
		    case Tipus.date:
		    case Tipus.time:
			ValorDateTime = DateTime.Parse(DR[Camp.NomCamp]);
			SB.AppendFormat("{0}='{1}'", Camp.NomCamp, FDateSQL(ValorDateTime));
			break;
		    case Tipus.dec:
			SB.AppendFormat("{0}={1}", Camp.NomCamp, DR[Camp.NomCamp].Replace(",", "."));
			break;
		    case Tipus.bit:
			if (DR[Camp.NomCamp].ToString().ToLower() == "true") SB.AppendFormat("{0}=1, ", Camp.NomCamp);
			else SB.AppendFormat("{0}=0", Camp.NomCamp);
			break;
		    default:
			SB.AppendFormat("{0}={1}", Camp.NomCamp, DR[Camp.NomCamp]);
			break;
		}
	    }
	}
    }
    return SB.ToString();
}

public string DEL_Builder(DataRow DR)
{
    DateTime ValorDateTime;
    StringBuilder SB = new StringBuilder();
    bool Primer = true;

    SB.AppendFormat("DELETE FROM {0} WHERE ", NomTaula);

    if (TeIdentitat)
    {
	SB.AppendFormat("{0}={1}", Identitat, DR[Identitat]);
    }
    else
    {
	foreach (clsCamp Camp in CampsResultat)
	{
	    if (Camp.PK)
	    {
		if (!Primer) SB.Append(" AND ");
		else Primer = false;

		switch (Camp.Tipus)
		{
		    case Tipus.nchar:
		    case Tipus.nvarchar:
		    case Tipus.chr:
		    case Tipus.varchar:
			SB.AppendFormat("{0}='{1}'", Camp.NomCamp, DR[Camp.NomCamp].ToString().Replace("'", "''"));
			break;
		    case Tipus.datetime:
		    case Tipus.date:
		    case Tipus.time:
			ValorDateTime = DR.Field<DateTime>(Camp.NomCamp);
			SB.AppendFormat("{0}='{1}'", Camp.NomCamp, FDateSQL(ValorDateTime));
			break;
		    case Tipus.dec:
			SB.AppendFormat("{0}={1}", Camp.NomCamp, DR[Camp.NomCamp].ToString().Replace(",", "."));
			break;
		    case Tipus.bit:
			if (DR[Camp.NomCamp].ToString().ToLower() == "true") SB.AppendFormat("{0}=1, ", Camp.NomCamp);
			else SB.AppendFormat("{0}=0", Camp.NomCamp);
			break;
		    default:
			SB.AppendFormat("{0}={1}", Camp.NomCamp, DR[Camp.NomCamp]);
			break;
		}
	    }
	}
    }
    return SB.ToString();
}

#28/02/2014 11:21
Programació
C#
SQLServer
Autor: Alex Canalda

Si no hi ha stored procedures, UPD

Aquestes funcions s'encarreguen de muntar un SQL amb un UPDATE per actualitzar un registre de la taula. Si no hi ha stored procedures es pot recórrer a això. Com sempre en dos sabors, un amb DataRow i un amb Dictionary. Quan es munta el WHERE el les taules que tenen identitat normalment serà fàcil, si no cal suportar que hi hagi una clau primària (PK) composta, per això cal fer un bucle per posar tots els camps i valors de la PK. Disponible a la clsDades


public string UPD_Builder(Dictionary<string, string> DR)
{
    StringBuilder SB = new StringBuilder();
    DateTime ValorDateTime;
    bool Primer = true;

    SB.AppendFormat("UPDATE {0} SET ", NomTaula);
    for (int i = 0; i < CampsResultat.Length; i++)
    {
	if (!CampsResultat[i].PK)
	{
	    if (!DR.ContainsKey(CampsResultat[i].NomCamp))
	    {
		throw new Exception("La fila no té el camp " + CampsResultat[i].NomCamp);
	    }

	    if (string.IsNullOrWhiteSpace(DR[CampsResultat[i].NomCamp]))
	    {
		if (!CampsResultat[i].Nulable)
                  throw new Exception("Camp " + CampsResultat[i].NomCamp + " no admet valors NULLs");
		else
		{
		    SB.AppendFormat("{0}=NULL, ", CampsResultat[i].NomCamp);
		}
	    }
	    else
	    {
		switch (CampsResultat[i].Tipus)
		{
		    case Tipus.nchar:
		    case Tipus.nvarchar:
		    case Tipus.chr:
		    case Tipus.varchar:
			SB.AppendFormat("{0}='{1}', ",
                          CampsResultat[i].NomCamp, DR[CampsResultat[i].NomCamp].Replace("'", "''"));
			break;
		    case Tipus.datetime:
		    case Tipus.date:
		    case Tipus.time:
			ValorDateTime = DateTime.Parse(DR[CampsResultat[i].NomCamp]);
			SB.AppendFormat("{0}='{1}', ",
                          CampsResultat[i].NomCamp, FDateSQL(ValorDateTime));
			break;
		    case Tipus.dec:
			SB.AppendFormat("{0}={1}, ",
                           CampsResultat[i].NomCamp, DR[CampsResultat[i].NomCamp].Replace(",", "."));
			break;
		    case Tipus.bit:
			if (DR[CampsResultat[i].NomCamp].ToLower() == "true")
                           SB.AppendFormat("{0}=1, ", CampsResultat[i].NomCamp);
			else SB.AppendFormat("{0}=0, ", CampsResultat[i].NomCamp);
			break;
		    default:
			SB.AppendFormat("{0}={1}, ", CampsResultat[i].NomCamp, DR[CampsResultat[i].NomCamp]);
			break;
		}
	    }
	}
    }
    SB.Remove(SB.Length - 2, 2);

    //La clau primaria
    SB.Append(" WHERE ");

    if (TeIdentitat)
    {
	SB.AppendFormat("{0}={1}", Identitat, DR[Identitat]);
    }
    else
    {
	foreach (clsCamp Camp in CampsResultat)
	{
	    if (Camp.PK)
	    {
		if (!Primer) SB.Append(" AND ");
		else Primer = false;

		switch (Camp.Tipus)
		{
		    case Tipus.nchar:
		    case Tipus.nvarchar:
		    case Tipus.chr:
		    case Tipus.varchar:
			SB.AppendFormat("{0}='{1}'", Camp.NomCamp, DR[Camp.NomCamp].Replace("'", "''"));
			break;
		    case Tipus.datetime:
		    case Tipus.date:
		    case Tipus.time:
			ValorDateTime = DateTime.Parse(DR[Camp.NomCamp]);
			SB.AppendFormat("{0}='{1}'", Camp.NomCamp, FDateSQL(ValorDateTime));
			break;
		    case Tipus.dec:
			SB.AppendFormat("{0}={1}", Camp.NomCamp, DR[Camp.NomCamp].Replace(",", "."));
			break;
		    case Tipus.bit:
			if (DR[Camp.NomCamp].ToString().ToLower() == "true") 
                           SB.AppendFormat("{0}=1, ", Camp.NomCamp);
			else SB.AppendFormat("{0}=0", Camp.NomCamp);
			break;
		    default:
			SB.AppendFormat("{0}={1}", Camp.NomCamp, DR[Camp.NomCamp]);
			break;
		}
	    }
	}
    }
    return SB.ToString();
}

public string UPD_Builder(DataRow DR)
{
    StringBuilder SB = new StringBuilder();
    DateTime ValorDateTime;
    bool Primer = true;

    SB.AppendFormat("UPDATE {0} SET ", NomTaula);
    for (int i = 0; i < CampsResultat.Length; i++)
    {
	if (!CampsResultat[i].PK)
	{
	    if (!DR.Table.Columns.Contains(CampsResultat[i].NomCamp))
	    {
		throw new Exception("La fila no té el camp " + CampsResultat[i].NomCamp);
	    }

	    if (DR.IsNull(CampsResultat[i].NomCamp))
	    {
		if (!CampsResultat[i].Nulable)
                   throw new Exception("Camp " + CampsResultat[i].NomCamp + " no admet valors NULLs");
		else
		{
		    SB.AppendFormat("{0}=NULL, ", CampsResultat[i].NomCamp);
		}
	    }
	    else
	    {
		switch (CampsResultat[i].Tipus)
		{
		    case Tipus.nchar:
		    case Tipus.nvarchar:
		    case Tipus.chr:
		    case Tipus.varchar:
			SB.AppendFormat("{0}='{1}', ", CampsResultat[i].NomCamp,
                           DR[CampsResultat[i].NomCamp].ToString().Replace("'", "''"));
			break;
		    case Tipus.datetime:
		    case Tipus.date:
		    case Tipus.time:
			ValorDateTime = DR.Field<DateTime>(CampsResultat[i].NomCamp);
			SB.AppendFormat("{0}='{1}', ", CampsResultat[i].NomCamp, FDateSQL(ValorDateTime));
			break;
		    case Tipus.dec:
			SB.AppendFormat("{0}={1}, ", CampsResultat[i].NomCamp,
                           DR[CampsResultat[i].NomCamp].ToString().Replace(",", "."));
			break;
		    case Tipus.bit:
			if (DR[CampsResultat[i].NomCamp].ToString().ToLower() == "true") 
                            SB.AppendFormat("{0}=1, ", CampsResultat[i].NomCamp);
			else SB.AppendFormat("{0}=0, ", CampsResultat[i].NomCamp);
			break;
		    default:
			SB.AppendFormat("{0}={1}, ", CampsResultat[i].NomCamp, DR[CampsResultat[i].NomCamp]);
			break;
		}
	    }
	}
    }
    SB.Remove(SB.Length - 2, 2);

    //La clau primaria
    SB.Append(" WHERE ");

    if (TeIdentitat)
    {
	SB.AppendFormat("{0}={1}", Identitat, DR[Identitat]);
    }
    else
    {
	foreach (clsCamp Camp in CampsResultat)
	{
	    if (Camp.PK)
	    {
		if (!Primer) SB.Append(" AND ");
		else Primer = false;

		switch (Camp.Tipus)
		{
		    case Tipus.nchar:
		    case Tipus.nvarchar:
		    case Tipus.chr:
		    case Tipus.varchar:
			SB.AppendFormat("{0}='{1}'", Camp.NomCamp, DR[Camp.NomCamp].ToString().Replace("'", "''"));
			break;
		    case Tipus.datetime:
		    case Tipus.date:
		    case Tipus.time:
			ValorDateTime = DR.Field<DateTime>(Camp.NomCamp);
			SB.AppendFormat("{0}='{1}'", Camp.NomCamp, FDateSQL(ValorDateTime));
			break;
		    case Tipus.dec:
			SB.AppendFormat("{0}={1}", Camp.NomCamp, DR[Camp.NomCamp].ToString().Replace(",", "."));
			break;
		    case Tipus.bit:
			if (DR[Camp.NomCamp].ToString().ToLower() == "true") SB.AppendFormat("{0}=1, ", Camp.NomCamp);
			else SB.AppendFormat("{0}=0", Camp.NomCamp);
			break;
		    default:
			SB.AppendFormat("{0}={1}", Camp.NomCamp, DR[Camp.NomCamp]);
			break;
		}
	    }
	}
    }

    return SB.ToString();
}

#27/02/2014 15:50
Programació
C#
SQLServer
Autor: Alex Canalda

Si no hi ha stored procedures...

Per raons que no arribo a entendre a vegades hi ha projectes on el client no vol stored procedures. O potser es requereix fer una migració de dades puntual i no es volen deixar un munt d'stored procedures que ningú farà servir més endevant. Aleshores tota la feina del generador d'storeds no serveix, hi ha que fer servir una altra tècnica.

Per això he mogut uns quants mètodes del generador d'stored a la clsDades, així la clsDades genera SQL sobre la marxa. Es a dir, aprofitant la informació sobre els camps de la taula de la que disposa una clase derivada (nom, longitud i tipus de les columnes) la clsDades ha incorporat unes funcions que generen inserts, updates i deletes sobre la marxa.

Estan disponibles en dos sabors, la que admet DataRow i la que admet Dictionary's, són calcades amb petites modificacions. Començaré pel constructor d'INSerts i deixaré per altres posts els UPD i DEL.


public string INS_Builder(Dictionary<string, string> DR, bool IdentityInsert)
{
    DateTime ValorDateTime;
    StringBuilder SB = new StringBuilder();

    SB.AppendFormat("INSERT INTO {0} (", NomTaula);

    for (int i = 0; i < CampsResultat.Length; i++)
    {
	if (!CampsResultat[i].PK || IdentityInsert)
	{
	    SB.Append(CampsResultat[i].NomCamp + ", ");
	}
    }
    //Treiem l'ultima , i espai
    SB.Remove(SB.Length - 2, 2);

    SB.Append(") VALUES (");
    for (int i = 0; i < CampsResultat.Length; i++)
    {
	if (!CampsResultat[i].PK || IdentityInsert)
	{
	    if (!DR.ContainsKey(CampsResultat[i].NomCamp))
	    {
		throw new Exception("La fila no té el camp " + CampsResultat[i].NomCamp);
	    }

	    if (string.IsNullOrWhiteSpace(DR[CampsResultat[i].NomCamp]))
	    {
		if (!CampsResultat[i].Nulable)
                  throw new Exception("Camp " + CampsResultat[i].NomCamp + " no admet valors NULLs");
		else
		{
		    SB.Append("NULL, ");
		}
	    }
	    else
	    {
		switch (CampsResultat[i].Tipus)
		{
		    case Tipus.nchar:
		    case Tipus.nvarchar:
		    case Tipus.chr:
		    case Tipus.varchar:
			SB.Append("'" + DR[CampsResultat[i].NomCamp].Replace("'", "''") + "', ");
			break;
		    case Tipus.datetime:
		    case Tipus.date:
		    case Tipus.time:
			ValorDateTime = DateTime.Parse(DR[CampsResultat[i].NomCamp]);
			SB.Append("'" + FDateSQL(ValorDateTime) + "', ");
			break;
		    case Tipus.dec:
			SB.Append(DR[CampsResultat[i].NomCamp].Replace(",", ".") + ", ");
			break;
		    case Tipus.bit:
			if (DR[CampsResultat[i].NomCamp].ToLower() == "true" ) SB.Append("1, ");
			else SB.Append("0, ");
			break;
		    default:
			SB.Append(DR[CampsResultat[i].NomCamp] + ", ");
			break;
		}
	    }
	}
    }
    SB.Remove(SB.Length - 2, 2);
    SB.Append(")");

    return SB.ToString();
}

public string INS_Builder(DataRow DR, bool IdentityInsert)
{
    DateTime ValorDateTime;
    Boolean ValorBool;
    StringBuilder SB = new StringBuilder();

    SB.AppendFormat("INSERT INTO {0} (", NomTaula);

    for (int i = 0; i < CampsResultat.Length; i++)
    {
	if (!CampsResultat[i].PK || IdentityInsert)
	{
	    SB.Append(CampsResultat[i].NomCamp + ", ");
	}
    }
    //Treiem l'ultima , i espai
    SB.Remove(SB.Length - 2, 2);

    SB.Append(") VALUES (");
    for (int i = 0; i < CampsResultat.Length; i++)
    {
	if (!CampsResultat[i].PK || IdentityInsert)
	{
	    if (!DR.Table.Columns.Contains(CampsResultat[i].NomCamp))
	    {
		throw new Exception("La fila no té el camp " + CampsResultat[i].NomCamp);
	    }

	    if (DR.IsNull(CampsResultat[i].NomCamp))
	    {
		if (!CampsResultat[i].Nulable)
                  throw new Exception("Camp " + CampsResultat[i].NomCamp + " no admet valors NULLs");
		else
		{
		    SB.Append("NULL, ");
		}
	    }
	    else
	    {
		switch (CampsResultat[i].Tipus)
		{
		    case Tipus.nchar:
		    case Tipus.nvarchar:
		    case Tipus.chr:
		    case Tipus.varchar:
			SB.Append("'" + DR[CampsResultat[i].NomCamp].ToString().Replace("'", "''") + "', ");
			break;
		    case Tipus.datetime:
		    case Tipus.date:
		    case Tipus.time:
			ValorDateTime = DR.Field<DateTime>(CampsResultat[i].NomCamp);
			SB.Append("'" + FDateSQL(ValorDateTime) + "', ");
			break;
		    case Tipus.dec:
			SB.Append(DR[CampsResultat[i].NomCamp].ToString().Replace(",", ".") + ", ");
			break;
		    case Tipus.bit:
			ValorBool = DR.Field<Boolean>(CampsResultat[i].NomCamp);
			if (ValorBool) SB.Append("1, ");
			else SB.Append("0, ");
			break;
		    default:
			SB.Append(DR[CampsResultat[i].NomCamp].ToString() + ", ");
			break;
		}
	    }
	}
    }
    SB.Remove(SB.Length - 2, 2);
    SB.Append(")");
    return SB.ToString();
}

Cal tenir en compte que si després d'un INSERT es vol obtenir el valor d'una columna identitat cal fer servir la funció IDENT_CURRENT. Si es volen agrupar varies sentències SQL s'haurà de fer en un StringBuilder on es vagin acumulant. Les versions de INS/UPD que reben un Dictionary encaixen amb el Deserialitzador que retorna també un Dictionary.

#27/02/2014 11:51
Programació
C#
SQLServer
Autor: Alex Canalda

Diferència entre Index Clustered i Non-Clustered

Sempre que escric un CREATE TABLE on hi ha la definició d'un Index em sorgeix el dubte de quina diferència hi ha entre un Clustered i un Non-Clustered.

Per entendre-ho cal veure que és un index: els indexos són objectes de la BBDD que ajuden en la cerca de dades dins de la BBDD. Conforme les taules es van fent grans, els indexos es fan indispensables per fer-hi cerques. Tenen una estructura en forma d'arbre, on hi ha una arrel i moltes fulles que són el nivell final. Quan es fa una cerca es mira la clau de l'index per tal fer el menor nombre de lectures possibles fins a arribar a les fulles.

De moment hem vist el que és un índex, ara cal veure la diferència, que està en el format de les fulles. En un index de tipus clustered, les dades de la taula es troben a la mateixa fulla, per tan un cop arriba la lectura de la fulla ja es tenen les dades disponibles.

En canvi en un index Non-Clustered el que hi ha a les fulles és un punter a les dades, cal fer un "bookmark lookup" (una lectura extra) per arribar fins les dades.

Òbviament els index Non-clustered tenen un rendiment inferior a un Clustered donat que tenen que fer una lectura més. Des de SQL Server 2005, es permet posar dins d'un index Non-Clustered camps que no formen part de la clau de l'index per tal d'evitar fer aquesta lectura extra i millorar el rendiment. Respecte com s'inclouen camps a un index, les limitacions sobre incloure camps, ... hi ha un article de la Technet que ho explica, amb exemples.

Tampoc es poden definir tots els indexos com Clustered, ja que només es permet un únic index d'aquest tipus en una taula. La raó és que en realitat els registres estan guardats a disc juntament amb l'index, ordenats seguint l'index i clar, no es pot tenir dos ordres físics alhora.

Punts a tenir en compte al fer servir indexos:

Els camps IDENTITY que siguin clau primària (PK) normalment són els que defineixen el index clustered. Això és per evitar el page split o fragmentació de la taula.
Per cerques de camps concrets que es repeteixen sovint, sense que siguin la PK, es millor fer servir un Non-Clustered. Si els resultats ho permeten es pot afegir algun camp resultat a les fulles.
La PK acostuma a ser una bona candidata per fer un Clustered Index, però si la PK no es fa servir gaire (per exemple factures on s'accedeix per la data de la factura) potser es pot plantejar de fer servir un altra. Però això ja varia a cada cas.
Per cerques compostes (es a dir que tinguin varis camps) cal fer servir Non-Clustered.

#22/01/2014 15:33
Programació
SQLServer
Autor: Alex Canalda

Capa d'acces a BBDD: QUERY - Taula temporal dinàmic

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

#27/11/2013 13:51
Programació
SQLServer
Autor: Alex Canalda

Capa d'acces a BBDD: QUERY - CTE - DENSE_RANK

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

#27/11/2013 10:48
Programació
SQLServer
Autor: Alex Canalda

Capa d'acces a BBDD: QUERY - CTE - ROW_NUMBER

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.

#26/11/2013 19:13
Programació
SQLServer
Autor: Alex Canalda

SQL Server Dense_Rank vs Row_Number

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.

#26/11/2013 19:10
Programació
SQLServer
Autor: Alex Canalda

Capa d'acces a BBDD: QUERY

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.

#26/11/2013 13:18
Programació
SQLServer
Autor: Alex Canalda

<< Posts anteriors | Posts més recents >>