SlideShare a Scribd company logo
DATABASE DEVELOPMENT AND
    CODING STANDARDS


    SQL & Database Guidelines
INDEX


  1.    NAMING CONVENTIONS
  2.    DECLARING VARIABLES
  3.    SELECT STATEMENTS
  4.    CURSORS
  5.    WILDCARD CHARACTERS
  6.    NOT EQUAL OPERATORS
  7.    DERIVED TABLES
  8.    SQL BATCHES
  9.    ANSI-STANDARD JOIN CLAUSES
  10.   STORED PROCEDURES NAMING CONVENTION
  11.   USING VIEWS
  12.   TEXT DATA TYPES
  13.   INSERT STATEMENTS
  14.   ACCESSING TABLES
  15.   STORED PROCEDURE RETURNING VALUES
  16.   OBJECT CASE
  17.   T-SQL VARIABLES
  18.   OFFLOAD TASKS
  19.   CHECK FOR RECORD EXISTENCE
  20.   OBJECT OWNER
  21.   UPSERT STATEMENTS
  22.   DATETIME COLUMNS
  23.   MEASURE QUERY PERFORMANCE
  24.   INDEXES
1 - Naming Conventions
All T-SQL Keywords must be upper case.
All declared variable names must be Camel Case while all stored procedure names, function names, trigger
names, Table names and Columns names in query must be Pascal Case.
All view names must start with the letter ‘v’ followed by the name of the view in Pascal Case
Example:

SELECT * FROM Employee WHERE ID = 2
DECLARE @minSalary int
CREATE PROCEDURE GetEmployees

If you are creating a table belonging to a specific module, make sure to append a 3 character prefix before the
name of each table, example:

LABResult
LABSpecimen
LABOrder
RADImage
RADResult

Note that all table names must be singular.
When creating columns, make sure to append a ‘_F’ to the end of each column you intend to use as a flag. If
there are exactly two statuses for the flag, use ‘bit’ data type, if there are 3 or more statuses, use ‘char(1)’ data
type. If the column is foreign key reference, append ‘_FK’ to the end of the column name. This makes it easy to
distinguish flag and foreign key columns:

CREATE TABLE Employee(
ID INT IDENTITY NOT NULL PRIMARY KEY,
FirstName varchar(max),
Sex_F BIT,
Person_FK int,
Status_F CHAR(1)
)

2 - Declaring Variables
Always declare variables at the top of your stored procedure and set their values directly after declaration. If your
database runs on SQL Server 2008, you can declare and set the variable on the same line. Take a look at the
following statement under SQL 2000/SQL 2005 and the second statement under SQL 2008. Standard
programming language semantics are added in SQL 2008 for short assignment of values:

DECLARE @i int
SET @i = 1
SET @i = @i + 1
-------------------
DECLARE @i int = 1
SET @i +=1
3 - Select Statements
Do not use SELECT * in your queries. Always write the required column names after the SELECT statement. This
technique results in reduced disk I/O and better performance:

SELECT CustomerID, CustomerFirstName, City From Customer

If you need to write a SELECT statement to retrieve data from a single table, don’t SELECT the data from a view
that points to multiple tables. Instead, SELECT the data from the table directly, or from a view that only contains
the table you are interested in. If you SELECT the data from the multi-table view, the query will experience
unnecessary overhead, and performance will be hindered.

4 - Cursors
Try to avoid server side cursors as much as possible. Always stick to a ‘set-based approach’ instead of a
‘procedural approach’ for accessing and manipulating data. Cursors can often be avoided by using SELECT
statements instead.
If a cursor is unavoidable, use a WHILE loop instead. A WHILE loop is always faster than a cursor. But for a WHILE
loop to replace a cursor you need a column (primary key or unique key) to identify each row uniquely.

5 - Wildcard Characters
Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that result
in an index scan, which defeats the purpose of an index. The following statement results in an index scan, while
the second statement results in an index seek:

SELECT EmployeeID FROM Locations WHERE FirstName LIKE '%li'
SELECT EmployeeID FROM Locations WHERE FirsName LIKE 'a%i'

6 - Not Equal Operators
Avoid searching using not equals operators (<> and NOT) as they result in table and index scans.

7 - Derived Tables
Use ‘Derived tables’ wherever possible, as they perform better. Consider the following query to find the second
highest salary from the Employees table:

SELECT MIN(Salary) FROM Employees WHERE EmpID IN (SELECT TOP 2 EmpID FROM Employees ORDER BY
Salary Desc)

The same query can be re-written using a derived table, as shown below, and it performs twice as fast as the
above query:

SELECT MIN(Salary) FROM (SELECT TOP 2 Salary FROM Employees ORDER BY Salary DESC)

This is just an example, and your results might differ in different scenarios depending on the database design,
indexes, volume of data, etc. So, test all the possible ways a query could be written and go with the most efficient
one.

8 - SQL Batches
Use SET NOCOUNT ON at the beginning of your SQL batches, stored procedures and triggers in production
environments.
This suppresses messages like ‘(1 row(s) affected)’ after executing INSERT, UPDATE, DELETE and SELECT
statements. This improves the performance of stored procedures by reducing network traffic.

9 - ANSI-Standard Join Clauses
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. Whereas with older style joins, the WHERE clause handles both the join
condition and filtering data. The first of the following two queries shows the old style join, while the second one
show the new ANSI join syntax:

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%'
----------------------------------------------
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%'

