Database

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

<italic>Work hard and a bit of luck is the key to success.

SELECT columnlist
FROM tablename
GROUP BY columnlist
HAVING COUNT(*) = 1

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc 
	ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE='PRIMARY KEY'
AND tc.TABLE_NAME = 'MyTable'

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

<italic>Work hard and a bit of luck is the key to success.

Use proper indenting. It increases readability. (So does proper naming: See SqlNamingConventions)

Comments
When using comments in a SQL script or in StoredProcedures, you should use the line comment -- instead of the block comment /* and */. This makes it easier to refactor, experiment and debug code, since you are able to use block comments to temporarily disable parts of the script.

Descriptive header comments
When a procedure or script contains a descriptive header (as it always should). You should state not only information about parameters and returnvalues, but also which objects that are available outside of its scope it may modify. If you use temporary objects that are created outside of the script or procedure, you should state that also.

Indenting
This document suggests a set of indenting rules for a readable layout of complex queries.

Only these SQL keywords should be at a start of a line, at the same indenting level in the same query (except subqueries, which can be placed at their own indenting levels as a whole following the same rules.):

SELECT 
FROM 
WHERE 
GROUP BY 
HAVING
At the next indenting level you should naturally put the following items

- Column names in the select, if there are multiple columns in the select part. 
- JOINs. 
- Additional conditions in a WHERE clause starting with the words AND or OR. 
- Additional GROUP BY conditions. 
- Additional conditions in a HAVING clause if you put them on seperate lines.
- ON conditions of a JOIN could be placed on the next line, one more indentation than the JOIN.

Examples:

SELECT
	p.Name,
	k.Name
FROM Persons p
	LEFT JOIN Kids k
		ON p.PersonPK = k.ParentPersonFK
WHERE k.Age > 18
	AND k.Height < 175
<br><br>
SELECT
	Gender,
	COUNT(*)
FROM Persons
WHERE Gender = 'To be confirmed...'
GROUP BY Gender
HAVING COUNT(*) > 0

Example showing the indenting of a subquery:

SELECT Something
FROM Somewhere
WHERE Something IN
(
	SELECT
		x
	FROM
		AnotherTable
	WHERE Comment LIKE '%free sex%'
)
<hr>
ArjanEinbu

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums