views:

10502

answers:

75

What are some hidden features of SQL Server?

For example, undocumented system stored procedures, tricks to do things which are very useful but not documented enough?


Answers

Thanks to everybody for all the great answers!

Stored Procedures

  • sp_msforeachtable: Runs a command with '?' replaced with each table name (v6.5 and up)
  • sp_msforeachdb: Runs a command with '?' replaced with each database name (v7 and up)
  • sp_who2: just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up)
  • sp_helptext: If you want the code of a stored procedure
  • sp_tables: return a list of all tables
  • sp_stored_procedures: return a list of all stored procedures
  • xp_sscanf: Reads data from the string into the argument locations specified by each format argument.
  • xp_fixeddrives:: Find the fixed drive with largest free space
  • sp_help: If you want to know the table structure, indexes and constraints of a table

Snippets

  • Returning rows in random order
  • All database User Objects by Last Modified Date
  • Return Date Only
  • Find records which date falls somewhere inside the current week.
  • Find records which date occurred last week.
  • Returns the date for the beginning of the current week.
  • Returns the date for the beginning of last week.
  • See the text of a procedure that has been deployed to a server
  • Drop all connections to the database
  • Table Checksum
  • Row Checksum
  • Drop all the procedures in a database
  • Re-map the login Ids correctly after restore
  • Call Stored Procedures from an INSERT statement
  • Find Procedures By Keyword
  • Drop all the procedures in a database
  • Query the transaction log for a database programmatically.

Functions

  • HashBytes()
  • EncryptByKey
  • PIVOT command

Misc

  • Connection String extras
  • TableDiff.exe
  • Triggers for Logon Events (New in Service Pack 2)
  • Boosting performance with persisted-computed-columns (pcc).
  • DEFAULT_SCHEMA setting in sys.database_principles
  • Forced Parameterization
  • Vardecimal Storage Format
  • Figuring out the most popular queries in seconds
  • Scalable Shared Databases
  • Table/Stored Procedure Filter feature in SQL Management Studio
  • Trace flags
  • Number after a GO repeats the batch
  • Security using schemas
  • Encryption using built in encryption functions, views and base tables with triggers
+7  A: 

Here are some features I find useful but a lot of people don't seem to know about:

sp_tables

Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause, except synonym objects.

Link

sp_stored_procedures

Returns a list of stored procedures in the current environment.

Link

Sklivvz
+14  A: 

HashBytes() to return the MD2, MD4, MD5, SHA, or SHA1 hash of its input.

Joel Coehoorn
Nice one!The correct link is http://msdn.microsoft.com/en-us/library/ms174415(SQL.90).aspx (2005 version)
Sklivvz
You're right, that was the 2008 version of the docs, even though the pages are pretty much identical. Fixed now.
Joel Coehoorn
+6  A: 

Simple encryption with EncryptByKey

John Sheehan
+9  A: 

Useful for parsing stored procedure arguments: xp_sscanf

Reads data from the string into the argument locations specified by each format argument.

The following example uses xp_sscanf to extract two values from a source string based on their positions in the format of the source string.

DECLARE @filename varchar (20), @message varchar (20)
EXEC xp_sscanf 'sync -b -fproducts10.tmp -rrandom', 'sync -b -f%s -r%s', 
  @filename OUTPUT, @message OUTPUT
SELECT @filename, @message

Here is the result set.

-------------------- -------------------- 
products10.tmp        random
Sklivvz
I must be having a dumb moment (no, really). Can you tell me where we can use this?
Raj More
+4  A: 

sp_who2, just like sp_who, but with a lot more info for troubleshooting blocks

Booji Boy
Link here http://www.devx.com/tips/Tip/14208
Sklivvz
+2  A: 

/* Find the fixed drive with largest free space, you can also copy files to estimate which disk is quickest */

EXEC master..xp_fixeddrives

/* Checking assumptions about a file before use or reference */

EXEC master..xp_fileexist 'C:\file_you_want_to_check'

More details here

ICW
+37  A: 

sp_msforeachtable: Runs a command with '?' replaced with each table name. e.g.

exec sp_msforeachtable "dbcc dbreindex('?')"

You can issue up to 3 commands for each table

exec sp_msforeachtable
    @Command1 = 'print ''reindexing table ?''',
    @Command2 = 'dbcc dbreindex(''?'')',
    @Command3 = 'select count (*) [?] from ?'

Also, sp_MSforeachdb

Mitch Wheat
Cool! This sounds very very useful! Thanks
Sklivvz
Wow, that would be very helpful!
Mitchel Sellers
You can get the name of the table in the query by using single quotes around the question mark.sp_msforeachtable "select count(*), '?' as tabenm from ?"
Jody
Nice! I could have used this when getting rid of the Hungarian prefixes within the tables and sprocs in a database that I inherited. Renamed them all the hard way.
p.campbell
+24  A: 

A less known TSQL technique for returning rows in random order:

-- Return rows in a random order
SELECT 
    SomeColumn 
FROM 
    SomeTable
