montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server
Estándares Tran ransact sact-SQ -SQL L Buenas Prácticas de Programación
Página 1 de 12
montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server
1. Convenciones y Estándares de Nombres Nota: Los términos “notación Pascal ! “notación de "amell son usados a tra#és de este documento$
Notación Pascal % El &rimer carácter de todas las &ala'ras se escri'e en (a!)sculas ! los otros caracteres en min)sculas$ E*em&lo: "olor+e,ondo
Notación de Camell % El &rimer carácter de todas las &ala'ras e.ce&to de la &rimera &ala'ra se escri'e en (a!)sculas ! los otros caracteres en min)sculas$ E*em&lo: color+e,ondo 1$ /sa notación Pascal &ara el nom're de las Ta'las CREATE TABLE dbo.Employee
2$ /sa notación Pascal &ara el nom're de los cam&os de ta'la CREATE TABLE dbo.Employee ( IdEmployee INT, FirstName VARCHAR(20), LastName VARCHAR(20) )
0$ N usar nunca “s& La ra3ón es 4ue: SQL Ser#er reconoce el &re5*o “s& como “S!stem Stored Procedure es decir un &rocedimiento almacenado de Sistema ! lo 'uscar6a en la Base de +atos$ /sa la siguiente sinta.is &ara los nom'res de los Stored &rocedures: /s&7Nom're Es4uema87Nom're Ta'la8 79ccion8 E*em&lo: us&ENEm&lo!ee;nsert us&ENEm&lo!eeet9ll <$ /sa es4uemas &ara agru&ar los o'*etos como ta'las los nom'res de'en de ser 9're#iados$ ;ncorrecto: ENEm&lo!ee "orrecto:
Página 2 de 12
montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server
EN$Em&lo!ee
Página 0 de 12
montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server
2. Consideraciones en el diseño de base de datos 1$ El nom're de la 'ase de datos de'e de aseme*arse al nom're de la a&licación no de'erá de contener el &re5*o +B !a 4ue se estar6a redundando indicando 4ue es una 'ase de datos$ /tili3ar notación Pascal &ara el nom're$
;ncorrecto: +B=entas
"orrecto: =entas
2$ Eliminación de registro$ Es recomenda'le N eliminar >6sicamente un registro de &rimeras$ Pasarlo a un estado de “'a*a$
0$ "rear cam&os de auditoria 4ue guardes los )ltimos #alores e*ecutadas &or el usuario: •
,ec?a@egistro
•
@es&onsa'le
•
(a4uina
•
;P
<$ E#ite crear cam&os 4ue ace&ten N/LLs solo a&l64uelo &ara los cam&os 4ue realmente lo re4uiera$
A$ ;m&lemente “SET N"/NT N en sus consultas$
Página < de 12
montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server
9l e*ecutar sentencias ti&o DML en un &rocedimiento almacenado el ser#idor SQL regresa un mensa*e indicando el n)mero de 5las a>ectadas &or el &roceso$ 9un4ue esta in>ormación &uede ser )til &ara de&urar el código resulta o'soleta si no se está de&urando$ 9l esta'lecer el uso de SET NC!NT N se desacti#a la >unción de retorno de esta in>ormación adicional$ En &rocedimientos almacenados >ormados &or #arias instrucciones o declaraciones de sentencias DML ?a'ilitar SET NC!NT N &uede a!udarnos a conseguir un aumento de rendimiento considera'le además si tomamos en cuenta una 'ase de datos 4ue contenga decenas de Procedimientos 9lmacenados &ara uso en una a&licación orientada a m)lti&les clientes al mismo tiem&o un detalle tan sencillo como este &uede a&ortar una di>erencia nota'le$ Se &odr6a usar a modo de e*em&lo el siguiente es4ueleto: CREATE PROC dbo.StoreProed!reE"#mple ( $%#r&#ble' INT, $%#r&#ble'2 VARCHAR(0), $%#r&#ble' BIT ) AS BE*IN SET NOCO+NT ON --INSTR+CCIN --INSTR+CCIN 2 --INSTR+CCIN N SET NOCO+NT O// EN
$ E#ite usar “SELE"T C 9un4ue resulte >ácil ! cómodo usar el comod6n DC &ara traer todos los cam&os este de'e omitirse ! en su lugar es&eci5carse los cam&os 4ue sean necesario traerse$ El uso del comod6n im&ide además un uso e>ecti#o de >orma e5ciente de los 6ndices$ En caso de 4ue sean todos como el uso del “C esta'lece es&eci54ue cada uno de ellos un sim&le 9LTF,1 so're el nom're de la ta'la seleccionada traerá su estructura &or lo 4ue co&iar &egar los cam&os ! aGadir una coma su&one un es>uer3o m6nimo$ (e ?e to&ado en #arias ocasiones 4ue al ?acer un c?e4ueo de e.istencia se recurre &or lo regular a la siguiente sinta.is: I/ E1ISTS (SELECT /RO3 T#bl# Cod&&o#te) BE*IN --INSTR+CCIONES ,26N EN
4HERE C#mpo
5
E#6telo no e.iste ning)n moti#o &ara cargar más tra'a*o a la 'ase de datos tra!endo todos los cam&os si lo )nico 4ue se desea sa'er es si e.iste$ Puede sustituirse sin ning)n &ro'lema el C &or HI o 1 cum&liendo con su >unción de c?e4ueo de e.istencia sin necesidad de traer datos: ;, EJ;STS DSELE"T KK ,@( "ondicionante BE;N
Ta'la
Página A de 12
ME@E "am&o
montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server
--;NST@/"";NES 12ON EN+ 'ien: ;, EJ;STS DSELE"T 1 ,@( Ta'la ME@E "am&o "ondicionante BE;N --;NST@/"";NES 12ON EN+
9m'os le darán la misma >uncionalidad sin necesidad de cargar de tra'a*o adicional e innecesario a la 'ase de datos. 7.
Use NOLOCK… sabiamente.
El uso de NLC" &uede me*orar considera'lemente la #elocidad de algunas consultas$ 9l usar NLC" en las consultas se esta'lece 4ue la lectura no “atra&a la ta'la ! esta &uede ser le6da al mismo tiem&o &or todos los usuarios$ Sin em'argo ?a! 4ue tomar en cuenta 4ue el con*unto de datos mostrados es considerado como una “lectura sucia$ Esto signi5ca 4ue los datos mostrados &odr6an no ser del todo &recisos &udiendo estos encontrarse en medio de alguna transacción del ti&o DELETE !PD#TE o $NSE%T$ 9un as6 &ara el caso de ta'las estáticas como en algunos casos las dedicadas a catálogos 5*os ! &ara escenarios de re&ortes cu!a in>ormación es más 4ue nada ?istórica ! 4ue &or lo tanto no se #erá a>ectada en el momento &odr6a su&oner una me*ora ace&ta'le so're todo si son consultas >recuentes en un am'iente multiusuario$ No se &uede usar NLC" de >orma indiscriminada en todas los Procedimientos 9lmacenados es más una cuestión de e#aluación so're escenarios &articulares$ NLC" se usa al momento de e>ectuar una consulta ! solo a&lica a las ta'las$ Su estructura seria la siguiente: SET NOCO+NT ON SELECT C#mpo, C#mpo2, . . C#mpoN /RO3 T#bl# T(NOLOC7) INNER 8OIN T#bl#2 T2(NOLOC7) ON T2.C#mpo15T.C#mpo1 INNER 8OIN T#bl#9 T9(NOLOC7) ON T9.C#mpo:5T2.C#mpo: 4HERE ;Cod&&o#te<= SET NOCO+NT O// 8.
Utilize SP_EXECUTESQL en lugar EXECUTE.
Se recomienda el uso de s&'e(ec)tes*l en lugar de la instrucción E+EC!TE o E+EC al e*ecutar código dinámico$ Esto de'ido a 4ue s&'e(ec)tes*l admite la sustitución de &arámetros es muc?o más #ersátil 4ue E+EC!TE ! además genera &lanes de e*ecución Lo 4ue aumenta las &ro'a'ilidades de 4ue al #ol#erlo a utili3ar resulte más e5ca3$
Página de 12
montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server
$ E#ada el uso de C!%S%ES Los cursores son una ?erramienta usada &ara acceder ! modi5car el resultado de una cláusula SELECT 5la &or 5la$ El &ro'lema con su uso es 4ue consumen una enorme cantidad de recursos es&ecialmente de memoria$ Siem&re 4ue sea &osi'le se de'e omitir el uso de cursores o minimi3ar su im&lementación$ 9lgunas alternati#as ! sustitutos al uso de cursores &ueden ser:
/sar ciclos ,-$LE$ /so de ta'las deri#adas$ /so de su'4ueries correlacionados$ /so de C#SE$ /so de m)lti&les consultas$ La com'inación de todas las anteriores$
1R$/tilice adecuadamente las #aria'les T$P T#L# T#L#S
TEMP%#LES
Siem&re es me*or usar #aria'les ti&o ta'la en lugar de ta'las tem&oralesU re#isemos las caracter6sticas ! di>erencias entre una ! otra$ =aria'les ti&o ta'la: Su uso en &rocedimientos almacenados &ro#oca menos re com&ilaciones$ 9&untan a estructuras de memoria &or lo 4ue &roducen menos consumo de recursos 4ue las ta'las tem&orales$ Su contenido no siem&re está en memoria$ En caso de 4ue se inserte una cantidad grande de registros esta se almacena en TEMPD$ El contenido de la #aria'le no es a>ectado &or el comando %LL#C" $ No se &ueden generar al #uelo$ No usan &aralelismo Dmulti&le t?reads en su &lan de e*ecución$ Su uso &ara ta'las de concurrencia alta o con una gran cantidad de datos &uede a>ectar su desem&eGo siendo este menor en com&aración con una ta'la tem&oral$ No se les &uede agregar 6ndices$ No se les &ueden modi5car ni truncar una #e3 creadas$
Ta'las tem&orales:
Se almacenan automáticamente en la 'ase de datos TEMPD$ Su creación &uede ocasionar 'lo4ueos en las ta'las sysob/ects sysinde(es ! a>ectar a todo el ser#idor$ Permite el uso de 6ndices$ Su uso en &rocedimientos almacenados &uede &ro#ocar una re com&ilación continua$ 9 grandes rasgos &ueden tratarse como una ta'la normal$
En general se &uede sugerir el uso de #aria'les ti&o ta'la siem&re Página V de 12
montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server
4ue sea &osi'le en lugar de ta'las tem&orales$ /se estas )ltimas solo en caso de 4ue se mane*e una cantidad mu! grande de in>ormación ! siem&re &rocurando crear su estructura &re#iamente nunca creándolas “al #uelo$ 11$;m&lementación de s4l dinamico Do codigo ro*o$ 9un4ue en general el uso de S0L Dinámico esta algo condenado de'ido a 4ue una mala im&lementación &uede resultar en un grieta de seguridad 4ue de entrada a un se#ero caso de S0L $n/ection $ La im&lementación 4ue se sugiere esta enca&sulada dentro de un &rocedimiento almacenado no es del todo dinámica &ara la a&licación cliente es decir no le &ermite estructurar sentencias li'res ! está orientada más 4ue nada a &rocedimientos almacenados cu!a >unción &rinci&al es una consulta &arametri3ada con o&ciones #aria'les$ /sare la 'ase de datos 9d#entureorWs+2RRX@2 &ara ilustrar un e*em&lo$ Pro'aremos con el siguiente &rocedimiento almacenado: CREATE PROCE+RE Be>oreRedCodeSELECT ( $Prod!t7ey INT5N+LL, $S#le
En este caso la consulta ace&ta #alores nulos ! se usa $SN!LL Dse &uede usar C#LESCE en su lugar &ero &ara ra3ones &rácticas en el e*em&lo no tiene muc?o caso ! $SN!LL es un &oco más rá&ido 4ue C#LESCE &ara dar la Ye.i'ilidad al &rocedimiento almacenado ! &oder consultar !a sea usando uno los cuatro o cual4uier com'inación de &arámetros$ 9?ora con#irtiendo la consulta en S0L Dinámico 4uedar6a de la siguiente >orma: CREATE PROCE+RE A>terRedCodeSELECT
Página X de 12
montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server ( $Prod!t7ey INT5N+LL, $S#le
94u6 el &rocedimiento almacenado solo incluirá las condiciones 4ue sean necesarias ! 4ue no tengan un #alor nulo$ 9m'os &rocedimientos de#uel#en los mismos resultados ! son e4ui#alentes el uno al otro$ /sando S0L Proler &odemos a&reciar la di>erencia en la o&timi3ación de recursos:
Página de 12
montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server
Las lecturas se reducen &ara este escenario &articular en &oco más de un XRZ la duración en un RZ ! en el uso del "P/ un cam'io total$ Por lo 4ue se a&recia el S0L Dinámico &odr6a no será tan malo como &arece ! &uede o>recernos alternati#as creati#as &ara o&timi3ar nuestras consultas$ E#alué sus consultas com&le*as ! &esadas &ara so&esar si una re>actori3ación usando SQL +inámico &uede a!udarle cuidando siem&re 4ue esta no se &reste &ara casos de S0L $n/ection$ 12$9&licar ND$CES de >orma o&timi3ada$ Es !a 'ien conocido el uso de 6ndices &ara acelerar consultas en S0L Server ! otros motores de 'ases de datos$ Estos le indican al motor de la 'ase de datos en turno 4ue esa columna o con*unto de columnas se #an a usar con ma!or >recuencia lo cual ?ace 4ue los datos se almacenen en memoria &ara contar con un acceso más e5ciente a los datos$ Es aconse*a'le 4ue los 3ndices se >ormen so're los cam&os 4ue encuentren 4rec)entemente en alguno de los si5)ientes escenarios :
Son P%$M#%6 "E6 o 7%E$8N "E6 $ Se usan >recuentemente &ara enla3ar ta'las con 9$N$ Se usan de >orma ?a'itual dentro de los &rocedimientos almacenados o en consultas em&are*ados con alguno de los siguientes comandos: ET,EEN %DE% 6 8%!P 6: TP: D$ST$NCT D9un4ue !a mencionamos 4ue estos de'en de e#itarse en lo &osi'le si no ?a! otra alternati#a ?a! 4ue considerar sus cam&os &ara la a&licación de 6ndices$ Son de uso corriente &ara 5ltrados en la clausura ,-E%E$
Pese a 4ue los 6ndices generen una me*ora en tiem&os de res&uesta tam&oco se &uede a'usar indiscriminadamente de ellos$ En contra&osición a su me*ora de tiem&o de res&uesta cada 6ndice ocu&a un es&acio e4ui#alente al n)mero de registros en la ta'la
Página 1R de 12
montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server
&enali3ando el rendimiento en la 'ase de datos al e*ecutar $NSE%T !PD#TES ! DELETE$ mita crear 6ndices con cam&os 4ue contienen &ocos #alores como a4uellos 4ue son de ti&o $T o C-#% Dde uno a cuatro ! aseg)rese de no agregar el mismo 6ndice con distintos nom'res$ El estudio &ro>undo de la creación diseGo ! mantenimiento de 6ndices esca&a del alcance de este articulo Des&ero escri'ir algo más ela'orado so're el tema de 6ndices en un >uturo sin em'argo es im&ortante 4ue se tome el tiem&o &ara com&renderlos$ Su correcta a&licación &uede >a#orecer &or muc?o el tiem&o de res&uesta de sus consultas$ 10$@ecomendaciones adicionales:
Si el &rocedimiento almacenado contiene demasiadas sentencias $7;ELSE &ara e*ecutar distintos &rocesos acorde a ciertos &arámetros resulta más e5ciente se&arar cada 'lo4ue ! enca&sularlos en &rocedimientos almacenados di>erentes Esto &ara e#itar 4ue el &lan de e*ecución cam'ie acorde al #alor del resultado de la e.&resión $7 des&erdiciando con esto la #enta*a de el &lan de e*ecución en memoria ! la &re com&ilación$ E#ite el uso de la instrucción SELECT;$NT $ 9l e*ecutarse se 'lo4uean las ta'las in#olucradas$ 9&li4ue en su lugar la sentencia $NSE%T $NT;SELECT $ Trate de usar siem&re la instrucción 9$N antes 4ue cual4uier su' consulta$ /se la instrucción ET,EEN en lugar de $N siem&re 4ue sea &osi'le$ En el caso de 4ue se use la instrucción L$"E e#ite el uso del comod6n “< al inicio de la cadena a 'uscar$ Esto de'ido a 4ue si se a&lica la ')s4ueda tendr6a 4ue leer todos los datos de la ta'la o ta'las in#olucradas &ara res&onder a la consulta$ Se recomienda 4ue e.istan al menos tres caracteres antes del comod6n$ E#ite en la medida de lo &osi'le el uso de +;ST;N"T En caso de usar la instrucción !N$N ! e.istiera la seguridad de 4ue en los SELE"T in#olucrados no se o'tendrán registros du&licados entonces lo recomenda'le en este escenario es sustituir !N$N &or !N$N #LL &ara e#itar 4ue se ?aga uso im&l6cito de la instrucción D$ST$NCT !a 4ue esta aumenta el consumo de recursos$ E#ada siem&re 4ue sea &osi'le el uso de %DE% 6 $ 9l igual 4ue D$ST$NCT consume una ele#ada cantidad de recursos$ "onsidere si es realmente necesario usarlo o si &or otro lado se &uede de*ar el ordenamiento de los resultados a la a&licación 4ue reci'irá los datos$ /se S0L Proler ! le#ante una tra3a &ara estudiar el rendimiento de los &rocedimientos almacenados de ma!or concurrencia ! #ea o&ciones &ara re>actori3ar el &roceso$ torgue es&ecial atención a las columnas CP! D)raction reads ! =rites &ara o&timi3ar el uso de memoria ! el ;[ generado$ (antenga las transacciones lo más cortas &osi'les dentro de un &rocedimiento almacenado$ Esto >a#orece a la reducción del n)mero de 'lo4ueos &romo#iendo a acelerar el rendimiento general de la 'ase de datos$ Página 11 de 12
montana.com.pe
Buenas Prácticas – Base de Datos – SQL Server
/tilice el Plan de E/ec)ción &ara re#isar sus consultas entienda como S0L Server está e*ecutando sus consultas ! e#alué sus sugerencias$ Si re4uiere almacenar #ol)menes de te.to mu! grandes &ero son menores a XRRR caracteres use el ti&o de dato >#%C-#% en lugar de TE+T$ E#alué cuidadosamente el uso de C-#% ! >#%C-#% de&endiendo si el cam&o en el 4ue se #a a usar #ar6a muc?o o no de tamaGo$ Esto &ara so&esar rendimiento de #elocidad so're rendimiento de almacenamiento$ SQL Ser#er &rocesa más rá&ido las columnas de longitud 5*a$ /se C-#% &ara columnas de &oca #ariación en longitud ! >#%C-#% &ara a4uellas 4ue no tienen una longitud esta'le o &romedio$ No use columnas con ti&os de datos 7L#T %E#L o D#TET$ME como 7%E$8N "E6 $
Página 12 de 12
