• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

MS SQL Interview Questions Answers

Question - 61 : - optimization tips?

Answer - 61 : - If you have enough RAM, you could remove all swap devices. Some operating systems will use a swap device in some contexts even if you have free memory. Use the --skip-locking MySQL option to avoid external locking. Note that this will not impact MySQL's functionality as long as you only run one server. Just remember to take down the server (or lock relevant parts) before you run myisamchk. On some system this switch is mandatory because the external locking does not work in any case. The --skip-locking option is on by default when compiling with MIT-pthreads, because flock() isn't fully supported by MIT-pthreads on all platforms. It's also on default for Linux as Linux file locking are not yet safe. The only case when you can't use --skip-locking is if you run multiple MySQL servers (not clients) on the same data, or run myisamchk on the table without first flushing and locking the mysqld server tables first. You can still use LOCK TABLES/UNLOCK TABLES even if you are using --skip-locking 12.2.1 How Compiling and Linking Affects the Speed of MySQL Most of the following tests are done on Linux with the MySQL benchmarks, but they should give some indication for other operating systems and workloads. You get the fastest executable when you link with -static. On Linux, you will get the fastest code when compiling with pgcc and -O6. To compile `sql_yacc.cc' with these options, you need about 200M memory because gcc/pgcc needs a lot of memory to make all functions inline. You should also set CXX=gcc when configuring MySQL to avoid inclusion of the libstdc++ library (it is not needed). Note that with some versions of pgcc, the resulting code will only run on true Pentium processors, even if you use the compiler option that you want the resulting code to be working on all x586 type processors (like AMD). By just using a better compiler and/or better compiler options you can get a 10-30 % speed increase in your application. This is particularly important if you compile the SQL server yourself! We have tested both the Cygnus CodeFusion and Fujitsu compilers, but when we tested them, neither was sufficiently bug free to allow MySQL to be compiled with optimizations on. When you compile MySQL you should only include support for the character sets that you are going

Question - 62 : - What is the recommended way to send mail from SQLAgent in SQL Server 2005?

Answer - 62 : - Database Mail You can use either Database Mail or SQLMail with SQL Agent in SQL Server 2005. However since SQLMail will be removed, it is recommended that you use Database Mail.

Question - 63 : - What are a  primary and foreign key?

Answer - 63 : -                                            A primary key is the field(s) in a table that uniquely defines the row in the table; the values in the primary key are always unique. A foreign key is a constraint that establishes a relationship between two tables. This relationship typically involves the primary key field(s) from one table with an adjoining set of field(s) in another table (although it could be the same table). The adjoining field(s) is the foreign key.                                                                       

Question - 64 : - If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table?

Answer - 64 : - CHAR(3), since MySQL automatically adjusted the data type.

Question - 65 : - Tuning Server Parameters ?

Answer - 65 : - You can get the default buffer sizes used by the mysqld server with this command: shell> mysqld --help This command produces a list of all mysqld options and configurable variables. The output includes the default values and looks something like this: Possible variables for option --set-variable (-O) are: back_log current value: 5 bdb_cache_size current value: 1048540 binlog_cache_size current_value: 32768 connect_timeout current value: 5 delayed_insert_timeout current value: 300 delayed_insert_limit current value: 100 delayed_queue_size current value: 1000 flush_time current value: 0 interactive_timeout current value: 28800 join_buffer_size current value: 131072 key_buffer_size current value: 1048540 lower_case_table_names current value: 0 long_query_time current value: 10 max_allowed_packet current value: 1048576 max_binlog_cache_size current_value: 4294967295 max_connections current value: 100 max_connect_errors current value: 10 max_delayed_threads current value: 20 max_heap_table_size current value: 16777216 max_join_size current value: 4294967295 max_sort_length current value: 1024 max_tmp_tables current value: 32 max_write_lock_count current value: 4294967295 myisam_sort_buffer_size current value: 8388608 net_buffer_length current value: 16384 net_retry_count current value: 10 net_read_timeout current value: 30 net_write_timeout current value: 60 query_buffer_size current value: 0 record_buffer current value: 131072 slow_launch_time current value: 2 sort_buffer current value: 2097116 table_cache current value: 64 thread_concurrency current value: 10 tmp_table_size current value: 1048576 thread_stack current value: 131072 wait_timeout current value: 28800 If there is a mysqld server currently running, you can see what values it actually is using for the variables by executing this command: shell> mysqladmin variables You can find a full description for all variables in the SHOW VARIABLES section in this manual. You can also see some s

Question - 66 : - Design Choices ?

Answer - 66 : - MySQL keeps row data and index data in separate files. Many (almost all) other databases mix row and index data in the same file. We believe that the MySQL choice is better for a very wide range of modern systems. Another way to store the row data is to keep the information for each column in a separate area (examples are SDBM and Focus). This will cause a performance hit for every query that accesses more than one column. Because this degenerates so quickly when more than one column is accessed, we believe that this model is not good for general purpose databases. The more common case is that the index and data are stored together (like in Oracle/Sybase et al). In this case you will find the row information at the leaf page of the index. The good thing with this layout is that it, in many cases, depending on how well the index is cached, saves a disk read. The bad things with this layout are: Table scanning is much slower because you have to read through the indexes to get at the data. You can't use only the index table to retrieve data for a query. You lose a lot of space, as you must duplicate indexes from the nodes (as you can't store the row in the nodes). Deletes will degenerate the table over time (as indexes in nodes are usually not updated on delete). It's harder to cache ONLY the index data. MySQL Design Limitations/Tradeoffs Because MySQL uses extremely fast table locking (multiple readers / single writers) the biggest remaining problem is a mix of a steady stream of inserts and slow selects on the same table. We believe that for a huge number of systems the extremely fast performance in other cases make this choice a win. This case is usually also possible to solve by having multiple copies of the table, but it takes more effort and hardware. We are also working on some extensions to solve this problem for some common application niches.

Computer Contributors

Rajeev Katiyar
Yes Baroda

Share your email for latest updates


Our partners