• +91 9971497814
  • info@interviewmaterial.com

Computer Interview Questions Answers

MS SQL Interview Questions Answers

Question - 31 : -  How does SQL Server store the datetime data type?

Answer - 31 : - SQL Server uses 8 bytes to store the datetime data type. The first 4 bytes make up an integer value that represents the number of days since January 1, 1900. The second 4 bytes are an integer value that represents the number of milliseconds since midnight.

Question - 32 : - Explain federated tables. ?

Answer - 32 : - Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.

Question - 33 : - Speed of UPDATE Queries ?

Answer - 33 : - Update queries are optimized as a SELECT query with the additional overhead of a write. The speed of the write is dependent on the size of the data that is being updated and the number of indexes that are updated. Indexes that are not changed will not be updated. Also, another way to get fast updates is to delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time if you lock the table. Note that, with dynamic record format, updating a record to a longer total length may split the record. So if you do this often, it is very important to OPTIMIZE TABLE sometimes.

Question - 34 : - Speed of SELECT Queries ?

Answer - 34 : - In general, when you want to make a slow SELECT ... WHERE faster, the first thing to check is whether or not you can add an index. All references between different tables should usually be done with indexes. You can use the EXPLAIN command to determine which indexes are used for a SELECT. Some general tips: To help MySQL optimize queries better, run myisamchk --analyze on a table after it has been loaded with relevant data. This updates a value for each index part that indicates the average number of rows that have the same value. (For unique indexes, this is always 1, of course.). MySQL will use this to decide which index to choose when you connect two tables with 'a non-constant expression'. You can check the result from the analyze run by doing SHOW INDEX FROM table_name and examining the Cardinality column. To sort an index and data according to an index, use myisamchk --sort-index --sort-records=1 (if you want to sort on index 1). If you have a unique index from which you want to read all records in order according to that index, this is a good way to make that faster. Note, however, that this sorting isn't written optimally and will take a long time for a large table!

Question - 35 : - How can I use replication to provide redundancy/high availability?

Answer - 35 : - With the currently available features, you would have to set up a master and a slave (or several slaves), and write a script that will monitor the master to see if it is up, and instruct your applications and the slaves of the master change in case of failure. Some suggestions: To tell a slave to change the master use the CHANGE MASTER TO command. A good way to keep your applications informed where the master is by having a dynamic DNS entry for the master. With bind you can use nsupdate to dynamically update your DNS. You should run your slaves with the log-bin option and without log-slave-updates. This way the slave will be ready to become a master as soon as you issue STOP SLAVE; RESET MASTER, and CHANGE MASTER TO on the other slaves. It will also help you catch spurious updates that may happen because of misconfiguration of the slave (ideally, you want to configure access rights so that no client can update the slave, except for the slave thread) combined with the bugs in your client programs (they should never update the slave directly). We are currently working on intergrating an automatic master election system into MySQL, but until it is ready, you will have to create your own monitoring tools.

Question - 36 : - But what if you really want to store the timestamp data, such as the publication date of the article?

Answer - 36 : - Create two columns of type TIMESTAMP and use the second one for your real data.

Question - 37 : - What is a correlated sub-query? How can these queries be useful?

Answer - 37 : - The more seasoned developer will be able to accurately describe this type of query. A correlated sub-query is a special type of query containing a sub-query. The sub-query contained in the query actually requests values from the outside query, creating a situation similar to a loop. You can find a more detailed description as to how these special types of queries work in this article.

Question - 38 : - What the Privilege System Does?

Answer - 38 : - The primary function of the MySQL privilege system is to authenticate a user connecting from a given host, and to associate that user with privileges on a database such as select, insert, update and delete. Additional functionality includes the ability to have an anonymous user and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations. MySQL User Names and Passwords There are several distinctions between the way user names and passwords are used by MySQL and the way they are used by Unix or Windows: User names, as used by MySQL for authentication purposes, have nothing to do with Unix user names (login names) or Windows user names. Most MySQL clients by default try to log in using the current Unix user name as the MySQL user name, but that is for convenience only. Client programs allow a different name to be specified with the -u or --user options. This means that you can't make a database secure in any way unless all MySQL user names have passwords. Anyone may attempt to connect to the server using any name, and they will succeed if they specify any name that doesn't have a password. MySQL user names can be up to 16 characters long; Unix user names typically are limited to 8 characters. MySQL passwords have nothing to do with Unix passwords. There is no necessary connection between the password you use to log in to a Unix machine and the password you use to access a database on that machine. MySQL encrypts passwords using a different algorithm than the one used during the Unix login process. Note that even if the password is stored 'scrambled', and knowing your 'scrambled' password is enough to be able to connect to the MySQL server! Connecting to the MySQL Server MySQL client programs generally require that you specify connection parameters when you want to access a MySQL server: the host you want to connect to, your user name, and your password. For example, the mysql client can be started like this (optional arguments are enclosed between `[' and `]'): shell> mysql [-h host_name] [-u user_name] [-pyour_pass] Alternate forms of the -h, -u, and -p options are --host=host_name, --user=user_name, and --password=your_pass. Note that there is no space between -p or --password= and the password following

Question - 39 : - Explain the difference between mysql and mysql interfaces in PHP?

Answer - 39 : - mysqli is the object-oriented version of mysql library functions.

Question - 40 : - How are ENUMs and SETs represented internally?

Answer - 40 : - As unique integers representing the powers of two, due to storage optimizations.

Computer Contributors

Rajeev Katiyar
Yes Baroda

Share your email for latest updates


Our partners