ORDER BY 
    CHECKSUM(NEWID())
Mitch Wheat
Great for small result sets. I wouldn't use it on a table with more than 10000 rows unless you've got time to spare
John Sheehan
I've used it on tables much larger than that, and it wasn't too slow.
Mitch Wheat
How does the performance compare with "ORDER BY NEWID"?
Sklivvz
What's the purpose of the CHECKSUM()? You can order by just NEWID().
Jonas Lincoln
I've even seen decent results on 100,000,000 (100 mil) rows, w/o CHECKSUM(). Also, I have to ask as well, why not just ORDER BY NEWID?
Troy DeMonbreun
Will it work in MySql ?? If not, is there any way to do it??
Prashant
@Prashant : this question is about SQL server ..!
Mitch Wheat
@GateKiller: I've rolled back your edit, because the Checksum() is not a mistake; it reduces the size of the sort column.
Mitch Wheat
I'd used rand() for that random order. This way will always be random in every run.select * from table order by cast(RAND(DATEPART(ms, GETDATE()) * id) * 1000000 as int) , id asc
+29  A: 

Connection String extras:

MultipleActiveResultSets=true;

This makes ADO.Net 2.0 and above read multiple, forward-only, read-only results sets on a single database connection, which can improve performance if you're doing a lot of reading. You can turn it on even if you're doing a mix of query types.

Application Name=MyProgramName

Now when you want to see a list of active connections by querying the sysprocesses table, your program's name will appear in the program_name column instead of ".Net SqlClient Data Provider"

C. Lawrence Wenham
I made Application Name a requirement at my company. Every new app must have a unique name. Makes tracking down which app locked/broke something a lot easier.
Neil N
Application Name is also available as a filter in profiler. It helps a lot if you want to only see your queries and not the queries of your coworkers.
Malcolm Frexner
+3  A: 

Here is a query I wrote to list All DB User Objects by Last Modified Date:

select name, modify_date, 
case when type_desc = 'USER_TABLE' then 'Table'
when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION') then 'Function'
end as type_desc
from sys.objects
where type in ('U', 'P', 'FN', 'IF', 'TF')
and is_ms_shipped = 0
order by 2 desc
Gordon Bell
A: 

A semi-hidden feature, the Table/Stored Procedure Filter feature can be really useful...

In the SQL Server Management Studio Object Explorer, right-click the Tables or Stored Procedures folder, select the Filter menu, then Filter Settings, and enter a partial name in the Name contains row.

Likewise, use Remove Filter to see all Tables/Stored Procedures again.

Gordon Bell
+7  A: 

Return Date Only

Select Cast(Floor(Cast(Getdate() As Float))As Datetime)

or

Select DateAdd(Day, 0, DateDiff(Day, 0, Getdate()))
GateKiller
Short version - SELECT CAST(FLOOR(CAST(@DateTime AS FLOAT))AS DATETIME)
Meff
Hell yes. CASTFLOORCAST rules.
StingyJack
@Meff: I have updated with your shorter version. Thank you.
GateKiller
Can't find a reference to it, but I seem to remember tests that suggested SELECT DateAdd(Day, 0, DateDiff(Day, 0, @DateTime)) was faster. Happy to be enlightened, either way!
Kristen
Found this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35296#107617 but it didn't include the CAST/FLOOR method. An informal test on a medium sized recordset suggests DATEADD may be about 7% faster than CAST/FLOOR - not enough to worry about for most situations
Kristen
I've added the other method, however; my quick testing shows that the cast floor method is 800 Nanoseconds quicker. So nothing in it really.
GateKiller
Some other "Date flooring" here: http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server
Kristen
I've had problems with casting dates to float and back not being accurate.
Emtucifor
+4  A: 

Find records which date falls somewhere inside the current week.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ), 0 )

Find records which date occurred last week.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )

Returns the date for the beginning of the current week.

select dateadd( week, datediff( week, 0, getdate() ), 0 )

Returns the date for the beginning of last week.

select dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )
GateKiller
+9  A: 

Drop all connections to the database:

Use Master
Go

Declare @dbname sysname

Set @dbname = 'name of database you want to drop connections from'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End
GateKiller
Is there a one-liner, or a drop database parameter that does this for me? I notice that if you attempt to 'delete database' through the ui, there's a checkbox for 'close existing connections' which implies that it's a boolean parameter.
Actually, I just found a two line solution. ALTER DATABASE [@[email protected]] SET READ_ONLY WITH ROLLBACK IMMEDIATE --this disconnects all usersALTER DATABASE [@[email protected]] SET READ_WRITE WITH ROLLBACK IMMEDIATEDROP DATABASE [@[email protected]]
`ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE` will prevent any new connections from occurring, too.
Emtucifor
+10  A: 

Table Checksum

Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK)

Row Checksum

Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK) Where Column = Value
GateKiller
Can you explain this better?
Sklivvz
These allow you to produce a checksum for all the data in the table. It is a simple and quick way to check if two rows or two tables are the same.
GateKiller
A: 

