Wednesday, August 03, 2005

 

Common (TOP 10?) T-SQL Programming mistakes

1. TOP Without ORDER BY
SQL Server doesn’t guarantee the order of records returned, if an explicit ORDER BY clause is not specified. Records are not returned in the order they were created. Most of the times records are returned in the order of the clustered index, but not necessarily always.
Eg.


CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

--Add a clustered index on ClientName
--CREATE CLUSTERED INDEX idx1 ON #Client(ClientName)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

SELECT TOP 1 * FROM #Client
--ORDER BY ClientID

DROP TABLE #Client

2. TOP Without TIES
If you are expecting TIES in the result and you want all tied records, (like in the case of finding rank) you should use WITH TIES clause with the SELECT statement.

Eg.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 30000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

SELECT TOP 1 * FROM #Client
ORDER BY AnnualIncome DESC

DROP TABLE #Client


3. Not Using an Alias

Consider the following query.


CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

CREATE TABLE #Account(AccountId int IDENTITY(1,1), ClientId int, AccountValue int)


--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(2, 1500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(3, 2000)

SELECT * FROM #Account
WHERE AccountId IN(SELECT AccountId FROM #Client)

DROP TABLE #Client
DROP TABLE #Account

Obviously there is no column called AccountId in the #Client table. Probably you are expecting an error, but the query returns all the records from the #Account table. What happened behind the scenes is that, SQL Server looks for the accountId column in the #Client table and when it fails to find the column there, it looks for the column in the outer table. This behaviour is referred as fancy scoping.

4. Aggreagation And JOINS

Be careful when doing aggregation on joined resultset. Chances are there that you may getting the wrong result. Consider the following example.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

CREATE TABLE #Account(AccountId int IDENTITY(1,1), ClientId int, AccountValue int)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 1500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(2, 1500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(3, 2000)

SELECT C.ClientId, C.ClientName,SUM(C.AnnualIncome)
FROM #Client C
INNER JOIN #Account A
ON C.ClientId = A.ClientId
GROUP BY C.ClientId, C.ClientName
ORDER BY C.ClientId

DROP TABLE #Client
DROP TABLE #Account

5. NOT IN AND NULL
If you are using the NOT IN Opereator with a subquery and the subquery contains any NULL values, the subquery will return NULL!. This can be dangerous, and this is not the case if you use IN.

Eg.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

CREATE TABLE #Account(AccountId int IDENTITY(1,1), ClientId int, AccountValue int)


--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(2, 1500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(NULL, 2000)

SELECT * FROM #Client
WHERE ClientID NOT IN(SELECT ClientId FROM #Account)

DROP TABLE #Client
DROP TABLE #Account

6. Aggregation And NULL

If any of the columns you are apllying aggregate functions contains NULL , SQL Server will return a warning, “Warning: Null value is eliminated by an aggregate or other SET operation.”. If you are accessing the recordset using ADO, you might face problems. Either use ISNULL() function or SET ANSI_WARNINGS OFF. Setting ANSI_WARNINGS inside an SP will cause it to recompile everytime.

Eg.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 30000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', NULL)

SELECT SUM(AnnualIncome) FROM #Client

DROP TABLE #Client


7. OUTER JOIN AND WHERE Condition

Consider the following query.



CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

CREATE TABLE #Account(AccountId int IDENTITY(1,1), ClientId int, AccountValue int)


--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(2, 1500)

--To find Clients who has an annualIncome > 10000
SELECT C.ClientName
FROM #Client C
LEFT JOIN #Account A
ON C.ClientId = A.ClientId
AND C.AnnualIncome > 10000


DROP TABLE #Client
DROP TABLE #Account


This query returns incorrect result. In case of INNER JOINs putting the filter condition on the WHERE clause or ON Clause has the same effect. But in case of OUTER JOINS, it’s a different story. You specify the JOIN Criteria on the ON clause and filter criteria on the WHERE clause. The condition ON the join criteria doesn’t have an impact on the number of rows returned.

A Select statement works in the following way.

SELECT
JOIN
WHERE
GROUP BY
HAVING
DISTINCT
ORDER BY
TOP

(See this link for a detailed description by Joe CELKO)

8. Concatenation And NULL

If you are concatenating a field which has a NULL value, the result will be NULL. To avoid this either use ISNULL() function or SET CONCAT_NULL_YIELDS_NULL OFF

Eg.


CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)


-- Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', NULL)

SELECT * FROM #Client

SELECT ClientName + ' - '+ CONVERT(VARCHAR,AnnualIncome) AS [ClientInfo]
FROM #Client

DROP TABLE #Client

9. NULL valued Parameters

If the parameter you are passing has a NULL value and even if there is matching records for NULL, the query wont return any rows back.



CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

CREATE TABLE #Account(AccountId int IDENTITY(1,1), ClientId int, AccountValue int)


--Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 30000)

--Populate the account table

INSERT INTO #Account(ClientId, AccountValue)
VALUES(1, 500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(2, 1500)
INSERT INTO #Account(ClientId, AccountValue)
VALUES(NULL, 2000)


DECLARE @intClientId int
SET @intClientId = NULL

SELECT * FROM #Account
WHERE ClientId = @intClientId

DROP TABLE #Client
DROP TABLE #Account

10. Error Handling

consider the following example.

CREATE TABLE #Client(ClientID int IDENTITY(1,1), ClientName varchar(30), AnnualIncome int)

-- Populate the client table.
INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Prasanth', 10000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Binu', 20000)

INSERT INTO #Client(ClientName, AnnualIncome)
VALUES('Shino', 3000)

--SELECT * FROM #Client

DECLARE @intVar int
SET @intVar = 0
SELECT AnnualIncome/@intVar AS Something
FROM #Client

IF @@ERROR <> 0
SELECT @@ERROR
ELSE
SELECT @@ROWCOUNT

DROP TABLE #Client

In both the cases, the Error handling section will not work as expected, because both @@ERROR and @@ROWCOUNT return the status of the last statement executed.

Comments:
it is really good thanks.
 
Yeah there are so many resources are available that have programming solutions. We just need to visit each site.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?