SQL Database Interview

First, the SQL theory question 1. What does a trigger do?

A: A trigger is a special stored procedure that is executed primarily by triggering an event. It can enforce constraints to maintain the integrity and consistency of data, and can track operations within the database without permitting unauthorized updates and changes. Can be associated with a level operation. For example, a trigger on a table contains data operations on another table, and that action causes the table trigger to be triggered.

2. What is a stored procedure? Using what to invoke?

A: A stored procedure is a precompiled SQL statement, and the advantage is that it allows for a modular design, meaning that it is created once and can be called more than once in the program. If an operation needs to execute multiple times of SQL, using a stored procedure is faster than simply SQL statement execution. You can invoke a stored procedure with a command object.

3. What is the role of the index? And what are the pros and cons of it?

A: The index is a special query table, the database search engine can use it to speed up the retrieval of data. It is similar to the catalogue of real-life books, and you can find the data you want without having to query the entire contents of the book. Indexes can be unique, and creating an index allows you to specify a single column or multiple columns. The disadvantage is that it slows down data entry and also increases the size of the database.

4. What is a memory leak?

A: Generally what we call a memory leak refers to a leak in heap memory. Heap memory is a program that is allocated to it from the heap, arbitrarily sized, and then shown to free memory after it is exhausted. When an application creates an object with the keyword new and so on, it allocates a piece of memory from the heap, the program calls free or the delete frees the memory after use, or the memory is not used, we say that the memory is compromised.

5. Maintain database integrity and consistency, do you prefer to use triggers or self-write business logic? Why?

A: I do this, as far as possible to use constraints, such as check, primary key, foreign keys, non-empty fields to constrain, so that the most efficient and most convenient. The second is the use of triggers, which ensures that the data is intact and consistent regardless of the business system access to the database. The final consideration is self-writing business logic, but this is cumbersome, programming complex, inefficient.

6. What is a transaction? What is a lock?

A: A transaction is a grouping of SQL statements that are bound together as a logical unit of work, and if any one statement fails then the entire operation fails, and the operation is rolled back to the pre-operation state, or there is a node on it. A transaction can be used to ensure that it is either executed or not executed. To consider a set of statements as transactions, you need to pass acid testing, that is, atomicity, consistency, isolation, and persistence.

Lock: In so the DBMS, the lock is the key to implement the transaction, the lock can guarantee the integrity and concurrency of the transaction. Like a real-life lock, it enables certain data owners to be unable to use certain data or structures for a certain period of time. Of course, locks are also divided into levels.

7. What do you call a view? What is a cursor?

A: A view is a virtual table that has the same functionality as a physical table. You can add, change, check, manipulate, and attempt a view that is usually a subset of rows or columns that have a table or multiple tables. Changes to the view do not affect the base table. It makes it easier for us to get data, compared to multiple table queries.

Cursor: is a query out of the result set as a unit to effectively handle. A cursor can be set to a specific row in that cell, retrieving one or more rows from the current row of the result set. You can make modifications to the current row of the result set. Cursors are generally not used, but cursors are important when you need to process the data individually.

8. Can you give me a brief account of some of the database objects used in SQL Server?

A: tables, indexes, views, stored procedures, triggers, user-defined functions, database diagrams, full-text indexes.

What does 9.NULL mean?

Null (NULL) this value represents unknown (unknown): It does not represent "" (empty string). Suppose you have ansi_nulls in your SQL Server database, and of course, by default, any comparison to NULL will produce a null value. You cannot compare any value to a unknown value and logically want to get an answer. You must use the IS null operator.

10. What is an index? What types of indexes are in SQL Server?

Simply put, an index is a data structure that is used to quickly access data in a database table or view. In SQL Server, they come in two forms: clustered and nonclustered indexes. The clustered index holds the data at the leaf level of the index. This means that regardless of which (or what) fields in the table are in the clustered index, the fields are saved sequentially in the table. Because of this sort, there is only one clustered index per table. A nonclustered index has a row identifier at the leaf level of the index. This row identifier is a pointer to the data on disk. It allows more than one nonclustered index per table.

11. What is a primary key? What is a foreign key?

A primary key is a field (or fields) in a table that defines only the rows in the table, and the values in the primary key are always unique. A foreign key is a constraint that is used to establish a relationship between two tables. This relationship typically involves a series of connected fields in a table where the primary key field is associated with another table (although it may be the same table). Then these connected fields are foreign keys.

12. What is a trigger? What are the different types of triggers for SQL Server?

