SQL Standards / Guidelines and SQL Best Practices

1     Advantages of Database Coding Standard



Here are the few advantages when we are following the coding standard and guidelines.

·         Easy Maintenance
·         Easy Code Integration / Deployment
·         Uniform Problem Solving approach
·         Minimizes Communication
·         Prevent Repeating Occurrence of Particular Problem
·         Minimizes Performance Pitfalls
·         Ensure Quality Product
·         Saves Money Due to Less Man Hours


Easy Maintenance

The source code will be more comprehensive and will become easy-to-maintain. As the programmers became more and more familiar with the coding style as they implements the coding standards on project after project.
Easy Code Integration/ Deployment

Prevent integration problems during merging all developer working copies to a master copy several times a day for deployment.
Uniform Problem Solving Approach

The uniform approach for solving problems will be handy because the code standards documents reveal the recommended methods that were tried and tested on the earlier projects.
Minimizes Communication

Less communication between developers and managers will be needed because the programmers will not asked anymore on the details of the specification document because the defaults are all stated in coding standards.
Prevent Repeating Occurrence of Particular Problem

It is common to the less experience programmer to re-invent the wheel. When there are coding standards, there is a big chance that particular problem is not really a new problem, but in fact, a solution may be documented before.

Minimizes Performance Pitfalls

Repeated performance pitfalls could be avoided. It is a common case that a released software product could be less impressive when it comes to performance when the real data has been loaded in the new developed database application.
Ensure Quality Product

·      Well documented coding standards will aid the creation of "Test Scripts". Having reviewed the source code and tested an application based on compliance to coding standards, it added strong direction to ensure quality of the software product.


·     Because code standards implements traceability, the item ids can be used to describe a violation in the "Test Results" document that both developers and testers are familiar.


·     It is important for the project managers to maintain and secure source code quality on their projects by implementing coding standards.



It is also beneficial for the organization who are applying for ISO 9001 license because coding standards is a complement from organization's execution plan requirements.


Saves Money Due to Less Man Hours

Lesser man-hour consumption as the sum of all efforts implementing coding standards.

2  Naming Conventions:


Naming Tables:

Rules:               Pascal notation; end without “s”

Examples:        Operator
BankAddress  - Junction table


Naming Stored Procedures:

Rules:               USP_<AppName>_<TableName>_<Action>[By]_<column[Abbr]>

Examples:        USP_PP_Operator_Insert
                        USP_PP_Operator_Delete
USP_PP_Operator_Select_By_ID

Naming Views:

Rules:               VW_<Table Name >_<Usage/Description>

Examples:        VW_FeatureAction_Report


Naming Triggers:

Rules:               TR_<TableName>_<action>_<Usage/Description>

Examples:         TR_Operator_Delete
TR_Bank_Insert
TR_Operator_Update_UserStatus

Notes:

The use of triggers is discouraged or not advisable unless until that is must.


Naming Indexes:

Rules:              IX_<TableName>_<columns separated by _>

Examples:        IX_Operator_UserLoginID


Naming Primary Keys:

Rules:               PK_<TableName>_ <columns separated by _>

Examples:         PK_Operator_OperatorID

                                    PK_PayeeAddressType_IDS
Naming Foreign Keys:

Rules:               FK_<TableName1>_< columns separated by _>

Example:           FK_FeatureAction_FeatureID



Naming Default Constraints:

Rules:               DF_<TableName>_<ColumnName>

Example:           DF_Feature_DisplayStatus



Naming Check Constraints:

Rules:               CH_<TableName>_<ColumnName>

Example:           CH_SystemMessage_MessageType



Naming Columns:
           
Naming it <table name>ID for IDENTITY or PRIMARY KEY columns of table

If a column references another table’s column, name it <table name>ID

Example:                                                                   
The PaymentType table has an PaymentTypeID column.
The PaymentTypeBank table should have a PaymentTypeID column.

                        OR

Utilize table prefix modifiers if it really required/followed based on your project needs. For example, the “Tele_Detail” table would have field names prefixed with 'td_'. This helps prevent confusion over same name fields in different tables.

Try to avoid _(under score) in table and column names. If it is necessary to use (_) please maintain the same format throughout the database.

