All Articles - SQL Authority with Pinal Dave

SQLAuthority News – Download Microsoft SQL Server 2000/2005 Management Pack

December 24, 2007

SQL, SQL Server, SQL Tips and Tricks, SQLAuthority News

Note: Download Microsoft SQL Server 2000/2005 Management Pack by Microsoft The SQL Server Management Pack monitors the availability and performance of SQL Server 2000 and 2005 and can issue alerts for configuration problems. Availability and performance monitoring is done using synthetic transactions. In addition, the Management Pack collects Event Log…

SQLAuthority News – Jobs, Search, Best Articles, Homepage

December 24, 2007

Pinal Dave

Personal, SQL, SQL Server, SQL Tips and Tricks, SQLAuthority News

Database, DBA

If you are looking for solution of any of your question : Search SQLAuthority If you are looking for best job in IT field : Find Job or email pinal@sqlauthority.com If you are looking for talented IT professional : Post Job or email pinal@sqlauthority.com If you want to read my…

SQL SERVER – 2008 – New DataTypes DATE and TIME

December 23, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DateTime, SQL Scripts

One of our project manager asked me why SQL Server does not have only DATE or TIME datatypes? I thought his question is very valid, he is not DBA however he understands the RDBMS concepts very well. I find his question very interesting. I told him that there are ways…

SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script

December 22, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

Index Rebuild : This process drops the existing Index and Recreates the index. USE AdventureWorks; GO ALTER INDEX ALL ON Production.Product REBUILD GO Index Reorganize : This process physically reorganizes the leaf nodes of the index. USE AdventureWorks; GO ALTER INDEX ALL ON Production.Product REORGANIZE GO Recommendation: Index should be…

SQL SERVER – Enabling Clustered and Non-Clustered Indexes – Interesting Fact

December 21, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

While playing with Indexes I have found following interesting fact. I did some necessary tests to verify that it is true. When a clustered index is disabled, all the nonclustered indexes on the same tables are auto disabled as well. User do not need to disable non-clustered index separately. However,…

SQL SERVER – DISTINCT Keyword Usage and Common Discussion

December 20, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts

Jr. DBA asked me a day ago, how to apply DISTINCT keyword to only first column of SELECT. When asked for additional information about question, he showed me following query. SELECT Roles, FirstName, LastName FROM UserNames He wanted to apply DISTINCT to only Roles and not across FirstName and LastName.…

SQL SERVER – Cumulative Update Package 5 for SQL Server 2005 Service Pack 2

December 19, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Download

Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 2 hotfix to an installation of SQL Server 2005 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in…

SQLAuthority News – RML Utilities for SQL Server

December 19, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Download

The RML utilities allow you to process SQL Server trace files and view reports showing how SQL Server is performing. For example, you can quickly see: Which application, database or login is using the most resources, and which queries are responsible for that Whether there were any plan changes for…

SQL SERVER – Get Information of Index of Tables and Indexed Columns

December 18, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Index, SQL Scripts

Knowledge of T-SQL inbuilt functions and store procedure can save great amount of time for developers. Following is very simple store procedure which can display name of Indexes and the columns on which indexes are created. Very handy stored Procedure. USE AdventureWorks; GO EXEC sp_helpindex 'Person.Address' GO Above SP will…

SQL SERVER – T-SQL Script to Find Details About TempDB Information

December 17, 2007

Pinal Dave

SQL Tips and Tricks

SQL Scripts, SQL Server, SQL TempDB

Two days ago I wrote an article about SQL SERVER – TempDB Restrictions – Temp Database Restrictions. Since then I have received few emails asking details about Temp DB. I use following T-SQL Script to know details about my TempDB. This script is a pretty old script but it does work great most of the time. I strongly encourage all of you to use a script to check your TempDB Information.