SQL Server

What is the difference between a file and a database? Can files qualify as a database?

1.       Database provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information.

2.       Secondly, the information has to be persistent, that means even after the application is closed the information should be persisted.

3.       Finally, it should provide an independent way of accessing data and should not be dependent on the application to access the information.

What is the Difference between DBMS and RDBMS?

DBMS provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information. RDBMS also provides what DBMS provides, but above that, it provides relationship integrity. So in short, we can say:

RDBMS = DBMS + REFERENTIAL INTEGRITY

For example, in the above Figure 1.1, every person should have an Address. This is a referential integrity between Name and Address. If we break this referential integrity in DBMS and files, it will not complain, but RDBMS will not allow you to save this data if you have defined the relation integrity between person and addresses. These relations are defined by using “Foreign Keys” in any RDBMS.

Many DBMS companies claimed that their DBMS product was RDBMS compliant, but according to industry rules and regulations, if the DBMS fulfills the twelve CODD rules, it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc.) fulfill all the twelve CODD rules and are considered truly as RDBMS.

What is Denormalization?

Denormalization is the process of putting one fact in numerous places (it is vice-versa of normalization). Only one valid reason exists for denormalizing a relational design - to enhance performance. The sacrifice to performance is that you increase redundancy in a database.

OR

denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.

What does UNION do? What is the difference between UNION and UNION ALL?

UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.

What is a key difference between Truncate and Delete?

Truncate is used to delete table content and the action cannot be rolled back, whereas Delete is used to delete one or more rows in the table and can be rolled back.

DELETE

1. DELETE is a DML Command.

2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.

3. We can specify filters in where clause

4. It deletes specified data if where condition exists.

5. Delete activates a trigger because the operation are logged individually.

6. Slower than truncate because, it keeps logs.

7. Rollback is possible.

TRUNCATE

1. TRUNCATE is a DDL command.

2. TRUNCATE TABLE always locks the table and page but not each row.

3. Cannot use Where Condition.

4. It Removes all the data.

5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.

6. Faster in performance wise, because it doesn't keep any logs.

7. Rollback is not possible.

What are the NVL and the NVL2 functions in SQL? How do they differ?

Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null.

With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1.

With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

What is Event in C#?

Events are kind of delegates who work in subscribe/Listener fashion. In other words, Event and Delegates work hand in hand. Following is a very basic example of Events about a person withdrawing money from bank. For any kind of account, money should be deducted from balance, Object Sender and EventArgs can be used to determine account type and request sender. To make the example simple, only basic functionality is implemented.

What is Implicit Typing or var Keyword?

Implicit typing through var keyword determines the data type of variable at the time of declaration and initialization. Var is different from Variant data type used in some languages and still holds strongly typed characteristics. Var variable cannot be initialize as null since the compiler cannot determine its data type (only Reference type variable can be declared as null). You must need to implicitly convert null value to some reference type, e.g., string.

var accountnum = "342425756342"; //Correct, compiled as string

accountnum = 50;                 //Compiler error since it is considered as string in first statement.

var username = null;             //Compiler error, unable to determine the datatype

var username = (string)null;     //Successfully compile, string is reference.

What is sub query and its properties?

A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. Properties of sub query can be defined as

·         A sub query should not have order by clause

·         A sub query should be placed in the right hand side of the comparison operator of the main query

·         A sub query should be enclosed in parenthesis because it needs to be executed first before the main query

·         More than one sub query can be included

What is SQL server agent?

The SQL Server agent plays  a vital role in day to day tasks of SQL server administrator(DBA). Server agent’s purpose is to implement the tasks easily with the scheduler engine which allows our jobs to run at scheduled date and time.

What is COALESCE in SQL Server?

COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.

Example –

Select COALESCE(empno, empname, salary) from employee;

What is Bulkcopy in SQL?

Bulkcopy is a tool used to copy large amount of data from Tables. This tool is used to load large amount of data in SQL Server.

What is Collation?

Collation is defined to specify the sort order in a table. There are three types of sort order –

• ·         Case sensitive
• ·         Case Insensitive
• ·         Binary

How can we get count of the number of records in a table?

Following are the queries can be used to get the count of records in a table –

Select * from <tablename> Select count(*) from <tablename> Select rows from sysindexes where id=OBJECT_ID(tablename) and indid<2

What is the difference between SUBSTR and INSTR in the SQL Server?

The SUBSTR function is used to return specific portion of string in a given string. But, INSTR function gives character position in a given specified string.

SUBSTR(“Smiley”,3)

Gives result as Smi

INSTR(“Smiley”,’i’,1)

Gives 3 as result as I appears in 3rd position of the string

What is the use of =,==,=== operators?

= is used to assign one value or variable to another variable. == is used for comparing two strings or numbers. === is used to compare only string with the string and number with numbers.

What is ISNULL() operator?

ISNULL function is used to check whether value given is NULL or not NULL in sql server. This function also provides to replace a value with the NULL.

