My Place For SQL

Who designed MSDB ??

2007-05-24T20:39:47-05:00:00

It looks abnoxious when as MS MVP I advocate MOF/MSF Database Designs for customers and USers but still one of our main System Databases named MSDB has columns which are related to Datetime have been kept as INT...

WHo Designed this Database and WHo pruned it... I wish to meet them .. God... It wasted my extra time and Extra resources to get them casted , So that my RDL can just publish the report on SYSJOB and SYSJOB HISTORY..

RUN_DATE and RUN_TIME why are they int type ?? By any standard both should have been Datetime Types.

It is not a BUG but a Flaw in Database Design Understanding???

Why GUEST ACCOUNT SQL Server

2007-05-24T20:36:09-05:00:00

It has been long time that I am back on my Blog... But trust me I missed it more than it might have missed me..

Okay here is a kwel finding.. For those who are confused about Guest account in SQL server Databases..

Well we need GUEST account in Master and TEMPDB ... because when when we create login that person should get authenticated at least w.r.t Public Profile.. then onwards suppose if he/she hasnt been added to any Database then he will keep on looking at Master DB from Public profile with no rights or rights conferred on Guest Account.

IS IT A SECURITY THREAT? Partially yes .. I feel so thats why I deny permission on all SYS OBJECT in these two Databases for Guest Account.. but it is mandatory to keep Guest account we cant remove it...

I wish Microsoft comes out with some other model and GUest account is taken off because it effects DBAs thought process more than it effects my SQL Server

MS PROJECT gbui://mainpage.htm/ Fixed

2006-10-04T00:28:00-05:00:00

I was fiddling

I was working on Project and suddenly this error took me to nuts

ERROR :- The command is attempting to use a webpage from the site gbui://blank.htm/. You have not identified this site as a trusted site in Internet Explorer.

Microsoft Project cannot display pages from untrusted sites, so we cannot execute this command. If you want to use pages from the site gbui://blank.htm/ in Microsoft Project, you need to make this site a trusted site in Internet Explorer. You should make a site trusted only if you are sure of the site's identity, and you trust the site will not attempt to damage your computer or your data. Note that when you trust a site in Internet Explorer, you are trusting the entire domain to which that site belongs. For example, if you trust the site http://www.microsoft.com/widgets, you are trusting the entire http://www.microsoft.com domain

click here for solution

http://support.microsoft.com/kb/887028/en-us?spid=2525&sid=global

or

Method 2: Add the GBUIDisplayToggle registry key value in the registry

To add the GBUIDisplayToggle registry key value in the registry, follow these steps.Warning If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

Click Start, click Run, type regedit , and then click OK.

Locate and then click the following subkey as appropriate.

•	For Project 2002: HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\MS Project\Options\Interface
•	For Project 2003: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\MS Project\Options\Interface

Note If the Interface registry subkey is not listed under the Options subkey, you must create the Interface subkey. To create the Interface subkey, right-click the Options subkey, point to New, click Key, and then type Interface. I couldnt find INTERFACE in my regedit and I created it and it worked fine

On the Edit menu, point to New, and then click DWORD Value.

Type GBUIDisplayToggle, and then press ENTER.

Right-click GBUIDisplayToggle, and then click Modify.

In the Edit DWORD Value dialog box, make sure that the Value data box contains a NULL value (“0”), and then click OK.

Close Registry Editor.

Myopia about technology in Kerela Indian State

2006-09-11T02:49:00-05:00:00

It doesn't happen everyday that a government sets itself in a way that may well turn out to be a disaster. The recent decision by Kerala Govt. to mandate Linux in high schools is just one of them. This is in the aftermath of having banned Colas in the state. (So tell me are colas more dangerous or are cigrattes and alchol? Why not ban them instead? )

"Stallman has inspired Kerala’s transition" quotes the education minister. Since when did foreign nationals start insipiring Indian govts. to take action? The interest of a state and region are now better understod by a foreign national?

"Achuthanandan’s government to develop the state as a FOSS (free and open software systems) destination" - WHY? WHY? the govt. is now deciding on behalf of its citizens what technology will students study? after liberalisation in '91, it was amply clear that there is merit in delinking economy and politics. why link up politics and technology now?

