I. SQL Server Management Studio

Task #1: Using Solution Explorer1 We use this to manage a list of work items in a project. To create a new solution and project: First, connect to the Server. On the File menu, point to New, and then click Project. In the New Project dialog box, select SQL Server Scripts. Optionally modify the project name in the Name text box. Optionally modify the path for the solution in the Location text box. Optionally modify the solution name in the Solution Name text box. Click OK. Create Connections, Queries, and change properties

(Task 1: 5 points) Complete Task #2 through Task #4 while placing your sql files in Queries. Show in a screenshot that your Homework 2 project in Solution Explorer contains three sql files in Solution Explorer. Refer to the example below.

Task #2: Creating a ModelDB

When a CREATE DATABASE statement is issued, the first part of the database is created by copying the contents in the model database. The rest of the new database is then filled with empty pages. If you modify the model database, all databases created afterward will inherit those changes. For example, you could set permissions or database options, or add objects such as tables, functions, or stored procedures.²

Create a new query file named ModelDB within the project you created in Task #1.

Create the following database.

CREATE DATABASE MyDatabase1

GO

Refresh Databases to confirm the creation

-Change to model database.

USE model

GO

Now the database to use is changed to model.

Run the following query to create DatabaseLog_test table in the model database.

CREATE TABLE dbo.DatabaseLog_test ( EventId INT

EventName SYSNAME

EventType SYSNAME

EventText NVARCHAR(3000)

)

GO

Next create the second database.

CREATE DATABASE MyDatabase2

GO

(Task 2: 5 points) Show in a screenshot that DatabaseLog_test table is created in MyDatabase2, and not in MyDatabase1.

After this task, delete DatabaseLog_test table in the model database.

Task #3: Creating a TempDB

For a nice overview of temp tables, go to the following link. Review it carefully to answer the questions below (This is the same in SQL Server 2005 and later).

https://www.sqlshack.com/overview-and-performance-tips-of-temp-tables-in-sql-server/

Create a new query file named TempDB within the project you have created in Task #1.

/* to view the temporary database file in the system */ USE tempdb

SELECT * FROM sys.database_files

/* to create local temp tables. #means temp table.*/

/* available only to the current connection for the user */ CREATE TABLE #TempTableA (colA CHAR(8000)) CREATE TABLE #TempTableB (colB CHAR(8000))

CREATE TABLE #TempTableC (colC CHAR(8000))

/* to create global temp tables */

/* available to any user by any connection */ CREATE TABLE ##GlobTempTable (colD CHAR(8000))

/* to view the existence of the temp tables that were just created */ SELECT * FROM sys.objects

SELECT * FROM sys.objects WHERE name like '#%'

/* to insert values into the table */

DECLARE @i INT

SET @i = 1

WHILE @i < 5000

BEGIN

INSERT #TempTableA values ('Database Security is Fun!!' )

SET @i = @i +1

END

/* to view the inserted entries*/

SELECT * FROM #TempTableA

/* to drop temp tables */

DROP TABLE #TempTableA

DROP TABLE #TempTableB

DROP TABLE #TempTableC

DROP TABLE ##GlobTempTable

(Task 3: 4 points) Create the three temp tables listed above and show their existence using the SELECT command above. Attach a screenshot. Use the SELECT command below.

SELECT * FROM sys.objects WHERE name like '#%'

(Question: 1 points) When do we use temporary Tables? List two scenarios. When do we use table variable over temp table?