What is the difference between COMMIT and ROLLBACK?

Every statement between BEGIN and COMMIT becomes persistent to database when the COMMIT is executed. Every statement between BEGIN and ROOLBACK are reverted to the state when the ROLLBACK was executed.

What is the difference between varchar and nvarchar types?

Varchar and nvarchar are same but the only difference is that nvarhcar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.

How to delete duplicate rows in SQL Server?

Duplicate rows can be deleted using CTE and ROW NUMER feature of SQL Server.

Which command is used for user defined error messages?

RAISEERROR is the command used to generate and initiates error processing for a given session. Those user defined messages are stored in sys.messages table.

What is SQL injection?

SQL injection is an attack by malicious users in which malicious code can be inserted into strings that can be passed to an instance of SQL server for parsing and execution. All statements have to checked for vulnerabilities as it executes all syntactically valid queries that it receives.

Even parameters can be manipulated by the skilled and experienced attackers.

What is Filtered Index?

Filtered Index is used to filter some portion of rows in a table to improve query performance, index maintenance and reduces index storage costs. When the index is created with WHERE clause, then it is called Filtered Index.

What is the difference among UNION, MINUS and INTERSECT

UNION combines the results from 2 tables and eliminates duplicate records from the result set.

MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.

INTERSECT operator returns us only the matching or common rows between 2 result sets.

To understand these operators, let’s see some examples. We will use two different queries to extract data from our emp table and then we will perform UNION, MINUS and INTERSECT operations on these two sets of data.

UNION

SELECT * FROM EMPLOYEE WHERE ID = 5

UNION

SELECT * FROM EMPLOYEE WHERE ID = 6

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
5	2	2.0	Anno	80.0	01-Feb-2012
6	2	2.0	Darl	80.0	11-Feb-2012

MINUS

SELECT * FROM EMPLOYEE

MINUS

SELECT * FROM EMPLOYEE WHERE ID > 2

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
1		2	Hash	100.0	01-Jan-2012
2	1	2	Robo	100.0	01-Jan-2012

INTERSECT

SELECT * FROM EMPLOYEE WHERE ID IN (2, 3, 5)

INTERSECT

SELECT * FROM EMPLOYEE WHERE ID IN (1, 2, 4, 5)

ID	MGR_ID	DEPT_ID	NAME	SAL	DOJ
5	2	2	Anno	80.0	01-Feb-2012
2	1	2	Robo	100.0	01-Jan-2012

What is the difference between a Local and a Global temporary table?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

What is the difference between SQL and MySQL or SQL Server?

SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft’s SQL Server both are relational database management systems that use SQL as their standard relational database language.

Is a NULL value same as zero or a blank space? If not then what is the difference?

A NULL value is not same as zero or a blank space. A NULL value is a value which is ‘unavailable, unassigned, unknown or not applicable’. Whereas, zero is a number and blank space is a character.

Say True or False. Give explanation if False.

If a column value taking part in an arithmetic expression is NULL, then the result obtained would be NULLM.

How do you search for a value in a database table when you don’t have the exact value to search for?

In such cases, the LIKE condition operator is used to select rows that match a character pattern. This is also called ‘wildcard’ search.

What is the purpose of the NVL function?

The NVL function converts a NULL value to an actual value.

What is the difference between the NVL and the NVL2 functions?

The NVL(exp1, exp2) function converts the source expression (or value) exp1 to the target expression (or value) exp2, if exp1 contains NULL. The return value has the same data type as that of exp1.

The NVL2(exp1, exp2, exp3) function checks the first expression exp1, if it is not null then, the second expression exp2 is returned. If the first expression exp1 is null, then the third expression exp3 is returned.

What is the difference between cross joins and natural joins?

The cross join produces the cross product or Cartesian product of two tables. The natural join is based on all the columns having same name and data types in both the tables.

What is the purpose of the group functions in SQL? Give some examples of group functions.

Group functions in SQL work on sets of rows and returns one result per group. Examples of group functions are AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE.

What’s wrong in the following query?

SELECT subject_code, count(name) 

FROM students;

It doesn’t have a GROUP BY clause. The subject_code should be in the GROUP BY clause.

   SELECT subject_code, count(name)

   FROM students

   GROUP BY subject_code;

What is the difference between VARCHAR2 AND CHAR datatypes?

VARCHAR2 represents variable length character data, whereas CHAR represents fixed length character data.

Cast and Convert Function

The CONVERT() function is a general function that converts an expression of one data type to another.The CONVERT() function can be used to display date/time data in different formats.

Select Cast(getdate() as date)

SELECT CONVERT(VARCHAR(12),GETDATE(), 113)

DATENAME() and DATEPART() Function

Both are same in every aspect but with little diffrenece

Select DATENAME(WEEKDAY,GetDate()) Return Type Is Character

Select DATEPART(WEEKDAY,GETDATE()) Return Type is Int