I would really like to know whether this decision was based on any project report or finding? Why not share with the world the report if it exists? There is no single reference to any benefit the state is going to derive by making this decision in any of the news reports! Why doesn't the CM or the education minister elaborate or is that there is no benefit?

Something to add on microsoft community kerala

Digg this story:

http://digg.com/tech_news/Technology_Myopia_in_Kerala

http://manand.typepad.com/thoughts_from_india/2006/08/is_teaching_onl.html

http://vinodunny.spaces.live.com/blog/cns!F162DB18909CE884!257.entry

http://abhishekkant.blogspot.com/2006/09/technology-myopia-in-kerala.html

Tool kit of Windows Utilities

2006-07-21T03:01:00-05:00:00

Found a nice article for Professionals, really liked it

http://www.computerperformance.co.uk/Litmus/tools_ezine.htm#DCDIAG%20and%20NETDIAG

Webcasts by Microsoft Most Valuable Professionals (MVPs)

2006-07-21T02:05:00-05:00:00

Webcasts by Microsoft Most Valuable Professionals (MVPs)

Based on your tremendous response, we now bring you LIVE Webcasts 5 times a week.

Attend these LIVE Webcasts by experts from Microsoft and get on to the edge of the steep-learning curve.

Participate in the Webcast Contest during the Webcast and 2 lucky winners will win a cool Orchid Music Player.

Watch Microsoft Experts all this July & August – LIVE.

Register Now!

Webcasts by Microsoft MVPs: July 24, 2006 - July 28, 2006

Date	Topic
July 24, 2006	Data integration in Excel (includes Office 2007) and how to incorporate it into your applications Speaker: Nitin Paranjape (Chairman & MD, Maestros Mediline Sys Ltd )
July 25, 2006	All about WCF “Indigo” Contracts Speaker: Manoj Ganapathi (Cognizant Technology Solutions)
July 26, 2006	Get the Power of Microsoft Atlas to your Web Application. Speaker: Saurabh Verma (Co-founder & Chief Software Architect of "The Perfect Future")
July 27, 2006	Sharepoint Portal 2007 - Managing your portal with Site Content Types and Workflows Speaker: Saurabh Verma (Co-founder & Chief Software Architect of "The Perfect Future")
July 28, 2006	WSS 3.0 Architecture and Enhancements Speaker: Ashvini Shahane (Member –Research Laboratory in Synergetics)

All about WPF: July 31, 2006 - Aug 04, 2006

Date	Topic
July 31, 2006	Introduction to WPF and Layout Speaker: Tarun Anand (CEO, "The Perfect Future")
Aug 01, 2006	Introduction to WPF Controls Speaker: Tarun Anand (CEO, "The Perfect Future")
Aug 02, 2006	Resources and styling Speaker: Tarun Anand (CEO, "The Perfect Future")
Aug 03, 2006	Data Binding Speaker: Tarun Anand (CEO, "The Perfect Future")
Aug 04, 2006	Graphics fundamentals and Animations with WPF Speaker: Tarun Anand (CEO, "The Perfect Future")

Please Note:

Each Webcast / Live Meeting Session has a Maximum Capacity of 200 participants. You are advised to join in the Live Meeting Session 15 minutes prior to the scheduled time to ensure your participation
You need to register for each Webcast that you intend to attend. Registering for one Webcast does not register you for all webcasts in the series.
Microsoft employees, vendors, and their relatives are not eligible to participate in the contest and win prizes.

This meeting will broadcast internet audio directly to your computer. Please ensure that you have Windows Media Player 9 or higher installed.

First-time users: Click here to install the Windows-based Live Meeting Console before your meeting.

To know more about the MVP program, please visit www.microsoft.com/india/mvp

Recommended private "Heartbeat" configuration on a cluster server

2006-06-29T00:21:00-05:00:00

I was quite stupid with respect to network configuration and being a sure shot DBA one needs to understand what my Systems guy is doing to network and SAN .. here I found a quite useful link to Cluster w.r.t private network heartbeat connectivity and support

http://support.microsoft.com/Default.aspx?id=258750

Check it out

Vista for lesser mortals

2006-06-01T04:21:00-05:00:00

Few days back someone made a comment “what the hell you know about VISTA” .. I didn't respond much ..but smiled...

Slowly I tried to navigate and gather information about Vista if not knowledge about it.... somehow I felt

Comfortable enough to write some lines about it... Here I go ...

SIDE TRACK :-

Few days back we had three day Session on VISTA for dev MVPS and Vinod provided some nice presentations and demos...
although I being long away from DEV but still enoyed it asDI was able to get a good insight of VISTA...
Also I met with Allen and Brij both have excelled a lot..hats off to them ...
After session meeting with Abhishek and Saurabh was equally fantastic...
Checked out the new Office of www.theperfectfuture.com with kapil Suri proved to be a nice stuff...
Saurabh and Tarun are doing a great JOB..My Best wishes to them....

BACK TO RACE

Okay... Comming back to where I left the topic... !!!
Now what do we look for when we talk of new OS ...

1 User experience

2. Developer platform

3. IT pro perspective

We will take all these three one by one...well everything is getting closed every second day
we haveing new updates from MS.
What does it take to install VISTA .

well we have two ways

:::: Windows Vista Capable :::

Modern processor (at least 800 MHz)1 ,
512 MB RAM ,
DirectX® 9 capable,Display Driver Model (WDDM) support recommended)
Rest all as normal PC

::::: Windows Vista Premium Ready :::

1 GHz 32-bit (x86) or 64-bit (x64)1,
1 GB memory ,
Runs Windows Aero2 (Windows Display Driver Model (WDDM) support recommended),
Graphics Memory - - 128 MB
HDD - - 40 GB
HDD Free Space - - 15 GB
Optical Drive - - DVD-ROM drive3
Audio - - Audio output capability
Internet - - Internet access capability

Although some features available in specific premium editions of Windows Vista, such as the ability to watch and record live TV, may require additional hardware.

More guidance on the requirements for these specific features is available at the Get Ready Web site.
Guidance for businesses will be refreshed on TechNet, see here: http://www.microsoft.com/technet/windowsvista/default.mspx

Supporting resources for more information:

www.windowsvista.com/getready
http://www.windowsmarketplace.com/getready
http://www.microsoft.com/technet/windowsvista/default.mspx

Now lets analyse plain features of VISTA from user experience

1. Enhanced Search
2. Software rendering using Processor is more powerful than using GPU card
3. User Account Controls
4. Side shows
5. Mobility content
6. Navigation
7. use of New Side bar gadgets which will soon develop into craze in india that I am sure off
8. Collaboration services which might break the Windows Live meeting {offff did I predict too much}
9. Shared Network projection etc

Now Lets go to IT PRO Perspective

1. User Account Control
2. eleveated privelege
3. Deny admin by default {Ffffff it is going to create major controversay and user denials unless our admins are smart to counter this menance with GPs}
4. RegKey Virtual which will help in saving registry information that is awesome feature
5. Shield Priveleges in Application
6. LUA or what Ic all Lowest User Account predictor which makes some thing of protected mode in IE
7. System Schemas and Cannonical Folders will be used more now.
8. Common Full text indexer
9. Federated Identity :: Auto Pickup Validation
10. Admin prioveleges comming to child process ..spawning privelges to child accounts has been prevented
11. RSS Feed reader which you must have been knowing already in IE 7 but now we feed filters and feed managers as well.
12. Windows Feedback for Error traping has been enhanced to help your application all you need is an account at www.winqual.microsoft.com
13. Document API recovery API restrarts as per comaptibility mode which comes down as it tries to restart .. till it gives up

Oh Finallly Developer perspective which has huge and surely I need to write a one more blog for that

what started as Indigo and avalon have culminated into WCF and WPF I leave that to great developers but after this all Lets talk about my cold and raw thoughts about VISTA.. why the hell I ahd to all this..
Here are my cold thoughts about it ... of All CTP s December CTP is most stable as far as News goes ....
But I am working with both of them and the rigorous testing in FEB CTP with little success and we had issues like

With Outlook 2003 which didnt respond at times and it restarts automatically while drafting an e-mail.
Opening of outlook 2003 PST file.
Cisco WIFI card and Cisco VPN client (ver 4.6) not working

