Hidden Features of SQL Server

Hello Again,

Here is an interesting web page that shows hidden features of SQL Server which are not documented. There are stored procedures and other tricks which are very useful.

http://stackoverflow.com/questions/121243/hidden-features-of-sql-server

Below is a small list from the site:

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

 
Have a look and book mark the link for future use, or just come back here :)
 
Enjoy,
 
Rudy

Microsoft Feature Pack and Upgrade advisor for SQL Server 2012 SP1

Hello,

The link below it the Microsoft site for additional feature packs that you can install onto your SQL Server 2012 SP1 installation. There are a hand full of excellent features you can add. One of them is the upgrade advisor.

Here is what Microsoft says about the upgrade advisor

"Microsoft Upgrade Advisor analyzes instances of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 in preparation for upgrading to SQL Server 2012. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it. "

http://www.microsoft.com/en-us/download/details.aspx?id=35580

Upgrade advisors are also available for older versions of SQL Server, just do a search on the link above.

It's a great tool to help you migrate/upgrade your older SQL Servers to the newer version.

Enjoy,

Rudy

Microsoft Support Lifecycle for SQL Server

Hello Everyone,

Here is a link to find out when your version of SQL Server will be out of support.

http://support.microsoft.com/lifecycle/?c2=1044

You can save the page to a CSV file or just go to the site for updates.

Enjoy!

Rudy