Problematic Functions!

We have unique dilemma at hand. Two SQL Server 2k5 functions are giving different results;

SP_Columns and Information_Schema.Columns

What could be the reason and how to resolve it? And I thought I was adept with SQL Server! :-D

Submitted by Atif Fasihi

Simple but Tricky :-)

Does the following run for infinity or finite time;

for (uint I = 1 ; I >-1 ; I ++);

Reason your answer


Submitted by Imran Younus

Re: What is wrong with this query?

I received a lot of replies about this query but one man was dead on! There is a reason he is known as the "Babe Jee" of Databases! Abdullah Idris cracked this query in 60 secs flat!

Syntactically, there is nothing wrong with this query however it is very unefficient and the logic does not make any sense. Here is how this query should be written;

SELECT
C.CustomerID, C.CustomerName,
C.CustomerType, C.Address1, C.City,
C.State, S.TotalSales
FROM
Customers C
INNER JOIN
(SELECT
CustomerID, SUM(Sales) as TotalSales
FROM
Sales
GROUP BY
CustomerID) S
ON
C.CustomerID = S.CustomerID

What is wrong with this query?

SELECT
C.CustomerID, C.CustomerName,
C.CustomerType, C.Address1,
C.City, C.State, SUM(S.Sales) as TotalSales
FROM
Customers C
INNER JOIN Sales S
ON C.CustomerID = S.CustomerID
GROUP BY
C.CustomerID, C.CustomerName,
C.CustomerType, C.Address1, C.City, C.State

Re: To Index or Not to Index

Whoa! This really heated up people. It seems there are a lot of people out with comments about this. But the recommendation by Ali Asghar is right on with our in-house experiment.

Microsoft recommends an index on a BIT column, especially in SQL 2K5. Our tables where we have opted for BIT columns, the data distribution ratio is 97:3. We created a non-clustered index and studying the execution plan, we can see a performance gain of 20% VS table scan. Ergo, index that column!

Thank you Ali and Adeel for your detailed insight :-)

If anyone wants the script for the test environment, feel free to contact us.

To Index or Not to Index

Our DBA team has been discussing a lot recently about whether to index a BIT column in our tables or not.

Tell us what do you think. Do you think that a table scan would work just as fast as a non-clustered index seek?

Come on people, show us your DBA skills :-)