Consulta SQL tabla relacionada con SubConsulta

Ante la necesidad de realizar consultas sobre una tabla maestra y otra relacionada con diferentes cardinalidad de resultados, detecté que es posible realizar una subconsulta con diferentes criterios de registro devueltos. 

Utilizando la palabra reservada OUTER APPLY  es posible indicar una subconsulta relacionada pero indicar TOP, Order by, etc

SELECT TOP (1000) [Expediente].*, mov.ID_Oficina

  FROM [Expediente]

 OUTER APPLY 

 (select top 1 * from Movimientos where [Expediente].[IdExpedienteAuto] = Movimientos.IdExpedienteAuto ) 

as mov

SQL Server configuration manager no aparece

El SQL Server configuration manager, debería estar como una entrada dentro del grupo de programas Microsoft Sql Server xxxx, pero si no está...

Puedes ejecutarlo directamente desde el cuadro de diálogo ejecutar (que a su vez puedes invocar con la combinación de teclas Windows + r.

En dicho diálogo, en el brir escribe:

SQLServerManager14.msc para SQL Server 2017

SQLServerManager13.msc para SQL Server 2016

SQLServerManager12.msc para SQL Server 2014

SQLServerManager11.msc para SQL Server 2012

SQLServerManager10.msc para SQL Server 2008

Luego presiona Enter.

Otra opción es accesible como complemento de la consola de administración (microsoft management console: mmc.exe).

Windows + r.

Luego escribes mmc y ejecutar.

Luego menú archivo, agregar o quitar complementos y buscas: SQL Server configuration manager

y listo.

Campo calculado o columna calculada

Un campo calculado es un campo que no se almacena físicamente en la tabla. SQL Server emplea una fórmula que detalla el usuario al definir dicho campo para calcular el valor según otros campos de la misma tabla.
Un campo calculado no puede:
- definirse como "not null".
- ser una subconsulta.
- tener restricción "default" o "foreign key".
- insertarse ni actualizarse.

Puede ser empleado como llave de un índice o parte de restricciones "primary key" o "unique" si la expresión que la define no cambia en cada consulta.

 create table empleados(
  documento char(8),
  nombre varchar(10),
  domicilio varchar(30),
  sueldobasico decimal(6,2),
  cantidadhijos tinyint default 0,
  sueldototal as sueldobasico + (cantidadhijos*100)
 );

También puede ser una columna que llame a una función, pero esta debe ser determinista.
Muchas funciones dan que es no determinista, para ello se puede utilizar una palabra reservada en la definición de la función, WITH SCHEMABINDING.


Create FUNCTION [dbo].[fn_xxxxx]

(
    @String VARCHAR(32),
    @MatchExpression VARCHAR(32)

)

RETURNS VARCHAR(32)
WITH SCHEMABINDING
AS
...


Luego en la definición de la tabla

alter table tablaxxx add [campoCalculado] as dbo.fn_xxxxx( 'dato' , '^0-9');


También a esa columna se le puede crear un índice y hacer que las consultas sean óptimas. En ese caso hay que agregar la palabra reservada PERSISTED en la columna.

alter table tablaxxx add [campoCalculado] as dbo.fn_xxxxx( 'dato' , '^0-9') PERSISTED;

Luego de ello se puede crear el índice:

create index idx_campoCalculado on tablaxxx (campoCalculado);

En ocasiones nuestro SQL Server consume demasiada CPU, un buen comienzo es localizar cuales son las consultas que más sobrecargan de media nuestro servidor.

Para ello, podemos utilizar el siguiente script que lista el top ten de las consultas que más cargan la CPU de nuestro servidor SQL.

 

 

SELECT TOP 10

qs.total_worker_time/qs.execution_count as [Avg CPU Time],

SUBSTRING(qt.text,qs.statement_start_offset/2,

(case when qs.statement_end_offset = -1

then len(convert(nvarchar(max), qt.text)) * 2

else qs.statement_end_offset end -qs.statement_start_offset)/2)

as query_text,

qt.dbid, dbname=db_name(qt.dbid),

qt.objectid

FROM sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY

[Avg CPU Time] DESC

 

 

Fuente: https://linube.com/blog/las-10-consultas-que-mas-cpu-consumen-en-sql-server/

Referencias cruzadas

Un ejemplo de como crear una referencia cruzada en sql server

drop table #datos

create table #datos (fila int, columna int, valor numeric(10,2))

insert into #datos (fila, columna, valor)values(1,1,10)

insert into #datos (fila, columna, valor)values(1,2,10)

insert into #datos (fila, columna, valor)values(2,3,10)

insert into #datos (fila, columna, valor)values(3,4,10)

DECLARE @columns varchar(MAX);
DECLARE @sql nvarchar(max)

SET @columns = STUFF( ( SELECT   ',' + QUOTENAME(columna) FROM (SELECT distinct columna FROM #datos ) AS T ORDER BY columna FOR XML PATH('') ), 1, 1, '');
print @columns

 SET @sql = N'
 SELECT
   *
  FROM
  (
  SELECT  fila, valor, columna
  FROM #datos
  ) AS T
  PIVOT 
  (
  sum(valor)
  FOR columna IN (' + @columns + N')
  ) AS P order by fila;';

EXEC sp_executesql @sql;


Si el resultado de la referencia cruzada quisieras utilizarlo combinado con otra tabla deberías inviarlo a una tabla temporal

drop table ##respuesta

 SET @sql = N'
 SELECT
   * into ##respuesta
  FROM
  (
  SELECT  fila, valor, columna
  FROM #datos
  ) AS T
  PIVOT 
  (
  sum(valor)
  FOR columna IN (' + @columns + N')
  ) AS P order by fila;
 ';

EXEC sp_executesql @sql;

select * from ##respuesta;

Usuario Huérfano

Cuando se restaura una base de datos en un nuevo servidor los usuarios de la base son transferidos con ella, pero los inicios de sesión no.

Cuando se intenta crear el inicio de sesión y establecer el acceso a la base restaurada si el usuario ya existe da error.

Para solucionar esto y ligar el usuario de la base de datos con el inicio de sesión ejecutar:

ALTER USER usuariobase WITH Login = iniciosesion


Fuente: https://msdn.microsoft.com/es-AR/library/ms175475.aspx

SQL Server Habilitar FileStream

Para habilitar la opción de FileStream en Sql Server se debe ir al Sql Server Configuration Manager a SQL Server (SQLEXPRESS), al nombre de la instancia y presionar botón derecho propiedades.

Ir a pestaña FileStream - Enable FILESTREAM for ......

Luego ejecutar desde el editor de consultas

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Reiniciar el servicio y debería estar...

Sorte amigo, a gente se ve.

Obtener información de las conexiones existentes.

Si desea obtener información de las conexiones existentes a una base de datos, por el nombre del host, por el nombre de la base de datos o el inicio de sesión.


Drop table #TMP_TABLE

CREATE TABLE #TMP_TABLE (
SPID INT,
ecid int,
STATUS VARCHAR(32),
LOGINAME VARCHAR(32),
HOSTNAME VARCHAR(32),
BLK CHAR(8),
DBNAME VARCHAR(32),
CMD VARCHAR(255),
request_id int )

INSERT INTO #TMP_TABLE EXEC sp_who

SELECT COUNT(*)
FROM #TMP_TABLE
WHERE DBNAME = (nombre de la base de datos)
AND LEN(LTRIM(RTRIM(HOSTNAME))) > 0
AND HOSTNAME <> (maquina)


SELECT *
FROM #TMP_TABLE


-- Por base de datos
SELECT dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by dbname
order by conexiones desc

SELECT COUNT(*) as total
FROM #TMP_TABLE
where dbname is not null

/*

SELECT dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null and status <> 'background'
group by dbname
order by conexiones desc


SELECT COUNT(*) as total
FROM #TMP_TABLE
where dbname is not null and status <> 'background'
*/


-- por usuarios

SELECT hostname, loginame, dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname, loginame, dbname
order by hostname, loginame, dbname, conexiones


SELECT hostname, loginame, dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname, loginame, dbname
order by conexiones desc, hostname, loginame, dbname



-- por maquina y base

SELECT hostname, dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname, dbname
order by conexiones desc, hostname, dbname


-- por maquina

SELECT hostname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname
order by conexiones desc, hostname


-- activas
SELECT *
FROM #TMP_TABLE
where dbname is not null
and status='runnable'


SELECT *
FROM #TMP_TABLE
where dbname is not null
and hostname = 'una aplicación'

Conectar java por jdbc a Sql Server Express 2012

Si intentas conectar java a la base de datos SQL Server Express por medio del jdbc de microsoft dice que no se puede establecer la conexión: cannot establish a connection to jdbc.

Luego de intentar varias cosas, reinicios de maquina y preguntar a varios, comencé a realizar pruebas, llegué a la conclusión que había que activar el puerto en la parte de configuración de protocolo para SQL SQLEXPRESS, que ya había tenido un problema similar y no recordaba bien. Comencé a buscar y encontre esta solución en un foro.

open SQL Server Configuration Manager -> Protocols for SQL SQLEXPRESS, select Properties of TCP/IP. In the tab IP Addresses, set the TCPPort in section IPAll to 1433.


Espero les sirva.



John wrote:My problem is the same as yours. I spent all day crawling from site to site to find the answer but the result is still the same: "The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect". Enable Name pipes, TCP/IP, change Authentication mode, change localhost to 127.0.0.1 or ., add the Instance Name to the url, change the port, enable port and Apps in the firewall... almost everything. Its horrible! But the answer for MY problem is: open SQL Server Configuration Manager -> Protocols for SQL SQLEXPRESS, select Properties of TCP/IP. In the tab IP Addresses, set the TCPPort in section IPAll to 1433. And everthing 's OK. 
Again, i would like to say: it's the answer to my problem and may be not yours. 
Thanks!

Fuente: http://www.coderanch.com/t/306316/JDBC/databases/SQLServerException-TCP-IP-connection-host

Deshabilitar constraint en sql server

Para deshabilitar constraint

--disable el constraint de una tabla
ALTER TABLE base.tabla NOCHECK CONSTRAINT CK_nombre

hacer algo con los datos

--enable el constraint de una tabla
ALTER TABLE base.tabla CHECK CONSTRAINT CK_nombre


O la opción más rápida

--disable el constraint de una tabla
ALTER TABLE base.tabla NOCHECK CONSTRAINT All

hacer algo con los datos

--enable el constraint de una tabla
ALTER TABLE base.tabla CHECK CONSTRAINT All

Monitorear SQL Server

Para realizar un monitoreo del servidor sql server se puede hacer utilizando la vista master.dbo.sysperfinfo

Ejecutar:

select
*
from
master.dbo.sysperfinfo


Se puede realizar filtros por el campo object_name que son los diferentes contadores:

select
*
from
master.dbo.sysperfinfo
where
object_name = 'SQLServer:SQL Statistics'
order by
counter_name


Las opciones de object_name son:

SQLServer:Access Methods                                                                                                      
SQLServer:Buffer Manager                                                                                                      
SQLServer:Buffer Partition                                                                                                    
SQLServer:Cache Manager                                                                                                        
SQLServer:Databases                                                                                                            
SQLServer:General Statistics                                                                                                  
SQLServer:Latches                                                                                                              
SQLServer:Locks                                                                                                                
SQLServer:Memory Manager                                                                                                      
SQLServer:SQL Statistics                                                                                                      
SQLServer:User Settable                                                                                                         

Purgar MSDB backup y Restore - Restore lento

En un base de datos SQL Server 2000 al querer restaurar una base de datos desde el administrador corporativo se queda esperando cuadro de dialogo de restauración. O sea, al posicionar el mouse en una base presionar botón derecho Todas las tareas - Restaurar base de datos, se cuelga.

Comienzo a seguir las consultas que realiza y detecto que se hace referencia a un conjunto de tablas:

backupset
backupfile
backupfilegroup
backupmediaset
backupmediafamily
restorehistory
restorefile
restorefilegroup
logmarkhistory
suspect_pages

que al parecer poseen demasiada información y es por ello el comportamiento lento.

Al buscar información sobre esas tablas, encuentro que hay unos procedimientos para realizar mantenimiento de eso:

EXEC msdb..sp_delete_database_backuphistory 'base'
-- Elimina los movimientos de una base

EXEC msdb..sp_delete_backuphistory '01/04/2014'
-- Elimina los movimientos anteriores a una fecha

Manualmente eliminé los datos de las siguiente tablas:

delete from msdb..backupmediafamily

delete from msdb..backupfile

delete from msdb..restorefile

delete from msdb..restorefilegroup

delete from msdb..restorehistory

delete from msdb..backupset

delete from msdb..backupmediafamily

delete from msdb..backupmediaset

delete from msdb..backupmediafamily

Modificar estructuras de tablas en SQL Server 2008

Por defecto el entorno de administración de Sql Server 2008 no permite realizar modificaciones en estructuras de tablas que requieran la re-creación de la misma, por ejemplo agregar a un campo que sea auto incremental (identity).

Si se intenta realizar esta modificación saldrá el siguiente mensaje:

Para permitir realizar la modificaciones ir a Herramientas, opciones del menú en la opción de diseño, tablas opciones desmarcar la "Prevent saving changes that require table re-creation"

Luego de ello dejará realizar modificaciones en las estructuras si necesidad de generar los script.

Configurar SQL Server 2008 Express para aceptar conexiones remotas

El título es algo extenso y la realidad que el problema también es extenso, no así la solución.

He instalado varios SQL Server 2008 Express y siempre tuve inconvenientes para aceptar conexiones remotas a éste servicio, pero luego de tantas veces encontré los pasos correctos y aquí los comparto.

Como primer inconveniente los protocolos de red están deshabilitados por defecto, y por otro lado la configuración no es la adecuada, para resolver esto vamos pasos a paso:

Primero:

Habilitar el protocolo TCP/IP, desde el SQL Server Configuration Manager hacemos clic en el nodo "Protocols for SQLEXPRESS " y con el botón derecho seleccionamos Enable.

Segundo:

Predeterminamos en que puerto va escuchar el sql server, se puede dejar dinámico pero vamos a tener problemas con el firewall.

Desde el SQL Server Configuration Manager hacemos clic en el nodo "Protocols for SQLEXPRESS " y con el botón derecho seleccionamos Propiedades.

Vamos IPAdress a IPAll, en el valor de TCP Dynamic Port lo limpiamos y escribimos un valor de puerto en TCP Port, por ejemplo 1433 que es el puerto por default de SQL Server.

Como paso tres:

Luego vamos al firewall de windows y agregamos una excepción para el puerto 1433 y listo....

Este es el resumen si tengo más tiempo lo voy a actualizar para poner más minucioso los pasos.

Formato de fechas con PHP, Apache y Sql server

Esta es una curiosidad que descubrimos luego de instalar un server windows 2003, el apache como servidor web y el sitio realizado en PHP.

Normalmente escribo los post muy reducidos para contar como solucionar una cosa particular y listo, y esta no va a ser la excepción.

Resulta que una vez instalado el server 2003, el apache y el sitio en php las fechas se comportaban de manera no esperada.

Luego de indagar a los programadores como habían realizado las funciones, no surge ninguna curiosidad, por lo tanto pensamos si windows esta igual, el apache igual y php igual que otro server funcionando. ¿Qué puede estar faltando?

Bueno la solución esta en instalar las herramientas clientes de sql server.

Si a alguien le pasa acá esta la solución.

Error Servidor: mensaje 8944 sql server

Si aparece este error se puede solucionar realizando una reparación de la base con el siguiente comando, previamente pasando la base a single user. Para pasar la base a single user pueden realizarlo desde el Administrador Corporativo, propiedades de la base, opciones, restringir acceso, Un único usuario.

Luego:

use master
go

dbcc checkdb ( basededatos, 'repair_rebuild' )
go

Una alternativa para que el proceso no se haga tan largo y pesado es ejecutar con la opción NOINDEX y luego verificar en que tablas se produce algún error y luego detectado en que tablas hay error, ejecutar

dbcc checkTable ( tablaConError , 'repair_rebuild')

Recuperar espacio y desfragmenta los índices

Si quisieramos reducir espacios no utilizados o reservado de un tabla deberíamos realizar lo siguiente.

Primero verificar si esa tabla efectivamente esta ocupando espacio reservado o no utilizado.

Desde el analizador de consulta

use base_de_datos
EXEC sp_spaceused tabla_a_consultar

Nos mostrará el espacio no utilizado (noused)

Para reducir espacio no utilizado, se puede ejecutar

DBCC CLEANTABLE ( base_de_datos , tabla_a_consultar )

Recupera espacio correspondiente a columnas de longitud variable y a columnas de texto que se han quitado.

Luego de ello,

DBCC DBREINDEX (tabla_a_consultar, '', 95)

Desfragmenta los índices agrupados y secundarios de la tabla o la vista especificada, esto no solo reorganiza la tabla, sino que recupera espacio no utilizado.

Luego de ello, si se redujerón efectivamente las tablas figurará en el archivo de datos un espacio no utilizado un mayor valor. Para reducir este valor del archivo de datos se deberá ejecutar el comando

dbcc shrinkfile ( nombre archivo logico base, tamaño)

El valor del tamaño no deberá superar el utilizado por los datos.

Ver: http://marcelocolombani.blogspot.com/2008/04/reducir-el-archivo-de-log-de-una-base.html

Espacio ocupado por tablas en Sql Server

Si queremos saber cuales tablas tienen espacio desperdiciado o cual es la tabla más grande, se puede utilizar el siguiente procedimiento almacenado.

-- Create the temporary table...
CREATE TABLE #tblResults
(
[name] nvarchar(20),
[rows] int,
[reserved] varchar(18),
[reserved_int] int default(0),
[data] varchar(18),
[data_int] int default(0),
[index_size] varchar(18),
[index_size_int] int default(0),
[unused] varchar(18),
[unused_int] int default(0)
)

-- Populate the temp table...
EXEC sp_MSforeachtable @command1=
"INSERT INTO #tblResults
([name],[rows],[reserved],[data],[index_size],[unused])

EXEC sp_spaceused '?'"

-- Strip out the " KB" portion from the fields
UPDATE #tblResults SET
[reserved_int] = CAST(SUBSTRING([reserved], 1,
CHARINDEX(' ', [reserved])) AS int),
[data_int] = CAST(SUBSTRING([data], 1,
CHARINDEX(' ', [data])) AS int),
[index_size_int] = CAST(SUBSTRING([index_size], 1,
CHARINDEX(' ', [index_size])) AS int),
[unused_int] = CAST(SUBSTRING([unused], 1,
CHARINDEX(' ', [unused])) AS int)

-- Return the results...
SELECT * FROM #tblResults

Se puede obtener tablas que tienen espacio desperdiciado....

Ejemplo: SELECT * FROM #tblResults where unused_int > 20000

Si queremos saber el espacio total utilizado de la base y el espacio libre, ejecutamos el siguiente procedimiento.

EXEC sp_spaceused

En muchas ocasiones cuando miramos desde el Administrador Corporativo, y nos informa que existe un espacio no utilizado muy grande y si ejecutamos la consulta anterior y totalizamos el espacion no utilizado nos da un valor diferente. A fin de actualizar esta información podemos ejecutar el procedimiento sp_spaceused con la opción de actualizar estadísticas.

EXEC sp_spaceused @updateusage = 'TRUE'



fuente: http://oberdata.com.ar/pred/blogs/ob/archive/2007/05/06/31157.aspx

Error 701 al intentar realizar backup del log

Al intentar realizar un backup del log de una base de datos denominada sistemas:

BACKUP LOG [Sistemas] TO [sistemasLog] WITH INIT , NOUNLOAD , NAME = N'Copia de seguridad Sistemas Log', SKIP , STATS = 10, NOFORMAT

arroja el siguiente error:
Servidor: mensaje 701, nivel 17, estado 1, línea 3
Memoria de sistema insuficiente para ejecutar esta consulta.
Servidor: mensaje 3013, nivel 16, estado 1, línea 3
Fin anómalo de BACKUP LOG.

Según estuve viendo esto puede ser por:

1. Posee asignada muy poca memoria en el server sql, esto se puede ver en propiedades del servidor, memorias.
2. O el servidor windows posee poca memoria virtual. Si esta administrada la memoria virtual por el sistema, entonces puede ser que tiene poco espacio en disco. Si esta asignado con un tamaño personalizado, entonces ampliar.
   La solución sería dejar como administrado por el sistema y que ocupe todo el espacio necesario. O dejar en tamaño fijo máximo 3096 MB.

Entonces cambiar las opciones y resetear el servidor.

Vincular Servidor SQL Server

Si quieres acceder desde a un servidor SQL Server a otro SQL Server, este puede ser el caso de uno de producción a otro de prueba, debes vincular dichos servidores.

Como realizarlo: te vas al grupo seguridad, luego buscas servidores vinculados, presionas botón derecho y seleccionas nuevo servidor vinculado.

Escribes el nombre del servidor a vincular, luego en seguridad puedes optar por "Se realizarán con el contexto actual de inicio de sesión".