Foreign key fields are named [table prefix]_[field name]. For example, a Model table would have m_id as PK. The FK in Model_Detail could be md_m_id.

Stored procedures should be named usp_tele_[description]. For example, a Tele_Detail insert procedure could be named: usp_tdeal_tele_details_insert.

3  DBA Guidelines:


  1. The DBO in the database must be set to SA.

  1. We use the SQL Server authentication.  A new SQL Server login/user must be established for connection to the database. If this is not a web application and the Project Manager plans to use Windows Authentication, he will need to document requirements and explain processes for bringing on new users to the DBA.

  1. The name of the new user includes the database project name. For example, “teledeal” or “teledeal_read” or “teledeal_web” or “teledeal_admin”.

  1. ALL sql statements MUST be in stored procedures. THERE ARE NO DIRECT SQL STATEMENTS EXECUTED FROM THE APPLICATION TO THE DATABASE!  There may be exceptions to this rule if the application is not a web application.

  1. There should be no need to give ANY permission to PUBLIC.

  1. Access to system tables or system stored procedures should not be required for the database user.  Exceptions must be outlined and discussed with the DBA.

  1. Typically, the new SQL Server user needs only EXECUTE permissions on stored procedures.

  1. If direct access to tables is needed, only required access to specific tables should be granted to the database user.

  1. DBO access to a new user should NOT be granted unless absolutely necessary.

  1. Datatypes should be consciously considered and designed into the database schema. Inappropriate datatypes or too large character fields should be identified and resolved prior to DEMO testing.

  1. Attention should be given to NOT NULL or NULL constraints. Data that is never NULL should have a NOT NULL constraint.

  1. Any special security requirements or concerns involving database interaction should be brought to the attention of the DBA.

  1. Any special email requirements or concerns involving database interaction should be brought to the attention of the DBA.

  1. Once QA starts testing of an application DEMO data cannot be lost for continuous testing, then all db updates must come in the form of T-SQL statements. If the Project Manager asks the DBA to replace an existing db, the data will be lost.

  1. Changes to the DEVELOPMENT database should be logged by developers after release for DEMO testing.  It is the developers’ responsibility to know what was done and why DEMO testing would be conducted after release.

  1. Reused code/objects should have one central location; changes should be able to make in one place and that code would be invoked in all the places.  For example, zip code location logic already is available on the database servers and does not require new table setups for every new application.

  1. Any T-SQL database updates must be tested for errors before submitting to the DBA. T-SQL statements with obvious errors are NOT ACCEPTABLE.

  1. Any T-SQL database updates should be restricted to logged database changes. Avoid updates that drop and restore all objects without regard to modifications (Avoid all objects and recreate all objects).

  1. Modifications to stored procedures should be briefly documented within the procedure if changed after release for DEMO testing.

  1. Cursors should be avoided in database coding if possible.

  1. If requested, the DBA can supply a backup for sending data to DEVELOPMENT team for testing.  However, this is not recommended as it creates miscommunications and errors in other opportunities.  The Project Manager should have a QA plan in place that does not necessitate ping-pong databases.

  1. Known future database phase functionality should be planned (as much as possible) in the first phase of the database design.

  1. Good database design will achieve ALL the required project functionality and deliver acceptable performance.  It takes into consideration growth and updation of tables and records.

  1. Referential Integrity within the database should be enforced.

  1. Tables will usually have a Primary Key.  The only time a Primary Key can be omitted from table design is for a limited size table (10-100 rows) that is stand-alone (not significantly referenced by other tables), is not involved in complex joins or queries, and not expected to receive any significant updates or insertions.

  1. Necessary INDEX should be created on tables for fast retrieval of data. The same index column order needs to be followed in WHERE clause.

  1. Proper LOCK mechanism should be defined for CLUSTERED INDEX (PRIMARY KEY) and NON CLUSTERED INDEX.

  1. The DBA should be informed of any specialized file or document storage functionality that impacts the database.

  1. The Project Manager and developers are responsible for trouble-shooting and debugging any third party components that access the database.

  1. No third party additions to the Database Servers are permitted without the full participation and consent of the DBA.

  1. The DBA should be informed of any path requirements or LAN architecture that impacts the database or database testing.  For example, the Project Manager should inform the DBA which web-server(s) are involved, whether web-services are utilized, the type of tools (Java, .NET, PHP, ASP etc.,), terminal server connections, etc.

  1. Provision must be made for DBA evaluation of the database design of the project.  This may occur in the form of a review of the proposed database schema, a review of the completed DEVELOPMENT version of the database, or both.  It is the Project Manager’s responsibility to schedule this with the DBA in order to avoid any conflicts with other project priorities, allow time for any issues to be resolved, and prevent vacation or time-off being scheduled on critical days.

  1. Requests for unscheduled, next-day or same-day rollouts of a database represent failure of project management and planning!.

