SQL SERVER – What is a self join and Explain it with an example

Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.

Ex:

CREATE TABLE emp ( empid int, mgrid int, empname char(10) )

INSERT emp SELECT 1,2,’Vyas’

INSERT emp SELECT 2,3,’Mohan’

INSERT emp SELECT 3,NULL,’Shobha’

INSERT emp SELECT 4,2,’Shridhar’

INSERT emp SELECT 5,2,’Sourabh’

SELECT t1.empname [Employee], t2.empname [Manager] FROM emp t1, emp t2 WHERE t1.mgrid = t2.empid

Here”s an advanced query’ using a LEFT OUTER JOIN that even returns the employees without managers (super bosses) 

SELECT t1.empname [Employee], COALESCE(t2.empname, ‘No manager’) [Manager] FROM emp t1 LEFT OUTER JOIN emp t2 ON t1.mgrid = t2.empid

Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER – What is a NULL value and What are the pros and cons of using NULLS

A NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value.

A NULL is not a space or empty string or a zero. But some value undefined telling that value is not present.

From front End we can send the value as NULL by DBNull.Value

And in SQL SERVER it can be searched by following query

use AdventureWorksDW2008R2
Go
Select * from dbo.DimCustomer where Suffix IS NULL

here it will return the rows with Suffix column containing a NULL means Noting.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER – What is a synonym and How is it used

A synonym is used to reference a table or view by another name.  The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated.  The synonym is linked to the AUTHID that created it.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER – Implementation of relationships

1-1 Relationship: It is the most uncommon relationship.You might use one-one relationship to divide a table with many fields,to isolate a part of table  for security reasons can be implemented by referencing a primary key in one table to a primary key in other table.

1-m Relationship: It is the most common type of relationship, a record in Table A can have multiple matching records in B, where as record in B should have only one matching record in A. It can be implemented by Primary-foreign key relationships.

M-m Relationship: Database doesn’t support m-m relationships, The same can be implemented by introducing a third (Intermediate) table, which connects both, It is equivalent to having 2 1-m relationships with junction table.The Intermediate table contains a foreign key which is made up of Primary keys in I & II table. For Example the orders and products table have many-many relationship as defined, by creating 2 1-m relationships with Order details table.

There are five normal forms

1st NF: Eliminate the repeating groups in the table.

2nd NF: All Non-key columns should depend on entire primary key, but not on partial key.

3rd NF: Eliminate interdependencies between non-key attributes.

If the table should be in 3rd normal form, the minimum no. of tables should be one.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER 2008 – There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better

Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.
Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

 

SQL SERVER – Cursors

  • What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
    • Cursors allow row-by-row processing of the resultsets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information. Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one roundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors. Most of the times, set based operations can be used instead of cursors. Here is an example: If you have to give a flat hike to your employees using the following criteria: Salary between 30000 and 40000 – 5000 hike Salary between 40000 and 55000 – 7000 hike Salary between 55000 and 65000 – 9000 hike. In this situation many developers tend to use a cursor, determine each employee’s salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
    • UPDATE tbl_emp SET salary = CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000 WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000 WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000 END
    • Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don’t have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the ‘My code library’ section of my site or search for WHILE. Write down the general syntax for a SELECT statements covering all the options. Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax).
    • SELECT select_list [INTO new_table_] FROM table_source [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC] ]
  • Can you have a nested transaction?
    • Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT
  • What is the system function to get the current user’s user id?
    • USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

SQL SERVER – What is subselect and Is it different from a nested select

Subselect is a select which works in conjunction with another select. A nested select is a kind of subselect where the inner select passes to the where criteria for the outer select.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )