Sql Server Hidden Features

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
Functions

* HashBytes()
* EncryptByKey
* PIVOT command

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.
See More

Return rows in a random order
SELECT
SomeColumn
FROM
SomeTable
ORDER BY
CHECKSUM(NEWID())

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

Identity Coloumn Insert

Last week i need a scenario to insert values into a table having an identity coloumn only.

eg: create table test(id int identity)

Is it possible to insert values into the without setting identity insert off/on.I find out a method as follows.

insert into test() default values
go 20

This will insert 20 records to the table haaa...
Will update later..