People still ask me for my coding standards complete list. Coding standards are a set of rules about how your team writes SQL. Most teams have a document nobody reads. The useful ones are short, explain why, and prevent bugs rather than argue about capital letters. Here is the list I actually give people.
Why Most Standards Documents Fail
They are too long. Forty pages of rules gets skimmed once and never opened again.
They are all taste and no consequence. A rule about where the comma goes has no cost when broken. A rule about SELECT star has a real one. Mix the two and people learn to ignore both.
So split them. Rules that prevent bugs go first, and they are not negotiable. Rules about layout go second, and one choice consistently applied beats the best choice argued about for a week.
The Rules That Prevent Real Bugs
Name your columns in INSERT. Without a column list your INSERT depends on column order, and the day somebody adds a column in the middle, it breaks quietly.
-- brittle
INSERT INTO dbo.Orders VALUES (1, 'Perth', 250.00);
-- safe
INSERT INTO dbo.Orders (id, city, amount) VALUES (1, 'Perth', 250.00);No SELECT star in anything that gets saved. Fine when you are poking about. In a view, a procedure or application code it drags columns nobody wanted and breaks when the table changes.
Always name the schema. Write dbo.Orders, not Orders. It avoids a lookup on every call and removes a whole class of surprise when two schemas hold the same name.
Every UPDATE and DELETE gets a WHERE, and gets tested as a SELECT first. Write the SELECT, look at the rows, then change the verb. This one habit has saved more Saturdays than any tool.
SET NOCOUNT ON at the top of every procedure. It stops a row count message after every statement, which some clients treat as a result set.
Match the data type in a WHERE clause. Compare an nvarchar parameter to a varchar column and SQL Server converts the column, not the parameter, and your index stops being used.
No NOLOCK by reflex. It does not read faster. It reads rows that are half written, or twice, or not at all.
Naming
The specific choice matters less than picking one and holding it. What I use:
Tables singular or plural, but the same everywhere. No prefixes on tables, so Orders not tblOrders, because you already know it is a table. No sp_ prefix on procedures ever, because SQL Server looks in master first for anything starting sp_ and you pay for that on every call.
-- find anything still carrying the sp_ prefix
SELECT SCHEMA_NAME(schema_id) AS sch, name, type_desc
FROM sys.objects
WHERE type IN ('P','FN','IF','TF') AND name LIKE 'sp[_]%'
ORDER BY name;Constraints and indexes get names, always. An unnamed constraint gets a generated name with random digits, and then it is different in every environment and your deployment scripts cannot find it.
-- unnamed, generates something like DF__Orders__amount__3B75D760
ALTER TABLE dbo.Orders ADD DEFAULT 0 FOR amount;
-- named, the same in every environment
ALTER TABLE dbo.Orders ADD CONSTRAINT DF_Orders_amount DEFAULT 0 FOR amount;Formatting
Keywords in one case, consistently. Uppercase SELECT reads well in a long procedure.
One column per line once you have more than three. Put the comma where your team already puts it and stop discussing it.
Indent the parts of a query so the shape is visible. Somebody reading your procedure at two in the morning should see where the joins end without counting brackets.
End every statement with a semicolon. Some newer syntax requires it, and the mixed style is worse than either.
Comments
A comment saying what the code does is noise. The code already says that.
A comment saying why is worth keeping forever. Why this hint is here. Why this filter excludes one customer. Why this runs in batches. That is the knowledge that leaves with the person who wrote it.
-- Batched on purpose. A single UPDATE here held locks long enough
-- to time out the order screen. Do not collapse this into one statement.Enforcing It Without Nagging
A rule nobody checks is a suggestion. Three things work.
Put the standard in the repository next to the code, not in a document store nobody opens. Review the first thing a new joiner writes, properly and kindly, because that is when habits are cheap to set. And check what you can automatically, so the machine raises the boring points and people discuss the interesting ones.
Keep the whole thing to two pages. If it does not fit on two pages, the parts nobody will remember are the parts to cut.
A coding standard is not a rulebook, it is the argument you only have to win once.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
48 Comments. Leave new
want to find highest salary from emp(eid,department,salary) without using any sql function.
Why do you want not to use anu sql function?
SELECT TOP 1 FROM EMP ORDER BY SALARY DESC
hi jp,
i’m new to dis site…as i m very much interested in learning sql database nd nw i no the basics of sql but not deeper..wat i hav to do to go get strong in sql…
pls advise…
pinaldave pls advise..
Hi Pinal
create Procedure [dbo].[sp_become_acc_reviewer_content]
(
@become_acc_reviewer text,
@action varchar(20)
)
as
begin
if(@action = ‘insert’)
begin
insert tbl_crm_details([become_ an_acc_reviewer])values(@become_acc_reviewer)
End
else
begin
update tbl_crm_details set [become_ an_acc_reviewer]=@become_acc_reviewer
end
end
when i save data in second time its save a new row
what i want to concatenate this
how can i do this
plz give me solution
thanks
What did you mean by concatenation?
hi,
I’m new to sql.I have a problem some one asked in one of my interview.he was asking like this”how to split one table data into two table result set but result should be side by side means
table:
id name sal
1 ravi 1000
2 kishore 2000
3 naveen 3000
4 suresh 4000
resulet:
id name sal : id name sal
1 ravi 1000 : 2 kishore 2000
3 kishore 3000 : 4 suresh 4000
note:result shoud be appears in single window
can any one help me.
Create Table Document
(
DocID Int,
PageID Int,
DocName Varchar(100)
)
Insert into Document values
(1,0,’document Test’),
(1,0,’document Test’),
(1,0,’document Test’),
(1,1,’document Test’),
(1,2,’document Test’),
(1,3,’document Test’),
(1,4,’document Test’),
(1,5,’document Test’),
(1,5,’document Test’),
(2,0,’document Test’),
(2,1,’document Test’),
(2,0,’document Test’),
(3,0,’document Test’),
(3,0,’document Test’)
Hai Sir,
I need a help…
I want to update the duplicate record with maximum of its pageID from the above table without use a loop.
After updation the DocID & PageID combination of record should be unique.
For eg.,
Output will be :
DocID PageID DocName
———————–
1 0 ‘document Test’
1 6 ‘document Test’
1 7 ‘document Test’
1 1 ‘document Test’
1 2 ‘document Test’
1 3 ‘document Test’
1 4 ‘document Test’
1 5 ‘document Test’
1 8 ‘document Test’
2 0 ‘document Test’
2 1 ‘document Test’
3 0 ‘document Test’
3 1 ‘document Test’
Thank you,
Nikhildas
Cochin
Dave Sir Can you gives us reference books for oracle OCA certification books
Hai Pinal sir,
Can we store a static value in sql server without table?
Hi,
In table one column passport field is there it allows only unique values at same time it allows more than two nulls ( i know unique only allow only one null value for example in college more than two students does not have passport at that time it allows null values at the same time who have passport that id must be unique. is it possible can anybody knws plz send snd answer.
can anyone help me with this please. . . thanks! !
iii. The report can be sorted by Vendor #, Purchase Order # or Item #
1. If sorted by Vendor #
a. Group Header: Vendor # (Company Name)
b. Details (in this order): PO #, Request Date, Warehouse, Item #, Unit of Measurement, Order Qty, Unit Cost, Discount Rate, Ext. Net Cost
2. If sorted by Purchase Order #
a. Details (in this order): PO #, Vendor #, Request Date, Warehouse, Item #, Unit of Measurement, Order Qty, Unit Cost, Discount Rate,
3. If sorted by Item #
a. Group Header: Item # (Description)
b. Details (in this order): Warehouse, PO #, Vendor #, Request Date, Order
how to use LOB data type in oracle database creation. and how to retrive those columns>
Hi
Should we use Singular or Plural Database Table Names??
Thanks
Vijayakumar P
Hi,
I am having a Question regarding to Database mail, the client requriment is that he must receive the mail in Excel format, without using SSIS packages,can you please give me sugguest me regarding may I get in script form and through GUI.
Thanks & Regards
Murali
Can you help me in this issue.
I need to have conditional operators inside the case for each case
Hello ,
I have 2 Applications say APP1 and APP2
APP1 database fetches the data , following is the structure of data:
dataabse name: APPDB1
ID Name Value
1 Hostname Lab_Client1
2 Hostname Lab_Client2
3 OS Windows 8
4 OS Windows 7
5 IP Address 10.0.1.2
6 IP Address 10.0.1.3
I want to import this data in APPDB2 in following Manner:
ID Hostname OS IP Address
1 Lab_Client1 Windwows 8 10.0.1.2
2 Lab_Client2 Windwows 7 10.0.1.3
The Data imported from APP1 with DB name APPDB1 is updated automatically when clients have some change in information.
Please help me with the script to create database say APPDB2 which will import all the data in Example 2 fashion and should update if any change is there in APPDB1
Great post.
nice i want to partcipiate in solving quires for users