SQL Server: Difference between != and <> Operator used for NOT EQUAL TO Operation

SQL – Difference between != and <> Operator used for NOT EQUAL TO Operation

Here is interesting question received on my Facebook page. (On a side note, today we have crossed over 50,000 fans on SQLAuthority Facebook Fan Page).

What is the difference between != and <>Operator in SQL Server as both of them works same for Not Equal To Operator? 

Very interesting question indeed. Even though this looks very simple when I asked quite a few people if they know the answer before I decided to blog about it. The answer which I received was that it seems that many know the answer but everybody wanted to know the more about it.

Here is the answer – Technically there is no difference between != and <>. Both of them work the same way and there is absolutely no difference in terms of performance or result.

Here is the follow up question I received right I answer that there is no difference between those operator.

If != and <> both are the same, which one should be used in SQL queries?

Here is the answer – You can use either != or <> both in your queries as both technically same but I prefer to use <> as that is SQL-92 standard.

Though, many of the leading database applications supports both of the operators. For example -

• SQL Server
• MySQL
• Oracle
• SQLite
• Sybase
• IBM Informix
• PostgreSQL

Here is my return question to you which one of the following operators you use for NOT EQUAL TO operation?

1. !=
2. <>

Website: How to take a Web application offline

To take a Web application offline before deployment

1. Create a file called App_offline.htm and include a message in the file that lets users know that the site is unavailable because you are updating the site.
2. Place the App_offline.htm file in the root folder of the target Web site.
   While the App_offline.htm file exists in the root of your Web site, any request to the Web site will redirect to that file.
   When you have finished deploying the site, remove the App_offline.htm file.

SQL: stored procedures

SQL Server user-defined functions and stored procedures offer similar functionality. Both allow you to create bundles of SQL statements that are stored on the server for future use. This offers you a tremendous efficiency benefit, as you can save programming time by:

• Reusing code from one program to another, cutting down on program development time
• Hiding the SQL details, allowing database developers to worry about SQL and application developers to deal only in higher-level languages
• Centralize maintenance, allowing you to make business logic changes in a single place that automatically affect all dependent applications

At first glance, functions and stored procedures seem identical. However, there are several subtle, yet important differences between the two:

• Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement.
• Stored procedure allow you to enhance application security by granting users and applications permission to use stored procedures, rather than permission to access the underlying tables. Stored procedures provide the ability to restrict user actions at a much more granular level than standard SQL Server permissions. For example, if you have an inventory table that cashiers must update each time an item is sold (to decrement the inventory for that item by 1 unit), you can grant cashiers permission to use a decrement_item stored procedure, rather than allowing them to make arbitrary changes to the inventory table.
• Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all
  .

SQL:Stored Procedure Vs User Defined Function in Sql Server

Difference between Stored Procedure and User Defined Function in Sql Server

June 16, 2012Differences, Sql ServerDiffereence Between Stored Procedure and User Defined function, Stored Procedure, User Defined FunctionBasavaraj Biradar

Below are the some of the major differences between User Defined Function and Stored Procedure in Sql Server.

To know more on the User-Defined functions with examples please visit the article: User-Defined function.To know more on the Stored Procedure with examples please visit the article: Stored Procedure.

Sl. No.	User Defined function	Stored Procedure
1	Function must return a value.	Stored procedure may or not return values.
2	Will allow only Select statement, it will not allow us to use DML statements.	Can have select statements as well as DML statements such as insert, update, delete etc
3	It will allow only input parameters, doesn’t support output parameters.	It can have both input and output parameters.
4	It will not allow us to use try-catch blocks.	For exception handling we can use try catch blocks.
5	Transactions are not allowed within functions.	Can use transactions within Stored procefures.
6	We can use only table variables, it will not allow using temporary tables.	Can use both table variables aswell as temporary table in it.
7	Stored procedures can’t be called from function.	Stored Procedures can call functions.
8	Functions can be called from select statement.	Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec statement can be used to call/execute stored procedure.
9	UDF can be used in join clause as a result set.	Procedures can’t be used in Join clause

Stored Procedures can contain a single SQL statement or a group of SQL statements with data flow control logic containing IF-ELSE, WHILE loop constructs, TRY-CATCH, transactions, etc.
SPs are used to return one or many result-sets to its calling application.