A trigger is a private type of stored procedure that is bundled into a table or view of SQL Server. In SQL Server 2000, there are instead-of and after two types of triggers. instead-of triggers are stored procedures that perform statements on a table in lieu of the data manipulation language (manipulation LANGUAGE,DML) statement. For example, if I had a instead-of-update trigger for TableA and an UPDATE statement for the table, the code in the Instead-of-update trigger would execute instead of the UPDATE statement I executed. After triggers are not executed until the DML statement is used in the database. These types of triggers are useful for monitoring data changes that occur in database tables.

13. How do you ensure that a TableB table with the name Fld1 field has only those values in the Fld1 field, and that the values are in the Fld1 field of the table named TableA?

There are two possible answers to this relationship-related question.

The first answer is to use a foreign key restriction. Foreign key restrictions are used to maintain referential integrity. It is used to ensure that the fields in the table only hold values that are defined in another field that is already in a different (or identical) table. This field is the candidate key (usually the primary key of the other table).

Another answer is a trigger. Triggers can be used to ensure that the same effect is achieved in another way, but it is very difficult to set up and maintain, and the performance is generally poor. For this reason, Microsoft recommends that developers use foreign key restrictions instead of triggers to maintain referential integrity.

14. What are the performance considerations for having too many indexes on an online transaction processing table that has been put into use?

The more indexes a table has, the more time it takes for the database engine to update, insert, or delete data because the index must be maintained when data manipulation occurs.

15. What can you use to ensure that the fields in the table only accept values in a specific range?

This question can be answered in a variety of ways, but only one answer is the check limit, which is defined in the database table and used to restrict the value of the input column.

Triggers can also be used to limit the values that fields in a database table can accept, but this approach requires that triggers be defined in the table, which may affect performance in some cases. Therefore, Microsoft recommends using check restrictions instead of other ways to limit domain integrity.

16. Experience with stored procedures.

The return parameter is always returned by the stored procedure, which is used to indicate whether the stored procedure succeeded or failed. The return parameter is always the int data type.

The output parameter is explicitly required by the developer to specify that it can return other types of data, such as character and numeric values. (There are some limitations to the data types that can be used as output parameters.) You can use more than one output parameter in a stored procedure and you can only use a return parameter.

17. What is a correlated subquery? How do I use these queries?

A related subquery is a special type of query that contains subqueries. The subquery contained in the query will actually request the value of the external query, thus creating a loop-like condition.

18. What is a SQL injection attack?

The so-called SQL injection attack is an attacker inserting a SQL command into a Web form's input domain or a page request query string, tricking the server into executing a malicious SQL command. In some forms, user-entered content is used directly to construct (or influence) dynamic SQL commands, or as input parameters to stored procedures, which are particularly susceptible to SQL injection attacks. Common SQL injection attack process classes such as:

⑴ an ASP. NET Web application has a login page that controls whether the user has access to the app and asks the user to enter a name and password.

The content entered on the ⑵ login page will be used directly to construct a dynamic SQL command, or directly as a parameter to a stored procedure. Here is an example of an ASP. NET application construct query:

System.Text.StringBuilder query = new System.Text.StringBuilder (
"SELECT * from Users WHERE login = '")
. Append (Txtlogin.text). Append ("' and Password= '")
. Append (txtPassword.Text). Append ("'");

⑶ the attacker enters "' or ' 1 ' = ' 1" in the user name and password input box.

After the ⑷ user input is submitted to the server, the server runs the above ASP. NET code constructs the SQL command to query the user, but because the attacker enters a very special content, the resulting SQL command becomes: SELECT * from Users WHERE login = ' or ' 1 ' = ' 1 ' and password = ' or ' 1 ' = ' 1 '.

The ⑸ server executes a query or stored procedure that compares the identity information entered by the user with the identity stored in the server.

⑹ because the SQL command has actually been modified by an injected attack, it has not been able to actually authenticate the user, so the system is incorrectly authorized to the attacker.

If an attacker knew that the app would use the input in the form to authenticate the query directly, he would try to enter some special SQL string tampering query to change its original functionality and spoof the system to grant access.

The system environment is different, the attacker may cause different damage, which is mainly determined by the security permissions of the application to access the database. If the user's account has administrator or other advanced privileges, an attacker could perform various actions on the database's tables, including adding, deleting, or updating data, and possibly even deleting the table directly.

19. How do I prevent SQL injection attacks?

Fortunately, it is not particularly difficult to prevent an ASP. NET application from being hacked into by a SQL injection attack, as long as all the input is filtered before the SQL command is constructed using the form's input content. Filtering input can be done in a variety of ways.

