Cómo devolver registros aleatoriamente en SQL Server

Cómo devolver registros aleatoriamente en SQL Server

En este post te voy a mostrar dos opciones que tenemos en SQL para hacer una búsqueda aleatoria de registros en una tabla y compararlas.

Opción ORDER BY con NEWID()

Esta es una opción de las mas encontradas en internet como ejemplo de búsqueda y consta simplemente en hacer tu consulta y luego agregar un Order BY NEWID() como es el siguiente ejemplo:

USE ADVENTUREWORKS2019 
GO

SELECT TOP (100) * 
FROM
SALES.SALESORDERDETAIL 
ORDER BY NEWID()

Opción con TABLESAMPLE

Esta otra opción utiliza la función TABLESAMPLE la cual es nativa y pensada para hacer esto, la misma funciona con paginas de registros y también se le puede poner cantidades pero no suelen ser exactas y lo que debemos hacer si por ejemplo queremos traer un random de 100 registros es poner números mas altos como el caso del ejemplo siguiente

SELECT TOP (100) * 
FROM
SALES.SALESORDERDETAIL TABLESAMPLE (1000 ROWS)

Performance

Ambos casos que he mostrado darán los mismos resultados funcionales, ¿ pero que pasara con la performance? bueno para eso mostrare ambos planes de ejecución y algunos detalles

Usando NEWID()
Usando TableSample

Al observar ambos planes vemos una diferencia sustancial, ahora para la misma operación voy a ver el costo de cada uno de ellos

MétodoCosto Plan ejecución
NEWID()3.34
TABLESAMPLE0.0041

Sin ninguna duda que la diferencia que se observo en los planes de ejecución se ve muy marcada en los costos en donde usar el TABLESAMPLE es mucho mas eficiente que el otro método

Cómo devolver registros aleatoriamente en SQL Server

Permisos en SQL Dinámico

El otro día en un grupo de Facebook un participante hizo una pregunta relacionada a un caso en donde tenia un Procedimiento Almacenado con SQL Dinámico y que al ejecutarlo le daba errores de permisos sobre los objetos que usaba ese procedimiento.

Esto técnicamente se da porque el SQL Dinámico pasa las credenciales del contexto de ejecución del SP el cual es Caller por defecto, para mas información puedes acceder al siguiente enlace

En este post voy a hacer una demostración paso a paso de este caso y cual seria la forma de resolverlo.

Paso 1: Creamos los logins y usuarios en nuestra base de datos

En este primer paso vamos a crear dos (2) logins y usuarios a nuestra base de datos, uno de ellos con permisos restringidos y el otro con mas permisos.

USE AdventureWorks2019 
GO
-- CREAMOS UN NUEVO LOGIN
CREATE LOGIN TRIGGERDB WITH PASSWORD ='PASSW@RD'
GO
CREATE LOGIN TRIGGERDB_FULL WITH PASSWORD ='PASSW@RD'
GO
-- CREAMOS EL USUARIO EN LA BASE DE DATOS
CREATE USER TRIGGERDB 
GO
CREATE USER TRIGGERDB_FULL 
GO

ALTER ROLE [db_owner] ADD MEMBER [TRIGGERDB_FULL]  -- PERMISO DE DBOWNER
GO

Paso 2: Creación de Procedimientos almacenados

En este paso vamos a crear dos procedimientos almacenados , uno de ellos común y el otro usando SQL Dinámico

Luego al login TRIGGERDB le vamos a dar permisos de ejecución sobre los SP pero no así sobre las tablas

CREATE OR ALTER PROC DBO.USP_CUSTOMER AS
 SELECT * FROM SALES.Customer 
GO
-- CREAMOS OTRO STORE QUE CONSUME LA TABLA PERO CON SQLDINAMICO
CREATE OR ALTER PROC DBO.USP_CUSTOMER_DINAMICO AS
 DECLARE @N NVARCHAR(50)
 SET @N = N'SELECT * FROM SALES.Customer'
 EXECUTE SP_EXECUTESQL @N 
GO
-- TRIGGERDB SOLO TIENE PERMISOS A LOS STORES
GRANT EXECUTE ON DBO.USP_CUSTOMER TO TRIGGERDB
GRANT EXECUTE ON DBO.USP_CUSTOMER_DINAMICO TO TRIGGERDB 
GO

Paso 3: Pruebas de funcionalidad

Ahora probaremos los dos procedimientos almacenados usando el login TRIGGERDB y además los permisos

EXECUTE AS LOGIN = 'TRIGGERDB'
SELECT SUSER_SNAME()

Hacemos un Select a la tabla con Triggerdb

SELECT * FROM SALES.Customer 

Ejecutamos el primer procedimiento normal y vemos que funciona porque el login tiene permisos.

Msg 229, Level 14, State 5, Line 78
The SELECT permission was denied on the object ‘Customer’, database ‘AdventureWorks2019’, schema ‘Sales’.

EXEC DBO.USP_CUSTOMER

