Tips
Calculating Table Width in SQL Server
I am concerned our database design is overly denormalized. I believe we have some very wide tables which makes some of our coding very easy, but I am concerned about the data access and overall performance. Before I go too far down the path of just changing some of these tables or trying to change the ways of my team for future database design, how can I find out how wide some of these tables really are based on the data as compared to the theoretical maximum size? Can you provide a script or two that I can run on my SQL Server databases? Can you also provide any resources for practical database design? I know having some denormalization is reasonable, I am just not sure if some of the database design has gone to an extreme or not.
Data model access after a SQL Server database restore
After a database restore, why is it possible for me to access all of my objects except for the data model I created? Did I back it up correctly? Is it a separate backup and recovery process? Has it been corrupted?
This is the error message I am receiving: Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
Getting started with SQL Server database diagrams
Trying to visual a database model is sometimes quite difficult. When the model is small it is pretty easy to have an idea what tables reference other tables. But at the data model gets larger it is often difficult to see exactly how the tables relate. In this tip I show you how you can use the built-in SQL Server database diagram tool. I go through some of them messages you may get when getting started and then look at various tasks that you would perform using this free tool.
Identify All SQL Server Tables with Columns of a BLOB Data Type
After some recent SQL Server performance tuning, I noticed that one of my large core tables (important) has a column with a text data type. I had no idea that this table had a BLOB (binary large object) data type. After running some quick queries on the table\column and doing some quick analysis I have found that none of the entries exceed a few hundred characters. After talking to the development team, they indicated that a 500 character limit is enforced on the front end.
With this being said, I clearly do not need a column with the text data type and could use a varchar or nvarchar (Unicode) column with a width of 500. Since I was not aware of this particular BLOB column in one of my large core tables (important), I suspect some
Identifying Object Dependencies in SQL Server
Is there a way to identify which objects are dependent on other objects within a SQL Server database? I need to modify the table structure to add new columns. However, before making any changes I want to make sure that I understand all the object dependencies. I know this can be done, but is there a better way in SQL Server 2008 to quickly identify all the object dependencies?
Modify SQL Server database tables to keep similar columns together
When designing tables you may not always know what columns need to exist prior to when the table is created. Therefore over time table changes are made where you may add or drop columns. Based on this table modification you may want to have certain columns next to each other for easier management and grouping of like data in the table structure. It doesn't really matter to SQL Server where the column is located, but to us humans it sometimes makes a difference. Dropping a column is not a big deal because the column just goes away, but when adding a column the default process is to add the column to the end of the table. So how can you modify the table structure so all of the like columns are next to each other?
Retrieving SQL Server Column Properties with COLUMNPROPERTY
SQL Server stores a lot of data about your database objects in various places and in various formats. When pulling data about table columns this data can be pulled directly from the syscolumns table. Some of this information is useful as it is, but some of the data needs to be interpreted to understand. In addition to pulling data directly from syscolumns you can also use the information schema view. If you query from INFORMATION_SCHEMA.COLUMNS you get a lot of data in a useful format, but there is still some missing data. So how can you retrieve additional data about column level properties?
Simple way to create tables in SQL Server using Excel
When creating a new application that requires a data model to support it, the best approach is to use a data modeling tool to develop the logical and then physical data model. Although this is the best approach, not everyone does this for a full blown application let alone for a small utility application or even add on features to an existing database. Here is a simple approach to generate table structures, use them as they are and then regenerate them when a change is needed.
SQL Server 2005 Data Modeling Tools
Rarely do we (Developers and DBAs) have sufficient time for our projects and every day tasks. So when it comes to building a new system, inevitable what happens is time is limited and individual tables end up being built not a comprehensive model. As the system grows, we hope that the front end code is correct and keeps the data related, but when you have a data problem it is necessary to spend a few hours on checking and validating that the data is correct. So how can we break this cycle and migrate from building tables to a comprehensive data model?
SQL Server 2008 Sparse Columns Identifying Columns For Conversion
SQL Server 2008 has introduced a new way to store data for columns that contain excessive NULL values called Sparse Columns. What this means is that when you declare a column as Sparse and any time a NULL value is entered in the column it will not use any space. Is there a way to identify what columns would make a good candidate for this without having to analyze each column individually?
SQL Server Data Modeling Tools
With the recent tip on the native SQL Server data modeling capabilities, questions arose about data modeling alternatives in the marketplace. The native tools are a very valuable free solution, but may lack advanced functionality needed in some circumstances. As such, this tip outlines the data modeling tools available for SQL Server which can be leveraged by DBAs and Developers to meet a variety of data modeling needs.
SQL Server Data Type Consistency
A short while ago, I discussed the importance of consistent naming conventions (Use consistent SQL Server Naming Conventions). Another similar issue I see creep into environments is the haphazard use of data types. Often it seems abundantly clear that different tables were designed by separate people - to the point that the same data could be represented in two tables with distinct data types. In some cases it even seems random... from one table to the next, you might not know what data type you'll get.
SQL Server Database Design with a One To One Relationship
In order to manage any business situation which may arise, a SQL developer needs to know the basic relationship types and their uses. Perhaps the most rare, a one-to-one arrangement offers a few elegant ways to handle some common business needs. In reading this tip, you will become familiar with some practical implementations of a one-to-one relationship. This article assumes that the reader knows how to create relationships, either through T-SQL commands or data modeling tool.
SQL Server User Defined Data Types, Rules and Defaults
SQL Server provides numerous system data types to store dates, character based data, numeric data, etc. However there are some situations when a customized data type is needed for consistency across an application. Some examples could be phone numbers in a specific format, alpha numeric employee ID's, IP addresses, etc. What options are available to store this data in a unique manner?
Storing E-mail addresses more efficiently in SQL Server
A lot of people are storing large quantities of e-mail addresses in their systems. As someone who focuses on squeezing every bit of performance out of the various hardware components in a system, when I look at a table full of e-mail addresses, I can't help but think that it is wasteful. In this tip we look at ways to reduce the storage needs for email addresses.
Storing E-mail addresses more efficiently in SQL Server - Part 2
In my last tip, I shared some ideas for determining if you should consider breaking up the e-mail addresses you're storing, even putting the domain names in a separate table. I performed storage and timing comparisons for working with 10,000 unique e-mail addresses, but I completely ignored data compression. I wanted to revisit the same test case and apply data compression to the tables and see how that impacted the outcome.
Understanding First Normal Form in SQL Server
I've been asked to be the data modeler for a new application and I've not done this before. I understand the idea of getting the entities and how they connect together from customer interviews, but how do I apply the normalization rules to my data model? Also, why are they important? Check out this tip to learn more.
Using Computed Columns in SQL Server with Persisted Values
In my databases I had some values that were often calculated while generating several reports. Also there were some columns that were dependent on one or more other columns. As one column was updated triggers were being used to synchronize new values in dependent columns. I was required to provide a more efficient and standard approach to handle these types of scenarios. So how could I get rid of the overhead for calculations at report generation time and how could I avoid the use of triggers for synchronizing updated columns?
Using Microsoft Quadrant for data analysis and modeling
SQL Server Modeling CTP - Nov 2009 Release 2 which was formerly known as Oslo has been released and it ships with tools like Intellipad and Quadrant and also with languages like "M". In my view, these services and tools are more targeted towards .NET applications, but there are some features which database developers and DBAs can use to their advantage. Quadrant is one of these and in this tip we explore how this tool can be used for modeling and data analysis.
Top 10
Storing E-mail addresses more efficiently in SQL Server - Part 2
In my last tip, I shared some ideas for determining if you should consider breaking up the e-mail addresses you're storing, even putting the domain names in a separate table. I performed storage and timing comparisons for working with 10,000 unique e-mail addresses, but I completely ignored data compression. I wanted to revisit the same test case and apply data compression to the tables and see how that impacted the outcome.
Storing E-mail addresses more efficiently in SQL Server
A lot of people are storing large quantities of e-mail addresses in their systems. As someone who focuses on squeezing every bit of performance out of the various hardware components in a system, when I look at a table full of e-mail addresses, I can't help but think that it is wasteful. In this tip we look at ways to reduce the storage needs for email addresses.
Using Computed Columns in SQL Server with Persisted Values
In my databases I had some values that were often calculated while generating several reports. Also there were some columns that were dependent on one or more other columns. As one column was updated triggers were being used to synchronize new values in dependent columns. I was required to provide a more efficient and standard approach to handle these types of scenarios. So how could I get rid of the overhead for calculations at report generation time and how could I avoid the use of triggers for synchronizing updated columns?
Getting started with SQL Server database diagrams
Trying to visual a database model is sometimes quite difficult. When the model is small it is pretty easy to have an idea what tables reference other tables. But at the data model gets larger it is often difficult to see exactly how the tables relate. In this tip I show you how you can use the built-in SQL Server database diagram tool. I go through some of them messages you may get when getting started and then look at various tasks that you would perform using this free tool.
Understanding First Normal Form in SQL Server
I've been asked to be the data modeler for a new application and I've not done this before. I understand the idea of getting the entities and how they connect together from customer interviews, but how do I apply the normalization rules to my data model? Also, why are they important? Check out this tip to learn more.
SQL Server 2005 Data Modeling Tools
Rarely do we (Developers and DBAs) have sufficient time for our projects and every day tasks. So when it comes to building a new system, inevitable what happens is time is limited and individual tables end up being built not a comprehensive model. As the system grows, we hope that the front end code is correct and keeps the data related, but when you have a data problem it is necessary to spend a few hours on checking and validating that the data is correct. So how can we break this cycle and migrate from building tables to a comprehensive data model?
Retrieving SQL Server Column Properties with COLUMNPROPERTY
SQL Server stores a lot of data about your database objects in various places and in various formats. When pulling data about table columns this data can be pulled directly from the syscolumns table. Some of this information is useful as it is, but some of the data needs to be interpreted to understand. In addition to pulling data directly from syscolumns you can also use the information schema view. If you query from INFORMATION_SCHEMA.COLUMNS you get a lot of data in a useful format, but there is still some missing data. So how can you retrieve additional data about column level properties?
SQL Server User Defined Data Types, Rules and Defaults
SQL Server provides numerous system data types to store dates, character based data, numeric data, etc. However there are some situations when a customized data type is needed for consistency across an application. Some examples could be phone numbers in a specific format, alpha numeric employee ID's, IP addresses, etc. What options are available to store this data in a unique manner?
Identifying Object Dependencies in SQL Server
Is there a way to identify which objects are dependent on other objects within a SQL Server database? I need to modify the table structure to add new columns. However, before making any changes I want to make sure that I understand all the object dependencies. I know this can be done, but is there a better way in SQL Server 2008 to quickly identify all the object dependencies?
SQL Server Database Design with a One To One Relationship
In order to manage any business situation which may arise, a SQL developer needs to know the basic relationship types and their uses. Perhaps the most rare, a one-to-one arrangement offers a few elegant ways to handle some common business needs. In reading this tip, you will become familiar with some practical implementations of a one-to-one relationship. This article assumes that the reader knows how to create relationships, either through T-SQL commands or data modeling tool.
Last 10
Storing E-mail addresses more efficiently in SQL Server - Part 2
In my last tip, I shared some ideas for determining if you should consider breaking up the e-mail addresses you're storing, even putting the domain names in a separate table. I performed storage and timing comparisons for working with 10,000 unique e-mail addresses, but I completely ignored data compression. I wanted to revisit the same test case and apply data compression to the tables and see how that impacted the outcome.
Storing E-mail addresses more efficiently in SQL Server
A lot of people are storing large quantities of e-mail addresses in their systems. As someone who focuses on squeezing every bit of performance out of the various hardware components in a system, when I look at a table full of e-mail addresses, I can't help but think that it is wasteful. In this tip we look at ways to reduce the storage needs for email addresses.
Understanding First Normal Form in SQL Server
I've been asked to be the data modeler for a new application and I've not done this before. I understand the idea of getting the entities and how they connect together from customer interviews, but how do I apply the normalization rules to my data model? Also, why are they important? Check out this tip to learn more.
SQL Server Database Design with a One To One Relationship
In order to manage any business situation which may arise, a SQL developer needs to know the basic relationship types and their uses. Perhaps the most rare, a one-to-one arrangement offers a few elegant ways to handle some common business needs. In reading this tip, you will become familiar with some practical implementations of a one-to-one relationship. This article assumes that the reader knows how to create relationships, either through T-SQL commands or data modeling tool.
SQL Server Data Type Consistency
A short while ago, I discussed the importance of consistent naming conventions (Use consistent SQL Server Naming Conventions). Another similar issue I see creep into environments is the haphazard use of data types. Often it seems abundantly clear that different tables were designed by separate people - to the point that the same data could be represented in two tables with distinct data types. In some cases it even seems random... from one table to the next, you might not know what data type you'll get.
Using Microsoft Quadrant for data analysis and modeling
SQL Server Modeling CTP - Nov 2009 Release 2 which was formerly known as Oslo has been released and it ships with tools like Intellipad and Quadrant and also with languages like "M". In my view, these services and tools are more targeted towards .NET applications, but there are some features which database developers and DBAs can use to their advantage. Quadrant is one of these and in this tip we explore how this tool can be used for modeling and data analysis.
Getting started with SQL Server database diagrams
Trying to visual a database model is sometimes quite difficult. When the model is small it is pretty easy to have an idea what tables reference other tables. But at the data model gets larger it is often difficult to see exactly how the tables relate. In this tip I show you how you can use the built-in SQL Server database diagram tool. I go through some of them messages you may get when getting started and then look at various tasks that you would perform using this free tool.
Identifying Object Dependencies in SQL Server
Is there a way to identify which objects are dependent on other objects within a SQL Server database? I need to modify the table structure to add new columns. However, before making any changes I want to make sure that I understand all the object dependencies. I know this can be done, but is there a better way in SQL Server 2008 to quickly identify all the object dependencies?
Using Computed Columns in SQL Server with Persisted Values
In my databases I had some values that were often calculated while generating several reports. Also there were some columns that were dependent on one or more other columns. As one column was updated triggers were being used to synchronize new values in dependent columns. I was required to provide a more efficient and standard approach to handle these types of scenarios. So how could I get rid of the overhead for calculations at report generation time and how could I avoid the use of triggers for synchronizing updated columns?
SQL Server 2008 Sparse Columns Identifying Columns For Conversion
SQL Server 2008 has introduced a new way to store data for columns that contain excessive NULL values called Sparse Columns. What this means is that when you declare a column as Sparse and any time a NULL value is entered in the column it will not use any space. Is there a way to identify what columns would make a good candidate for this without having to analyze each column individually?