For SQL Server 2005:

select * from sys.dm_os_performance_counters

select * from sys.dm_exec_requests
SomeMiscGuy
I believe it's 2005-only.
Constantin
@Constantin is correct. Those are 2005-only.
Mitch Wheat
+23  A: 

TableDiff.exe

  • Table Difference tool allows you to discover and reconcile differences between a source and destination table or a view. Tablediff Utility can report differences on schema and data. The most popular feature of tablediff is the fact that it can generate a script that you can run on the destination that will reconcile differences between the tables.

Link

Sklivvz
+million OMG I have needed this for 5 years!
FreshCode
+3  A: 

I find this small script very hand to see the text of a procedure that has been deployed to a server:

DECLARE @procedureName NVARCHAR( MAX ), @procedureText NVARCHAR( MAX )

SET @procedureName = 'myproc_Proc1'

SET @procedureText =    (
                            SELECT  OBJECT_DEFINITION( object_id )
                            FROM    sys.procedures 
                            WHERE   Name = @procedureName
                        )

PRINT @procedureText
cheeves
sp_helptext 'myproc_Proc1' is shorter
Eduardo Molteni
Anyway, good to know the existence of OBJECT_DEFINITION
Jhonny D. Cano -Leftware-
+1  A: 

If you want to drop all the procedures in a DB -

SELECT  IDENTITY ( int, 1, 1 ) id, 
        [name] 
INTO    #tmp 
FROM    sys.procedures 
WHERE   [type]        = 'P' 
    AND is_ms_shipped = 0 

DECLARE @i INT 

SELECT   @i = COUNT( id ) FROM #tmp 
WHILE    @i > 0 
BEGIN 
   DECLARE @name VARCHAR( 100 ) 
   SELECT @name = name FROM #tmp WHERE id = @<a href="#121613">i </a>
   EXEC ( 'DROP PROCEDURE ' + @name ) 
   SET @i = @i-1 
END

DROP TABLE #tmp
cheeves
+8  A: 

If you want the code of a stored procedure you can:

sp_helptext 'ProcedureName'

(not sure if it is hidden feature, but I use it all the time)

Eduardo Molteni
Don't know why, but sp_helptext output is a bit goofy on any overly long lines in the original. When scripting Sprocs this doens't happen, so maybe there is another, more robust, export mechanism? sp_helptext 'MyView' also useful.
Kristen
I'm not sure what you mean. For me, the SPs code are outputed with the same format I have scripted them in the original file (with all the CRs, etc)
Eduardo Molteni
RolandTumble
+9  A: 

useful when restoring a database for Testing purposes or whatever. Re-maps the login ID's correctly:

EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'

Kolten
Hey that's pretty cool! I've always just had a script that looped through all the objects granting permissions, but this way looks like you don't over-do it.
Valerion
I have had this proc not work before, and I had to change the objects ownership to a temp user, drop the original user, re -add the original and assign the ownership back. Ugh...
StingyJack
+4  A: 

Not so much a hidden feature but setting up key mappings in Management Studio under Tools\Options\Keyboard: Alt+F1 is defaulted to sp_help "selected text" but I cannot live without the adding Ctrl+F1 for sp_helptext "selected text"

This one is pure gold. I'm lost without it most of the time :-)
Dan F
I use to configure the USE command also, for moving along the db's
Jhonny D. Cano -Leftware-
+4  A: 

Trace Flags! "1204" was invaluable in deadlock debugging on SQL Server 2000 (2005 has better tools for this).

Constantin
"1204" sounds awefully familiar for some reason...
Sung Meister
+8  A: 

A stored procedure trick is that you can call them from an INSERT statement. I found this very useful when I was working on an SQL Server database.

CREATE TABLE #toto (v1 int, v2 int, v3 char(4), status char(6))
INSERT #toto (v1, v2, v3, status) EXEC dbo.sp_fulubulu(sp_param1)
SELECT * FROM #toto
DROP TABLE #toto
edomaur
Sadly can't be used with @TableVariable
Kristen
The pain with this very useful technique is that unlike most #tables, you have to fully define all the columns. The lazy way of doing this is to create the #table inside the proc you are calling right at the end, then sp_help in tempdb, copy and paste, remove code from proc. Done
adolf garlic
+2  A: 

My favorite is master..xp_cmdshell. It allows you to run commands from a command prompt on the server and see the output. It's extremely useful if you can't login to the server, but you need to get information or control it somehow.

For example, to list the folders on the C: drive of the server where SQL Server is running.

  • master..xp_cmdshell 'dir c:\'

You can start and stop services, too.

  • master..xp_cmdshell 'sc query "My Service"'

  • master..xp_cmdshell 'sc stop "My Service"'

  • master..xp_cmdshell 'sc start "My Service"'

It's very powerful, but a security risk, also. Many people disable it because it could easily be used do bad things on the server. But, if you have access to it, it can be extremely useful.

Jim
+14  A: 

If you want to know the table structure, indexes and constraints:

sp_help 'TableName'
Eduardo Molteni
Thanks for this. I just wasted 20 min writing a sproc in the master db that takes a db name and table name and reports the column names and definitions.
Rob Allen
A: 

@Gatekiller - An easier way to get just the Date is surely

CAST(CONVERT(varchar,getdate(),103) as datetime)

If you don't use DD/MM/YYYY in your locale, you'd need to use a different value from 103. Lookup CONVERT function in SQL Books Online for the locale codes.

Ollie
Kristen
I always use 106 to convert dates. That uses the month name instead of a number and is totally unambiguous.
Jonathan
+47  A: 

In Management Studio, you can put a number after a GO end-of-batch marker to cause the batch to be repeated that number of times:

PRINT 'X'
GO 10

Will print 'X' 10 times. This can save you from tedious copy/pasting when doing repetitive stuff.

GilM
Wow..! Never knew about this one.
Sung Meister
+2  A: 

Triggers for Logon Events

  • Logon triggers can help complement auditing and compliance. For example, logon events can be used for enforcing rules on connections (for example limiting connection through a specific username or limiting connections through a username to a specific time periods) or simply for tracking and recording general connection activity. Just like in any trigger, ROLLBACK cancels the operation that is in execution. In the case of logon event that means canceling the connection establishment. Logon events do not fire when the server is started in the minimal configuration mode or when a connection is established through dedicated admin connection (DAC).

Link

Sklivvz
+6  A: 

Persisted-computed-columns

  • Computed columns can help you shift the runtime computation cost to data modification phase. The computed column is stored with the rest of the row and is transparently utilized when the expression on the computed columns and the query matches. You can also build indexes on the PCC’s to speed up filtrations and range scans on the expression.

Link

Sklivvz
A: 

DEFAULT_SCHEMA setting in sys.database_principles

  • SQL Server provides great flexibility with name resolution. However name resolution comes at a cost and can get noticeably expensive in adhoc workloads that do not fully qualify object references. SQL Server 2005 allows a new setting of DEFEAULT_SCHEMA for each database principle (also known as “user”) which can eliminate this overhead without changing your TSQL code.

Link

Sklivvz
A: 

Forced Parameterization

  • Parameterization allows SQL Server to take advantage of query plan reuse and avoid compilation and optimization overheads on subsequent executions of similar queries. However there are many applications out there that, for one reason or another, still suffer from ad-hoc query compilation overhead. For those cases with high number of query compilation and where lowering CPU utilization and response time is critical for your workload, force parameterization can help.

Link

Sklivvz
A: 

Vardecimal Storage Format

  • SQL Server 2005 adds a new storage format for numeric and decimal datatypes called vardecimal. Vardecimal is a variable-length representation for decimal types that can save unused bytes in every instance of the row. The biggest amount of savings come from cases where the decimal definition is large (like decimal(38,6)) but the values stored are small (like a value of 0.0) or there is a large number of repeated values or data is sparsely populated.

Link

Sklivvz
+12  A: 

Figuring out the most popular queries

  • With sys.dm_exec_query_stats, you can figure out many combinations of query analyses by a single query.

Link with the commnad

select * from sys.dm_exec_query_stats 
order by execution_count desc
Sklivvz
how do you know the syntax of the command?
none
A: 

Scalable Shared Databases

  • Through Scalable Shared Databases one can mount the same physical drives on commodity machines and allow multiple instances of SQL Server 2005 to work off of the same set of data files. The setup does not require duplicate storage for every instance of SQL Server and allows additional processing power through multiple SQL Server instances that have their own local resources like cpu, memory, tempdb and potentially other local databases.

Link

Sklivvz
+2  A: 

Find Procedures By Keyword

What procedures contain a certain piece of text (Table name, column name, variable name, TODO, etc)?

SELECT OBJECT_NAME(ID) FROM SysComments 
WHERE Text LIKE '%SearchString%' 
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
Meff
+8  A: 

I know it's not exactly hidden, but not too many people know about the PIVOT command. I was able to change a stored procedure that used cursors and took 2 minutes to run into a speedy 6 second piece of code that was one tenth the number of lines!

BoltBait
+3  A: 

Here is one I learned today because I needed to search for a transaction.

::fn_dblog
This allows you to query the transaction log for a database.

USE mydatabase;
SELECT *
FROM ::fn_dblog(NULL, NULL)

http://killspid.blogspot.com/2006/07/using-fndblog.html

Chris Roland
A: 

A few of my favorite things:

Added in sp2 - Scripting options under tools/options/scripting

New security using schemas - create two schemas: user_access, admin_access. Put your user procs in one and your admin procs in the other like this: user_access.showList , admin_access.deleteUser . Grant EXECUTE on the schema to your app user/role. No more GRANTing EXECUTE all the time.

Encryption using built in encryption functions, views(to decrypt for presentation), and base tables with triggers(to encrypt on insert/update).

A: 

OK, here's my 2 cents:

http://dbalink.wordpress.com/2008/10/24/querying-the-object-catalog-and-information-schema-views/

I am too lazy to re-write the whole thing here, so please check my post. That may be trivial to many, but there will be some who will find it a "hidden gem".

EDIT:

After a while, I decided to add the code here so you don't have to jump to my blog to see the code.

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],   CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE’;

Or, if you want to pull all the User Tables altogether, use CURSOR like this:

DECLARE @tablename VARCHAR(60)

DECLARE cursor_tablenames CURSOR FOR
SELECT name FROM AdventureWorks.sys.tables

OPEN cursor_tablenames
FETCH NEXT FROM cursor_tablenames INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT  t.name AS [TABLE Name], c.name AS [COLUMN Name], p.name AS [DATA Type], p.max_length AS[SIZE],   CAST(p.PRECISION AS VARCHAR) +‘/’+ CAST(p.scale AS VARCHAR) AS [PRECISION/Scale]
FROM AdventureWorks.sys.objects AS t
JOIN AdventureWorks.sys.columns AS c
ON t.OBJECT_ID=c.OBJECT_ID
JOIN AdventureWorks.sys.types AS p
ON c.system_type_id=p.system_type_id
WHERE t.name = @tablename
AND t.type_desc=‘USER_TABLE’
ORDER BY t.name ASC

FETCH NEXT FROM cursor_tablenames INTO @tablename
END

CLOSE cursor_tablenames
DEALLOCATE cursor_tablenames

ADDITIONAL REFERENCE (my blog): http://dbalink.wordpress.com/2009/01/21/how-to-create-cursor-in-tsql/

MarlonRibunal
+1  A: 
sp_executesql

For executing a statement in a string. As good as Execute but can return parameters out

Eduardo Molteni
**Better** than EXEC
RolandTumble
+2  A: 

Since I'm a programmer, not a DBA, my favorite hidden feature is the SMO library. You can automate pretty much anything in SQL Server, from database/table/column creation and deletion to scripting to backup and restore. If you can do it in SQL Server Management Studio, you can automate it in SMO.

Kyralessa
A: 

Not undocumented

RowNumber courtesy of Itzik Ben-Gan http://www.sqlmag.com/article/articleid/97675/sql_server_blog_97675.html

SET XACT_ABORT ON rollback everything on error for transactions

all the sp_'s are helpful just browse books online

keyboard shortcuts I use all the time in management studio F6 - switch between results and query Alt+X or F5- run selected text in query if nothing is selected runs the entire window Alt+T and Alt+D - results in text or grid respectively

+1  A: 

I find sp_depends useful. It displays the objects which depend on a given object, e.g.

exec sp_depends 'fn_myFunction'

returns objects which depend on this function (note, if the objects have not originally been run into the database in the correct order this will give incorrect results.)

I've found sp_depends a bit unreliable in complex enterprise scenarios.
ip
RolandTumble
+3  A: 

EXCEPT and INTERSECT

Instead of writing elaborate joins and subqueries, these two keywords are a much more elegant shorthand and readable way of expressing your query's intent when comparing two query results. New as of SQL Server 2005, they strongly complement UNION which has already existed in the TSQL language for years.

The concepts of EXCEPT, INTERSECT, and UNION are fundamental in set theory which serves as the basis and foundation of relational modeling used by all modern RDBMS. Now, Venn diagram type results can be more intuitively and quite easily generated using TSQL.

Ray Vega
A: 

In SQL Server 2005 you no longer need to run the sp-blocker-pss80 stored procedure. Instead, you can do:

exec sp_configure 'show advanced options', 1;
reconfigure;
go
exec sp_configure 'blocked process threshold', 30;
reconfigure;

You can then start a SQL Trace and select the Blocked process report event class in the Errors and Warnings group. Details of that event here.

Logicalmind
+2  A: 

The most surprising thing I learned this week involved using a CASE statement in the ORDER By Clause. For example:

UPDATE

Looks like the post lost my example somehow. Here it is again..

declare @orderby varchar(10)

set @orderby = 'NAME'

select * 
    from Users
    ORDER BY 
     CASE @orderby
      WHEN 'NAME' THEN LastName
      WHEN 'EMAIL' THEN EmailAddress
     END
Chris Lively
... for example, what?
Blorgbeard
A: 

Based on what appears to be a vehement reaction to it by hardened database developers, the CLR integration would rank right up there. =)

casperOne
CLR Integration with SQL Server is the one of those "best things Microsoft ever did", ranking up there with creating the CLR itself and the C# language.
Triynko
+1  A: 

Some undocumented ones are here: Undocumented but handy SQL server Procs and DBCC commands

SQLMenace
+5  A: 

In SQL Server 2005/2008 to show row numbers in a SELECT query result:

SELECT ( ROW_NUMBER() OVER (ORDER BY OrderId) ) AS RowNumber,
        GrandTotal, CustomerId, PurchaseDate
FROM Orders

ORDER BY is a compulsory clause. The OVER() clause tells the SQL Engine to sort data on the specified column (in this case OrderId) and assign numbers as per the sort results.