⑴ for dynamically constructing SQL queries, you can use the following techniques:

First: Replace single quotation marks, that is, all individual occurrences of single quotation marks are changed to two single quotes, to prevent the attacker to modify the meaning of the SQL command. Take another look at the previous example, "SELECT * from users WHERE login = ' or ' 1 ' = ' 1 ' and password = ' ' or ' 1 ' = ' 1 '" will obviously get with "select * from Users WHERE login = ' or ' 1 ' = ' 1 ' and password = ' or ' 1 ' = ' 1 ' "different results.

Second: Delete all hyphens in user input to prevent attackers from constructing classes such as "SELECT * from users where login = ' mas '--and Password = '", because the second half of this type of query has been commented out, is no longer valid, attacks If you know a legitimate user login name, you do not need to know the user's password to gain access to the smooth.

Third: Restrict permissions for the database account that is used to execute the query. Perform query, insert, UPDATE, and delete operations with different user accounts. Because of the isolation of operations that can be performed by different accounts, the place used to execute the SELECT command is prevented from executing the INSERT, UPDATE, or delete commands.

⑵ uses a stored procedure to execute all queries. The way SQL parameters are passed prevents attackers from using single quotes and hyphens to enforce attacks. In addition, it allows database permissions to be restricted to only certain stored procedure executions, and all user input must follow the security context of the stored procedure being called, making it difficult to inject attacks again.

⑶ limit the length of the form or query string input. If a user's login name is at most 10 characters, do not endorse more than 10 characters entered in the form, which greatly increases the difficulty of an attacker inserting harmful code into a SQL command.

⑷ checks the legality of user input and is confident that the input contains only valid data. Data inspection should be performed on both the client and server side-the reason for server-side validation is to compensate for the fragile security of the client authentication mechanism.

At the client, it is entirely possible for an attacker to obtain the source code of the Web page, modify the script that validates the legitimacy (or delete the script directly), and then submit the illegal content to the server through the modified form. Therefore, the only way to ensure that the validation operation is actually performed is to perform validation on the server side. You can use many of the built-in validation objects, such as RegularExpressionValidator, which automatically generate client-side scripts for validation, and you can also insert a method call from the server. If you cannot find a ready-made validation object, you can create one by CustomValidator yourself.

⑸ saves data such as user login name, password, and so on. Encrypt the data entered by the user and compare it to the data saved in the database, which is equivalent to "disinfect" the data entered by the user, and the data entered by the user no longer has any special meaning to the database, thus preventing the attacker from injecting SQL commands. The System.Web.Security.FormsAuthentication class has a hashpasswordforstoringinconfigfile that is ideal for disinfecting input data.

⑹ checks the number of records returned by the query that extracted the data. If a program requires only one record to be returned, but the actual returned record exceeds a row, it is treated as an error.

20. How do I move the database?

1) Detach the database, 2) Move the data file, log file to another path, and 3) attach on another computer.

21. What are the default system databases?

1) master database (master); 2) tempdb database (temporary); 3) model database (template); 4) msdb database (scheduled tasks);

22. What files will be generated by default for creating a database?

1) main file (. mdf), 2) log file (. ldf), no secondary file (. ndf).

23. When creating a database, can you separate the data files from the log files?

Can be separated, play the role of optimization. Put the data file in the high-speed reading and writing area, and put the log file in the low speed reading area.

24. What is an index covering query?

Index covering query refers to data that can be obtained only through an index, without touching the table.

25. What is a transaction?

A transaction is a series of operations performed as a logical unit, and a logical work cell must have four properties, called ACID (atomicity, consistency, isolation, and persistence) properties, in order to be a transaction:
1) atomicity
The transaction must be an atomic unit of work, either all executed or not executed for its data modification.
2) Consistency
When a transaction is complete, you must keep all data in a consistent state. In a related database, all rules must be applied to transaction modifications to maintain the integrity of all data. At the end of the transaction, all internal data structures, such as B-tree indexes or doubly linked lists, must be correct.
3) Isolation
Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transaction. The state in which the data is located when the transaction is viewing the data, either when another concurrent transaction modifies its state or after another transaction modifies it, and the transaction does not view the data in the middle state. This is called serializable because it is able to reload the starting data and replay a series of transactions so that the state at the end of the data is the same state as the original transaction execution.
4) Durability
After the transaction is complete, its effect on the system is permanent. This modification will persist even if a system failure occurs.

SQL Database Interview