On the other hand Functions or UDFs can contain single or multiple SQL statements depending on its type. A Scalar UDF & Inline UDF can only have a single SELECT statement. And a Multi-Statement UDF can contain a body with multiple SQL statements including SELECTS, IF-ELSE, WHILE loops and DMLs but limited to manipulating table variables only.
UDFs return a single Scalar value or a Table variable to the calling SELECT statement.

Following are some common differences between an SP & a UDF:

Stored Procedures:
– Can be used to read and modify data.
– To run an SP Execute or Exec is used, cannot be used with SELECT statement.
– Cannot JOIN a SP in a SELECT statement.
– Can use Table Variables as well as Temporary Tables inside an SP.
– Can create and use Dynamic SQL.
– Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
– Can use used with XML FOR clause.
– Can use a UDF inside a SP in SELECT statement.
– Cannot be used to create constraints while creating a table.
– Can execute all kinds of functions, be it deterministic or non-deterministic.

Functions:
– Can only read data, cannot modify the database.
– Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
– Can JOIN a UDF in a SELECT statement.
– Cannot use a Temporary Table, only Table Variables can be used.
– Cannot use a Dynamic SQL inside a UDF.
– Cannot use transactions inside a UDF.
– Cannot be used with XML FOR clause.
– Cannot execute an SP inside a UDF.
– Can be used to create Constraints while creating a table.
– Cannot execute some non-deterministic built-in functions, like GETDATE().

SQL Server: Natural vs. Surrogate Keys in SQL Server : Getting the Proper Perspective

Natural vs. Surrogate Keys in SQL Server : Getting the Proper Perspective

BY MICHAEL ASHER

I once walked into a bar, and saw two construction workers pounding each other to a pulp.  The argument was over what was the better tool—a  hammer or screwdriver.  I feel a similar sensation when I see SQL developers arguing over whether to use natural or surrogate keys.    Few other arguments in database design can cause tempers to flare so quickly.  Fans of surrogates consider anyone who uses a natural key a drooling idiot, whereas natural key acolytes believe the use of a surrogate warrants burning alive at the stake.    

Which side is right?  Neither.  Both natural and surrogate keys have their own place, and a developer who doesn’t use both as the situation demands is shortchanging both himself and his applications.

Definitions

A natural key is simply a column or set of columns in a table that uniquely identifies each row.   Natural keys are a feature of the data, not the database, and thus have business meaning on their own.  Quite often a natural key is more than a single column.  For instance, the natural key for a table of addresses might be the five columns: street number, street name, city, state, and zip code.

What is a surrogate key?  Most people will define it as some variation of “a system-generated value used to uniquely identify a row”.    Unlike natural keys, surrogates have no business meaning.   In SQL Server, by far the most common technique for generating surrogate values is the ubiquitous IDENTITY column.

While the above definition is true, there’s another very important part to it.    The value of a surrogate must never be exposed to the outside world.   Users should never be allowed to see the key, under any conditions.   Display the value of a surrogate key on a report, allow it to be viewed on a form or even used as a search term – these are all forbidden.   Once you expose a surrogate key, it immediately begins acquiring business meaning.  

Smart Keys: The Worst of Both Worlds

A smart key is an artificial key with one or more parts that contain business meaning.  For instance, an employee table where each primary key begins with the initial of the employee (“JD1001” for John Doe), or a table of paint products, where the key identifies the can size, color, and paint type, i.e.  “1G-RED- LATEX”.

Smart keys are a sort of hybrid between natural and surrogate keys.  They’re seductively attractive to many developers, but should be avoided like the plague.  They tend to make your design very brittle and subject to failure as business rules change.

Note: if your data already contains meaningful product codes or other keys such as those described above, then they are simply natural keys and the above caveat doesn’t apply.     It’s a smart key only when the value is constructed by the developer.

Benefits of Natural Keys

A natural key is…well, natural.  Since its values already exist in the data, using a natural key means you don’t have to add and maintain a new column.  This also means smaller tables and less storage requirements.  As more rows fit on a database page, it can sometimes mean greater performance. (It can also mean less- more on this later) .  However, in practical terms, the space savings are minor, except for very narrow tables.   Using a surrogate key usually means an additional index is required, though. 

Generating sequential key values is inherently a serial process, so using a natural key rather than an IDENTITY column can be a performance boost for inserts, especially in OLTP environments.

Since natural key values are used as foreign keys in child tables, it can mean the elimination of joins for queries that require no other columns from the parent other than the natural key.

