<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
Month: January 2016
What is a deadlock in SQL Server | What are “GRANT” and “REVOKE’ statements in SQL | How do you find the Second highest Salary in SQL Server
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?
Answer:
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 @@ROWCOUNT in SQL | What is Difference between Commit and Rollback in SQL | What is Candidate key, alternate key and composite key in SQL | What are Isolation Levels in SQL | What is Lock Escalation in SQL Server
What is the Use of @@ROWCOUNT?
Returns the number of rows affected by the Query.
Ex:
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:
BEGIN TRANSACTION
Operations
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
- Serializable
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 | What is UNIQUE KEY Constraint | What is FOREIGN KEY Constraint | What is CHECK Constraint | What is NOT NULL Constraint in 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 | What is Connection Pooling and why it is used | What are the Authentication Modes in SQL Server | What is the difference between UNION and UNION ALL | What is Identity in SQL Server
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 | What is the Difference between Clustered and a Non-clustered Index in SQL Server | What is Collation | What are Primary Keys and Foreign Keys in SQL Server | What’s the Difference between a Primary Key and a Unique Key in SQL Server
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 | What is a View | What is the Difference between a Function and a Stored Procedure | What are the Advantages of Using Stored Procedures in SQL Server
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
What is Normalization | What is De Normalization | What is a Stored Procedure | What is a Trigger in SQL Server
What is Normalization?
In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.There are three main normal forms, each with increasing levels of normalization:
- First Normal Form (1NF): Each field in a table contains different information. For example, in an employee list, each table would contain only one birthdate field.
- Second Normal Form (2NF): Each field in a table that is not a determiner of the contents of another field must itself be a function of the other fields in the table.
- Third Normal Form (3NF): No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflecting in all tables that link to the birthdate table.
There are additional normalization levels, such as Boyce Codd Normal Form (BCNF), fourth normal form (4NF) and fifth normal form (5NF). While normalization makes databases more efficient to maintain, they can also make them more complex because data is separated into so many different tables.
(2) In data processing, a process applied to all data in a set that produces a specific statistical property. For example, each expenditure for a month can be divided by the total of all expenditures to produce a percentage.
(3) In programming, changing the format of a floating-point number so the left-most digit in the mantissa is not a zero.
What is De-normalization?
As the name indicates, de-normalization is the reverse process of normalization. It’s the controlled introduction of redundancy in to the database design.
It helps improve the query performance as the number of joins could be reduced.
What is a Stored Procedure?
Stored Procedure is a group of sql statements that has been created once and stored in server database. Stored procedures will accept input parameters so that single stored procedure can be used over network by multiple clients using different input data. Stored procedures will reduce network traffic and increase the performance. (Read more Here)
What is a Trigger?
Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there’s no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder
Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, and DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also.
What is the difference between char, varchar, nvarchar in sql server?
Char DataType
Char datatype which is used to store fixed length of characters. Suppose if we declared char(50) it will allocates memory for 50 characters. Once we declare char(50) and insert only 10 characters of word then only 10 characters of memory will be used and other 40 characters of memory will be wasted.
varchar DataType
Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. Suppose if we declared varchar(50) it will allocates memory of 0 characters at the time of declaration. Once we declare varchar(50) and insert only 10 characters of word it will allocate memory for only 10 characters.
nvarchar DataType
nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.
So if we are not using other languages then it’s better to use varchar datatype instead of nvarchar
How to handle exceptions in SQL Server?
To handle exceptions in SQL Server we can use TRY…… CATCH blocks. To use TRY…… CATCH blocks in stored procedure we need to write the query like as shown below
|
BEGIN TRY —Write Your Code END TRY BEGIN CATCH —Write Code to handle errors END CATCH |
In TRY block we will write our queries and in CATCH block we will write code to handle exceptions. In our SQL statements if any error occurs automatically it will move to CATCH block in that we can handle error messages. To handle error messages we have defined Error Functions in CATCH block those are
ERROR_LINE() – This function will return error line number of SQL query which cause to raise error.
ERROR_NUMBER() – This function will return error number which is unique and assigned to it.
ERROR_SEVERITY() – This function will return severity of error which indicates how serious the error is. The values are between 1 and 25.
ERROR_STATE() – This function will return state number of error message which cause to raise error.
ERROR_PROCEDURE() – This function will return name of the procedure where an error occurred.
ERROR_MESSAGE() – This function will return the complete text of the error message which cause to raise error.
Check below sample query to handle errors in stored procedure
|
BEGIN TRY SELECT 300/0 END TRY BEGIN CATCH SELECT ErrorNumber = ERROR_NUMBER(), ErrorSeverity = ERROR_SEVERITY(), ErrorState =ERROR_STATE(), ErrorProcedure = ERROR_PROCEDURE(), ErrorLine = ERROR_LINE(), ErrorMessage =ERROR_MESSAGE() END CATCH |
pramadhablog 
You must be logged in to post a comment.