1 / 32

T-SQL Coding Standards And Best Practices

for developers. T-SQL Coding Standards And Best Practices. Gogula G. Aryalingam http://dbantics.wordpress.com. Rationale. Witness to totally messed up databases Perspectives of a lot of developers regarding SQL Server (or any database system ) :

nola-butler
Download Presentation

T-SQL Coding Standards And Best Practices

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. for developers T-SQLCoding Standards And Best Practices Gogula G. Aryalingam http://dbantics.wordpress.com

  2. Rationale • Witness to totally messed up databases • Perspectives of a lot of developers regarding SQL Server (or any database system): • Hostility towards SQL Server (the database system) • High priority to the client application • Back-end nature of SQL Server (the database system) • Any more reasons why you do not like SQL Server?

  3. Developers: • What Do You Do with SQL Server?

  4. Object Naming Conventions

  5. Object Naming Conventions • Why naming conventions? • Reduces the effort needed to read and understand • Increases the fluency in identifying objects • Do you use naming conventions?

  6. Object Naming Conventions • Tables: • Use upper camel case • Do not use underscores to separate words • Use the plural form of nouns • E.g.: EmailAddresses, Customers, SalesInvoices • For relationship tables combine the related tables names • E.g.: StudentsCourses • Consider using Schemas (in SQL Server 2005 and later) to group related objects (in databases with large structures) • Suggestions?

  7. Object Naming Conventions • Field Names: • Use upper camel case • Do not use underscores to separate words • Use the singular form of nouns • E.g.: FirstName, DateOfBirth • Using the data type as a prefix is not considered a best practice anymore • For identity column names use table name suffixed with ‘ID’ • Suggestions?

  8. Object Naming Conventions • Other SQL Server objects • Views: vwSalesSummary2007 • Indexes: IX_Customers_SocialSecurityNumber • Stored Procedures: • Do not prefix with sp_ • Names based on functionality: • Get: GetSalesInvoices • Insert: InsertCustomers • Update: UpdateCourses • References: • http://vyaskn.tripod.com/object_naming.htm • http://www.cms.hhs.gov/dbadmin/downloads/sqlserverstandardsandguildelines.pdf

  9. Prettifying the Horrifying

  10. Horrifying • Take a look at this piece of code:

  11. Prettifying the Horrifying • Why prettify? • Readability by others • Readability by self • Results in: • Ease of debugging and modifying of code

  12. Prettifying the Horrifying • How to prettify? • Indents • Comments • Upper case for keywords • Shortening lines • Use square braces for table/view names and column names • Use table aliases using • Use tabs instead white spaces • Suggestions?

  13. Standards for Creating Tables

  14. Standards for creating tables • Make sure all tables are in the 3rd Normal form • Primary keys for unique row identification • Choosing a primary key • Natural key vs. Surrogate key • Natural keys have a tendency to change • An integer identity (surrogate) column is the best suited • [UserID] int IDENTITY(1,1) • Avoid GUID/UNIQUEIDENTIFIER data types for primary key • Reference: • http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

  15. Standards for creating tables • Choose data types with the minimal size as possible • Use Unicode data types only if it is needed • Make sure data integrity is applied • Primary keys, Foreign keys, Check, Default and Unique constraints • Keep in mind the 8060 B row size rule • Choosevarchar(max), nvarchar(max) and varbinary(max) over text, ntext and image

  16. Standards for creating tables • Avoid storing BLOBs in tables especially if there is constant access • Alternatively: • Store the path in the table and the data in files • Store the data in a varbinary(max) field using FILESTREAM (SQL Server 2008)

  17. Best Querying Practices

  18. Querying Tips • Rather than SELECT * FROM …use SELECT [col1],… [coln] FROM …Select only the columns that are required for output • Use Common Tables Expressions (CTEs) wherever possible instead of temporary and derived tables • SET NOCOUNT ON within batches and stored procedures to increase on performance

  19. Querying Tips • Avoid cursors as much as possible • Alternatively use: • Set based approach to update or insert data from one tables to another • Tables variable and While loop (suited for small result sets) • Wild card characters at the beginning of a phrase in the LIKE clause should be avoided • WHERE [Name] LIKE ‘%Powell’ • Refer to table names with schema name prefixed • … FROM [HumanResources].[Employee]

  20. Querying Tips • Prefix column names with table name or alias • SELECT Employee.[Name], Contact.[Address]FROM … • Avoid using functions on columns in the WHERE clause • WHERE UPPER([Name]) = ‘BARBIE’ • Declare all variables and initialize values at the beginning of the code (Makes the query optimizer reuse plans) • When checking for existence of records, simply useIF EXISTS(SELECT * FROM dbo.Employees).It does not return a result set, hence is fast.

  21. Querying Tips • Avoid dynamic SQL • Try to find alternatives that do not constitute of dynamic SQL. • If at all using dynamic SQL, use sp_executesql instead of EXECUTE (EXEC) • When testing query performance using the graphical execution plan, look for Index seeks over Index scans or Table scans. • When performing Inserts, use column list in the INSERT clause: • INSERT INTO ([Name], [Age], [Address])VALUES (‘Neil’, 32, ‘Hendala Junc.’)

  22. Querying Tips • Place all data access tasks in SQL Server itself.Avoid queries and data manipulations on the client app/business tier. • Use stored procedures • Reference: • http://blog.sqlauthority.com/2008/09/25/sql-server-guidelines-and-coding-standards/ • Suggestions?

  23. Trigger Mania

  24. Trigger Mania • Perform all referential and domain integrity rules using constraints • Avoid using triggers for this purpose (poor performance) • Use only if cannot be implemented by constraints • Avoid triggers for business functionality • Less visible • Can avoid indirect recursion problems • Alternatively use stored procedures • Triggers can be used for tasks such as auditing and custom validations

  25. Trigger Mania • When writing triggers • Write for a recordset rather than for a single record • Suggestions?

  26. Q & A

  27. Q1 • You have an SQL Server 2008 database. You need to load data from one table to another. New records will have to be added, existing records need to be updated and records not in the source should be deleted from the destination. • How would you perform this with best performance? • Using the MERGE statement

  28. Q2 • You use an SQL Server 2005 database. You need to store map images which are between 50MB and 100MB in size. What is the optimum method to perform the storage? • Store the images in the file system and the file path in an SQL Server table

  29. Q3 • What alternatives can be used for cursors? • Set based operations • Table variables and WHILE loop (for small data sets)

  30. Q4 • Name some Best Practices for triggers. • Avoid using business functionality within triggers • Code for record sets rather than single records • Best suited for auditing and other custom tasks

  31. Thank you • E-mail: gogulaa@gmail.com • Twitter: http://twitter.com/gogula • Blog: http://dbantics.wordpress.com • Web: http://sqlserveruniverse.com

More Related