4  General Rules:


·         Follow the naming conventions.

·     Do not use spaces in the name of database objects.

·     Do not use SQL keywords as the name of database objects, In cases where this is  necessary, surround the object name with brackets, such as [Year].

·     Do not prefix stored procedures with ‘sp_’, prefix  it with “USP_”(user defined stored   procedure).

·     Prefix table names with the owner name.
o   Example: dbo.Action

5  Formatting:


·         Use upper case for all SQL keywords.

o    SELECT, INSERT, UPDATE, WHERE, AND, OR, LIKE, etc.

·      Indent code to improve readability.

·     Comment code blocks that are not easily understandable.

o    Use single-line comment markers(--).
o    Reserve multi-line comments (/*.. ..*/) for blocking out sections of code.

·     Use single quote characters to delimit strings.

o    Nest single quotes to express a single quote or apostrophe within a string.
o    For example, SET @Example = 'SQL''s Tutorial Tips'.

·     Use parentheses to increase readability.

o    WHERE (color=’red’ AND (size = 1 OR size = 2)).

·  Use BEGIN..END blocks only when multiple statements are present within a conditional code segment.

·     Use one blank line to separate code sections.

·    Use spaces so that expressions read like sentences.

o    @OperatorID = 25, not @OperatorID=25.

·    Format JOIN operations using indents.

·    Also, use ANSI 92 Joins instead of old style joins.

·   Place SET statements before any executing code in the procedure.

6  Coding:


Good database design and coding will achieve all the required project functionality and deliver acceptable performance.  It takes into quality delivery to your customer.

·         Optimize queries using the tools provided by SQL Server.

Use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Make sure your queries do an "Index seek" instead of an "Index scan" or a "Table scan." A table scan or an index scan is a highly undesirable and should be avoided where possible.

EXAMPLE 1:

IF EXISTS (SELECT 1 FROM dbo.Action WITH (NOLOCK) WHERE ActionID = 50)
Instead Of: 
IF EXISTS (SELECT COUNT(ActionID) FROM dbo.Action WHERE ActionID = 50)


EXAMPLE 2:

Consider the following query to find the second highest offer MaxManPayAmount from the Operator table:

SELECT MIN(MaxManPayAmount) FROM Operator WHERE OperatorID IN
(SELECT TOP 2 OperatorID FROM Operator ORDER BY MaxManPayAmount Desc)

The same query can be re-written using a derived table, as shown below, and it performs generally as fast as the above query:
           
SELECT MIN(opr.MaxManPayAmount) FROM
(SELECT TOP 2 MaxManPayAmount FROM dbo.Operator WITH (NOLOCK) ORDER BY MaxManPayAmount DESC) AS opr


·         Do not use SELECT *,  instead practice writing required column names after the SELECT statement.

·      Do not use SQL keywords as the name of database objects, In cases where this is necessary, surround the object name with brackets, such as [Year].

·      Use table WITH (NOLOCK) command for SELECT statement which avoids a deadlock occurrence.

·      Avoid dynamic SQL statements as much as possible.

o    Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan at runtime. IF and CASE statements come in handy to avoid dynamic SQL.

·         Avoid using <> as a comparison operator.

o    Use ID IN (1,3,4,5) instead of ID <> 2.

·         Avoid unnecessary use of temporary tables.

o    Use 'Derived tables' or CTE (Common Table Expressions) wherever possible, as they perform better.

·         Use SET NOCOUNT ON at the beginning of stored procedures, SQL batches and Triggers. This improves the performance of stored  procedures.

