Exploring Some Important SQL Queries

Jump To Right Section Show

Structured Query Language (SQL) is a versatile and powerful programming language that enables users to interact with and manipulate relational databases. Whether you’re a seasoned database administrator, a software developer, or a data analyst, having a strong grasp of SQL queries is essential. In this article, we will explore some important SQL queries that will help you harness the true potential of SQL and make your data operations more efficient.

How many stored procedures have been executed so far and how many times


SELECT DB_NAME(database_id) DatabaseName, OBJECT_NAME(object_id) ProcedureName,cached_time, last_execution_time, 
execution_count, total_elapsed_time/execution_count AS avg_elapsed_time,type_desc
FROM sys.dm_exec_procedure_stats
ORDER BY avg_elapsed_time;

SELECT DB_NAME(database_id) DatabaseName, OBJECT_NAME(object_id) ProcedureName,cached_time, last_execution_time,

execution_count, total_elapsed_time/execution_count AS avg_elapsed_time,type_desc

FROM sys.dm_exec_procedure_stats

ORDER BY avg_elapsed_time;

Kill All the Processes for Any Database


ALTER DATABASE YourDatabaseName 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE YourDatabaseName 
SET MULTI_USER;

ALTER DATABASE YourDatabaseName

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE YourDatabaseName

SET MULTI_USER;

How Many Temporary Tables are Created So Far in SQL Server


SELECT MAX(RIGHT([name],12)) TempTableCreated
FROM tempdb.sys.tables

SELECT MAX(RIGHT([name],12)) TempTableCreated

FROM tempdb.sys.tables

Validate Email Address in SQL Server


SELECT EmailAddress AS ValidEmail FROM Contacts
WHERE EmailAddress LIKE '%_@__%.__%' AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', EmailAddress) = 0

SELECT EmailAddress AS ValidEmail FROM Contacts

WHERE EmailAddress LIKE '%_@__%.__%' AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', EmailAddress) = 0

Allow Only Alphabets in Columns


USE tempdb
GO
-- Create Test table
CREATE TABLE TestTable (ID INT, FirstCol VARCHAR(100),
CONSTRAINT FirstCol CHECK (FirstCol NOT LIKE '%[^A-Z]%'))
GO

-- This will be successful
INSERT INTO TestTable (ID, FirstCol)
VALUES (1, 'SQLAuthority')
GO

-- This will throw an error
INSERT INTO TestTable (ID, FirstCol)
VALUES (1, 'SQLAuthority 1')
GO

-- Clean up
DROP TABLE TestTable
GO

USE tempdb

-- Create Test table

CREATE TABLE TestTable (ID INT, FirstCol VARCHAR(100),

CONSTRAINT FirstCol CHECK (FirstCol NOT LIKE '%[^A-Z]%'))

-- This will be successful

INSERT INTO TestTable (ID, FirstCol)

VALUES (1, 'SQLAuthority')

-- This will throw an error

INSERT INTO TestTable (ID, FirstCol)

VALUES (1, 'SQLAuthority 1')

-- Clean up

DROP TABLE TestTable

Count Week Days Between Two Dates


DECLARE @FirstDate DATETIME
SET @FirstDate = '2017/07/01'
DECLARE @SecondDate DATETIME
SET @SecondDate = '2017/07/31'
 
SELECT COUNT(DISTINCT number) FROM master..spt_values
WHERE CAST(number AS INT) BETWEEN DATEPART(dy, @FirstDate) AND DATEPART(dy, @SecondDate)
AND (DATEPART(dw, DATEADD(d, number, @FirstDate)) IN (2,3,4,5,6))
GO

DECLARE @FirstDate DATETIME

SET @FirstDate = '2017/07/01'

DECLARE @SecondDate DATETIME

SET @SecondDate = '2017/07/31'

SELECT COUNT(DISTINCT number) FROM master..spt_values

WHERE CAST(number AS INT) BETWEEN DATEPART(dy, @FirstDate) AND DATEPART(dy, @SecondDate)

AND (DATEPART(dw, DATEADD(d, number, @FirstDate)) IN (2,3,4,5,6))

Write Case Statement in WHERE Clause


DECLARE @FirstName VARCHAR(100)
SET @FirstName = ''
 
DECLARE @LastName VARCHAR(100)
SET @LastName = 'Dave' 
 
SELECT FirstName, LastName
FROM Contacts
WHERE  
    FirstName = CASE
    WHEN LEN(@FirstName) > 0 THEN  @FirstName 
    ELSE FirstName 
    END
