:::: MENU ::::

Posts Categorized / Data Modeling and Database Design

  • Feb 07 / 2012
  • 0
Data Modeling and Database Design, DBA Interview questions, dbDigger, Identity Columns, SQL Server Error messages, T-SQL Interview Questions

Multiple identity columns specified for table %’. Only one identity column per table is allowed

Following error message would be generated if more than one identity column is tried to define in a table.
Multiple identity columns specified for table ‘%’. Only one identity column per table is allowed
SQL Server does not allow to have more than one identity column in a single table. Consider following design for generating error message.

 CREATE TABLE identTest  
 (ID1 SMALLINT IDENTITY(1,1),   
 ID2 SMALLINT IDENTITY(1,1))  
 GO  

There would be hardly any scenario when such a design is required. However such functionality may be achieved by a couple of work around

  • Use trigger to insert values in second column
  • Configure second column as computed column and reflect values from identity column
  • Dec 05 / 2011
  • 0
Data Modeling and Database Design, dbDigger, Monitoring and Analysis, T-SQL Scripts

Analyze all foreign keys in your SQL Server database

Creating foreign keys ensure data integrity across different tables in a database. However only creating these is not enough. Keeping in view the requirement we have to configure proper impact on foreign key value when its primary key is affected by update or delete operation.
SQL Server provides four rules for implementing the effect of primary key on foreign key value in case of update or delete operation. These are

  • No Action
  • Cascade
  • Set NULL
  • Set Default

In my related article on MSSQLTips, I have discussed the configuration and effect of these four rules. The emerged problem is to analyze the existing foreign keys for their configured rules for update and delete operations. It is required to generate a list of all foreign keys along with their associated columns and rules for update and delete operations. Below is the script that would provide a brief information to start with

 
SELECT fkey.name AS FKeyName,  
 object_name(fkey.parent_object_id) AS FKeyTable,  
 cols.name AS FKeyColumn,  
 object_name(fkey.referenced_object_id) AS PKeyTable,  
 colsa.name AS PKeyColumn,  
 CASE is_disabled WHEN 1 THEN 'YES'  
 WHEN 0 THEN 'No' END AS IsDisabled,  
 delete_referential_action_desc AS DeleteRule,  
 update_referential_action_desc AS UpdateRule  
 FROM sys.foreign_key_columns FKeyC  
 INNER JOIN sys.foreign_keys fkey  
 ON fkeyc.parent_object_id = fkey.parent_object_id  
 AND fkeyc.referenced_object_id = fkey.referenced_object_id  
 INNER JOIN sys.all_columns cols  
 ON fkeyc.parent_object_id = cols.object_id  
 AND parent_column_id = cols.column_id  
 INNER JOIN sys.all_columns colsa  
 ON fkeyc.referenced_object_id = colsa.object_id  
 AND parent_column_id = colsa.column_id  
 ORDER BY is_disabled DESC, delete_referential_action_desc, update_referential_action_desc  

You may also add create_date and modify_date in the select list for creation and modification date of respective foreign key. Once the list is available, analyze the requirement and configured rule along with disabled foreign keys.

Analyze all foreign keys in your SQL Server database

  • Sep 06 / 2011
  • 0
Further utilizing the computed columns in your database design
Data Modeling and Database Design, dbDigger, Publications of Atif Shehzad on MSSQLTips.com

Further utilizing the computed columns in your database design

Computed columns are handy feature of SQL Server database design. Few months back i write an article related to basics of computed columns in SQL Server. Some readers furthers discussed about more flexibility in different scenarios. So click here to read my new article related to using computed columns with little enhancements for different requirements.

  • Apr 23 / 2011
  • 0
Constraints and Keys, Data Modeling and Database Design, dbDigger, Publications of Atif Shehzad on MSSQLTips.com

Understanding the update and delete rules for SQL Server Foreign Key Relationships

Foreign Key relationships are widely used in SQL Server database design. They provide numerous benefits. However there are some associated issues while applying update/delete statements on parent table .i.e. table with referenced primary key. SQL Server provides rules to manage the effect of operation on child records. It is important to know the use and effects of these rules. Read my recently published article  about Understanding the update and delete rules for SQL Server Foreign Key Relationships.

  • Jun 18 / 2010
  • 0
Data Modeling and Database Design, dbDigger, Performance Tunning and Optimization

Better approach for storing images in SQL Server

Storing images in database rather than storing their path (on disk) is mostly avoided due to performance perspectives. The obvious benefit is that images are also backed up along with database and there is no need to take care of images backups separately.
On the other hand utilizing images path and getting images from disk storage system is optimal. However these are design considerations that appear rare as compared to take over of existing database.

You take over an existing database and find that instead of path of disk images are stored in tables. This situation would demand some extra consideration while working with images stored in binary format inside the tables.

Some examples of these limitations are

  • Images may not be included in distinct list
  • Images can not be used while using UNION, however you may use these with UNION ALL
  • Len() function may not be used with images, you may use DataLength() to get bytes used by image

My Suggestion:

If insert and update operations for images are rare in your scenario then it would be better option to store images in binary format when inserted along with inserting path for image on disk. Later image may be retrieved from disk through path stored in database. It would make sure that images are also backed up along with database.
  • May 07 / 2010
  • 0
Data Modeling and Database Design, dbDigger, T-SQL Interview Questions

Is it possible to have NULL value in foreign key?

It is interesting question that either a foreign key column can have NULL value or not? The answer is Yes. A foreign key column may have NULL value. It simple means that you can record with NULL value in foreign key column is not associated with any record of primary key (being referenced) table.
It may easily be bound to have no NULL value in foreign key column. Simply alter it with NOT NULL constraint and it would prevent NULL value like any other column.

Consult us to explore the Databases. Contact us