o    SET NOCOUNT ON suppresses messages like '(1 row(s) affected)' after executing INSERT, UPDATE, DELETE and SELECT statements. Performance is improved due to the reduction of network traffic.

·         Place all DECLARE statements before any other code in the stored procedure.

·         Do not use cursors or application loops to do inserts.

o    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. For a WHILE loop to replace a cursor, however, you need a column (primary key or unique key) to identify each row uniquely.

·         Fully qualify tables and column names.

o  Prefix table names with the owner name. This improves readability and avoids unnecessary confusion.Microsoft SQL Server Books Online states that qualifying table names with owner names help in execution plan reuse, further boosting performance.

Example: dbo.FeatureAction

·         Use ANSI 92 Joins instead of old style joins his improves readability and avoids unnecessary confusion.

False code:
            SELECT * FROM dbo.FeatureAction, Feature 
WHERE Feature.FeatureID = FeatureAction.FeatureID
           
True code:
SELECT FeatureAction.FeatureID, Feature.FeatureName FROM dbo.FeatureAction
INNER JOIN dbo.Feature WITH (NOLOCK) ON Feature.FeatureID = FeatureAction.FeatureID

·         Avoid the use of cross joins, if possible.

·     Avoid to define default values for parameters in stored procedure unless until it is needed.

o    If a default is needed, the front end will supply the value.

·         Do not use the RECOMPILE option for stored procedures.

·         Do not use column numbers in the ORDER BY clause. Use appropriate column name.

·         Do not use GOTO.

·         Check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can rollback the transaction if an error occurs.
OR  
  Use TRY/CATCH

·         Do basic validations in the front-end itself during data entry.

·         Off-load 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.

·         Always use a column list in your INSERT statements.

o    This helps avoid problems when the table structure changes.(like adding or dropping a column)

·         When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible. This reduces error possibilities.

o    Each table must have a primary key, In most cases it should be an IDENTITY column named <tablename>ID

o    Tables will usually have a Primary Key.  The only time a Primary Key can be omitted from table design is for a limited size table (10-100 rows) that is stand-alone (not significantly referenced by other tables), is not involved in complex joins or queries, and not expected to receive any significant updates or insertion. 

·         Normalize data to third normal form.

o    Do not compromise on performance, sometimes, a little de-normalization results in better performance.

·         Necessary INDEX should be created on tables for fast retrieval of data. The same index column order needs to be followed in WHERE clause.

·      Proper LOCK mechanism should be defined for CLUSTERED INDEX (PRIMARY KEY) and NON CLUSTERED INDEX.

Following instructions help to avoid deadlocks. Other things to keep in mind to avoid deadlocks are:

  • Always access tables in the same order in all stored procedures and triggers consistently.

  • Keep transactions as short as possible.

  • Touch the minimum amount of data possible during a transaction.

  • Never wait for user input in the middle of a transaction.

  • Do not use higher level locking hints or restrictive isolation levels unless  they are absolutely needed. Use table WITH (NOLOCK) command for SELECT statement which avoids the deadlock occurs on table level.

  • Define the appropriate LOCKs Option during Primary Key, Unique Key, and Index creation. [ALLOW_ROW_LOCKS =  ON,   ALLOW_PAGE_LOCKS = ON,  hint tells the SQL engine to start out with row locks. If more than twenty five row locks are needed, that might be escalated into a page lock, and so on. This is the granularity of the lock]  

·         Minimize the use of NULLs, as they often confuse front-end applications, unless the  applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form.

o    Any expression that deals with NULL results in a NULL output.

o    The ISNULL and COALESCE functions are helpful in dealing with NULL values.

·         Do not use the identitycol or rowguidcol unless until it is needed.

·         Default constraints must be defined at the column level.

o    Not to specify DEFAULT CONSTRAINTS for Columns allow with NULL value.  

·         Define all constraints (that apply to more than one column) at the table level other than defaults.

·         Referential Integrity (Foreign Key) within the database should be enforced.

·     Avoid rules, database level defaults that must be bound or user-defined data types. While these are legitimate database constructs, opt for constraints and column defaults to hold the database consistent for development and conversion coding.

·         Use the CHAR data type for a column only when the column content is non-nullable and fixed length.

