• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

MS SQL Interview Questions Answers

Question - 11 : - Why do I sometimes see more than one Binlog_Dump thread on the master after I have restarted the slave?

Answer - 11 : - Binlog_Dump is a continuous process that is handled by the server in the following way: Catch up on the updates. Once there are no more updates left, go into pthread_cond_wait(), from which we can be awakened either by an update or a kill. On wake up, check the reason. If we are not supposed to die, continue the Binlog_dump loop. If there is some fatal error, such as detecting a dead client, terminate the loop. So if the slave thread stops on the slave, the corresponding Binlog_Dump thread on the master will not notice it until after at least one update to the master (or a kill), which is needed to wake it up from pthread_cond_wait(). In the meantime, the slave could have opened another connection, which resulted in another Binlog_Dump thread. The above problem should not be present in Version 3.23.26 and later versions. In Version 3.23.26 we added server-id to each replication server, and now all the old zombie threads are killed on the master when a new replication thread connects from the same slave

Question - 12 : - How MySQL Uses Memory ?

Answer - 12 : - The list below indicates some of the ways that the mysqld server uses memory. Where applicable, the name of the server variable relevant to the memory use is given: The key buffer (variable key_buffer_size) is shared by all threads; Other buffers used by the server are allocated as needed. Each connection uses some thread-specific space: A stack (default 64K, variable thread_stack), a connection buffer (variable net_buffer_length), and a result buffer (variable net_buffer_length). The connection buffer and result buffer are dynamically enlarged up to max_allowed_packet when needed. When a query is running, a copy of the current query string is also allocated. All threads share the same base memory. Only the compressed ISAM / MyISAM tables are memory mapped. This is because the 32-bit memory space of 4GB is not large enough for most big tables. When systems with a 64-bit address space become more common we may add general support for memory mapping. Each request doing a sequential scan over a table allocates a read buffer (variable record_buffer). All joins are done in one pass, and most joins can be done without even using a temporary table. Most temporary tables are memory-based (HEAP) tables. Temporary tables with a big record length (calculated as the sum of all column lengths) or that contain BLOB columns are stored on disk. One problem in MySQL versions before Version 3.23.2 is that if a HEAP table exceeds the size of tmp_table_size, you get the error The table tbl_name is full. In newer versions this is handled by automatically changing the in-memory (HEAP) table to a disk-based (MyISAM) table as necessary. To work around this problem, you can increase the temporary table size by setting the tmp_table_size option to mysqld, or by setting the SQL option SQL_BIG_TABLES in the client program. In MySQL Version 3.20, the maximum size of the temporary table was record_buffer*16, so if you are using this version, you have to increase the value of record_buffer. You can also start mysqld with the --big-tables option to always store temporary tables on disk. However, this will affect the speed of many complicated queries. Most requests doing a sort allocates a sort buffer and 0-2 temporary files depending on the result set size. Almost all parsing and calculating is

Question - 13 : - When Privilege Changes Take Effect?

Answer - 13 : - When mysqld starts, all grant table contents are read into memory and become effective at that point. Modifications to the grant tables that you perform using GRANT, REVOKE, or SET PASSWORD are noticed by the server immediately. If you modify the grant tables manually (using INSERT, UPDATE, etc.), you should execute a FLUSH PRIVILEGES statement or run mysqladmin flush-privileges or mysqladmin reload to tell the server to reload the grant tables. Otherwise your changes will have no effect until you restart the server. If you change the grant tables manually but forget to reload the privileges, you will be wondering why your changes don't seem to make any difference! When the server notices that the grant tables have been changed, existing client connections are affected as follows: Table and column privilege changes take effect with the client's next request. Database privilege changes take effect at the next USE db_name command. Global privilege changes and password changes take effect the next time the client connects.

Question - 14 : - In which order do you perform an upgrade to SQL Server 2005 for replicated databases?

Answer - 14 : - Distributor, Publisher, then Subscriber. You always perform an upgrade in this order: distributor, publisher, subscriber.

Question - 15 : - Explain advantages of MyISAM over InnoDB?

Answer - 15 : - Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.

Question - 16 : - How do I retrieve rows based on datetime values?

Answer - 16 : - SQL Server recognizes date and time data enclosed in single quotes. You can couple date and time values together or use them independently. You can also combine character date formats ('May 15, 2004 4 am'), numeric date formats ('5/15/2004 04:30'), or contiguous string formats ('20040515') with standard <, >, or = operators, as the following example shows: SELECT * FROM orders WHERE OrderDate < 'May 15, 2004'4. How do I retrieve only the date or time portion of the data?

Question - 17 : - How do I insert a value into a datetime column?

Answer - 17 : - To insert values into a datetime column, you need to enclose the values in single quotes, then use one of SQL Server's date formats to supply the date value that you want to insert. For example: DECLARE @MyTable TABLE  (MyDateTime DATETIME) INSERT INTO @MyTable VALUES ('May   15, 2004 11:25am')

Question - 18 : - How would you change a column from VARCHAR(10) to VARCHAR(50)?

Answer - 18 : - ALTER TABLE techpreparation_questions CHANGE techpreparation_content techpreparation_CONTENT VARCHAR(50).

Question - 19 : - Getting Maximum Performance from MySQL ?

Answer - 19 : - Optimization is a complicated task because it ultimately requires understanding of the whole system. While it may be possible to do some local optimizations with small knowledge of your system/application, the more optimal you want your system to become the more you will have to know about it. So this chapter will try to explain and give some examples of different ways to optimize MySQL. But remember that there are always some (increasingly harder) additional ways to make the system even faster.

Question - 20 : - How do you convert a string to UTF-8?

Answer - 20 : - SELECT (techpreparation_question USING utf8);

Computer Contributors

Rajeev Katiyar
Yes Baroda

Share your email for latest updates


Our partners