http://www.sqlservercentral.com/Content tagged Advanced, Database Design posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)Sizing a database can be one of the most arduous tasks a DBA, analyst or developer must attend to. It?s time consuming and more hours go into analyzing the database than actually sizing it. This article focuses on how to monitor the database?s growth after its deployed and contains some tips on how to size it before deployment. We will also dive a little into how to benchmark your database against a robust data load. http://www.sqlservercentral.com/articles/Administering/sizingadatabase/383/2008/08/01http://www.sqlservercentral.com/articles/Administering/sizingadatabase/383/Two weeks ago Andy started his wildly successful series on Worst Practices. This week he continues that series discussing why failing to use primary keys and clustered indexes are worst practices. Agree or disagree, read the article and join the discussion. One thing you'll have to admit, reading an article by Andy is a lot more interesting than reading Books Online! http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/2008/05/09http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/An IP address is something we all recognize and is a piece of data that is quite prevalent in many systems. However it is a piece of data tha presents some challenges in its storage and retrieval. SQL Server guru David Poole presents us with a look at how you can work with this strange formatting. http://www.sqlservercentral.com/articles/Advanced+Querying/2871/2008/02/19http://www.sqlservercentral.com/articles/Advanced+Querying/2871/In working on an existing database, DBAs often look to normalize tables and correct obvious flaws in design. Recently Andy Warren was working on a consulting project and realized that defaults would prevent many issues. http://www.sqlservercentral.com/articles/Advanced/2779/2007/12/28http://www.sqlservercentral.com/articles/Advanced/2779/How many DBAs need a solution to track those changes made for multiple systems? Auditing is becoming more and more prevalent in all systems and having a good solution can really make your DBA job interesting. New author Keren Ramot brings us his technique that works indepedent of the database structure. http://www.sqlservercentral.com/articles/Security/2773/2007/12/26http://www.sqlservercentral.com/articles/Security/2773/As systems get bigger, servers consolidated, and SQL Server back ends more types of applications, the need for cross database queries for reports, updates, and more will continue to grow. Steve Jones looks at a few ways that you can design your linkages and talks about why he prefers one over the other. http://www.sqlservercentral.com/articles/Advanced/designingcrossdatabasequeries/1753/2007/10/19http://www.sqlservercentral.com/articles/Advanced/designingcrossdatabasequeries/1753/Developing an enterprise architecture can be viewed as connect-the-dots for adults. However, in doing so, all you get is a pretty picture and not a management tool that can help an organization understand and manage itself.http://www.sqlservercentral.com/redirect/articles/3246/2007/10/04http://www.sqlservercentral.com/redirect/articles/3246/We are always searching for new ways to improve the performance of our database systems. Raghavendra Narayana brings us a new article that examines an idea for rebuilding partitions and indexes based on data access: in real time! http://www.sqlservercentral.com/articles/Advanced/3210/2007/09/26http://www.sqlservercentral.com/articles/Advanced/3210/Optimize the performance of a database which includes history or status tables.http://www.sqlservercentral.com/redirect/articles/3233/2007/09/13http://www.sqlservercentral.com/redirect/articles/3233/There is an urgent need to acknowledge the limitations of data models and find a way to bridge the logical-physical divide between models and actual databases so that database designs can be fully understood.http://www.sqlservercentral.com/redirect/articles/3244/2007/09/13http://www.sqlservercentral.com/redirect/articles/3244/Part 7 of our "Developing a Complete SQL Server OLTP Database Project" discusses performance issues resulting from using EncryptByCert and DecryptByCert. Read the article and download the code to run the test yourself.http://www.sqlservercentral.com/redirect/articles/3160/2007/08/28http://www.sqlservercentral.com/redirect/articles/3160/SQL Server 2005 includes support for encrypting and decrypting data for storage using EncryptByCert. Let’s look at how to implement this in the SqlCredit database.http://www.sqlservercentral.com/redirect/articles/3161/2007/08/24http://www.sqlservercentral.com/redirect/articles/3161/Alex Kuznetsov starts off a series of three articles on 'Unit Testing' your database development work. He begins by describing five simple rules that make all the difference. http://www.sqlservercentral.com/redirect/articles/3153/2007/08/20http://www.sqlservercentral.com/redirect/articles/3153/Many new DBAs think that User-defined types are a very cool feature in SQL Server when they first encounter them. However many experienced DBAs soon realize that UDTs can be a pain to work with and longtime SQL Server guru David Poole discusses his experiences http://www.sqlservercentral.com/articles/Advanced/3104/2007/07/23http://www.sqlservercentral.com/articles/Advanced/3104/In this article, we will examine some rarely explored areas that concern foreign keys; in particular, we will look at disabled and un-trusted foreign keys. http://www.sqlservercentral.com/redirect/articles/3057/2007/06/29http://www.sqlservercentral.com/redirect/articles/3057/The only way to guarantee that your business rules are always enforced for all your data is by using constraints. Learn how to use them to enforce business rules in the database.http://www.sqlservercentral.com/redirect/articles/3016/2007/06/05http://www.sqlservercentral.com/redirect/articles/3016/A view is most commonly thought of as a SELECT statement. Most developers will simply create a view to "group" complex SELECT statements for reuse within another view or stored procedures. It makes typing easier! But the really power of views is their ability to implement business rules.http://www.sqlservercentral.com/redirect/articles/2966/2007/04/23http://www.sqlservercentral.com/redirect/articles/2966/Should you store dynamically generated web-site graphics in a database or is the file system the better option? Dino illustrates how to make this decision in ASP.NEThttp://www.sqlservercentral.com/redirect/articles/2961/2007/04/17http://www.sqlservercentral.com/redirect/articles/2961/The great debate over NULLs will probably never end. Should we use them in our databases? What meaning do they have? These are all valid questions. In this article SQL Server expert Michael Coles takes a look at the problems that are inherent in eliminating NULLs completely from a database. http://www.sqlservercentral.com/articles/Advanced/2921/2007/04/10http://www.sqlservercentral.com/articles/Advanced/2921/An IP address is something we all recognize and is a piece of data that is quite prevalent in many systems. However it is a piece of data tha presents some challenges in its storage and retrieval. SQL Server guru David Poole presents us with a look at how you can work with this strange formatting. http://www.sqlservercentral.com/articles/Advanced+Querying/2871/2008/02/19http://www.sqlservercentral.com/articles/Advanced+Querying/2871/In most relational database implementations. Update and Delete commands destroy the data that was there prior to their issue. However, some systems require that no information is ever physically deleted from or updated in the database. In this article, Arthur Fuller presents a solution to this requirement in the form of a Point-in-Time architecture: a database design which allows a user to recreate an image of the database as it existed at any previous point in time, without destroying the current image.http://www.sqlservercentral.com/redirect/articles/2882/2007/02/28http://www.sqlservercentral.com/redirect/articles/2882/How many DBAs need a solution to track those changes made for multiple systems? Auditing is becoming more and more prevalent in all systems and having a good solution can really make your DBA job interesting. New author Keren Ramot brings us his technique that works indepedent of the database structure. http://www.sqlservercentral.com/articles/Security/2773/2007/12/26http://www.sqlservercentral.com/articles/Security/2773/This is the fourth part of an ongoing series. The series is titled The Data Stewardship Approach to Data Governance. For information regarding future chapters, please use my contact information below. Previous articles were titled The Data Won't Govern Itself, Data Governance Is NOT a Methodology and The Tools of Data Governance.http://www.sqlservercentral.com/redirect/articles/2794/2007/01/23http://www.sqlservercentral.com/redirect/articles/2794/This article describes how to implement a single refactoring within your database. It contains a worked example of the Move Column structural refactoring, whereby we move the Customer.Balance column to the Account table, a seemingly straightforward change to improve the database design.http://www.sqlservercentral.com/redirect/articles/2812/2007/01/16http://www.sqlservercentral.com/redirect/articles/2812/In SQL Server 2005, object ownership will have a different meaning, but since most of us work with SQL Server 2000, Kathi Kellenberger has written a good introduction to understanding how object ownership works and why you might want to follow certain practices. http://www.sqlservercentral.com/articles/Advanced/understandingobjectownership/1966/2007/01/05http://www.sqlservercentral.com/articles/Advanced/understandingobjectownership/1966/In working on an existing database, DBAs often look to normalize tables and correct obvious flaws in design. Recently Andy Warren was working on a consulting project and realized that defaults would prevent many issues. http://www.sqlservercentral.com/articles/Advanced/2779/2007/12/28http://www.sqlservercentral.com/articles/Advanced/2779/Joe Celko spouts off on one of the basics of database design.http://www.sqlservercentral.com/redirect/articles/2729/2006/12/25http://www.sqlservercentral.com/redirect/articles/2729/Are you up to the latest design challenge? A great way to sharpen our analysis and modeling skills is to continuously address real-world scenarios. A modeling scenario with suggested solutions appears each month in this Design Challenge column. The scenario is emailed to more than 1,000 modelers. The responses are then consolidated into this column. http://www.sqlservercentral.com/redirect/articles/2610/2006/10/16http://www.sqlservercentral.com/redirect/articles/2610/Sizing a database can be one of the most arduous tasks a DBA, analyst or developer must attend to. It?s time consuming and more hours go into analyzing the database than actually sizing it. This article focuses on how to monitor the database?s growth after its deployed and contains some tips on how to size it before deployment. We will also dive a little into how to benchmark your database against a robust data load. http://www.sqlservercentral.com/articles/Administering/sizingadatabase/383/2008/08/01http://www.sqlservercentral.com/articles/Administering/sizingadatabase/383/A seemingly never-ending battle in online database forums involves the question of whether or not database application development should involve the use of stored procedures.http://www.sqlservercentral.com/redirect/articles/2443/2006/06/08http://www.sqlservercentral.com/redirect/articles/2443/