What are your five most common T-SQL commands

  • 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

  • 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

  • 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'

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

  • 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!

  • 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

  • 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.

  • 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

  • -- 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;

  • 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

  • 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

  • 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]

  • 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

  • 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

  • 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

  • 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