For example, the following check constraint ensures that the salary column in the Employees table will support only positive values. There are no NULL marks in the referenced candidate key columns.ДЄ check constraint allows you to define a predicate that a row must meet to be entered into the table or to be modified. Note that NULL marks are allowed in the foreign key columns (mgrid in the last example) even if This constraint is defined on one or more attributes in whatвЂ™s called the referencing table and points to candidate key (primary key or unique constraint) attributes in whatвЂ™s called the referenced table. Q 5 : What is a Foreign Key Constraints ?ДЄ foreign key enforces referential integrity. Also, a unique constraint is not restricted to columns defined as NOT NULL. Unlike with primary keys, you can define multiple unique constraints within the same table. We can allocate or spread filegroups nd1,nd2вЂ¦ndn to multiple drives to increase performance.ДЄ primary key constraint enforces uniqueness of rows and also disallows NULL marks in the constraint attributes.ДЄ unique constraint enforces the uniqueness of rows, allowing you to implement the concept of alternate keys from the relational model in your database.

в– Using multiple filegroups can organize the backup and recovery plan by containing static data in one filegroup and more active data in another filegroup. в– Using multiple filegroups can increase performance by separating heavily used tables or indexes onto different disk subsystems.

This technique can support two main strategies: You can decide which filegroup is marked as the default filegroup. User filegroups contain only secondary data files. You can optionally add secondary data files (which have an. mdf extension) for the database, and the databaseвЂ™s system catalog.

The PRIMARY filegroup contains the primary data file (which has an. Filegroups are your way of controlling the physical locations of your objects.ДЄ database must have at least one filegroup called PRIMARY, and can optionally have other user filegroups as well. The object data will be spread across the files that belong to the target filegroup. Data files are organized in logical groups called filegroups.ДЄ filegroup is the target for creating an object, such as a table or an index. The data files hold object data, and the log files hold information that SQL Server needs to maintain transactions. The database is made up of data files and transaction log files. The msdb database also holds information related to other SQL Server features such as Database Mail, Service Broker, backups, and more. The SQL Server Agent is also the service in charge of replication. SQL Server Agent is in charge of automation, which includes entities such as jobs, schedules, and alerts. в– msdb The msdb database is where a service called SQL Server Agent stores its data. The contains information that was previously in the master database and was split out from the master database to make service pack upgrades easier to install. Note that changes you apply to the model database will not affect existing databases. So if you want certain objects (such as data types) to appear in all new databases that you create, or certain database properties to be configured in a certain way in all new databases, you need to create those objects and configure those properties in the model database. Every new database that you create is initially created as a copy of model. в– Model The model database is used as a template for new databases. Previously in the master database and was split out from the master database to make service pack upgrades easier to install. When you query system objects in a database, they appear to reside in the sys schema of the local database, but in actuality their definitions reside in the Resource database. в– Resource The Resource database is a hidden, read-only database that holds the definitions of all system objects.

SQL Server allows you to create temporary tables for your own use, and the physical location of those temporary tables is tempdb. в– tempdb The tempdb database is where SQL Server stores temporary data such as work tables, sort space, row versioning information, and so on. the system views contain information about system, hardware, indexes, columns, memory etc. в– Master database holds instance-wide metadata information, server configuration, information about all databases in the instance, and initialization information. The following are the types of databases: Q 1 : What are types of system databases:

Adventureworks2012 practice