{"id":1179,"date":"2011-09-29T03:55:02","date_gmt":"2011-09-29T08:55:02","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?p=1179"},"modified":"2011-09-29T04:16:18","modified_gmt":"2011-09-29T09:16:18","slug":"case-capitalization-in-sql-statement","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/","title":{"rendered":"Case (Capitalization) in SQL statement"},"content":{"rendered":"

When format SQL query, capitalization refers to the way in which SQL token should or should not be capitalized in your code. For example, some developers prefer to make all reserved keys uppercase, others prefer lowercase, and some mix and match. It\u2019s all a matter of preference. When determining what strategies to implement with regard to case, we should take into account the following considerations:<\/p>\n

1. SQL token should be categorized in terms of keywords, identifier, data types, variables and constants. Identifier includes schema name, table name, column name, alias name, function\/stored procedure name, parameter and etc. Some SQL tokens may be treated as a specific category such identifier start with sp_ in SQL Server.<\/p>\n

2. Case rules can be upppercase, lowercase, InitCapEachWord(camelCase<\/a>), InitCap(capitalize first letter only) and no change.<\/p>\n

Once you have those considerations in mind, then you can define your own capitalization strategies.<\/p>\n

Let\u2019s look at some examples that demonstrate different capitalization strategies. <\/p>\n

In the first statement, all SQL reserved words are uppercase, all identifier such as tables and columns are lowercase, but the first letter of function name was capitalized(InitCap).<\/p>\n


\nSELECT<\/span> p1<\/span>.<\/span>productmodelid<\/span>
\n
FROM<\/span>   production<\/span>.<\/span>product<\/span> AS<\/span> p1<\/span>
\n
GROUP<\/span>  BY<\/span> p1<\/span>.<\/span>productmodelid<\/span>
\n
HAVING<\/span> Max<\/span>(<\/span>p1<\/span>.<\/span>listprice<\/span>)<\/span> >=<\/span> ALL<\/span> (<\/span>SELECT<\/span> Avg<\/span>(<\/span>p2<\/span>.<\/span>listprice<\/span>)<\/span>
\n
                                 FROM<\/span>   production<\/span>.<\/span>product<\/span> AS<\/span> p2<\/span>
\n
                                 WHERE<\/span>  p1<\/span>.<\/span>productmodelid<\/span> =<\/span> p2<\/span>.<\/span>productmodelid<\/span>)<\/span>;<\/span> 
\n<\/span><\/div>\n

In the next example, all the reserved words and identifer are lowercase, but the table name and alias name are uppercase.<\/p>\n


\nselect<\/span> P1<\/span>.<\/span>productmodelid<\/span>
\n
from<\/span>   production<\/span>.<\/span>PRODUCT<\/span> as<\/span> P1<\/span>
\n
group<\/span>  by<\/span> P1<\/span>.<\/span>productmodelid<\/span>
\n
having<\/span> Max<\/span>(<\/span>P1<\/span>.<\/span>listprice<\/span>)<\/span> >=<\/span> all<\/span> (<\/span>select<\/span> Avg<\/span>(<\/span>P2<\/span>.<\/span>listprice<\/span>)<\/span>
\n
                                 from<\/span>   production<\/span>.<\/span>PRODUCT<\/span> as<\/span> P2<\/span>
\n
                                 where<\/span>  P1<\/span>.<\/span>productmodelid<\/span> =<\/span> P2<\/span>.<\/span>productmodelid<\/span>)<\/span>;<\/span> 
\n<\/span><\/div>\n

Here\u2019s one other example. This time, the keywords are uppercase, the data types lowercase, identifier(such as dbo) lowercase, the table and column are camel case:<\/p>\n


\nCREATE<\/span> TABLE<\/span> dbo<\/span>.<\/span>EmployeePhoto<\/span>
\n
  (<\/span>
\n
     EmployeeId<\/span>      int<\/span> NOT<\/span> NULL<\/span> PRIMARY<\/span> KEY<\/span>,<\/span>
\n
     Photo<\/span>           varbinary<\/span>(<\/span>Max<\/span>)<\/span> NULL<\/span>,<\/span>
\n
     MyRowGuidColumn<\/span> uniqueidentifier<\/span> NOT<\/span> NULL<\/span> ROWGUIDCOL<\/span> UNIQUE<\/span> DEFAULT<\/span> Newid<\/span>(<\/span>)<\/span>
\n
  )<\/span>;<\/span> 
\n<\/span><\/div>\n

The key to a capitalization strategy should be readability. There\u2019s no real right or wrong, as long as the standards are applied consistently throughout the organization.<\/p>\n

SQL formatting tool can help you do this job precisely and quickly<\/h3>\n

Some conventional sql formatting tools use ad hoc string processing methods to implement the formatting process, so it can’t distinguish table and column name from other identifiers.<\/p>\n

SQL Pretty Printer<\/a> parse the SQL query according to the syntax of various database SQL dialect. So it can easily turn table name(product) into uppercase while make column name(productmodelid) camelCase like this.<\/p>\n


\nselect<\/span> ProductModelId<\/span>
\n
from<\/span>   production<\/span>.<\/span>PRODUCT<\/span> 
\n<\/span><\/div>\n

Please note that schema name(production) was keep unchanged.<\/p>\n

SQL Pretty Printer<\/a> also has the ability to apply case rule to a specific identifier category, identifier start with sp_ was set to uppercase in next SQL statement: <\/p>\n


\nexecute<\/span> SP_EXECUTESQL<\/span>
\n
  @QUERY<\/span>,<\/span>
\n
  N’@Age int’<\/span>,<\/span>
\n
  @AGE<\/span> =<\/span> 25<\/span> 
\n<\/span><\/div>\n

Here is a screenshot of SQL Pretty Printer’s capitalization format options.<\/p>\n


\n

\"SQL
SQL Pretty Printer capitalization format options<\/figcaption><\/figure>
\n<\/a><\/p>\n

<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

When format SQL query, capitalization refers to the way in which SQL token should or should not be capitalized in your code. For example, some developers prefer to make all reserved keys uppercase, others prefer lowercase, and some mix and match. It\u2019s all a matter of preference. When determining what strategies to implement with regard to case, we should take into account the following considerations: 1. SQL token should be categorized in terms of keywords, identifier, data types, variables and constants. Identifier includes schema name, table name, column name, alias name, function\/stored procedure name, parameter and etc. Some SQL tokens\u2026<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[20,25],"tags":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nDetermining what strategies to implement with regard to case of SQL<\/title>\n<meta name=\"description\" content=\"Determining what strategies to implement with regard to case of SQL\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Determining what strategies to implement with regard to case of SQL\" \/>\n<meta property=\"og:description\" content=\"Determining what strategies to implement with regard to case of SQL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:published_time\" content=\"2011-09-29T08:55:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2011-09-29T09:16:18+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.dpriver.com\/images\/screenshots\/sqlpp_case_option.png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/\",\"name\":\"Determining what strategies to implement with regard to case of SQL\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2011-09-29T08:55:02+00:00\",\"dateModified\":\"2011-09-29T09:16:18+00:00\",\"description\":\"Determining what strategies to implement with regard to case of SQL\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Case (Capitalization) in SQL statement\"}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7\"},\"headline\":\"Case (Capitalization) in SQL statement\",\"datePublished\":\"2011-09-29T08:55:02+00:00\",\"dateModified\":\"2011-09-29T09:16:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/\"},\"wordCount\":747,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"articleSection\":[\"formatting\",\"sql\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/#respond\"]}]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"url\":\"https:\/\/www.dpriver.com\/blog\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Determining what strategies to implement with regard to case of SQL","description":"Determining what strategies to implement with regard to case of SQL","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/","og_locale":"en_US","og_type":"article","og_title":"Determining what strategies to implement with regard to case of SQL","og_description":"Determining what strategies to implement with regard to case of SQL","og_url":"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/","og_site_name":"SQL and Data Blog","article_published_time":"2011-09-29T08:55:02+00:00","article_modified_time":"2011-09-29T09:16:18+00:00","og_image":[{"url":"http:\/\/www.dpriver.com\/images\/screenshots\/sqlpp_case_option.png"}],"author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/","url":"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/","name":"Determining what strategies to implement with regard to case of SQL","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2011-09-29T08:55:02+00:00","dateModified":"2011-09-29T09:16:18+00:00","description":"Determining what strategies to implement with regard to case of SQL","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Case (Capitalization) in SQL statement"}]},{"@type":"Article","@id":"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/#article","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/"},"author":{"name":"admin","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7"},"headline":"Case (Capitalization) in SQL statement","datePublished":"2011-09-29T08:55:02+00:00","dateModified":"2011-09-29T09:16:18+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/"},"wordCount":747,"commentCount":0,"publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"articleSection":["formatting","sql"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dpriver.com\/blog\/case-capitalization-in-sql-statement\/#respond"]}]},{"@type":"Person","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/fd33d9e572b65788a80dc4bac2b4e5a7","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/61d4e409068502b7e4960eb89efb79f8?s=96&d=mm&r=g","caption":"admin"},"url":"https:\/\/www.dpriver.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/1179"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=1179"}],"version-history":[{"count":30,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/1179\/revisions"}],"predecessor-version":[{"id":1209,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/1179\/revisions\/1209"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=1179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/categories?post=1179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/tags?post=1179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}