SQL Server DBA 30's Q & A (1st ~ 10 questions)

First, I declare that this is my answer. It is not an official answer. Do not blindly pursue it because I think this question is representative and many people want to know it, by the way, you can also score yourself. Because I usually use sqlserver2000, most of my answers are based on sql2000.

Original post http://www.cnblogs.com/fygh/archive/2011/10/18/2216166.html

The problem is as follows:

1. Differences between char, varchar, and nvarchar (including usage and space occupation); methods for finding data of a node in xml format and high efficiency;

What is the difference between the process and the use of T-SQL to query data;

A: char is a fixed-length character type. If the length is not enough, spaces are automatically added to the backend. varchar is a variable-length character type. The length depends on the data stored; nvarchar can be understood as a variable-length character type of unicode;

Xml fields have never been used, because xml is not indexed;

 

2. What are the functions of the system DB? Backup is not required. Why? How to restore the system database (mainly the master database) if the database is damaged );

A: The master database stores system-level information, such as the databases and accounts of the database instances, and must be backed up;

Model template. Each time a database is created, it is created based on the structure of the database. If you change the database, we recommend that you back up the database;

Msdb stores information such as scheduled tasks and jobs and needs to be backed up; otherwise, jobs and backup plans will be lost;

Temporary data generated by tempdb users during SQL server operations depends on this database. The most common is temporary tables, which cannot be backed up;

Resource is a new read-only database added to sqlserver2005. It stores the System objects of SQL Server, such as sys. objects. We recommend that you back up the database. It is required when the disk is damaged;

The restoration of the master depends on sqlcmd. It is estimated that few people often do this, so we should check the information at this time;

 

3. What operations will be used in TempDB? What are the possible causes of an exception in TempDB;

A: tempdb is used for temporary data generated during SQL server running. The most common is the record set to be returned when an SQL script is executed;

The exception increases because the record set returned by the executed operation is too large. Find out the statement optimization, reduce the data range, or operate the data in batches;

 

4. What types of indexes are available, what are their differences and implementation principles, what are the advantages and disadvantages of indexes, how to create appropriate indexes for SQL statements, and what are the requirements for Index creation?

Note, how to check whether the index you created is used; how to maintain the index; how to check if the index is damaged, how to fix it; T-SQL has a better index but runs

The index is not used. What is the possible cause;

A: clustered indexes are non-clustered indexes. Only one clustered index can exist. Multiple non-clustered indexes are supported. data depends on Clustered indexes for storage. If no clustered index exists, data is a disordered heap;

Advantages: suitable indexes can effectively improve query efficiency. Disadvantages: excessive indexes increase index maintenance costs during insert, update, and delete operations to reduce concurrency;

Generally, index creation depends on the where and order by keywords. The execution plan shows whether the index is used;

No index damage has been encountered. If the index is damaged, re-build it;

If indexes are not used, the index may contain too many fragments and can be fragmented. If not, you can add a forced index with (index name ))

5. Can we create an index on a view? What are the advantages and disadvantages of an index on a view? What is the difference between an index on a view and an index on a table;

A: I have an index view, but I never use it because the view is defined as a virtual table. If you create an index for it, you must have some demanding conditions and it is not easy to maintain, so it is never used;

 

6. What tables can we get the Job information, which views can be obtained by running statements, and how to get the IO and Time information of a T-SQL statement;

A: sql2000 uses the master. dbo. sysjobs. dbo. sysprocesses can be viewed in combination with dbcc inputbuffer. Generally, blocking is useful (for blocking locating, see my other blog, blocking locating );

IO. In sql2000, I use profiler to view reads, duration, and sql2005 with dynamic views (dmv );

 

7. In the online system, a table has 50 million records. Now you need to import 20 million records to a table on the other server. After importing the 20 million records, you need to delete the data.

Besides, what are your advantages and disadvantages;

A: I have previously written a blog post. Here, I will answer a question from DBA thirty (question 7th)

 

8. The database server reports that the disk space is insufficient. How can you handle this problem and request recovery as soon as possible;

A: If the log file is too large and the log is backed up, sqlserver will automatically truncate the log;

If the data file is too large, move it to a hard disk that is large enough and try to split the database. Generally, a database of GB should be similar. If it is too large, it is not very convenient to back up and restore everything, the splitting scheme cannot be generalized;

 

9. What are the differences and relationships between temporary tables, table variables, and CTE (common table expressions), storage locations, and how to decide which one to choose for use;

A: The temporary table is saved in tempdb,

The table variables are stored in the memory. The difference between the table variables and the temporary table is that one is saved in tempdb and the other is saved in memory,

Cte is newly added by sqlserver2005. It uses a record set as a variable. The difference between cte and table variables is that insert data is not required, so it should be more efficient than table variables;

It should be best to use cte only once. If you want to use the same record set multiple times, I personally think it is still a table variable,

If you still need to use the truncate statement to clear temporary data, it is best to use a temporary table because table variables do not support truncate. For details, refer to my other nature: why can't SQL Server truncate table variables?

 

 

10. What are the isolation levels and default levels of SQLServer, what are the major lock types of databases, and how row version control is implemented;

A: isolation level: read uncommitted, equivalent to adding with (nolock) to each select statement, which will generate dirty reads. You can set this option to increase the concurrency;

Read committed does not allow dirty data. The default level is repeatable read, which locks the select dataset. serializable is equivalent to adding with (holdlock) to the select table and never used it;

Query set transaction isolation level in books online.

 

 

When I first started reading this question, I felt that it was often used. It should be easy, but I didn't know how to say it when I really wanted to answer it, in the end, I spent a lot of time checking some information, which is a further consolidation of my own knowledge. Some answers may not be accurate. If any of you have a high opinion, you may wish to give us some advice. You can also learn from each other and make progress together. From my recruitment experience, it is quite good to answer 1st and 4th questions for asp.net development. From this point, we can see that SQL is a weakness for the majority of programmers.