10 - Stored Procedures Naming Convention
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.

11 - Using Views
Views are generally used to show specific data to specific users based on their interest. Views are also used to
restrict access to the base tables by granting permission only on views. Yet another significant use of views is
that they simplify your queries.
Incorporate your frequently required, complicated joins and calculations into a view so that you don’t have to
repeat those joins/calculations in all your queries. Instead, just select from the view.

12 - Text Data Types
Try not to use TEXT or NTEXT data types for storing large textual data.
The TEXT data type has some inherent problems associated with it and will be removed from future version of
Microsoft SQL Server.
For example, you cannot directly write or update text data using the INSERT or UPDATE
Statements. Instead, you have to use special statements like READTEXT, WRITETEXT and UPDATETEXT.
There are also a lot of bugs associated with replicating tables containing text columns.
So, if you don’t have to store more than 8KB of text, use CHAR(8000) or VARCHAR(8000) data types instead.
In SQL 2005 and 2008, you can use VARCHAR(max) for storing unlimited amount of textual data.
13 - Insert Statements
Always use a column list in your INSERT statements. This helps in avoiding problems when the table structure
changes (like adding or dropping a column).

14 - Accessing Tables
Always access tables in the same order in all your stored procedures and triggers consistently. This helps in
avoiding deadlocks. Other things to keep in mind to avoid deadlocks are:
1. Keep your transactions as short as possible. Touch as few data as possible during a transaction.
2. Never, ever wait for user input in the middle of a transaction.
3. Do not use higher level locking hints or restrictive isolation levels unless they are absolutely needed.
4. Make your front-end applications deadlock-intelligent, that is, these applications should be able to resubmit
the transaction incase the previous transaction fails with error 1205.
5. In your applications, process all the results returned by SQL Server immediately so that the locks on the
processed rows are released, hence no blocking.

15 - Stored Procedure Returning Values
Make sure your stored procedures always return a value indicating their status. Standardize on the return values
of stored procedures for success and failures.
The RETURN statement is meant for returning the execution status only, but not data. If you need to return data,
use OUTPUT parameters.
If your stored procedure always returns a single row result set, consider returning the result set using OUTPUT
parameters instead of a SELECT statement, as ADO handles output parameters faster than result sets returned by
SELECT statements.

16 - Object Case
Always be consistent with the usage of case in your code. On a case insensitive server, your code might work
fine, but it will fail on a case sensitive SQL Server if your code is not consistent in case.
For example, if you create a table in SQL Server or a database that has a case-sensitive or binary sort order; all
references to the table must use the same case that was specified in the CREATE TABLE statement.
If you name the table as ‘MyTable’ in the CREATE TABLE statement and use ‘mytable’ in the SELECT statement,
you get an ‘object not found’ error.

17 - T-SQL Variables
Though T-SQL has no concept of constants (like the ones in the C language), variables can serve the same
purpose. Using variables instead of constant values within your queries improves readability and maintainability
of your code. Consider the following example:

SELECT OrderID, OrderDate FROM Orders WHERE OrderStatus IN (5,6)

The same query can be re-written in a mode readable form as shown below:

DECLARE @ORDER_DELIVERED, @ORDER_PENDING
SELECT @ORDER_DELIVERED = 5, @ORDER_PENDING = 6
SELECT OrderID, OrderDate FROM Orders
WHERE OrderStatus IN (@ORDER_DELIVERED, @ORDER_PENDING)

18 - Offload tasks
Offload tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc.,
to the front-end applications if these operations are going to consume more CPU cycles on the database server.
Also try to do basic validations in the front-end itself during data entry. This saves unnecessary network
roundtrips.

19 - Check for record Existence
If you need to verify the existence of a record in a table, don’t use SELECT COUNT (*) in your Transact-SQL code
to identify it, which is very inefficient and wastes server resources. Instead, use the Transact-SQL IF EXITS to
determine if the record in question exits, which is much more efficient. For example:
Here’s how you might use COUNT(*):

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')

Here’s a faster way, using IF EXISTS:

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

The reason IF EXISTS is faster than COUNT(*) is because the query can end immediately when the text is proven
true, while COUNT(*) must count go through every record, whether there is only one, or thousands, before it can
be found to be true.

20 - Object Owner
For best performance, all objects that are called from within the same stored procedure should all be owned by
the same owner, preferably dbo. If they are not, then SQL Server must perform name resolution on the objects if
the object names are the same but the owners are different. When this happens, SQL Server cannot use a stored
procedure “in-memory plan” over, instead, it must re-compile the stored procedure, which hinders performance.
There are a couple of reasons, one of which relates to performance. First, using fully qualified names helps to
eliminate any potential confusion about which stored procedure you want to run, helping to prevent bugs and
other potential problems. But more importantly, doing so allows SQL Server to access the stored procedures
execution plan more directly, and in turn, speeding up the performance of the stored procedure. Yes, the
performance boost is very small, but if your server is running tens of thousands or more stored procedures every
hour, these little time savings can add up.

21 - Upsert Statements
SQL Server 2008 introduces Upsert statements which combine insert, update, and delete statements in one
‘Merge’ statement.
Always use the Merge statement to synchronize two tables by inserting, updating, or deleting rows in one table
based on differences found in the other table