Binoj Antony
wouldn't be simpler if they used syntactic suger in sql engine to parse it syntax word as "RowNumberInTable"
none
A: 

use db go
DECLARE @procName varchar(100)
DECLARE @cursorProcNames CURSOR
SET @cursorProcNames = CURSOR FOR
select name from sys.procedures where modify_date > '2009-02-05 13:12:15.273' order by modify_date desc

OPEN @cursorProcNames
FETCH NEXT
FROM @cursorProcNames INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
-- see the text of the last stored procedures modified on -- the db , hint Ctrl + T would give you the procedures test set nocount off;
exec sp_HelpText @procName --- or print them
-- print @procName

FETCH NEXT
FROM @cursorProcNames INTO @procName
END
CLOSE @cursorProcNames

select @@error

YordanGeorgiev
A: 
use db
go 

select o.name 
, (SELECT [definition] AS [text()] 
     FROM sys.all_sql_modules 
     WHERE sys.all_sql_modules.object_id=a.object_id 
     FOR XML PATH(''), TYPE
  )  AS Statement_Text
 , a.object_id
 , o.modify_date 

 FROM sys.all_sql_modules a 
 LEFT JOIN  sys.objects o ON a.object_id=o.object_id 
 ORDER BY  4 desc

--select * from sys.objects
YordanGeorgiev
+1  A: 

Get a list of column headers in vertical format:

Copy column names in grid results

Tools - Options - Query Results - SQL Server - Results to Grid tick "Include column headers when copying or saving the results"

you will need to make a new connection at this point, then run your query

Now when you copy the results from the grid, you get the column headers

Also If you then copy the results to excel

Copy col headers only

Paste Special (must not overlap copy area)

tick "Transpose"

OK

[you may wish to add a "," and autofill down at this point]

You have an instant list of columns in vertical format

adolf garlic
+6  A: 

I'm not sure if this is a hidden feature or not, but I stumbled upon this, and have found it to be useful on many occassions. You can concatonate a set of a field in a single select statement, rather than using a cursor and looping through the select statement.

Example:

DECLARE @nvcConcatonated nvarchar(max)
SET @nvcConcatonated = ''

SELECT @nvcConcatonated = @nvcConcatonated + C.CompanyName + ', '
FROM tblCompany C
WHERE C.CompanyID IN (1,2,3)

SELECT @nvcConcatonated

Results: "Acme, Microsoft, Apple,"

Sheki
you can also use COALESCE() to do the same thing without the need to initialize the variable.SELECT @nvcConcatonated = COALESCE(@nvcConcatonated+',','')+CAST(C.CompanyName as VARCHAR(255)) FROM...
SomeMiscGuy
A: 

Returing results based on a pipe delimited string of IDs in a single statmeent (alternative to passing xml or first turning the delimited string to a table)

Example:

DECLARE @nvcIDs nvarchar(max)
SET @nvcIDs = '|1|2|3|'

SELECT C.*
FROM tblCompany C
WHERE @nvcIDs LIKE '%|' + CAST(C.CompanyID as nvarchar) + '|%'
Sheki
SELECT C.* FROM tblCompany C WHERE @nvcIDs IN(1,2,3) also works, without all the casting and string-searching.
richardtallent
Don't ever do this on tables which are of any considerable size.
erikkallen
A: 

Execute a stored proc and capture the results in a (temp) table for further processing, e.g.:

INSERT INTO someTable EXEC sp_someproc

Example: Shows sp_help output, but ordered by database size:

CREATE TABLE #dbs
(
 name nvarchar(50),
 db_size nvarchar(50),
 owner nvarchar(50),
 dbid int,
 created datetime,
 status nvarchar(255),
 compatiblity_level int
)
INSERT INTO #dbs EXEC sp_helpdb

SELECT * FROM #dbs 
ORDER BY CONVERT(decimal, LTRIM(LEFT(db_size, LEN(db_size)-3))) DESC

DROP TABLE #dbs
Duncan Smart
+4  A: 

SQLCMD

If you've got scripts that you run over and over, but have to change slight details, running ssms in sqlcmd mode is awesome. The sqlcmd command line is pretty spiffy too.

My favourite features are:

  • You get to set variables. Proper variables that don't require jumping through sp_exec hoops
  • You can run multiple scripts one after the other
  • Those scripts can reference the variables in the "outer" script

Rather than gushing any more, Simpletalk by Red Gate did an awesome wrap up of sqlcmd - The SQLCMD Workbench. Donabel Santos has some great SQLCMD examples too.

Dan F
+1  A: 

I use to add this stored procedure to the master db,

Improvements:

  • Trim on Host name, so the copy-paste works on VNC.
  • Added a LOCK option, for just watching what are the current locked processes.

Usage:

  • EXEC sp_who3 'ACTIVE'
  • EXEC sp_who3 'LOCK'
  • EXEC sp_who3 spid_No

That's it.

CREATE procedure sp_who3
       @loginame sysname = NULL --or 'active' or 'lock'
as

declare  @spidlow int,
      @spidhigh int,
      @spid  int,
      @sid  varbinary(85)

select   @spidlow =     0
     ,@spidhigh = 32767


if @loginame is not NULL begin
    if upper(@loginame) = 'ACTIVE' begin
     select spid, ecid, status
      , loginame=rtrim(loginame)
      , hostname=rtrim(hostname)
      , blk=convert(char(5),blocked)
      , dbname = case
          when dbid = 0 then null
          when dbid <> 0 then db_name(dbid)
         end
        ,cmd
     from  master.dbo.sysprocesses
     where spid >= @spidlow and spid <= @spidhigh AND
        upper(cmd) <> 'AWAITING COMMAND'
     return (0)
    end
    if upper(@loginame) = 'LOCK' begin
     select spid , ecid, status
      , loginame=rtrim(loginame)
      , hostname=rtrim(hostname)
      , blk=convert(char(5),blocked)
      , dbname = case
          when dbid = 0 then null
          when dbid <> 0 then db_name(dbid)
         end
        ,cmd
     from  master.dbo.sysprocesses
     where spid >= 0 and spid <= 32767 AND
        upper(cmd) <> 'AWAITING COMMAND'
     AND convert(char(5),blocked) > 0
     return (0)
    end

end

if (@loginame is not NULL
   AND  upper(@loginame) <> 'ACTIVE'
   )
begin
    if (@loginame like '[0-9]%') -- is a spid.
    begin
     select @spid = convert(int, @loginame)
     select spid, ecid, status
      , loginame=rtrim(loginame)
      , hostname=rtrim(hostname)
      , blk=convert(char(5),blocked)
      , dbname = case
          when dbid = 0 then null
          when dbid <> 0 then db_name(dbid)
         end
        ,cmd
     from  master.dbo.sysprocesses
     where spid = @spid
    end
    else
    begin
     select @sid = suser_sid(@loginame)
     if (@sid is null)
     begin
      raiserror(15007,-1,-1,@loginame)
      return (1)
     end
     select spid, ecid, status
      , loginame=rtrim(loginame)
      , hostname=rtrim(hostname)
      , blk=convert(char(5),blocked)
      , dbname = case
          when dbid = 0 then null
          when dbid <> 0 then db_name(dbid)
         end
         ,cmd
     from  master.dbo.sysprocesses
     where sid = @sid
    end
    return (0)
end


/* loginame arg is null */
select spid,
       ecid,
       status
       , loginame=rtrim(loginame)
       , hostname=rtrim(hostname)
       , blk=convert(char(5),blocked)
       , dbname = case
        when dbid = 0 then null
        when dbid <> 0 then db_name(dbid)
       end
       ,cmd
from  master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh


return (0) -- sp_who
Jhonny D. Cano -Leftware-
+3  A: 

Ok here's the few I've got left, shame I missed the start, but keep it up there's some top stuff here!

Query Analyzer

  • Alt+F1 executes sp_help on the selected text
  • Ctrl-D - focus to the database dropdown so you can use select db with cursor keys of letter.

T-Sql

  • if (object_id("nameofobject") IS NOT NULL) begin <do something> end - easiest existence check
  • sp_locks - more in depth locking informaiton than sp_who2 (which is the first port of call)
  • dbcc inputbuffer(spid) - list of top line of executing process (kinda useful but v. brief)
  • dbcc outputbuffer(spid) - list of top line of output of executing process

General T-sql tip

  • With large volumes use sub queries liberally to process data in sets

e.g. to obtain a list of married people over fifty you could select a set of people who are married in a subquery and join with a set of the same people over 50 and output the joined results - please excuse the contrived example

ip
A: 

CTRL-E executes the currently selected text in Query Analyzer.

Chris McCall
+19  A: 

A lot of SQL Server developers still don't seem to know about the OUTPUT clause (SQL Server 2005 and newer) on the DELETE, INSERT and UPDATE statement.

It can be extremely useful to know which rows have been INSERTed, UPDATEd, or DELETEd, and the OUTPUT clause allows to do this very easily - it allows access to the "virtual" tables called inserted and deleted (like in triggers):

DELETE FROM (table)
OUTPUT deleted.ID, deleted.Description
WHERE (condition)

If you're inserting values into a table which has an INT IDENTITY primary key field, with the OUTPUT clause, you can get the inserted new ID right away:

INSERT INTO MyTable(Field1, Field2)
OUTPUT inserted.ID
VALUES (Value1, Value2)

And if you're updating, it can be extremely useful to know what changed - in this case, inserted represents the new values (after the UPDATE), while deleted refers to the old values before the UPDATE:

UPDATE (table)
SET field1 = value1, field2 = value2
OUTPUT inserted.ID, deleted.field1, inserted.field1
WHERE (condition)

If a lot of info will be returned, the output of OUTPUT can also be redirected to a temporary table or a table variable (OUTPUT INTO @myInfoTable).

Extremely useful - and very little known!

Marc

