{"id":3560,"date":"2019-07-31T16:24:07","date_gmt":"2019-07-31T10:54:07","guid":{"rendered":"https:\/\/www.idslogic.com\/blog\/?p=3560"},"modified":"2019-08-17T14:52:39","modified_gmt":"2019-08-17T09:22:39","slug":"sql-server-database-design-best-practices-and-tips-a-guide","status":"publish","type":"post","link":"https:\/\/www.idslogic.com\/blog\/sql-server-database-design-best-practices-and-tips-a-guide","title":{"rendered":"SQL Server Database Design Best Practices and Tips:  A Guide"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_72 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.idslogic.com\/blog\/sql-server-database-design-best-practices-and-tips-a-guide\/#Handling_Tables_and_Indexes_in_the_SQL_Server_Databases\" title=\"Handling Tables and Indexes in the SQL Server Databases\">Handling Tables and Indexes in the SQL Server Databases<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.idslogic.com\/blog\/sql-server-database-design-best-practices-and-tips-a-guide\/#Normalize_the_Data\" title=\"Normalize the Data:\u00a0\">Normalize the Data:\u00a0<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.idslogic.com\/blog\/sql-server-database-design-best-practices-and-tips-a-guide\/#Adopt_Ways_to_Boost_the_Database_Performance\" title=\"Adopt Ways to Boost the Database Performance:\">Adopt Ways to Boost the Database Performance:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.idslogic.com\/blog\/sql-server-database-design-best-practices-and-tips-a-guide\/#Rebuild_or_De-Fragment_Your_Indexes_Periodically\" title=\"Rebuild or De-Fragment Your Indexes Periodically:\">Rebuild or De-Fragment Your Indexes Periodically:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.idslogic.com\/blog\/sql-server-database-design-best-practices-and-tips-a-guide\/#Give_Room_to_Your_Database_to_Grow\" title=\"Give Room to Your Database to Grow:\">Give Room to Your Database to Grow:<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.idslogic.com\/blog\/sql-server-database-design-best-practices-and-tips-a-guide\/#Recovery_Options_for_SQL_Server\" title=\"Recovery Options for SQL Server:\">Recovery Options for SQL Server:<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<p><span style=\"font-size: 14pt;\">While designing a database, a lot has to be kept in mind so that information stored in the framework can be easily pulled out when needed. Many organizations initially design their databases by professionals who are not deeply experienced in database designing. <\/span><\/p>\n<p><span style=\"font-size: 14pt;\">And unfortunately at times, it leads to making technical decisions that are later on discovered to be less functional. Good database design is needed to meet the processing requirements in the SQL Server systems.<\/span><\/p>\n<p><span style=\"font-size: 14pt;\">The goal of this blog is to help database professionals recognize the best development practices and techniques that would offer optimized results. Knowing what you want from your SQL server is the first step towards running a successful business application. <\/span><\/p>\n<p><span style=\"font-size: 14pt;\">Effective deployment comes from knowing the business goals of your company and how to use the SQL server best to meet them. When it is about database designing, thinking about the long term is a good idea.<\/span><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Handling_Tables_and_Indexes_in_the_SQL_Server_Databases\"><\/span><span style=\"font-size: 18pt; color: #008000;\"><strong>Handling Tables and Indexes in the SQL Server Databases<\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>&nbsp;<\/p>\n<p><span style=\"font-size: 14pt;\">It is recommended that the smaller or narrower the database tables are, the better they are. Avoid using blob and nvarchar columns in the <span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/www.idslogic.com\/ms-sql-server-databse-development\/\">SQL Server database<\/a><\/span> design in order to keep the tables on the narrow site. Using the nvarchar will only require twice as much space in the tables and this will take a query longer to read the larger columns.<\/span><\/p>\n<p><span style=\"font-size: 14pt;\">Using clustered indexes however, doesn\u2019t matter what kind of columns your table contains, this will help in the long run. Non clustered indexes often create extra copies of the data and this takes up more space in the database. This leads to unused indexes, and Database admins should watch for them as part of the database monitoring process.<\/span><\/p>\n<p>&nbsp;<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Normalize_the_Data\"><\/span><span style=\"font-size: 16pt;\"><strong><span style=\"color: #003366;\">Normalize the Data:\u00a0<\/span> <\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-size: 14pt;\">Most organizations today work in a hybrid world of SQL and NoSQL databases that work together in complex arrangements. And with such complicated structures, it is vital to normalize the data to attain minimum redundancy.\u00a0 So, it is best to eliminate repeated attributes and multi valued attributes and then start on the sub keys.<\/span><\/p>\n<p>&nbsp;<\/p>\n<hr \/>\n<h3><span class=\"ez-toc-section\" id=\"Adopt_Ways_to_Boost_the_Database_Performance\"><\/span><span style=\"font-size: 16pt;\"><strong><span style=\"color: #003366;\">Adopt Ways to Boost the Database Performance:<\/span> <\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-size: 14pt;\">Columnstore indexes are very useful for querying data warehouses as they help to accelerate large table scans.\u00a0 When data is stored in columns in the SQL server instead of the row based format, the admins can control the columns that are read or checked by the system and this makes the entire process quicker. <\/span><\/p>\n<p><span style=\"font-size: 14pt;\">Columnstore indexes can be used with In-memory OLTP which is an in-memory engine built into SQL server to boost the performance of transactional databases.<\/span><\/p>\n<p>&nbsp;<\/p>\n<hr \/>\n<h3><span class=\"ez-toc-section\" id=\"Rebuild_or_De-Fragment_Your_Indexes_Periodically\"><\/span><span style=\"font-size: 16pt;\"><strong><span style=\"color: #003366;\">Rebuild or De-Fragment Your Indexes Periodically:<\/span> <\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-size: 14pt;\">For best performance of your database, always ensure to rebuild or de-fragment the indexes periodically. The SQL Server supports on-line index rebuild and this helps to reduce the index maintenance window considerably.\u00a0 <\/span><\/p>\n<p><span style=\"font-size: 14pt;\">Since maintaining indexes require considerable system resources, weigh your index maintenance options carefully for faster results.<\/span><\/p>\n<p>&nbsp;<\/p>\n<hr \/>\n<h3><span class=\"ez-toc-section\" id=\"Give_Room_to_Your_Database_to_Grow\"><\/span><span style=\"font-size: 16pt;\"><strong><span style=\"color: #003366;\">Give Room to Your Database to Grow:<\/span> <\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-size: 14pt;\">Your business may be small now, but within a few years it will grow and you will have lots of data to handle. It is recommended to check the auto grow option in SQL Server to ensure that they align with the future need for added file or log space when the transactions are run. In future if you have to create a new database, then the default settings that are applied to it may not meet your application needs.<\/span><\/p>\n<p><span style=\"font-size: 14pt;\">It is best to plan for all the data that you think you might need in the future and tacking on an extra year\u2019s worth of storage space. This will allow you some extra space and offer you room to grow when you produce more data than it is expected.<\/span><\/p>\n<p>&nbsp;<\/p>\n<hr \/>\n<h3><span class=\"ez-toc-section\" id=\"Recovery_Options_for_SQL_Server\"><\/span><span style=\"font-size: 16pt;\"><strong><span style=\"color: #003366;\">Recovery Options for SQL Server:<\/span> <\/strong><\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-size: 14pt;\">Before delving deep into the designing aspects, think about what may happen during any natural or man-made disaster. So, plan for auto backups, replication and recovery and any other procedures essential to ensure that the database structure remains intact.<\/span><\/p>\n<p><span style=\"font-size: 14pt;\">Thus to conclude, it can be said that following the above mentioned points will help to make the performance of your database faster, and with business directives changing over time, the database will also need some fine tuning to stay in line with the organization\u2019s current goals.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>While designing a database, a lot has to be kept in mind so that information stored in the framework can be easily pulled out when needed. Many organizations initially design their databases by professionals who are not deeply experienced in&#8230;<\/p>\n","protected":false},"author":1,"featured_media":3689,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[104],"tags":[924,925],"class_list":["post-3560","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-web-development","tag-sql-server-database","tag-sql-server-database-development"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/www.idslogic.com\/blog\/wp-json\/wp\/v2\/posts\/3560","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.idslogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.idslogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.idslogic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.idslogic.com\/blog\/wp-json\/wp\/v2\/comments?post=3560"}],"version-history":[{"count":1,"href":"https:\/\/www.idslogic.com\/blog\/wp-json\/wp\/v2\/posts\/3560\/revisions"}],"predecessor-version":[{"id":3562,"href":"https:\/\/www.idslogic.com\/blog\/wp-json\/wp\/v2\/posts\/3560\/revisions\/3562"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.idslogic.com\/blog\/wp-json\/wp\/v2\/media\/3689"}],"wp:attachment":[{"href":"https:\/\/www.idslogic.com\/blog\/wp-json\/wp\/v2\/media?parent=3560"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.idslogic.com\/blog\/wp-json\/wp\/v2\/categories?post=3560"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.idslogic.com\/blog\/wp-json\/wp\/v2\/tags?post=3560"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}