SQL Server DBA Eve asked

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

Top 20 Ask the conceptual answer: http://blog.sina.com.cn/s/blog_81b2b2a101010ldu.html

Each of the following links is a detailed and specific answer.

1. The difference between char, varchar, nvarchar (including usage and space occupancy), what methods the XML type looks for data for a node, which is efficient, what is different about using stored procedures and querying data using T-SQL;

Http://www.bubuko.com/infodetail-1411174.html

2. What is the system db, what is the role, need not do backup, why; damaged how to do the restore (mainly the master library);

http://blog.csdn.net/travylee/article/details/8646144
3. What operations will be used to tempdb, and if tempdb becomes abnormally large, what the probable cause is, what to do with it;
4. What are the types of index, what are their differences and implementation principles, what are the advantages and disadvantages of the index, how to create an appropriate index for the SQL statement, what to look for when the index is created, how to see if the index you created is used, how to maintain the index, how the index corruption is checked, and how to fix it; T-SQL has a better index present, but the runtime does not use the index for what it might be;
5. Can we build the index on the view, if it can be built, what will be the advantages and disadvantages; What is the difference between the index on the view and the index on the table;
6. Job information which tables we can get, which views the system is running, and how to get the IO, time and other information of a T-SQL statement;
7. Online system, a table has 50 million records, now you want to import 20 million of the records into a table in another server, after the end, you need to delete the 20 million data, you prepare how to deal with the pros and cons is what;
8. The database server reported insufficient disk space, how you will respond, demand recovery as soon as possible;
9. What is the difference between temporary table, table variable, CTE (common table expression), what is different from the storage location, and how to decide which to choose when using;
What are the isolation levels of SQL Server, which is the default level, what are the main types of locks in the database, and how row versioning is implemented;
11. How deadlocks are tracked, how blocking is tracked and looked up, how processing is done when a problematic statement is found, and what events we typically need to track when tracking with profiles;
What are the main Windows logs, SQL Server logs generally keep a few, under what circumstances will generate a new SQL log, the database log recovery model is different, what is the difference between the database log suddenly become large, and you can not shrink, the probable cause is what, how to find the cause, What will be dealt with separately;
13. What is the concept of partitioned tables and partitioned views, and what are the benefits of using them in general?
14. How to compare the differences between the table data of two identical structures, how to fix the table if it is damaged, how to restore the data if there is a problem with the backup file, and how to zero the identity property of a table;
Checkpoint and LazyWriter differences; What is the use of DDL Trigger and DML Trigger, and what is the difference?
Mirroring and logshipping differences and usage scenarios; What is the difference between SQL Server's mirroring and Oracle's similar technology?
Mirroring construction steps, mirroring three modes of difference, mirroring synchronous and asynchronous principle and requirements, build up the mirroring, need to do what the database log processing;
Replication configuration and usage scenarios; What kinds of patterns are there in replication; What is the difference between push and pull? When a replication is built, it will produce a library; When the error is used to check the specific statement of the wrong, clear out a library of replication use what statement, see the synchronization chain information mainly through which tables;
Replication the table at the end of the release can truncate, why; Replication the identity column, how to handle missing field errors, how to handle primary key conflict errors, how to skip the specified error, how the Subscriber table was deleted, how to handle the large-scale change of data; A synchronization chain because one of the table changes the data very large caused the serious delay of the synchronization chain, the need to restore the synchronization chain as soon as possible, how to deal with.
SSB (service Broker) usage scenarios, how to create, what objects will be created, what are the pros and cons, the main way to achieve the message delivery between different servers, which ways can be wrong;
21. How to track changes in database data, their advantages and disadvantages (CDC (change data Capture), CT (change Tracking), trigger, etc.) use;
SQL Tuning step, how to determine the SQL statements exist problems, how to locate the problem, how to solve these problems;
23. Database troubleshooting steps, how to deal with emergency database problems;
24. How to consider and develop a database backup plan; The company requires a very large database or table to be backed up, and requires as little data as possible to lose, you may adopt what method;
25. If you want to do database monitoring, you will be concerned about those indicators (including SQL Server and Windows), how to develop performance baselines, which monitoring software you have used;
26. Database migration steps; rebuild a larger set of test systems (minimum of 10 db instances), if the original DB data volume is not large, but DB is more, the new system data are not needed, how to quickly achieve;
27. Create cluster brief steps, require a minimum number of IP, what services need to be installed, what fixed disks are required, how the raid is set up, and how the disks are divided; The difference between Sqlservercluster and MySQL Cluster and Oracle RAC;
28. If you encounter a stored procedure with unsatisfactory performance and complex code, it is difficult to solve the problem through database tuning, how do you persuade the developer to modify it (perhaps the developer is unwilling to modify it);
29. Have you ever encountered a system failure caused by your mis-operation, how did you handle it, and if not, suppose you mistakenly deleted an important table, what should you do with it?
30. What kind of dba you are going to be, how will you prepare (or what kind of career plan you have, how you intend to spend your DBA career), and if you enter the company, what you want most.

SQL Server DBA Eve asked