Some Important SQL Queries 2018

Jump To Right Section Show

In this post, you will learn some important SQL queries 2018. Before we get started if you want to know how to optimize SQL queries, please go through the following article: SQL Query Optimize .

How can we know how many stored procedures have 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;

How to 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

How to 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 '%[email protected]__%.__%' AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', EmailAddress) = 0

How to Allow Only Alphabets in Column?


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

How to 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))

How to 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

How to 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());

How to find the 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

How to 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

How to Hide Stored Procedure’s Code so anyone with access to stored procedure cannot see it?


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

CREATE PROCEDURE EncryptedSP

WITH ENCRYPTION

SELECT 5 AS 'FIVE'

How to 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

Write a script to list all the stored procedure modified in 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

Write a query to retrieve 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)

Some Important SQL Queries 2018

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

Some Important SQL Queries 2018

How can we know how many stored procedures have executed so far and how many times?

How to Kill All the Processes for Any Database?

How Many Temporary Tables are Created So Far in SQL Server?

How to Validate Email Address in SQL Server?

How to Allow Only Alphabets in Column?

How to Count Week Days Between Two Dates?

How to Write Case Statement in WHERE Clause?

How to Get Random Records from Table?

How to find the longest running query in SQL Server with Execution Plan?

How to Introduce Time Delay Between Two TSQL Commands?

How to Hide Stored Procedure’s Code so anyone with access to stored procedure cannot see it?

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

Write a script to list all the stored procedure modified in last 7 days.

Write a query to retrieve Second (or Nth) highest salary of an employee from a table.

Some Important SQL Queries 2018

You may also like

Leave a Comment X