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.
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:
<< Home
Yeah there are so many resources are available that have programming solutions. We just need to visit each site.
Post a Comment
<< Home