Ejecutamos el otro SP con SQL Dinámico sobre la misma tabla y falla por mas que tengamos permisos sobre el mismo , al ser SQL Dinámico se pasan las credenciales del Caller (TRIGGERDB) al objeto que usa el SP_executeSQL y como este login no tiene permisos sobre dicha tabla va a dar error


EXEC DBO.USP_CUSTOMER_DINAMICO

Msg 229, Level 14, State 5, Line 78
The SELECT permission was denied on the object ‘Customer’, database ‘AdventureWorks2019’, schema ‘Sales’.

REVERT 

Paso 4: Resolviendo el problema

Para poder resolver estos problemas sin la necesidad de darle permisos al Login TRIGGERDB sobre la tabla al usar SQL Dinámico lo que debemos hacer es cambiar el contexto de ejecución del SP de caller a Owner o Login , yo lo voy a cambiar indicándole un login que si tiene permisos sobre la tabla por mas que luego lo llame otro que no tiene permisos.

ALTER PROC DBO.USP_CUSTOMER_DINAMICO
WITH EXECUTE AS 'TRIGGERDB_FULL' -- CAMBIO DE CONTEXTO A OWNER
AS
DECLARE @N NVARCHAR(50)
SET @N = N'SELECT * FROM SALES.Customer'
EXECUTE SP_EXECUTESQL @N 

Ahora solo nos toca volver a probar (correr el código por partes)

EXECUTE AS LOGIN = 'TRIGGERDB'
SELECT SUSER_SNAME()

SELECT * FROM SALES.Customer  -- FALLA PORQUE NO TENEMOS PERMISO

EXEC DBO.USP_CUSTOMER -- FUNCIONA
EXEC DBO.USP_CUSTOMER_DINAMICO -- FUNCIONA

REVERT

Conclusiones

Si usamos SQL Dinámico y nuestro user que llama al SP no tiene permisos sobre los objetos del mismo y no queremos darlos, entonces lo que debemos hacer es cambiar el contexto de ejecución de ese SP, Función o Trigger

Cómo devolver registros aleatoriamente en SQL Server

Cómo depurar registros de una tabla y moverlos a otra

En varias oportunidades podemos tener la necesidad de depurar alguna tabla grande, esta operación puede ser compleja y hasta impactar la performance de tu base de datos.

En el siguiente tutorial te muestro paso a paso cómo podes hacer un proceso para mover registros viejos a una tabla de historia afectando lo menos posible la performance de tu SQL.

Comparto también el código utilizado en dicho video tutorial

USE master
GO

DROP DATABASE DATOS 
DROP DATABASE DATOS_HISTORY 

CREATE DATABASE DATOS
CREATE DATABASE DATOS_HISTORY

USE DATOS
GO
-- CREAMOS UNA TABLA Y LA LLENAMOS CON 4M DE REGISTROS
SELECT TOP 4000000  
H.AccountNumber,
h.BillToAddressID,
h.CustomerID,
h.OrderDate,
h.SalesOrderNumber,
h.TotalDue       
INTO DBO.VENTAS FROM AdventureWorks2019.Sales.SalesOrderHeader  H
CROSS JOIN AdventureWorks2019.sys.columns C

-- CREAMOS A LA BASE HISTORY LA TABLA VACIA
USE DATOS_HISTORY 
GO

SELECT TOP 0 * INTO DBO.VENTAS FROM DATOS.DBO.VENTAS   

USE DATOS 
GO

-- CALCULAMOS LAS ORDENES MENORES A 2007
SELECT COUNT(1)
FROM DBO.VENTAS 
WHERE OrderDate >= '20130101' and OrderDate < '20140101'

SELECT COUNT(1)
FROM DBO.VENTAS 

use DATOS 

DBCC TRACEON (1224) -- NO ESCALAN LOS LOKEOS PARA ESTA SESION

WHILE 1 = 1
BEGIN
  DELETE TOP (50000) FROM DBO.VENTAS 
    OUTPUT
	  Deleted.AccountNumber,
      Deleted.BillToAddressID,  
      Deleted.CustomerID,
      Deleted.OrderDate,
      Deleted.SalesOrderNumber,
      Deleted.TotalDue    
	
      INTO DATOS_HISTORY.dbo.VENTAS 
      WHERE OrderDate >= '20130101' 
	  and OrderDate < '20140101'

  IF @@ROWCOUNT = 0 BREAK;
END;
SELECT COUNT(1) FROM DBO.VENTAS 
SELECT COUNT(1) FROM DATOS_HISTORY.DBO.VENTAS
Cómo devolver registros aleatoriamente en SQL Server

¿Porque no usa SQL Server mis índices?

En algunas oportunidades observamos que SQL no utiliza nuestros índices creados.

En el siguiente tutorial te explico cuales son las principales causas.

También te comparto los ejemplos utilizados:

USE AdventureWorks2019 
GO

SP_HELPINDEX 'Sales.SalesOrderDetail'

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 897;
 

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID +1  = 898;

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 897;


SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE upper(ProductID) = '897';


SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 897;
 
SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = 870;

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WITH (INDEX=ix_salesorderdetail_productid)
WHERE ProductID = 870;

Cómo devolver registros aleatoriamente en SQL Server

Usando String_split() para pasar una lista de valores a tus Procedimientos almacenados

En este tutorial te voy a mostrar cómo podes usar la función de TSQL string_split() para poder pasar una lista de valores a tus procedimientos almacenados.

También comparto el código utilizado en la demostración

USE ADVENTUREWORKS2019
 GO

 SELECT * FROM STRING_SPLIT('HOLA,MUNDO',',');
 
USE [MASTER]
GO
ALTER DATABASE [ADVENTUREWORKS2019] SET COMPATIBILITY_LEVEL = 120
GO

 USE ADVENTUREWORKS2019
 GO

 SELECT * FROM STRING_SPLIT('HOLA,MUNDO',',');

 ALTER DATABASE [ADVENTUREWORKS2019] SET COMPATIBILITY_LEVEL = 150
 GO

 CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_STRING
 @VALORES VARCHAR(8000)
 AS
 SET NOCOUNT ON

 SELECT * FROM SALES.SALESORDERDETAIL D 
 WHERE  D.SALESORDERID
 IN
 (
    SELECT VALUE FROM 
	STRING_SPLIT(@VALORES,',') 
	
 )
 GO

 
--- USANDO FUNCIONES
CREATE OR ALTER   FUNCTION [dbo].[SplitInts]  
(  
   @List      VARCHAR(MAX),  
   @Delimiter VARCHAR(255)  
)  
RETURNS TABLE  
AS  
  RETURN ( SELECT Item = Item FROM  
      ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)')  
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'  
        + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.')  
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y  
      WHERE Item IS NOT NULL  
  );
GO


CREATE OR ALTER FUNCTION [DBO].[SPLITSTRING]
(    
      @INPUT NVARCHAR(MAX),
      @CHARACTER CHAR(1)
)
RETURNS @OUTPUT TABLE (
      ITEM NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @STARTINDEX INT, @ENDINDEX INT

      SET @STARTINDEX = 1
      IF SUBSTRING(@INPUT, LEN(@INPUT) - 1, LEN(@INPUT)) <> @CHARACTER
      BEGIN
            SET @INPUT = @INPUT + @CHARACTER
      END

      WHILE CHARINDEX(@CHARACTER, @INPUT) > 0
      BEGIN
            SET @ENDINDEX = CHARINDEX(@CHARACTER, @INPUT)

            INSERT INTO @OUTPUT(ITEM)
            SELECT SUBSTRING(@INPUT, @STARTINDEX, @ENDINDEX - 1)

            SET @INPUT = SUBSTRING(@INPUT, @ENDINDEX + 1, LEN(@INPUT))
      END

      RETURN
END

SELECT * FROM DBO.SPLITINTS( 'HOLA,MUNDO', ',') 
SELECT * FROM DBO.SPLITSTRING( 'HOLA,MUNDO', ',') 


CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_FUNCTION
 @VALORES VARCHAR(8000)
 AS
 SET NOCOUNT ON

 SELECT * FROM SALES.SALESORDERDETAIL D 
 WHERE  D.SALESORDERID
 IN
 (
    SELECT * FROM 
	DBO.SPLITINTS(@VALORES,',') 
	
 )
 GO


CREATE OR ALTER PROCEDURE DBO.USP_SPLIT_FUNCTION2
 @VALORES VARCHAR(8000)
 AS
 SET NOCOUNT ON

 SELECT * FROM SALES.SALESORDERDETAIL D 
 WHERE  D.SALESORDERID
 IN
 (
    SELECT * FROM 
	DBO.SPLITSTRING(@VALORES,',') 
	
 )
 GO



-- PRUEBAS 
 EXEC DBO.USP_SPLIT_STRING 
 '43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'
 
 EXEC DBO.USP_SPLIT_FUNCTION2
 '43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'


EXEC DBO.USP_SPLIT_FUNCTION
 '43665,43668,43673,43677,43678,43680,43681,43683,43692,43693'

Cómo devolver registros aleatoriamente en SQL Server

Cómo obtener el tamaño nuestras bases y archivos usando TSQL

En algunas oportunidades necesitamos conocer el tamaño de nuestras bases de datos y/o Datafiles.

Les comparto los siguientes códigos en los cuales podrán obtener esta información por medio de comandos TSQL usando la vista de sistemas sys.master_files

-- ESPACIO POR DATAFILE
SELECT 
DB_NAME(DATABASE_ID) AS DBNAME,
NAME AS LOGICAL_NAME,
TYPE_DESC, 
PHYSICAL_NAME, (SIZE*8)/1024 SIZE_MB
FROM SYS.MASTER_FILES
ORDER BY 1
-- ESPACIO POR BASE DE DATOS
SELECT 
DB_NAME(DATABASE_ID) AS DBNAME,
 SUM((SIZE*8)/1024) SIZE_MB
FROM SYS.MASTER_FILES
GROUP BY 
DB_NAME(DATABASE_ID)
ORDER BY 1