·         Use the VARCHAR data type for a column when the column content is variable size and could allow null.

o  CHAR(100), when NULL, will consume 100 bytes, resulting in space wastage. Preferably, use VARCHAR(100) in this situation. Variable-length columns have very little processing overhead compared with fixed-length columns.

o      VARCHAR data columns use an empty string wherever needed.

·         Avoid using TEXT or NTEXT datatypes for storing large textual data. use the maximum allowed characters of VARCHAR(MAX) and NVARCHAR(MAX).

o      Use the maximum allowed characters of VARCHAR instead.

o   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. So, if you don't have to store more than 8KB of text, use the CHAR(8000) or VARCHAR(8000)  data type instead.

·         Do not use white space in identifiers.

Example [Birth Date] date

·   Create STORED PROCEDURE as much as possible with business logic on the same database for accessing data.

o    Do not prefix stored procedures with ‘sp_’.

o    The prefix sp_ is reserved for system stored procedures 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. Time spent locating the stored procedure can be saved by avoiding the "sp_" prefix.

o    Do prefix stored procedures with “USP_”(user defined stored procedure).

·        You might need the length of a string variable in many places of your stored procedure, but don't call the LEN function whenever it's needed. Instead, call the LEN function once and store the result in a variable for later use.

·    Multiple records to be stored /deleted to table at an attempt, pass XML data as parameter-value to stored procedure for BULK INSERT OR BULK UPDATE OR BULK DELETE. XML data should created/formed from application server and pass it xml.

Example: Bulk Insert.

DECLARE @XML AS XML
--SET @XML = (select * from ED for xml auto)
SET @XML = '<ED EID="1" DID="1" SN="1"/>
                     <ED EID="1" DID="2" SN="2"/>
                     <ED EID="1" DID="3" SN="3"/>
                     <ED EID="2" DID="4" SN="1"/>
                     <ED EID="2" DID="5" SN="2"/>
                     <ED EID="3" DID="7" SN="2"/>
                     <ED EID="4" DID="7" SN="1"/>
                     <ED EID="4" DID="8" SN="2"/>
                     <ED EID="5" DID="4" SN="1"/>
                     <ED EID="5" DID="5" SN="2"/>
                     <ED EID="5" DID="6" SN="3"/>
                     <ED EID="6" DID="7" SN="1"/>
                     <ED EID="6" DID="8" SN="2"/>
                     <ED EID="6" DID="9" SN="3"/>
                     <ED EID="7" DID="0" SN="1"/>'
INSERT INTO EmployeeDegree
([EmployeeID], [DegreeID], [Sequence]) 
SELECT
    DOC.col.value('@EID', 'INT') as EmployeeID,
    DOC.col.value('@DID', 'INT') as DegreeID,
    DOC.col.value('@SN', 'INT') as Sequence
       FROM @XML.nodes('ED') DOC(col)



·         Access tables in the same order in your stored procedures and triggers consistently.

·         Do not call functions repeatedly within your stored procedures, triggers, functions and batches.

·         The RETURN statement is meant for returning the execution status only, but not data.

·      Return multiple result sets from one stored procedure to avoid trips from the application server to SQL server.

·      Avoid wild-card characters at the beginning of a word while searching using the LIKE keyword, as these results in an index scan, which defeats the purpose of an index.


7  Security

Secure Coding Practices are;
  • Encrypt high-value and sensitive data (User Credentials mandatory), the Bank Account, Bank Routing and TaxId if required
  • Use symmetric keys to encrypt data, and asymmetric keys or certificates to protect the symmetric keys.
  • Password-protect keys and remove master key encryption for the most secure configuration if required.
  • Do not delete pre-provisioned system certificates in the master database
  • Always back up the service master key, database master keys, and certificates by using the key-specific DDL statements.
  • Always back up your database to back up your symmetric and asymmetric keys.
  • TDE is recommended for encrypting existing applications or for performance sensitive applications.
  • Cell-level encryption can be used for defense in depth both for a database encrypted by TDE and for limited access control through the use of passwords.
  • Audit logs to be recorded and stored in database for debugging/tracing/reporting.
  • The Dev databases must be backed up daily basis, Stage/Test Databases must be backed up monthly. These backups are moved to the backup servers.
  • Use Parameterized stored procedure instead of dynamic SQL statements to prevent the SQL injection.