Cheers to Vista .. Hope teched 2006 will rock more ...my New Lab testing from tomorrow will rock even deeper...
good night

Teched 2006 :: Things to Watch For from ::: SQL IT perspective

2006-05-31T05:06:00-05:00:00

Well lots of buzz in the town.. watched some kwel thoughts around and listened to some wild wind about Teched 2006. But here is my recommended list for IT pro people and particularly those who work in remote management

Believe me you should start from here {Click it}.... where Teched is more than we thought about it ....

Dont miss the big MVP Logo on the left handside.... ask me if you want to know what it means...???

Some good sessions that I am interested in

Using CLR inside SQL Server: Best Practices
[Nauzad Kapdia]

Data-Warehousing using 64-bit SQL Server: Best Practices

SQL Internals: Core Database Engine Changes in SQL Server 2005
[Deepak, Mandar Inamdar and Arvind Shyamsundar]

Analysis Services: Best Practices for Effective BI
[Ramakrishnan K and PSN Kumar]

Microsoft Operations Manager v3: Introduction
[Satya Narayana KP]

SQL Server Monitoring Tools: The Key to Running 24x7 SQL Deployments
[Vinod Kumar]

Getting the Best out of your SQL Box: Performance Tuning SQL Server 2005
[Deepak, Mandar Inamdar and Arvind Shyamsundar]

Reporting Services - Advanced Scenarios
[Kumar Sacchidanandam]

Well for Database Administrators two key topics from management perspective are very important one is MOM and other is Tools for monitoring ...

What we will be missing here is a key session on Mirroring and High Availibility in SQL 2005 .

Many guys ahve been asking about it .... may be in TBD we will have it????

Windows Vista Release Delayed

2006-05-04T03:53:00-05:00:00

Windows Vista Release Delayed

Microsoft said today that consumer versions of its next-generation version of Windows would not hit the market until January 2007, dashing plans that it would be on PCs for sale during the 2006 end-of-year holiday season.

Check out more here

http://www.internetnews.com/ent-news/article.php/3593251

Optimization Examples I used in my recent Seminar (Unedited and Without Formating)

2005-05-02T12:56:00-05:00:00

It was packed room of some 40 Odd people in Noida and from developers to DBAs anb my 8 Hour long Seminar/Training/ Presentation for them.

I have used lots of examples in that seminar worth mentioning are the last 13 examples but you can just try reading from here and hopefuly after page reads you will be on track

WARNING First few pages are un formatted and might confuse you but carry on soon you will see a nice road ahead.Read SARGS,OPTIMIZER, and INDEXs for refernce here

Lets start from here:-

CASE 1

USE Northwind
go
SELECT CompanyName
FROM Customers
WHERE Country = 'Mexico'
--Results--

/* When the Query Optimizer receives this request it analyzes the statement and determines how the data should be accessed (the execution plan). The Query Optimizer can decide to do one of three things to determine which rows have
‘Mexico’ in the Country column. It can do a table scan, which looks at all the rows
in the table and examines the contents of Country ; it can use a clustered index scan; or it can use an index that references Country . If the Query Optimizer cannot choose a specific index for an operation, it will always choose to use a clustered index scan if one exists on the target table. When a clustered index is not present, a table scan is used. In order for the Query Optimizer to choose the third option, the column on which the comparison operation is being performed must have an associated index.*/

/*In order for the Query Optimizer to use an index with a statement the comparison
operation must be able to be resolved into a search argument (SARG). To
produce a valid SARG, the comparison operation must be of the form shown here.
Column ComparisonOperator Constant/Variable*/

If either the Column or Constant/Variable is acted on by a function (e.g., SUBSTRING) a valid SARG is not
produced.

CASE 2

The following example uses the SUBSTRING function on the Country column, so a SARG cannot be produced and an index cannot be used.

USE Northwind
go
SELECT CompanyName,
Country
FROM Customers
WHERE SUBSTRING(Country,1,1) = 'A'

---Adding an Index to Reduce Query Costs The SELECT statements executed thus far have referenced the
-- Customers table in the Northwind database. You can use the system stored procedure sp_helpindex
-- to see the existing indexes on Customers.

CASE 3

USE Northwind
go
sp_helpindex 'Customers'

