August 21, 2015 at 7:14 am
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
August 21, 2015 at 7:24 am
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
August 21, 2015 at 7:49 am
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'
---------------------------------------------------------------------
August 21, 2015 at 8:14 am
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
August 21, 2015 at 8:14 am
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
August 21, 2015 at 8:32 am
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.
August 21, 2015 at 8:34 am
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
August 21, 2015 at 8:43 am
-- 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;
August 21, 2015 at 8:44 am
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
August 21, 2015 at 8:49 am
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
August 21, 2015 at 8:55 am
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]
August 21, 2015 at 9:09 am
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
August 21, 2015 at 9:17 am
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
August 21, 2015 at 9:30 am
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
August 21, 2015 at 9:35 am
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 ...
Viewing 15 posts - 1 through 15 (of 63 total)
You must be logged in to reply to this topic. Login to reply