MERGE table1 AS target
USING (
SELECT
ID,Name
FROM table2
) AS source (ID,Name)
ON
(
target.Table2ID = source.ID
)
WHEN NOT MATCHED AND target.Name IS NULL THEN
DELETE
WHEN NOT MATCHED THEN
INSERT (name, Table2ID)
VALUES(name + ' not matched', source.ID)
WHEN MATCHED THEN
UPDATE
SET target.name = source.name + ' matched'
OUTPUT $action,inserted.id,deleted.id;

22 - DateTime Columns
Always use ‘datetime2’ data type in SQL 2008 instead of the classic ‘datetime’. Datetime2 offers optimized data
storage by saving 1 additional byte from the classic datetime. It has a larger date range, a larger default
fractional precision, and optional user-specified precision.
If your column is supposed to store the date only portion, use the ‘date’ date type while if you want to store the
time portion, use the ‘time’ data type. Below is a list of examples of these new data types look like:

time 12:35:29. 1234567
date 2007-05-08
smalldatetime 2007-05-08 12:35:00
datetime 2007-05-08 12:35:29.123
datetime2 2007-05-08 12:35:29. 1234567
datetimeoffset 2007-05-08 12:35:29.1234567 +12:15

23 - Measure Query Performance
Always use statistics time feature to measure your important query and stored procedure’s performance. Use
statistics time to optimize your queries Take a look at this example:

SET STATISTICS TIME ON
EXEC GetMedicalProcedures 1,10
SET STATISTICS TIME OFF

