Download - Sql Server
![Page 1: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/1.jpg)
Mejores PrácticasMejores Prácticasdede
SQL Server.SQL Server.
Gonzalo SayagoGonzalo Sayago
![Page 2: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/2.jpg)
AgendaAgenda
Tipos de instrucciones.Tipos de instrucciones. Indices.Indices. Formalizando el Join.Formalizando el Join. Relaciones Muchos a Muchos.Relaciones Muchos a Muchos. Fechas.Fechas. Insert con indices.Insert con indices. Union.Union.
![Page 3: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/3.jpg)
SQLSQL
DatosDatos
DML (Data Manipulation DML (Data Manipulation Language)Language)
SelectSelect InsertInsert UpdateUpdate Delete.Delete.
TCL (Transactional Control TCL (Transactional Control Language)Language)
Begin TransBegin Trans CommitCommit Rollback.Rollback.
AdministraciónAdministración
DDL (Data Definition Language)DDL (Data Definition Language) CreateCreate AlterAlter Drop.Drop.
DCL (Data Control Language)DCL (Data Control Language) GrantGrant Revoke.Revoke.
![Page 4: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/4.jpg)
¿Qué es índice?¿Qué es índice?
Es una estructura de datos que mejora la Es una estructura de datos que mejora la velocidad de acceso a los registros de una velocidad de acceso a los registros de una tabla.tabla.
![Page 5: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/5.jpg)
IndicesIndicesBranchBranch
IndiceIndice ConsultaConsulta
IdId Select Id, Name From BranchSelect Id, Name From Branch
Where Id = @IdWhere Id = @Id
NameName Select Id, Name From BranchSelect Id, Name From Branch
Where Name = @NameWhere Name = @Name
Id+NameId+Name Select Id, Name From BranchSelect Id, Name From Branch
Where Id = @Id and Name like @Name%Where Id = @Id and Name like @Name%
Name+IdName+Id Select Id, Name From BranchSelect Id, Name From Branch
Where Id = @Id and Name = @NameWhere Id = @Id and Name = @Name
+ Indices
![Page 6: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/6.jpg)
Indice (Arbol B+)Indice (Arbol B+)
Seek >=2Seek >=2
http://www.seanster.com/BplusTree/BplusTree.html
Seek 6Seek 6
Clustered Index
![Page 7: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/7.jpg)
Bookmark LookupBookmark Lookup
Select [Name] From [Area] Where [Id] = 3Select [Name] From [Area] Where [Id] = 3
![Page 8: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/8.jpg)
Bookmark LookupBookmark Lookup
http://www.seanster.com/BplusTree/BplusTree.html
Area
Select [Name] From [Area] Where [Id] = 3Select [Name] From [Area] Where [Id] = 3
![Page 9: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/9.jpg)
TIPTIPSelect Name From Branch Where (Id < 2) Or (Id > 2)Select Name From Branch Where (Id < 2) Or (Id > 2)
Select Name From Branch Where (Id <> 2)Select Name From Branch Where (Id <> 2)
Index Scan
Index Seek
![Page 10: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/10.jpg)
Cluster y Non Cluster IndexCluster y Non Cluster Index Puede haber un solo índice Clustered por Puede haber un solo índice Clustered por
tabla.tabla. Los índices Clustered están asociados a Los índices Clustered están asociados a
la tabla.la tabla.
Los índices non clustered puede haber Los índices non clustered puede haber mas de uno por tabla.mas de uno por tabla.
![Page 11: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/11.jpg)
¿Cuándo crear un índice?
![Page 12: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/12.jpg)
IndicesIndicesSelect Name From Area
Where Campo1 = @Param1 And Campo2 = @Param2
Indice sobre Campo1
Indice sobre Campo2
Indice sobre Campo1 y Campo2
Indice compuesto sobre Campo1 y Campo2
![Page 13: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/13.jpg)
SelectSelect
![Page 14: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/14.jpg)
Campos de un SelectCampos de un SelectTabla.columnaTabla.columnaVista.columnaVista.columnaTable_Alias.columnaTable_Alias.columna
Tabla.columna as AliasTabla.columna as AliasVista.columna as AliasVista.columna as AliasTable_Alias.columna as AliasTable_Alias.columna as Alias
Tabla.columna AliasTabla.columna AliasVista.columna AliasVista.columna AliasTable_Alias.columna AliasTable_Alias.columna Alias
columnacolumna
Expresion as AliasExpresion as AliasExpresion AliasExpresion AliasAlias = ExpresionAlias = ExpresionExpresionExpresion
**
Tabla.*Tabla.*
Vista.*Vista.*
Alias.*Alias.*
![Page 15: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/15.jpg)
Select [Id], [Name]Select [Id], [Name]
From [Branch]From [Branch]
Order By [Name]Order By [Name]
BranchBranch
SelectSelect
Resultado
![Page 16: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/16.jpg)
Select [Hola] = [Id],Select [Hola] = [Id],
[Como estas?] = [Name][Como estas?] = [Name]
From [Branch]From [Branch]
Order By [Como estas?]Order By [Como estas?]
BranchBranch
SelectSelect
Resultado
![Page 17: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/17.jpg)
Select [Branch].[Id],Select [Branch].[Id],[Branch].[Name], [Branch].[Name], [Area].[BranchId],[Area].[BranchId],[Area].[Id], [Area].[Id], [Area].[Name][Area].[Name]
From [Branch], [Area]From [Branch], [Area]
BranchBranch
Branch Area
Index Scan
Index Scan
Cuantos registros trae esta consulta?
Area
Los graficos son iguales
![Page 18: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/18.jpg)
Producto CartesianoProducto Cartesiano
Dados los conjuntos A y B, se llama producto cartesiano Dados los conjuntos A y B, se llama producto cartesiano de A con B al conjunto AxB definido por:de A con B al conjunto AxB definido por:
AxB = {(x, y) / x Є A e y Є B}AxB = {(x, y) / x Є A e y Є B}
Es decir:Es decir:
BranchBranchA BAxBAxB
Select [Branch].[Id], [Branch].[Name], [Area].[BranchId], [Area].[Id], [Area].Select [Branch].[Id], [Branch].[Name], [Area].[BranchId], [Area].[Id], [Area].[Name] [Name] From [Branch], [Area]From [Branch], [Area]
Area
![Page 19: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/19.jpg)
Select [Branch].[Id], [Branch].[Name], Select [Branch].[Id], [Branch].[Name], [Area].[Id], [Area].[Name], [Area].[Area].[Id], [Area].[Name], [Area].[BranchId][BranchId]
From [Branch], [Area]From [Branch], [Area]WhereWhere[Branch].[Id] = [Area].[BranchId][Branch].[Id] = [Area].[BranchId]
AreaBranchBranch
O mas conocido como:O mas conocido como:
Select [Branch].[Id], [Branch].[Name], [Area].[Id], [Area].[Name], [Area].[BranchId]Select [Branch].[Id], [Branch].[Name], [Area].[Id], [Area].[Name], [Area].[BranchId]
FromFrom [Branch] Inner Join [Area] On [Branch].[Id] = [Area].[BranchId][Branch] Inner Join [Area] On [Branch].[Id] = [Area].[BranchId]
Branch Area
![Page 20: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/20.jpg)
Select [Branch].[Id], [Branch].[Name], Select [Branch].[Id], [Branch].[Name], [Area].[Id], [Area].[Name], [Area].[Area].[Id], [Area].[Name], [Area].[BranchId][BranchId]
From [Branch], [Area]From [Branch], [Area]WhereWhere[Branch].[Id] = [Area].[BranchId][Branch].[Id] = [Area].[BranchId]
AreaBranchBranch
Index Scan
Index Seek
Select [Branch].[Id], [Branch].[Name], [Area].[Id], [Area].[Name], [Area].[BranchId]Select [Branch].[Id], [Branch].[Name], [Area].[Id], [Area].[Name], [Area].[BranchId]
FromFrom [Branch][Branch]
Inner Join [Area] On [Branch].[Id] = [Area].[BranchId]Inner Join [Area] On [Branch].[Id] = [Area].[BranchId]
Los gráficos son diferentes.
![Page 21: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/21.jpg)
Muchos a MuchosMuchos a Muchos
FacturaFactura ArtículoArtículo ArtículoFacturaArtículoFactura
Quiero obtener todos los datos de la Quiero obtener todos los datos de la factura 1 con todos los artículos.factura 1 con todos los artículos.
![Page 22: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/22.jpg)
Muchos a MuchosMuchos a Muchos
Select F.*, A.* From Factura FSelect F.*, A.* From Factura F
Inner Join ArtículoFactura FA FA.FacturaId = F.IdInner Join ArtículoFactura FA FA.FacturaId = F.Id
Inner Join Artículo A on FA.ArticuloId = A.IdInner Join Artículo A on FA.ArticuloId = A.Id
Where F.Id = 1Where F.Id = 1
==
Select F.*, A.* From Factura FSelect F.*, A.* From Factura F
Inner Join ArtículoFactura FA FA.FacturaId = F.IdInner Join ArtículoFactura FA FA.FacturaId = F.Id
Inner Join Artículo A on FA.ArticuloId = A.IdInner Join Artículo A on FA.ArticuloId = A.Id
Where FA.FacturaId = 1Where FA.FacturaId = 1
![Page 23: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/23.jpg)
Muchos a MuchosMuchos a Muchos
Select F.* From Factura FSelect F.* From Factura F
Left Join ArtículoFactura FA FA.FacturaId = F.IdLeft Join ArtículoFactura FA FA.FacturaId = F.Id
Where FA.Cantidad Is Null Or FA.Cantidad > 100Where FA.Cantidad Is Null Or FA.Cantidad > 100
==
Select F.* From Factura FSelect F.* From Factura F
Left Join ArtículoFactura FA FA.FacturaId = F.IdLeft Join ArtículoFactura FA FA.FacturaId = F.Id
Where FA.FacturaId Is Null Or FA.Cantidad > 100Where FA.FacturaId Is Null Or FA.Cantidad > 100
![Page 24: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/24.jpg)
Constantes, Parametros y ValoresConstantes, Parametros y Valores
![Page 25: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/25.jpg)
Constantes, Parametros y ValoresConstantes, Parametros y Valores
Create Procedure Ejemplo1Create Procedure Ejemplo1
@id Int@id Int
AsAs
Select Name From Area Where Id = @IdSelect Name From Area Where Id = @Id
Create Procedure Ejemplo2Create Procedure Ejemplo2
AsAs
Select Name From Area Where Expire > GetDate()Select Name From Area Where Expire > GetDate()
![Page 26: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/26.jpg)
Date y TimeDate y Time
Tipo de dato Intervalo Precisión Tamaño
datetime Del 1 de enero de 1753hasta el 31 de diciembre de 9999
3,33Milisegundos
8 bytes
smalldatetime
Del 1 de enero de 1900hasta el 6 de junio de 2079
1 minuto 4 bytes
![Page 27: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/27.jpg)
Funciones DateAdd y DateDiffFunciones DateAdd y DateDiff
Select Nombre From Promocion WhereVigenteDesde <= GetDate()
AndDateAdd(day,1,VigenteHasta)>=GetDate()
Select Nombre From Promocion WhereVigenteDesde <= GetDate()
AndVigenteHasta >= GetDate()-1
![Page 28: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/28.jpg)
Funciones DateAdd y DateDiffFunciones DateAdd y DateDiff
Supongamos que las promociones duran 30 dias:
Select Nombre From Promocion WhereDateDiff(day, VigenteDesde, Getdate()) < 30
Select Nombre From Promocion WhereGetDate() - 30 < VigenteDesde
![Page 29: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/29.jpg)
Date y TimeDate y TimeCreate Procedure Fechas
@date_from smalldatetime=null, @date_to smalldatetime=null
AsSelect Name From Area Where
(CreateDate > @date_from Or @date_from Is Null)And (CreateDate < @date_to Or @date_to Is Null)
Create Procedure Fechas@date_from smalldatetime=‘19000101’, @date_to smalldatetime=‘20790606’
AsSelect Name From Area Where
(CreateDate > @date_from)And (CreateDate < @date_to)
![Page 30: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/30.jpg)
Date y TimeDate y TimeCreate Procedure Fechas
@date_from smalldatetime=null, @date_to smalldatetime=null
AsSelect Name From Area Where
(CreateDate > @date_from Or @date_from Is Null)And (CreateDate < @date_to Or @date_to Is Null)
Create Procedure Fechas@date_from smalldatetime=‘19000101’, @date_to smalldatetime=‘20790606’
AsSelect Name From Area Where
(CreateDate > @date_from)And (CreateDate < @date_to)
![Page 31: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/31.jpg)
Date y TimeDate y TimeCreate Procedure Fechas
@date_from smalldatetime=null, @date_to smalldatetime=null
AsSelect Name From Area Where
(CreateDate > @date_from Or @date_from Is Null)And (CreateDate < @date_to Or @date_to Is Null)
Create Procedure Fechas@date_from smalldatetime=‘19000101’,@date_to smalldatetime=‘20790606’
AsSelect Name From Area Where
(CreateDate between @date_from and @date_to)
![Page 32: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/32.jpg)
Insert Statement
![Page 33: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/33.jpg)
Insert
Insert Into [Area] ([Name], [BranchId]) Values ('Plan', 1)
![Page 34: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/34.jpg)
Union Statement
![Page 35: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/35.jpg)
Union All
Union
La Union NO garantiza que los resultados estén
ordenados.
Para eso hay que usar un Order By.
![Page 36: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/36.jpg)
Ultimo TIPUltimo TIPSelect Id, Name from AreaSelect Id, Name from Area
Where (Name = 'Default' and @Id Is Null) Or (Id = @Id)Where (Name = 'Default' and @Id Is Null) Or (Id = @Id)
==
Select Id, Name from Area Where (Name = 'Default' and @Id Is Null)Select Id, Name from Area Where (Name = 'Default' and @Id Is Null)Union AllUnion AllSelect Id, Name from Area Where (Id = @Id)Select Id, Name from Area Where (Id = @Id)
==
If @Id Is NullIf @Id Is NullSelect Id, Name from Area where Name = 'Default'Select Id, Name from Area where Name = 'Default'
ElseElseSelect Id, Name from Area where Id = @IdSelect Id, Name from Area where Id = @Id
![Page 37: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/37.jpg)
PreguntasPreguntas
??
![Page 38: Sql Server](https://reader033.vdocuments.us/reader033/viewer/2022050808/5560cb31d8b42a08088b49d8/html5/thumbnails/38.jpg)
The EndThe End