Question 1: Why do group by and Order BY slow down queries?

A: Group by and Order BY operations usually require the creation of a temporary table to process the results of the query, so a large number of query results can seriously affect performance.

Question 2: What is the difference between DELETE, TRUNCate, and DROP?

A: The Delete command is used to Delete all or part of a table. After the Delete command is executed, the user needs to commit a commmit or rollback transaction to Delete or undo the deletion. The delete command triggers all delete triggers on this table. Truncate deletes all data in a table. This operation cannot be rolled back and triggers no triggers on the table. Truncate is faster and takes less space than DELETE. The Drop command drops a table from the database. All rows, indexes, and permissions are dropped. All DML triggers are not triggered and the command cannot be rolled back.

Question 3: Describe the database design process.

Answer: Database design is divided into the following five stages: Demand analysis: mainly collect user information needs and processing needs accurately, and collect the results of the sorting and analysis, the formation of requirements. Conceptual structure design: to synthesize, induce, and abstract user requirements to form a conceptual model independent of specific DBMS (generally ER model). Logical structure design: transform the conceptual model of conceptual structure design into the data model supported by a specific DBMS, establish and optimize the logical schema of the database, and design the external schema for various users and applications at the same time. Physical structure design: select the physical structure for the designed logical model, including the storage structure and access method, and establish the physical model of the database. Implementation and maintenance: implementation is the use of DLL language to establish database mode, the actual data into the database, the establishment of a real database. The maintenance phase is the evaluation, adjustment, and modification of the running database.

Question 4: Can I insert a record without specifying a field name?

Answer: The correct number of VALUES must be given regardless of which INSERT syntax is used. If you do not provide field names, you must provide a value for each field, or an error message will be generated. To omit fields from an INSERT operation, these fields must meet certain conditions: the column is defined to allow null values; Or the default value is given when the table is defined. If no value is given, the default value is used.

Question 5: The difference and applicability of local and global indexes.

A: For local indexes, each table partition corresponds to an index partition. When the table partition changes, the database automatically maintains the indexes. For global indexes, partitioning is optional, and the partitioning of the index may not correspond to table partitioning. When performing maintenance on a partition, it usually results in INVALDED global indexes, which must be rebuilt after the operation is complete.

Question 6: What do transactions have to do with locks?

A: You can use a variety of mechanisms to ensure data integrity, such as constraints, triggers, and the transactions and locks described in this chapter. Transactions and locks are closely related. A transaction consists of a series of operations, all of which either succeed or fail. A transaction mechanism manages multiple transactions to ensure the consistency of transactions. A transaction uses locks to protect specified resources and prevent other users from modifying data in another transaction that has not yet completed.

Question 7. What is your understanding of indexes?

A: An index is a list of keywords in several data rows. When querying data, you can use keywords in the index to quickly locate the data block where the record to be accessed resides. In this way, the I/O number of data block reads is greatly reduced and the performance is greatly improved.

Question 8: What is your understanding of the business?

A: A transaction begins with a COMMIT, ROLLBACK, connection to the database, or the start of the first executable SQL statement, and ends with a COMMIT, ROLLBACK, or exit from the database. If a DDL statement is included in a transaction, a COMMIT statement is implicitly executed before and after the DDL statement to start or end the transaction. If a transaction has to be cancelled before it is committed due to some failure or a change of heart by the user, the database is restored to the state it was in before these statements and procedures were executed. The ROLLBACK statement allows you to undo or ROLLBACK a transaction at any time before the COMMIT command. You can roll back an entire transaction or part of a transaction, but you cannot roll back a committed transaction. The ROLLBACK command to ROLLBACK some transactions is ROLLBACK to savepoint storage point name. A storage point is a mark placed by the user in a transaction to indicate a location that can be rolled back. The SAVEPOINT is inserted by placing a SAVEPOINT command in the transaction. The syntax of this command is: SAVEPOINT store roll name. If the store is not called in the ROLLBACK statement, the entire transaction is rolled back.

Question 9: How do I set the size of network packets?

A: Generally, you can use the following methods to set the size of network packets. Set this value to a larger value if your application often performs bulk replication or sends or receives large amounts of text and image data. If your application is receiving and sending small amounts of information, you can set it to 512 bytes.

Question 10: What is your understanding of cursors and the classification of cursors?

A: A cursor is a pointer in the result set data that stores the result of each record as it traverses the result set. Cursors are divided into explicit cursors and implicit cursors.

Question 11: Can COMMIT be used in triggers, and why?

A: Transaction control statements such as COMMIT cannot be used in triggers. Because triggers are triggered by transactions and if you have a transaction control statement it affects the transaction that triggered it. That is, uncommitted statements that have completed before the statement that triggered it are affected. This will affect the consistency of the data.

Question 12: Archived vs. non-archived? Why can archive mode be restored to any time?