One benefit of natural keys often claimed by its proponents is that they can aid in self-documenting your database schema.   Explicitly naming each natural key documents what specifically identifies each row in a table, and joining on natural keys helps to identify the natural relationships between tables.  This argument based onelegance appeals strongly to those working in academia; it may or may not have much value for developers working in the dirt and grime of real production systems.

Benefits of Surrogate Keys

Since surrogate values are controlled by the system, you never have to worry about duplicate, missing, or changing values.  They’re also an easy and reliable way to join tables; when writing queries, you never need worry about remembering which combination of columns is the natural key.  However, some of these benefits are less compelling than they seem.   I’ll discuss separately each case for using a surrogate, and whether or not it holds up.

When no natural key on the table exists – Yes.  
If the table has no unique identifier, then you must create one.  Unkeyed tables are in general a very bad idea.   There are exceptions such as logging or summary tables in which rows are only inserted, never updated.  Otherwise, if your table doesn’t have a unique key—create one.

When the natural key can change- Sometimes.  
Immutability is certainly a desirable feature for a key, but it’s by no means a requirement.   Using this as an excuse is the trademark of a lazy developer.  Data changes.   If the key changes, update it.  If it’s being used as a foreign key in another table – update that table too.  Cascading updates exist for a reason, after all.

Obviously if a key changes very often, or will be used as a FK for many other tables, there can be performance or concurrency implications to making it the primary key .  In this case, you do want to consider a surrogate, and use it if appropriate. 

There’s one particular case where the stability of a surrogate key actually works against you.  For lookup tables, particular those containing selection options for given fields, changes to the lookup value are often not meant to be cascaded into child tables.   For example, an application may store the “referral source” for new customers or marketing leads, whether they were generated via an ad in a newspaper or magazine, a yellow pages entry, word of mouth, etc.   These referral codes can be very specific, and are normally stored in a lookup table.  Once set, the code should be preserved historically, even if the original lookup table value is updated or removed.   This is behavior very difficult to achieve with a surrogate key, but trivial with a natural key.

When natural key values can be missing or duplicated - No. 
This is probably the most misunderstood aspect of the natural vs. surrogate debate.   Natural keys are unique by definition.   If it isn’t a serious error for a value to be missing or duplicated, then that value isn’t a natural key to begin with.  And if it is an error, then you’re almost always better off trapping that error at the database level,  rather than allowing that bad data into your DB. 

As example, consider a table of employees keyed off Social Security Number.   Users are complaining the database throws errors when they don’t have a SSN or mistakenly enter a duplicate.   So you replace the SSN PK with a surrogate and smugly conclude you’ve solved the problem.   But have you?   Now some employees don’t have SSNs, and the accounting module starts failing when printing tax records … or worse, collates all the NULL SSN entries together, reporting them as a single employee.     The search function starts returning the wrong rows because some employees are sharing the same SSNs, and the new hire in the mailroom gets the boss’s paycheck, because someone in HR accidentally cut and pasted a SSN.  

In reality, all you’ve done is short-circuit out the data integrity safeguards in your database, and pass the responsibility for the problem up to the application level. Bad move.

These sorts of problems exist because most tables have a uniqueness requirement at the tion level.    A surrogate key only solves the uniqueness problem  at the database level, but users (who cannot and should not see the surrogate value) still don’t have a way to uniquely identify each record.  This also explains why when, even if you choose to use a surrogate key, you will usually want to also add a unique constraint on the original natural key, since uniqueness is no longer being automatically enforced by the PK.

But wait a minute!  What if your business rules specifically require employees to be input before you have their SSN data?  Or what if your table holds overseas employees that may not have a SSN at all?  Does that mean you can’t use a natural key?  Maybe.  One possibility is to assign your own unique values in these cases.   One system I’ve seen used randomly generated alphabetic values for temporary SSNs, whereas the standard numeric value identified a “real” one.   Better yet is to examine your table for some other column or columns that can be used as a natural key.  Or maybe you really do want to drop natural keys altogether.  The point here is not that surrogates should never be used, but simply that if your natural key isn’t unique, you are going to have problems beyond those that a surrogate will solve.

When the natural key is very wide, or a composite of multiple columns – Sometimes . 
Wide keys make for fat indexes.  Fat indexes have performance implications.  A very wide key  can hurt performance far more than the extra space required by a surrogate.   Replacing a composite key with a surrogate also simplifies your queries, but this should never be a primary consideration.  It’s poor form to replace the natural key of a two CHAR(2) columns with am INT IDENTITY, for no other reason than it makes your queries prettier.