--Results--
index_name index_description index_keys
------------- ------------------------------------------------- -------------
City nonclustered located on PRIMARY City
CompanyName nonclustered located on PRIMARY CompanyName
PK_Customers clustered, unique, primary key located on PRIMARY CustomerID
PostalCode nonclustered located on PRIMARY PostalCode
Region nonclustered located on PRIMARY Region

CASE 4
---What is all about Lets delve into the details of the estimated execution plan,back to PPt --Hope you know to use (:):):))

SELECT CompanyName
FROM Customers
WHERE Country = 'Mexico'

--The Estimated Cost for the Clustered Index Scan is .0392 seconds; this makes
--up 100 percent of the time required to execute the statement.
--Create a non-clustered index on Country and then re-examine the estimated execution plan.

CREATE NONCLUSTERED INDEX VEERSQL_CustomersCountry
ON Customers (Country)

SELECT CompanyName
FROM Customers
WHERE Country = 'Mexico'

-- Two operators are used in the new execution plan. The first is Index Seek and the second is Bookmark Lookup.
-- Notice that the cost to perform the Index Seek is 34 percent of the total costs and the Bookmark Lookup is 66 percent of -- the total costs.

-- When you add the Estimated Cost for each operator (you can’t see the value
-- for the Bookmark Lookup) you get .018918.
-- When the costs for pre- and post-index queries are compared, you see that
-- the query that used the index realized a 52 percent reduction in costs
Simple Select ((.0392-.0189)/.0392)*100=51.78)
--- If this type of query is performed on a regular basis and Customers
--- is going to contain a large number of rows, then the index should remain in place.
--- On the other hand, if the query is only executed periodically or
--- Customers is not going to contain a larger number of rows, the index should be deleted.

/* Creating Valid Search Arguments BUt I ll come back on Indexes again

VALID SEARCH ARGS is */
Column ComparisonOperator Constant/Variable
/* In order for the Query Optimizer to use an existing index, however, the comparison operation
must be able to be resolved into a valid SARG.

SELECT
presented in the previous section is affected when the SARG requirement is not met.

SELECT CompanyName
FROM Customers
WHERE SUBSTRING(Country,1,1) = 'A'

/*The SUBSTRING function caused the Query Optimizer to ignore the index and
use a Clustered Index Scan. This caused the estimated cost to revert back to .0392,

But I doubt it wont here because the Data is less
and the OPtimizer will take Cache Plan*/

SELECT CompanyName
FROM Customers
WHERE Country LIKE 'A%'

--WRONG QUERRY

SELECT CompanyName
FROM Customers
WHERE Country LIKE '_A%'

IMPORTANT CASE 5

--Let us try to determine the most Effective Querry

--Query 1 -- LEFT JOIN
SELECT CompanyName
FROM Customers a
LEFT JOIN ORDERS b ON a.CustomerID = b.CustomerID
WHERE b.CustomerID IS NULL
--Query 2 -- NOT EXISTS
SELECT CompanyName
FROM Customers a
WHERE NOT EXISTS (SELECT *
FROM Orders b
WHERE a.CustomerID = b.CustomerID)
--Query 3 -- NOT IN
SELECT CompanyName
FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID
FROM Orders)

Check the batch

Now what I am doing is loading the Data

SELECT *
INTO Orders2
FROM Orders
go
INSERT Orders2
SELECT CustomerID,
EmployeeID,
OrderDate,
RequiredDate,
ShippedDate,
ShipVia,
Freight,
ShipName,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry
FROM Orders

-- Now let me run teh First THree querries...
-- and see the difference by modifying the
--

SELECT CompanyName
FROM Customers a
LEFT JOIN ORDERS2 b ON a.CustomerID = b.CustomerID
WHERE b.CustomerID IS NULL
--Query 2 -- NOT EXISTS
SELECT CompanyName
FROM Customers a
WHERE NOT EXISTS (SELECT *
FROM Orders2 b
WHERE a.CustomerID = b.CustomerID)
--Query 3 -- NOT IN
SELECT CompanyName
FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID
FROM Orders2)

The relative cost for Query 3 increased to over 44 percent. This is a good
example of how the size of the database affects a poorly written query.
Wat is good today may not be tomorrow..