8  Example: Stored Procedure



-- ======================================================
/*
AUTHOR                        : Govind
CREATE DATE  : 22-Aug-2014
DESCRIPTION  : Logging Error.
SET STATISTICS TIME ON
EXEC [USP_PP_ErrorLog_Insert] 1, '1', 23, 'This is Error due to invalid data', 'Action Table', 'Action.js','getAction'
SET STATISTICS TIME OFF

SNO ALTERED DATE       AUTHOR                                    DESCRIPTION
---    ------------             ------                             -----------
1          09-APR-2015     Govind                          Changed Condition with @@TRANCOUNT instead of
@ERROR
2          25-JUN-2015      Govind                          Added additional parameter @ErrorLogMethodName  
*/
-- ======================================================
ALTER PROCEDURE [dbo].[USP_PP_ErrorLog_Insert]
            -- Parameters for the stored procedure
            @OperatorID INT,
            @SystemMessageID INT = 0,
            @ErrorLogLineNumber INT,
            @ErrorLogMessage VARCHAR(1000) = null,
            @ErrorLogFunction VARCHAR(50) = null,
            @ErrorLogClassName VARCHAR(30) = null,
            @ErrorLogMethodName VARCHAR(30) = null
AS

BEGIN
     SET NOCOUNT ON;
      BEGIN TRY
               BEGIN TRANSACTION
                                     
                        INSERT INTO [dbo].[ErrorLog]
           ([ErrorLogDate]
           ,[SystemMessageID]
           ,[ErrorLogMessage]
           ,[OperatorID]
           ,[ErrorLogFunction]
           ,[ErrorLogClassName]
           ,[ErrorLogMethodName]
           ,[ErrorLogLineNumber])
                        VALUES
           (GETDATE()
           ,@SystemMessageID
           ,@ErrorLogMessage
           ,@OperatorID
           ,@ErrorLogFunction
           ,@ErrorLogClassName
           ,@ErrorLogMethodName
           ,@ErrorLogLineNumber)

               COMMIT TRANSACTION
               RETURN -- 'SUCCESS'
     END TRY
           
     BEGIN CATCH
                         

IF @@TRANCOUNT > 0 -- @@ERROR>0
                                    ROLLBACK
                       
            SELECT @ErrorLogLineNumber = ERROR_LINE(),
                        @ErrorLogFunction = ERROR_PROCEDURE(),
@ErrorLogMessage = CAST(ERROR_NUMBER() AS VARCHAR(15)) + '-' + ERROR_MESSAGE()
        
         INSERT INTO [dbo].[ErrorLog]
           ([ErrorLogDate]
           ,[SystemMessageID]
           ,[ErrorLogMessage]
           ,[OperatorID]
           ,[ErrorLogFunction]
           ,[ErrorLogClassName]
           ,[ErrorLogMethodName]
           ,[ErrorLogLineNumber])
                        VALUES
           (GETDATE()
           ,@SystemMessageID
           ,@ErrorLogMessage
           ,@OperatorID
           ,@ErrorLogFunction
           ,@ErrorLogClassName
           ,@ErrorLogMethodName
           ,@ErrorLogLineNumber)
 
                         --SELECT @OutputText AS ErrorCode
                        RETURN --'ERROR'
                         
     END CATCH
                       
     SET NOCOUNT OFF;
                       
END



1 comment:

  1. Scott2 September 2020 at 22:29

    Yes, many companies are chasing "Big Data" dreams and Mongo, Hadoop, etc. However, these have their own limitations and are not necessarily the right solution for every situation. SQL Server, especially Azure SQL is prepared to handle some pretty big datasets and industries like Health Care have a lot already invested in SQL Server. Most certainly it is essential to have good planning and database architecture and the first step in that is having guidelines for coding standards and conventions.

    I can tell a lot about a company by looking at their schema and the adherence to, or lack of a good naming convention and solid code standards (such as "Code Complete"). There is a huge difference between good SQL code and poor SQL code and knowledge of indexing and query tuning.

    In fact, I am currently looking for a good standards doc as a starting point.

    ReplyDelete