Hidden Features of SQL Server

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, view & UDF
• sp_tables: return a list of all tables and views of database in scope.
• 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. Also views and UDFs. Shortcut is Alt+F1

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

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.

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

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

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"