Hello SQL

A list of SQL best practices

James Wang — Tue, 04 Oct 2011 07:38:57 GMT

Here are some SQL programming guidelines and best practices we collected, keeping quality, performance and maintainability in mind. This list is not complete at this moment, and will be constantly updated.

Do not use SELECT * in your queries.
Always use table aliases when your SQL statement involves more than one source.
Use the more readable ANSI-Standard Join clauses instead of the old style joins.
Do not use column numbers in the ORDER BY clause.
Always use a column list in your INSERT statements.
Don’t ever use double quotes in your T-SQL code.
Do not prefix your stored procedure names with “sp_”.
Always use a SQL formatter to format your sql like Instant SQL Formatter(Free and Online)

Do not use SELECT * in your queries, write out the full syntax.

Always write the required column names after the SELECT statement, like:

SELECT CustomerID, CustomerFirstName, City from Emp;

This technique results in reduced disk I/O and better performance.

Always use table aliases when your SQL statement involves more than one source

If more than one table is involved in a from clause, each column name must be qualified using either the complete table name or an alias. The alias is preferred. It is more human readable to use aliases instead of writing columns with no table information.

Use the more readable ANSI-Standard Join clauses instead of the old style joins

With ANSI joins, the WHERE clause is used only for filtering data. Where as with older style joins, the WHERE clause handles both the join condition and filtering data. Furthermore ANSI join syntax supports the full outer join. The first of the following two queries shows the old style join, while the second one shows the new ANSI join syntax:

-- old style join
SELECT a.Au_id,
       t.Title
FROM   TITLES t,
       AUTHORS a,
       TITLEAUTHOR ta
WHERE  a.Au_id = ta.Au_id
       AND ta.Title_id = t.Title_id
       AND t.Title LIKE ‘%Computer%’

--ANSI join syntax
SELECT a.Au_id,
       t.Title
FROM   AUTHORS a
       INNER JOIN TITLEAUTHOR ta
         ON a.Au_id = ta.Au_id
       INNER JOIN TITLES t
         ON ta.Title_id = t.Title_id
WHERE  t.Title LIKE ‘%Computer%’

Do not use column numbers in the ORDER BY clause

Always use column names in an order by clause. Avoid positional references. Consider the following example in which the second query is more readable than the first one:

SELECT OrderID, OrderDate
FROM Orders
ORDER BY 2

SELECT OrderID, OrderDate
FROM Orders
ORDER BY OrderDate

Always use a column list in your INSERT statements

Always specify the target columns when executing an insert command. This helps in avoiding problems when the table structure changes (like adding or dropping a column). Consider the following table:

CREATE TABLE EUROPEANCOUNTRIES
  (
     Countryid   INT PRIMARY KEY,
     Countryname VARCHAR(25)
  )

Here’s an INSERT statement without a column list , that works perfectly:

INSERT INTO EuropeanCountries
VALUES (1, ‘Ireland’)

Now, let’s add a new column to this table:

ALTER TABLE EuropeanCountries
ADD EuroSupport bit

Now run the above INSERT statement. You get the following error from SQL Server: Server: Msg 213, Level 16, State 4, Line 1 Insert Error: Column name or number of supplied values does not match table definition. This problem can be avoided by writing an INSERT statement with a column list as shown below:

INSERT INTO EuropeanCountries
(CountryID, CountryName)
VALUES (1, ‘England’)

Don't ever use double quotes in your T-SQL code

Use single quotes for string constants. If it's necessary to qualify an object name, use (non-ANSI SQL standard) brackets around the name, like table name: ORDER DETAILS in this SQL.

SELECT od.[Discount],
       od.[Quantity],
       od.[Unitprice]
FROM   [northwind].[dbo].[ORDER DETAILS] AS od

Do not prefix your stored procedure names with “sp_”

The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. So you can really save time in locating the stored procedure by avoiding the “sp_” prefix.

Always use a SQL formatter to format your sql like Instant SQL Formatter(Free and Online)

The formatting of SQL code may not seem that important,but consistent formatting makes it easier for others to scan and understand your code. SQL statements have a structure, and having that structure be visually evident makes it much easier to locate and verify various parts of the statements. Uniform formatting also makes it much easier to add sections to and remove them from complex T-SQL statements for debugging purposes. Instant SQL Formatter is a free online SQL tidy tool that makes your SQL script readable instantly.

Datatypes translation between Oracle and SQL Server part 2: number

James Wang — Wed, 04 Aug 2010 14:36:11 GMT

This is an article in the series that we talking about translate SQL query among different databases.

This article focus on the translation of number datatype between oracle and SQL Server database.

The main difference of number datatype between oracle and SQL Server is float. ANSI SQL requires float precision to be specified in terms of binary bits. But the number of binary bits specified in float definition of Oracle and SQL Server doesn’t have the same meanings.

In SQL Server, syntax of float datatype is float[(n)], Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53. SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53. The SQL Server float[(n)] data type complies with the ISO standard for all values of n from 1 through 53. The synonym for double precision is float(53).

Oracle uses its NUMBER datatype internally to represent float. precision of Oracle FLOAT range from 1 to 126 in binary bits, The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision which is used by NUMBER datatype.

From this article “Internal representation of the NUMBER datatype”, you may agree with me that the number of binary bits specified in float definition of Oracle and SQL Server doesn’t have the same meanings. I don’t know how to map n in SQL server (1<=n<=53) to size in Oracle(1<=size<=126). If anybody know this, please kindly send me an email. But this article tells me how to map float from Oracle to SQL Server,

float -> float
float(1-53) -> float(1-53)
float(54-126) -> float