One common case where a multi-column natural key should always be used is the so-called junction table: a table used to implement a many-many relationship between two other tables.  Most junction tables have only two columns, each a FK back to a parent table.  The combination of these two FKs is itself the primary key for the table.   Adding a surrogate to a table like this is just asking for trouble.

For performance reasons.  
This is the trickiest question of all.   Replacing a wide key with a narrower value means smaller indexes, and more values retrieved from every index page read.  This does boost performance.  However,  you’ll usually retain the index on the natural key (to enforce uniqueness if nothing else) and that means another index to maintain.  If your table is very narrow, the additional column for the surrogate can noticeably impact performance and storage requirements.  Finally, some queries that may have not required JOINs with a natural  foreign key may now need them.  For instance, our employee SSN example might have a child table containing reported hours worked:

Table: ReportedHours
Start_Time	DATETIME
Stop_Time	DATETIME
EmployeeID	(Foreign key to Employee table)

If the EmployeeID  FK is SSN, then we can retrieve a list of total hours by SSN from this table alone:

SELECT EmployeeID, SUM(DATEDIFF(hr,StopTime,StartTime))

FROM ReportedHours

GROUP BY EmployeeID

With EmployeeID as a surrogate key, however, we must JOIN back to the EMPLOYEE table:

SELECT SSN, SUM(DATEDIFF(hr,StopTime,StartTime))

FROM ReportedHours h

JOIN Employees e ON h.EmployeeID = e.EmployeeID

GROUP BY e.SSN

Continues…

SQL SERVER – Add Any User to SysAdmin Role – Add Users to System Roles

The reason I like blogging is follow up questions. I have wrote following two articles earlier this week. I just received question based on both of them. Before I go on questions, I recommend to read both of the article first. Both of them are very small article so they are quick to read.

SQL SERVER – DISABLE and ENABLE user SA

SQL SERVER – Fix : Error : Msg 15151, Level 16, State 1, Line 2 Cannot alter the login ’sa’, because it does not exist or you do not have permission

The question I had received is following: “How to add any user to systemadmin role?“

This is very simple process.

Method 1: T-SQL

USE [master]
GOCREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOEXEC master..sp_addsrvrolemember @loginame = N'test', @rolename =N'sysadmin'GO

Method 2:  SQL Server Management Studio

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – ERROR: FIX – Database diagram support objects cannot be installed because this database does not have a valid owner

Recently one of my friend sent me email that he is having some problem with his very small database. We talked for few minutes and we agreed that to further investigation I will need access to the whole database. As the database was very big he dropped it in common location (you can use livemesh or dropbox  or any other similar product) and I picked up from the location.

I was able to install the database successful. He informed me that he has created database diagram so I can easily understand his database tables. As soon as I tried to open the database diagram I faced following error. For a while I could not figure out how to resolve the error.

Error:
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Workaround / Fix / Solution :

Well for a while I attempted few things and nothing worked. After that I carefully read the error and I realized that solution was proposed in the error only. I just have to read it carefully. Here are the steps I did to make this work.

-- Replace YourDatabaseName in following scriptALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa
GO

• Select your database >> Right Click >> Select Properties
• Select FILE in left side of page
• In the OWNER box, select button which has three dots (…) in it
• Now select user ‘sa’ or NT AUTHORITY\SYSTEM and click OK.

This should solve your problem.

Please note, I suggest you check your security policies before changing authorization. I did this to quickly solve my problem on my development server. If you are on production server, you may open yourself to potential security compromise.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Server: Interview Questions

1. What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

2. What are the properties of the Relational tables?

Relational tables have six properties:

1. Values are atomic.
2. Column values are of the same kind.
3. Each row is unique.
4. The sequence of columns is insignificant.
5. The sequence of rows is insignificant.
6. Each column must have a unique name.

3. What is Normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. 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.

4. What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

5. What are different normalization forms?

1. 1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3. 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
4. BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
5. 4NF: Isolate Independent Multiple Relationships No table may contain two or more 1:n or n:m relationships that are not directly related.
6. 5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships.
7. ONF: Optimal Normal Form A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
8. DKNF: Domain-Key Normal Form A model free from all modification anomalies is said to be in DKNF.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

6. What is Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g. sp_helpdb, sp_renamedb, sp_depends etc.

7. What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

8. What is Nested Trigger?

A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

9. What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

10. What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

11. What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

12. What is Cursor?

Cursor is a database object used by applications to manipulate data in a set on a row-by- row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor we need to perform some steps in the following order:

1. Declare cursor
2. Open cursor
3. Fetch row from the cursor
4. Process fetched row
5. Close cursor
6. Deallocate cursor

13. What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width.

14. What is Difference between Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF's can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

15. What is sub-query? Explain properties of sub-query?

Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

16. What are different Types of Join?

1. Cross Join A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
2. Inner Join A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.
3. Outer Join A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:
   1. Left Outer Join: In Left Outer Join all rows in the first-named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.
   2. Right Outer Join: In Right Outer Join all rows in the second-named table i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
   3. Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.
4. Self Join This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.

17. What are primary keys and foreign keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

18. What is User Defined Functions? What kind of User-Defined Functions can be created?

User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.
Different Kinds of User-Defined Functions created are:

1. Scalar User-Defined Function A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.
2. Inline Table-Value User-Defined Function An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.
3. Multi-statement Table-Value User-Defined Function A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

19. What is Identity?

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.

20. What is DataWarehousing?

1. Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
2. Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
3. Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting.
4. Integrated, meaning that the database contains data from most or all of an organization's operational applications, and that this data is made consistent.

SQL Server database developer interview questions and answers

Development hiring managers and potential interviewees may find these open-ended SQL Server proficiency interview Q&As useful. 

The first installment of this TechRepublic series focused on C# developer interview questions. It generated a lively discussion on the merits of such questions and the different approaches to measuring technical aptitude during an interview process.

This week we take a look at SQL Server. These questions (which all apply to SQL Server 2008) are not meant to be the only way to ascertain a candidate's technical expertise during an interview, but rather one piece of the puzzle that may spawn more thorough discussions. The depth of the questions may vary according to the level of the open position; for instance, you would expect more from a senior level candidate than from a junior level candidate.

Note: This content is also available as a downloadable PDF.

What are DMVs?

Dynamic management views (DMVs) and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance; that is, they let you see what is going on inside SQL Server. They were introduced in SQL Server 2005 as an alternative to system tables. One example is viewing operating system wait statistics via this query:

SELECT * FROM sys.dm_os_wait_stats;

Another example is examining current sessions, much like the sp_who2 command:

SELECT * FROM sys.dm_exec_sessions;

What are temp tables? What is the difference between global and local temp tables?

Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character is used to declare a temporary table as it is prepended to the table name. A single hash (#) specifies a local temporary table.

CREATE TABLE #tempLocal ( nameid int, fname varchar(50), lname varchar(50) )

Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.

Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.

CREATE TABLE ##tempGlobal ( nameid int, fname varchar(50), lname varchar(50) )

Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.

How are transactions used?

Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.

What is the difference between a clustered and a nonclustered index?

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.

A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.

You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book's index.

What are DBCC commands?

Basically, the Database Consistency Checker (DBCC) provides a set of commands (many of which are undocumented) to maintain databases -- maintenance, validation, and status checks. The syntax is DBCC followed by the command name. Here are three examples:

DBCC CHECKALLOC -- Check disk allocation consistency.

DBCC OPENTRAN -- Display information about recent transactions.

DBCC HELP -- Display Help for DBCC commands.

What is the difference between truncate and delete?

Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.

What does the NOLOCK query hint do?

Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data -- you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.

SELECT * FROM table_name (NOLOCK)

Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint. There are lots more query hints with plenty of information online.

What is a CTE?

A common table expression (CTE) is a temporary named result set that can be used within other statements like SELECT, INSERT, UPDATE, and DELETE. It is not stored as an object and its lifetime is limited to the query. It is defined using the WITH statement as the following example shows:

WITH ExampleCTE (id, fname, lname)

AS

(

SELECT id, firstname, lastname FROM table

)

SELECT * FROM ExampleCTE

A CTE can be used in place of a view in some instances.

What is a view? What is the WITH CHECK OPTION clause for a view?

A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables.

The WITH CHECK OPTION for a view prevents data modifications (to the data) that do not confirm to the WHERE clause of the view definition. This allows data to be updated via the view, but only if it belongs in the view.

What is a query execution plan?

SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.

What does the SQL Server Agent Windows service do?

SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned -- often it is as simple as the SQL Server Agent service not running.

What is the default port number for SQL Server?

If enabled, the default instance of Microsoft SQL Server listens on TCP port 1433. Named instances are configured for dynamic ports, so an available port is chosen when SQL Server starts. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

The list of possible questions is endless. I am sure these questions will spawn debate and discussion.