<html> <head> <link runat="server" rel="shortcut icon" href="~/favicon.ico" type="image/x-icon" /> <link runat="server" rel="icon" href="~/favicon.ico" type="image/ico" /> </head> <body> </body> </html> http://pramadha.com
What is a deadlock in SQL Server?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
SQL Server detects deadlocks and terminates one user’s process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.
SQL Server detects the situation after four denials and refuses further shared locks.
A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
What are “GRANT” and “REVOKE’ statements in SQL?
GRANT statement grants rights to the objects (table). While revoke does the vice-versa of it, it removes rights from the object.
How do you find the Second highest Salary?
We can write a sub-query to achieve the result
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)
The first sub-query in the WHERE clause will return the MAX SALARY in the table, the main query SELECT’s the MAX SALARY from the results which doesn’t have the highest SALARY.
What is the Use of @@ROWCOUNT?
Returns the number of rows affected by the Query.
UPDATE authors SET au_lname = ‘Jones’
WHERE au_id = ‘999-888-7777’
IF @@ROWCOUNT = 0
print ‘Warning: No rows were updated’
What is Difference between Commit and Rollback when Used in Transactions?
The usual structure of the TRANSACTION is as follows:
COMMIT TRANSACTION or ROLLBACK TRANSACTION
When Commit is executed, every statement between BEGIN and COMMIT becomes persistent to database. When Rollback is executed, every statement between BEGIN and ROLLBACK are reverted to the state when BEGIN was executed.
What is Candidate, alternate and composite keys in SQL?
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.
What are Isolation Levels?
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed.
Following are the other isolation levels (in the ascending order of isolation):
- Read Uncommitted
- Read Committed
- Repeatable Read
One can set isolation levels at connection level using the following sql
SET TRANSACTION ISOLATION LEVEL
What is Lock Escalation?
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).
Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks.
Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it’s dynamically managed by SQL Server.
What is PRIMARY KEY Constraint in SQL Server?
Primary key constraint is used to uniquely identify each record in database table. It won’t allow repetition or duplication of data. Each table is having only one primary key constraint and it contains only unique values. Primary key constraint doesn’t accept null values (Read more Here)
What is UNIQUE KEY Constraint in SQL Server?
Unique key constraint is same as Primary key Constraint it doesn’t allow duplication or repetition of data in column and we can uniquely identify records in table. The main difference is Primary Key constraint won’t allow null values but unique key constraint allows null values. We have a chance to define only one primary key on table but we can define many unique key constraints on table (Read more Here)
What is FOREIGN KEY Constraint in SQL Server?
A Foreign key in one table point to primary key in another table. The foreign key constraint is used to prevent the actions that would destroy the links between two tables (Read more Here)
What is CHECK Constraint in SQL Server?
The check constraint is used to limit the value range that can be placed in a column. If we set Check constraint property on particular column the values in particular column must satisfy condition set by check constraint (Read more Here)
What is NOT NULL Constraint in SQL Server?
If we set Not Null constraint property on any column in table that column won’t accept NULL or Empty values. If you want enforce any column not to accept NULL or empty value just set Not NULL Constraint property for that particular column (Read more Here)
What is the Difference between a HAVING clause and a WHERE clause in SQL Server?
They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query, whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query
What is Connection Pooling and why it is used?
To minimize the cost of opening and closing connections, ADO.NET uses an optimization technique called connection pooling.
The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.
What are the Authentication Modes in SQL Server?
Windows mode and Mixed Mode – SQL & Windows.
What is the difference between UNION and UNION ALL in SQL Server?
The Union operator is used to return the result set of two or more select statements. It will return only distinct values from the tables (Read more Here).
Union ALL operator is used to return the result set of two or more select statements including duplication values (Read more Here)
What is Identity in SQL Server?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed
What is an Index in SQL Server?
An index is a physical structure containing pointers to the data. Indexes are created in an existing table to locate the data more efficiently and quickly (Read more Here)
What is the Difference between Clustered and a Non-clustered Index in SQL Server?
The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db. (Read more Here)
What is Collation?
Collation refers to a set of rules that determine how the data is sorted and compared (Read more Here)
What are Primary Keys and Foreign Keys in SQL Server?
Primary key is used to identify records in table uniquely and it won’t allow null values and duplication of data. Only one primary key is allowed for each table ()
Foreign key in one table points to primary key in another table. Foreign key is used to prevent the actions that would destroy the link between two tables.
What’s the Difference between a Primary Key and a Unique Key?
Primary key is used to identify records in table uniquely and it won’t allow null values and duplication of data. Only one primary key is allowed for each table.
Unique key is same as primary key only the difference is unique key allow null values.
What are the Different Types of Triggers?
After triggers and Instead of triggers
After triggers are the triggers which are raised before the transaction committed or postback.
Instead of triggers are used to update the database tables which are associated with view instead of updating the view directly.
What is a View?
A view is virtual table based on result set of sql statement which contains rows and columns just like real table. The fields in view are fields from real tables in the databases.
Ex: Create VIEW [view_name] as select Column_Name from table_name where condtion
A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in underlying tables.
A view also serves as mechanism to simply query execution. Complex queries can be stored in the form as a view and data from the view can be extracted using simple queries
What is the Difference between a Function and a Stored Procedure?
Stored procedure returns one or more values where as function can return one value that is mandatory
Stored procedure can have input and output parameters where as function is having only input parameters.
Functions can be called from procedures but procedures cannot be called from functions
Exceptions can be handled by try-catch block in procedures whereas try-catch block cannot be used in function. (Read more Here)
What are the Advantages of Using Stored Procedures?
1) Stored Procedures reduce the network traffic and increase the performance
2) Stored Procedures allows faster execution
3) Stored Procedures allows modular programming
4) Stored Procedures provides better security to our data