:::: MENU ::::

Posts Categorized / Data Modeling and Database Design

  • Nov 15 / 2008
  • 0
Data Modeling and Database Design, Data Types, dbDigger

Included columns in SQL Server 2005

Question: What are included columns?


  • A new feature in SQL Server 2005
  • Non key columns included in addition to the index key columns in an index structure.

Explanation: In SQL Server 2005, a nonclustered index can be extended by including nonkey columns in addition to the index key columns. The nonkey columns are stored at the leaf level of the index b-tree.

You can read detail at –

Note: The Question is taken from SQLServercentral.com.

  • Nov 12 / 2008
  • 0
Data Modeling and Database Design, dbDigger

Relational Database VS dimensional database

The typical relational database supports online transaction processing (OLTP). For example, an OLTP database might support bank transactions or store sales. The transactions are immediate and the data is current, with regard to the most recent transaction. The database conforms to a relational model for efficient transaction processing and data integrity. The data is treated as atomic units and there is minimal amount of redundant data.
A data warehouse, on the other hand, generally conforms to a dimensional model, which is more concerned with query efficiency than issues of normalization. Even though a data warehouse is, strictly speaking, a relational database (because it’s stored in a RDBMS), the tables and relationships between those tables are modelled very differently from the tables and relationships defined in the relational database.

  • Nov 05 / 2008
  • 0
Data Modeling and Database Design, dbDigger

Data Integrity for Relational Databases

Data integrity
Data Integrity refers to the accuracy, consistency, and correctness of the data. Rules are set up in the database to help ensure the validity of the data. Data integrity falls into the following categories:

Domain integrity
Domain integrity is also known as column integrity. Domain integrity specifies a set of data values that are valid for a column. This can be defined by the data type, format, data length, nullability, default value, and range of allowable values.

Entity integrity
Entity integrity is also known as table integrity or row integrity. Entity integrity requires that all of the rows in a table have a unique identifier, enforced by either a PRIMARY KEY or UNIQUE constraint.

Referential integrity
Referential integrity ensures that the relationships between tables are maintained. Every FOREIGN KEY value in the referencing table must either be NULL, match a PRIMARY KEY value, or match a UNIQUE key value in an associated referenced table. The referenced row cannot be deleted if a FOREIGN KEY refers to the row, nor can key values be changed if a FOREIGN KEY refers to it. Also, you cannot insert or change records in a referencing table if there is not an associated record in the primary referenced table.

User-defined integrity
User-defined integrity lets you define business rules that do not fall under one of the other integrity categories, including column-level and table-level constraints.

  • Aug 21 / 2008
  • 0
Data Modeling and Database Design, Data Types, dbDigger

Data Type Precedence (T-SQL)

When two expressions of different data types are combined by an operator, the data type precedence rules specify which data type is converted to the other. The data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

This is the precedence order for the Microsoft® SQL Server™ data types:

* datetime (highest)
* smalldatetime
* float
* real
* decimal
* money
* smallmoney
* int
* smallint
* tinyint
* bit
* ntext
* text
* image
* timestamp
* nvarchar
* nchar
* varchar
* char
* varbinary
* binary
* uniqueidentifier (lowest)

Data Type Precedence and Comparison Operators

These data type precedence rules are modified by the comparison operators. Comparison operators always return a Boolean data type. When a comparison operator is applied to expressions of two different data types, one of the expressions must be converted to the data type of the other before the comparison can be made. The data type precedence rules control this internal conversion, with the following exceptions:

* If one of the expressions is an aggregate function that is not in a subquery, and the other expression is not an aggregate, then the data type of the aggregate function is used regardless of the precedence rules.

* If one of the expressions is a column, and the other is not a column or aggregate function, the data type of the column is used regardless of the precedence rules.

  • Aug 11 / 2008
  • 1
Data Modeling and Database Design, DBA best practices, dbDigger, Performance Tunning and Optimization

DBA Best Practices for Database Design

Good database design will be a luxury for a life time. Follow these basic guide lines to give a good database design.

  1. Bad logical database design results in bad physical database design, and generally results in poor database performance. So, if it is your responsibility to design a database from scratch, be sure you take the necessary time and effort to get the logical database design right. Once the logical design is right, then you also need to take the time to get the physical design right.
  2. Normalize your data to ensure best performance.
  3. Take advantage of SQL Server’s built-in referential integrity. You don’t need to write your own.
  4. Always specify the narrowest columns you can. In addition, always choose the smallest data type you need to hold the data you need to store in a column. The narrower the column, the less amount of data SQL Server has to store, and the faster SQL Server is able to read and write data.
  5. Try to avoid performing both OLTP and OLAP transactions within the same database.

Chosen from SQL Server DBA best practices By Brad M.mcGehee

Consult us to explore the Databases. Contact us