AND
    LastName = CASE
    WHEN LEN(@LastName) > 0 THEN  @LastName 
    ELSE LastName 
    END
GO

DECLARE @FirstName VARCHAR(100)

SET @FirstName = ''

DECLARE @LastName VARCHAR(100)

SET @LastName = 'Dave'

SELECT FirstName, LastName

FROM Contacts

WHERE

FirstName = CASE

WHEN LEN(@FirstName) > 0 THEN @FirstName

ELSE FirstName

END

AND

LastName = CASE

WHEN LEN(@LastName) > 0 THEN @LastName

ELSE LastName

END

Get Random Records from Table


SELECT FirstName, LastName FROM [Person].[Person]
ORDER BY CHECKSUM(NEWID());

SELECT FirstName, LastName FROM [Person].[Person]

ORDER BY CHECKSUM(NEWID());

Longest-running query in SQL Server with Execution Plan


SELECT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn, 
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
,query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u
CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t
ORDER BY MaxElapsedTime DESC

SELECT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime,

ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,

s.creation_time AS LogCreatedOn,

ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec

,query_plan

FROM sys.dm_exec_query_stats s

CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u

CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t

ORDER BY MaxElapsedTime DESC

Introduce Time Delay Between Two TSQL Commands


SELECT GETDATE() CurrentTime
WAITFOR DELAY '00:00:10' -- 10 Second Delay
SELECT GETDATE() CurrentTime

SELECT GETDATE() CurrentTime

WAITFOR DELAY '00:00:10' -- 10 Second Delay

SELECT GETDATE() CurrentTime

Hide Store Procedure’s Code so anyone with access


CREATE PROCEDURE EncryptedSP
WITH ENCRYPTION
AS
SELECT 5 AS 'FIVE'
GO

CREATE PROCEDURE EncryptedSP

WITH ENCRYPTION

SELECT 5 AS 'FIVE'

Find all the queries in SQL Server? Can you find it with the help of DMV?


SELECT dest.TEXT AS [Query], deqs.execution_count [Count], deqs.last_execution_time AS [Time]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

SELECT dest.TEXT AS [Query], deqs.execution_count [Count], deqs.last_execution_time AS [Time]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

Script to list all the stored procedures modified in the last 7 days.


SELECT name, modify_date, create_date
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7

SELECT name, modify_date, create_date

FROM sys.objects

WHERE type = 'P'

AND DATEDIFF(D,modify_date, GETDATE()) < 7

Retrieve the Second (or Nth) highest salary of an employee from a table.


SELECT * FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)

SELECT * FROM Employee E1

WHERE (N-1) = (

SELECT COUNT(DISTINCT(E2.Salary))

FROM Employee E2

WHERE E2.Salary > E1.Salary)

If you may like to know how to optimize SQL queries, please go through the following article: SQL Query Optimize .

Some Important SQL Queries 2018

The article was published on April 1, 2018 @ 10:30 AM

Unlocking the Power of SQL: Exploring Some Important SQL Queries

How many stored procedures have been executed so far and how many times

Kill All the Processes for Any Database

How Many Temporary Tables are Created So Far in SQL Server

Validate Email Address in SQL Server

Allow Only Alphabets in Columns

Count Week Days Between Two Dates

Write Case Statement in WHERE Clause

Get Random Records from Table

Longest-running query in SQL Server with Execution Plan

Introduce Time Delay Between Two TSQL Commands

Hide Store Procedure’s Code so anyone with access

Find all the queries in SQL Server? Can you find it with the help of DMV?

Script to list all the stored procedures modified in the last 7 days.

Retrieve the Second (or Nth) highest salary of an employee from a table.

Some Important SQL Queries 2018

Leave a Comment X

Unlocking the Power of SQL: Exploring Some Important SQL Queries

How many stored procedures have been executed so far and how many times

Kill All the Processes for Any Database

How Many Temporary Tables are Created So Far in SQL Server

Validate Email Address in SQL Server

Allow Only Alphabets in Columns

Count Week Days Between Two Dates

Write Case Statement in WHERE Clause

Get Random Records from Table

Longest-running query in SQL Server with Execution Plan

Introduce Time Delay Between Two TSQL Commands

Hide Store Procedure’s Code so anyone with access

Find all the queries in SQL Server? Can you find it with the help of DMV?

Script to list all the stored procedures modified in the last 7 days.

Retrieve the Second (or Nth) highest salary of an employee from a table.

Some Important SQL Queries 2018

You may also like

Leave a Comment X