SQL Server Code,Tips and Tricks, Performance Tuning

Use the numbers 3,4,5,6 and the operators +, -, / and * to get the number 28 You can use each number only one time and also each operator on...

Sometimes you want to know what the percentage is of null values in a table for a field Or you might want to know what the percentage of all...

For all of you people who don't have a MSDN subscription, Amazon is selling the developer edition of SQL Server 2005 for $59.99 with fre...

SQL Server 2005 and Visual Studio 2005 are available for download at the MSDN site For people who have a subscription go here http://www.msd...

Below are the links for the SQL Server Certification exams Microsoft Certified Technology Specialist: SQL Server 2005 Microsoft Certified IT...

This is my top 3 SQL server books. They are not in any order. I use all 3 of them frequently The Guru's Guide to Transact-SQL by Ken He...

For those of you who don't want to download the books on line for SQL Server 2005 but would like to see it online here is the link ( htt...

DBCC MEMORYSTATUS This DBCC command provides detailed info about SQL Server memory usage DBCC TRACEON (3604) DBCC Resource DBCC TRACEOFF (...

If you want to know how many occurrences of a substring you have in a string play around with the code below. The trick here is to take the ...

When you do this the nonclustered indexes are dropped and recreated twice, once when you drop the clustered index and then again when you cr...

To get the answer to this question run the code below SELECT SERVERPROPERTY ( 'ProductVersion' ) AS SqlServerVersion, ...

The query below will list all the indexes on all user tables in the database SELECT OBJECT_NAME ( si.id ) AS TableName , ...

To concatenate data from several rows you can use the code below I have declared the variable as varchar 1000, adjust the size if you think...

This is a quick way to find out what roles a user has on the SQL Server Just change the value of @chvLoginName to the login you want DECLARE...

Use the code below USE PUBS GO --Get All Tables SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' GO --Get...

-- Create our Pivot table ** do this only once-- populate it with 1000 rows CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY ) DECLARE ...

The trick to create date ranges without loops is to use a pivot table. How does this work? Run the code below and you will see, only create ...

If you have lookup tables (or other small tables) that are frequently accessed and you don’t want to reduce I/O use the command DBCC PINTABL...

Here is a link to a list of all the MS SQL Server MVP's All of them have website links next to their name Now you can see who all those ...

In Query Analyzer you can save a lot of time by using this trick instead of typing all the column names of a table Hit F8, this will open Ob...