and this article tells me how to map float from SQL Server to Oracle.

float -> float(49)
real, float(24) -> float(23)

Oracle allows numbers to be defined with a scale greater than the precision, such as NUMBER(4,5), but SQL Server requires the precision to be equal to or greater than the scale. To ensure there is no data truncation, if the scale is greater than the precision at the Oracle database, the precision is set equal to the scale when the data type is mapped: NUMBER(4,5) would be mapped as NUMERIC(5,5).

Number datatype convert from Oracle to SQL Server
Oracle(source)	SQL Server(target)
number	float
number([1-38])	numeric([1-38])
number([0-38],[1-38])	numeric([0-38],[1-38])
float	float
float([1-53])	float([1-53])
float([54-126])	float
binary_float	float
binary_double	float(53)
int	numeric(38)
real	float

Number datatype convert from SQL Server to Oracle
SQL Server(source)	Oracle(target)
bigint	NUMBER(19)
int	NUMBER(10)
smallint	NUMBER(5)
tinyint	NUMBER(3)
bit	NUMBER(3)
numeric	NUMBER(p[,s])
money	number(19,4)
smallmoney	NUMBER(10,4)
float	FLOAT(49)
real	FLOAT(23)

Reference:
1. Oracle datatypes
2. SQL Server datatypes

Datatypes translation between Oracle and SQL Server part 1: character, binary strings

James Wang — Thu, 29 Jul 2010 07:07:18 GMT

Datatypes translation is one of the most important things you need to consider when migrate your application from one database to the other. This is an article in the series that we talking about translate SQL query among different databases.

This article will focused on conversion of those datatypes: character, binary strings between Oracle and SQL Server. We will talk about conversion of other datatypes such as nunber, float, date and etc in other articles later.

When you convert character datatypes from Oracle to SQL Server or vice verse, you not only need to find corresponding datatype name but also need to find out how string was stored in database. Is this string stored in character or byte? and you must be aware of the maximum length of datatype in source and target databases.

In SQL Server, char [ ( n ) ] is fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. You can easily find corresponding datatype name “char” in Oracle, but char in oracle with a maximum length of 2000 bytes. So you can’t migrate char(2048) in your SQL Server script to Oracle without any changes, you should use clob instead if n > 2000.

In Oracle database, char[(size)] can be also be used in fixed-length character data of length size in characters. When you use char qualifier, for example char(10 char), then you supply the column length in characters. A character is technically a code point of the database character set. Its size can range from 1 byte to 4 bytes, depending on the database character set. When translate this datatype to SQL Server, target datatype can be char(10) or char(40) depends on the database character set in source database(Oracle).

Detailed information about Oracle datatypes and SQL Server datatypes: including datatype name, description and what’s the corresponding datatype in other databases.

Below are summary tables show how Character and binary string datatypes translated from Oracle to SQL Server and vice verse.

Oracle(source)	SQL Server(target)
CHAR [(size [BYTE \| CHAR])]	char[(size)]
VARCHAR2(size [BYTE \| CHAR])	varchar(size)
NCHAR[(size)]	nchar[(size)]
NVARCHAR2(size)	nvarchar(size)
long	varchar(max)
long raw	varbinary(max)
raw(size)	varbinary(size)
blob	varbinary(max)
clob	varchar(max)
nclob	ntext
bfile	N/A

How Character and binary string datatypes translated from SQL Server to Oracle.

SQL Server(source)	Oracle(target)
char [ ( n ) ]	char[(n)], 1<=n<=2000; clob, n>2000
varchar [ ( n \| max ) ]	varchar2(n), 1<=n<=4000; clob (n>4000)
text	clob
nchar [ ( n ) ]	nchar[(n)], 1<=n<=2000; nclob(n>2000)
nvarchar [ ( n \| max ) ]	nvarchar2[(n)], 1<=n<=4000; nclob( n>4000 )
ntext	nclob
binary [ ( n ) ]	raw(1)(n was omitted); raw(n), 1<=n<=2000; blob(n>2000)
varbinary [ ( n \| max) ]	raw(1)(n was omitted); raw(n), 1<=n<=2000; blob(n>2000)
image	blob

Tidy sql posted on your blog

James Wang — Tue, 28 Jul 2009 17:31:36 GMT

As a sql developer, DBA. we often post sql code on our blog. Take this sql for example,

select pub_name, count(qty) as orders, sum(qty) as total 
from sales inner join titles on (sales.title_id=titles.title_id) 
right join publishers on (publishers.pub_id=titles.pub_id) 
group by pub_name

It's really difficult for others to read. and how about this one after beautify previous sql:

 SELECT   pub_name, 

         Count(qty) AS orders, 

         Sum(qty)   AS total 

FROM     sales 

         INNER JOIN titles 

           ON (sales.title_id = titles.title_id) 

         RIGHT JOIN publishers 

           ON (publishers.pub_id = titles.pub_id) 

GROUP BY pub_name



Yes, it's really good for your readers. 



Now, you can do this instantly with the help of a free online sql formatter:



http://www.dpriver.com/pp/sqlformat.htm



1. Paste original sql code into input sql text box, then select corresponding database type, and output type(sql(html:span) should be ok).



2. Click "format sql" button



3. Copy Html code from text box at the bottom of that page.



4. In your FCKeditor(If you're using SUBTEXT which is used by sqlteam weblogs), switch to source code mode by click "source code icon" in the up-left corner,

then paste html code, after that switch back to normal mode by click that button again.



5. You are done.



I think it should as easy as this to tidy sql in other blog systems like wordpress, community server. Anyway, I will check those blogs system, and let you know the result in next following days.