What are your five most common T-SQL commands

Question

Post reply

What are your five most common T-SQL commands

Grant Fritchey

SSC Guru

Points: 398690
More actions
August 21, 2015 at 7:14 am

#320752

I'm working on an article and would appreciate some input.
There are a few T-SQL commands that just write themselves on my keyboard. I almost don't have to type them, they just appear. I want to know what your queries are. What are the ones that you're typing over and over, all the time. I'm looking for DBA stuff, not just SELECT *. Don't worry if someone has already posted your common query. Post it again. I want to tabulate the most common ones to arrive at a list of between 10 and 20. Also, nothing proprietary. I love spBlitz/spWhoIsActive/Minion as much as the next person, but I'm just looking at the common T-SQL language and the DBAs job.
Any five scripts or parts of scripts or statements. Shorter is better, but I'm interested in what it is that you can type fast because you know it because you've typed it a thousand times this week.
As a starter, here are my five:
BACKUP DATABASE x
TO DISK = 'E:\Backups\x.bak'
WITH COPY_ONLY;
RESTORE DATABASE y
FROM DISK = 'E:\Backups\x.bak'
WITH MOVE 'x_data' TO 'D:\data\y.mdf',
MOVE 'x_log' TO 'L:\log\y_log.ldf';
DBCC SHOW_STATISTICS ('dbo.Table','index');
SELECT *
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest;
UPDATE STATISTICS dbo.Table Index WITH FULLSCAN;
So, what are your five?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 15 posts - 1 through 15 (of 63 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

I'm no DBA, so I don't have a "daily set of instructions I need to run", but I use those quite a lot:

sp_spaceused 'TableName'

sp_who2

IF OBJECT_ID('tempdb..#Results') IS NOT NULL

DROP TABLE #Results;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

george sibbald SSC Guru Points: 104210 More actions · Answer 2

I don't type as much as I cut and paste or load pre-written scripts, most frequent of those would be to show fragmentation on all indexes and reverse engineer database permissions.

as for typing favorite is probably still 'select (some combination) from master..sysprocesses'

then backup database dbname to disk = 'H:\..........'

backup log equivalent with the norecovery option

and this for database growth info from backups

select backup_start_date,backup_finish_date,backup_size/1048576 as 'data size in MB' ,

from msdb..backupset

where database_name = 'dbname' and type = 'D'

---------------------------------------------------------------------

Lowell SSC Guru Points: 323504 More actions · Answer 3

for me, its keyboard shortcuts that point to procedures i marked as system procedures, that dig into the current database context of various system views;

they help me find objects, script objects, script data, and so much more.

it's one of those little enhancements that make me incredibly productive.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Factor SSC-Insane Points: 20244 More actions · Answer 4

I have to confess I'm forever doing this....

SET Statistics time on

Set statistics io on

$SELECTEDTEXT$ -- sql to execute and time

SET Statistics time off

Set statistics io off

--and I'm always wanting to alter a stored procedure when I want to keep any explicit permissions intact. Dont you hate the way that you have to change CREATE to ALTER when you are developing a stored procedure or function? If you always delet if it exists first, that loses your explicit permissions on the object!?

-- first check if the procedure exists

IF EXISTS ( SELECT 1

FROM information_schema.Routines

WHERE ROUTINE_NAME = 'MyProcedureName'--name of procedire

AND ROUTINE_TYPE = 'PROCEDURE'--for a function --'FUNCTION'

AND ROUTINE_SCHEMA = 'MySchema' )

SET NOEXEC ON

GO

PRINT 'MyProcedureName: creating a stub'

go

-- if the routine exists this stub creation stem is parsed but not executed

CREATE PROCEDURE MyProcedureName

AS

Select 'created, but not implemented yet.'--just anything will do

GO

-- the following section will be always executed

SET NOEXEC OFF

PRINT 'MyProcedureName: Updating routine code...'

GO

ALTER PROCEDURE MyProcedureName

-- here are all the parameters if needed

AS

-- here will be all your code

Select 'MyProcedureName ... running ...'--just for now!

---

go

PRINT 'MyProcedureName Routine code updated.'

go

---and I'm forever having to dig this out for turning a list of words into a table ...

if exists (Select * from sys.xml_schema_collections where name like 'WordList')

drop XML SCHEMA COLLECTION WordList

go

create xml schema collection WordList as '

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="words">

<xs:simpleType>

<xs:list itemType="xs:string" />

</xs:simpleType>

</xs:element>

</xs:schema>'

go

DECLARE @String VARCHAR(MAX) --our list of words or strings

SELECT @String='Dave Dee Dozy Beaky Mitch and Titch' --just an example

declare @xml_data xml(WordList)

set @xml_data='<words>'+ replace(@string,'&', '&')+'</words>'

select T.ref.value('.', 'nvarchar(100)')

FROM (Select @xml_data.query('

for $i in data(/words) return

element li { $i }

')) A(list)

cross apply A.List.nodes('/li') T(ref)

... and the fast way of doing a list as a table ...

DECLARE @XMLlist XML

SELECT @XMLList = '<list><y i="2" /><y i="4" /><y i="6" /><y i="8" /><y i="10" /><y i="15" /><y i="17" /><y i="21" /></list>'

SELECT x.y.value('.','int')

FROM @XMLList.nodes('list/y/@i') AS x( y )

Best wishes,
Phil Factor

LAW1143 SSCrazy Points: 2743 More actions · Answer 5

REPLACE, DatePart/DateAdd, StatisticsIO/Time ON/Off, identity insert

most of these get used a good bit in preparing nonproduction test-databases/scrubbing production data, or perf analysis.

EDIT: and how can I forget... #1 by 1000 miles: sp_whoisactive

Also, Ive written an sp prefixed proc on the server that stores environment management metadata to lookup what server a db actually lives on. created as an sp so I dont have to specify db context to look them up. sp_cd dbname that just runs a simple select to tell me what server a particular customer's db lives on.

Fatherjack SSCommitted Points: 1575 More actions · Answer 6

Those listed already plus

#1

IF OBJECT_ID('OBJECTNAME') IS | IS NOT NULL

BEGIN

END

#2

CTEs

#3

The code to create a Digits and then a Numbers table (see https://www.simple-talk.com/blogs/2013/11/14/its-all-a-numbers-game/[/url])

DECLARE @digits TABLE ( d INT )

INSERT @digits ( [d] )

SELECT TOP 10

ROW_NUMBER() OVER ( ORDER BY id ) - 1

FROM [sys].[sysobjects] AS S

INSERT @digits ( d )

VALUES ( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 )

CREATE TABLE Numbers (N INT)

INSERT Numbers ( [N] )

SELECT D.[d] + [D2].[d] * 10 + [D3].[d] * 100 + [D4].[d] * 1000

FROM @digits AS D , @digits AS D2 , @digits AS D3 , @digits AS D4

JenMidnightDBA Right there with Babe Points: 787 More actions · Answer 7

-- Just a handful...

SELECT ROW_COUNT() OVER (PARTITION BY col1 ORDER BY col1) as RowNum, ...

DBCC SQLPERF(LOGSPACE);

RESTORE FILELISTONLY FROM DISK='c:\backup\xyz.bak';

RESTORE DATABASE xyz FROM DISK='c:\backup\xyz.bak'

WITH MOVE 'file1' TO 'D:\sql\xyz.mdf',

MOVE 'file1_log' TO 'E:\sql\xyz.ldf',

STATS=10;

SELECT @@SERVERNAME;

Eric M Russell SSC Guru Points: 125522 More actions · Answer 8

We already have the SSMS Template Browser to cover commonly used commands and DMV queries.

However, the MERGE statement is complicated enough that it could use a GUI helper, if that what you're building. Point it to a table, and then let the developer specify key columns, what columns triggers an update, etc. which can be hard to code up from scratch.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 9

Eric M Russell (8/21/2015)
We already have the SSMS Template Browser to cover commonly used commands and DMV queries.
However, the MERGE statement is complicated enough that it could use a GUI helper, if that what you're building. Point it to a table, and then let the developer specify key columns, what columns triggers an update, etc. which can be hard to code up from scratch.

Nah, not building anything, just writing an article.

Although...

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

JenMidnightDBA Right there with Babe Points: 787 More actions · Answer 10

Eric M Russell (8/21/2015)
We already have the SSMS Template Browser to cover commonly used commands and DMV queries.
However, the MERGE statement is complicated enough that it could use a GUI helper, if that what you're building. Point it to a table, and then let the developer specify key columns, what columns triggers an update, etc. which can be hard to code up from scratch.

Swart already did it: http://michaeljswart.com/2012/05/t-sql-merge-statement-syntax-generator/[/url]

Eric M Russell SSC Guru Points: 125522 More actions · Answer 11

Here is a little ditty I coded up yesterday to return a quick and dirty difference comparison between two tables having identical column definition and overlapping rowsets. It's new, but I expect to be using it a lot going forward. By leveraging the CHECKSUM() function, I'm keeping it very simple and generic. It's brute force (two table scans, followed by a sorted merge join and filter) but it's still compares two tables containing 10 million rows in under a minute.

select count(a.chksum)a_diff, count(b.chksum)b_diff

from

(select *, checksum(*)chksum from TableA) a

full outer join

(select *, checksum(*)chksum from TableB) b

on a.chksum = b.chksum

where a.chksum is null or b.chksum is null;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 12

SET Statistics time, io on

...

Set statistics time, io off

SELECT ...

INTO [TraceMMdd]

FROM ::fn_trace_gettable('E:\Thermo\MSSQL12.MSSQLSERVER\MSSQL\Log\ALZDBA_SQL_Trace_..._20150630_0838_.trc', default)

an off course The Glenn Berry Diagnostic Information Queries fairly often !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

cveerabahu SSC Rookie Points: 49 More actions · Answer 13

Hi,

I Use the following commands frequently.

sp_helpdb

dbcc sqlperf(logspace)

select * from sysprocesses order by cpu

backup database

sp_configure

Best Regards

Veera

Papa Yaw SSC Rookie Points: 43 More actions · Answer 14

my most used TSQL might be

if object_id('tempdb..#temp',N'U') is not null drop table #temp

go

create table #temp (...)

go

if object_id('some_proc',N'P') is not null drop proc some_proc

go

create proc ...