A: Archive is to copy the redo log file to the specified location by copying the operating system file before the redo log file is overwritten. The collection of saved redo log files is called archived redo log files, and the replication process is called archiving. In ARCHIVELOG mode, the database performs archiving of reworked archival documents. In NOARCHIVELOG mode, redo log files are not archived. Since the non-archived mode is not saved before overwrite, the redo log files of the database before a certain time are lost and cannot be recovered before overwrite. In archive mode, redo log files are available at any time and can be recovered at any time.

Question 13. How do I increase the frequency of log switching?

A: The ARCHIVE_LAG_TARGET parameter controls the log switchover interval in seconds. By reducing the time interval, the switching frequency of logs can be improved. SQL> ALTER SYSTEM SET ARCHIVE_LAG_TARGET=50 SCOPE=both; Using the preceding command, you can switch logs every 50 seconds.

Question 14: There are three columns A, B and C in the table

If column A is greater than column B, select column A, otherwise select column B. If column B is greater than column C, select column B, otherwise select column C. A: This can be done with the following SQL statement:

Q15: For a date determination SQL statement, please fetch all records with date (SendTime field) in table TB_SEND for that day? (The SendTime field is datetime and contains the date and time)?

Select * from TB where datediff(dd,SendTime, getDate ())=0

Question 16: There is a table with three fields: Chinese, math and English. Among them, there are 3 records respectively indicating Chinese 70 points, mathematics 80 points, English 58 points, please use a SQL statement to query these three records and display them according to the following conditions (and write your thoughts) : greater than or equal to 80 indicates excellent, greater than or equal to 60 indicates passing, less than 60 points indicates failing.

Question 17: A table Id has multiple records. Look up all the records of this Id and show how many records there are.

A: This can be done with the following SQL statement:

Question 18. What is the difference between a stored procedure and a custom function?

A: Stored procedures are powerful. They can perform a series of database operations, such as modifying tables. They can also be created as stored procedures that are automatically run when SQL Server is started. While custom functions, user-defined functions cannot be used to perform a set of operations that modify the state of the global database. Stored procedures that can use nondeterministic functions. Custom functions. Non-deterministic functions are not allowed to be built into user-defined function bodies. Stored procedure that returns a recordset. A custom function that can return a table variable or have any number of output parameters. Stored procedure whose return value cannot be referenced directly and must be called separately. A custom function whose return value can be directly referenced, i.e. the select * from function.

Question 19. What does a view do?

A: The main functions of a database view are:

  1. The database view hides the complexity of the data.
  2. Database views are useful for controlling user access to certain columns in a table.
  3. Database views simplify user queries. A view is a virtual table whose contents are defined by the query. Just like a real table, a view contains a series of named columns and rows of data.

However, the view does not exist in the database as a set of stored data values. The row and column data comes from the table referenced by the query that defines the view and is generated dynamically when the view is referenced. Views act like filters for the underlying tables they reference. Filters that define views can come from one or more tables in the current or other databases, or from other views. Distributed queries can also be used to define views that use data from multiple heterogeneous sources. This can be useful if you have several different servers that store data in different parts of the organization and you need to combine data from similar structures on those servers. There are no restrictions on querying through views, and few restrictions on modifying data through them.

Question 20: Is more indexes better?

A: Proper indexes can speed up queries, but more indexes is not better. When an insert statement is executed, the database indexes the newly inserted record. So too many indexes can slow the insert operation. In principle, only the query fields are indexed. Question 21. What does the STR function do when it encounters decimals? Answer: When using the STR function, if the number is a decimal, only the integer part of the number is returned when converting to a string data type. If the number after the decimal point is greater than or equal to 5, the integer part is returned by rounding.

Question 22: Does the custom function support output parameters?

A: A custom function can take zero or more input arguments and return a numeric value or a table, but the custom function does not support output arguments.

Question 23: Why delete unused triggers in a timely manner?

A: After a trigger is defined, each time a trigger event is executed, the trigger is activated and the statements in the trigger are executed. If the requirements change and the trigger is not changed or deleted accordingly, the trigger will still execute the old statement, affecting the integrity of the new data. Therefore, delete triggers that are no longer in use.

Question 24. What is a unique index?

Answer: Unique indexes ensure that index columns do not contain duplicate values. In the case of multi-column unique indexes, this index ensures that each combination of values in the index column is unique. For example, if the unique index full_name is created on the combination of the LAST_name, first_NAME, and MIDDLE_INITIAL columns, no two people in the table can have the same full name. Both clustered and non-clustered indexes can be unique. Therefore, it is possible to create a unique clustered index and multiple unique non-clustered indexes on the same table as long as the data in the columns is unique. Specifying a unique index makes sense only if uniqueness is a feature of the data itself. If uniqueness must be enforced to ensure data integrity, create a UNIQUE or PRIMARY KEY constraint on the column rather than a UNIQUE index.

Question 25. Why is UNION ALL faster than UNION?

A: Because duplicate records need to be deleted when UNION is used, but not when UNION ALL is used. So be sure to use UNION ALL when you know that the query that requires a UNION is unlikely to have duplicate data.