Designing a SQL Server Database Calvin P Schrotenboer

Designing a SQL Server Database Calvin P. Schrotenboer gotdotnet@lycos. com

Database Design n All tables should model only a single real world object Every table should have one or more rows which uniquely distinguish each row from all other rows (ie. A Primary Key) To the extent possible it is best to avoid allowing NULL values

Database Normalization n Never allow multi-valued columns as they cannot be efficiently indexed Also avoid multiple columns for the identical attribute as they likewise are difficult to search In both cases, the solution is a second table linked with a Foreign Key

Entity (Table) Relationships n n n One-to-One Relationships are rare and can generally best be handled through a View One-to-Many Relationships are by far the most common type of relationship and are normally handled with a Foreign Key Many-to-Many Relationships are generally handled with a Linking Table which contains Foreign Keys from both of the Primary Tables

Files & Filegroups n Every SQL Server database must have a Primary datafile which has a. mdf extension n n Secondary datafiles are optional and have an extension of. ndf n n By default the name of this file is DBName_Data. mdf The purpose of secondary datafiles is to provide higher performance by distributing the DB over multiple hard disks Every SQL Server DB has at least one transaction log file which is used to restore the DB up to the point of failure

Files & Filegroups n n Filegroups are a logical container for physical files No file or filegroup can ever belong to more than one DB No file can ever belong to more than one filegroup Transaction logs do not belong to any filegroup

Files and Filegroups n n n At any given time, only one filegroup can be the default filegroup Any new DB objects (tables, stored procedures, views, etc. ) for which a filegroup is not specified are made part of the default filegroup For higher performance it is important to place non-system files in a different filegroup from system files

Filegroup Recommendations n n Leave files which contain system objects in the primary filegroup and put files which contain user objects in other filegroups Spread these filegroups onto different physical hard disks for higher performance Place tables and their indexes in different filegroups (and consequently on different disks) Place the transaction logs on a different hard disk

Transaction Logs n n Transaction logs can be used to roll forward in the case of a DB restore At a checkpoint, SQL Server causes all transaction log records and modified data to be written to disk Roll forward is only necessary from the last checkpoint Transaction logs can be used to roll back if a user issues a Rollback command

SQL Server Security n n SQL Server supports two Authentication Modes: Windows only & Windows + SQL Server Authentication involves establishing the identity of the user through the presentation of credentials (Login ID & password)

SQL Server Security n n n Authorization is separate from authentication (authorization uses the User. ID) User. IDs must be linked to a Login account to be effective All access permissions are regulated via the User account