1. Deeply understand the data files and log files of SQL Server; 2. Master data dictionary; 3. Learn about temporary files of SQL Server. SQL Server 2008 Compiler: SQL Server Management Studio 1) Understand the main data file MDF, secondary data file NDF and log file LDF of SQL.
2) Use database files to create A new database (two students A and B completed together), the experiment description is as follows: Student A and B are respectively creating A database named by their own names, the database needs to have at least one table containing data, check the corresponding data files (.mdf,.LDF, etc.). Using the database files of the other party, student A and student B respectively import the database created by the other party into their OWN SQL Server instance (SSMS).
3) Observe and record the generation of secondary data files
Temporary file When SQL statements are used for query, the query result is stored in a temporary file whose extension name is TMP. When the query, this file stores the query results, when the database is closed, the temporary file will be automatically deleted, so when we query, is not aware of the existence of the file. Observe the process of creating, writing, and deleting temporary files.
Mysql > select test_log. LDF, test_log. LDF, test_log. LDF, test_log. LDF, test_log. LDF, test_log. LDF, test_log. LDF, test_log. LDF, test_log. LDF, test_log. LDF, test_log. LDF Create database test again. The system displays test database already exists.
6) Insert 2 rows of the same data, why the failure?
1. Students can team up in groups of three or five, discuss the solution and finish it on the computer independently; 2. Record the experiment process, and analyze and explain all activities (including wrong activities).
Solution: 1) How do I copy related files of the database to the other party without shutting down the SQL Server instance (SSMS)? A: Select your own database on the SSMS, right-click task-separate, find the MDF and LDF files in the file, and copy the FILES to the computer of the other party on the USB flash drive to restore the database
2) For “use the other party’s complete database file to import the other party’s database in my OWN SQL Server instance”, how to achieve this through SSMS graphical mode and SQL statement mode? A: SSMS graphical mode: Restore data and log files in the database file in object Explorer. For details, see Experiment 1.1 to restore data and log files based on integrity backup.
3. Create school database and student table 1) After performing corresponding operations according to experimental requirements (the data file school. MDF is set to a fixed size of 3MB, and the maximum allowed growth is 4MB), enter the first SQL statement and enter the result as shown in the figure below: declare @i int declare @j int set @i = 1 set @j = 1 while @i <= 20000 begin insert into [school].[dbo].[student] values (@i, ‘Sheldon’, @j, ‘Computer Science’, ‘Class 2’) set @i = @i + 1 set @j = @j + 1 end
Displays an error
2) Enter: declare @i int declare @j int set @i = 20001 set @j = 5 while @i <= 30000 begin insert into [school].[dbo].[student] Values (@i, ‘Katherine’, @j, ‘Information Management’, ‘Class 3’) set @i = @i + 1 set @j = @j + 1 end
Problem: The temporary file directory does not change after the SQL statement is run and SSMS is closed. This may be because the hidden file is not displayed. Changes are observed after the display.
Consider: a temporary file, the equivalent of memory to a buffer area on disk that temporarily stores the user’s input and output in memory and then moves the data to hard disk after it is officially saved to save time for changes.
5. Create table student, select sys.master_files, locate test. MDF and test_log. LDF files, and delete them manually
Question: Does it make sense to have two identical records in a data table and the necessity of using primary keys?
Thinking: Two identical records in the data table is a kind of redundancy, which is not expected to be seen in the same table, because it will cause a lot of space waste, and at the same time, we can not determine whether the same records belong to different, which is easy to cause errors. Due to the uniqueness of the primary key, we can use the primary key as an index to search for information corresponding to different users or situations