Pinal Dave - SQL Server Performance Tuning Expert

SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case

February 1, 2007

Following function will convert any string to Title Case. I have this function for long time. I do not remember that if I wrote it myself or I modified from original source. Run Following T-SQL statement in query analyzer: SELECT dbo.udf_TitleCase('This function will convert this string to title case!') The…

SQL SERVER – ReIndexing Database Tables and Update Statistics on Tables

January 31, 2007

Pinal Dave

SQL Performance

Best Practices, SQL Constraint and Keys, SQL Cursor, SQL Index, SQL Joins, SQL Scripts, SQL Server DBCC, SQL Stored Procedure

SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions. Let us learn how to do ReIndexing Database Tables and Update Statistics on Tables.

SQL SERVER – Query Analyzer Short Cut to display the text of Stored Procedure

January 30, 2007

Pinal Dave

SQL Tips and Tricks

SQL Server, SQL Server Management Studio, SQL Shortcut, SQL Stored Procedure

This is quick but interesting trick to display the text of Stored Procedure in the result window. Open SQL Query Analyzer >> Tools >> Customize >> Custom Tab
type sp_helptext against Ctrl+3 (or shortcut key of your choice)

SQL SERVER – SQL Joke, SQL Humor, SQL Laugh

January 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Humor

I have heard this joke from my friend. I always wanted to write it but I was not able to find the source of the joke. This joke I have located on DavidM’s Blog on SQLTeam. It is March 1st and the first day of DBMS school The teacher starts…

SQL SERVER – Query Analyzer Shortcuts

January 20, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Download, SQL Shortcut, SQL Utility

Download Query Analyzer Shortcuts (PDF) Shortcut Function Shortcut Function ALT+BREAK Cancel a query CTRL+SHIFT+F2 Clear all bookmarks ALT+F1 Database object information CTRL+SHIFT+INSERT Insert a template ALT+F4 Exit CTRL+SHIFT+L Make selection lowercase CTRL+A Select all CTRL+SHIFT+M Replace template parameters CTRL+B Move the splitter CTRL+SHIFT+P Open CTRL+C Copy CTRL+SHIFT+R Remove comment CTRL+D…

SQL SERVER – Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database

January 10, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure

USE DatabaseName GO CREATE TABLE #temp ( table_name sysname , row_count INT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50)) SET NOCOUNT ON INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?''' SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size FROM #temp a INNER JOIN information_schema.columns b ON a.table_name collate database_default = b.table_name collate…

SQL SERVER – Simple Example of Cursor

January 1, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Cursor, SQL Scripts

UPDATE: For working example using AdventureWorks visit : SQL SERVER – Simple Example of Cursor – Sample Cursor Part 2 This is the simplest example of the SQL Server Cursor. I have used this all the time for any use of Cursor in my T-SQL. DECLARE @AccountID INT DECLARE @getAccountID…