CASE 6

--We will Use profiler and trace the events and then Querry analyser to suggest for Index Tuning wizard
--I wil use the same Orders2 ans see what it sugegst

USE Northwind
go
SELECT *
FROM Orders2
Go
SELECT *
FROM Orders2
WHERE CustomerID = 'CENTC'
Go
--Some updates
SET ShipName = 'El Centro Rocket Shop'
WHERE CustomerID = 'VICTE'
GO
SELECT *
FROM Orders2
WHERE CustomerID = 'VICTE'
Go

DEMOnstrate how to use Profiler tracing
---Create the Index and then we can drop it
-- check for the Estimation after index is created..do map
-- the performanmce Benefits
/*Beware SQL Profiler trace files grow at an alarming rate when they capture data on
a server that generates a lot of activity. Once you create a trace file, make
sure you monitor its growth rate for the first few hours so you can ensure
that you have plenty of available disk space to hold the contents.*/

--Dropping Index Foo
DROP INDEX Orders2.Orders21

--Use ITW via Query Analyzer to analyze the following SELECT.
SELECT *
FROM Orders2
WHERE ShipName LIKE 'A%'
ORDER BY ShipName

/*
we can analyse more with Set Statistics IO
It is very importantcommand
LETS take a tour of recommendatiosn on Query Writimng ..A very important tour..*/

SOME RECOMMENDATIONS

1. operate on Small result Sets
Limit the numebr of Columns in select list

set statistics IO on
select productId,SupplierId from products where supplierid = 1
--Table 'Products'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

select * from products where supplierid = 1
---Table 'Products'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0.

2. Use Highly Selective Where Clause

-- A column has a high degree of selectivity
-- If it is likely to return a small number of rows
-- for a given value specified in a predicate argument.
-- The distribution statistics can be used to estimate the degree of selectivity:
-- As they say selectivity governs the rules of chossing Non clustered indexes also
3. Using Indexes Effectviely as we did some query on this feature already.
4. Important is to avoid Non INdexable Search COnditions

Example INdexable = , > , <=, >= , Between , and some like conditions
Non indexable Exclusion != , !> and ,!< , Not Exists, not in , not like , or ,like '%%'

Select * from [Order Details]
where OrderId In (10300, 10301,10302,10303)
---Table 'Order Details'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0.
-- A clustered Index Seek wovv then whats the issue:):):) Just check the Argument and scan count
--HERE WE HAVE MORE THAN FOUR sCANS HAPPENING
Select * from [Order Details]
where OrderId between 10300 and 10303
--Table 'Order Details'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

5. LETS CHECK lIKE CONDITION

SELECT * FROM pRODUCTS WHERE pRODUCTNAME LIKE 'S%'
--Table 'Products'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0.

--IT CONVERTS THE LIKE CONDITION INTO sEARCHABLE INDEX CHECK TH >= COPNDITIOPN

SELECT * FROM PRODUCTS WHERE PRODUCTNAME >='s' AND pRODUCTNAME <'t'
--Table 'Products'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0.
THUS LEADING CHARACTERS IN liKE OPTIMIZE THE sEARCH TO ALLOW THE USAGE OF InDEX

6. AVOID ARITHMETIC OPERATORS

Select * from orders where orderid*2 = 22000
--Table 'Orders'. Scan count 1, logical reads 5, physical reads 3, read-ahead reads 2.
--See the Execution Plan which prevented the use of index on the column
Select * from orders where orderid = 22000/2
--Table 'Orders'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

7. Sunstring vs like

select * from orders where substring(Shippostalcode,1,1)='V'
--Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 20.
--see the Execplan where index on ShipPostalcode index is not used and Clustered is used
--Now lets see with LIke
select * from orders where Shippostalcode like 'V%'
--Table 'Orders'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0. and a
-- also see the Index Selectivity Shippostal code is used

8 always use datepart queries for Date opperators
A known truth ..dont cast them

9 Avoid Optimizer hints lets see how they can mislead us

---JOin Hint , Index hint and Firceplan hint let Optimizer do it

Select * from [order details] Od join Orders O
On Od.Orderid = O.Orderid
where O.shipCountry = 'spain'

54 row(s) affected)

