What stateless and stateful load balancing?

Sql server Interview Questions :What is the stateless and stateful load balancing?
Stateless load balancer
Stateless load balancer uses the hashing algorithm. It takes packet from the client and selects fields from the packet to be hashed. For instance from each client it can take IP address and port number and hash them in to an integer between 1 to number of servers. You can see from the figure we have four servers. So the load balancer takes the IP address and port and hashes them with a number between 1 to 4. Depending on the number the client is directed to the server. For instance client A is directed to server 2, client b to server 1 and so on. Once the client is connected to a server it is always redirected to the same server.
Figure: - Stateless load balancing
Advantage of stateless load balancing is that it’s simple. The biggest disadvantage is that it treats all clients equally and connects one client to one server always. That is if client A is connects to server 2 it will always connect to server 2. This is irrespective of how many times client A sends request. So if client A sends 100 request and client B sends 10 requests even then client will be sent to server 2 for all the 100 request and client B to server 1 for all requests.

Stateful load balancer
In stateful balancing the load balancer looks at each session and assigns it to the appropriate server based on load. In order that the load balancer can track each session it needs to know when the session starts and when it ends.

Figure: - Stateful load balancing

You can see from the figure for every session the load balancer redirects the request to different servers.

SQL Server interview questions

SQL Server interview questions
1)Explain architecture of Notification Services?
2)What is the stateless and stateful load balancing?
3)What do you mean about Fragmentation in SQL Server?
4)Explain two types of indexes?
5)What do you mean by State publisher, distributor and subscriber in “Replication”?
6)Different types of relationship existing in database designing?
7)How do we connect to SQL SERVER, which namespace do we use?
8)What is meant by Referential Integrity in SQL Server?
9)How to convert a Table Data in to XML format in SQL Server?
10)State the term SQL injection in SQL Server?
11)What is ACID fundamental? What are transactions in SQL SERVER?
12)What are the different types of replication supported by SQL SERVER?
13)How you can find the age of an Employee whose age is greater than 30?
14)How to back-up SQL Server Database and Restore Database?
15)Describe DBCC?
16)What is Normalization and its different forms?
17)What are the differences between INNER JOIN, LEFT JOIN and RIGHT JOIN in SQL Server?
18)What is the difference between unique key and primary key?
19)Select record with the second lowest value from the below customer table?

Command is used to create a table by copying the structure of another table?

Command is used to create a table by copying the structure of another table?
Command: CREATE TABLE .. AS SELECT command
Explanation :
To copy only the structure, the WHERE clause of the SELECT command should
contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE
WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition
will be copied to the new table.

SQL Aptitude interview questions for practice

Q. What is the value of ‘comm’ & ‘sal’ after executing the following query if the initial
value of ‘sal’ is 10000?
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;
sal = 11000, comm = 1000 .

Q. Why does the following comm& give a compilation error?
DROP TABLE &TABLE_NAME;
Variable names should start with an alphabet. Here the table name starts with an '&' symbol.


Q. What is the advantage of specifying WITH GRANT OPTION in the GRANT comm& in sql?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.

Q. What is the use of DESC in SQL?
Answer :
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation :
The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

Q. What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP comm&, a parent table can be dropped even when a child table exists.

What is the use of the DROP option in the ALTER TABLE comm&? in SQL

It is used to drop constraints specified on the table.

Difference between TRUNCATE & DELETE commands in SQL- Interview questions

Difference between TRUNCATE & DELETE commands in SQL- Interview questions
TRUNCATE is a DDL command whereas DELETE is a DML command. Hence
DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back.
WHERE clause can be used with DELETE & not with TRUNCATE.

What are hints in SQL?

What are hints in sql?
Hints are options or strategies specified for enforcement by the SQL Server 2005 query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query.
There three types of hints
•Join Hints
•Query Hints
•Table Hints