forum.venkateswarlu.net
      Register      Login
what are the steps we need follow for database design in sql server?

3 replies to this topic

Subramaniam R #1
Member
152 Points
 Posted on 16 Jul 2013 03:18 PM IST what are the steps we need follow for database design in sql server?what are the major steps we need to follow for database design and optimization the query 
SQL Server     850 views     Reply to this topic
Jahnavi N #2
Member
164 Points
 Replied on 17 Jul 2013 09:39 PM IST
There are two components to designing a database: logical and physical.

Logical database design involves modeling your business requirements and data using database components such as tables and constraints without regard for how or where the data will be physically stored.

Physical database design involves mapping the logical design onto physical media, taking advantage of the hardware and software features available that allow the data to be physically accessed and maintained as quickly as possible, and indexing.

It is important to correctly design the database to model your business requirements and to take advantage of hardware and software features early in the development cycle of a database application because it is difficult to make changes to these components later.

Logical Database Design
Using Microsoft SQL Server effectively begins with normalized database design. Normalization is the process of removing redundancies from the data.

For example, when converting from an indexed sequence access method (ISAM) style application, normalization often involves breaking data in a single file into two or more logical tables in a relational database. Transact-SQL queries then recombine the table data by using relational join operations.

By avoiding the need to update the same data in multiple places, normalization improves the efficiency of an application and reduces the opportunities for introducing errors due to inconsistent data.

However, there are tradeoffs to normalization. A database that is used primarily for decision support (as opposed to update-intensive transaction processing) may not have redundant updates and may be more understandable and efficient for queries if the design is not fully normalized. Nevertheless, unnormalized data is a more common design problem in database applications than over-normalized data.

Starting with a normalized design and then selectively denormalizing tables for specific reasons is a good strategy.

Whatever the database design, you should take advantage of these features in SQL Server to automatically maintain the integrity of your data:
  • CHECK constraints ensure that column values are valid. 
  • DEFAULT and NOT NULL constraints avoid the complexities (and opportunities for hidden application bugs) caused by missing column values. 
  • PRIMARY KEY and UNIQUE constraints enforce the uniqueness of rows (and implicitly create an index to do so). 
  • FOREIGN KEY constraints ensure that rows in dependent tables always have a matching master record. 
  • IDENTITY columns efficiently generate unique row identifiers. 
  • TIMESTAMP columns ensure efficient concurrency checking between multiple-user updates. 
  • User-defined data types ensure consistency of column definitions across the database. 

 
Reply to this topic
Subramaniam R #3
Member
152 Points
 Replied on 18 Jul 2013 10:48 AM IST

Thanks a lot

 
Reply to this topic
Venkat Ramaraju #4
Member
12 Points
 Replied on 22 Jul 2013 05:13 PM IST

Hi, Try This

Non-clustered index
should be created on columns of table based on query which is running



Following priority order
should be followed when any index is created a) WHERE clause, b) JOIN clause,
c) ORDER BY clause, d) SELECT clause



Do not to use Views or
replace views with original source table



Triggers should not be
used if possible, incorporate the logic of trigger in stored procedure



Remove any adhoc queries
and use Stored Procedure instead



If possible move the
logic of UDF to SP as well



Remove * from SELECT and
use columns which are only necessary in code



Remove any unnecessary
joins from table



If there is cursor used
in query, see if there is any other way to avoid the usage of this (either by
SELECT … INTO or INSERT … INTO, etc)



 

 
Reply to this topic