How to get last inserted values

1/ SCOPE_IDENTITY function for IDENTITY column.

2/OUTPUT clause

3/ If you are in a trigger, then inserted table is what you are looking for.

List of stored procedures and functions associated with an assembly

SELECT object_name(M.object_id) as SPName, M.assembly_class, M.assembly_method
FROM
    sys.assembly_modules M
    INNER JOIN sys.assemblies A ON A.assembly_id = M.assembly_id
WHERE A.name = 'YourAssemblyName'

Index usability - tune your index strategy

The view sys.dm_db_index_usage_stats returns statistical counts of the various index operations that have occurred to an index over time.

SELECT
     object_name(i.object_id) as TableName
     , i.name as [Index]
     , user_seeks + user_scans + user_lookups + user_updates as usability
     , user_seeks, user_scans, user_lookups, user_updates
     , i.index_id, i.type_desc, i.is_unique, i.is_primary_key, i.is_unique
FROM
     sys.indexes i    
     INNER JOIN sys.dm_db_index_usage_stats s ON s.index_id = i.index_id AND s.object_id = i.object_id
WHERE 1=1
     AND i.name is not NULL
     AND s.database_id = DB_ID() -- current DB
     --AND object_name(i.object_id) = '<TableName>'
ORDER BY
     usability, [Index], TableName;

List of long running queries

T-SQL script that returns the queries that take the longest time to run:

SELECT TOP 20
        R.session_id, R.status, R.start_time, R.command, Q.text
FROM
        sys.dm_exec_requests R
        CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) Q
--WHERE R.status in ('runnable')
ORDER BY R.start_time

There’s also an interesting script, completely free on http://www.brentozar.com.

T-SQL Split function

There are some excellent options available at: http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings, but my favourite solution remains this:

DECLARE
@InputString NVARCHAR(MAX) = 'token1,token2,token3,token4,token5'
, @delimiter varchar(10) = ','

DECLARE @xml AS XML = CAST(('<X>'+REPLACE(@InputString,@delimiter ,'</X><X>')+'</X>') AS XML)
SELECT C.value('.', 'varchar(10)') AS value
FROM @xml.nodes('X') as X(C)

How to get random rows from a table?

Did you ever think how to get random rows from a table?Let this article back and try to find a solution on your own. It is not so simple task, isn’t it?Almost sure you thought to JOIN the table with itself, and you might found a reasonable solution.One way to do this would be to:

  1. create a column in the table, put NewId() as its default value (i.e. creates a unique value of type uniqueidentifier.)
  2. sort the table by this column,
  3. then take first x rows, using TOP clause ORDER BY sorts the rows in the table. Combined with the TOP keyword, it ensures that only the first n items in the resultset, as sorted according to the ORDER BY clause, appears in the results. Since you don't have a random column in your table, you might use directly NewId() function, which generates a random GUID, and sorted by that.

SELECT TOP x * FROM MyTable ORDER BY NewId()

How to move transaction log file (.ldf)

Is your drive out of space because your database is growing? Then you might think to move the transaction log by using SQL Server stored procedures for detaching and attaching the database.

For example, to move the pubs database from drive C to drive D:

  1. First use the following sp_detach_db command to detach the database
    EXEC sp_detach_db 'pubs'
  2. Next, copy the pubs_log.ldf file to the destination drive.
  3. Finally, after you copy the file, you can use the sp_attach_db stored procedure to reattach the data to SQL Server.
    EXEC sp_attach_db 'pubs', 'c:\mssql\data\pubs.mdf', 'd:\mssql\data\pubs_log.ldf'

How to show all connected users in SQL Server

The simplest way to list all users and processes is to use the system stored procedure sp_who. To list all active users:

sp_who 'active'

To list details about a particular user:

sp_who 'login-name'

How to check if SQL Server Agent is running

Here it is the T-SQL code snippet:

SELECT program_name, login_time, loginame, cmd, hostname
FROM master.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher'

Pages

Subscribe to SQLhint RSS