SQL Database Management

Database Management II

Download: SQL Database
Convert an insert statement for one of your tables into a transaction

Like the script on page 505 (Figure 16-2), write a script that performs a test before committing a transaction for deleting a transaction.

Add two database roles to your database and describe what they do.

Hint: Under Roles, right click add database role, fill out name in ‘General’ and then pick a database object under dbo schema. Then select Securables and search for objects in your database. And choose permissions.

I created a backup role with permissions to backup the database and database log. This role is defined as the allowing members of the db_backupoperator fixed database role to back up the database.

I created the datawriter role to allow grant permissions to the Appointments table to alter, control, delete, insert and update. The db_datawriter is a fixed database role that can add, delete, or change data in a user table.

Create two users. Assign membership to one of the users and assign Select and Update privileges and nothing else to the other. Test out the permissions. Submit to me the usernames and passwords.

Using the AP database, Write a script that uses the XML document shown below to update the information in the Vendors table.
<ContactUpdates>
<Contact VendorID=”2″>
<LastName>Stanley</LastName>
<FirstName>Henry</FirstName>
</Contact>
<Contact VendorID=”12″>
<LastName>Ono</LastName>
<FirstName>Santa</FirstName>
</Contact>
</ContactUpdates>

Do the same thing but for a table in your midterm database. You will have to write your XML as well, but you can use a similar format.

Write a script that returns a result set that contains all the data stored in the following XML document:
<ContactUpdates>
<Contact VendorID=”2″>
<LastName>Stanley</LastName>
<FirstName>Henry</FirstName>
</Contact>
<Contact VendorID=”12″>
<LastName>Ono</LastName>
<FirstName>Santa</FirstName>
</Contact>
</ContactUpdates>

A good example of this can be also be found on Microsoft’s Technet website — https://msdn.microsoft.com/en-us/library/ms187367.aspx

You will need to use the following stored procedures: sp_Xml_PrepareDocument, sp_Xml_RemoveDocument

What are SQL Server locks? What are deadlocks?
Server Locks are an integral part of SQL Server and are needed for data integrity within the system. The locking of objects stops all other processes from being able to change a running object until its process is finished and the lock is removed. The primary locks are Shared(S) and Exclusive(X). A deadlock occurs when two processes are competing for exclusive access, but are not able to achieve it, because the other process is preventing it. The only way to stop the deadlock if for one of the processes or locks to be terminated. SQL Server will automatically detect a deadlock and kill one of the processes, which is known as the victim.

What are some pros and cons of using Blobs?
Binary Large Objects (BLOBs) have both their advantages and disadvantages. The pros of storing a pointer to a binary file are that there is no limit to the size of the BLOB and the file system provides fast access. The Cons are that the BLOB is not backed up with the database and is controlled by network security, not the database security. The Pros of storing a BLOB in a varbinary(max) datatype is that the BLOB is backed up by the database and database security can be used to control access. The Cons are the BLOB must be smaller than 2GB and the database access is not as fast as a file system. Finally, when using Filestream Storage with BLOBS the Pros are the BLOB can be larger than 2GB, access is as fast a file system, and it can be backed up and used with database security. The Cons are it requires more time and work to setup.