Saturday, May 26, 2007

SQL Server 2005 Performance Dashboard Reports

This is a wonderful resource to have with SSMS. With SP2 giving you an window to build custom reports use these for your help to diagonize problems. Download from here

SQL Server 2005 Integration Services Guided Tours

Good resource in SSIS

Friday, May 25, 2007

How to unlock the user with out password reset

there is no direct way... this is a workaround

Alter login [test1] with CHECK_POLICY = OFF
Alter login [test1] with CHECK_POLICY = ON

SQL Server 2005 Deployment Guidance for Web Hosting Environments - Best Practices

a wonderful article which explain almost all issues in SQL Server 2005

SQL Server 2005 Deployment Guidance for Web Hosting Environments - by Alex DeNeui

Saturday, May 19, 2007

FAQ : How to change Authentication Mode in SQL Server 2005

Trying to connect to SQL Server instance using SQL Server Login (mixed mode Authentication ) when the server is actually configured for Windows authentication is a very common issue for a newbie. How to change authentication mode using Management Studio is as follows :-

• Open management Studio and connect to the server
• Right Click on the Server Name and select the properties
• Select Security Page in the left pane
• On Security page there are two options
o Windows Authentication
o SQL Server and Windows Authentication Mode
• Select whichever the mode you want and apply and close (OK)
• The authentication mode is read from the registry when SQL Server first
Starts up. Therefore, if you change the authentication mode, you must stop
and restart the SQL Server service for it to take effect.


Wednesday, May 2, 2007

Optimizable SARGs in SQL Server

SQL Server’s Query Optimizer examines the conditions within the query’s where clause to determine which indexes are actually useful. If SQL Server can optimize the where condition using an index, the condition is referred to as a search argument or SARG for short. However, not every condition is a “sargable” search argument:

✦ Multiple conditions that are ANDed together are SARGs, but ORed conditions are
not SARGs.
✦ Negative search conditions (<>, !>, !<, Not Exists, Not In, Not Like) are not
It’s easy to prove that a row exists, but to prove it doesn’t exist
requires examining every row.
✦ Conditions that begin with wildcards don’t use indexes. An index can quickly
locate Smith, but must scan every row to find any rows with ith anywhere in the
✦ Conditions with expressions are not SQL Server compliant, so these expressions
will be broken down with the use of algebra to aide with the procurement of
valid input data.
✦ If the where clause includes a function, such as a string function, a table
scan is required so every row can be tested with the function applied to the
Locations of visitors to this page