--Table 'Order Details'. Scan count 23, logical reads 46, physical reads 0, read-ahead reads 0.
--Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.
--it went for nested Loop with Inner Join HInt with Order Details outside and Order PK inside

--So if we suppose it will use this as hint we are wrong Lets see how

Select * from [order details] Od inner loop join Orders O
On Od.Orderid = O.Orderid
where O.shipCountry = 'spain'

---Table 'Orders'. Scan count 2155, logical reads 4504, physical reads 0, read-ahead reads 0.
---Table 'Order Details'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0.

-- THus Join Hint costed us more here OrderDetail is inside and Orders is inside as such the Scans where more
-- because the JOIN hint forced the Optimizer to use the first table
-- So you must check the iterative and come to the conclusion by Analysisng the Hash Mapping of Joins
10 Dont use INDEX HInts unless you are prety Sure

Select * from Orders With (Index(Pk_orders))Where OrderId * 2 = 2200
--Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0.

Select * from Orders Where OrderId * 2 = 2200
--Table 'Orders'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

--Check the Displyed Subtree Costa nd also see the NOnClustered Index selectivity

11 Avoid Implicit Conversion
For Example

Declare @int INT , @flt FLOAT
Set @int = 10
Set @flt = 10

Select * from products where SupplierID = @int -- Int
Select * from products where SupplierID = @flt -- Int
/*
(1 row(s) affected)

Table 'Products'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

(1 row(s) affected)

Table 'Products'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.

*/ All same from Statis IO

---But See th Disply Estims Exec pla and you will see how Implicti Conversion Behaved in Case of Float ..
-- We must understand the Data TYpe Precedence
/* Implicit conversions are those conversions that occur without specifying either
the CAST or CONVERT function. Explicit conversions are those conversions that
require the CAST (CONVERT) function to be specified. This chart shows all
explicit and implicit data type conversions allowed for SQL Server
system-supplied data types, including bigint and sql_variant. */

--Column Type is kept as it is and the Constant for comparison is
--Implicted COnverted with disregard to Precedence

-- I will show more on thsi SQL Help File where
--we Have precedence Chart Lower to upper is Implicited Converted

12 QUESTION what is Better UNION or UNION All

select * from products where SupplierID = 1
union
Select * from products where SupplierID =2
0.0311
--Sp_help Products
--Table 'Products'. Scan count 2, logical reads 16, physical reads 0, read-ahead reads 0.
--Merge Join cost 18%
select * from products where SupplierID
= 1
union all
Select * from products where SupplierID
=2
0.0254
--Table 'Products'. Scan count 2, logical reads 16, physical reads 0, read-ahead reads 0.
--THis uses Concatenation whose cost is 0
--Both return Same rows???
--Use UnION All as it avoids the Detecting of removing of duplicates

13 Use of Subqueries over Joins

Select * into OD from [Order Details]
Create Clustered Index ProdId On OD(ProductID)

Select * into Prod from products
create Nonclustered Index SuppId On prod(SupplierId)

-------------------------
Select Od.* from OD where OD.ProductID in (Select p.productId from Prod P
Where P.SupplierId = 10)
--0.0311 -- 61%
--Table 'OD'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
--Table 'Prod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

Select Od.* from OD , prod P
where Od.ProductiD = P.ProductId
and SupplierId = 10
--0.0197 -- 38%
--Table 'OD'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
--Table 'Prod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

--Now if I create the a Non clustered Index ProdId on Prod to avoid this Sort in the Middle cabn the Query behave better

Create Unique NonClustered Index ProdID On Prod(ProductId)
Now we execute thequery again
Select Od.* from OD where OD.ProductID in (Select p.productId from Prod P
Where P.SupplierId = 10)

--Table 'OD'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
--Table 'Prod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
-->>>>>>>>>>.But what has changed then Cost has come to 0.0197 from 0.0311

--> No more SORT for Distinct Selects as such Query has improved

14 In Sps

Set NOCOUNT ON
--Batch Querries
Set noCOunt OF

Upgrading SQL 2000 Windows FailOver Cluster to SQL 2005

2006-03-07T03:36:00-06:00:00

A very nice article and quite useful as well

http://msdn2.microsoft.com/en-us/library/ms191295.aspx