The below information will be displayed in the Messages tab:
SQL Server parse and compile time:
CPU time = 6 ms, elapsed time = 6 ms.
SQL Server Execution Times:
CPU time = 24 ms, elapsed time = 768 ms.
(10 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 125 ms.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 131 ms.
This provides a good estimation of how long the query took to be executed, showing the CPU time (processing
time) and elapsed time (CPU + I/O).

24 - Indexes
Create indexes on tables that have high querying pressure using select statements. Be careful not to create an
index on tables that are subject to real-time changes using CRUD operations.
An index speeds up a select clause if the indexed column is included in the query, especially if it is in the WHERE
clause. However, the same index slows down an insert statement whether or not the indexed column is included
in the query. This downside occurs because indexes readjust and update statistics every time the table structure
is changed. So use indexes wisely for optimizing tables having high retrieval rate and low change rate.
Ad

More Related Content

What's hot (20)

Sql operators & functions 3
Sql operators & functions 3Sql operators & functions 3
Sql operators & functions 3
Dr. C.V. Suresh Babu
 
C string
C stringC string
C string
University of Potsdam
 
Stored procedure
Stored procedureStored procedure
Stored procedure
baabtra.com - No. 1 supplier of quality freshers
 
SQL - DML and DDL Commands
SQL - DML and DDL CommandsSQL - DML and DDL Commands
SQL - DML and DDL Commands
Shrija Madhu
 
SQL : introduction
SQL : introductionSQL : introduction
SQL : introduction
Shakila Mahjabin
 
SQL commands
SQL commandsSQL commands
SQL commands
GirdharRatne
 
Java Arrays
Java ArraysJava Arrays
Java Arrays
OXUS 20
 
Oraclesql
OraclesqlOraclesql
Oraclesql
Priya Goyal
 
Sql joins
Sql joinsSql joins
Sql joins
Gaurav Dhanwant
 
Sql and Sql commands
Sql and Sql commandsSql and Sql commands
Sql and Sql commands
Knowledge Center Computer
 
Basic Sql Handouts
Basic Sql HandoutsBasic Sql Handouts
Basic Sql Handouts
jhe04
 
SQL
SQLSQL
SQL
Vineeta Garg
 
Coding standards for java
Coding standards for javaCoding standards for java
Coding standards for java
maheshm1206
 
Standard Template Library
Standard Template LibraryStandard Template Library
Standard Template Library
Nilesh Dalvi
 
MYSQL
MYSQLMYSQL
MYSQL
Ankush Jain
 
Oracle: Procedures
Oracle: ProceduresOracle: Procedures
Oracle: Procedures
DataminingTools Inc
 
Array in c
Array in cArray in c
Array in c
Ravi Gelani
 
R Basics
R BasicsR Basics
R Basics
Dr.E.N.Sathishkumar
 
Database Management - Lecture 2 - SQL select, insert, update and delete
Database Management - Lecture 2 - SQL select, insert, update and deleteDatabase Management - Lecture 2 - SQL select, insert, update and delete
Database Management - Lecture 2 - SQL select, insert, update and delete
Al-Mamun Sarkar
 
sql function(ppt)
sql function(ppt)sql function(ppt)
sql function(ppt)
Ankit Dubey
 

Similar to Database development coding standards (20)

Oracle notes
Oracle notesOracle notes
Oracle notes
Prashant Dadmode
 
MYSQL
MYSQLMYSQL
MYSQL
ARJUN
 
Myth busters - performance tuning 101 2007
Myth busters - performance tuning 101 2007Myth busters - performance tuning 101 2007
Myth busters - performance tuning 101 2007
paulguerin
 
Hira
HiraHira
Hira
hira elahi
 
Lab1 select statement
Lab1 select statementLab1 select statement
Lab1 select statement
Balqees Al.Mubarak
 
My sql.ppt
My sql.pptMy sql.ppt
My sql.ppt
MAGNA COLLEGE OF ENGINEERING
 
Mysqlppt
MysqlpptMysqlppt
Mysqlppt
poornima sugumaran
 
Mysqlppt
MysqlpptMysqlppt
Mysqlppt
poornima sugumaran
 
Ms sql server ii
Ms sql server  iiMs sql server  ii
Ms sql server ii
Iblesoft
 
Mysqlppt
MysqlpptMysqlppt
Mysqlppt
Reka
 
SQL Database Performance Tuning for Developers
SQL Database Performance Tuning for DevelopersSQL Database Performance Tuning for Developers
SQL Database Performance Tuning for Developers
BRIJESH KUMAR
 
Stored procedure tuning and optimization t sql
Stored procedure tuning and optimization t sqlStored procedure tuning and optimization t sql
Stored procedure tuning and optimization t sql
nishantdavid9
 
My sql with querys
My sql with querysMy sql with querys
My sql with querys
NIRMAL FELIX
 
Sql Server 2008 New Programmability Features
Sql Server 2008 New Programmability FeaturesSql Server 2008 New Programmability Features
Sql Server 2008 New Programmability Features
sqlserver.co.il
 
Advanced MySQL Query Optimizations
Advanced MySQL Query OptimizationsAdvanced MySQL Query Optimizations
Advanced MySQL Query Optimizations
Dave Stokes
 
SQL.pptx for the begineers and good know
SQL.pptx for the begineers and good knowSQL.pptx for the begineers and good know
SQL.pptx for the begineers and good know
PavithSingh
 
HPD SQL Training - Beginner - 20220916.pptx
HPD SQL Training - Beginner - 20220916.pptxHPD SQL Training - Beginner - 20220916.pptx
HPD SQL Training - Beginner - 20220916.pptx
PatriceRochon1
 
Structure Query Language Advance Training
Structure Query Language Advance TrainingStructure Query Language Advance Training
Structure Query Language Advance Training
parisaxena1418
 
Creating database using sql commands
Creating database using sql commandsCreating database using sql commands
Creating database using sql commands
Belle Wx
 
Database COMPLETE
Database COMPLETEDatabase COMPLETE
Database COMPLETE
Abrar ali
 
MYSQL
MYSQLMYSQL
MYSQL
ARJUN
 
Myth busters - performance tuning 101 2007
Myth busters - performance tuning 101 2007Myth busters - performance tuning 101 2007
Myth busters - performance tuning 101 2007
paulguerin
 
Ms sql server ii
Ms sql server  iiMs sql server  ii
Ms sql server ii
Iblesoft
 
Mysqlppt
MysqlpptMysqlppt
Mysqlppt
Reka
 
SQL Database Performance Tuning for Developers
SQL Database Performance Tuning for DevelopersSQL Database Performance Tuning for Developers
SQL Database Performance Tuning for Developers
BRIJESH KUMAR
 
Stored procedure tuning and optimization t sql
Stored procedure tuning and optimization t sqlStored procedure tuning and optimization t sql
Stored procedure tuning and optimization t sql
nishantdavid9
 
My sql with querys
My sql with querysMy sql with querys
My sql with querys
NIRMAL FELIX
 
Sql Server 2008 New Programmability Features
Sql Server 2008 New Programmability FeaturesSql Server 2008 New Programmability Features
Sql Server 2008 New Programmability Features
sqlserver.co.il
 
Advanced MySQL Query Optimizations
Advanced MySQL Query OptimizationsAdvanced MySQL Query Optimizations
Advanced MySQL Query Optimizations
Dave Stokes
 
SQL.pptx for the begineers and good know
SQL.pptx for the begineers and good knowSQL.pptx for the begineers and good know
SQL.pptx for the begineers and good know
PavithSingh
 
HPD SQL Training - Beginner - 20220916.pptx
HPD SQL Training - Beginner - 20220916.pptxHPD SQL Training - Beginner - 20220916.pptx
HPD SQL Training - Beginner - 20220916.pptx
PatriceRochon1
 
Structure Query Language Advance Training
Structure Query Language Advance TrainingStructure Query Language Advance Training
Structure Query Language Advance Training
parisaxena1418
 
Creating database using sql commands
Creating database using sql commandsCreating database using sql commands
Creating database using sql commands
Belle Wx
 
Database COMPLETE
Database COMPLETEDatabase COMPLETE
Database COMPLETE
Abrar ali
 
Ad

Recently uploaded (20)

AI Agents at Work: UiPath, Maestro & the Future of Documents
AI Agents at Work: UiPath, Maestro & the Future of DocumentsAI Agents at Work: UiPath, Maestro & the Future of Documents
AI Agents at Work: UiPath, Maestro & the Future of Documents
UiPathCommunity
 
How to Install & Activate ListGrabber - eGrabber
How to Install & Activate ListGrabber - eGrabberHow to Install & Activate ListGrabber - eGrabber
How to Install & Activate ListGrabber - eGrabber
eGrabber
 
Jignesh Shah - The Innovator and Czar of Exchanges
Jignesh Shah - The Innovator and Czar of ExchangesJignesh Shah - The Innovator and Czar of Exchanges
Jignesh Shah - The Innovator and Czar of Exchanges
Jignesh Shah Innovator
 
Agentic Automation - Delhi UiPath Community Meetup
Agentic Automation - Delhi UiPath Community MeetupAgentic Automation - Delhi UiPath Community Meetup
Agentic Automation - Delhi UiPath Community Meetup
Manoj Batra (1600 + Connections)
 
machines-for-woodworking-shops-en-compressed.pdf
machines-for-woodworking-shops-en-compressed.pdfmachines-for-woodworking-shops-en-compressed.pdf
machines-for-woodworking-shops-en-compressed.pdf
AmirStern2
 
Mastering Testing in the Modern F&B Landscape
Mastering Testing in the Modern F&B LandscapeMastering Testing in the Modern F&B Landscape
Mastering Testing in the Modern F&B Landscape
marketing943205
 
Slack like a pro: strategies for 10x engineering teams
Slack like a pro: strategies for 10x engineering teamsSlack like a pro: strategies for 10x engineering teams
Slack like a pro: strategies for 10x engineering teams
Nacho Cougil
 
Everything You Need to Know About Agentforce? (Put AI Agents to Work)
Everything You Need to Know About Agentforce? (Put AI Agents to Work)Everything You Need to Know About Agentforce? (Put AI Agents to Work)
Everything You Need to Know About Agentforce? (Put AI Agents to Work)
Cyntexa
 
UiPath Agentic Automation: Community Developer Opportunities
UiPath Agentic Automation: Community Developer OpportunitiesUiPath Agentic Automation: Community Developer Opportunities
UiPath Agentic Automation: Community Developer Opportunities
DianaGray10
 
Config 2025 presentation recap covering both days
Config 2025 presentation recap covering both daysConfig 2025 presentation recap covering both days
Config 2025 presentation recap covering both days
TrishAntoni1
 
RTP Over QUIC: An Interesting Opportunity Or Wasted Time?
RTP Over QUIC: An Interesting Opportunity Or Wasted Time?RTP Over QUIC: An Interesting Opportunity Or Wasted Time?
RTP Over QUIC: An Interesting Opportunity Or Wasted Time?
Lorenzo Miniero
 
Kit-Works Team Study_아직도 Dockefile.pdf_김성호
Kit-Works Team Study_아직도 Dockefile.pdf_김성호Kit-Works Team Study_아직도 Dockefile.pdf_김성호
Kit-Works Team Study_아직도 Dockefile.pdf_김성호
Wonjun Hwang
 
Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptxReimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
John Moore
 
AsyncAPI v3 : Streamlining Event-Driven API Design
AsyncAPI v3 : Streamlining Event-Driven API DesignAsyncAPI v3 : Streamlining Event-Driven API Design
AsyncAPI v3 : Streamlining Event-Driven API Design
leonid54
 
Q1 2025 Dropbox Earnings and Investor Presentation
Q1 2025 Dropbox Earnings and Investor PresentationQ1 2025 Dropbox Earnings and Investor Presentation
Q1 2025 Dropbox Earnings and Investor Presentation
Dropbox
 
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
James Anderson
 
fennec fox optimization algorithm for optimal solution
fennec fox optimization algorithm for optimal solutionfennec fox optimization algorithm for optimal solution
fennec fox optimization algorithm for optimal solution
shallal2
 
Build With AI - In Person Session Slides.pdf
Build With AI - In Person Session Slides.pdfBuild With AI - In Person Session Slides.pdf
Build With AI - In Person Session Slides.pdf
Google Developer Group - Harare
 
AI 3-in-1: Agents, RAG, and Local Models - Brent Laster
AI 3-in-1: Agents, RAG, and Local Models - Brent LasterAI 3-in-1: Agents, RAG, and Local Models - Brent Laster
AI 3-in-1: Agents, RAG, and Local Models - Brent Laster
All Things Open
 
On-Device or Remote? On the Energy Efficiency of Fetching LLM-Generated Conte...
On-Device or Remote? On the Energy Efficiency of Fetching LLM-Generated Conte...On-Device or Remote? On the Energy Efficiency of Fetching LLM-Generated Conte...
On-Device or Remote? On the Energy Efficiency of Fetching LLM-Generated Conte...
Ivano Malavolta
 
AI Agents at Work: UiPath, Maestro & the Future of Documents
AI Agents at Work: UiPath, Maestro & the Future of DocumentsAI Agents at Work: UiPath, Maestro & the Future of Documents
AI Agents at Work: UiPath, Maestro & the Future of Documents
UiPathCommunity
 
How to Install & Activate ListGrabber - eGrabber
How to Install & Activate ListGrabber - eGrabberHow to Install & Activate ListGrabber - eGrabber
How to Install & Activate ListGrabber - eGrabber
eGrabber
 
Jignesh Shah - The Innovator and Czar of Exchanges
Jignesh Shah - The Innovator and Czar of ExchangesJignesh Shah - The Innovator and Czar of Exchanges
Jignesh Shah - The Innovator and Czar of Exchanges
Jignesh Shah Innovator
 
machines-for-woodworking-shops-en-compressed.pdf
machines-for-woodworking-shops-en-compressed.pdfmachines-for-woodworking-shops-en-compressed.pdf
machines-for-woodworking-shops-en-compressed.pdf
AmirStern2
 
Mastering Testing in the Modern F&B Landscape
Mastering Testing in the Modern F&B LandscapeMastering Testing in the Modern F&B Landscape
Mastering Testing in the Modern F&B Landscape
marketing943205
 
Slack like a pro: strategies for 10x engineering teams
Slack like a pro: strategies for 10x engineering teamsSlack like a pro: strategies for 10x engineering teams
Slack like a pro: strategies for 10x engineering teams
Nacho Cougil
 
Everything You Need to Know About Agentforce? (Put AI Agents to Work)
Everything You Need to Know About Agentforce? (Put AI Agents to Work)Everything You Need to Know About Agentforce? (Put AI Agents to Work)
Everything You Need to Know About Agentforce? (Put AI Agents to Work)
Cyntexa
 
UiPath Agentic Automation: Community Developer Opportunities
UiPath Agentic Automation: Community Developer OpportunitiesUiPath Agentic Automation: Community Developer Opportunities
UiPath Agentic Automation: Community Developer Opportunities
DianaGray10
 
Config 2025 presentation recap covering both days
Config 2025 presentation recap covering both daysConfig 2025 presentation recap covering both days
Config 2025 presentation recap covering both days
TrishAntoni1
 
RTP Over QUIC: An Interesting Opportunity Or Wasted Time?
RTP Over QUIC: An Interesting Opportunity Or Wasted Time?RTP Over QUIC: An Interesting Opportunity Or Wasted Time?
RTP Over QUIC: An Interesting Opportunity Or Wasted Time?
Lorenzo Miniero
 
Kit-Works Team Study_아직도 Dockefile.pdf_김성호
Kit-Works Team Study_아직도 Dockefile.pdf_김성호Kit-Works Team Study_아직도 Dockefile.pdf_김성호
Kit-Works Team Study_아직도 Dockefile.pdf_김성호
Wonjun Hwang
 
Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptxReimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
Reimagine How You and Your Team Work with Microsoft 365 Copilot.pptx
John Moore
 
AsyncAPI v3 : Streamlining Event-Driven API Design
AsyncAPI v3 : Streamlining Event-Driven API DesignAsyncAPI v3 : Streamlining Event-Driven API Design
AsyncAPI v3 : Streamlining Event-Driven API Design
leonid54
 
Q1 2025 Dropbox Earnings and Investor Presentation
Q1 2025 Dropbox Earnings and Investor PresentationQ1 2025 Dropbox Earnings and Investor Presentation
Q1 2025 Dropbox Earnings and Investor Presentation
Dropbox
 
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
GDG Cloud Southlake #42: Suresh Mathew: Autonomous Resource Optimization: How...
James Anderson
 
fennec fox optimization algorithm for optimal solution
fennec fox optimization algorithm for optimal solutionfennec fox optimization algorithm for optimal solution
fennec fox optimization algorithm for optimal solution
shallal2
 
AI 3-in-1: Agents, RAG, and Local Models - Brent Laster
AI 3-in-1: Agents, RAG, and Local Models - Brent LasterAI 3-in-1: Agents, RAG, and Local Models - Brent Laster
AI 3-in-1: Agents, RAG, and Local Models - Brent Laster
All Things Open
 
On-Device or Remote? On the Energy Efficiency of Fetching LLM-Generated Conte...
On-Device or Remote? On the Energy Efficiency of Fetching LLM-Generated Conte...On-Device or Remote? On the Energy Efficiency of Fetching LLM-Generated Conte...
On-Device or Remote? On the Energy Efficiency of Fetching LLM-Generated Conte...
Ivano Malavolta
 
Ad

Database development coding standards

  • 1. DATABASE DEVELOPMENT AND CODING STANDARDS SQL & Database Guidelines
  • 2. INDEX 1. NAMING CONVENTIONS 2. DECLARING VARIABLES 3. SELECT STATEMENTS 4. CURSORS 5. WILDCARD CHARACTERS 6. NOT EQUAL OPERATORS 7. DERIVED TABLES 8. SQL BATCHES 9. ANSI-STANDARD JOIN CLAUSES 10. STORED PROCEDURES NAMING CONVENTION 11. USING VIEWS 12. TEXT DATA TYPES 13. INSERT STATEMENTS 14. ACCESSING TABLES 15. STORED PROCEDURE RETURNING VALUES 16. OBJECT CASE 17. T-SQL VARIABLES 18. OFFLOAD TASKS 19. CHECK FOR RECORD EXISTENCE 20. OBJECT OWNER 21. UPSERT STATEMENTS 22. DATETIME COLUMNS 23. MEASURE QUERY PERFORMANCE 24. INDEXES
  • 3. 1 - Naming Conventions All T-SQL Keywords must be upper case. All declared variable names must be Camel Case while all stored procedure names, function names, trigger names, Table names and Columns names in query must be Pascal Case. All view names must start with the letter ‘v’ followed by the name of the view in Pascal Case Example: SELECT * FROM Employee WHERE ID = 2 DECLARE @minSalary int CREATE PROCEDURE GetEmployees If you are creating a table belonging to a specific module, make sure to append a 3 character prefix before the name of each table, example: LABResult LABSpecimen LABOrder RADImage RADResult Note that all table names must be singular. When creating columns, make sure to append a ‘_F’ to the end of each column you intend to use as a flag. If there are exactly two statuses for the flag, use ‘bit’ data type, if there are 3 or more statuses, use ‘char(1)’ data type. If the column is foreign key reference, append ‘_FK’ to the end of the column name. This makes it easy to distinguish flag and foreign key columns: CREATE TABLE Employee( ID INT IDENTITY NOT NULL PRIMARY KEY, FirstName varchar(max), Sex_F BIT, Person_FK int, Status_F CHAR(1) ) 2 - Declaring Variables Always declare variables at the top of your stored procedure and set their values directly after declaration. If your database runs on SQL Server 2008, you can declare and set the variable on the same line. Take a look at the following statement under SQL 2000/SQL 2005 and the second statement under SQL 2008. Standard programming language semantics are added in SQL 2008 for short assignment of values: DECLARE @i int SET @i = 1 SET @i = @i + 1 ------------------- DECLARE @i int = 1 SET @i +=1
  • 4. 3 - Select Statements Do not use SELECT * in your queries. Always write the required column names after the SELECT statement. This technique results in reduced disk I/O and better performance: SELECT CustomerID, CustomerFirstName, City From Customer If you need to write a SELECT statement to retrieve data from a single table, don’t SELECT the data from a view that points to multiple tables. Instead, SELECT the data from the table directly, or from a view that only contains the table you are interested in. If you SELECT the data from the multi-table view, the query will experience unnecessary overhead, and performance will be hindered. 4 - Cursors Try to avoid server side cursors as much as possible. Always stick to a ‘set-based approach’ instead of a ‘procedural approach’ for accessing and manipulating data. Cursors can often be avoided by using SELECT statements instead. If a cursor is unavoidable, use a WHILE loop instead. A WHILE loop is always faster than a cursor. But for a WHILE loop to replace a cursor you need a column (primary key or unique key) to identify each row uniquely. 5 - Wildcard Characters Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that result in an index scan, which defeats the purpose of an index. The following statement results in an index scan, while the second statement results in an index seek: SELECT EmployeeID FROM Locations WHERE FirstName LIKE '%li' SELECT EmployeeID FROM Locations WHERE FirsName LIKE 'a%i' 6 - Not Equal Operators Avoid searching using not equals operators (<> and NOT) as they result in table and index scans. 7 - Derived Tables Use ‘Derived tables’ wherever possible, as they perform better. Consider the following query to find the second highest salary from the Employees table: SELECT MIN(Salary) FROM Employees WHERE EmpID IN (SELECT TOP 2 EmpID FROM Employees ORDER BY Salary Desc) The same query can be re-written using a derived table, as shown below, and it performs twice as fast as the above query: SELECT MIN(Salary) FROM (SELECT TOP 2 Salary FROM Employees ORDER BY Salary DESC) This is just an example, and your results might differ in different scenarios depending on the database design, indexes, volume of data, etc. So, test all the possible ways a query could be written and go with the most efficient one. 8 - SQL Batches Use SET NOCOUNT ON at the beginning of your SQL batches, stored procedures and triggers in production environments.
  • 5. This suppresses messages like ‘(1 row(s) affected)’ after executing INSERT, UPDATE, DELETE and SELECT statements. This improves the performance of stored procedures by reducing network traffic. 9 - ANSI-Standard Join Clauses 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. Whereas with older style joins, the WHERE clause handles both the join condition and filtering data. The first of the following two queries shows the old style join, while the second one show the new ANSI join syntax: 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%' ---------------------------------------------- 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%' 10 - Stored Procedures Naming Convention 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. 11 - Using Views Views are generally used to show specific data to specific users based on their interest. Views are also used to restrict access to the base tables by granting permission only on views. Yet another significant use of views is that they simplify your queries. Incorporate your frequently required, complicated joins and calculations into a view so that you don’t have to repeat those joins/calculations in all your queries. Instead, just select from the view. 12 - Text Data Types Try not to use TEXT or NTEXT data types for storing large textual data. The TEXT data type has some inherent problems associated with it and will be removed from future version of Microsoft SQL Server. For example, you cannot directly write or update text data using the INSERT or UPDATE Statements. Instead, you have to use special statements like READTEXT, WRITETEXT and UPDATETEXT. There are also a lot of bugs associated with replicating tables containing text columns. So, if you don’t have to store more than 8KB of text, use CHAR(8000) or VARCHAR(8000) data types instead. In SQL 2005 and 2008, you can use VARCHAR(max) for storing unlimited amount of textual data.
  • 6. 13 - Insert Statements Always use a column list in your INSERT statements. This helps in avoiding problems when the table structure changes (like adding or dropping a column). 14 - Accessing Tables Always access tables in the same order in all your stored procedures and triggers consistently. This helps in avoiding deadlocks. Other things to keep in mind to avoid deadlocks are: 1. Keep your transactions as short as possible. Touch as few data as possible during a transaction. 2. Never, ever wait for user input in the middle of a transaction. 3. Do not use higher level locking hints or restrictive isolation levels unless they are absolutely needed. 4. Make your front-end applications deadlock-intelligent, that is, these applications should be able to resubmit the transaction incase the previous transaction fails with error 1205. 5. In your applications, process all the results returned by SQL Server immediately so that the locks on the processed rows are released, hence no blocking. 15 - Stored Procedure Returning Values Make sure your stored procedures always return a value indicating their status. Standardize on the return values of stored procedures for success and failures. The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters. If your stored procedure always returns a single row result set, consider returning the result set using OUTPUT parameters instead of a SELECT statement, as ADO handles output parameters faster than result sets returned by SELECT statements. 16 - Object Case Always be consistent with the usage of case in your code. On a case insensitive server, your code might work fine, but it will fail on a case sensitive SQL Server if your code is not consistent in case. For example, if you create a table in SQL Server or a database that has a case-sensitive or binary sort order; all references to the table must use the same case that was specified in the CREATE TABLE statement. If you name the table as ‘MyTable’ in the CREATE TABLE statement and use ‘mytable’ in the SELECT statement, you get an ‘object not found’ error. 17 - T-SQL Variables Though T-SQL has no concept of constants (like the ones in the C language), variables can serve the same purpose. Using variables instead of constant values within your queries improves readability and maintainability of your code. Consider the following example: SELECT OrderID, OrderDate FROM Orders WHERE OrderStatus IN (5,6) The same query can be re-written in a mode readable form as shown below: DECLARE @ORDER_DELIVERED, @ORDER_PENDING SELECT @ORDER_DELIVERED = 5, @ORDER_PENDING = 6 SELECT OrderID, OrderDate FROM Orders WHERE OrderStatus IN (@ORDER_DELIVERED, @ORDER_PENDING) 18 - Offload tasks Offload tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications if these operations are going to consume more CPU cycles on the database server.
  • 7. Also try to do basic validations in the front-end itself during data entry. This saves unnecessary network roundtrips. 19 - Check for record Existence If you need to verify the existence of a record in a table, don’t use SELECT COUNT (*) in your Transact-SQL code to identify it, which is very inefficient and wastes server resources. Instead, use the Transact-SQL IF EXITS to determine if the record in question exits, which is much more efficient. For example: Here’s how you might use COUNT(*): IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx') Here’s a faster way, using IF EXISTS: IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx') The reason IF EXISTS is faster than COUNT(*) is because the query can end immediately when the text is proven true, while COUNT(*) must count go through every record, whether there is only one, or thousands, before it can be found to be true. 20 - Object Owner For best performance, all objects that are called from within the same stored procedure should all be owned by the same owner, preferably dbo. If they are not, then SQL Server must perform name resolution on the objects if the object names are the same but the owners are different. When this happens, SQL Server cannot use a stored procedure “in-memory plan” over, instead, it must re-compile the stored procedure, which hinders performance. There are a couple of reasons, one of which relates to performance. First, using fully qualified names helps to eliminate any potential confusion about which stored procedure you want to run, helping to prevent bugs and other potential problems. But more importantly, doing so allows SQL Server to access the stored procedures execution plan more directly, and in turn, speeding up the performance of the stored procedure. Yes, the performance boost is very small, but if your server is running tens of thousands or more stored procedures every hour, these little time savings can add up. 21 - Upsert Statements SQL Server 2008 introduces Upsert statements which combine insert, update, and delete statements in one ‘Merge’ statement. Always use the Merge statement to synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table MERGE table1 AS target USING ( SELECT ID,Name FROM table2 ) AS source (ID,Name) ON ( target.Table2ID = source.ID ) WHEN NOT MATCHED AND target.Name IS NULL THEN DELETE
  • 8. WHEN NOT MATCHED THEN INSERT (name, Table2ID) VALUES(name + ' not matched', source.ID) WHEN MATCHED THEN UPDATE SET target.name = source.name + ' matched' OUTPUT $action,inserted.id,deleted.id; 22 - DateTime Columns Always use ‘datetime2’ data type in SQL 2008 instead of the classic ‘datetime’. Datetime2 offers optimized data storage by saving 1 additional byte from the classic datetime. It has a larger date range, a larger default fractional precision, and optional user-specified precision. If your column is supposed to store the date only portion, use the ‘date’ date type while if you want to store the time portion, use the ‘time’ data type. Below is a list of examples of these new data types look like: time 12:35:29. 1234567 date 2007-05-08 smalldatetime 2007-05-08 12:35:00 datetime 2007-05-08 12:35:29.123 datetime2 2007-05-08 12:35:29. 1234567 datetimeoffset 2007-05-08 12:35:29.1234567 +12:15 23 - Measure Query Performance Always use statistics time feature to measure your important query and stored procedure’s performance. Use statistics time to optimize your queries Take a look at this example: SET STATISTICS TIME ON EXEC GetMedicalProcedures 1,10 SET STATISTICS TIME OFF The below information will be displayed in the Messages tab: SQL Server parse and compile time: CPU time = 6 ms, elapsed time = 6 ms. SQL Server Execution Times: CPU time = 24 ms, elapsed time = 768 ms. (10 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 125 ms. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 131 ms. This provides a good estimation of how long the query took to be executed, showing the CPU time (processing time) and elapsed time (CPU + I/O). 24 - Indexes Create indexes on tables that have high querying pressure using select statements. Be careful not to create an index on tables that are subject to real-time changes using CRUD operations. An index speeds up a select clause if the indexed column is included in the query, especially if it is in the WHERE clause. However, the same index slows down an insert statement whether or not the indexed column is included
  • 9. in the query. This downside occurs because indexes readjust and update statistics every time the table structure is changed. So use indexes wisely for optimizing tables having high retrieval rate and low change rate.
  翻译: