#Database

Sweet SQL Queries

by sarn

Over the course of several years of coding, you’ll be required to write some pretty gnarly SQL queries.  In today’s post, I’m highlighting some of the more advance querying I’ve had to create at some point.  My hope is so that these query may help someone else complete their projects by giving them insight on the possibilities that are out there.

Common Table Expressions (CTE) – https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-cte-basics/

WITH
  cteTotalSales (SalesPersonID, NetSales)
  AS
  (
    SELECT SalesPersonID, ROUND(SUM(SubTotal), 2)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
  )
SELECT
  sp.FirstName + ' ' + sp.LastName AS FullName,
  sp.City + ', ' + StateProvinceName AS Location,
  ts.NetSales
FROM Sales.vSalesPerson AS sp
  INNER JOIN cteTotalSales AS ts
    ON sp.BusinessEntityID = ts.SalesPersonID
ORDER BY ts.NetSales DESC

 

When using Cursors, you can optimize it, because the default cursor is too way, but typically its used to traverse only. So you can do

  • DECLARE c CURSOR LOCAL FAST_FORWARD
    • Fast forward cursor is one way, no skips
    • Also do with NO COUNT ON;
SET NOCOUNT ON;
DECLARE @cur CURSOR;
DECLARE @userid INT;

SET @cur = CURSOR LOCAL FAST_FORWARD FOR SELECT r.user_id FROM [ms].[tblUsers] r

OPEN @cur FETCH NEXT FROM @cur INTO @userid

WHILE @@FETCH_STATUS = 0 BEGIN

  -- ------------------------------------------
  print @userid
  -- ------------------------------------------

FETCH NEXT FROM @cur INTO @userid
END;

CLOSE @cur;
DEALLOCATE @cur;

JSON output via queries.

  • https://docs.microsoft.com/en-us/sql/relational-databases/json/solve-common-issues-with-json-in-sql-server?view=sql-server-2017
  • https://stackoverflow.com/questions/48595442/sql-server-for-json-path-statement-does-not-return-more-than-2984-lines-of-j

Search for Spanish products ignoring the Spanish characters.

select * from [dw].[tbProducts]
where title like '%Edicion%' collate SQL_Latin1_General_CP1_CI_AI

Search USPs, Views, etc…

MSForEachDB 'select ''?'' as DbName,object_name(id) as Object,text as Text from ?..syscomments where text like ''%<<SearchString%''' 

or

SELECT distinct
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE
m.definition Like '%'+@Search+'%'
ORDER BY 2,1

Get table roles/permission if you have access to the system tables.

SELECT
DB_NAME() AS 'DBName'
,p.[name] AS 'PrincipalName'
,p.[type_desc] AS 'PrincipalType'
,p2.[name] AS 'GrantedBy'
,dbp.[permission_name]
,dbp.[state_desc]
,so.[Name] AS 'ObjectName'
,so.[type_desc] AS 'ObjectType'
FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
ON dbp.[grantor_principal_id] = p2.[principal_id]
WHERE
p.[name] = 'website_Login'