MCTS Fix

Ace up your sleeve with MCTS

Tag Archives: SQL server

How to create a relationship between two tables in Microsoft SQL Server 2005

Take the following steps to create a relationship:

   1. Run SQL Server Management Studio from Start – Programs – Microsoft SQL Server 2005 – SQL Server Management Studio.
   2. In the Connect to Server dialog box, click the Connect button.
   3. In the Microsoft SQL Server Management Studio window, double-click the Databases folder.
   4. In the Microsoft SQL Server Management Studio window, double-click the database.
   5. In the Microsoft SQL Server Management Studio window, double-click the Tables folder.
   6. Right-click the table and click Modify.
   7. The table will open in edit mode. Select the column for which a foreign key is to be assigned.
   8. Go to Table Designer – Relationships.
   9. The Foreign Key Relationships dialog box will open.
  10. In the Foreign Key Relationships dialog box, click the Add button to create the foreign key. Click the Browse button in the Tables and Columns Specification property.
  11. The Tables and Columns dialog box will open.
  12. Select the primary key table from the Primary key table dropdown list.
  13. Select the primary key column from the Primary key table dropdown list.
  14. Select the foreign key column from the Foreign key table dropdown list. Click the OK button.
  15. In the Foreign Key Relationships dialog box, click the Close button.
  16. In the Microsoft SQL Server Management Studio window, go to File – Save Customer (Customer is the name of the table.) to save the changes.

Share

Create a Computed Column using SQL Server Management Studio:

Take the following steps to create a computed column using SQL Server Management Studio:

  1. Run SQL Server Management Studio from Start > Programs > Microsoft SQL Server 2005 > SQL Server Management Studio.
  2. In the Connect to Server window, click the Connect button.
  3. In the Microsoft SQL Server Management Studio window, double-click the Databases folder.
  4. In the Microsoft SQL Server Management Studio window, double-click the required database. Here, the Axle database is being used.
  5. In the Microsoft SQL Server Management Studio window, double-click the Tables folder.
  6. In the Microsoft SQL Server Management Studio window, right-click the required table and click Modify.
  7. Type the name of the new column. Here, Amount is the new column.
  8. In Column Properties, expand Computed Column Specification. In the Formula box, type the formula. Here, the Price column is multiplied by the Quantity column.
  9. Go to the File menu and click Save Products to save the modifications in the Products table.
Share

Buffer Management in Microsoft SQL Server:

Buffer management in Microsoft SQL server:

SQL Server buffer pages in RAM to minimize disc I/O. Any 8 KB page can be buffered in-memory, and the set of all pages currently buffered is called the buffer cache. The amount of memory available to SQL Server decides how many pages will be cached in memory. The buffer cache is managed by the Buffer Manager. Either reading from or writing to any page copies it to the buffer cache. Subsequent reads or writes are redirected to the in-memory copy, rather than the on-disc version. The page is updated on the disc by the Buffer Manager only if the in-memory cache has not been referenced for some time. While writing pages back to disc, asynchronous I/O is used whereby the I/O operation is done in a background thread so that other operations do not have to wait for the I/O operation to complete. Each page is written along with its checksum when it is written. When reading the page back, its checksum is computed again and matched with the stored version to ensure the page has not been damaged or tampered with in the meantime.

Share