|
|
|
|
Welcome to the "Examples" page. Below you will find lots of different examples. They are all organized by category. Use the scroll bar to browse to a category. Once you find an example you want to view click on the "read more..." link to view the example. If you would like a document containing all the examples click HERE.
If you have any examples you would like to submit please use the "contact us" link to send your example.
|
|
|
|
Performance Monitoring
|
 |
|
| Top 100 Stored Procedures by Execution Count - Friday, June 27, 2008This example displays the top 100 stored procedures based on the number of times they have been executed. read more ...
|
|
| Top 100 Stored Procedures by IO Counts - Monday, June 23, 2008This example shows the top 100 stored procedures executed by IO counts. read more ...
|
|
| Finding the Worst Performing T-SQL Statements - Tuesday, June 10, 2008This example is a stored procedure that will display the worst performing T-SQL statements on your server. It does this by using DMV's and DMF's. read more ...
|
|
|
|
|
|
| Maintain Table without MERGE - Wednesday, February 13, 2008This example uses the MERGE statement to update, insert and delete records for a target table. This example maintains a table to ensure that old records are deleted. read more ...
|
|
| Simple MERGE Statement - Tuesday, February 12, 2008This example dimonstrates how to use the new MERGE statement to perform an UPDATE and INSERT statement with a single MERGE statement. read more ...
|
|
| Inserting Multiple Records With a Single Insert - Monday, February 11, 2008With SQL Server 2005 you now can insert multiple records with a single insert statement. This example shows how this can be done. read more ...
|
|
| Setting Variable Value with Compound Assignment Operator - Sunday, February 10, 2008New with SQL Server 2008 is compound assignments operators. A compound assignment operator is the merging of an operator like "+" (addition) with the "=" (equal) sign. This example will show you how to use the different compound assignment operators available in SQL Server 2008. read more ...
|
|
|
|
|
|
| Getting Output To a File without Column Headings - Saturday, November 22, 2008This examples shows you how you how to get output from a T-SQL statement into a file without it having column headings. read more ...
|
|
| Obtaining SQL Server Setup Information - Thursday, July 17, 2008This example obtains different SQL Server instance setup information by running a number of select statements. read more ...
|
|
| Displaying SQL Server Agent Job Statistics - Thursday, June 19, 2008This example displays SQL Server Agent Job statistics like, maximum duration, average duration and number of executions. read more ...
|
|
| Finding Status of SQL Server Agent Using T-SQL - Sunday, June 15, 2008This example allows you to determine the status of SQL Server Agent using T-SQL. read more ...
|
|
| How to Delete a File with T-SQL - Friday, May 23, 2008This short example shows you how you delete an operating system file with T-SQL. read more ...
|
|
| Output Record Set to File Using T-SQL - Thursday, May 15, 2008This example shows how to T-SQL to ouput the results of a SELECT statement to a file. read more ...
|
|
| List Transaction Log Backups Taken in the Last 24 hours - Sunday, May 04, 2008This short example shows how to find all the transaction log backups for database "SSD" for that where taken in the last 24 hours. read more ...
|
|
| Executing T-SQL Script File Using T-SQL - Wednesday, April 30, 2008This short example shows how to execute a T-SQL script file using T-SQL. This example uses the xp_cmdshell extended stored procedure and the OSQL utility to accomplish submitting a script file using T-SQL. read more ...
|
|
| Finding Missing Indexes - Thursday, April 24, 2008This script uses DMV information to identify missing indexes. SQL Server identifies missing indexes based on commands that have been run since instance last started. The output of this script identifies the value of implement a new index and display a CREATE INDEX statement for the missing index. read more ...
|
|
| Set all databases to DBO ONLY - Sunday, December 02, 2007This script will set all databases to DBO ONLY mode. read more ...
|
|
| Get Record Counts for All Tables in Every Database - Sunday, December 02, 2007This stored procedure produces a report of record counts for every table on your server. read more ...
|
|
|
|
|
|
Handling Duplicate Records
|
|
|
| Identifying Duplicate Records - Sunday, December 02, 2007This script identifies records where every column is an exact duplicate of another record in the table. read more ...
|
|
| Identifying Non-unique Columns - Sunday, December 02, 2007This script identifies the values of a column where their values are not unique within the table. read more ...
|
|
| Updates All But First Duplicate - Sunday, December 02, 2007This script processes through a table that contains duplicate column values, and for each duplicate column value it updates all but one of them. read more ...
|
|
| Updates All but the First One Another Way - Sunday, December 02, 2007This script processes through a table that contains duplicate column values, and for each duplicate column value it updates all but one of them. read more ...
|
|
|
|
|
|
| First Day of the Quarter - Sunday, July 13, 2008This example calculates the first day of the current quarter. read more ...
|
|
| Setting Datetime Column From Character Data - Friday, May 30, 2008This example takes a character string and formats it correctly so it can be used to update a datetime column. read more ...
|
|
| Calculating Age - Saturday, February 23, 2008This examples shows you how to calculate the age between the current date and a date. read more ...
|
|
| Number of Years Between - Wednesday, February 20, 2008This example calculates the number of years beween two different dates. read more ...
|
|
| First Day of the Next Month - Sunday, December 02, 2007This example takes the current date and calculates the first day of next month. read more ...
|
|
| First Day of the Current Month - Sunday, December 02, 2007This example calculates the first day of the current month, based on the current date. Two different method are shown. read more ...
|
|
| Last Day of Current Month - Sunday, December 02, 2007This script uses the current date to calculate the last of the current month. read more ...
|
|
| First Day of Prior Month - Sunday, December 02, 2007These example calculates the first day of prior month using the current date. read more ...
|
|
| Last Day of the Prior Month - Sunday, December 02, 2007The example calculates the last day of the prior month. read more ...
|
|
| Last Day of Next Month - Saturday, December 01, 2007This example calculates the last day of next month. read more ...
|
|
| Calculate the First Day of the Month Another Way - Friday, November 30, 2007
|
|
| Monday of the Current Week - Thursday, November 29, 2007This example calcuates Monday of the current week using the current date. read more ...
|
|
| First Day of the Year - Wednesday, November 28, 2007This example calculates the first day of the current year. read more ...
|
|
|
|
|
|
| Sting Columns Together Using FOR XML PATH - Saturday, April 19, 2008This example takes columns that are on separate records and stings them together using FOR XML PATH. When stinging the columns together it places commas between. read more ...
|
|
| Pivot Table With All Column Values Strung Together - Wednesday, December 19, 2007Builds a report where each line contains all the values stung together from table rows for a given key. read more ...
|
|
| Pivot Table Without Case Statement - Wednesday, December 19, 2007Dynamically create a pivot table report without the use of a case statement. read more ...
|
|
| Pivot Table Summorized By Year With Case Statement - Saturday, December 15, 2007This example creates a pivot report showing summarized column data based on a year using case statement. read more ...
|
|
|
|
|
|
| Padding Record Set with Dummy Records - Monday, April 21, 2008This script pads a record set with dummy records if the record set doesn't have a specific number of records. read more ...
|
|
| Joining two tables based on key - Saturday, February 02, 2008This script performs a simple join of two tables based on a common key. read more ...
|
|
|
|
|
|
| Maximum Usage Length for Each Column in a Table - Sunday, April 06, 2008This example was goes through a table and calculates the maximum size of each column in table, and then displays the results. read more ...
|
|
| Displaying Smallest Row In Table - Friday, March 21, 2008This code displays the smallest row in table. This code will work for any table. It works by building dynamic SQL. read more ...
|
|
| Display the Largest Row in Table - Tuesday, March 18, 2008This example will display the largest row in table. This code will work on any table. It does this by dynamically generating the code. read more ...
|
|
| Finding the Size of the Largest Row in Table - Thursday, March 13, 2008This example determines the size of the largest row in a table. This script will work for any table. read more ...
|
|
| Set Variable In Calling Code Using sp_execute - Wednesday, December 19, 2007This objective of this code is to return a variable to the calling code. In this example the numbers of records for a given table is returned to the calling code. read more ...
|
|
|
|
|
|
Asynchronous / Recursive TSQL
|
|
|
| Asynchronously Processing a SP via TSQL - Sunday, February 03, 2008This example shows how to write a T-SQL script to execute a SP asynchronously, where control is returned to the calling T-SQL code prior to SP code completing. read more ...
|
|
|
|
|
|
| Calculate Running SubTotals - Sunday, June 08, 2008This example creates a running subtotals for each OrderDate. read more ...
|
|
| Calculate Running Total Using Subquery - Monday, June 02, 2008This example calculates the running total. It does this by using a sub query. read more ...
|
|
| Using ROLLUP to Create Subtotals and Grandtotal - Sunday, February 03, 2008This example creates a report that contains subtotals and a grand total for some based on a key. read more ...
|
|
|
|
|
|
| Merge Two or More Columns Together with Comma - Sunday, May 11, 2008This example shows a method where you can merges different columns together with commas. In this example it takes address columns and merges them onto a single column. read more ...
|
|
| Find Email Address in Text Column - Saturday, March 29, 2008This script extracts an email address from within a TEXT Column within a table. Note this example only works in SQL Server 2005 and above. read more ...
|
|
| Find Email Address in VARCHAR - Wednesday, March 26, 2008This script display the email address contained in a VARCHAR column within a table. It does this by parsing apart the VARCHAR column to extract the email address using a single SELECT statement. read more ...
|
|
| Finding Email Address in String - Sunday, March 23, 2008This example extracts an email address from a string. It does this by parsing apart the email address one piece at a time. Eventually displaying just the email address contained in a string. read more ...
|
|
| Upper Case the First Character of Each Word - Sunday, February 03, 2008This is a function that uppercases the first character of each word of a string passed to this function. read more ...
|
|
| Remove White Space Between Two Words - Sunday, February 03, 2008This script removes the extra white space between two words. read more ...
|
|
| Removes White Space Between Multiple Words - Sunday, February 03, 2008This script removes all the white space between each word in a multi word string. read more ...
|
|
| Uppercasing First Character of Column - Sunday, February 03, 2008This script will uppercase the first character of a column. read more ...
|
|
| Padding with Zeroes - Sunday, February 03, 2008
|
|
| Padding with Zeroes Another Way - Sunday, February 03, 2008This script will pad a number with leading zeroes using the replicate function. read more ...
|
|
| Proper Case Person Name - Sunday, February 03, 2008This script will proper case the first, middle, and last name for a person. read more ...
|
|
| Find How Many Times One String Appears In Another - Sunday, February 03, 2008This script counts the number of occurrences of one string within another. read more ...
|
|
|
|
|
|
| Recursive Code Without Using SP - Sunday, February 03, 2008This script calculates the factorial of a number without using a stored procedure. read more ...
|
|
| Converting Integer Numbers to Hex Character String - Sunday, February 03, 2008This code takes an integer number and converts it to a hex character string. read more ...
|
|
|
|
|
|
| List Indexes That Have Not Been Used - Thursday, May 08, 2008Here is an example that will display all the CLUSTERED and NON-CLUSTERED indexes in a database that have not been used. read more ...
|
|
| Redindex All Tables in Database - Friday, February 29, 2008This example will reindex all tables in the current database. read more ...
|
|
| Finding Index Fragmentation - Monday, February 25, 2008This example shows you how to find index fragmentation in a specific SQL Server 2005 database. read more ...
|
|
|
|
|
|
| Calling Web Service from T-SQL - Friday, February 29, 2008Here is an example of how to call a Web Service using T-SQL code. read more ...
|
|
|
|
|
|
| How to Use DENSE_RANK Function - Sunday, March 09, 2008This examples shows you how to use the DENSE_RANK function to display sequential numbers next to your result set read more ...
|
|
| How to Use The RANK Function - Tuesday, March 04, 2008This example shows you two different ways to use the RANK function to provide different ways to number your result set. read more ...
|
|
| How To Use the ROW_Number Function - Monday, March 03, 2008This example show three different ways to use the ROW_NUMBER function to sequentially number rows returned from a select statement. read more ...
|
|
|
|
|
|
| Creating Identity Like Column using NEWID - Wednesday, April 02, 2008This example uses the NEWID function to create an identity like secondard column in a table. read more ...
|
|
|
|
|
|
| Top 3 Records for Each Item Based on RowNumber - Tuesday, April 08, 2008This examples returns the TOP 3 records for an Item based on RowNumber. It does this by performing a correlated subquery based on RowNumber. read more ...
|
|
| TOP 3 Records For Item Based On Date - Saturday, April 05, 2008This example joins two tables and returns the top 3 records in right table for every records in the left table. It does this by using the TOP clause in a correlated subquery. read more ...
|
|
|
|
|
|
| Returning Relational Data In Nested XML Format - Monday, May 19, 2008This example uses a SELECT statement to return relational data as nested XML elements. read more ...
|
|
|
|
|
|
Record Set
|
|
|
| Sequentially Number Records by Using Cursor - Wednesday, July 09, 2008This example sequentially numbers a record set using a cursor. read more ...
|
|
| Sequential Number Records by Altering Table - Saturday, July 05, 2008This examples shows how you can sequential number records by adding an identity column to a table. read more ...
|
|
| Sequential Numbering Record Set Using Temp Table - Tuesday, July 01, 2008This example shows how to number a record set using a temporary table. read more ...
|
|
|
|
|
|
|
|
|