{"id":1119,"date":"2011-09-27T22:43:52","date_gmt":"2011-09-28T03:43:52","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?p=1119"},"modified":"2011-09-28T21:52:27","modified_gmt":"2011-09-29T02:52:27","slug":"a-list-of-sql-best-practices","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2011\/09\/a-list-of-sql-best-practices\/","title":{"rendered":"A list of SQL best practices"},"content":{"rendered":"

Here are some SQL programming guidelines and best practices we collected, keeping quality, performance and maintainability in mind. This list is not complete at this moment, and will be constantly updated.<\/p>\n

\n
- \nDo not use SELECT * in your queries.\n<\/li>\n
- \nAlways use table aliases when your SQL statement involves more than one source.\n<\/li>\n
- \nUse the more readable ANSI-Standard Join clauses instead of the old style joins.\n<\/li>\n
- \nDo not use column numbers in the ORDER BY clause.\n<\/li>\n
- \nAlways use a column list in your INSERT statements.\n<\/li>\n
- \nDon’t ever use double quotes in your T-SQL code.\n<\/li>\n
- \nDo not prefix your stored procedure names with \u201csp_\u201d.\n<\/li>\n
- \nAlways use a SQL formatter to format your sql like Instant SQL Formatter<\/a>(Free and Online)\n<\/li>\n<\/ul>\n

  ### \nDo not use SELECT * in your queries, write out the full syntax.
  \n<\/h3>\n

  Always write the required column names after the SELECT statement, like:\t\t<\/p>\n

  ###

  ###

  ###

  ###

  ###

  ###

  ###

      \r\nSELECT CustomerID, CustomerFirstName, City from Emp;\r\n<\/pre>\nThis technique results in reduced disk I\/O and better performance.<\/p>\n\n Always use table aliases when your SQL statement involves more than one source
      \n<\/h3>\nIf more than one table is involved in a from clause, each column name must be qualified using either the complete table name or an alias. The alias is preferred.
      \nIt is more human readable to use aliases instead of writing columns with no table information.<\/p>\n\nUse the more readable ANSI-Standard Join clauses instead of the old style joins
      \n<\/h3>\nWith ANSI joins, the WHERE clause is used only for filtering data.
      \nWhere as with older style joins, the WHERE clause handles both the join condition and filtering data. <\/p>\nFurthermore ANSI join syntax supports the full outer join.<\/p>\nThe first of the following two queries shows the old style join,
      \nwhile the second one shows the new ANSI join syntax:<\/p>\n
      \n-- old style join<\/span>
      \n
      SELECT<\/span> a<\/span>.<\/span>Au_id<\/span>,<\/span>
      \n
             t<\/span>.<\/span>Title<\/span>
      \n
      FROM<\/span>   TITLES<\/span> t<\/span>,<\/span>
      \n
             AUTHORS<\/span> a<\/span>,<\/span>
      \n
             TITLEAUTHOR<\/span> ta<\/span>
      \n
      WHERE<\/span>  a<\/span>.<\/span>Au_id<\/span> =<\/span> ta<\/span>.<\/span>Au_id<\/span>
      \n
             AND<\/span> ta<\/span>.<\/span>Title_id<\/span> =<\/span> t<\/span>.<\/span>Title_id<\/span>
      \n
             AND<\/span> t<\/span>.<\/span>Title<\/span> LIKE<\/span> \u2018<\/span>%<\/span>Computer<\/span>%<\/span>\u2019<\/span>
      \n<\/span><\/div>\n <\/p>\n
      \n--ANSI join syntax<\/span>
      \n
      SELECT<\/span> a<\/span>.<\/span>Au_id<\/span>,<\/span>
      \n
             t<\/span>.<\/span>Title<\/span>
      \n
      FROM<\/span>   AUTHORS<\/span> a<\/span>
      \n
             INNER<\/span> JOIN<\/span> TITLEAUTHOR<\/span> ta<\/span>
      \n
               ON<\/span> a<\/span>.<\/span>Au_id<\/span> =<\/span> ta<\/span>.<\/span>Au_id<\/span>
      \n
             INNER<\/span> JOIN<\/span> TITLES<\/span> t<\/span>
      \n
               ON<\/span> ta<\/span>.<\/span>Title_id<\/span> =<\/span> t<\/span>.<\/span>Title_id<\/span>
      \n
      WHERE<\/span>  t<\/span>.<\/span>Title<\/span> LIKE<\/span> \u2018<\/span>%<\/span>Computer<\/span>%<\/span>\u2019<\/span>
      \n<\/span><\/div>\n\nDo not use column numbers in the ORDER BY clause
      \n<\/h3>\nAlways use column names in an order by clause. Avoid positional references. Consider the following example in which the second query is more readable than the first one:<\/p>\n
      \nSELECT<\/font> OrderID<\/font>,<\/font> OrderDate<\/font>
      \n
      FROM<\/font> Orders<\/font>
      \n
      ORDER<\/font> BY<\/font> 2<\/font><\/p>\nSELECT<\/font> OrderID<\/font>,<\/font> OrderDate<\/font>
      \n
      FROM<\/font> Orders<\/font>
      \n
      ORDER<\/font> BY<\/font> OrderDate<\/font>
      \n
      \n<\/span><\/div>\n\nAlways use a column list in your INSERT statements
      \n<\/h3>\nAlways specify the target columns when executing an insert command. This helps in avoiding problems when the table structure changes (like adding or dropping a column). <\/p>\nConsider the following table:<\/p>\n
      \nCREATE<\/span> TABLE<\/span> EUROPEANCOUNTRIES<\/span>
      \n
        (<\/span>
      \n
           Countryid<\/span>   INT<\/span> PRIMARY<\/span> KEY<\/span>,<\/span>
      \n
           Countryname<\/span> VARCHAR<\/span>(<\/span>25<\/span>)<\/span>
      \n
        )<\/span>
      \n<\/span><\/div>\nHere\u2019s an INSERT statement without a column list , that works perfectly:<\/p>\n
      \nINSERT<\/font> INTO<\/font> EuropeanCountries<\/font>
      \n
      VALUES<\/font> (<\/font>1<\/font>,<\/font> \u2018Ireland\u2019<\/font>)<\/font>
      \n
      \n<\/span><\/div>\nNow, let\u2019s add a new column to this table:<\/p>\n
      \nALTER<\/font> TABLE<\/font> EuropeanCountries<\/font>
      \n
      ADD<\/font> EuroSupport<\/font> bit<\/i><\/font>
      \n
      \n<\/span><\/div>\nNow run the above INSERT statement. You get the following error from SQL Server:<\/p>\n\tServer: Msg 213, Level 16, State 4, Line 1<\/p>\n\tInsert Error: Column name or number of supplied values does not match table definition. <\/p>\nThis problem can be avoided by writing an INSERT statement with a column list as shown below:<\/p>\n
      \nINSERT<\/font> INTO<\/font> EuropeanCountries<\/font>
      \n
      (<\/font>CountryID<\/font>,<\/font> CountryName<\/font>)<\/font>
      \n
      VALUES<\/font> (<\/font>1<\/font>,<\/font> \u2018England\u2019<\/font>)<\/font>
      \n
      \n<\/span><\/div>\n\nDon’t ever use double quotes in your T-SQL code
      \n<\/h3>\nUse single quotes for string constants. If it’s necessary to qualify an object name, use (non-ANSI SQL standard) brackets around the name, like table name: ORDER DETAILS in this SQL.<\/p>\n
      \nSELECT<\/font> od<\/font>.<\/font>[Discount]<\/font>,<\/font>
      \n
             od<\/font>.<\/font>[Quantity]<\/font>,<\/font>
      \n
             od<\/font>.<\/font>[Unitprice]<\/font>
      \n
      FROM<\/font>   [northwind]<\/font>.<\/font>[dbo]<\/font>.<\/font>[ORDER DETAILS]<\/font> AS<\/font> od<\/font>
      \n
      \n<\/span><\/div>\n\nDo not prefix your stored procedure names with \u201csp_\u201d
      \n<\/h3>\nThe 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. <\/p>\nSo you can really save time in locating the stored procedure by avoiding the \u201csp_\u201d prefix.<\/p>\n\nAlways use a SQL formatter to format your sql like Instant SQL Formatter<\/a>(Free and Online)
      \n<\/h3>\nThe formatting of SQL code may not seem that important,but consistent formatting makes it easier for others to scan and understand your code. <\/p>\nSQL statements have a structure, and having that structure be visually evident makes it much easier to locate and verify various parts of the statements. <\/p>\nUniform formatting also makes it much easier to add sections to and remove them from complex T-SQL statements for debugging purposes.<\/p>\nInstant SQL Formatter<\/a> is a free online SQL tidy tool that makes your SQL script readable instantly.<\/p>\n","protected":false},"excerpt":{"rendered":"Here are some SQL programming guidelines and best practices we collected, keeping quality, performance and maintainability in mind. This list is not complete at this moment, and will be constantly updated. Do not use SELECT * in your queries. Always use table aliases when your SQL statement involves more than one source. Use the more readable ANSI-Standard Join clauses instead of the old style joins. Do not use column numbers in the ORDER BY clause. Always use a column list in your INSERT statements. Don’t ever use double quotes in your T-SQL code. Do not prefix your stored procedure names\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[19,25],"tags":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nsql coding best practices<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n