marc_s
wow, new to me and looks awesome.
Neil N
+1  A: 

Using the osql utility to run command line queries/scripts/batches

Kane
+1  A: 

Here's a simple but useful one:

When you're editing table contents manually, you can insert NULL in a column by typing Control-0.

Kyralessa
You can also just type NULL in the cell.
Gordon Bell
+2  A: 

Sql 2000+ DBCC DROPCLEANBUFFERS : Clears the buffers. Useful for testing the speed of queries when the buffer is clean.

Brian
+2  A: 

In SQL Server Management Studio (SSMS) you can highlight an object name in the Object Explorer and press Ctrl-C to copy the name to the clipboard.

There is no need to press F2 or right-click, rename the object to copy the name.

You can also drag and drop an object from the Object Explorer into your query window.

Rob Boek
+6  A: 

Row Constructors

You can insert multiple rows of data with a single insert statement.

INSERT INTO Colors (id, Color)
VALUES (1, 'Red'),
       (2, 'Blue'),
       (3, 'Green'),
       (4, 'Yellow')
Rob Boek
I voted this up, but then tried it in MSSQL 2005, and it doesn't work. 2008 only?
richardtallent
Yes, it's a new feature of 2008.
Rob Boek
That is interesting
Pierre-Alain Vigeant
This was a feature that I missed when I came from DB2 to SQL Server. In DB2, there was a significant speed improvement when using this instead of individual insert statements
Nathan Koop
+3  A: 

Batch Seperator

Most people don't know it, but "GO" is not a SQL command. It is the default batch separator used by the client tools. You can find more info about it in Books Online.

You can change the Batch separator by selecting Tools -> Options in Management Studio, and changing the Batch separator Option in the Query Execution section.

I'm not sure why you would want to do this other than as a prank, but it is a somewhat interesting piece of trivia.

Rob Boek
+1  A: 

These are some SQL Management Studio hidden features I like.

Something I love is that if you hold down the ALT key while highlighting information you can select columnar information and not just whole rows.

In SQL Management Studio you have predefined keyboard shortcuts:

Ctrl+1 runs sp_who Ctrl+2 runs sp_lock Alt+F1 runs sp_help Ctrl+F1 runs sp_helptext

So if you highlight a table name in the editor and press Alt+F1 it will show you the structure of the table.

Jose Chama
+1  A: 

Use

select * from information_schema

to list out all the databases,base tables,sps,views etc in sql server.

Ramesh
How is this hidden? The information_schema view is part of the SQL 92 standard.
Hogan
A: 

Alternative to Kolten's sp_change_users_login:

ALTER USER wacom_app WITH LOGIN = wacom_app
Michhes
+1  A: 

Stored proc sp_MSdependencies tells you about object dependencies in a more useful fashion than sp_depends. For some production releases it's convenient to temporarily disable child table constraints, apply changes then reenable the child table constraints. This is a great way of finding objects that depend on your parent table.

This code disables child table constraints:

create table #deps
( oType int,
  oObjName sysname,
  oOwner nvarchar(200),
  oSequence int
)

insert into #deps  
exec sp_MSdependencies @tableName, null, 1315327

exec sp_MSforeachtable @command1 = 'ALTER TABLE ? NOCHECK CONSTRAINT ALL',
@whereand = ' and o.name in (select oObjName from #deps where oType = 8)'

After the change is applied one can run this code to reenable the constraints:

exec sp_MSforeachtable @command1 = 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL',
@whereand = ' and o.name in (select oObjName from #deps where oType = 8)'

The third parameter is called @flags and it controls what sort of dependencies will be listed. Go read the proc contents to see how you can change @flags for your purposes. The proc uses bit masks to decipher what you want returned.

Sir Wobin
+12  A: 

In Management Studio, you can quickly get a comma-delimited list of columns for a table by :

  1. In the Object Explorer, expand the nodes under a given table (so you will see folders for Columns, Keys, Constraints, Triggers etc.)
  2. Point to the Columns folder and drag into a query.

This is handy when you don't want to use heinous format returned by right-clicking on the table and choosing Script Table As..., then Insert To... This trick does work with the other folders in that it will give you a comma-delimited list of names contained within the folder.

Thomas
+7  A: 

dm_db_index_usage_stats

This allows you to know if data in a table has been updated recently even if you don't have a DateUpdated column on the table.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'MyDatabase')
AND OBJECT_ID=OBJECT_ID('MyTable')

Code from: http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/

Information referenced from: http://stackoverflow.com/questions/837709/sql-server-what-is-the-date-time-of-the-last-inserted-row-of-a-table

Available in SQL 2005 and later

Nathan Koop
+4  A: 

There are times when there's no suitable column to sort by, or you just want the default sort order on a table and you want to enumerate each row. In order to do that you can put "(select 1)" in the "order by" clause and you'd get what you want. Neat, eh?

select row_number() over (order by (select 1)), * from dbo.Table as t
Denis Valeev
+1  A: 

BCP_IN and BCP_OUT perfect for BULK data import and export

sourabh