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?
1 2 3 4 5 6 |
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?
1 2 3 4 5 6 7 |
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?
1 2 3 4 |
SELECT MAX(RIGHT([name],12)) TempTableCreated FROM tempdb.sys.tables |
How to Validate Email Address in SQL Server?
1 2 3 4 |
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?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 |
How to Count Week Days Between Two Dates?
1 2 3 4 5 6 7 8 9 10 11 |
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 |
How to Write Case Statement in WHERE Clause?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 |
How to Get Random Records from Table?
1 2 3 4 |
SELECT FirstName, LastName FROM [Person].[Person] ORDER BY CHECKSUM(NEWID()); |
How to find the longest running query in SQL Server with Execution Plan?
1 2 3 4 5 6 7 8 9 10 11 |
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?
1 2 3 4 5 |
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?
1 2 3 4 5 6 7 |
CREATE PROCEDURE EncryptedSP WITH ENCRYPTION AS SELECT 5 AS 'FIVE' GO |
How to find all the queries in SQL Server? Can you find it with the help of DMV?
1 2 3 4 5 6 |
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.
1 2 3 4 5 6 |
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.
1 2 3 4 5 6 7 |
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
Leave a Comment